Трехмерный поиск по нескольким листам (ВПР 3D)

Продолжая развивать идею ВПР 2D, давайте рассмотрим решение задачи поиска не в двух, а в трех измерениях, когда к нахождению нужной строки и столбца добавляется еще и лист. Рассмотрим следующий пример. Допустим, у нас есть несколько листов по городам с данными продаж по товарам (строки) и магазинам (столбцы):

vlookup3d-1.png

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

На отдельном листе создана форма ввода, куда пользователь с помощью выпадающих списков вводит желаемый город, товар и магазин в желтые ячейки D5, D7 и D9

vlookup3d-2.png

Содержимое выпадающих списков автоматически подгружается в желтые ячейки из трех синих "умных" таблиц справа (как это реализовать было описано в этой статье). Необходимо в зеленой ячейке D11 получить сумму, соответствующую заданному товару и магазину, причем с нужного листа.

Для решения нам потребуется всего три функции:

  • ПОИСКПОЗ(искомое_значение; массив; тип_поиска) – ищет заданное значение в диапазоне (строка или столбец) и выдает порядковый номер ячейки, где оно было найдено. Нам эта функция поможет найти порядковые номера строки и столбца в таблице, где расположено нужное число. Для примера, формула:
    =ПОИСКПОЗ("Альфа";A2:G1;0)
    … вычислит номер столбца в таблице, где расположен магазин Альфа. Последний аргумент этой функции (0) означает, что нам нужен точный поиск.
  • ИНДЕКС(диапазон; номер_строки; номер_столбца) – выбирает значение из диапазона по номеру строки и столбца. Так, например, формула:
    =ИНДЕКС(B2:G9;3;2)
    … выдаст нам содержимое ячейки в 3-й строке 2-го столбца из диапазона B2:G9.
  • ДВССЫЛ(адрес_как_текст) – превращает адресную строку в виде текста в настоящий адрес. Причем адрес запросто может склеиваться из фрагментов с помощью оператора сцепки &. Например, формула:
    =ДВССЫЛ(A1&"!B3")
    … берет имя листа из ячейки A1, приклеивает к нему восклицательный знак-разделитель и адрес ячейки B3. Если в ячейке A1 будет лежать слово Москва, то на выходе мы получим ссылку Москва!B3, т.е. содержимое ячейки B3 с листа Москва.

Теперь сводим все в единое целое для решения нашей задачи:

vlookup3d-3.png

Единственный оставшийся нюанс в том, что по синтаксису Excel, если в именах листов есть пробел, то их нужно дополнительно заключать в апострофы (одинарные кавычки), т.е. ссылка на ячейку A1 на листе Нижний Новгород, например, должна выглядеть так:

=Нижний Новгород!A1

Таким образом для универсальности нужно добавить апострофы и к нашей формуле:

vlookup3d-4.png

Ссылки по теме



14.10.2015 12:38:11
Добрый день, а как можно "склеить" адрес, если таблица в другой книге? (брать наименование листа из ячейки-ДВССЫЛ("'[движения октябрь 2015  Microsoft Excel.xls]13.10'!$B$9:$K$64") ?
14.10.2015 14:20:11
Вопрос снимаю,разобралась. правда, если файл с таблицей закрыт ячейка выдает #ССЫЛКА!
23.10.2015 07:08:18
Добрый день. Очень крутая.
В примере города заменил на месяц год (соответсвенно и наименование листов такие же). Формат ячеек даты Март 2012В итоге формула не вычисляется, пишет #ССЫЛКА! Как победить -  не знаю.



Скрины этапов вычисления прикреплены.
Помогите, плиииз.

Ps. добавил после месяца добавил нижнее подчеркивание (Январь_2015) все заработало.
23.10.2015 08:38:19
В формуле вместо D5 укажите ТЕКСТ(D5;"ММММ ГГГГ") и будет вам СЧАСТЬЕ)))
23.10.2015 09:12:14
Спасибо! Очень помогли!
28.10.2015 10:11:26
Добрый день, подскажите пожалуйста, а как сделать так, чтобы считалась сумма продаж по всем листам, т.е. без выбора города
29.01.2016 19:27:16
А как сделать так, чтобы при добавлении или удалении листов выпадающие списки с городами изменялись?
26.03.2017 20:27:33
Использовать функцию ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ, как это описано в этой статье, для получения динамического оглавления. И затем сослаться в выпадающем списке на полученный диапазон с именами листов.