Примеры функций ИНДЕКС и ПОИСКПОЗ по нескольким аспектам Excel

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

Примеры использования функций ИНДЕКС и ПОИСКПОЗ по двум аспектам в Excel

Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких установок. Найти:

  1. Сколько очков заработала команда (поиск по наименованию) сейчас.
  2. Суммарное значение очков, заработанных всеми командами.
  3. Сколько игр было сыграно какой-нибудь командой.

Вид начальной таблицы данных:

Пример 1.

Для удобства в ячейках A11, A13 и A15 сделаны выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих наименования установок. Для сотворения первого выпадающего перечня нужной перейти курсором на ячейку A11. Избрать вкладку «ДАННЫЕ» ленты меню, отыскать секцию с инструментами «Работа с данными» и избрать инструмент «Проверка данных»:

ДАННЫЕ.

В открывшемся диалоговом окне нужно избрать «Тип данных:» — «Перечень» и указать в поле «Источник» спектр ячеек:

Тип данных.

Перебегаем в ячейку A13 и исполняем подобные деяния лишь только указываем другую ссылку на спектр в поле «Источник:»

Источник.

Таковой же выпадающий перечень следует сделать и для ячейки A15.

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

Для получения корректного результата выражение обязано быть выполнено как формула массива. Функция СУММ получает массив ячеек в виде столбца таблицы, номер которого был определен функцией ПОИСКПОЗ по аспекту поиска «Очки» (наименование столбца). Так как в качестве аргумента номер_строки функции ИНДЕКС было передано значение 0, будет возвращен весь столбец.

Интересно почитать:  Как в excel скрыть ненужные столбцы в

ИНДЕКС и ПОИСКПОЗ.

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

Данная формула подобна предшествующей и также обязана употребляться в качестве формулы массива. Благодаря списку, привязанному к ячейке A13, можно просто найти число сыгранных игр для хоть какой команды:

Сколько игр.

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

Сколько очков.

В итоге мы получили значение по 2-м аспектам:

  1. – «Челси».
  2. – «Очки».

Динамическое суммирование спектра ячеек по аспекту в Excel

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

Вид таблицы данных:

Пример 2.

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

Функция СУММ рассчитывает сумму значений, хранящихся в столбце «Очки», при всем этом количество ячеек для расчета быть может задано при помощи аспекта – избранного наименования команды. Функция ИНДЕКС может возвращать не только лишь значение, хранящееся в разыскиваемой ячейке, да и ссылку на эту ячейку. Потому можно применять запись типа E2:ИНДЕКС(…). В итоге выполнения функция ИНДЕКС возвратит ссылку на ячейку, и приведенная выше запись воспримет, к примеру, последующий вид: E2:E4 (если выбрана команда «Манчестер Ю.».

Динамическое суммирование диапазона.

Проверим итог выборочного динамического суммирования столбца таблицы с верху вниз.

Проверим результат.

Сумма чисел в спектре E2:E7 и в ячейке B13 совпадает все ОК.

Подсчет количества рабочих дней в Excel по условию исходной даты

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

Интересно почитать:  Как сложить суммы в столбцах excel

Вид таблицы данных:

Пример 3.

Как видно на рисунке в ячейке A10 опять употребляется выпадающий перечень, сделанный по аналогичной схеме, описанной чуть повыше.

Для определения искомого значения даты используем последующую формулу (формула массива CTRL+SHIFT+ENTER):

1-ая функция ИНДЕКС делает поиск ячейки с датой из спектра A1:I1. Номер строчки указан как 1 для упрощения итоговой формулы. Функция СТОЛБЕЦ возвращает номер столбца с ячейкой, в которой хранится 1-ая запись о часах работы. Выражение «ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>»»» делает поиск первой непустой ячейки для избранной фамилии работника, обозначенной в ячейке A10 (<>”” – не равно пустой ячейке). 2-ой аргумент «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер строчки с избранной фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>»»» — номер позиции значения ИСТИНА в массиве (соответствует номеру столбца), приобретенном в итоге операции сопоставления с пустым значением.

Примеры определения дат для нескольких служащих:

Примеры определения дат.

Для автоматического подсчета количества лишь рабочих дней начиная от даты приема сотрудника на работу, будем применять функцию ЧИСТРАБДНИ:

подсчет количества только рабочих дней.

Для проверки выберем другую фамилию сотрудника из выпадающего перечня в ячейке A9:

выберем другую фамилию.

Индивидуальности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel

Функция ИНДЕКС может возвращать ссылку либо массив значений из 1-го спектра либо нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек либо константу массива. При всем этом следующие аргументы разрешают указать номера интересующих строчки и столбца относительно избранного спектра, также порядковый номер спектра (если спектры ячеек не являются смежными, к примеру, при поиске в разных таблицах). В простом случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строчки и столбца. К примеру, =ИНДЕКС(A2:B5;2;2) возвратит значение, которое хранится в ячейке B3, так как 3-я строчка является 2-ой по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.

Интересно почитать:  Вставить столбец в excel

По мере необходимости можно получить целую строчку либо целый столбец, указав в качестве номера строчки и столбца соответственно значение 0 (нуль). Для вывода приобретенных строчки либо столбца функцию ИНДЕКС нужно применять в качестве формулы массива.

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

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

=ИНДЕКС(спектр; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))

Таковая формула употребляется почаще всего для поиска сходу по двум аспектам.

Ссылка на основную публикацию
Adblock
detector