Хитрости »

Основные понятия (23) Сводные таблицы и анализ данных (9) Графики и диаграммы (5) Работа с VB проектом (12) Power BI и Power Query (14)

Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (63) Разное (39) Баги и глюки Excel (3)

---

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

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

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

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

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

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

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

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

Реализация 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 Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки