Блог

Главная - Вопросы по программированию - Создание формулы множественного поиска / сопоставления в Excel

Создание формулы множественного поиска / сопоставления в Excel

#excel #excel-formula

#excel #excel-формула

Вопрос:

Предполагается простая рабочая книга с двумя листами. На обоих листах A и B есть столбец ID, который мне сначала нужно сопоставить. На листе B есть два дополнительных столбца текста. Целью формулы было бы предоставить простое значение Y / N на листе A, если 1) Лист B имеет идентификатор, соответствующий строке на листе A, и любой из текстовых столбцов этой соответствующей строки содержит некоторый статический текст. Статическим текстом для поиска в этом примере будет слово «слепой». Я пытался использовать несколько итераций функций VLOOKUP, MATCH amp; INDEX, но смог получить успешное 1-мерное совпадение только по идентификатору.

ЛИСТ A: столбец ID и желаемый столбец Y / N из запрошенной формулы. Кроме того, столбец примечаний, объясняющий, почему Y / N следует оценивать на основе данных на листе B 

 ID   |  MATCHED BY FORMULA  |  WHY
--------------------------------------------
1    |  Y                   | ID and text match
2    |  N                   | NO ID match
3    |  N                   | NO text match
4    |  N                   | NO ID match
5    |  N                   | NO text match

ЛИСТ B: также содержит идентификатор для сопоставления с листом A. Он также содержит два текстовых столбца, любой из которых (или оба) может содержать статический текст («слепой»), который ищется по формуле. Текст для поиска должен существовать в строке и не обязательно должен точно соответствовать всей строке. 

 ID   | TEXT1      |  TEXT2
--------------------------------
1    | three      | blind, mice
3    | mice       | see
5    | how, mice  | they
7    | run        | three
9    | blind      | mice

Ответ №1:

Вы можете использовать SUMPRODUCT функцию:

 =IF(SUMPRODUCT((A2=$G$2:$G$6)*(($B$10=$H$2:$I$6))),"Y","N")

введите описание изображения здесь

Редактировать

Чтобы найти частичные совпадения строки, вам нужно использовать SEARCH функцию:

 =IF(SUMPRODUCT((A2=$G$2:$G$6)*ISNUMBER(SEARCH($B$10,$H$2:$I$6))),"Y","N")

Комментарии:

1. Спасибо! Это близко. Одна вещь, о которой я не упомянул в своем посте, заключается в том, что мне нужно найти подстроки. Таким образом, текст может быть «слепым» или «слепые мыши». Для совпадения должно быть достаточно слова «слепой». Я попытался настроить формулу для использования поиска, но безрезультатно.

Ответ №2:

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

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

Лист А

Лист B

Лист формул

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