В этой статье объясняется с примерами формул, как использовать функцию ПОИСКПОЗ в Excel. Также вы узнаете, как улучшить формулы поиска, создав динамическую формулу с функциями ВПР и ПОИСКПОЗ.
В Microsoft Excel существует множество различных функций, которые могут помочь вам найти определенное значение в диапазоне ячеек, и ПОИСКПОЗ (MATCH на английском) — одна из них. По сути, она определяет относительное положение элемента в массиве значений. Однако функция ПОИСКПОЗ может делать гораздо больше.
Функция ПОИСКПОЗ Excel — пошаговая инструкция
Функция ПОИСКПОЗ в Excel ищет указанное значение в массиве и возвращает порядковый номер этого значения.
Рассмотрим пошагово, как составить формулу ПОИСКПОЗ:
ПОИСКПОЗ(искомое_значение; массив_поиска, [тип_совпадения])
Шаг 1. Искомое_значение (обязательный аргумент) — значение, которое вы хотите найти. Это может быть число, текстовое или логическое значение, а также ссылка на ячейку.
Шаг 2. Массив_поиска (обязательно) — диапазон ячеек для поиска.
Шаг 3. Указываем Тип_совпадения (необязательно) — определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1. Аргумент, установленный в 0, возвращает только точное совпадение, в то время как два других значения допускают приблизительное совпадение.
- 1 или опущено (по умолчанию) — найти наибольшее значение в массиве поиска, которое меньше или равно значению поиска. Требуется сортировка массива поиска в порядке возрастания, от меньшего к большему или от А до Я.
- 0 - найти первое значение в массиве, точно равное искомому значению. Никакой сортировки не требуется.
- -1 — найти наименьшее значение в массиве, которое больше или равно искомому значению. Массив поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от Я до A.
Чтобы лучше понять это, давайте составим простую формулу на основе этих данных: имена учащихся в столбце А и их экзаменационные баллы в столбце В, отсортированные от наибольшего к наименьшему. Чтобы узнать, какое место среди других занимает конкретный ученик (скажем, Елена ), используйте эту простую формулу:
=ПОИСКПОЗ(“Елена”; A2:A8;0)
При желании вы можете поместить искомое значение в какую-либо ячейку (E1 в этом примере) и сослаться на эту ячейку в своей формуле:
=ПОИСКПОЗ(E1; A2:A8;0)
Как вы видите на скриншоте выше, имена расположены не по алфавиту, а потому мы устанавливаем аргументу тип_совпадения значение 0 (точное соответствие). Ведь только этот вариант не требует сортировки в массиве поиска.
Технически формула ПОИСКПОЗ показывает, на какой позиции находится "Елена" в списке. Но поскольку оценки отсортированы от наибольшей суммы к наименьшей, это также говорит нам о том, что Елена занимает пятое место среди 8 учащихся.
4 вещи, которые вы должны помнить
Как вы только что убедились, использовать ПОИСКПОЗ в Excel достаточно просто. Однако, как и в случае с почти любой другой функцией Excel, есть несколько особенностей, о которых вам следует помнить:
- Функция ПОИСКПОЗ возвращает координаты искомого значения в массиве, а не само значение.
- Она нечувствительна к регистру , то есть не различает строчные и прописные символы при работе с текстовыми значениями.
- Если искомый массив содержит несколько вхождений значения, то возвращается позиция первого найденного.
- Если нужное значение не найдено, возвращается ошибка #Н/Д.
Секреты ПОИСКПОЗ в Excel — примеры формул
Теперь, когда вы знаете основные способы использования функции ПОИСКПОЗ Excel, давайте обсудим еще несколько примеров формул, чуть более сложных.
Частичное совпадение с подстановочными знаками
Как и многие другие функции, ПОИСКПОЗ понимает следующие подстановочные знаки :
- Вопросительный знак (?) - заменяет любой одиночный символ
- Звездочка (*) - заменяет любую последовательность символов
Примечание. Подстановочные знаки можно использовать только тогда, если для параметра тип_совпадения установлено значение 0.
Формула поиска с подстановочными знаками полезна в ситуациях, когда требуется сопоставить не всю текстовую строку, а только некоторые символы или её часть. Чтобы проиллюстрировать это, рассмотрим следующий пример.
Предположим, у вас есть данные о продажах напитков за последний месяц. Вы хотите найти относительную позицию определенного наименования в списке (отсортированном по суммам продаж в порядке убывания), но не можете точно вспомнить полное наименование, хотя помните несколько первых символов.
Предположим, что наименования товаров находятся в диапазоне A2:A11, и вы ищете имя, начинающееся с «доб». Формула выглядит следующим образом:
=ПОИСКПОЗ("доб*"; A2:A11;0)
Чтобы сделать нашу формулу более универсальной, вы можете ввести искомое значение в какую-либо ячейку (E1 в этом примере) и соединить эту ячейку с подстановочным знаком, например:
=ПОИСКПОЗ(E1&"*"; A2:A11;0)
Как показано на скриншоте ниже, формула возвращает 4, что является позицией «Добрый экзотик»:
Обратите внимание, что напитков «Добрый» в нашем массиве целых три. Но формула останавливает поиск, как только будет найдено первое подходящее соответствие.
Чтобы заменить только один символ в искомом значении, используйте подстановочный знак «?».
Поиск с учетом регистра
Как упоминалось ранее, функция ПОИСКПОЗ не различает символы верхнего и нижнего регистра.
Чтобы создать формулу поиска с учетом регистра, включите в вашу формулу функцию СОВПАД, которая точно сравнивает содержимое ячеек, включая регистр символов.
Вот общая формула с учетом регистра для сопоставления данных:
=ПОИСКПОЗ(ИСТИНА;СОВПАД(искомый массив ; искомое значение );0)
Формула работает по следующей логике:
- Функция СОВПАД (EXACT в английской версии) сравнивает значение с каждым элементом массива поиска. Если сравниваемые ячейки точно равны, функция возвращает значение ИСТИНА, в противном случае — ЛОЖЬ.
- Затем функция ПОИСКПОЗ сравнивает ИСТИНА (которое является ее искомым_значением ) с каждым значением в массиве, возвращаемом функцией СОВПАД, и возвращает позицию первого совпадения.
Пожалуйста, имейте в виду, что это формула массива , которая требует нажатия Ctrl + Shift + Enter при завершении ее ввода.
Предполагая, что искомое значение находится в ячейке E1, а массив поиска — A2:A9, формула выглядит следующим образом:
=ПОИСКПОЗ(ИСТИНА;СОВПАД(A2:A9;E1);0)
На скриншоте показана формула поиска соответствия с учетом регистра в Excel:
Как видите, формула различает регистр букв и по этой причине «А-201» было признано неподходящим.
Поиск с несколькими условиями
Выше мы рассматривали поиск по одному условию. Но на практике очень часто критериев поиска бывает несколько. Давайте рассмотрим такой случай.
Предположим, у нас есть список продаж отдельных товаров в нескольких регионах. Нужно найти первую подходящую позицию в списке для нужного товара и заданного региона.
Вот формула для нескольких условий:
=ПОИСКПОЗ(1;(B2:B12=G1)*(C2:C12=G2);0)
Давайте разбираться.
Наши два условия мы записываем в виде выражения (B2:B12=G1)*(C2:C12=G2). Первое условие (B2:B12=G1) означает, что мы сравниваем каждое из значений в столбце "Регион" с целью "Север", которая записана в G1. Получаем массив {ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}
Аналогично каждый из товаров сравниваем с "Яблоки" из G2. Аналогично получаем результат {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА}.
После перемножения этих массивов получаем матрицу единиц и нулей: {0:0:0:1:0:0:0:0:1:0:0}
Здесь единицы означают те позиции, в которых оба условия выполняются.
И вот в этом массиве функция ПОИСКПОЗ ищет первую единицу. И находит ее в 4-й позиции.
Обратите внимание, что в наших данных есть две строки, в которых выполняются оба условия - товар и регион. Но функция ПОИСКПОЗ ищет в массиве только первое совпадение и после этого поиск останавливается.
Думаю, вы понимаете, что совершенно аналогичным образом можно вести поиск по трём и большему количеству условий.
Номер позиции в данном случае для нас может быть не так уж и важен сам по себе. Но зато, зная его, мы можем при помощи функции ИНДЕКС извлечь значение из любого столбца в этой позиции. Более подробно об этом читайте: ИНДЕКС+ПОИСКПОЗ с несколькими условиями.
Как можно сравнить столбцы
Проверка двух списков на совпадения и различия — одна из наиболее распространенных задач в Excel, и ее можно выполнить различными способами. Формула ЕНД/ПОИСКПОЗ является одним из них:
ЕСЛИ(ЕНД(ПОИСКПОЗ( 1-е значение в Списке1 , Списке2 , 0)), "Нет в Списке 1", "")
Для любого значения списка 2, которого нет в списке 1, формула возвращает « Нет в списке 1 ».
Рассмотрим пошагово:
- Функция ПОИСКПОЗ ищет значение из списка 1 в списке 2. Если значение найдено, оно возвращает его относительное положение, в противном случае получается ошибка #Н/Д.
- Функция ЕНД в Excel выполняет только одну работу — проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если обрабатываемое ею значение является ошибкой #Н/Д, функция возвращает значение ИСТИНА, в противном случае – ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из списка 1 не найдено в списке 2 (т. е. функция ПОИСКПОЗ возвращает ошибку).
- Поскольку вам может быть не слишком понятным видеть ИСТИНА для значений, которые не отображаются в списке 1, вы оборачиваете функцию ЕСЛИ вокруг ЕНД, чтобы вместо ИСТИНА отображалось « Нет в списке 1 » или любой другой текст, который вы хотите.
Например, чтобы сравнить значения в столбце B со значениями в столбце A, формула может быть записана так (где B2 — самая верхняя ячейка):
=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0)); "Нет в списке 1"; "")
Как вы помните, функция ПОИСКПОЗ в Excel сама по себе нечувствительна к регистру. Чтобы она различала регистр символов, вставьте функцию СОВПАД (EXACT по английски) в аргумент массив_поиска и не забудьте нажать Ctrl + Shift + Enter , чтобы ввести эту формулу массива :
{=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A; B2);0)); "Нет в списке 1"; "")}
На следующем рисунке показаны обе формулы в действии:
Быть может, вам также будет интересен этот материал: 5 способов сравнения ячеек в Excel.
Совместное использование ВПР и ПОИСКПОЗ
В этом примере предполагается, что у вас уже есть базовые знания о функции ВПР в Excel . И если вы работали с ВПР, то есть вероятность, что вы столкнулись с ее многочисленными ограничениями (подробный обзор которых можно найти в статье Почему не работает функция ВПР в Excel ) и ищете более надежную и универсальную альтернативу.
Один из самых досадных недостатков ВПР заключается в том, что она перестает корректно работать после вставки или удаления столбца в таблице поиска. Это происходит потому, что функция ВПР извлекает совпадающее значение на основе указанного вами номера возвращаемого столбца (номер индекса). Поскольку номер индекса «жестко запрограммирован» в формуле, Excel не может изменить его, когда в таблицу добавляются или удаляются новые столбцы.
Функция ПОИСКПОЗ в Excel работает с относительным положением искомого значения, что идеально подходит для аргумента номер_стролбца функции ВПР. Другими словами, вместо указания возвращаемого столбца в виде статического числа вы используете её, чтобы найти текущую позицию этого столбца.
Чтобы упростить понимание, давайте снова воспользуемся таблицей с экзаменационными баллами студентов (аналогичной той, которую мы использовали в начале этой статьи). Но на этот раз мы будем получать реальную оценку, а не ее порядковый номер в массие.
Предполагая, что искомое значение находится в ячейке F1, значения таблицы расположены в $A$1:$C$8 (рекомендуется зафиксировать его с помощью абсолютных ссылок на ячейки, если вы планируете копировать формулу), выражение выглядит следующим образом:
=ВПР($G$1;$A$1:$С$8; 2; ЛОЖЬ)
Третий аргумент ( номер столбца ) имеет значение 2, потому что результат Теста 1 , который мы хотим получить, является вторым столбцом в таблице. Как вы можете видеть на скриншоте ниже, эта обычная формула ВПР работает хорошо.
Но только до тех пор, пока вы не вставите или не удалите какие-то столбцы:
Как видите, если мы вставляем дополнительный столбец, то формула теперь извлекает неверные данные. Она по-прежнему ссылается на второй столбец, в то время как данные Тест 1 сместились в третий.
Чтобы предотвратить подобные вещи, вы можете сделать свою формулу ВПР более гибкой, включив в нее такое выражение:
ПОИСКПОЗ($F$2;$A$1:$С$1;0)
Где:
- F2 — это искомое значение, которое в точности совпадает с именем возвращаемого столбца, т. е. столбца, из которого вы хотите извлечь значение ( Тест 1 в этом примере).
- A1:C1 — массив поиска, содержащий заголовки таблицы.
А теперь запишите эту формулу в аргумент номер_столбца вашей формулы ВПР, например:
=ВПР($G$1;$A$1:$D$8; ПОИСКПОЗ($F$2;$A$1:$С$1;0); ЛОЖЬ)
И убедитесь, что формула работает безупречно, независимо от того, сколько столбцов вы добавляете или удаляете.
Вставляем столбец с итоговым тестом:
Формула автоматически изменяется и продолжает работать, возвращая верный результат:
=ВПР($G$1;$A$1:$D$8; ПОИСКПОЗ($F$2;$A$1:$D$1;0); ЛОЖЬ)
Использование вместе с ГПР
Аналогичным образом вы можете использовать функцию ПОИСКПОЗ в Excel, чтобы улучшить свои формулы ГПР . Общий принцип, по сути, такой же, как и в случае ВПР: вы используете функцию ПОИСКПОЗ, чтобы получить относительное положение возвращаемого столбца, и передаете это число аргументу номер_строки вашей формулы ГПР.
Предположим, что искомое значение находится в ячейке B5, данные таблицы — B1:H3, имя возвращаемой строки (значение поиска для ПОИСКПОЗ) — в ячейке A6, а заголовки строк — A1:A3.
Тогда формула выглядит следующим образом:
=ГПР(B5;B1:H3;ПОИСКПОЗ(A6; A1:A3;0);ЛОЖЬ)
Как вы только что видели, комбинация ВПР/ГПР и ПОИСКПОЗ, безусловно, является улучшением по сравнению с обычными формулами. Однако функция ПОИСКПОЗ не устраняет всех их ограничений и недостатков. В частности, эта формула ВПР по-прежнему не может делать «левый поиск», а ГПР не может выполнять поиск ни в одной строке, кроме самой верхней.
Чтобы преодолеть вышеуказанные (и некоторые другие) ограничения, научитесь использовать комбинацию ИНДЕКС+ПОИСКПОЗ, которая обеспечивает действительно мощный и универсальный способ поиска в Excel, превосходящий ВПР и ГПР во многих отношениях.
Подробное руководство и примеры формул можно найти в ИНДЕКС и ПОИСКПОЗ в Excel — лучшей альтернативе ВПР .
Вот как можно использовать формулы ПОИСКПОЗ в Excel для быстрого поиска в массиве данных.
Надеемся, что примеры, рассмотренные в этом руководстве, окажутся полезными в вашей работе.
