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

• ИНДЕКС функция
• Функция ПОИСКПОЗ
• Объедините ИНДЕКС и СООТВЕТСТВИЕ.

• Двусторонний поиск
• Левый поиск
• Поиск с учетом регистра
• Ближайшее совпадение
• Поиск по нескольким критериям
• Поиск по нескольким столбцам
• Поиск первого непустого значения
• Поиск первого числового значения
• Поиск ассоциаций MAX или MIN
• Совет: настройте свои собственные сообщения об ошибках #Н/Д.

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

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

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

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

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

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

=INDEX(C2:C11,6)

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

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

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

=INDEX(A2:C11,6,3)

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

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Edge, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

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

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

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

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

√ Примечание. Результат «4» означает, что имя «Вера» находится на 4-й позиции списка.

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

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

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

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

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

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

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

• row_num: ПОИСКПОЗ("Эвелин",A2:A11,0) предоставляет INDEX позицию строки значения "Эвелин" В диапазоне A2: A11, Которая является 5.
• номер_столбца: 3 указывает на 3rd столбец для ИНДЕКС, чтобы найти оценку в массиве.
• массив: A2: C11 инструктирует INDEX вернуть соответствующее значение на пересечении указанной строки и столбца в диапазоне от от А2 до С11. Наконец, мы получаем результат 90.

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

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

Примеры формул ИНДЕКС и ПОИСКПОЗ

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

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

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

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

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

ИНДЕКС и ПОИСКПОЗ, чтобы применить поиск слева

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

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

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

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

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

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

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

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

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

√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter, кроме Excel 365 и Excel 2021.

ИНДЕКС и ПОИСКПОЗ, чтобы найти ближайшее совпадение.

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

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

Например, чтобы узнать чей счет ближе всего к 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.

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Edge, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

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

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

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

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

=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 for Excel's Индексирование и сопоставление по нескольким столбцам функция пригодится. Это упрощает процесс, позволяя быстро и легко сопоставлять определенные записи с соответствующими категориями. Чтобы разблокировать этот мощный инструмент и легко сопоставить Шона с его классом, просто загрузите и установите надстройку Kutools для Excel, а затем сделайте следующее:

1. Выберите ячейку назначения, в которой вы хотите отобразить соответствующий класс.
2. На Кутулс вкладку нажмите Формула Помощник > Поиск и справка > Индексирование и сопоставление по нескольким столбцам.
3. 
3. Во всплывающем диалоговом окне выполните следующие действия:
   1. Нажмите на 1-й рядом с Поиск_столбец чтобы выбрать столбец, содержащий ключевую информацию, которую вы хотите вернуть, т. е. имена классов. (Здесь можно выбрать только один столбец.)
   2. Нажмите на 2-й рядом с Таблица_rng для выбора ячеек, соответствующих значениям в выбранных Поиск_столбец, то есть имена студентов.
   3. Нажмите на 3-й рядом с Искомое_значение чтобы выбрать ячейку, содержащую имя учащегося, которого вы хотите сопоставить с его классом, в данном случае Шон.
   4. Нажмите OK.
   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 и MATCH для поиска ассоциаций MAX или MIN.

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

• ИНДЕКС и ПОИСКПОЗ для получения значения, связанного с максимальным значением:
• =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))

Совет: настройте свои собственные сообщения об ошибках #Н/Д.

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

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

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

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