Трюк №75. Использование функции ВПР (VLOOKUP) в нескольких таблицах

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

Иногда нужно использовать одну формулу ВПР (VLOOKUP) для поиска в различных таблицах существующих данных. Один из способов, как это можно сделать, — вложить несколько функций ВПР (VLOOKUP), приказав им проводить поиск в определенной таблице в зависимости от числа, которое вы вводите в определенной ячейке. Например: =IF(Al=1;VLOOKUP(B1;Table1;2;FALSE);IF(A1=2;VLOOKUP(B1;Table2;2;FALSE),"")), В русской версии Excel =ЕСЛИ(А1=1,ВПР(В1;Table1;2;ЛОЖЬ);ЕСЛИ(А1=2;ВПР(В1;Table2;2;ЛОЖЬ),"")). В этой формуле вы говорите функции ВПР (VLOOKUP) искать в именованном диапазоне Table1, если ячейка А1 содержит число 1, и в именованном диапазоне Table2, если ячейка А1 содержит число 2.

Как можно понять, если использовать больше двух вложенных функций ЕСЛИ (IF), эта формула станет очень большой и неудобной. В следующей формуле, например, используется только пять вложенных функций, но она уже устрашает!

=IF(Al=1;VLOOKUP(B1;Table1;2;FALSE);IF(A1=2,VLOOKUP(B1;Table2;2;FALSE);IF(A1=3;VLOOKUP(B1;Table2;3;FALSE);IF(A1=4;VLOOKUP(B1;Table4;2;FALSE);IF(A1=5;VLOOKUP(B1;Table5;2;FALSE);""))))).

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

Предположим, у вас производство стеклопакетов и у вас есть 12 разных таблиц с данными о компонентах в одной электронной таблице; каждая таблица представляет один из месяцев года. Каждая таблица включает два столбца и содержит имена пяти сотрудников и пять соответствующих сумм. Имя каждой таблицы соответствует представляемому ею месяцу, то есть данные для января помещены в именованный диапазон January, данные для февраля содержатся в именованном диапазоне February и т. д.

Выделите ячейку А1. Затем выберите команду Данные → Проверка (Data → Validation) и на вкладке Параметры (Settings) в поле Тип данных (Allow) выберите Список (List). В поле Источник (Source) введите названия месяцев по-английски, отделив их друг от друга запятыми. Важно, чтобы название именованного диапазона для каждой таблицы соответствовало названиям месяцев в списке проверки. Щелкните на кнопке ОК.

Выделите ячейку В1 и настройте список проверки, как вы это сделали только что. На этот раз укажите имена всех сотрудников. Если имена сотрудников слишком большие, в качестве источника укажите ссылку на диапазон ячеек, содержащий эти имена. Щелкните на кнопке ОК.

В ячейке А2 введите следующую формулу =VLOOKUP($B$1;INDIRECT($A$1);2;FALSE), в русской версии Excel =ВПР($В$1;ДВССЫЛ($А$1);2;ЛОЖЬ). Выберите нужный месяц из списка в ячейке А1 и нужное имя сотрудника в списке в ячейке В1, и формула в ячейке А2 вернет соответствующую сумму для этого сотрудника для данного месяца.
[stextbox id=»warning»]У этого способа есть несколько преимуществ. Функция ДВССЫЛ (INDIRECT) используется для чтения содержимого ячейки как адреса диапазона, а не как текста. Так как вы определили 12 диапазонов, каждый из которых представляет месяц года, формула, использующая функцию ДВССЫЛ (INDIRECT), считывает слово January как ссылку на диапазон, а не как текстовую строку. Еще одно преимущество использования формулы с функцией ДВССЫЛ (INDIRECT): можно обойти ограничение Excel, которое позволяет указывать только семь уровней вложенности функций.[/stextbox]