Трехмерный поиск по нескольким листам (ВПР 3D)
Продолжая развивать идею ВПР 2D, давайте рассмотрим решение задачи поиска не в двух, а в трех измерениях, когда к нахождению нужной строки и столбца добавляется еще и лист. Рассмотрим следующий пример. Допустим, у нас есть несколько листов по городам с данными продаж по товарам (строки) и магазинам (столбцы):
Причем в таблицах товары и магазины перемешаны, т.е. их последовательность различается. Количество строк и столбцов тоже может быть разным.
На отдельном листе создана форма ввода, куда пользователь с помощью выпадающих списков вводит желаемый город, товар и магазин в желтые ячейки D5, D7 и D9
Содержимое выпадающих списков автоматически подгружается в желтые ячейки из трех синих "умных" таблиц справа (как это реализовать было описано в этой статье). Необходимо в зеленой ячейке D11 получить сумму, соответствующую заданному товару и магазину, причем с нужного листа.
Для решения нам потребуется всего три функции:
- ПОИСКПОЗ(искомое_значение; массив; тип_поиска) – ищет заданное значение в диапазоне (строка или столбец) и выдает порядковый номер ячейки, где оно было найдено. Нам эта функция поможет найти порядковые номера строки и столбца в таблице, где расположено нужное число. Для примера, формула:
=ПОИСКПОЗ("Альфа";A2:G1;0)
… вычислит номер столбца в таблице, где расположен магазин Альфа. Последний аргумент этой функции (0) означает, что нам нужен точный поиск.
- ИНДЕКС(диапазон; номер_строки; номер_столбца) – выбирает значение из диапазона по номеру строки и столбца. Так, например, формула:
=ИНДЕКС(B2:G9;3;2)
… выдаст нам содержимое ячейки в 3-й строке 2-го столбца из диапазона B2:G9.
- ДВССЫЛ(адрес_как_текст) – превращает адресную строку в виде текста в настоящий адрес. Причем адрес запросто может склеиваться из фрагментов с помощью оператора сцепки &. Например, формула:
=ДВССЫЛ(A1&"!B3")
… берет имя листа из ячейки A1, приклеивает к нему восклицательный знак-разделитель и адрес ячейки B3. Если в ячейке A1 будет лежать слово Москва, то на выходе мы получим ссылку Москва!B3, т.е. содержимое ячейки B3 с листа Москва.
Теперь сводим все в единое целое для решения нашей задачи:
Единственный оставшийся нюанс в том, что по синтаксису Excel, если в именах листов есть пробел, то их нужно дополнительно заключать в апострофы (одинарные кавычки), т.е. ссылка на ячейку A1 на листе Нижний Новгород, например, должна выглядеть так:
=’Нижний Новгород’!A1
Таким образом для универсальности нужно добавить апострофы и к нашей формуле:
Ссылки по теме
- Что такое функция ВПР (VLOOKUP) и как с ее помощью подставить значения из одной таблицы в другую
- Как реализовать поиск по двум измерениям (ВПР 2D)
- Динамический выпадающий список с наполнением
В примере города заменил на месяц год (соответсвенно и наименование листов такие же). Формат ячеек даты Март 2012В итоге формула не вычисляется, пишет #ССЫЛКА! Как победить - не знаю.
Скрины этапов вычисления прикреплены.
Помогите, плиииз.
Ps. добавил после месяца добавил нижнее подчеркивание (Январь_2015) все заработало.