Диапазон значений поиска на нескольких рабочих листах с динамическим именем листа

Во многих случаях вам может понадобиться собрать данные с нескольких рабочих листов для создания сводки. Объединив функции ВПР и ДВССЫЛ, вы сможете создать формулу, которая динамически ищет нужные значения на разных листах — даже если их имена меняются.

Как выполнить поиск значения на нескольких листах с динамическими именами?

Представьте, что у вас есть четыре рабочих листа с данными о продажах разных продавцов за каждый из четырёх кварталов (см. скриншот ниже). Теперь вам нужно собрать все эти данные на одном сводном листе, чтобы получить полную картину продаж по всем продавцам. Ниже — пошаговое руководство для решения этой задачи.

Универсальная формула

=VLOOKUP(lookup_value,INDIRECT("'"&,sheet&,"'!"&,"range"),col_index,0)

Создайте сводный лист с именами продавцов и названиями соответствующих листов, как показано на скриншоте ниже.

1. Выберите пустую ячейку (в данном случае — C3), вставьте в неё приведённую ниже формулу и нажмите клавишу Enter.

=VLOOKUP($B3,INDIRECT("'"&,C$2&,"'!"&,"B5:C11"),2,0)

Примечания:

• B3содержит имя продавца, по которому вы найдёте соответствующие продажи;
• C2содержит значение, совпадающее с именем листа, из которого вы будете извлекать данные;
• B5:C11— это диапазон таблицы на квартальных листах, включающий как столбец «Имя», так и столбец «Продажи»;
• 0 означает, что функция ВПР вернёт точное совпадение. Если точное совпадение не найдено, функция выдаст ошибку #Н/Д.

2. Затем перетащите маркер заполнения вправо и вниз, чтобы получить все результаты.

Связанная функция

Связанные формулы