Поиск данных в нескольких таблицах⁠⁠

Допустим, что у нас имеется четыре одинаковых по конструкции таблицы с данными по заказам товаров в трех странах:

Рассмотрим значение используемых формул по отдельности.

Сперва нужно найти два вспомогательных параметра: номер строки с требуемым товаром и номер столбца со страной. Для этого можно применить две функции ПОИСКПОЗ, используя в качестве основы для поиска любую из наших таблиц, например четвертую:

=ПОИСКПОЗ(W3;Q4:Q8;0)

=MATCH(W3;Q4:Q8;0)

=ПОИСКПОЗ(W4;R3:T3;0)

=MATCH(W4;R3:T3;0);W5)

Дальше используем функцию ИНДЕКС, чтобы извлечь данные из набора нескольких таблиц

=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

В результате в ячейке X4 получаем данные из таблиц:

Можно же обойтись без дополнительных формул и сразу ввести следующую формулу в удобную для вас ячейку:

=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);

ПОИСКПОЗ(W3;Q4:Q8;0);

ПОИСКПОЗ(W4;R3:T3;0);W5)

=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);

MATCH(W3;Q4:Q8;0);

MATCH(W4;R3:T3;0);W5)

Теперь рассмотрим способ поиска данных в таблицах, разных по структуре и размеру, где названия товаров и городов указаны в разном порядке:

Сперва на вкладке «Формулы» - «Диспетчер имен» создадим именованные диапазоны, которые на них указывают:

Далее используем формулу для поиска номера строки товара:

=ПОИСКПОЗ(W4;ИНДЕКС(ДВССЫЛ(W3);0;1);0)

=MATCH(W4;INDEX(INDIRECT(W3);0;1);0)

Для тех, кто предпочитает знать как всё устроено, разберём её подробно))

Во-первых, функция ДВССЫЛ(W3) в данном случае представляет собой ссылку на именованный диапазон 4-го квартала. Прямую ссылку на ячейку с именем W3 использовать нельзя, т.к. Excel будет воспринимать ее как текст. Чтобы превратить текст «Квартал4» в живую ссылку на именованный диапазон «Квартал4», и нужна функция ДВССЫЛ (INDIRECT).

Во-вторых, фрагмент: ИНДЕКС(ДВССЫЛ(W3);0;1)

… представляет собой ссылку на первый столбец именованного диапазона «Квартал4», т.е. на Q3:Q10.

Как это получилось?

Классический вариант использования функции ИНДЕКС на одной двумерной таблице, напомним, предполагает три аргумента: =ИНДЕКС(диапазон; номер_строки; номер_столбца)

В этом случае мы получим содержимое ячейки в указанном диапазоне с пересечения строки и столбца с заданными номерами.

Хитрость в том, что если номер строки равен 0, то ИНДЕКС выдает уже не содержимое ячейки, а ссылку на весь столбец с указанным номером, т.е. на первый столбец именованного диапазона заданного ДВССЫЛ(W3), т.е. на ячейки Q3:Q10.

Ну а затем функция ПОИСКПОЗ (MATCH) ищет в этом диапазоне требуемый товар (Пиво) и возвращает его позицию (4 строка, т.к. пустая Q3 тоже считается).

Аналогично можно найти номер столбца с нужной страной:

=ПОИСКПОЗ(W5;ИНДЕКС(ДВССЫЛ(W3);1;0);0)

=MATCH(W5;INDEX(INDIRECT(W3);1;0);0)

Только в этом случае нулю равен не номер строки, а номер столбца, чтобы получить ссылку на первую строку именованного диапазона «Квартал4», где затем функция ПОИСКПОЗ будет искать «KZ».

И, последним останется вытащить количество заказов функцией ИНДЕКС:

=ИНДЕКС(ДВССЫЛ(W3);X4;X5)

=INDEX(INDIRECT(W3);X4;X5)

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

P.S. Ссылка на файл примера в комментариях.

P.P.S. Уважаемые подписчики, Вас уже больше 7000 человек, это приятно радует и вдохновляет на создание новых постов)) В комментариях Вы писали, что я один из немногих, на кого Вы подписаны, кто-то впервые подписался на меня, а кто-то специально зарегистрировался, чтобы подписаться)) Это очень приятно и я не хочу Вас разочаровывать.

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