Использование ДВССЫЛ для получения данных из диапазона ячеек

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel. В предыдущих главах вы познакомились с функцией ДВССЫЛ, узнали, как с ее помощью сослаться на другой лист, что делать, если имя листа содержит дату. Сегодня еще о двух интересных возможностях использования функции ДВССЫЛ.

Задача: как было показано ранее, ДВССЫЛ удобно использовать для получения значения ячейки, адрес которой вычисляется в другой ячейке. Можно использовать ДВССЫЛ для получения данных из диапазона ячеек, который затем использовался бы внутри функций ВПР или СУММЕСЛИ?

Решение: вы можете построить функцию ДВССЫЛ, которая ссылается на диапазон. Извлеченный диапазон далее может быть использован в качестве таблицы подстановки в ВПР или внутри СУММЕСЛИ, СЧЁТЕСЛИ.

Формула =СУММЕСЛИ(ДВССЫЛ(D$4&"!A2:A300");$A5;ДВССЫЛ(D$4&"!C2:C300")) в ячейке D5 (рис. 1) извлекает данные из рабочего листа Store3 (он указан в строке 4). Первый аргумент функции СУММЕСЛИ извлекает диапазон дат Store3!A2:A300. Второй аргумент – $A5 – осуществляет поиск записей, которые соответствуют определенной дате из столбца А. Третий аргумент – Store3!С2:С300 – находит количество в столбце С, соответствующее дате, и возвращает его.

Рис. 1. Каждая функция ДВССЫЛ ссылается на прямоугольный диапазон на другом листе

Рис. 1. Каждая функция ДВССЫЛ ссылается на прямоугольный диапазон на другом листе

Скачать заметку в формате Word или pdf, примеры в формате Excel

Примечание: так как каждый лист может иметь различное число записей, на всякий случай диапазон расширен до 300. Это число существенно больше, чем количество сделок на любом листе.

Резюме: вы можете использовать ДВССЫЛ, чтобы извлечь данные из диапазона ячеек. Так как в этом случае ДВССЫЛ вернет массив, вы должны его подставить в формулу, работающую с массивом.

Всегда ссылаться на ячейку B10

Задача: требуется создать формулу, которая бы всегда ссылалась на ячейку B10. Обычно, если у вас есть формула, которая ссылается на В10 или даже на $В$10, формула меняется, если вы вырежете/вставите строку/столбец выше/левее B10.

Например (рис. 2), формула в D1 проверяет, есть ли какое-либо содержимое в ячейке B10.

Рис. 2. Формула в D1 ссылается на В10

Рис. 2. Формула в D1 ссылается на В10

Если удалить строки 6, 8, и 9, формула изменит ссылку на B7 (рис. 3).

Рис. 3. Ссылка перемещается на B7 если удалить три строки выше В10

Рис. 3. Ссылка перемещается на B7 если удалить три строки выше В10

Решение: вы можете использовать ДВССЫЛ(«В10»), чтобы гарантировать, что формула всегда будет ссылаться на ячейку B10. Даже если удалить или вставить строки, и даже если вырезать В10 и вставить, ее в B99, ваша формула всегда будет указывать В10 (рис. 4).

Рис. 4. ДВССЫЛ(В10) заставит Excel всегда ссылаться на B10

Рис. 4. ДВССЫЛ("В10") заставит Excel всегда ссылаться на B10

Подводный камень: если вы любите использовать аудит формул, обратите внимание, что опции Влияющие ячейки и Зависимые ячейки не будут работать в отношении формулы ДВССЫЛ в D1, и не покажут связь с ячейкой B10 (рис. 5). Excel сообщит, что текущая ячейка не содержит ссылок на другие ячейки.

Рис. 5. Опция Влияющие ячейки не работает с функцией ДВССЫЛ

Рис. 5. Опция Влияющие ячейки не работает с функцией ДВССЫЛ

Резюме: чтобы заставить формулу всегда ссылаться на ячейку B10, используйте ДВССЫЛ("В10").


Прокомментировать