Нечеткое слияние в СТЕПЕНЬ (Power) Query: как сопоставить данные с ошибками в Excel
Когда стандартные функции Excel, такие как ВПР (VLOOKUP) или ПРОСМОТРХ (XLOOKUP), не справляются из-за различий в форматах данных, на помощь приходит нечеткое слияние (Fuzzy ПОИСКПОЗ (Match)) в СТЕПЕНЬ (Power) Query. Этот мощный инструмент позволяет находить соответствия даже при наличии опечаток, разных форматов имен или сокращений.
Рассмотрим типичный кейс: у вас есть два списка сотрудников — один официальный (Фамилия, Имя), а второй — с данными по формам, где имена могут быть записаны неформально или с ошибками. Задача — найти, кто не сдал форму.
Для повышения точности сопоставления используется таблица преобразований (например, «William» → «Bill»). Все исходные диапазоны данных необходимо сначала преобразовать в таблицы Excel, нажав Ctrl + Т (T).
Пошаговая инструкция по настройке нечеткого слияния
- Создайте подключения в СТЕПЕНЬ (Power) Query. Для каждой таблицы (например, «Census», «Forms», «Nicknames») выберите ячейку внутри нее, перейдите на вкладку «Данные» → «Из таблицы/диапазона». В редакторе СТЕПЕНЬ (Power) Query нажмите «Закрыть и загрузить в…» и выберите «Только создать подключение».
После создания всех трех подключений они появятся на панели «Запросы и подключения» как «Только подключение».
- Запустите слияние. Выберите пустую ячейку, перейдите «Данные» → «Получить данные» → «Объединить запросы» → «Слияние».
- Настройте диалоговое окно «Слияние».
- Выберите первую таблицу (например, «Census») и ключевое поле (например, «Employee Name»).
- Выберите вторую таблицу (например, «Forms») и соответствующее ключевое поле (например, «Name»).
- Для «Тип соединения» выберите «Левое внешнее (все из первой, соответствующие из второй)».
- Установите флажок «Использовать нечеткое соответствие для выполнения слияния».
- Настройте параметры нечеткого соответствия. Раскройте меню «Параметры нечеткого соответствия». Ключевые настройки:
- Порог сходства: По умолчанию 0.8 (80%). Можно уменьшить до 0.7 или 0.6 для большего числа совпадений, но осторожно — слишком низкое значение может привести к ложным срабатываниям.
- Таблица преобразований: Укажите таблицу с синонимами (например, «Nicknames»).
- Нажмите «ОК». В редакторе СТЕПЕНЬ (Power) Query появится новая таблица. Щелкните значок развертывания (две стрелки) в столбце «Forms», чтобы выбрать, какие поля из второй таблицы добавить в результат. Снимите флажок «Использовать исходное имя столбца в качестве префикса», чтобы избежать длинных имен.
- Нажмите «Главная» → «Закрыть и загрузить», чтобы вывести итоговую таблицу на новый или существующий лист.
Советы и проверка результатов
Проверка на ложные срабатывания: После получения результатов внимательно их проверьте. Для поиска записей во второй таблице, которые не попали в слияние, выполните еще одно слияние, поменяв таблицы местами и выбрав тип соединения «Левая анти-полусоединение (только строки из первой таблицы)».
Настройка точности: Экспериментируйте с «Порогом сходства». Более низкое значение найдет больше совпадений, но повысит риск ошибок.
Автоматизация: По мере поступления новых данных просто используйте «Данные» → «Обновить все», чтобы автоматически перезапустить процесс нечеткого слияния с обновленными списками.
Заключение
Несмотря на кажущуюся сложность, нечеткое слияние в СТЕПЕНЬ (Power) Query — это незаменимый инструмент для работы с «грязными» данными, где точное совпадение невозможно. Он экономит часы ручной работы по сверке списков с опечатками, разными форматами имен или сокращениями, обеспечивая гибкость и возможность автоматической актуализации результатов.