Динамические таблицы подстановки ВПР, СУММЕСЛИ и ДВССЫЛ с именованными диапазонами devsap
Динамические таблицы подстановки ВПР, СУММЕСЛИ и ДВССЫЛ с именованными диапазонами
ВПР Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце.
СУММЕСЛИ Суммирует ячейки, удовлетворяющие заданному условию.
ДВССЫЛ Определяет ссылку, заданную текстовым значением.
=ВПР(критерий,таблица,столбец,истина)
таблица = ДВССЫЛ(текст, значение ,имя таблицы именованного диапазона)
Связанные формулы
СУММЕСЛИ для нескольких листов
Чтобы позволить динамическую таблицу подстановки, можно использовать косвенные функции с именованными диапазонами внутри ВПР.
В примере, приведенном в Формуле G6-это:
=ВПР(F6;ДВССЫЛ(E6);2;0)
Цель этой формулы в том, чтобы удобно переключать диапазоны в таблице внутри функции поиска. Один способ решает это, чтобы создать именованный диапазон для каждой таблицы, и ссылаться на именованный диапазон внутри ВПР. Однако, если вы просто попробуйте дать ВПР массив таблиц в виде текста (например, «таблица1») формулу не будет работать. Косвенная функция необходима для разрешения текста действительной ссылки.
Как эта формула работает
По сути, это стандартная формула ВПР. Единственным отличием является использование косвенных вернуть действительный массив столбцов таблицы.
В показанном примере два именованных диапазона были созданы: «таблица1» относится к В5:С9, и «таблица2» относится к В11:В15*.
ДВССЫЛ текст в е6 («таблица1») и решает это именованный диапазон Таблица1, которая решает В5:В9, который возвращается в ВПР.
ВПР осуществляет поиск и возвращает значение из таблицы
Файл EXCEL Динамические таблицы подстановки ВПР, СУММЕСЛИ и ДВССЫЛ с именованными диапазонами devsap