Найти в ячейке любое слово из списка

Найти в ячейке любое слово из списка

Предположим, вы получаете от поставщика/заказчика/клиента заполненную таблицу с перечнем продукции:

и эту таблицу необходимо сравнить с артикулами/кодами товара в имеющемся у вас каталоге продукции:

Как видно - в нашем каталоге только артикулы без наименований. У заказчика же помимо артикулов еще и название товара, т.е. много лишнего. И вам надо понять какие товары присутствуют в вашем каталоге, а какие нет:

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

Tips_All_AnyoneOfArray.xls (49,5 KiB, 23 318 скачиваний)

На листе "Заказ" в этом файле таблица, полученная от заказчика, а на листе "Каталог" наши артикулы. Сама формула на примере файла будет выглядеть так: =ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2 );Каталог!$A$2:$A$11) =LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11) эта формула вернет название артикула, если в тексте есть хоть один артикул из каталога и #Н/Д (#N/A) если артикул не найден в каталоге. Прежде чем облагородить эту формулу всякими дополнениями(вроде виде убирания ненужных #Н/Д (#N/A) ) давайте разберемся как она работает. Кратко о принципе работы функция ПРОСМОТР (LOOKUP) : она берет значение, заданное первым аргументом и ищет его в массиве(диапазоне) из второго аргумента. Как только находит - запоминает его позицию(строку, если угодно) и в итоге возвращает значение, расположенное в этой же позиции в массиве третьего аргумента. Но у функции есть пара особенностей:

  1. Она ищет не обязательно именно точное совпадение с искомым, а максимально приближенное к нему. Если точнее - равное или большее искомому.
  2. Функция ПРОСМОТР (LOOKUP) старается преобразовать непосредственно в массив любое выражение, записанное вторым аргументом.

Как уже упомянул выше - в качестве диапазона для поиска значения обычно приводится массив ячеек, но т.к. функция ПРОСМОТР (LOOKUP) старается преобразовать непосредственно в массив любое выражение - она вычисляет данное ей выражение 1/ПОИСК(Каталог!$A$2:$A$11;A2) , работая в итоге с результатами этого вычисления. Само же выражение работает следующим образом: ПОИСК(Каталог!$A$2:$A$11;A2) ищет поочередно каждое значение из списка Каталога в ячейке A2 (в наименовании артикула из таблицы Заказчика). Если значение найдено, то возвращается номер позиции первого символа найденного значения(т.е. какое-то число). Если значение не найдено - возвращается значение ошибки #ЗНАЧ! (#VALUE!) . Т.е. получается следующий массив: По идее, после того, как функция ПРОСМОТР (LOOKUP) вычислит это выражение, она будет последовательно просматривать результаты функции ПОИСК (SEARCH) в поисках заданного нами искомого значения, пока не найдет значение максимально близкое к искомому(оптимально равное или чуть больше него). Но т.к. функция ПОИСК (SEARCH) может вернуть неизвестно какие по величине значения, мы, чтобы не гадать и не думать над тем, какое число задать для поиска, сначала единицу делим на выражение ПОИСК(Каталог!$A$2:$A$11;A2) , чтобы получить массив вида: А в качестве искомого значения мы подсовываем функции ПРОСМОТР (LOOKUP) число 2 - т.е. заведомо большее число, чем может вообще встретиться в массиве(т.к. единица, поделенная на любое число будет меньше двух), чем заставляем её найти последнее подходящее совпадение из каталога(т.е. последнее не ошибочное значение). После этого функция ПРОСМОТР (LOOKUP) запомнит эту позицию и вернет значение из массива Каталог!$A$2:$A$11 (третий аргумент), записанное в этом массиве для этой позиции. Вы можете просмотреть этапы вычисления функции самостоятельно для каждой ячейки, я здесь просто приведу этапы чуть в расширенном для понимания виде:

  1. =ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11; A2 );Каталог!$A$2:$A$11)
  2. =ПРОСМОТР(2; 1/; Каталог!$A$2:$A$11)
  3. =ПРОСМОТР(2;;Каталог!$A$2:$A$11)
  4. =ПРОСМОТР(2; 1; )
  5. ="FM2-3320"

Теперь немного облагородим функцию и сделаем еще пару реализаций Реализация 1: Вместо артикулов и #Н/Д (#N/A) выведем для найденных позиций "Есть" , а для отсутствующих "Не найден в каталоге" : =ЕСЛИ(ЕНД(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2)));"Не найден в каталоге";"Есть") =IF(ISNA(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2))),"Не найден в каталоге","Есть") работа функции проста - с ПРОСМОТР(LOOKUP) разобрались, поэтому остались только ЕНД и ЕСЛИ. ЕНД (ISNA) проверяет вложенное в неё выражение на предмет ошибки #Н/Д (#N/A) . Если ошибка есть - ЕНД возвращает ИСТИНА (TRUE) , а если ошибки нет - ЛОЖЬ (FALSE) . ЕСЛИ (IF) проверяет выражение из первого аргумента на выполнение. Если выражение выполняется(т.е. оно равно ИСТИНА (TRUE) ), то функция запишет значение из второго аргумента( "Не найден в каталоге" ). А если не выполняется(ЛОЖЬ (FALSE) ) - то значение из третьего( "Есть" ). Т.е. все вместе получается: если наша основная функция возвращает значение ошибки #Н/Д (#N/A) , то мы записываем в ячейку "Не найден в каталоге" , в противном случае - "Есть" .

Реализация 2: Вместо #Н/Д выведем "Не найден в каталоге" , но при этом если артикулы найдены - выведем названия этих артикулов: =ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11);"Нет в каталоге") =IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$A$2:$A$11),"Нет в каталоге") Про функция ЕСЛИОШИБКА (IFERROR) я подробно рассказывал в этой статье: Как в ячейке с формулой вместо ошибки показать 0. Если вкратце, то функция проверяет выражение, записанное первым аргументом(наша основная функция). Если в итоге получаем любую ошибку, то ЕСЛИОШИБКА запишет значение из второго аргумента(в нашем случае это текст "Не найден в каталоге" ). Если же выражение(проверяемая функция) не возвращает ошибку, то функция ЕСЛИОШИБКА запишет именно то значение, которое было получено проверяемой функцией(в нашем случае это будет наименование артикула).

Реализация 3 Надо не просто определить какому артикулу соответствует, но и вывести цену для наименования по этому артикулу(сами цены должны быть расположены в столбце B листа Каталог): =ЕСЛИОШИБКА(ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$B$2:$B$11);"") =IFERROR(LOOKUP(2,1/SEARCH(Каталог!$A$2:$A$11,A2),Каталог!$B$2:$B$11),"")

Пара важных замечаний:

  • данные на листе с артикулами не должны содержать пустых ячеек . Иначе с большой долей вероятности формула будет возвращать значение именно пустой ячейки, а не то, которое подходит под условия поиска
  • формула осуществляет поиск таким образом, что ищется любое совпадение. Например, в качестве артикула записана цифра 1, а в строке наименований может встречаться помимо целой 1 еще и 123, 651123, FG1412NM и т.п. Для всех этих наименований может быть подобран артикул 1, т.к. он содержится в каждом наименовании. Как правило это может произойти, если артикул 1 расположен в конце списка

Поэтому желательно перед использованием формулы отсортировать список по возрастанию(от меньшего к большему, от А до Я).

В приложенном в начале статьи примере вы найдете все разобранные варианты.

Если же вам понадобится выводить все наименования, то можно воспользоваться функцией СОДЕРЖИТ_ОДНО_ИЗ из моей надстройки MulTEx.

📎📎📎📎📎📎📎📎📎📎