Нечеткий поиск в Excel

Обновлено: 30.06.2024

Надстройка Excel Fuzzy Lookup используется для сопоставления похожих, но не совсем совпадающих данных. Эта функция часто используется вместо ВПР, когда мы хотим сравнить два столбца, которые имеют очень похожие данные, но не совсем одинаковые. На выходе Fuzzy Lookup возвращает таблицу совпадающих похожих данных в выбранном столбце. При ежедневной обработке данных часто возникает необходимость сравнить два одинаковых набора данных, когда один из них поступает из какого-либо внешнего источника и может быть написан с ошибкой или введен неправильно. В этом руководстве вы узнаете, как установить надстройку Fuzzy Lookup, подготовить данные и создать Fuzzy Lookup, что может быть очень полезно при консолидации данных и сэкономить много времени.

Установка нечеткого поиска в Excel
Объяснение функции
Подготовка данных для нечеткого поиска
Создать нечеткий поиск

Нечеткий поиск — это не стандартная функция Excel, поэтому ее нельзя найти на стандартных вкладках и кнопках. Чтобы включить эту функцию, Microsoft создала надстройку, которую можно загрузить по следующей ссылке:

После того, как вы загрузили установочный файл, вам необходимо открыть его и установить, следуя инструкциям. После того, как вы установили надстройку, в следующий раз, когда вы откроете Excel, она автоматически импортирует надстройку. В результате вы получите новую вкладку в конце ленты под названием «Нечеткий поиск» и кнопку с таким же названием:

Как упоминалось во вступлении к статье, нечеткий поиск используется, когда мы хотим сопоставить два набора данных (две таблицы), но у нас нет абсолютно одинаковых значений в соответствующих полях. Например, мы хотим сопоставить две таблицы на основе значений в столбце «Имя», и в первой таблице у нас есть значение «Майкл Джексон», а во второй таблице у нас есть похожее, но с ошибкой имя «Майкл Джексон». В этом случае, если мы используем стандартную функцию ВПР, она не будет сопоставлять эти два значения, потому что ищет точное совпадение. Использование Fuzzy Lookup решает эту проблему, сопоставляя столбцы на основе их сходства.

Это очень часто используется, когда мы получаем таблицу в Excel, импортированную из какого-либо другого источника или просто скопированную вручную, и хотим сопоставить ее с другой таблицей с такими же отсортированными данными. В большинстве случаев в первой таблице будет много опечаток и слов с ошибками, и ее сначала нужно будет очистить вручную, чтобы можно было найти ее в нашей подготовленной таблице. Это может занять очень много времени, и именно здесь Fuzzy Lookup экономит драгоценное время. Обратите внимание, что совпадающие столбцы должны быть отформатированы как текст.

Прежде чем выполнять нечеткий поиск, нам нужно преобразовать наши данные в таблицы. Для этого выберите диапазон ячеек, перейдите на вкладку «Вставка» и выберите «Таблица». После того, как мы создали две таблицы, им нужно дать имя, чтобы их можно было использовать в функции Fuzzy Lookup. Это делается путем выбора всей таблицы и ввода имени в поле имени:

Теперь у нас есть данные, готовые для нечеткого поиска: как вы можете видеть на предыдущих рисунках, у нас есть две таблицы: первая (под названием Sales_Actual) содержит данные о фактических продажах на одного продавца (столбцы «Продавец» и «Фактические продажи»). ), а второй содержит данные о целевых продажах на одного продавца (столбцы «Продавец» и «Цель продаж»).

После того как мы отформатировали наши данные в электронной таблице, мы можем приступить к созданию нечеткого поиска с двумя созданными таблицами. Мы можем представить, что первая таблица импортирована из какого-то другого источника данных и представляет отчет о продажах на одного продавца, а вторая таблица содержит упорядоченную таблицу, созданную в нашем файле Excel, которая содержит целевые продажи для каждого человека. В нашем примере мы хотим сопоставить эти две таблицы на основе столбца «Продавец» и создать новую таблицу, в которой будут собраны все данные («Продавец», «Фактические продажи», «Цель продаж»). Мы видим, что в первой таблице есть несколько названий с ошибками, и мы хотим сопоставить их с правильными именами во второй таблице на основе их сходства.

Давайте теперь создадим пример нечеткого поиска и объясним, как он работает. Сначала нам нужно выбрать ячейку, которая будет первой ячейкой вновь созданной таблицы, затем перейти на вкладку «Нечеткий поиск» и нажать кнопку «Нечеткий поиск». У нас откроется следующее окно с правой стороны:

Чтобы создать нашу таблицу, нам нужно сделать несколько шагов:

В первой части окна Fuzzy Lookup нам нужно выбрать две таблицы, которые будут сопоставлены.В нашем случае левая таблица будет «Sales_Actual», а правая — «Sales_Target». После этого нам нужно выбрать столбцы, которые мы хотим сопоставить, и нажать на кнопку между ними. В нашем примере мы хотим сопоставить таблицы на основе сходства столбцов «Продавец», поэтому мы выберем этот столбец как в левых столбцах, так и в правых столбцах. Как только мы это сделаем, в таблице ниже появится одна новая строка с этими соответствующими столбцами. В выходных столбцах нам нужно проверить столбцы, которые мы хотим видеть во вновь созданной таблице: «Sales_Person», «Sales_Actual» и «Sales_Target».

Есть также возможность выбрать поле «FuzzyLookup.Similarity», которое дает процент сходства между двумя столбцами. В конце концов, мы можем выбрать порог подобия (0-100%), который сообщает функции, какой уровень подобия мы хотим сопоставить. После того, как все настроено, мы можем нажать go и получить таблицу на основе введенных параметров:

Как видно на картинке, новая таблица создается из первых двух выбранных. Он состоит из 3 выбранных нами столбцов и сходства столбцов, которое вычисляет сходство столбцов «Sales_Person» в двух таблицах в процентах. Например, «Джон Брайант» из первой таблицы совпадает с «Джон Т. Брайант» из второй таблицы, так как их сходство равно 92%. Кроме того, имена «Рэйчел Уильямс» и «Гарри Уайт» не имеют одинаковых значений во второй таблице (исходя из процента сходства — 50%), поэтому для этих двух записей в столбце «Цель продаж» не указано значение.

Если бы мы хотели получить эти два значения также в сопоставленной таблице, мы бы установили меньшее пороговое значение сходства в окне нечеткого поиска. В следующем примере мы поставили его на 0,2 (20%), что означает, что мы хотим сопоставить все имена, которые имеют сходство 20% или больше. Вот результат:

Вывод теперь представляет собой ту же таблицу, но с включенными именами «Рэйчел Уильямс» и «Гарри Уайт», поскольку их сходство с «Джейсон Дж. Уильямс» и «Харрисон Л. Уайт» превышает 20%.

Вам все еще нужна помощь с функцией ВПР? Ознакомьтесь с подробным обзором руководств по функциям ВПР здесь.

В этом посте рассматриваются функции поиска в Excel, приблизительные совпадения, нечеткие поиски и точные совпадения. Встроенные функции поиска Excel, такие как ВПР, просто великолепны. При правильном применении для специальных проектов или периодически используемых рабочих книг они могут сэкономить массу времени. Одна только функция ВПР сэкономила бесчисленное количество часов в моих рабочих тетрадях постоянного использования. Однако функция ВПР, аналогичная другим функциям поиска Excel, таким как ГПР и ПОИСКПОЗ, создана для выполнения точного совпадения или поиска в диапазоне. Оба они сильно отличаются от приблизительного совпадения или нечеткого поиска. В этой публикации подробно рассматриваются эти идеи и демонстрируется выполнение нечеткого поиска в Excel 2010 и более поздних версиях.

Понимание встроенных функций поиска

Встроенные функции поиска Excel, такие как ВПР, ГПР и ПОИСКПОЗ, работают с аналогичной логикой поиска. Чтобы упростить этот пост, мы будем использовать только один в качестве примера. Поскольку функция ВПР, вероятно, является наиболее часто используемой и знакомой функцией поиска, мы будем использовать ее при изучении этих идей.

Основная идея функции поиска Excel заключается в поиске значения в списке. Например, мы можем попросить Excel найти «Компания ABC» в списке имен клиентов. Это основная идея, но функции поиска многочисленны, и их реализации могут быть весьма сложными и мощными.

В этом посте я хотел бы разделить задачи, которые выполняет функция поиска, на два этапа. Я назову первый шаг сопоставлением, а второй шаг возвратом. На первом этапе сопоставления Excel должен найти совпадающее значение. Вы указываете Excel значение для поиска, например «Компания ABC», и указываете Excel, где искать, например, в диапазоне ячеек. Вы просите Excel найти искомое значение в диапазоне поиска.

Второй шаг — возврат — это результат работы функции. То есть какое значение функция должна вернуть в ячейку. Некоторые функции поиска, например функция ПОИСКПОЗ, сообщают Excel о необходимости возврата номера позиции. Другие функции поиска, такие как функция ВПР, сообщают Excel о необходимости возврата связанного значения. Таким образом, в зависимости от того, какую функцию поиска вы выберете и какие значения аргументов функции вы введете, Excel знает, что вернуть, как только найдет соответствие. Пока все хорошо?

Давайте сделаем небольшой пример.

Я хочу найти конкретное имя клиента «Компания ABC» в списке клиентов, и если он будет найден, я хочу, чтобы Excel вернул идентификатор клиента, который находится в следующем столбце.

Я бы использовал функцию ВПР и попросил бы ее найти «компанию ABC» в таблице клиентов и вернуть идентификатор. Если предположить, что имя клиента было введено в C7, а клиенты сохранены в таблице с именем Table1, то следующая функция поможет:

=ВПР(C7, Таблица1, 2, ЛОЖЬ)

C7 – это значение, которое нужно найти.
Таблица 1 – это диапазон поиска.
2 — это столбец со значением, которое мы хотим вернуть
FALSE означает, что поиск по диапазону не выполняется.

Эта функция введена в C8 на снимке экрана ниже.

Как видите, идентификатор AC100 был успешно возвращен в ячейку формулы C8. И в этом мой друг основная идея функции ВПР. Найдите значение (совпадение) и вычислите результат (возврат).

Важно отметить, что значение поиска, текстовая строка «Компания ABC», должна быть найдена в диапазоне поиска. За исключением регистра (верхний и нижний), два значения должны точно совпадать. «Компания ABC» не будет соответствовать «Компания ABC, Inc.», «Компания ABC» или «Компания ABC». Без пробелов в начале, без пробелов в конце, без дополнительных сокращений или символов. Они должны быть одинаковыми. Это называется точным совпадением. Если значение не совпадает, функция не будет соответствовать ему, и вы получите сообщение об ошибке, как показано на снимке экрана ниже.

Теперь, когда мы рассмотрели основы, пришло время изучить четвертый аргумент ВПР.

Правда о четвертом аргументе ВПР

Четвертый аргумент функции ВПР имеет официальное название: range_lookup. Это логический аргумент, то есть вы можете передать ему значение ИСТИНА или ЛОЖЬ или любое другое представление ИСТИНА или ЛОЖЬ. То, что может ввести пользователей Excel в заблуждение, — это описание, которое Microsoft использовала для этих параметров. Excel описывает значение TRUE как «приблизительное совпадение», а FALSE — как «точное совпадение». Более четким описанием было бы что-то вроде ИСТИНА «Вы выполняете поиск по диапазону» и ЛОЖЬ «Вы не выполняете поиск по диапазону», но в любом случае описания являются такими, какие они есть.

Когда вы выбираете ИСТИНА (приблизительное совпадение), вы не просите Excel сопоставлять значения, которые приблизительно совпадают друг с другом. Описание «Приблизительное совпадение», как правило, подразумевает, что функция будет соответствовать «ABC Company» и «ABC Company, Inc.». так как они примерно одинаковые. В некоторых случаях и в некоторых наборах данных эта идея сработает. Но эта идея работает не во всех случаях, и поэтому на нее нельзя полагаться в наших рабочих тетрадях. Например, на снимке экрана ниже функция не нашла совпадения между «ABC Company» и «ABC Company, Inc.». о чем свидетельствует неправильный идентификатор, возвращенный в C8:

Однако на следующем снимке экрана функция нашла совпадение между «ABC Company» и «ABC Co», о чем свидетельствует ожидаемый идентификатор, возвращенный C8:

На самом деле функция работает, когда выбрано значение TRUE, так: она проходит по списку строка за строкой и в конечном итоге останавливается на строке, которая меньше значения, и где следующая строка больше значения. Вот почему диапазон поиска должен быть отсортирован в порядке возрастания, чтобы функция возвращала точный результат, когда четвертый аргумент равен TRUE.

Эта идея может сбивать с толку, когда речь идет о текстовых строках, но имеет больше смысла, когда речь идет о числах. Например, при попытке найти правильную комиссионную ставку на основе объема продаж. В этом случае вы хотите выполнить поиск диапазона. Вы хотите найти значение из диапазона. Это показано на снимке экрана ниже.

Функция проходит строку за строкой, пытаясь определить, на какой строке остановиться. Он продолжается до тех пор, пока не найдет строку, которая больше искомого значения, а затем останавливается на предыдущей строке. Он останавливается на строке, которая меньше значения, и где следующая строка больше, чем искомое значение. Это довольно легко понять, когда думаешь о числах, но бывает сложнее визуализировать, когда думаешь о текстовых строках. Однако ключом к пониманию этого аргумента функции является понимание того, что логика идентична при работе с текстовыми строками и числами.Вот почему «ABC Company» не соответствует «ABC Company, Inc.», потому что «ABC Company Inc.» больше, чем компания ABC. Вот почему «ABC Company» будет соответствовать «ABC Co», потому что «ABC Co» меньше «ABC Company». Как видите, это не то, что мы имеем в виду, когда думаем о приблизительном совпадении.

Что такое нечеткий поиск или приблизительное совпадение

Приблизительное совпадение для нас означает, что две текстовые строки, которые примерно одинаковы, но не обязательно идентичны, должны совпадать. Например, «Компания ABC» должна соответствовать «Компания ABC, Inc.», «Компания ABC» и «Компания ABC». Мы думаем о приблизительном совпадении как о нечетком, где совпадают некоторые символы, но не все.

Идея нечеткого поиска заключается в том, что значения не совпадают, они не идентичны. Но то, что они, вероятно, совпадают, есть вероятность, что они совпадают. Скорее всего, они представляют собой один и тот же базовый объект.

Теперь, когда мы понимаем, что функция ВПР на самом деле не выполняет логику приблизительного совпадения, по крайней мере, не так, как мы этого хотим, что нам делать?

Дополнение

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

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

После установки эта надстройка выполняет нечеткий поиск. Это не меняет поведение ни одной из встроенных функций поиска. Это не позволяет вашим функциям ВПР выполнять нечеткий поиск. Это надстройка, которая в основном обрабатывает два списка и вычисляет вероятность совпадения.

Вы указываете две таблицы и в каждой таблице столбцы для проверки. По сути, вы определяете первый шаг соответствие. Затем вы определяете шаг два, определяя, какие столбцы из таблиц должны быть включены в результат. Вы также можете указать порог вероятности. Вы нажимаете go, и надстройка выполняет свою работу, а затем выводит результирующую таблицу, начиная с активной ячейки. По сути, он создает статический отчет на основе выбранных вами настроек.

Вот скриншот вывода, показывающий, что он успешно сопоставил "Компания ABC" и "Компания ABC, Inc." в том же наборе данных, который вызвал сбой нашей функции ВПР.

Для получения дополнительной информации о надстройке нечеткого поиска и более подробной информации о том, как ее использовать, перейдите по ссылке Microsoft выше. Надстройка поставляется с инструкциями, образцом файла Excel и файлом PDF с фоном и логикой, которую она использует, чтобы творить чудеса. Он также поставляется с лицензией, поэтому обязательно ознакомьтесь с условиями лицензии в документе LicenseTerms.rtf, включенном в загружаемый файл.

За кулисами работают очень интересные компьютерные и математические науки, в том числе сходство Жаккара, токенизация записей и преобразования. Там довольно тяжелая математика. Спасибо Microsoft Research за эту надстройку!!

В Microsoft есть группа исследований и разработок, известная как Microsoft Research Labs. Почти 10 лет назад компания изобрела бесплатную надстройку Fuzzy Lookup для Excel. Алгоритм нечеткого сопоставления ищет слова, которые имеют процент общих символов. Эта функция теперь встроена в версии Microsoft 365 для Windows.

На рис. 1 показаны два набора данных, которые необходимо сопоставить. Столбцы A и B содержат список сотрудников. Столбцы D и E содержат имена сотрудников, заполнивших требуемую форму. Вам нужно определить людей, которые еще не вернули форму. К сожалению, VLOOKUP или XLOOKUP не будут работать, так как столбец A использует формат «фамилия, имя», а столбец D содержит псевдоним и фамилию. Эти два набора данных можно сопоставить с помощью параметра нечеткого сопоставления.

Столбцы G и H показывают таблицу перевода, которая будет использоваться при нечетком сопоставлении, чтобы помочь сопоставить полные имена с их псевдонимами. Для таблицы перевода требуется два столбца с пометками «От» и «Кому». Нечеткое совпадение, скорее всего, совпадет с Крисом и Кристи, потому что у них много одинаковых букв, но для этого потребуется запись в таблице перевода для Билла и Уильяма или Натана и Нейта.

Прежде чем вы сможете выполнить сопоставление, все три диапазона данных необходимо преобразовать в таблицу, выбрав каждый отдельный диапазон и нажав Ctrl+T. Затем переименуйте каждую таблицу: выберите одну ячейку в таблице. Перейдите на вкладку Работа с таблицами на ленте и введите значимое имя, например «Перепись», «Формы» и «Псевдонимы».

ОПРЕДЕЛЕНИЕ СОЕДИНЕНИЙ С КАЖДОЙ ТАБЛИЦЕЙ

Вам необходимо преобразовать каждую из трех таблиц в соединение в Excel. В ячейке A1 выберите Data, From Table/Range (как показано красной стрелкой на рисунке 1). Excel откроет редактор Power Query. Первый значок на вкладке «Главная» говорит «Закрыть и загрузить». Щелкните раскрывающееся меню под ним и выберите «Закрыть и загрузить в…», чтобы открыть диалоговое окно «Импорт данных». Выберите четвертый элемент под названием «Только создать соединение».

Повторите процесс создания соединения для двух других таблиц, начиная с ячейки D1 и ячейки G1 соответственно. Если вы правильно создали все три подключения, вы должны увидеть три запроса, отмеченных как «Только подключение» на панели «Запросы и подключения» в правой части окна Excel.

ПРОВЕДЕНИЕ НЕЧЕТКОГО СООТВЕТСТВИЯ

Выберите пустую ячейку на листе. На вкладке «Данные» выберите «Получить данные», «Объединить запросы», «Объединить», чтобы открыть диалоговое окно «Объединить». В этом диалоговом окне есть много тонких настроек, которые не интуитивно понятны. На рис. 2 показаны восемь шагов:

В верхнем раскрывающемся меню выберите таблицу Census.
В небольшом окне предварительного просмотра данных нажмите на заголовки полей, которые будут использоваться для сопоставления. В данном случае это заголовок «Имя сотрудника».
Во втором раскрывающемся меню выберите имя таблицы поиска. В данном случае — Формы.
В окне предварительного просмотра данных нажмите на заголовки полей, которые будут использоваться для сопоставления, например "Имя".
Установите флажок "Использовать нечеткое сопоставление для объединения".
Некоторые специальные настройки скрыты за раскрывающимся меню параметров нечеткого соответствия. Нажмите на треугольник, чтобы открыть этот раздел.
Прокрутите до конца раздела и установите Псевдонимы в качестве таблицы преобразования.
Убедитесь, что количество найденных совпадений совпадает с количеством записей в таблице Forms.

Нажмите "ОК", чтобы выполнить объединение. В сетке редактора Power Query будут отображаться столбцы «Имя сотрудника», «Отдел», а затем столбец «Формы». Значение в каждой строке для форм просто говорит «Таблица» в каждой строке. Справа от заголовка «Формы» находится значок «Развернуть» с двумя стрелками, указывающими влево и вправо. Щелкните этот значок, чтобы выбрать возвращаемые поля из таблицы Forms.

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

На этом этапе просмотрите результаты, чтобы убедиться в отсутствии ложных совпадений. Если все выглядит хорошо, вы можете отсортировать или отфильтровать записи, которые показывают совпадение, оставив людей, которые не обратились в форму.

Еще со времен курса "Бухгалтерский учет 101" меня учили, что "закрыть" в бухгалтерском учете нельзя. Это приводит к нежеланию доверять алгоритму нечеткого сопоставления. Тем не менее бывают случаи, когда инструмент нечеткого сопоставления является единственным решением, кроме ручного сопоставления записей.

SF говорит:

По мере того, как все больше людей отправляют свои формы, выбор «Данные» и «Обновить все» автоматически выполнит нечеткое совпадение снова.

Сегодня я покажу, как использовать функцию ВПР для поиска нечеткого совпадения в Excel.

При работе с большими наборами данных мы часто пытаемся отфильтровать похожие типы значений. Один из этих типов сопоставления называется нечетким сопоставлением, при котором значения не совсем совпадают, но тем не менее они сопоставляются на основе их сходства.

Сегодня я покажу, как можно использовать функцию ВПР Excel для поиска нечеткого совпадения.

Введение в нечеткое соответствие

Нечеткое совпадение — это тип частичного совпадения.

В этих типах совпадений один текст не полностью совпадает с другим текстом. Но важные разделы текста совпадают с другим текстом.

В данном примере книга «История Индии во время мировой войны» содержит три важных раздела: История, Индия и Мировая война.

Поэтому все книги, содержащие один или несколько из этих разделов, будут нечетко соответствовать книге.

История Второй мировой войны
История Древней Греции
Мировая война: причины и следствия
Цивилизация Инда: древняя история
Индия побеждает в борьбе за свободу
Адольф Гитлер: до и после мировой войны
Открытие Индии

Скачать рабочую тетрадь

Нечеткое соответствие ВПР в Excel

Здесь у нас есть набор данных с названиями некоторых книг из книжного магазина под названием Kingfisher Bookstore.

Наша цель сегодня — использовать функцию ВПР Excel для создания нечетких совпадений.

1. Нечеткое сопоставление ВПР с использованием подстановочных знаков (совпадение всего искомого_значения)

Прежде всего, мы сгенерируем несколько нечетких совпадений, используя подстановочный знак, символ звездочки (*).

Но помните, что в этом методе вы должны сопоставлять искомое_значение целиком, а не отдельные части искомого_значения.

Например, таким образом мы можем найти книгу, содержащую текст «Вторая мировая война».

Только книги с полным текстом «Вторая мировая война» будут совпадать.

Формула проста. Поместите символ звездочки (*) на обоих концах текста искомое_значение.

Формула будет такой:

Или вы также можете использовать ссылку на ячейку вместо исходного текста. Используйте символ амперсанда (&), чтобы объединить их в один текст. Вот так:

Чтобы узнать больше о функции ВПР с использованием подстановочных знаков, посетите эту статью.

2. Нечеткое соответствие с использованием VBA

Метод из предыдущего раздела частично выполняет нашу задачу, но не полностью.

Теперь мы выведем формулу, используя код VBA, который почти полностью выполнит нашу задачу.

Откройте окно VBA и вставьте следующий код VBA в новый модуль:

Код:

Этот код создает функцию FUZZYMATCH.

Сохраните его (посетите эту статью, чтобы узнать, как сохранить код VBA в Excel)

Эта функция FUZZYMATCH напрямую находит все нечеткие совпадения искомого значения.

Синтаксис этой функции FUZZYMATCH:

Чтобы найти нечеткие совпадения книги «История Индии во время мировой войны», введите это искомое_значение в ячейку (D4 в этом примере) и введите эту формулу в другую ячейку:

=НЕЧЕТКОЕ СООТВЕТСТВИЕ(D4,B4:B21)

Смотрите, мы нашли все нечеткие совпадения книги «История Индии во время мировой войны»

Здесь D4 – это ссылка на ячейку искомого_значения ("История Индии во время мировой войны").
B4:B21 – это диапазон поиска.

Давайте найдем нечеткие совпадения из другой книги под названием «Записная книжка о причинах преступности в больших городах».

Введите это искомое_значение в ячейку (D4 в этом примере) и введите эту формулу в другую ячейку:

=НЕЧЕТКОЕ СООТВЕТСТВИЕ(D4,B4:B21)

Объяснение формулы

Функция FUZZYMATCH — это функция, которую мы создали в VBA. Он принимает строку с именем искомое_значение и диапазон ячеек с именем искомый_диапазон и возвращает массив всех нечетких совпадений строки.
Поэтому FUZZYMATCH(D4,B4:B21) возвращает массив всех нечетких совпадений строки в ячейке D4 из диапазона B4:B21.

3. Нечеткое соответствие с помощью надстройки нечеткого поиска Excel

Microsoft Excel предоставляет надстройку под названием Fuzzy Lookup. Используя его, вы можете сопоставить две таблицы для нечеткого поиска.

Загрузите и установите надстройку по этой ссылке.

После успешной загрузки и установки надстройка Fuzzy Lookup появится на панели инструментов Excel.

Затем поместите наборы данных в две таблицы, которые вы хотите сопоставить.

Здесь у меня есть две таблицы, содержащие два списка книг из двух книжных магазинов под названием Robert Bookshop и Martin Bookshop.

Далее перейдите в раздел Нечеткий поиск>Нечеткий поиск на панели инструментов Excel.

Нажмите. Вы получите нечеткую таблицу поиска, созданную на боковой панели вашей книги.

В параметрах «Левая таблица» и «Правая таблица» выберите имена двух таблиц.

Для этого примера выберите Роберта и Мартина.

Затем в разделе "Столбцы" выберите имена столбцов каждой таблицы.

В разделе «Совпадение столбца» выберите тип соответствия между двумя столбцами. Для параметра «Нечеткое соответствие» выберите «По умолчанию».

Наконец, нажмите «Перейти». Вы получите соотношение таблиц в новой таблице.

Заключение

Используя эти методы, вы можете использовать функцию ВПР Excel для поиска нечеткого совпадения. Хотя эти методы не на 100% эффективны, тем не менее они очень полезны. У вас есть вопросы? Не стесняйтесь спрашивать нас.

Читайте также: