Индекс поиск позиции в excel по двум критериям из 2 х таблиц
Ранее мы уже объясняли новичкам, как использовать базовые функции ВПР (англ. VLOOKUP, аббревиатура расшифровывается как “функция вертикального просмотра”). А опытным пользователям показали несколько формул посложнее.
А в этой статье мы постараемся дать информацию про другой метод работы с вертикальным поиском.
У Вас может возникнуть вопрос: «Зачем это нужно?». А нужно это для того, чтобы показать все возможные способы поиска. К тому же, многочисленные ограничения ВПР часто мешают получить нужный результат. В этом отношении ИНДЕКС( ) ПОИСКПОЗ( ) гораздо функциональнее и разнообразнее, а также в них меньшее количество ограничений.
Основы ИНДЕКС ПОИСКПОЗ
Так как цель этого руководства — показать, чем хороша эта функция, мы рассмотрим базовую информацию касательно принципов её работы. И покажем примеры, а также рассмотрим почему, она лучше ВПР().
Функция ИНДЕКС: синтаксис и применение
Эта функция помогает в поиске требуемого значения среди указанных областей поиска, основываясь на номере колонки или строки. Синтаксисы:
=ИНДЕКС(массив, номер строки, номер столбца):
- массив – область в которой будет проходить поиск;
- номер строки – номер строки, которую нужно искать в указанном массиве. Если номер строки неизвестен, нужно указать номер столбца;
- номер столбца – номер столбца, который надо найти в указанном массиве. Если значение неизвестно, требуется указать номер строки.
Пример простой формулы:
=ИНДЕКС(А1:С10,2,3)
Функция будет производить поиск в диапазоне от А1 до С10. Цифры показывают, из какой строки (2) и колонки (3) показать искомое значение. Результатом будет ячейка С2.
Довольно просто, верно? Но когда вы работаете с настоящими документами, вы вряд ли будете располагать информацией касательно номеров колонок или ячеек. Именно для этого и существует функция ПОИСКПОЗ().
Функция ПОИСКПОЗ: синтаксис и использование
Функция ПОИСКПОЗ() ищет нужное значение и показывает его примерный номер в указанной области поиска.
Синтаксис Поискпоз() выглядит так:
=ПОИСКПОЗ(искомое значение, просматриваемый массив, тип сопоставления)
- искомое значение – цифра или текст, который нужно найти;
- просматриваемый массив – область, где будет проходить поиск;
- тип сопоставления – уточняет, искать точное значение или ближайшие к нему значения:
- 1 (или значение не указано) – выдает самое большое значение, которое равно или меньше значения, которое было указано;
- 0 – показывает точное совпадение с искомым значением. В комбинации ИНДЕКС() ПОИСКПОЗ() вам практически всегда понадобится точное совпадение, так что прописываем 0;
- -1 – показывает наименьшее значение, которое больше или равно указанному в формуле. Сортировка проводится по убыванию.
Например, в диапазоне В1:В3 прописаны Нью-Йорк, Париж, Лондон. Указанная ниже формула покажет номер 3, потому что Лондон является третьим по списку:
=ПОИСКПОЗ(Лондон,В1:В3,0)
Как работать с функцией ИНДЕКС ПОИСКПОЗ
Вы наверняка уже начали понимать, по какому принципу строится совместная работа этих функций. Если вкратце, то ИНДЕКС() проводит поиск нужного значения среди указанных строк и колонок. А ПОИСКПОЗ() показывает номера этих значений:
=ИНДЕКС(колонка из которой возвращается значение, ПОИСКПОЗ(значение для поиска, колонка в которой искать, 0))
Все еще сложно понять, как это работает? Может быть на примере получится объяснить лучше. Предположим, у Вас есть список мировых столиц и численность их населения:
Для того, чтобы выяснить размеры населения какой-то определенной столицы, например, столицы Японии, воспользуемся такой формулой:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(Япония, А2:А10,0))
- Функция ПОИСКПОЗ() ищет значение – «Япония» в массиве А2:А10 и выдает цифру 3, потому что Япония — это третье значение по списку.
- Эта цифра идет в « номер строки » в формуле ИНДЕКС() и указывает функции на необходимость вывести значение из этой строки.
Таким образом, вышеуказанная формула становится стандартной формулой ИНДЕКС(С2:С10,3). Формула проводит поиск в ячейках от С2 до С10 и выдает данные из третьей ячейки этого диапазона, то есть С4, потому что отсчет начинается со второй строки.
Не хотите прописывать название города в формуле? Тогда напишите его в любой ячейке, скажем, F1, и используйте её как ссылку в формуле ПОИСКПОЗ(). И у вас получится динамическая формула поиска:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Важно! Количество строк в массиве ИНДЕКС() должно быть такое же, как и количество строк в рассматриваемом массиве в ПОИСКПОЗ(), иначе вы получите неправильный результат.
Подождите-ка, почему бы просто не использовать формулу ВПР()?
=ВПР(F1, A2:C10, 3, Ложь)
Какой смысл тратить время в попытках разобраться во всех этих сложностях ИНДЕКС ПОИСКПОЗ?
В этом случае нет разницы, какую функцию использовать. Это просто пример, чтобы был понятен принцип совместной работы функций ИНДЕКС() и ПОИСКПОЗ(). Другие примеры покажут, на что способны эти функции в ситуациях, когда ВПР оказывается бессильным.
ИНДЕКС ПОИСКПОЗ или ВПР
Решая, какую использовать формулу для поиска, многие соглашаются что ИНДЕКС() и ПОИСКПОЗ() значительно лучше ВПР. Однако, многие люди все еще пользуются ВПР(). Во-первых, ВПР() проще, во-вторых, пользователи до конца не понимают все плюсы работы с ИНДЕКС() и ПОИСКПОЗ(). Не имея этих знаний, никто не согласится тратить свое время на изучение сложной системы.
Ниже мы приведем ключевые преимущества ИНДЕКС() и ПОИСКПОЗ() над ВПР():
- Безопасное добавление или удаление колонок. Формула ВПР() показывает неправильные результаты при удалении или добавлении колонок, потому что ВПР() нуждается в точных указаниях номера колонки для успешного поиска. Естественно, при добавлении или удалении колонок, меняются и их номера.
А в формуле ИНДЕКС() и ПОИСКПОЗ() указывается диапазон колонок, а не отдельные колонки. В результате, можно безопасно добавлять и удалять колонки, без необходимости каждый раз обновлять формулу.
Влияние ВПР() на производительность будет особенно заметным, если ваш рабочий лист содержит большое количество формул вроде ВПР() и СУММ(). Для анализа каждого значения в массиве требуются отдельные проверки функций ВПР(). Так что Excel приходится обрабатывать огромное количество информацию, и это значительно замедляет работу.
Примеры формул
Мы уже выяснили полезность этих функций, так что можно перейти к самой интересной части: к применению знаний на практике.
Формула для поиска справа налево
Как уже было сказано, ВПР не может проводить такую форму поиска. Так что, если нужные значения расположены не в самой левой колонке, ВПР() не выдаст результат. Функции ИНДЕКС() и ПОИСКПОЗ() более универсальны, и для их работы расположение значений не играет большой роли.
Для примера, мы добавим колонку ранга в левую часть нашей таблицы и попробуем разобраться, какой ранг по численности населения занимает столица России.
В ячейке G1 прописываем значение, которое нужно найти, а потом используем указанную ниже формулу для поиска в диапазоне С1:С10 и возвращаем соответствующее значение из А2:А10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Подсказка. Если вы планируете использовать эту формулу для нескольких ячеек, убедитесь, что вы зафиксировали диапазоны с помощью абсолютной адресации (например, $А$2: $А$10 и $С$2:4С$10 ).
ИНДЕКС ПОИСКПОЗ ПОИСКПОЗ для поиска в колонках и строках
В вышеуказанных примерах мы использовали эти функции как замену для ВПР(), чтобы вернуть значения из заранее определенного диапазона строк. Но что, если вам нужно провести матричный или двусторонний поиск?
Звучит сложно, но формула для таких расчетов похожа на стандартную формулу ИНДЕКС() ПОИСКПОЗ(), но только с одним различием: формулу ПОИСКПОЗ() нужно использовать дважды. Первый раз, чтобы получить номер строки, и второй раз — чтобы получить номер колонки:
=ИНДЕКС(массив, ПОИСКПОЗ(вертикальное искомое значение, искомая колонка, 0), ПОИСКПОЗ(горизонтальное искомое значение, искомая строка,0))
Посмотрим на таблицу внизу и попробуем составить формулу ИНДЕКС() ПОИСКПОЗ() ПОИСКПОЗ() для того, чтобы отобразить демографию в определенной стране за выбранный год.
Целевая страна указана в ячейке G1 (вертикальное искомое значение), а целевой год — в ячейке G2 (горизонтальное искомое значение). Формула будет выглядеть так:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Как работает эта формула
Как и с любыми другими сложными формулами, в них легче разобраться, разбив их на отдельные уравнения. И тогда вы сможете понять, что делает каждая индивидуальная функция:
- ПОИСКПОЗ(G1,A2:A11,0) – ищет значение (G1) в диапазоне A2:A11 и показывает номер этого значения, в нашем случае это 2;
- ПОИСКПОЗ(G2,B1:D1,0) – ищет значение (G2) в диапазоне B1:D1. В данном случае результат был 3.
Найденные номера строк и колонок отправляются в соответствующее значение в формуле ИНДЕКС():
=ИНДЕКС(B2:D11,2,3)
В результате, имеем значение, которое находится в ячейке на пересечении 2 строки и 3 колонки в диапазоне B2:D11. И формула показывает искомое значение, которое находится в ячейке D3.
Поиск по нескольким условиям с помощью ИНДЕКС и ПОИСКПОЗ
Если вы читали наше руководство по ВПР(), вы наверняка пробовали формулу поиска по нескольким условиям. Но этот способ поиска имеет одно значительное ограничение – необходимость добавлять вспомогательную колонку.
Но хорошая новость заключается в том, что с помощью ИНДЕКС() и ПОИСКПОЗ() можно проводить поиск по нескольким условиям без необходимости редактировать или менять вашу рабочую таблицу.
Вот общая формула поиска по нескольким условиям для ИНДЕКС() ПОИСКПОЗ():
Заметка: эту формулу нужно использовать вместе с сочетанием клавиш CTRL+SHIFT+ENTER.
Предположим, вам нужно найти искомое значение, основанное на 2 условиях: Покупатель и Продукт.
Для этого нужна следующая формула:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
В этой формуле С2:С10 — диапазон в котором будет проходить поиск, F1 – это условие, А2:А10 — это диапазон для сравнения условия, F2 – условие 2, В2:В10 – диапазон для сравнения условия 2.
Не забывайте в конце работы с формулой нажать сочетание CTRL+SHIFT+ENTER – Excel автоматически закроет формулу фигурными скобками, как указано в примере:
Если же вы не хотите использовать формулу массива для вашей работы, то добавьте еще один ИНДЕКС() к формуле и нажмите ENTER, выглядеть это будет как на примере:
Как работают эти формулы
Эта формула работает по тому же принципу, что и стандартная формула ИНДЕКС() ПОИСКПОЗ(). Для того, чтобы искать по нескольким условиям, вы просто создаете несколько Ложных и Истинных условий, которые представляют правильные и неправильные индивидуальные условия. А потом эти условия распространяются на все соответствующие элементы массива. Формула конвертирует Ложные и Истинные аргументы в 0 и 1 соответственно и выводит массив, в котором 1 — это соответствующие условию значения, которые были найдены в строке. ПОИСКПОЗ() найдет первое соответствующее 1 значение и передаст его в формулу ИНДЕКС(). А она, в свою очередь, возвратит уже искомое значение в указанную строку из нужной колонки.
Формула без массива зависит от способности ИНДЕКС() самостоятельно с ними справляться. Второй ИНДЕКС() в формуле соответствует ложному значению (0), так что он передает весь массив с такими значениями в формулу ПОИСКПОЗ().
Это довольно пространное объяснение логики, по которой работает эта формула. Для более детальной информации прочтите статью « ИНДЕКС ПОИСКПОЗ с несколькими условиями ».
СРЗНАЧ, МАКС и МИН в ИНДЕКС и ПОИСКПОЗ
В Excel есть свои специальные функции для поиска средних, максимальных и минимальных значений. Но что, если нужно получить данные из ячейки, связанной с этими значениями? В этом случае СРЗНАЧ, МАКС и МИН нужно использовать вместе с ИНДЕКС и ПОИСКПОЗ.
ИНДЕКС ПОИСКПОЗ и МАКС
Чтобы найти наибольшее значение в колонке D и показать его в колонке C, используем формулу:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
ИНДЕКС ПОИСКПОЗ и МИН
Чтобы обнаружить наименьшее значение в колонке D и вывести его в колонке С, используется такая формула:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
ИНДЕКС ПОИСКПОЗ и СРЗНАЧ
Чтобы найти среднее значение в колонке D и вывести это значение в С:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
В зависимости от того, как записаны ваши данные, в качестве третьего аргумента для ПОИСКПОЗ() используется либо 1, либо 0, либо -1:
- если колонки отсортированы по возрастанию – ставьте 1 (тогда формула рассчитает максимальное значение, которое меньше или равняется среднему значению);
- если сортировка по убыванию, тогда -1 (формула выведет минимальное значение, которое больше или равняется среднему);
- если же массив поиска содержит значение, которое в точности равняется среднему, тогда ставьте 0.
В нашем примере, численность населения отсортирована в убывающем порядке, поэтому мы ставим -1. И в результате получаем Токио, так как значение населения (13,189, 000) самое близкое к среднему значению (12,269,006).
ВПР() тоже может проводить такие расчеты, но только как формула массива: ВПР со СРЗНАЧ, МИН и МАКС .
ИНДЕКС ПОИСКПОЗ и ЕСНД/ЕСЛИОШИБКА
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Если же хотите отловить все ошибки, тогда кроме ЕСНД можно использовать ЕСЛИОШИБКА :
=ЕСЛИОШИБКА(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)), «Что-то пошло не так!»)
Но помните, что маскировать ошибки таким образом – не лучшая идея, ведь стандартные ошибки сообщают о нарушениях в формуле.
Надеемся, наше руководство пользования функцией ИНДЕКС ПОИСКПОЗ() оказалось полезным.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Двухмерная таблица — это прямоугольный диапазон ячеек, то есть неразрывный диапазон состоящий из нескольких строк и столбцов. Для выборки значений из двухмерных таблиц легко и удобно использовать эффективную формулу из комбинации функции ИНДЕКС и ПОИСКПОЗ. Главным недостатком такой формулы является тот факт, то ее можно применять только для двухмерных прямоугольных таблиц в неразрывном диапазоне ячеек. Но на своем поле эта формула чувствует себя как рыба в воде и создает шикарные инструменты для поиска по нескольким условиям пользователя.
Выборка значений формулой ИНДЕКС и ПОИСКПОЗ с несколькими условиями
Ниже на рисунке представленная таблица с данными о продажах по регионам за 4 года. Каждая строка представляет собой отдельную область региона, а каждый столбец – отдельный год. Допустим пользователь должен по двум условиям сделать выборку значений из таблицы:
В результате должен получить значение ячейки из соответственной строки и столбца таблицы. Как в таблице Пифагора:
Большинству пользователей Excel хорошо известна формула из функций ИНЕДКС и ПОИСКПОЗ. В отличие от других формул, здесь используется сразу две функции ПОИСКПОЗ во втором и третьем аргументе функции ИНДЕКС. Так как поиск выполняется по двум условиям. В третьем аргументе «Номер столбца» функции ИНДЕКС нет постоянного числа (константы), а вместо него функция ПОИСКПОЗ, которая динамически изменяет значение.
Поисковая функция ПОИСКПОЗ возвращает позицию найденного значения в списке. На рисунке сейчас выбран регион «Северный», значит функция возвращает значение 3, так как этот регион находится на третьем месте в списке. Это же число на данный момент является значением второго аргумента функции ИНДЕКС. Год 2011 найден в строке заголовка таблицы. Так как это вторая позиция в списке, то функция ПОИСКПОЗ возвращает число 2 – для третьего аргумента. Функция ИНДЕКС на основе чисел 3 и 2 возвращаемых через функцию ПОИСКПОЗ возвращает соответственное значение указанным критериям выборки пользователем.
Альтернативная формула для ИНДЕКС и ПОИСКПОЗ по нескольким условиям
Можно существенно расширить возможности выше приведенной формулы. Данные по продажам можно выводить из таблицы сразу по нескольким условиям выборки значений. Так, чтобы пользователь мог указать 1 вариант из четырех условий:
- Указаны регион и год (как в предыдущем примере).
- Указывать только регион.
- Указывать только год.
- Вообще ничего не указывать ни одного критерия выборки.
Теперь будет выполняться новая формула ИНДЕКС и ПОИСКПОЗ с несколькими условиями. Измененная формула и все равно должна предоставлять правильные итоговые результаты ни в чем не ограничивая своего пользователя.
Например, если не будет указан ни один из критериев выборки данных о продажах, тогда формула будет подразумевать, что пользователю необходимая общая сумма продаж за все года по всем регионам в итоговом значении. Другими словами, если не указывать пользовательские критерии условий выборки, формула возвращает общую сумму всех чисел в таблице:
Общая структура модифицированной формулы такая же, как и в предыдущем примере. Изменено только несколько деталей. Диапазон, определенный функцией ИНДЕКС, теперь охватывает и строку листа №9 и столбец F. Так же модифицированы обе функции ПОИСКПОЗ и дополнительно расположены в аргументах функций ЕСЛИОШИБКА. Эта же функция в формуле позволяет возвращать общую сумму чисел по строкам или по столбцам благодаря охвату итоговых значений в строке B9:F9 и в столбце F3:F9.
Заменителем в функции ЕСЛИОШИБКА выступает функция СЧЕТЗ. Данная функция позволяет посчитать количество непустых ячеек, которые содержат как числа, так и текстовое содержимое. То есть возвращает номер последней строки или столбца в диапазоне таблицы. В принципе можно обойтись и без этой функции вписав неизменяемые числа количества строк и столбцов вручную. Но если в будущем планируется добавлять в таблицу новые строки и столбцы формула будет выдавать неточные итоговые результаты. А так благодаря функции СЧЕТЗ формула будет сама динамически подсчитывать общее количество строк и столбцов при заполнении дополнительными данными таблицы отчета по продажам.
Одним из основных способов поиска данных в таблицах Excel является функция ВПР, однако она имеет массу недостатков, и зачастую пользователи испытывают сложности при ее использовании. Связка функций ИНДЕКС и ПОИСКПОЗ открывает более широкие возможности для поиска данных в одной и даже нескольких таблицах сразу, на что неспособна ВПР.
Примеры использования функций ИНДЕКС и ПОИСКПОЗ по двум критериям в Excel
Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:
- Сколько очков заработала команда (поиск по названию) на данный момент.
- Суммарное значение очков, заработанных всеми командами.
- Сколько игр было сыграно какой-либо командой.
Вид исходной таблицы данных:
Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:
В открывшемся диалоговом окне необходимо выбрать «Тип данных:» - «Список» и указать в поле «Источник» диапазон ячеек:
Переходим в ячейку A13 и выполняем аналогичные действия только лишь указываем другую ссылку на диапазон в поле «Источник:»
Такой же выпадающий список следует создать и для ячейки A15.
Для подсчета общего количества очков в ячейке B11 используем формулу:
Для получения корректного результата выражение должно быть выполнено как формула массива. Функция СУММ получает массив ячеек в виде столбца таблицы, номер которого был определен функцией ПОИСКПОЗ по критерию поиска «Очки» (наименование столбца). Поскольку в качестве аргумента номер_строки функции ИНДЕКС было передано значение 0, будет возвращен весь столбец.
Количество сыгранных игр для каждой команды может быть рассчитано как сумма выигранных, сыгранных вничью и проигранных игр. Используем следующую формулу:
Данная формула аналогична предыдущей и также должна использоваться в качестве формулы массива. Благодаря списку, привязанному к ячейке A13, можно легко определить число сыгранных игр для любой команды:
Для определения количества очков используем формулу ИНДЕКС, в которой оба аргумента, указывающие номер строки и столбца, будут принимать значения, возвращаемые функцией ПОИСКПОЗ:
В результате мы получили значение по 2-м критериям:
Динамическое суммирование диапазона ячеек по критерию в Excel
Пример 2. Используя таблицу из предыдущего примера определить суммарное количество заработанных очков несколькими командами (задается опционально).
Вид таблицы данных:
Искомое значение может быть найдено с помощью следующей формулы:
Функция СУММ рассчитывает сумму значений, хранящихся в столбце «Очки», при этом количество ячеек для расчета может быть задано с помощью критерия – выбранного названия команды. Функция ИНДЕКС может возвращать не только значение, хранящееся в искомой ячейке, но и ссылку на эту ячейку. Поэтому можно использовать запись типа E2:ИНДЕКС(…). В результате выполнения функция ИНДЕКС вернет ссылку на ячейку, и приведенная выше запись примет, например, следующий вид: E2:E4 (если выбрана команда «Манчестер Ю.».
Проверим результат выборочного динамического суммирования столбца таблицы с верху вниз.
Сумма чисел в диапазоне E2:E7 и в ячейке B13 совпадает все ОК.
Подсчет количества рабочих дней в Excel по условию начальной даты
Пример 3. В таблице табеля рабочего времени хранятся данные о недавно принятых сотрудниках фирмы. Определить, сколько рабочих дней на текущий момент отработал любой из новых недавно принятых сотрудников фирмы.
Вид таблицы данных:
Как видно на рисунке в ячейке A10 снова используется выпадающий список, созданный по аналогичной схеме, описанной выше.
Для определения искомого значения даты используем следующую формулу (формула массива CTRL+SHIFT+ENTER):
Первая функция ИНДЕКС выполняет поиск ячейки с датой из диапазона A1:I1. Номер строки указан как 1 для упрощения итоговой формулы. Функция СТОЛБЕЦ возвращает номер столбца с ячейкой, в которой хранится первая запись о часах работы. Выражение «ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>""» выполняет поиск первой непустой ячейки для выбранной фамилии работника, указанной в ячейке A10 (<>”” – не равно пустой ячейке). Второй аргумент «ПОИСКПОЗ(A10;A1:A6;0)» возвращает номер строки с выбранной фамилией, а «ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B1:I6;ПОИСКПОЗ(A10;A1:A6;0);0)<>""» - номер позиции значения ИСТИНА в массиве (соответствует номеру столбца), полученном в результате операции сравнения с пустым значением.
Примеры определения дат для нескольких сотрудников:
Для автоматического подсчета количества только рабочих дней начиная от даты приема сотрудника на работу, будем использовать функцию ЧИСТРАБДНИ:
Для проверки выберем другую фамилию сотрудника из выпадающего списка в ячейке A9:
Особенности работы с формулами ИНДЕКС И ПОИСКПОЗ в Excel
Функция ИНДЕКС может возвращать ссылку или массив значений из одного диапазона или нескольких несмежных диапазонов, принимая на вход ссылку на области ячеек или константу массива. При этом последующие аргументы позволяют указать номера интересующих строки и столбца относительно выбранного диапазона, а также порядковый номер диапазона (если диапазоны ячеек не являются смежными, например, при поиске в различных таблицах). В простейшем случае функция ИНДЕКС возвращает значение, хранящееся в ячейке на пересечении строки и столбца. Например, =ИНДЕКС(A2:B5;2;2) вернет значение, которое хранится в ячейке B3, поскольку третья строка является второй по счету относительно ячейки A2, а столбец B:B является вторым относительно столбца A:A.
При необходимости можно получить целую строку или целый столбец, указав в качестве номера строки и столбца соответственно значение 0 (нуль). Для вывода полученных строки или столбца функцию ИНДЕКС необходимо использовать в качестве формулы массива.
Поскольку ПОИСКПОЗ возвращает относительную позицию элемента в диапазоне, то есть, номер строки или столбца, эта функция может быть использована как один или сразу два аргумента функции:
=ИНДЕКС(диапазон; ПОИСКПОЗ(аргументы); ПОИСКПОЗ(аргументы))
Такая формула используется чаще всего для поиска сразу по двум критериям.
Функция INDEX имеет две формы. Первая, содержит 3 аргумента для ввода. Однако существует функция ИНДЕКС с 4 аргументами, где вместо одной таблицы можно указать несколько таблиц. Такой подход позволяет одновременную работу сразу по нескольким таблицам.
Работа функции ИНДЕКС и ПОИСКПОЗ по нескольким условиям
В качестве последнего аргумента необходимо выбрать номер области, к которой ссылается формула. Не пресмыкающиеся друг к другу области должны быть заключены в круглые скобки, а таблицы должны быть отделены друг от друга, так как и аргументы в Excel, то есть с использованием точки с запятой.
В этой версии функции ИНДЕКС можно установить другую функцию. Для примера я сделал три таблицы с ценами из разных магазинов. Диапазонам данных я дал именам, соответствующие их названиям, используя поле «Имя». Теперь диапазоны называются Магазин1(B2:E5), Магазин2(B8:E11) и Магазин3(B14:E17).
Первый аргумент определен теперь осталось написать формулы для остальных аргументов.
Формула из комбинации функций ВЫБОР, ИНДЕКС и ПОИСКПОЗ
С помощью функции ВЫБОР, я создал виртуальную таблицу данных, состоящую из 3 ячеек с именами магазинов, т.е. A1, A7, A13 и я использовал ее как массив для функции ПОИСКПОЗ. Благодаря этому, после ввода названия выбранного магазина в ячейку G1, функция ИНДЕКС относится к диапазону с ценами указанного мной магазина.
Последней модификацией формулы является добавление функции ПОИСКПОЗ вместо индекса строки и столбца, для того чтобы эти значения были переменными, а не постоянными. В ячейках G2 и G3 я ввожу пример названия продукта и веса. Теперь, изменяя значения в ячейках от G1 до G3, в ячейке H1 отображается цена, выбранная на основании трех аргументов.
Этот пример должен был показать, как работает функция ИНДЕКС, а также какие возможности предоставляет использование вложения функций и массивов.
Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза "внешность обманчива" работает на 100%. Одна из наиболее многогранных и полезных - функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.
Вариант 1. Извлечение данных из столбца по номеру ячейки
Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:
=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )
Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH) , которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP) :
. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.
Вариант 2. Извлечение данных из двумерного диапазона
Если диапазон двумерный, т.е. состоит из нескольких строк и столбцов, то наша функция будет использоваться немного в другом формате:
=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )
Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.
Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:
Вариант 3. Несколько таблиц
Если таблица не одна, а их несколько, то функция ИНДЕКС может извлечь данные из нужной строки и столбца именно заданной таблицы. В этом случае используется следующий синтаксис:
=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )
Обратите особое внимание, что в этом случае первый аргумент – список диапазонов - заключается в скобки, а сами диапазоны перечисляются через точку с запятой.
Вариант 4. Ссылка на столбец / строку
Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:
Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.
Вариант 5. Ссылка на ячейку
Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:
Нечто похожее можно реализовать функцией СМЕЩ (OFFSET) , но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.
Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте - это сочетание с функцией СЧЁТЗ (COUNTA) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.
Читайте также: