Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как применить нечеткий поиск, чтобы найти примерный результат совпадения в Excel?

Нечёткий текстовый поиск в Power Query

Однажды я написал подробный обзор бесплатной надстройки Fuzzy Lookup от Microsoft, которая позволяет сопоставить два списка, когда данные не совпадают в точности. Последние обновления Office 365 добавили аналогичную функциональность в Power Query в Excel. Power BI Desktop, кстати, тоже получил ее.

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

Мы будем практиковаться, используя немного улучшенный пример из предыдущей статьи о дополнении Fuzzy Lookup: два списка должны быть объединены в один на основе совпадения адреса:

Исходные данные

Прежде чем мы начнем, обратите внимание на эти пункты:

  • Только один адрес точно совпадает в этих списках — «Пушкино, Набережная ул. 61». Все остальные адреса отличаются в большей или меньшей степени.
  • В некоторых адресах слова заменены — например, «Ульяновск, улица Лермонтова, 63» и «Ульяновск, улица Лермонтова, 63».
  • Некоторые данные отсутствуют — например, город по адресу «90 Lilac street» отсутствует во второй таблице.
  • Где-то город с «d. «, а где-то и без. То же самое относится и к улицам.
  • Есть адреса, которые уникальны, ни на что не похожи и ни с чем не сочетаются (Париж и Рио-де-Жанейро в конце каждого списка).
  • Имеются орфографические ошибки или опечатки в словах в адресах (Чилябинск, Козан…)

С другой стороны, я хочу упомянуть проблему Санкт-Петербурга: об этом городе можно писать по-разному. Для того чтобы учесть это при установлении связей, нам необходимо предварительно создать специальную таблицу преобразований. Колонки этой таблицы должны называться строго De и A и содержать все возможные названия (колонка De) и их правильные аналоги (колонка A):

Читайте так же:
Как преобразовать диапазон в таблицу или наоборот в Excel?

Таблица преобразований

Шаг 1. Грузим исходные данные в Power Query

Первым шагом в импорте данных в Power Query является загрузка каждой из трех исходных таблиц. Это можно сделать несколькими способами (именованный диапазон, область печати, весь лист), но самым удобным, вероятно, будет преобразование в «умные таблицы» с помощью сочетания клавиш Ctrl + T или команды Главная — Формат как таблица.

По умолчанию каждой умной таблице присваивается стандартное имя Table1,2, которое вы можете, при желании, изменить (но я не буду этого делать).

После создания «умной таблицы» просто залейте ее в Power Query с помощью кнопки From Table на вкладке Data (если у вас Excel 2010-2013 и Power Query установлен отдельно от Excel):

Грузим таблицу в Power Query

В открытом окне редактора Power Query мы можем в основном изменить наши данные, если это необходимо, а затем сохранить результирующую таблицу в виде join через Home — Close&Load — Close&Load в :

Выгружаем таблицу как подключение

В следующем окне выберите вариант создания только одного подключения:

Выбор типа импорта

Все это нужно сделать со всеми тремя таблицами по очереди, чтобы в правой панели запросов отображались все три наши таблицы, связанные вместе:

Все загруженные таблицы в режиме подключения

Вот и все. Самое скучное позади. Давайте перейдем к фактическому слиянию.

Шаг 2. Выполняем объединение

На вкладке Данные или Power Query выберите Получить данные / Новый запрос — Объединить запросы — Слияние:

Команда объединения запросов в Power Query

Появится окно объединения:

Окно слияния

В этом окне следует:

1. Из раскрывающегося списка выберите Таблицы 1 и 2.

Выделите столбцы в двух таблицах, с помощью которых мы связываем наши списки (столбцы Address и Location соответственно).

3. чтобы увидеть не только сходства, но и различия и четко понять, что мы нашли, а что нет — выберите тип соединения Full Outer.

Читайте так же:
Как преобразовать одну строку в несколько столбцов и строк в Excel?

4. Установите (самое главное!) флажок Использовать нечеткое соответствие, чтобы выполнить слияние. Это позволяет Power Query искать не только точные, но и приблизительные совпадения.

Ссылка «Параметры нечеткого сопоставления» предоставляет целый набор дополнительных настроек для нечеткого сопоставления, в том числе:

Параметры нечеткого соответствия

  • Порог сходства — это дробный коэффициент (от 0 до 1), определяющий, насколько необходимо соответствие при сборке. Когда этот коэффициент равен 1, Power Query будет искать только точные совпадения. Значения, близкие к нулю, значительно увеличивают вероятность ошибок. Имеет смысл найти наибольшее возможное значение (т.е. самый строгий поиск), но при котором все (или большинство) результатов будут найдены, за 2-3 попытки.
  • Игнорировать регистр — по умолчанию Power Query строг при поиске, т.е. он различает, например, Москва и МОСКВА. Если вы установите этот флажок, вы сможете избавиться от чувствительности к регистру при объединении.
  • Сопоставление путем объединения фрагментов текста — на обычном языке это означает, что поиск совпадений будет искать слова внутри текста (помните Ульяновскую и Лермонтовскую улицы?)
  • Если адрес в первой таблице совпадает с несколькими похожими адресами во второй таблице (особенно важно, если порог сходства низкий), вы можете ограничить количество найденных совпадений с помощью параметра Максимальное количество совпадений. Чтобы учесть различные варианты написания Санкт-Петербурга, мы должны определить нашу третью таблицу как таблицу трансформации.

После выполнения всех настроек нажмите OK и разверните вторую таблицу в появившемся окне Power Query с помощью кнопки в заголовке (флажок Use original column name as prefix (Использовать исходное имя столбца в качестве префикса) можно снять):

Разворачиваем вложенные таблицы

Результат выглядит примерно следующим образом:

Результат нечеткого слияния

Как вы можете видеть, все адреса были сопоставлены, за исключением уникальных адресов Париж и Рио-де-Жанейро, которые были сопоставлены с нулевыми ячейками, т.е. пустыми.

Читайте так же:
Как проверить, есть ли в ячейке изображение в Excel?

Шаг 3. Пишем свою М-функцию подобия

В принципе, мы могли бы остановиться на этом, но есть одна вещь, которая меня смущает во всей этой истории: как мы можем узнать, в какой степени Power Query нашел соответствие для каждого адреса? Представьте себе, что вам приходится объединять таким образом таблицы в несколько тысяч строк: вероятность ошибки при таком объеме данных уже ощутима. Как узнать, хорошо ли Power Query справился с нечетким слиянием (текст совпадает почти точно), и нужно ли проверять совпадение вручную и, возможно, вносить некоторые коррективы?

Вот бы в наших данных была такая (давайте помечтаем!) колонка, в которой указывался бы коэффициент сходства найденных адресов, наглядно иллюстрирующий точность подбора! Представьте, если бы это облегчило поиск вариантов!

К сожалению, я не нашел встроенных инструментов для этого в Power Query 🙁 Однако мы можем сделать что-то подобное, написав собственную функцию сходства двух текстовых строк на языке M в Power Query (за идею спасибо Андрею VG на нашем форуме).

1 . На вкладке Данные выберите Получить данные / Новый запрос — Из других источников — Пустой запрос.

2 . В открывшемся окне редактора запросов нажмите кнопку Расширенный редактор на вкладке Главная или вкладке Вид.

3 . В появившемся окне удалите все значения по умолчанию и скопируйте и вставьте M-код нашей функции:

В итоге все должно получиться следующим образом:

Код М-функции подобия в Power Query

Если вас интересует более подробная информация, вы можете прочитать больше об этой функции:

  • Преобразует две текстовые строки в верхний регистр с помощью функции Text.Upper, чтобы избежать различий между верхним и нижним регистром
  • Разбирает исходные текстовые строки на отдельные символы с помощью функции Text.ToList
  • Находит количество совпадающих символов с помощью функций List.Intersect и List.Count и помещает их в переменную matching_chars
  • Подсчитывает среднюю длину исходных текстовых строк с помощью функции Text.Length и помещает результат в переменную average_length
  • Делит количество совпадений на среднюю длину для получения коэффициента сходства.
Читайте так же:
Как преобразовать числовую строку в дату и время в Excel?

Конечно, эта логика отличается от той, которую использует Power Query при поиске совпадений (и как именно Power Query это делает, известно только разработчикам Microsoft). Однако в подавляющем большинстве реальных случаев наша функция прекрасно справляется со своей задачей — опыт доказывает это.

После нажатия кнопки Done в правой панели окна Power Query вы можете переименовать нашу функцию, дав ей более описательное имя (например, Coefficients вместо Query1).

Теперь нам просто нужно применить его к нашим данным. Выберите Добавить колонку — вызовите пользовательскую функцию на вкладке Добавить колонку и введите ее аргументы в открывшемся окне:

Вводим аргументы М-функции

Нажав на кнопку OK, мы наконец перейдем к тому, что искали — столбцу, показывающему степень сходства между двумя адресами.

Столбец с вычисленным коэф. подобия

Щелкнув правой кнопкой мыши на заголовке получившегося столбца, можно выбрать команду Заменить ошибки и легко заменить получившиеся Ошибка в Париже и Рио-де-Жанейро на нули. А затем отсортируем нашу таблицу в порядке убывания по столбцу коэффициентов и выгрузим ее обратно в Excel с помощью уже знакомой команды Home — Close&Load:

Таблица с результатами нечеткого слияния в Excel

Точные совпадения в начале списка не будут проблемой, но строки в конце списка могут потребовать вашего внимания и «тонкой настройки». Я считаю этот метод слияния более надежным в любом случае.

P.S. А у меня в Excel такого нет!

Если после прочтения этой статьи вы сразу броситесь к Excel или Power Query, я хотел бы еще раз уточнить:

Синтаксис

Функция БПФ имеет четыре параметра:

=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), тут:

<ЧТО> — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

<ГДЕ> — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;

Читайте так же:
Как проверить, действителен ли адрес электронной почты в Excel?

Column_number> — Номер столбца в диапазоне, из которого будет возвращаться значение;.

<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Готов поспорить, что многие из тех, кто знает функцию FFT как свои пять пальцев, будут чувствовать себя неловко, читая описание четвертого параметра, потому что они привыкли видеть его несколько иначе: обычно он относится к точному совпадению при поиске (FALSE или 0) или к поиску по диапазону (TRUE или 1).

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector