Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

Сегодня обсудим еще одну задачку и повторим материал по функции =ДВССЫЛ().

Здравствуйте, уважаемые подписчики. С Вами Алексей Шмуйлович.

Сегодня обсудим еще одну задачку и повторим материал по функции =ДВССЫЛ().

Вопрос читателя:

 

Я веду некоторую базу данных в Экселе. Иногда к списку добавляются новые наименования, иногда они удаляются. Если наименование добавлено, то оно вносится очередной строкой внизу, если удалено, то вместо наименования записывается "блокировано", "Удалено" или просто остается пустая ячейка.
Мне надо оперативно знать, сколько в списке всего уничтоженных наименований.
В конце списка я написал такую формулу:

=СЧЁТЕСЛИ(C1:C1021;"блокировано")+СЧЁТЕСЛИ(C1:C1021;"удалено")+СЧЁТЕСЛИ(C1:
C101;"")

Она всем удобна, кроме адреса (в данном примере адрес С1021)
Т.е. если я дописываю новое наименование, то (согласно примеру) я пишу его уже на строку 1022, и мне вручную приходится корректировать всю формулу, меняя С1021 на С1022.


А нельзя сделать так, чтобы вот этот меняющийся адрес (желательно цифру) формула сама брала из какой-либо ячейки? Т.е. я вручную в ячейку ставлю 1420 (например), и формула сама поменяла адреса блока ячеек с С1:С1021 на С1:С1420.
Если подскажете - большое спасибо :)

От чего ж не подсказать, подскажем Cool.


Сначала тот вариант, о котором Вы говорите – номер строки Вы указываете сами, внося его в какую-нибудь служебную ячейку.

Используйте функцию =ДВССЫЛ(). Эта функция возвращает значение ячейки, адрес которой задан текстовой строкой в качестве ее аргумента.

Например формула =ДВССЫЛ("С1420";1) вернет значение ячейки С1420. Самое приятное в этой функции то, что строчку с адресом можно получать из значений нескольких ячеек. Предположим, в ячейке В18 хранится номер строки (см. условия задачи выше). Значение этой ячейки равно 1420. Тогда формула =ДВССЫЛ("С"&B18;1) даст тот же результат, что предыдущая, с той лишь разницей, что теперь нам достаточно изменить номер строки в ячейке В18 и мы получим результат из другой строки.

Единица во втором аргументе, означает, то ссылка задана в стиле А1. О стилях ссылок поговорим в другой раз.

Функцию =ДВССЫЛ() можно использовать и для ссылки на диапазон значений. Например, формула =СУММ(ДВССЫЛ("B7:B"&B18;1)) вычислит сумму диапазона ячеек столбца В от 7- строки, до строки, номер которой установлен в ячейке В18.



Я использовал вопрос читателя для пояснения принципа работы с функцией =ДВССЫЛ(), однако такое решение, на мой взгляд, не самое простое для поставленной задачи.

Гораздо проще между строками базы данных (списком) и строкой итога оставить пустую строчку и включить эту пустую строчку в итоговую формулу, будь то формула =СУММ() или =СЧЁТЕСЛИ(), как у нашего читателя, или любая другая функция, обрабатывающая значения диапазона ячеек.

Теперь, если Вы добавите в базу данных новую строчку ВЫШЕ нашей служебной пустой строки, итоговая формула автоматически перестроится, диапазон в аргументе расширится. Все очень просто.

 

Пример к сегодняшней задаче по адресу http://studenttools.narod.ru/excel/dvssyl.rar


Н
а сегодня все. Не забудьте оценить выпуск. Пишите на форум.

С уважением,
Алексей Шмуйлович
Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.
+7 920 660 9496