Поиск в массиве с помощью функции SEARCH
В этой статье с помощью примеров формул объясняется, как использовать функцию ПОИСКПОЗ в Excel. Вы также узнаете, как улучшить формулы поиска, создав динамическую формулу с использованием функций WRP и SEARCH.
В Microsoft Excel есть несколько различных функций, которые могут помочь вам найти определенное значение в диапазоне ячеек, и ПОИСК (MATCH на английском языке) - одна из них. По сути, он определяет относительное положение элемента в массиве значений. Однако функция ПОИСК может сделать гораздо больше.
- Функция ПОИСК - пошаговые инструкции
- Найдите частичное совпадение с помощью подстановочных знаков
- Поиск с учетом регистра
- ПОИСК и другие условия
- Как сравнить столбцы с помощью HIPPING
- Комбинация IDT и HIPPING
- Использование ОРГ и ГИППИНГ
Функция ПОИСКПОЗ Excel — пошаговая инструкция
Функция ПОИСКПОЗ в Excel ищет определенное значение в массиве и возвращает относительное положение этого значения.
Узнайте шаг за шагом, как создать формулу ПОИСК:
SEARCH_value; array_search, [match_type])
Шаг 1 SEARCH_value (обязательный аргумент) - значение, которое мы хотим найти. Это может быть число, текст или булево значение, или ссылка на ячейку.
Шаг 2. search_array (обязательный) - диапазон ячеек для поиска.
Шаг 3. specify Match_type (необязательно) - указывает тип соответствия. Это может быть одно из следующих значений: 1, 0, -1. Аргумент, установленный в 0, возвращает только точное совпадение, в то время как два других значения допускают приблизительное совпадение.
- 1 или опущено (по умолчанию) - Найти наибольшее значение в массиве поиска, которое меньше или равно искомому значению. Требуется отсортировать массив поиска в порядке возрастания, от меньшего к большему или от A к Z.
- 0 - Найти первое значение в массиве, которое точно равно искомому значению. Сортировка не требуется.
- -1 - Найти наименьшее значение в массиве, которое больше или равно искомому значению. Массив для поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от I к A.
Чтобы лучше понять функцию ПОИСКПОЗ, давайте создадим простую формулу, используя эти данные: имена студентов в столбце A и их результаты экзаменов в столбце B, отсортированные от высшего к низшему. Чтобы узнать, какое место занимает конкретный студент (скажем, Елена ) среди других, используйте эту простую формулу:
=POST("Елена"; A2:A8;0)
Если вы хотите, вы можете поместить искомое значение в ячейку (E1 в данном примере) и ссылаться на нее в своей формуле:
=SEARCHPOS(E1;A2:A8;0)
Как видно на скриншоте выше, имена расположены не по алфавиту, поэтому мы установили аргумент match_type равным 0 (точное совпадение). Это единственный вариант, который не требует сортировки в таблице поиска.
Технически, формула ПОИСКПОЗ возвращает относительное положение 'Elena' в списке. Но поскольку результаты сортируются от самого высокого к самому низкому, это также говорит нам о том, что Елена занимает пятое место из 8 студентов.
4 вещи, которые вы должны знать о функции ПОИСКПОЗ
Как вы только что убедились, использовать функцию ПОИСКПОЗ в Excel довольно просто. Однако, как и почти с любой другой функцией Excel, есть несколько моментов, о которых следует знать:
- Функция ПОИСКПОЗ возвращает относительную позицию искомого значения в массиве, а не само значение.
- Функция ПОИСК является регистронезависимой, т.е. не различает символы верхнего и нижнего регистра при работе с текстовыми значениями.
- Если искомый массив содержит несколько вхождений искомого значения, возвращается позиция первого найденного значения.
- Если искомое значение не найдено в массиве поиска, возвращается ошибка #N/D.
Как использовать ПОИСКПОЗ в Excel — примеры формул
Теперь, когда вы знаете основные способы использования функции ПОИСКПОЗ в Excel, давайте обсудим еще несколько примеров формул, которые немного сложнее.
Частичное совпадение с подстановочными знаками
Как и многие другие функции, функция ПОИСК понимает следующие подстановочные знаки :
- Вопросительный знак (?) - заменяет любой отдельный символ
- Звездочка (*) - заменяет любую последовательность символов
Предупреждение. Вы можете использовать символы подстановки, только если для параметра match_type установлено значение 0.
Формула совпадения с подстановочным знаком полезна в ситуациях, когда вы не хотите, чтобы совпадала вся текстовая строка, а только определенные символы или их часть. Чтобы проиллюстрировать это, рассмотрим следующий пример.
Предположим, у вас есть данные о продажах напитков за последний месяц. Вы хотите найти относительное положение определенного имени в списке (отсортированном по сумме продаж в порядке убывания), но вы не можете точно вспомнить полное имя, хотя можете вспомнить несколько первых символов.
Предположим, что имена элементов находятся в диапазоне A2:A11, и вы ищете имя, начинающееся с 'good'. Формула выглядит следующим образом:
=POPES("dob*"; A2:A11;0)
Чтобы сделать нашу формулу более универсальной, вы можете ввести искомое значение в ячейку (в данном примере E1) и связать эту ячейку с подстановочным знаком, например:
=SEARCHPOS(E1&"*"; A2:A11;0)
Как вы можете видеть на скриншоте ниже, формула возвращает 4, что является предметом "Kind Exotics":
Обратите внимание, что в нашем массиве есть целых три напитка "Добрый". Но формула прекращает поиск, как только найден первый совпадающий символ.
Чтобы заменить только один символ в значении поиска, используйте подстановочный знак "?".
Формула ПОИСКПОЗ с учетом регистра
Как упоминалось ранее, функция ПОИСК не чувствительна к регистру.
Чтобы создать формулу поиска с учетом регистра, используйте СЧЕТЧИКИ в сочетании с ДОМ, что приведет к точному сравнению содержимого ячеек с учетом регистра.
Вот общая формула сопоставления данных с учетом регистра:
=DISCRIPTION(TRUE;DIVISION(массив поиска ; значение поиска );0)
Формула работает по следующей логике:
- Функция DIFFERENCE (EXACT на английском языке) сравнивает значение с каждым элементом искомого массива. Если сравниваемые ячейки абсолютно равны, функция возвращает TRUE, в противном случае - FALSE.
- Затем функция ПОИСКПОЗ сравнивает TRUE (которое является ее значением_поиска) с каждым значением в массиве, возвращенном функцией ИСТОЧНИК, и возвращает позицию первого совпадения.
Обратите внимание, что это формула массива, которая требует нажатия Ctrl + Shift + Enter после завершения ввода.
Если предположить, что искомое значение находится в ячейке E1, а массив поиска - A2:A9, формула выглядит следующим образом:
=SEARCHPOS(TRUE;MATCH(A2:A9;E1);0)
На скриншоте показана формула поиска с учетом регистра в Excel:
Как видно, формула чувствительна к регистру, и по этой причине "A-201" был признан неуместным.
ПОИСКПОЗ и несколько условий
Выше мы рассмотрели функцию ПОИСКПОЗ с одним условием. На практике, однако, часто существует более одного критерия поиска. Давайте рассмотрим такой случай.
Предположим, у нас есть список продаж определенных продуктов в нескольких регионах. Нам нужно найти первый совпадающий элемент в списке для запрашиваемого продукта и заданного региона.
Вот формула SEARCHPOZ для нескольких условий:
=ПОИСКПОЗ(1;(B2:B12=G1)*(C2:C12=G2);0)
Давайте разложим это на простые коэффициенты.
Запишем наши два условия в виде выражения (B2:B12=G1)*(C2:C12=G2). Первое условие (B2:B12=G1) означает, что мы сравниваем каждое из значений в столбце 'Region' с целевым значением 'North', которое хранится в G1. Мы получаем массив {False:True:True:True:True:True:True:False:False:False:False:False}
Аналогично, мы сравниваем каждый из продуктов с "яблоками" из G2. Аналогично получаем результат {False:True:False:True:False:True:False:False:False:False:False:False}.
После перемножения этих массивов мы получаем массив единиц и нулей: {0:0:0:1:0:0:0:0:1:0:0}
Здесь единицы обозначают позиции, в которых выполняются оба условия.
Поэтому функция ПОИСКПОЗ ищет первую единицу в этом массиве. Он находит его в позиции 4.
Обратите внимание, что в наших данных есть две строки, в которых выполнены оба условия - продукт и регион. Но функция ПОИСКПОЗ ищет в массиве только первое совпадение, а затем поиск прекращается.
Я думаю, вы понимаете, что поиск по трем и более условиям может осуществляться совершенно одинаково.
Номер изделия в данном случае может быть не так важен сам по себе. Однако, зная это, мы можем использовать функцию INDEX для получения значения из любого столбца в этой позиции. Подробнее об этой теме: INDEX+POSS с несколькими условиями.
Как сравнить столбцы при помощи ПОИСКПОЗ
Проверка двух списков на совпадения и различия является одной из наиболее распространенных задач в Excel и может быть выполнена различными способами. Формула UND/SPIDER - одна из них:
IF(END(HIPP( 1-е значение из списка1 , список2 , 0)), "Отсутствует в списке 1", "")
Для каждого значения в списке 2, которого нет в списке 1, формула возвращает " Not in List 1 ".
Давайте проследим за этим шаг за шагом:
- Функция ПОИСКПОЗ ищет значение из списка 1 в списке 2. Если значение найдено, она возвращает его относительную позицию, в противном случае выдается ошибка #N/D.
- Функция END в Excel выполняет только одну задачу - проверяет ошибки #H/D (т.е. "нет в наличии"). Если обрабатываемое значение является ошибкой #N/D, то возвращается TRUE, в противном случае возвращается FALSE. В нашем случае TRUE означает, что значение из списка 1 отсутствует в списке 2 (т.е. функция ПОИСКПОЗ возвращает ошибку).
- Поскольку для вас может быть не слишком понятным отображение TRUE для значений, которые не отображаются в списке 1, вы оборачиваете функцию IF вокруг END, чтобы вместо TRUE она выводила "None in list 1" или любой другой текст, который вы хотите.
Например, чтобы сравнить значения в столбце B со значениями в столбце A, формула может быть записана следующим образом (где B2 - самая верхняя ячейка):
= IF(END(SEARCH(B2;A:A;0)); "Отсутствует в списке 1"; "")
Как вы помните, функция ПОИСКПОЗ в Excel не чувствительна к регистру. Чтобы сделать его чувствительным к регистру, вставьте функцию SEARCH (EXACT на английском) в аргумент array_search и не забудьте нажать Ctrl + Shift + Enter, чтобы ввести эту формулу массива :
{= IF(END(SEARCH(TRUE; SOURCE(A:A; B2);0)); "Не указан 1"; "")}
На рисунке ниже показаны обе формулы в действии:
Вам также может быть интересен этот ресурс: 5 способов сравнения ячеек в Excel.
Совместное использование ВПР и ПОИСКПОЗ
Этот пример предполагает, что вы уже имеете базовые знания о функции Insert в Excel . И если вы работали с функцией VRP, есть вероятность, что вы столкнулись с ее многочисленными ограничениями (подробный обзор которых можно найти в статье Почему функция VRP в Excel не работает) и ищете более надежную и всеобъемлющую альтернативу.
Один из самых раздражающих недостатков функции XRP заключается в том, что она перестает работать правильно, когда вы вставляете или удаляете столбец в таблице поиска. Это происходит потому, что функция PDF извлекает соответствующее значение на основе номера столбца поиска (индексного номера), который вы указали. Поскольку номер индекса "жестко закодирован" в формуле, Excel не может изменить его при добавлении или удалении новых столбцов в таблице.
Функция ПОИСКПОЗ в Excel работает с относительным положением искомого значения, что идеально подходит для аргумента номера столбца функции БПФ. Другими словами, вместо того, чтобы указывать возвращаемый столбец как статическое число, вы используете SEARCH для поиска текущей позиции этого столбца.
Для простоты давайте снова воспользуемся таблицей экзаменационных баллов студентов (аналогичной той, которую мы использовали в начале статьи). На этот раз, однако, мы получим фактическую оценку, а не ее относительное положение.
Если предположить, что искомое значение находится в ячейке F1, а значения таблицы - в ячейках $A$1:$C$8 (желательно зафиксировать их абсолютными ссылками на ячейки, если вы планируете копировать формулу), то выражение выглядит следующим образом
=VPR($G$1;$A$1:$C$8;2; FALSE)
Третий аргумент (номер столбца) равен 2, потому что нужный нам результат теста 1 находится во втором столбце таблицы. Как вы можете видеть на скриншоте ниже, эта простая формула БПФ работает хорошо.
Но только до тех пор, пока вы не вставляете и не удаляете столбцы:
Как вы можете видеть, если вы вставите дополнительный столбец, формула теперь вытащит неправильные данные. Он по-прежнему относится ко второй колонке, в то время как данные из теста 1 переместились в третью колонку.
Чтобы предотвратить подобные ситуации, вы можете сделать свою формулу БПФ более гибкой, включив в нее функцию SEARCHPOZ:
FINDPOZ($F$2;$A$1:$C$1;0)
Где:
- F2 - это искомое значение, которое является именем возвращаемого столбца, т.е. столбца, из которого вы хотите извлечь значение ("Тест 1" в данном примере).
- A1:C1 - это таблица поиска, содержащая заголовки таблиц.
Теперь запишите эту формулу в аргументе column_number вашей формулы VRP, например:
=VPR($G$1;$A$1:$D$8; SEARCHPOS($F$2;$A$1:$C$1;0); FALSE)
И убедитесь, что формула работает идеально, независимо от того, сколько столбцов вы добавляете или удаляете.
Вставьте колонку с итоговым тестом:
Формула автоматически изменяется и продолжает работать, возвращая правильный результат:
=VPR($G$1;$A$1:$D$8; SEARCHPOS($F$2;$A$1:$D$1;0); FALSE)
Использование ГПР и ПОИСКПОЗ
Аналогично, вы можете использовать функцию SEARCHPOZ в Excel для улучшения формул GPR. Общий принцип в основном такой же, как и для ERS: вы используете функцию SEARCH для получения относительной позиции возвращаемого столбца, и передаете это число в аргумент number_row вашей формулы GPR.
Предположим, что искомое значение находится в ячейке B5, данные таблицы - в ячейках B1:H3, имя возвращаемой строки (поисковое значение для SEARCH) - в ячейке A6, а заголовки строк - в ячейках A1:A3.
Тогда формула выглядит следующим образом:
=GPR(B5;B1:H3;SEARCHPOS(A6;A1:A3;0);FALSE)
Как вы только что убедились, сочетание FFT/GPR и SEARCHPOZ, безусловно, является улучшением по сравнению с обычными формулами. Однако функция SEARCHPOZ не устраняет всех их ограничений и недостатков. В частности, эта формула ERT по-прежнему не может выполнять "поиск слева", а PEA не может выполнять поиск в любой строке, кроме самой верхней.
Чтобы преодолеть перечисленные выше (и некоторые другие) ограничения, научитесь использовать комбинацию INDEX+SIZE, которая обеспечивает действительно мощный и универсальный способ поиска в Excel, во многом превосходящий VPR и GPR.
