Как проверить частичные совпадения в Excel

Как

КАдам Дэвидсон

Опубликовано31 мая 2023 г.

Когда вы работаете с данными в электронной таблице, вы можете быстро заполнить огромное количество ячеек. Просмотр всех этих ячеек для поиска определенных текстовых строк — это не то, что вы хотели бы делать вручную. Но это Эксель, да? Поиск частичных совпадений должен быть простым делом.

Что ж, оказывается, это не так просто, как вы думаете. Функции, которые сразу приходят на ум, когда вы думаете о поиске текста, не всегда хорошо играйте с подстановочными знаками, которые вам нужно будет использовать для поиска частичного соответствовать.

К счастью, есть способы обойти это. На самом деле есть несколько способов. Вот как проверить частичные совпадения в Excel.

Как проверить частичные совпадения в Excel с помощью ЕСЛИ

Вы могли бы подумать, что поиск частичных совпадений в Excel будет простым случаем использования

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

Однако вы можете обойти это, комбинируя IF с другими функциями, где работают подстановочные знаки.

Чтобы проверить частичные совпадения в Excel с помощью ЕСЛИ:

1. Выберите первую ячейку, в которой вы хотите проверить частичное совпадение.
2. Введите следующую формулу, заменив В4 с ячейкой, которую вы хотите проверить на частичное совпадение, и * с текстом, который вы хотите найти, между двумя звездочками:

   =ЕСЛИ(СЧЁТЕСЛИ(B4,"*the*"),"Частичный текст найден","Нет совпадений")

   
3. Нажимать Входить и формула проверит частичное совпадение строки «the».
   
4. Чтобы применить формулу к другим ячейкам, нажмите и удерживайте маркер перетаскивания в нижнем углу ячейки.
   
5. Перетащите вниз по ячейкам, где вы хотите, чтобы формула появилась.
   
6. Отпустите мышь или трекпад, и теперь формула будет применена ко всем выбранным вами ячейкам.
   
7. Вы можете изменить «Частичный текст найден» и «Нет совпадений» на все, что пожелаете.

Как проверить частичные совпадения в Excel с помощью ПОИСКПОЗ

Вы также можете использовать формулу ПОИСКПОЗ для поиска частичных совпадений в Excel. СООТВЕТСТВОВАТЬ формула возвращает позицию первого совпадения, найденного в заданном диапазоне. Например, если текст находится в третьей ячейке вашего диапазона, формула ПОИСКПОЗ вернет 3.

Затем вы можете объединить это с ИНДЕКС функция, возвращающая строку, содержащую частичное совпадение.

Чтобы проверить частичные совпадения с помощью ПОИСКПОЗ:

1. В пустую ячейку введите часть текста, который вы хотите найти.
   
2. В следующей ячейке введите следующую формулу, заменив С4 с ячейкой, содержащей ваш частичный текст, и Б4:Б11 с диапазоном ячеек, которые вы хотите найти:

   =ПОИСКПОЗ("*"&C4&"*",B4:B11,0)

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

   =ИНДЕКС(B4:B11,ПОИСКПОЗ("*"&C4&"*",B4:B11,0))

   
5. Нажимать Входить и формула рассчитает.
   
6. Вы можете изменить ячейку, содержащую частичную текстовую строку, которую вы хотите найти, и она будет автоматически применена к вашей формуле.
   

Как проверить частичные совпадения с помощью ПОИСКА

Еще один способ проверить частичные совпадения в Excel — использовать ПОИСК функция. Эта функция возвращает позицию искомого текста в самой строке. Так, например, если неполный текст был найден в самом начале строки, он вернет 1. Если бы он был найден, начиная с четвертого символа, он вернул бы 4. Если часть текста вообще не найдена, функция ПОИСК возвращает ошибку #ЗНАЧ! ошибка.

Поскольку функция возвращает число, когда текст найден, и ошибку, когда нет, мы можем использовать метод ISNUMBER функция, чтобы определить, была ли функция найдена или нет. Соедините это с ЕСЛИ функция, и мы можем генерировать наш собственный текст, когда неполный текст найден или не найден.

Чтобы проверить частичные совпадения с помощью ПОИСК:

1. Выберите пустую ячейку, чтобы ввести формулу.
2. Введите следующую формулу, заменив В4 с расположением первой ячейки, которую вы хотите найти, и *ou* с частичным текстом, который вы хотите найти:

   =ЕСЛИ(ISNUMBER(ПОИСК("ou",B4)),"совпадает","нет совпадения")

   
3. Нажимать Входить и формула рассчитает.
   
4. Чтобы применить его к другим ячейкам, щелкните и удерживайте маркер перетаскивания и нижнюю часть ячейки.
   
5. Перетащите вниз по ячейкам, где вы хотите, чтобы формула появилась.
   
6. Отпустите, и ячейки заполнятся формулой, и произведите расчет.
   

Как проверить частичные совпадения с помощью функции ВПР

Если вы хотите проверить частичное совпадение в Excel, но вам нужно вернуть данные из другого места в той же строке, а не из самого текста, вы можете использовать метод ВПР функция. Аналогичный метод можно использовать с ГПР и XLOOKUP функции тоже.

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

Чтобы проверить частичные совпадения с помощью функции ВПР:

1. Введите часть текста, который вы хотите найти, в пустую ячейку.
   
2. В другой ячейке введите следующую формулу, заменив F4 с ячейкой, содержащей искомый текст, $B$4:$D$11 с ячейками, содержащими ваши данные, и 2 с номером столбца, из которого вы хотите вернуть значение:

   =ВПР("*"&$F$4&"*",$B$4:$D$11,2,ЛОЖЬ)

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

Поиск результатов в Excel

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

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

И если ваши сложные формулы дают ошибки, вы можете узнать, как исправить проблемы со ссылками на формулы Excel.