Вы можете использовать функцию ПОИСКПОЗ в Excel, чтобы найти относительное положение определенного значения в диапазоне ячеек или массиве.
Функция ПОИСКПОЗ аналогична функции ВПР, поскольку обе они отнесены к функциям поиска / справочника Excel. ВПР ищет определенное значение в столбце и возвращает значение в той же строке, в то время как функция ПОИСКПОЗ ищет определенное значение в диапазоне и возвращает положение этого значения.
Функция ПОИСКПОЗ в Excel ищет указанное значение в диапазоне ячеек или массиве и возвращает относительное положение первого появления этого значения в диапазоне. Функцию ПОИСКПОЗ также можно использовать для поиска определенного значения и возврата соответствующего значения с помощью функции ИНДЕКС (как и в Vlookup). Давайте посмотрим, как использовать функцию ПОИСКПОЗ в Excel, чтобы найти положение искомого значения в диапазоне ячеек.
Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ - это встроенная функция в Excel, которая в основном используется для определения относительного положения искомого значения в столбце или строке.
Синтаксис функции ПОИСКПОЗ:
= ПОИСКПОЗ (искомое_значение; искомое_массив; [тип_ совпадения})Где:
lookup_value - Значение, которое вы хотите найти в указанном диапазоне ячеек или в массиве. Это может быть числовое значение, текстовое значение, логическое значение или ссылка на ячейку, имеющую значение.
lookup_array - Массивы ячеек, в которых вы ищите значение. Это должен быть один столбец или одна строка.
match_type - Это необязательный параметр, который может иметь значение 0,1 или -1, а значение по умолчанию - 1.
- 0 ищет точное совпадение, если оно не найдено, возвращает ошибку.
- -1 ищет наименьшее значение, которое больше или равно lookup_value при поиске в массиве по возрастанию.
- 1 ищет наибольшее значение, которое меньше или равно значению look_up, когда массив поиска в порядке убывания.
Найти позицию точного совпадения
Предположим, у нас есть следующий набор данных, в котором мы хотим найти положение определенного значения.
В этой таблице мы хотим найти позицию названия города (Мемфис) в столбце (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.