Как использовать функцию сопоставления Excel

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

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

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

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

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

Синтаксис функции ПОИСКПОЗ:

= ПОИСКПОЗ (искомое_значение; искомое_массив; [тип_ совпадения})

Где:

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

lookup_array - Массивы ячеек, в которых вы ищите значение. Это должен быть один столбец или одна строка.

match_type - Это необязательный параметр, который может иметь значение 0,1 или -1, а значение по умолчанию - 1.

  • 0 ищет точное совпадение, если оно не найдено, возвращает ошибку.
  • -1 ищет наименьшее значение, которое больше или равно lookup_value при поиске в массиве по возрастанию.
  • 1 ищет наибольшее значение, которое меньше или равно значению look_up, когда массив поиска в порядке убывания.

Найти позицию точного совпадения

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

Это изображение имеет пустой атрибут alt; имя его файла - allthings.how-how-to-use-excel-match-function-image-1.png

В этой таблице мы хотим найти позицию названия города (Мемфис) в столбце (A2: A23), поэтому мы используем эту формулу:

= ПОИСКПОЗ ("мемфис"; A2: A23,0)

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

Примечание: Если lookup_value не найден в диапазоне поиска или если вы указали неправильный диапазон поиска, функция вернет ошибку # N / A.

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

Найти позицию приблизительного совпадения

Есть два способа найти приблизительное или точное совпадение искомого значения и вернуть его позицию.

  • Один из способов - найти наименьшее значение, которое больше или равно (следующее наибольшее совпадение) указанному значению. Этого можно добиться, установив последний аргумент (match_type) функции как «-1».
  • Другой способ - наибольшее значение, которое меньше или равно (следующее наименьшее совпадение) заданному значению. Этого можно добиться, задав для match_type функции значение «1».

Следующее наименьшее совпадение

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

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

= ПОИСКПОЗ (F2; D2: D23,1)

Когда эта формула не может найти точное совпадение для значения в ячейке F2, она указывает на позицию (16) следующего наименьшего значения, т. Е. 98.

Следующее наибольшее совпадение

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

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

= ПОИСКПОЗ (F2; D2: D23; -1)

Эта функция ПОИСКПОЗ ищет значение в F2 (55) в диапазоне поиска D2: D23, и, когда не может найти точное совпадение, возвращает позицию (16) следующего наибольшего значения, то есть 58.

Подстановочный знак

Подстановочные знаки могут использоваться в функции ПОИСКПОЗ, только если match_type установлено в «0», а значение поиска - текстовая строка. В функции ПОИСКПОЗ можно использовать подстановочные знаки: звездочку (*) и вопросительный знак (?).

  • Вопросительный знак (?) используется для сопоставления любого символа или буквы с текстовой строкой.
  • Звездочка (*) используется для сопоставления любого количества символов в строке.

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

= ПОИСКПОЗ ("Lo ?? n"; A2: A22,0)

Вы можете использовать подстановочный знак (*) так же, как (?), Но звездочка используется для соответствия любому количеству символов, а вопросительный знак используется для соответствия любому одиночному символу.

Например, если вы используете «sp *», функция может соответствовать динамику, скорости или спилбергу и т. Д. Но если функция находит несколько / повторяющихся значений, соответствующих поисковому значению, она вернет только позицию первого значения.

В этом примере мы ввели «Kil * o» в аргумент lookup_value. Таким образом, функция ПОИСКПОЗ () ищет текст, который содержит «Kil» в начале, «o» в конце и любое количество символов между ними. «Kil * o» соответствует Килиманджаро в массиве, поэтому функция возвращает относительное положение Килиманджаро, равное 16.

ИНДЕКС и МАТЧ

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

Функция VLOOKUP может искать значение только по вертикали, то есть по столбцам, в то время как комбинация INDEX MATCH может выполнять поиск как по вертикали, так и по горизонтали.

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

Синтаксис функции ИНДЕКС:

= ИНДЕКС (массив; номер_строки; [номер_столбца];)

В любом случае, давайте посмотрим, как работает INDEX MATCH, на примере.

В приведенном ниже примере мы хотим получить оценку «Quiz2» для ученицы «Anne». Для этого воспользуемся следующей формулой:

= ИНДЕКС (B2: F20; ПОИСКПОЗ (H2; A2: A20,0); 3)

Для получения значения INDEX требуется номер строки и столбца. В приведенной выше формуле вложенная функция ПОИСКПОЗ находит номер строки (позицию) значения «Anne» (H2). Затем мы передаем этот номер строки в функцию ИНДЕКС с диапазоном B2: F20 и номером столбца (3), который мы указываем. А функция ИНДЕКС возвращает результат «91».

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

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

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

= ИНДЕКС (A1: F20; ПОИСКПОЗ (H2; A2: A20,0); ПОИСКПОЗ (H3; A1: F1,0))

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

Теперь вы знаете, как использовать функцию Match в Excel.