Нечеткое слияние в Power Query: как сопоставить данные с ошибками в Excel
Если значения в двух списках различаются из-за опечаток, перестановки слов или сокращений, можно попробовать нечёткое слияние (Fuzzy Merge) в Power Query. В отличие от точного поиска ВПР (VLOOKUP) или ПРОСМОТРX (XLOOKUP), оно сравнивает сходство текста. Результаты нужно проверять. Ниже описан Excel для Microsoft 365 для Windows с доступным флажком нечёткого соответствия; наличие Power Query в другой версии само по себе не гарантирует эту возможность.
Эта инструкция подойдет тем, кто занимается обработкой данных в Excel, очисткой таблиц и хочет быстрее подготовить данные к анализу и отчетности.
Рассмотрим типичный кейс: у вас есть два списка сотрудников — один официальный (Фамилия, Имя), а второй — с данными по формам, где имена могут быть записаны неформально или с ошибками. Задача — найти, кто не сдал форму.
Для сокращений задайте таблицу преобразований, например «William» → «Bill», со столбцами From и To именно с такими заголовками и текстовым типом. В этом примере исходные диапазоны преобразуем в таблицы Excel сочетанием Ctrl+T; сравниваемым столбцам в Power Query также задайте тип «Текст».
Пошаговая инструкция по настройке нечеткого слияния
- Создайте подключения в Power Query. Для каждой таблицы (например, «Census», «Forms», «Nicknames») выберите ячейку внутри нее, перейдите на вкладку «Данные» → «Из таблицы/диапазона». В редакторе Power Query нажмите «Закрыть и загрузить в…» и выберите «Только создать подключение».
После создания всех трех подключений они появятся на панели «Запросы и подключения» как «Только подключение».
- Запустите слияние. Откройте запрос Census в редакторе Power Query. На вкладке «Главная» выберите «Объединить» → «Объединить запросы» → «Объединить запросы как новые» (Merge Queries as New).
- Настройте диалоговое окно «Слияние».
- Выберите первую таблицу (например, «Census») и ключевое поле (например, «Employee Name»).
- Выберите вторую таблицу (например, «Forms») и соответствующее ключевое поле (например, «Name»).
- Для «Тип соединения» выберите «Левое внешнее (все из первой, соответствующие из второй)».
- Установите флажок «Использовать нечеткое соответствие для выполнения слияния».
- Настройте параметры нечеткого соответствия. Раскройте меню «Параметры нечеткого соответствия». Ключевые настройки:
- Порог сходства: По умолчанию 0,8 по шкале от 0 до 1. Это мера сходства, а не вероятность верного сопоставления. Можно уменьшить до 0,7 или 0,6 для большего числа совпадений, но осторожно — слишком низкое значение может привести к ложным срабатываниям.
- Таблица преобразований: Укажите запрос с таблицей From/To (например, «Nicknames»). Чтобы ограничить число кандидатов, задайте максимальное число совпадений: например, 1 для одного результата на строку. Это не гарантирует взаимно однозначного сопоставления людей; без ограничения одна строка может дать несколько результатов.
- Нажмите «ОК». В редакторе Power Query появится новая таблица. Щелкните значок развертывания (две стрелки) в столбце «Forms», чтобы выбрать, какие поля из второй таблицы добавить в результат. Снимите флажок «Использовать исходное имя столбца в качестве префикса», чтобы избежать длинных имен.
- Нажмите «Главная» → «Закрыть и загрузить», чтобы вывести итоговую таблицу на новый или существующий лист.
Советы и проверка результатов
Проверка результата: В исходном левом соединении Census → Forms после развёртывания отберите строки, где идентификатор найденной формы равен null: это кандидаты в список не сдавших форму. Отсутствие совпадения по имени ещё не доказывает, что человек не ответил. Для отдельного списка несопоставленных записей Forms выполните соединение Forms → Census типа «Левое антисоединение» (Left Anti). Проверьте неоднозначные имена и, если возможно, сверяйте их по уникальным идентификаторам.
Настройка точности: Экспериментируйте с «Порогом сходства». Более низкое значение найдет больше совпадений, но повысит риск ошибок.
Автоматизация: По мере поступления новых данных просто используйте «Данные» → «Обновить все», чтобы автоматически перезапустить процесс нечеткого слияния с обновленными списками.
Заключение
Несмотря на кажущуюся сложность, нечеткое слияние в Power Query — это незаменимый инструмент для работы с «грязными» данными, где точное совпадение невозможно. Он экономит часы ручной работы по сверке списков с опечатками, разными форматами имен или сокращениями, обеспечивая гибкость и возможность автоматической актуализации результатов.