ИНДЕКС и ПОИСКПОЗ в Excel: базовые и расширенные варианты поиска

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

• Функция ИНДЕКС
• Функция ПОИСКПОЗ
• Совместное использование ИНДЕКС и ПОИСКПОЗ

• Поиск по двум критериям
• Поиск слева
• Поиск с учетом регистра
• Поиск ближайшего совпадения
• Поиск по нескольким критериям
• Поиск по нескольким столбцам
• Поиск первого непустого значения
• Поиск первого числового значения
• Поиск связей с максимальным или минимальным значением
• Совет: настройте собственные сообщения об ошибке #N/A

Как использовать функции ИНДЕКС и ПОИСКПОЗ в Excel

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

Как использовать функцию ИНДЕКС в Excel

Функция ИНДЕКС в Excel возвращает значение по заданному местоположению в определённом диапазоне. Синтаксис функции ИНДЕКС выглядит следующим образом:

• массив (обязательный) — диапазон, из которого нужно вернуть значение.
• номер_строки(обязательный, если не указан)номер_столбца) — номер строки в массиве.
• номер_столбца(необязательный, но обязательный, если опущен)номер_строки) — номер столбца в массиве.

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

=INDEX(C2:C11,6)

√ Примечание: диапазон C2:C11содержит перечень баллов, а число 6указывает на экзаменационный балл 6-го студента.

Давайте проведём небольшой тест. Какое значение вернёт формула =INDEX(A1:C1,2)? — Да, она вернёт дату рождения, то есть 2-е значение в указанной строке.

Теперь мы знаем, что функция ИНДЕКС отлично работает как с горизонтальными, так и с вертикальными диапазонами. Но что делать, если нужно получить значение из более крупного диапазона, содержащего несколько строк и столбцов? В этом случае необходимо указать и номер строки, и номер столбца. Например, чтобы найти балл Джеффа в пределах всей таблицы, а не только одного столбца, можно определить его балл с помощью номера строки 6 и номера столбца 3 в диапазоне ячеек от A2 до C11 следующим образом:

=INDEX(A2:C11,6,3)

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

Как использовать функцию ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ в Excel возвращает позицию указанного элемента в заданном диапазоне в виде числа. Её синтаксис выглядит следующим образом:

• искомое_значение (обязательный параметр) — значение, которое необходимо найти в массиве_поиска.
• массив_поиска (обязательный) — диапазон ячеек, в котором функция ПОИСКПОЗ выполняет поиск.
• тип_сопоставления(необязательный):1,0или -1.
  • 1 (по умолчанию): функция ПОИСКПОЗ находит наибольшее значение, которое меньше или равно значение_поиска. Значения в массиве массив_поиска должны быть расположены в порядке возрастания.
  • 0 — функция ПОИСКПОЗ находит первое значение, точно совпадающее со значением_поиска. Значения в массиве массив_поиска могут располагаться в любом порядке. (Если тип сопоставления установлен в 0, можно использовать подстановочные знаки.)
  • -1 — функция ПОИСКПОЗ находит наименьшее значение, которое больше или равно значение_поиска. Значения в массиве массив_поиска должны быть расположены в порядке убывания.

Например, чтобы узнать позицию Веры в списке Список имен, можно использовать функцию Выделить формулы следующим образом:

=MATCH("Vera",A2:A11,0)

√ Примечание: результат «4» означает, что имя «Vera» занимает 4-ю позицию в списке.

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

Как объединить ИНДЕКС и ПОИСКПОЗ в Excel

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

Чтобы найти балл Эвелин, зная, что экзаменационные баллы находятся в 3-м столбце, можно использовать функцию ПОИСКПОЗ для автоматического определения номера строки — без ручного подсчёта. Затем с помощью функции ИНДЕКС можно получить значение на пересечении найденной строки и 3-го столбца:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Поскольку формула может показаться немного сложной, давайте подробно разберём каждую её часть.

Формула ИНДЕКСсодержит три аргумента:

• номер_строки:MATCH(«Evelyn»,A2:A11,0)указывает функции ИНДЕКС позицию строки со значением «Evelyn» в диапазоне A2:A11, которая равна5.
• номер_столбца: 3 задаёт 3-й столбец, в котором функция ИНДЕКС ищет балл в массиве.
• массив: A2:C11 указывает функции ИНДЕКС вернуть соответствующее значение на пересечении заданных строки и столбца в диапазоне от A2 до C11. В итоге мы получаем результат 90.

В приведённой выше формуле мы использовали жёстко заданное значение «Evelyn». Однако на практике такие значения нецелесообразны, поскольку их придётся менять каждый раз при поиске других данных — например, балла другого студента. В подобных случаях лучше использовать ссылки на ячейки, чтобы создавать динамические формулы. Например, в данном случае я заменю «Evelyn» на F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Примеры использования ИНДЕКС и Выделить формулы

В этом разделе мы рассмотрим различные сценарии применения функций ИНДЕКС и ПОИСКПОЗ для решения самых разных задач.

ИНДЕКС и ПОИСКПОЗ для двунаправленного поиска

В предыдущем примере мы знали номер столбца и использовали функцию «Выделить формулы» для определения номера строки. Но что делать, если номер столбца тоже неизвестен?

В таких случаях можно выполнить двунаправленный поиск (также называемый матричным), применив две функции ПОИСКПОЗ: одну — для определения номера строки, а другую — для определения номера столбца. Например, чтобы узнать балл Эвелин, используйте следующую формулу:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

ИНДЕКС и ПОИСКПОЗ для поиска влево

Теперь рассмотрим ситуацию, когда необходимо определить класс Эвелин. Возможно, вы заметили, что столбец с классами расположен слева от столбца с именами — это выходит за рамки возможностей другой мощной функции поиска Excel, ВПР.

На самом деле, возможность выполнять поиск влево — одно из преимуществ сочетания ИНДЕКС и ПОИСКПОЗ по сравнению с ВПР.

Чтобы найти класс Эвелин, используйте следующую формулу для поиска имени Эвелин в диапазоне B2:B11 и получения соответствующего значения из диапазона A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

ИНДЕКС и ПОИСКПОЗ для поиска с учётом регистра

Функция ПОИСКПОЗ по умолчанию не учитывает регистр. Однако если вам нужен поиск с учётом прописных и строчных букв, формулу можно усовершенствовать с помощью функции ТОЧНОЕСОВПАД. Объединив ПОИСКПОЗ с ТОЧНОЕСОВПАД в формуле ИНДЕКС, вы сможете эффективно выполнять поиск с учётом регистра — как показано ниже:

• массив — диапазон, из которого нужно вернуть значение.
• искомое_значение — значение, которое ищется с учётом регистра символов в массиве_поиска.
• массив_поиска — это диапазон ячеек, в котором функция ПОИСКПОЗ сравнивает значения с искомым_значением.

Например, чтобы узнать экзаменационный балл JIMMY, используйте следующую формулу:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Примечание: это формула массива, которую необходимо вводить с помощью комбинации клавиш Ctrl+Shift+Enter, за исключением Excel 365, Excel 2021 и более новых версий.

ИНДЕКС и ПОИСКПОЗ для поиска ближайшего совпадения

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

• массив— диапазон, из которого требуется вернуть значение.
• массив_поиска — диапазон значений, в котором необходимо найти ближайшее совпадение для искомого_значения.
• искомое_значение — значение, для которого нужно найти ближайшее совпадение.

Например, чтобы определить, чей балл ближе всего к значению 85, используйте следующую формулу для поиска балла, наиболее близкого к 85, в диапазоне C2:C11 и получения соответствующего значения из диапазона A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Примечание: это формула массива, которую необходимо вводить с помощью комбинации клавиш Ctrl+Shift+Enter, за исключением Excel 365, Excel 2021 и более новых версий.

ИНДЕКС и ПОИСКПОЗ для поиска по нескольким критериям

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

√ Примечание: это формула массива, которую необходимо вводить с помощью комбинации клавиш Ctrl+Shift+Enter. После этого в строке формул появятся фигурные скобки.

• массив— диапазон, из которого требуется вернуть значение.
• (искомое_значение=массив_поиска) представляет собой одно условие. Оно проверяет, совпадает ли конкретное искомое_значение со значениями в массиве_поиска.

Например, чтобы найти оценку Коко из класса А, чья дата рождения — 7/2/2008, вы можете использовать следующую формулу:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

Функции ИНДЕКС и ПОИСКПОЗ для поиска по нескольким столбцам

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

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

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>,0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Вот где вам пригодится функция Kutools для Excel — Индекс и сопоставление нескольких столбцов. Она упрощает процесс, делая сопоставление конкретных записей с соответствующими категориями быстрым и лёгким. Чтобы воспользоваться этим мощным инструментом и без усилий сопоставить Шона с его классом, просто загрузите и установите надстройку Kutools для Excel, а затем выполните следующие действия:

1. Выберите ячейку назначения, в которой будет отображаться соответствующий класс.
2. На вкладке Kutools нажмите Помощник формул > Поиск и ссылка > Индекс и сопоставление нескольких столбцов.
3. 
3. В появившемся диалоговом окне выполните следующие действия:
   1. Нажмите первую кнопку рядом с полем Lookup_col, чтобы выбрать столбец с ключевой информацией, которую нужно вернуть — в данном случае названия классов. (Выбрать можно только один столбец.)
   2. Нажмите вторую кнопку рядом с полем Table_rng, чтобы выбрать ячейки, значения которых нужно сопоставить со значениями из выбранного столбца Lookup_col — то есть имена учащихся.
   3. Нажмите третью кнопку рядом с полем Lookup_value, чтобы выбрать ячейку с именем учащегося, которого нужно сопоставить с его классом — в данном случае это Шон.
   4. Нажмите ОК.
   5. 

Результат

Kutools автоматически сгенерировал формулу, и название класса Шона сразу же отобразится в целевой ячейке.

ИНДЕКС и ПОИСКПОЗ для поиска первого непустого значения

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

• Получение первого непустого значения в столбце или строке с игнорированием ошибок:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<,>,0),0),0))

• Получение первого непустого значения в столбце или строке с учетом ошибок:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

ИНДЕКС и ПОИСКПОЗ для поиска первого числового значения

Чтобы получить первое числовое значение из столбца или строки, используйте формулу на основе функций ИНДЕКС, ПОИСКПОЗ и ЕЧИСЛО.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

ИНДЕКС и ПОИСКПОЗ для поиска значений, связанных с МАКС или МИН

Если вам нужно получить значение, соответствующее максимальному или минимальному значению в заданном диапазоне, используйте функции МАКС или МИН совместно с ИНДЕКС и ПОИСКПОЗ.

• ИНДЕКС и ПОИСКПОЗ для получения значения, связанного с Максимальное значение:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• ИНДЕКС и ПОИСКПОЗ для получения значения, связанного с Минимальное значение:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• В приведенных выше формулах используются два аргумента:
• • Массив указывает диапазон, из которого требуется вернуть связанную информацию.
  • массив_поиска представляет набор значений, подлежащих проверке или поиску по определённым критериям, например по максимальному или минимальному значению.

Например, если вы хотите определить,у кого самый высокий балл, используйте следующую формулу:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Совет: настройте собственные сообщения об ошибке #N/A

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

Чтобы сделать ваши таблицы более удобными для пользователей, вы можете настроить сообщение об ошибке, обернув свою формулу ИНДЕКС Выделить формулы в функцию ЕСЛИНД:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

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