Excel

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

How Use Index Match

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





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

ИНДЕКС функция | Функция ПОИСКПОЗ | ИНДЕКС и МАТЧ | Двусторонний поиск | Левый поиск | Деликатный случай | Ближайшее совпадение | Несколько критериев | Еще примеры





Функция ИНДЕКС

Функция ИНДЕКС в Excel фантастически гибкая и мощная, и вы найдете ее в огромном количестве формул Excel, особенно в сложных формулах. Но что на самом деле делает INDEX? Вкратце, INDEX извлекает значение в заданном месте в диапазоне. Например, предположим, что у вас есть таблица планет в нашей солнечной системе (см. Ниже), и вы хотите получить название 4-й планеты, Марс, с помощью формулы. Вы можете использовать ИНДЕКС так:

 
= INDEX (B3:B11,4)

Используя ИНДЕКС, чтобы получить название 4-й планеты
ИНДЕКС возвращает значение в 4-й строке диапазона.



Видео: Как найти информацию с помощью INDEX

Что, если вы хотите получить диаметр Марса с помощью INDEX? В этом случае мы можем указать как номер строки, так и номер столбца, а также предоставить больший диапазон. В приведенной ниже формуле ИНДЕКС используется полный диапазон данных в B3: D11 с номером строки 4 и номером столбца 2:

 
= INDEX (B3:D11,4,2)

Используя ИНДЕКС, чтобы получить диаметр 4-й планеты
INDEX извлекает значение в строке 4, столбце 2.

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

В этот момент вы можете подумать: «Ну и что? Как часто вы действительно знаете положение чего-либо в таблице? '

Совершенно верно. Нам нужен способ определить положение вещей, которые мы ищем.

Войдите в функцию ПОИСКПОЗ.

Функция ПОИСКПОЗ

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

 
= MATCH ('peach',B3:B9,0)

Использование ПОИСКПОЗ для поиска положения в вертикальном диапазоне
ПОИСКПОЗ возвращает 3, так как «Персик» является третьим элементом. MATCH не чувствителен к регистру.

MATCH не заботится о том, является ли диапазон горизонтальным или вертикальным, как вы можете видеть ниже:

 
= MATCH ('peach',C4:I4,0)

Использование ПОИСКПОЗ для поиска положения в горизонтальном диапазоне
Тот же результат с горизонтальным диапазоном, ПОИСКПОЗ возвращает 3.

Видео: Как использовать MATCH для точных совпадений

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

ИНДЕКС и МАТЧ вместе

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

Продажи по продавцам по месяцам

Допустим, мы хотим написать формулу, которая возвращает количество продаж за февраль для данного продавца. Из приведенного выше обсуждения мы знаем, что можем дать INDEX номер строки и столбца для получения значения. Например, чтобы вернуть номер продаж за февраль для Frantz, мы предоставляем диапазон C3: E11 со строкой 5 и столбцом 2:

 
= INDEX (C3:E11,5,2) // returns 94

Но мы явно не хотим жестко кодировать числа. Вместо этого мы хотим динамичный Погляди.

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

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

Сделав еще один шаг вперед, мы будем использовать значение H2 в MATCH:

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

ИНДЕКС и МАТЧ, чтобы найти февральские продажи для любого имени
ПОИСКПОЗ находит 'Frantz' и возвращает 5 в ИНДЕКС для строки.

Обобщить:

  1. INDEX требует числовых позиций.
  2. MATCH находит эти позиции.
  3. МАТЧ вложенный внутри INDEX.

Теперь займемся номером столбца.

Двусторонний поиск с помощью INDEX и MATCH

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

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

 
= MATCH ('Mar',C2:E2,0) // returns 3

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

Динамический поиск с помощью INDEX и MATCH
Полностью динамический двусторонний поиск с помощью INDEX и MATCH.

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

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

 
= INDEX (C3:E11,5,3)

а ИНДЕКС правильно возвращает 10 525 долларов - это число продаж Франца в марте.

Примечание: вы можете использовать Проверка данных для создания раскрывающихся меню для выбора продавца и месяца.

Видео: Как выполнить двусторонний поиск с помощью INDEX и MATCH

Видео: Как отлаживать формулу с помощью F9 (чтобы увидеть возвращаемые значения MATCH)

Левый поиск

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

Поиск слева с помощью ИНДЕКС и ПОИСКПОЗ

Прочтите подробное объяснение здесь .

Поиск с учетом регистра

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

Поиск с учетом регистра с помощью INDEX и MATCH

Прочтите подробное объяснение здесь .

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

Ближайшее совпадение

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

Найдите ближайшее совпадение с помощью ИНДЕКС и ПОИСКПОЗ

Прочтите подробное объяснение здесь .

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

Поиск по нескольким критериям

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

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

Прочтите подробное объяснение здесь .

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

что такое вложенная функция if

Еще примеры ИНДЕКС + ПОИСКПОЗ

Вот еще несколько основных примеров использования INDEX и MATCH в действии, каждый с подробным объяснением:

Автор Дэйв Брунс Вложения Файл индекс exceljet и match.xlsx


^