Поиск данных в нескольких таблицах
Допустим, что у нас имеется четыре одинаковых по конструкции таблицы с данными по заказам товаров в трех странах:
Рассмотрим значение используемых формул по отдельности.
Сперва нужно найти два вспомогательных параметра: номер строки с требуемым товаром и номер столбца со страной. Для этого можно применить две функции ПОИСКПОЗ, используя в качестве основы для поиска любую из наших таблиц, например четвертую:
=ПОИСКПОЗ(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, стабильно будут выходить по понедельникам. При этом, я хочу наполнить свою страницу смешными гифками, интересными историями и познавательной информацией. Напишите пожалуйста в комментариях, что бы вы хотели видеть в моих постах.
