Нечеткий текстовый поиск с Fuzzy Lookup в Excel

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

А вот если в одной таблице "Пупкин Василий", а в другой просто "Пупкин", или "Пупкин В.", или даже "Пупкен", то все эти красивые способы не работают. Причем на практике такое встречается постоянно, особенно с почтовыми адресами или названиями компаний:

Неточный поиск адресов

Обратите внимание на различные типы несоответствий, которые могут встречаться:

  • переставлены местами улица, город, дом
  • отсутствует какая-то часть адреса или, наоборот, есть что-то лишнее (индекс, номер квартиры)
  • по-разному записан город (с буквой "г." или без) или улица
  • опечатки и ошибки (Козань вместо Казань)

Про точное соответствие или даже поиск по маске тут говорить не приходится. Помочь в таком случае могут только специальные макросы или надстройки для Excel. Про одну из таких макро-функций на VBA я уже писал, а здесь хочется рассказать про еще один вариант решения подобной задачи - надстройку Fuzzy Lookup от компании Microsoft.

Эта надстройка существует с 2011 года и совершенно бесплатно скачивается с сайта Microsoft. Системные требования: Windows 7 или новее, Office 2007 или новее, соответственно. После установки у вас в Excel появляется одноименная вкладка с единственной кнопкой на ней:

Кнопка надстройки Fuzzy Lookup

Нажатие на эту кнопку включает специальную панель в правой части окна Excel, где и задаются все настройки поиска:

Неточный поиск в Fuzzy Lookup

Сразу хочу отметить, что эта надстройка умеет работать только с умными таблицами, поэтому все исходные таблицы нужно конвертировать в умные с помощью сочетания Ctrl+T или кнопки Форматировать как таблицу на вкладке Главная (Home - Format as Table):

Алгоритм действий при работе с надстройкой Fuzzy Lookup следующий:

  1. Выберите какие таблицы нужно связать в выпадающих списках Left и Right Table.
  2. Выберите ключевые столбцы в левой и правой таблицах, по которым нужно проверить соответствие и нажмите кнопку для добавления созданной пары в список Match Columns
  3. В списке Output Columns отметьте галочками столбцы, которые вы хотите получить на выходе в качестве результата.
  4. Установите активную ячейку в пустое место на листе, куда вы хотите вывести данные
  5. Нажмите кнопку Go

После анализа мы получаем таблицу, где каждому элементу ключевого столбца из первой таблицы подобрано максимально похожее значение из второй:

Результат

Лепота!

Нюансы и подводные камни

  • Точность подбора можно регулировать с помощью ползунка Similarity Threshold в нижней части панели Fuzzy Lookup. Чем правее его положение, тем строже будет поиск, и - как следствие - тем меньше результатов надстройка будет находить. Если сдвинуть его влево, то результатов станет больше, но возрастет риск ошибочного совпадения. Тут все зависит от вашей конкретной ситуации - экспериментируйте.
  • На больших таблицах поиск может занимать приличное количество времени (до нескольких десятков секунд), хотя многое, конечно, зависит от мощности вашего компьютера. Как вариант, для ускорения в настройках (кнопка Configure в нижней части панели) можно попробовать включить параметр UseApproximateIndexing в разделе Global Settings.
  • Перед нажатием на кнопку Go не забудьте выделить пустую ячейку, начиная с которой вы хотите вывести результаты. Если случайно вы оставите активную ячейку где-нибудь в исходных данных, то надстройка выведет итоговую таблицу прямо поверх них, и вы их потеряете. Причем отмена последнего действия будет невозможна, а кнопка Undo в нижней части панели не всегда срабатывает почему-то.
  • Для вывода столбца с коэффициентом подобия FuzzyLookup.Similarity необходимо, чтобы у вашего Excel была точка в качестве десятичного разделителя (целой и дробной части). Если это не так, то эту настройку временно можно поменять через Файл - Параметры - Дополнительно (File - Options - Advanced).
  • Fuzzy Lookup - это не обычная надстройка, написанная на VBA (как мой PLEX, например), а COM-надстройка. Разница в том, что она устанавливается как отдельная программа, т.е. вам нужны соответствующие права на установку ПО на вашем компьютере. Дома, ясное дело, проблем не будет, а вот многим корпоративным пользователям, скорее всего, придется обращаться к вашим айтишникам. После установки отключать и подключать ее в дальнейшем можно на вкладке Разработчик - Надстройки COM (Developer - COM Add-ins).

В любом случае, при всех имеющихся минусах, эта надстройка однозначно стоит того, чтобы находиться в арсенале любого продвинутого пользователя Microsoft Excel.

Ссылки по теме




18.06.2018 16:07:20
Здравствуйте, Николай!

При загрузки надстройки Fuzzy Lookup от компании Microsoft возникает ошибка: "Загрузка не выполнена. Не удалось инициализировать загрузчик управляемых настроек".
Саму надстройку скачать возможно, ее даже установить получается, но вот почему-то не работает, выдает ошибку. Подскажите, пожалуйста, в чем может быть причина?
Версия программы: Office для дома и учебы 2007.
19.06.2018 11:41:57
Рекомендую, для начала, попробовать на другом ПК. Что-то с Windows, скорее всего :(
26.06.2018 14:16:43
Очень крутая штука. Сам пользуюсь.
29.06.2018 17:24:20
Просто Бомба! Как мне её не хватало. Нужна редко, но когда нужна - ОЧЕНЬ НУЖНА!

До этого в PowerQuery сводил 2 таблицы так, чтобы удобно было вручную быстро проверять адреса, чтобы из одной таблицы в другую вытащить нужную информацию.

Вот теперь жизнь упростится!

Спасибо!
04.07.2018 12:01:25
Николай, мы - активные пользователи Excel, как всегда "в тренде" благодаря вашим наглядным и полезным статьям!
И отдельное спасибо за труды по совершенствованию PLEX!
05.07.2018 09:12:40
Не за что, Михаил! Всегда рад помочь :)
05.07.2018 06:26:21
ни чего себе, контент бомба, первый раз решил пошарить сайт, теперь буду частым поситителем
17.07.2018 13:58:38
Здравствуйте Николай!
Подскажите пожалуйста в чём может быть причина.
Скачал надстройку, установил, пытаюсь подключить и ничего не происходит. Пишет дословно "Загрузка не выполнена. Произошла ошибка времени выполнения во время загрузки надстройки СОМ"

Установлен Windows 7, Office 2010.

Заранее благодарен.
25.07.2018 12:47:02
Пользуюсь активно уже год. Надстройка вещь!;)
25.07.2018 18:02:59
Приветствую. Нужна помощь по данной надстройке. Я с её помощью обновляю прайс-лист в интернет-магазине. Довольно часто возникает проблема в результате обработки с добавлением лишних регистров в число. Таким образом цены в прайс-листе значительно искажаются.  
14.08.2018 01:45:25
Скачал.Установил. В больших таблицах (например, 150 строк) почему-то сверяет и выводит результат только первых 10 строк. Может я что то не так делаю? Винд 7, Офис 2013. Спасибо.
14.08.2018 14:33:20
То же самое, потом выводит сообщение

TITLE: Microsoft Excel
------------------------------

Нельзя установить свойство NumberFormatLocal класса Range

------------------------------
BUTTONS:

OK
------------------------------
23.08.2018 17:48:11
Попробуйте установить в настройках Excel точку в качестве разделителя целой и дробной части (Файл - Параметры).
10.10.2018 18:57:42
Классная штука. Текстовые совпадения определяются согласно алгоритму Левенштейна, а если стоит задача сопоставить еще и столбцы с числами? Не нашел такой настройки
07.11.2018 10:10:51
Как то мне в голову пришла идея, зашифровать текст или цифры чтобы никто кроме меня не смог прочитать, нужная штука иногда. и тут меня осенило, так ведь с этим с легкостью справится ексель в обе стороны. есть какие нибудь идеи как это сделать?
Наверх