Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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

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

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


На листе "Заказ" в этом файле таблица, полученная от заказчика, а на листе "Каталог" наши артикулы.
Сама формула на примере файла будет выглядеть так:

=ПРОСМОТР(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) старается преобразовать непосредственно в массив любое выражение, записанное вторым аргументом.
  3. Этими особенностями мы и будем пользоваться.

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

  1. =ПРОСМОТР(2;1/ПОИСК(Каталог!$A$2:$A$11;A2);Каталог!$A$2:$A$11)
  2. =ПРОСМОТР(2;
    1/{55:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};
    Каталог!$A$2:$A$11)
  3. =ПРОСМОТР(2;{0,0181818181818182:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!};Каталог!$A$2:$A$11)
  4. =ПРОСМОТР(2;
    1;
    {"FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX292710"})
  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.

Так же см.:
Сравнение текста по части предложения
Что такое формула массива
Как найти значение в другой таблице или сила ВПР
ВПР с поиском по нескольким листам


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 14 комментариев
  1. Анна:

    Круто! Огромное спасибо!

  2. Дмитрий:

    Как же долго я это искал! Спасибо огромное! К сожалению не смог закинуть "Спасибо", выдает ошибку и на визу и на кошелек.
    Просто бомба! ЕЩЕ РАЗ СПАСИБО!

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти