«ВПР» по частичному совпадению

Спустя катастрофически большой промежуток времени с момента публикации моего последнего поста, решил поделиться супер крутой, на мой взгляд, Excel-формулой, узнав о которой, начинаешь удивляться, как же раньше-то я жил без нее. Но, должен сказать, авторство ее создания не мое, а вероятнее всего принадлежит англоязычному ресурсу, о котором я скажу ниже.
Кто более-менее часто работает с массивами данных в Excel почти наверняка знает про функцию ВПР (см. мою статью) или ИНДЕКС+ПОИСКПОЗ, которые решают достаточно частую задачу по объединению двух наборов данных по каким-либо совпадающим значениям. И действительно, использование этих функций решает задачи по сопоставлению и объединению данных в 90% случаев. Если бы не одно но — данные, по которым производится объединение, действительно должны именно совпадать. Но бывают случаи, когда требуется сопоставление по частичному совпадению. Да, в ВПР есть поиск по приблизительному совпадению, но работает он не совсем прозрачно, а потому предугадать, почему было подобрано одно похожее слово, а не другое, может быть не просто. Как вы поняли, эту прелюдию я затеял не просто так, а для того, чтобы рассказать, как же решить такую задачу при помощи Excel.

Предположим, у нас есть список товаров, которые надо как-то сгруппировать:

Список намеренно «кривой» в плане однородности данных — бренд то в начале, то в конце, текст то с большой, то с маленькой буквы. В реальных задачах, увы, так обычно и бывает. Поставим теперь себе задачу для каждой записи этого неструктурированного списка сопоставить вид товара (бытовая техника или продукты) и выдернуть из текста название производителя, которое нам известно.
Первым делом нам надо сделать пару таблиц.
Таблица, сопоставляющая ключевое слово виду товара:

Т.е. нам необходимо позаботиться о списке ключевых слов, по которым мы будем определять вид товара. Здесь намеренно в слове Яблок оставлена только постоянная часть без окончания, которая есть во всех описаниях товаров. Также обращаю ваше внимание, для той формулы, которую мы напишем, регистр не имеет значения.
Далее составим список известных производителей:

К сожалению, функции ВПР и ИНДЕКС+ПОИСКПОЗ тут оказываются бессильными.
Перерыв весь российский интернет, я не смог найти решения. На тематических форумах мне рекомендовали для решения этой задачи воспользоваться VBA, заплатить знающему человеку за макрос и тому подобное. Я чувствовал, что решение где-то рядом и для него могут пригодиться формулы массивов, но самостоятельно решить эту проблему у меня все никак не получалось, пока не нагуглил аналогичную задачу в англоязычном интернете, там решение нашлось очень быстро на интересном сайте, посвященном работе с Excel, который с радостью вам рекомендую — Exceljet.
Приведу сразу готовое решение применительно к нашей задаче, эта бронебойная формула выглядит следующим образом:
{=ЕСЛИОШИБКА(ИНДЕКС(Виды;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(Виды[Товар];[@Товары]));0);2);"")}
Она же на картинке:

Напоминаю, что для формул массива, после их ввода, необходимо нажимать комбинацию клавиш Ctrl+Shift+Enter, после чего у формулы по бокам появляются фигурные скобки.
Как же работает эта формула? Для начала подсветим здесь переменные:

Чтобы не писать тонну текста, приведу короткую гифку с описанием шагов вычисления этой формулы (обновление кадров на ней довольно медленное, специально для того, чтобы можно было успеть понять, что происходит):

Формула для определения производителя будет аналогичной, но т.к. там всего один столбец, то номер столбца в ПОИСКе будет 1, а не 2:
{=ЕСЛИОШИБКА(ИНДЕКС(Бренды;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(Бренды[Бренд];[@Товары]));0);1);"")}
Как это часто бывает, изучив какой-то инструмент, начинаешь находить ему применение во множестве областей, так вышло и у меня. В следующей статье я расскажу об интересной практической задаче, которую можно решить, используя эту потрясающую формулу.
PS
В рамках данной статьи предполагается наличие знаний об умных таблицах в Excel и о том, как их именовать и использовать в формулах. Данная формула может быть также эффективно использована и без умных таблиц, но их использование сильно упрощает чтение и построение сложных формул в Excel. Так что всем, кто ими еще не пользуется, всячески рекомендую ознакомиться с ними, тем более в последних версиях Excel они предоставляют мощные возможности по объединению данных, почти как в Access.

2 thoughts on “«ВПР» по частичному совпадению

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *