|
|
Знаете ли Вы, что ... | |
...нарушения правил форума наказываются. Старайтесь их не нарушать. | |
<< Предыдущий совет - Случайный совет - Следующий совет >> |
Софт Программки, утилиты, операционки - все, что превращает мертвую груду металла в компьютер |
Ответить |
|
Опции темы | Опции просмотра |
19.11.2019 00:46 | #1 | ||
Допустим, что у нас имеется четыре одинаковых по конструкции таблицы с данными по заказам товаров в трех странах:
Рассмотрим значение используемых формул по отдельности. Сперва нужно найти два вспомогательных параметра: номер строки с требуемым товаром и номер столбца со страной. Для этого можно применить две функции ПОИСКПОЗ, используя в качестве основы для поиска любую из наших таблиц, например четвертую: =ПОИСКПОЗ(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 получаем данные из таблиц: [img]https://cs9.pikabu.ru/post_img/big/2019/11/18/10/1574098460119831252.jpg[img] Можно же обойтись без дополнительных формул и сразу ввести следующую формулу в удобную для вас ячейку: =ИНДЕКС((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) [img]https://cs11.pikabu.ru/post_img/big/2019/11/18/10/157409860516552087.jpg[img] Данный способ позволяет находить данные в любых таблицах. Единственное условие: заголовки строк и столбцов (названия товаров и стран) должны быть идентичными во всех таблицах.
__________________
С мужчиной должно быть хорошо, плохо жить я и сама смогу. |
|||
|
Ответить |
|