Нечеткий текстовый поиск с Fuzzy Lookup в Excel
Одна из самых неприятных ситуаций, с которой может столкнуться пользователь при работе в Microsoft Excel - это поиск и подстановка данных с неточным совпадением. Когда вам надо подставить данные из одной таблицы в другую, но вы при этом уверены, что в обеих таблицах совпадающие элементы называются одинаково, то проблем нет - к вашим услугам множество способов: функции ВПР и её аналоги, надстройка Power Query и т.д.
А вот если в одной таблице "Пупкин Василий", а в другой просто "Пупкин", или "Пупкин В.", или даже "Пупкен", то все эти красивые способы не работают. Причем на практике такое встречается постоянно, особенно с почтовыми адресами или названиями компаний:
Обратите внимание на различные типы несоответствий, которые могут встречаться:
- переставлены местами улица, город, дом
- отсутствует какая-то часть адреса или, наоборот, есть что-то лишнее (индекс, номер квартиры)
- по-разному записан город (с буквой "г." или без) или улица
- опечатки и ошибки (Козань вместо Казань)
Про точное соответствие или даже поиск по маске тут говорить не приходится. Помочь в таком случае могут только специальные макросы или надстройки для Excel. Про одну из таких макро-функций на VBA я уже писал, а здесь хочется рассказать про еще один вариант решения подобной задачи - надстройку Fuzzy Lookup от компании Microsoft.
Эта надстройка существует с 2011 года и совершенно бесплатно скачивается с сайта Microsoft. Системные требования: Windows 7 или новее, Office 2007 или новее, соответственно. После установки у вас в Excel появляется одноименная вкладка с единственной кнопкой на ней:
Нажатие на эту кнопку включает специальную панель в правой части окна Excel, где и задаются все настройки поиска:
Сразу хочу отметить, что эта надстройка умеет работать только с умными таблицами, поэтому все исходные таблицы нужно конвертировать в умные с помощью сочетания Ctrl+T или кнопки Форматировать как таблицу на вкладке Главная (Home - Format as Table):
Алгоритм действий при работе с надстройкой Fuzzy Lookup следующий:
- Выберите какие таблицы нужно связать в выпадающих списках Left и Right Table.
- Выберите ключевые столбцы в левой и правой таблицах, по которым нужно проверить соответствие и нажмите кнопку для добавления созданной пары в список Match Columns
- В списке Output Columns отметьте галочками столбцы, которые вы хотите получить на выходе в качестве результата.
- Установите активную ячейку в пустое место на листе, куда вы хотите вывести данные
- Нажмите кнопку 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.
При загрузки надстройки Fuzzy Lookup от компании Microsoft возникает ошибка: "Загрузка не выполнена. Не удалось инициализировать загрузчик управляемых настроек".
Саму надстройку скачать возможно, ее даже установить получается, но вот почему-то не работает, выдает ошибку. Подскажите, пожалуйста, в чем может быть причина?
Версия программы: Office для дома и учебы 2007.
До этого в PowerQuery сводил 2 таблицы так, чтобы удобно было вручную быстро проверять адреса, чтобы из одной таблицы в другую вытащить нужную информацию.
Вот теперь жизнь упростится!
Спасибо!
И отдельное спасибо за труды по совершенствованию PLEX!
Подскажите пожалуйста в чём может быть причина.
Скачал надстройку, установил, пытаюсь подключить и ничего не происходит. Пишет дословно "Загрузка не выполнена. Произошла ошибка времени выполнения во время загрузки надстройки СОМ"
Установлен Windows 7, Office 2010.
Заранее благодарен.
TITLE: Microsoft Excel
------------------------------
Нельзя установить свойство NumberFormatLocal класса Range
------------------------------
BUTTONS:
OK
------------------------------
Если строки различаются не опечатками или склонениями (легко), то советую сначала раза 3 прогнать при включенном таком параметре, а потом выключить и прогнать хорошенько ещё пару раз.
Та же проблема в настройках COM надстройку не получается включить.
Пробовал на двух компах. На обоих office 2010, но на одном win 7 на другом win 10.
Плиззз оч надо
Подскажите пожалуйста в чём может быть причина.
Скачал надстройку, установил, пытаюсь подключить и ничего не происходит. Пишет дословно "Загрузка не выполнена. Произошла ошибка времени выполнения во время загрузки надстройки СОМ"
Установлен Windows 10, Office 2010.
Заранее благодарен.
У меня такая же проблема.
Скачал, установил, подключаю. Пишет дословно "Загрузка не выполнена. Произошла ошибка времени выполнения во время загрузки надстройки СОМ"
Установлен Windows 10, Office 2010.
Пожалуйста помогите!!!!
Подскажите, пожалуйста, а возможно ли с помощью этой надстройки получить список слов/наименований исключений.
Выбрать отсутствующие во втором списке наименования, присутствующие в первом?
С уважением, Наталия
Если делать это через Power Query, то можно попробовать при слиянии выбрать тип соединения - Анти-соединение слева (только строки из первой таблицы).
Если делать надстройкой Fuzzy Lookup, то только вручную после слияния отбирать фильтром, скорее всего.
У меня возникла проблема - есть несколько таблиц с товарами (суммарно более 100 000 строк), мне нужно сделать из них общий словарь по типам товаров ( колготки, юбка, кофта и т.д.).
Названия, естественно, разные, как и сами таблицы. (в названии не одно слово - товар, а несколько, по типу тегов)
Подскажите, как средствами fuzzy lookup или иными средствами excel это можно сделать.
Заранее спасибо за ответ!
С таким лучше на (только правила сначала почитайте).
Установить VSTO 4.0 Runtime с
Подскажите в сравниваемых столбцах есть настройка ExactMatch, как я понял это настраиваемая функция по точности совпадения. Но почему-то при указании 1 (т.е. полное совпадение) все равно проскальзывает не точность. Есть необходимость сопоставить один столбец жестко, а другой не четко). Возможно ли это сделать?