Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Очистка данных и Power Query

Нечеткое слияние в Power Query: как сопоставить данные с ошибками в Excel

forest_paw 05.03.2026 728

Нечеткое слияние в Power Query: как сопоставить данные с ошибками в Excel

Если значения в двух списках различаются из-за опечаток, перестановки слов или сокращений, можно попробовать нечёткое слияние (Fuzzy Merge) в Power Query. В отличие от точного поиска ВПР (VLOOKUP) или ПРОСМОТРX (XLOOKUP), оно сравнивает сходство текста. Результаты нужно проверять. Ниже описан Excel для Microsoft 365 для Windows с доступным флажком нечёткого соответствия; наличие Power Query в другой версии само по себе не гарантирует эту возможность.

Эта инструкция подойдет тем, кто занимается обработкой данных в Excel, очисткой таблиц и хочет быстрее подготовить данные к анализу и отчетности.

Рассмотрим типичный кейс: у вас есть два списка сотрудников — один официальный (Фамилия, Имя), а второй — с данными по формам, где имена могут быть записаны неформально или с ошибками. Задача — найти, кто не сдал форму.

Столбец A содержит Фамилию, Имя. Столбец D содержит Имя Фамилия. Дополнительная таблица в столбцах G и H показывает синонимы. Например, Edward и Ed — это одно и то же. Emmanuelle и Manny — это одно и то же.
Столбец A содержит Фамилию, Имя. Столбец D содержит Имя Фамилия. Дополнительная таблица в столбцах G и H показывает синонимы. Например, Edward и Ed — это одно и то же. Emmanuelle и Manny — это одно и то же.

Для сокращений задайте таблицу преобразований, например «William» → «Bill», со столбцами From и To именно с такими заголовками и текстовым типом. В этом примере исходные диапазоны преобразуем в таблицы Excel сочетанием Ctrl+T; сравниваемым столбцам в Power Query также задайте тип «Текст».

Пошаговая инструкция по настройке нечеткого слияния

  1. Создайте подключения в Power Query. Для каждой таблицы (например, «Census», «Forms», «Nicknames») выберите ячейку внутри нее, перейдите на вкладку «Данные» → «Из таблицы/диапазона». В редакторе Power Query нажмите «Закрыть и загрузить в…» и выберите «Только создать подключение».
В редакторе Power Query на вкладке 'Главная' первая кнопка — 'Закрыть и загрузить'. Нижняя часть кнопки — раскрывающееся меню. Выберите 'Закрыть и загрузить в...', чтобы открыть диалоговое окно 'Импорт данных'.
В редакторе Power Query на вкладке 'Главная' первая кнопка — 'Закрыть и загрузить'. Нижняя часть кнопки — раскрывающееся меню. Выберите 'Закрыть и загрузить в...', чтобы открыть диалоговое окно 'Импорт данных'.
Первые четыре варианта в диалоговом окне 'Импорт данных': 'Таблица', 'Сводная таблица', 'Сводная диаграмма', 'Только создать подключение'. В данном случае выберите 'Только создать подключение'.
Первые четыре варианта в диалоговом окне 'Импорт данных': 'Таблица', 'Сводная таблица', 'Сводная диаграмма', 'Только создать подключение'. В данном случае выберите 'Только создать подключение'.

После создания всех трех подключений они появятся на панели «Запросы и подключения» как «Только подключение».

На панели 'Запросы и подключения' перечислены три запроса типа 'Только подключение': Census, Forms и Nicknames.
На панели 'Запросы и подключения' перечислены три запроса типа 'Только подключение': Census, Forms и Nicknames.
  1. Запустите слияние. Откройте запрос Census в редакторе Power Query. На вкладке «Главная» выберите «Объединить» → «Объединить запросы» → «Объединить запросы как новые» (Merge Queries as New).
  2. Настройте диалоговое окно «Слияние».
    • Выберите первую таблицу (например, «Census») и ключевое поле (например, «Employee Name»).
    • Выберите вторую таблицу (например, «Forms») и соответствующее ключевое поле (например, «Name»).
    • Для «Тип соединения» выберите «Левое внешнее (все из первой, соответствующие из второй)».
    • Установите флажок «Использовать нечеткое соответствие для выполнения слияния».
Диалоговое окно 'Слияние' с множеством настроек: выбор таблиц, полей, типа соединения 'Левое внешнее', включение нечеткого соответствия, настройка порога сходства (по умолчанию 0.8), выбор таблицы преобразований Nicknames.
Диалоговое окно 'Слияние' с множеством настроек: выбор таблиц, полей, типа соединения 'Левое внешнее', включение нечеткого соответствия, настройка порога сходства (по умолчанию 0.8), выбор таблицы преобразований Nicknames.
  1. Настройте параметры нечеткого соответствия. Раскройте меню «Параметры нечеткого соответствия». Ключевые настройки:
    • Порог сходства: По умолчанию 0,8 по шкале от 0 до 1. Это мера сходства, а не вероятность верного сопоставления. Можно уменьшить до 0,7 или 0,6 для большего числа совпадений, но осторожно — слишком низкое значение может привести к ложным срабатываниям.
    • Таблица преобразований: Укажите запрос с таблицей From/To (например, «Nicknames»). Чтобы ограничить число кандидатов, задайте максимальное число совпадений: например, 1 для одного результата на строку. Это не гарантирует взаимно однозначного сопоставления людей; без ограничения одна строка может дать несколько результатов.
  2. Нажмите «ОК». В редакторе Power Query появится новая таблица. Щелкните значок развертывания (две стрелки) в столбце «Forms», чтобы выбрать, какие поля из второй таблицы добавить в результат. Снимите флажок «Использовать исходное имя столбца в качестве префикса», чтобы избежать длинных имен.
Результат в редакторе Power Query: три столбца. В столбце Forms в каждой строке указано 'Таблица'. Используйте значок развертывания справа от заголовка Forms, чтобы выбрать поля для отображения, например, Names и Forms Received Date.
Результат в редакторе Power Query: три столбца. В столбце Forms в каждой строке указано 'Таблица'. Используйте значок развертывания справа от заголовка Forms, чтобы выбрать поля для отображения, например, Names и Forms Received Date.
  1. Нажмите «Главная» → «Закрыть и загрузить», чтобы вывести итоговую таблицу на новый или существующий лист.
Результат сопоставления. Ed Ackman сопоставлен с Ackman, Edward. Несколько сотрудников все еще не сопоставлены.
Результат сопоставления. Ed Ackman сопоставлен с Ackman, Edward. Несколько сотрудников все еще не сопоставлены.

Советы и проверка результатов

Проверка результата: В исходном левом соединении Census → Forms после развёртывания отберите строки, где идентификатор найденной формы равен null: это кандидаты в список не сдавших форму. Отсутствие совпадения по имени ещё не доказывает, что человек не ответил. Для отдельного списка несопоставленных записей Forms выполните соединение Forms → Census типа «Левое антисоединение» (Left Anti). Проверьте неоднозначные имена и, если возможно, сверяйте их по уникальным идентификаторам.

Настройка точности: Экспериментируйте с «Порогом сходства». Более низкое значение найдет больше совпадений, но повысит риск ошибок.

Автоматизация: По мере поступления новых данных просто используйте «Данные» → «Обновить все», чтобы автоматически перезапустить процесс нечеткого слияния с обновленными списками.

Заключение

Несмотря на кажущуюся сложность, нечеткое слияние в Power Query — это незаменимый инструмент для работы с «грязными» данными, где точное совпадение невозможно. Он экономит часы ручной работы по сверке списков с опечатками, разными форматами имен или сокращениями, обеспечивая гибкость и возможность автоматической актуализации результатов.

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.