Поиск значений на нескольких листах с динамическим именем листа

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

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

Допустим, у вас есть четыре рабочих листа, содержащих продажи различных торговых представителей за четыре сезона, как показано на скриншоте ниже, и теперь вам нужно собрать продажи всех торговых представителей с этих четырех листов и вернуть их в сводный рабочий лист. Вот пошаговое решение для вас.

Общая формула

=ВПР(искомое_значение;ДВССЫЛ("'"&лист&"'!"&"диапазон");индекс_столбца;0)

Создайте сводный рабочий лист, который содержит имена торговых представителей и названия рабочих листов, как показано на скриншоте ниже.

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

=ВПР($B3;ДВССЫЛ("'"&C$2&"'!"&"B5:C11");2;0)

Примечания:

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

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

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

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