Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. В предыдущих главах вы познакомились с функцией ДВССЫЛ, узнали, как с ее помощью сослаться на другой лист, что делать, если имя листа содержит дату. Сегодня еще о двух интересных возможностях использования функции ДВССЫЛ.
Задача: как было показано ранее, ДВССЫЛ удобно использовать для получения значения ячейки, адрес которой вычисляется в другой ячейке. Можно использовать ДВССЫЛ для получения данных из диапазона ячеек, который затем использовался бы внутри функций ВПР или СУММЕСЛИ?
Решение: вы можете построить функцию ДВССЫЛ, которая ссылается на диапазон. Извлеченный диапазон далее может быть использован в качестве таблицы подстановки в ВПР или внутри СУММЕСЛИ, СЧЁТЕСЛИ.
Формула =СУММЕСЛИ(ДВССЫЛ(D$4&"!A2:A300");$A5;ДВССЫЛ(D$4&"!C2:C300")) в ячейке D5 (рис. 1) извлекает данные из рабочего листа Store3 (он указан в строке 4). Первый аргумент функции СУММЕСЛИ извлекает диапазон дат Store3!A2:A300. Второй аргумент – $A5 – осуществляет поиск записей, которые соответствуют определенной дате из столбца А. Третий аргумент – Store3!С2:С300 – находит количество в столбце С, соответствующее дате, и возвращает его.
Рис. 1. Каждая функция ДВССЫЛ ссылается на прямоугольный диапазон на другом листе
Скачать заметку в формате Word или pdf, примеры в формате Excel
Примечание: так как каждый лист может иметь различное число записей, на всякий случай диапазон расширен до 300. Это число существенно больше, чем количество сделок на любом листе.
Резюме: вы можете использовать ДВССЫЛ, чтобы извлечь данные из диапазона ячеек. Так как в этом случае ДВССЫЛ вернет массив, вы должны его подставить в формулу, работающую с массивом.
Всегда ссылаться на ячейку B10
Задача: требуется создать формулу, которая бы всегда ссылалась на ячейку B10. Обычно, если у вас есть формула, которая ссылается на В10 или даже на $В$10, формула меняется, если вы вырежете/вставите строку/столбец выше/левее B10.
Например (рис. 2), формула в D1 проверяет, есть ли какое-либо содержимое в ячейке B10.
Рис. 2. Формула в D1 ссылается на В10
Если удалить строки 6, 8, и 9, формула изменит ссылку на B7 (рис. 3).
Рис. 3. Ссылка перемещается на B7 если удалить три строки выше В10
Решение: вы можете использовать ДВССЫЛ(«В10»), чтобы гарантировать, что формула всегда будет ссылаться на ячейку B10. Даже если удалить или вставить строки, и даже если вырезать В10 и вставить, ее в B99, ваша формула всегда будет указывать В10 (рис. 4).
Рис. 4. ДВССЫЛ("В10") заставит Excel всегда ссылаться на B10
Подводный камень: если вы любите использовать аудит формул, обратите внимание, что опции Влияющие ячейки и Зависимые ячейки не будут работать в отношении формулы ДВССЫЛ в D1, и не покажут связь с ячейкой B10 (рис. 5). Excel сообщит, что текущая ячейка не содержит ссылок на другие ячейки.
Рис. 5. Опция Влияющие ячейки не работает с функцией ДВССЫЛ
Резюме: чтобы заставить формулу всегда ссылаться на ячейку B10, используйте ДВССЫЛ("В10").