Моё меню Общее меню Пользователи Правила форума Все прочитано
Вернуться   uForum.uz > ИКТ и телеком > IT-индустрия > Софт
Знаете ли Вы, что ...
...инструкция по установке аватара описана в Правилах форума.
<< Предыдущий совет - Случайный совет - Следующий совет >>

Софт Программки, утилиты, операционки - все, что превращает мертвую груду металла в компьютер


Ответить

 
Опции темы Опции просмотра
Старый 19.11.2019 00:46   #1  
Аватар для Otkritka
Оффлайн
Дизайн студия открыток
Поздравление пользователей
AKA:Otkritka
Сообщений: 4,528
+ 115  1,979/1,004
– 602  199/178

UzbekistanОтправить сообщение для Otkritka с помощью ICQОтправить сообщение для Otkritka с помощью AIMОтправить сообщение для Otkritka с помощью MSNОтправить сообщение для Otkritka с помощью YahooОтправить сообщение для Otkritka с помощью Skype™LiveJournalМой КругАккаунт на TwitterМой мирFacebook
Information Поиск данных в нескольких таблицах

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



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

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




Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd. Перевод: zCarot
Advertisement System V2.5 By Branden
OOO «Единый интегратор UZINFOCOM»


Новые 24 часа Кто на форуме Новички Поиск Кабинет Все прочитано Вверх