Нечеткий поиск в excel
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Найдите и замените похожие записи из списка значений ячеек
2. В Нечеткий поиск панели, пожалуйста, настройте следующие параметры:
(2.) Введите максимальное количество различных символов, которые вы хотите искать из списка ячеек, в поле Максимальное количество разных символов поле;
А затем введите число в Длина строки ячеек не менее поле, чтобы исключить короткие значения для поиска.
Заметки:
(1.) Если вы просто хотите найти аналогичное значение на основе определенного текста, проверьте Найти по указанному тексту вариант, а в Текст в поле введите текст, по которому нужно выполнить поиск.
(2.) Если каждая ячейка содержит несколько слов, для поиска и проверки каждого слова в ячейках установите флажок Разделить ячейки на следующие символы вариант, а затем введите разделитель, который напрямую разделяет ваши текстовые строки, он будет автоматически заключен в двойные кавычки, см. снимок экрана:
3. После завершения настроек нажмите Найдите кнопку, и все приблизительные совпадающие значения сгруппированы аналогичным текстом в поле списка. Смотрите скриншот:
(1.) Щелкните корневой узел и введите правильный текст в Правильное значение текстовое окно;
(2.) Затем щелкните Заполнять или нажмите Enter Клавиша, чтобы заполнить введенный текст всем элементом узла автоматически.
Внимание:
Вы также можете выбрать значение, которое хотите применить для исправления, а затем нажать Заполнять в списке «Выполнить», это значение будет присвоено всем элементам узла, см. снимок экрана:
5. После выбора правильного значения для каждой группы данных нажмите кнопку Применить кнопку в нижней части панели. И все аналогичные значения были изменены на желаемые, как показано на следующем скриншоте:
Ноты:
обновление кнопка: эта кнопка может помочь вам обновить недавно обновленные данные в списке панели после применения правильного значения;
расстегивать Кнопка: с помощью этого параметра вы можете быстро восстановить исходное значение данных на листе после применения недавно обновленных данных.
Демонстрация: поиск и замена похожих записей из списка значений ячеек
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.
Kutools for Excel
Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.
Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 60 дней.
Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.
Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.
Тренироваться будем на слегка модернизированном примере из прошлой статьи про надстройку Fuzzy Lookup - двух списках, которые нужно объединить в один по совпадению адресов:
Прежде, чем начнём, обратите внимание на следующие моменты:
- Точно в этих списках совпадает только один адрес - "Пушкино, Набережная ул., д.61". Все остальные адреса различаются с большей или меньшей степенью разброса.
- В некоторых адресах переставлены местами слова - например "Ульяновск, Лермонтова ул., д.63" и "улица Лермонтова д.63, г. Ульяновск".
- В некоторых не хватает части данных - например, нет города в "Сиреневая ул. д.90" во второй таблице.
- Где-то город с "г.", а где-то без. С улицами - аналогично.
- Есть адреса уникальные и совершенно ни на что не похожие и ни с чем не совпадающие (Париж и Рио-де-Жанейро в конце каждого списка).
- Есть адреса с орфографическими ошибками или опечатками внутри слов (Чилябинск, Козань...)
Отдельно хочу отметить проблему с Санкт-Петербургом - этот город может быть записан кучей разных способов. Чтобы учесть этот момент при связывании нам придется заранее сделать специальную таблицу преобразований. Колонки в этой таблице должны строго называться From и To и содержать все возможные варианты наименований (столбец From) и их правильные аналоги (столбец To):
Шаг 1. Грузим исходные данные в Power Query
Сначала, само-собой, нужно загрузить все наши три исходные таблицы в Power Query. Сделать это можно несколькими способами (именованный диапазон, область печати, лист целиком), но самым удобным будет, наверное, преобразование в "умные таблицы" с помощью сочетания клавиш Ctrl + T или командой Главная - Форматировать как таблицу (Home - Format as Table) .
По умолчанию, каждая умная таблица получает стандартное имя а-ля Таблица1,2. что можно, при желании изменить (но я здесь не буду).
После этого созданную "умную таблицу" можно легко залить в Power Query с помощью кнопки Из таблицы (From Table) на вкладке Данные (Data) или на вкладке Power Query (если у вас версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):
В открывшемся окне редактора запросов Power Query можно, в приципе, "допилить" наши данные при необходимости и затем сохранить полученную таблицу как подключение через Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close&Load - Close&Load to. ) :
И выбрать в следующем окне опцию Только создать подключение (Only create connection) :
Всё это нужно по очереди проделать со всеми тремя таблицами, чтобы в итоге в правой панели запросов появились все три наши таблицы в режиме подключения:
Всё. Самая скучная часть - позади. Теперь переходим, непосредственно, к слиянию.
Шаг 2. Выполняем объединение
На вкладке Данные (Data) или на вкладке Power Query выбираем команду Получить данные / Создать запрос - Объединить - Объединить (Get Data / New Query - Combine queries - Merge) :
Откроется окно слияния:
В этом окне нужно:
1. Выбрать в выпадающих списках Таблицы 1 и 2, которые мы хотим объединить.
2. Выделить в обеих таблицах столбцы, по которым мы связываем наши списки (колонки Адрес и Место, соответственно).
3. Чтобы увидеть потом не только совпадения, но и отличия и ясно понимать что именно мы нашли, а что нет - выбрать тип соединения Полное внешнее (Full Outer).
4. Включить (самое главное!) флажок Использовать нечеткие соответствия для слияния (Use fuzzy matching to perform the merge) . Именно он заставляет Power Query искать не только точные совпадения, но и приблизительные.
Под ссылкой Параметры нечеткого соответствия (Fuzzy matching options) скрывается целый блок дополнительных настроек для нечеткого слияния:
- Порог подобия (Similarity Threshold) - дробный коэффициент (от 0 до 1), определяющий, насколько строгого соответствия вы требуете при сборке. При значении этого коэффициента равном единице, Power Query будет искать, фактически, только точные совпадения. При значениях близких к нулю сильно возрастает вероятность ошибки. Имеет смысл путем 2-3 попыток подобрать максимально большое значение (т.е. максимально строгий поиск), но при котором находятся все (или большинство) результатов.
- Игнорировать регистр (Ignore case) - по умолчанию Power Query учитывает регистр при поиске, т.е. различает Москва и МОСКВА, например. Включение этого флажка позволяет избавиться от регистрочувствительности при слиянии.
- Сопоставление путем объединения текстовых фрагментов (Match by combining text parts) - в переводе на человеческий язык означает, что при поиске соответствий будет производится проверка на переставление слов внутри текста (помните Ульяновск и ул.Лермонтова?)
- Если одному адресу в первой таблице соответствуте несколько похожих адресов во второй (это особенно актуально при низких значениях порога подобия), то можно ограничить количество найденных вариантов - за это отвечает параметр Максимальное число совпадений (Maximum number of matches) .
- Чтобы учесть разные варианты написания Санкт-Петербурга - укажем нашу третью таблицу как Таблицу преобразования (Transformation Table) .
Выполнив все настройки, нажмём на ОК и развернём в появившемся окне Power Query вторую таблицу с помощью кнопки в шапке (флажок Использовать исходное имя столбца как префикс можно снять):
В результате получим что-то похожее на:
Как видите, все адреса нашли свои аналоги, кроме уникальных Парижа и Рио-де-Жанейро, в паре с которыми появились ячейки с null, т.е. пустотой.
Шаг 3. Пишем свою М-функцию подобия
В принципе, на этом можно было бы и остановиться, но, вот, лично меня во всей этой истории смущает один момент: как определить, насколько хорошо Power Query нашёл соответствие для каждого адреса? Представьте, что вам нужно объединить подобным образом таблицы по несколько тысяч строк - вероятность ошибки при таком объеме данных уже ощутимая. Как понять, где Power Query отработал нечёткое слияние хорошо (текст совпадает почти точно), а где стоит проверить совпадение вручную и, возможно, внести правки?
Вот если бы был (помечтаем!) в наших данных столбец, где указывался бы коэффициент подобия найденных адресов, наглядно иллюстрирующий точность подбора! Как бы это упростило поиск подозрительных вариантов!
К сожалению, я не нашел в Power Query встроенных инструментов для подобного :( Однако, мы можем своими силами реализовать похожую штуку, написав собственную функцию подобия двух текстовых строк на встроенном в Power Query языке М (за идею огромное спасибо и поклон в пояс Андрею VG с нашего форума).
1 . На вкладке Данные выбираем команду Получить данные / Создать запрос - Из других источников - Пустой запрос (Get Data / New Query - From other sources - Blank query) .
2 . В открывшемся окне редактора запросов жмем на Главной (Home) или на вкладке Просмотр (View) кнопку Расширенный редактор (Advanced Editor) .
3 . В появившемся окне удаляем всё, что там есть по-умолчанию и копируем-вставляем туда М-код нашей функции:
Выглядеть это всё должно, в итоге, вот так:
Если интересны детали, то эта функция:
- переводит обе текстовых строки в заглавные буквы функцией Text.Upper, чтобы избежать регистрочувствительности
- разбирает исходные строки на отдельные символы функциями Text.ToList
- ищет количество совпадений символов функциями List.Intersect и List.Count и помещает его в переменную matching_chars
- вычисляет среднюю длину исходных текстовых строк с помощью функций Text.Length и помещает результат в переменную average_length
- делит число совпадений на среднюю длину, чтобы получить коэффициент подобия
Само-собой, эта логика отличается от той, что использует Power Query при поиске соответствий (а как именно это делает Power Query - знают только разработчики в Microsoft). Однако, в подавляющем большинстве реальных случаев, наша функция со своей задачей отлично справляется - проверено на опыте.
После нажатия на Готово в правой панели окна Power Query можно переименовать нашу функцию, дав ей более наглядное имя (например, КоэфПодобия вместо Запрос1).
Теперь осталось применить её к нашим данным. Выберем на вкладке Добавление столбца команду Вызвать настраиваемую функцию (Add Column - Invoke Custom Function) и введём ее аргументы в открывшемся окне:
После нажатия на ОК мы, наконец, получим желаемое - столбец, где будет виден числовой коэффициент подобия, наглядно отображающий качество подбора наших адресов:
Щёлкнув правой кнопкой мыши по заголовку получившегося столбца, можно выбрать команду Заменить ошибки (Replace Errors) и легко заменить получившиеся Error в Париже и Рио-де-Жанейро на нули. Ну, а затем отсортировать нашу таблицу по убыванию по столбцу коэффициентов и выгрузить обратно в Excel уже знакомой командой Главная - Закрыть и загрузить (Home - Close&Load) :
Точные совпадения в начале списка проблем не составят, а вот строки в конце списка, возможно, потребуют вашего внимания и "доработки напильником". В любом случае, такой вариант слияния мне кажется более надежным.
P.S. А у меня в Excel такого нет!
Для всех, кто после прочтения этой статьи немедленно рванёт в свой Excel проверять наличие нечёткого поиска в Power Query, ещё разок хочу уточнить:
Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.
Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.
Тренироваться будем на слегка модернизированном примере из прошлой статьи про надстройку Fuzzy Lookup - двух списках, которые нужно объединить в один по совпадению адресов:
Прежде, чем начнём, обратите внимание на следующие моменты:
- Точно в этих списках совпадает только один адрес - "Пушкино, Набережная ул., д.61". Все остальные адреса различаются с большей или меньшей степенью разброса.
- В некоторых адресах переставлены местами слова - например "Ульяновск, Лермонтова ул., д.63" и "улица Лермонтова д.63, г. Ульяновск".
- В некоторых не хватает части данных - например, нет города в "Сиреневая ул. д.90" во второй таблице.
- Где-то город с "г.", а где-то без. С улицами - аналогично.
- Есть адреса уникальные и совершенно ни на что не похожие и ни с чем не совпадающие (Париж и Рио-де-Жанейро в конце каждого списка).
- Есть адреса с орфографическими ошибками или опечатками внутри слов (Чилябинск, Козань...)
Отдельно хочу отметить проблему с Санкт-Петербургом - этот город может быть записан кучей разных способов. Чтобы учесть этот момент при связывании нам придется заранее сделать специальную таблицу преобразований. Колонки в этой таблице должны строго называться From и To и содержать все возможные варианты наименований (столбец From) и их правильные аналоги (столбец To):
Шаг 1. Грузим исходные данные в Power Query
Сначала, само-собой, нужно загрузить все наши три исходные таблицы в Power Query. Сделать это можно несколькими способами (именованный диапазон, область печати, лист целиком), но самым удобным будет, наверное, преобразование в "умные таблицы" с помощью сочетания клавиш Ctrl + T или командой Главная - Форматировать как таблицу (Home - Format as Table) .
По умолчанию, каждая умная таблица получает стандартное имя а-ля Таблица1,2. что можно, при желании изменить (но я здесь не буду).
После этого созданную "умную таблицу" можно легко залить в Power Query с помощью кнопки Из таблицы (From Table) на вкладке Данные (Data) или на вкладке Power Query (если у вас версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):
В открывшемся окне редактора запросов Power Query можно, в приципе, "допилить" наши данные при необходимости и затем сохранить полученную таблицу как подключение через Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close&Load - Close&Load to. ) :
И выбрать в следующем окне опцию Только создать подключение (Only create connection) :
Всё это нужно по очереди проделать со всеми тремя таблицами, чтобы в итоге в правой панели запросов появились все три наши таблицы в режиме подключения:
Всё. Самая скучная часть - позади. Теперь переходим, непосредственно, к слиянию.
Шаг 2. Выполняем объединение
На вкладке Данные (Data) или на вкладке Power Query выбираем команду Получить данные / Создать запрос - Объединить - Объединить (Get Data / New Query - Combine queries - Merge) :
Откроется окно слияния:
В этом окне нужно:
1. Выбрать в выпадающих списках Таблицы 1 и 2, которые мы хотим объединить.
2. Выделить в обеих таблицах столбцы, по которым мы связываем наши списки (колонки Адрес и Место, соответственно).
3. Чтобы увидеть потом не только совпадения, но и отличия и ясно понимать что именно мы нашли, а что нет - выбрать тип соединения Полное внешнее (Full Outer).
4. Включить (самое главное!) флажок Использовать нечеткие соответствия для слияния (Use fuzzy matching to perform the merge) . Именно он заставляет Power Query искать не только точные совпадения, но и приблизительные.
Под ссылкой Параметры нечеткого соответствия (Fuzzy matching options) скрывается целый блок дополнительных настроек для нечеткого слияния:
- Порог подобия (Similarity Threshold) - дробный коэффициент (от 0 до 1), определяющий, насколько строгого соответствия вы требуете при сборке. При значении этого коэффициента равном единице, Power Query будет искать, фактически, только точные совпадения. При значениях близких к нулю сильно возрастает вероятность ошибки. Имеет смысл путем 2-3 попыток подобрать максимально большое значение (т.е. максимально строгий поиск), но при котором находятся все (или большинство) результатов.
- Игнорировать регистр (Ignore case) - по умолчанию Power Query учитывает регистр при поиске, т.е. различает Москва и МОСКВА, например. Включение этого флажка позволяет избавиться от регистрочувствительности при слиянии.
- Сопоставление путем объединения текстовых фрагментов (Match by combining text parts) - в переводе на человеческий язык означает, что при поиске соответствий будет производится проверка на переставление слов внутри текста (помните Ульяновск и ул.Лермонтова?)
- Если одному адресу в первой таблице соответствуте несколько похожих адресов во второй (это особенно актуально при низких значениях порога подобия), то можно ограничить количество найденных вариантов - за это отвечает параметр Максимальное число совпадений (Maximum number of matches) .
- Чтобы учесть разные варианты написания Санкт-Петербурга - укажем нашу третью таблицу как Таблицу преобразования (Transformation Table) .
Выполнив все настройки, нажмём на ОК и развернём в появившемся окне Power Query вторую таблицу с помощью кнопки в шапке (флажок Использовать исходное имя столбца как префикс можно снять):
В результате получим что-то похожее на:
Как видите, все адреса нашли свои аналоги, кроме уникальных Парижа и Рио-де-Жанейро, в паре с которыми появились ячейки с null, т.е. пустотой.
Шаг 3. Пишем свою М-функцию подобия
В принципе, на этом можно было бы и остановиться, но, вот, лично меня во всей этой истории смущает один момент: как определить, насколько хорошо Power Query нашёл соответствие для каждого адреса? Представьте, что вам нужно объединить подобным образом таблицы по несколько тысяч строк - вероятность ошибки при таком объеме данных уже ощутимая. Как понять, где Power Query отработал нечёткое слияние хорошо (текст совпадает почти точно), а где стоит проверить совпадение вручную и, возможно, внести правки?
Вот если бы был (помечтаем!) в наших данных столбец, где указывался бы коэффициент подобия найденных адресов, наглядно иллюстрирующий точность подбора! Как бы это упростило поиск подозрительных вариантов!
К сожалению, я не нашел в Power Query встроенных инструментов для подобного :( Однако, мы можем своими силами реализовать похожую штуку, написав собственную функцию подобия двух текстовых строк на встроенном в Power Query языке М (за идею огромное спасибо и поклон в пояс Андрею VG с нашего форума).
1 . На вкладке Данные выбираем команду Получить данные / Создать запрос - Из других источников - Пустой запрос (Get Data / New Query - From other sources - Blank query) .
2 . В открывшемся окне редактора запросов жмем на Главной (Home) или на вкладке Просмотр (View) кнопку Расширенный редактор (Advanced Editor) .
3 . В появившемся окне удаляем всё, что там есть по-умолчанию и копируем-вставляем туда М-код нашей функции:
Выглядеть это всё должно, в итоге, вот так:
Если интересны детали, то эта функция:
- переводит обе текстовых строки в заглавные буквы функцией Text.Upper, чтобы избежать регистрочувствительности
- разбирает исходные строки на отдельные символы функциями Text.ToList
- ищет количество совпадений символов функциями List.Intersect и List.Count и помещает его в переменную matching_chars
- вычисляет среднюю длину исходных текстовых строк с помощью функций Text.Length и помещает результат в переменную average_length
- делит число совпадений на среднюю длину, чтобы получить коэффициент подобия
Само-собой, эта логика отличается от той, что использует Power Query при поиске соответствий (а как именно это делает Power Query - знают только разработчики в Microsoft). Однако, в подавляющем большинстве реальных случаев, наша функция со своей задачей отлично справляется - проверено на опыте.
После нажатия на Готово в правой панели окна Power Query можно переименовать нашу функцию, дав ей более наглядное имя (например, КоэфПодобия вместо Запрос1).
Теперь осталось применить её к нашим данным. Выберем на вкладке Добавление столбца команду Вызвать настраиваемую функцию (Add Column - Invoke Custom Function) и введём ее аргументы в открывшемся окне:
После нажатия на ОК мы, наконец, получим желаемое - столбец, где будет виден числовой коэффициент подобия, наглядно отображающий качество подбора наших адресов:
Щёлкнув правой кнопкой мыши по заголовку получившегося столбца, можно выбрать команду Заменить ошибки (Replace Errors) и легко заменить получившиеся Error в Париже и Рио-де-Жанейро на нули. Ну, а затем отсортировать нашу таблицу по убыванию по столбцу коэффициентов и выгрузить обратно в Excel уже знакомой командой Главная - Закрыть и загрузить (Home - Close&Load) :
Точные совпадения в начале списка проблем не составят, а вот строки в конце списка, возможно, потребуют вашего внимания и "доработки напильником". В любом случае, такой вариант слияния мне кажется более надежным.
P.S. А у меня в Excel такого нет!
Для всех, кто после прочтения этой статьи немедленно рванёт в свой Excel проверять наличие нечёткого поиска в Power Query, ещё разок хочу уточнить:
Одна из самых неприятных ситуаций, с которой может столкнуться пользователь при работе в Microsoft Excel - это поиск и подстановка данных с неточным совпадением. Когда вам надо подставить данные из одной таблицы в другую, но вы при этом уверены, что в обеих таблицах совпадающие элементы называются одинаково, то проблем нет - к вашим услугам множество способов: функции ВПР и её аналоги, надстройка Power Query и т.д.
А вот если в одной таблице "Пупкин Василий", а в другой просто "Пупкин", или "Пупкин В.", или даже "Пупкен", то все эти красивые способы не работают. Причем на практике такое встречается постоянно, особенно с почтовыми адресами или названиями компаний:
Обратите внимание на различные типы несоответствий, которые могут встречаться:
- переставлены местами улица, город, дом
- отсутствует какая-то часть адреса или, наоборот, есть что-то лишнее (индекс, номер квартиры)
- по-разному записан город (с буквой "г." или без) или улица
- опечатки и ошибки (Козань вместо Казань)
Про точное соответствие или даже поиск по маске тут говорить не приходится. Помочь в таком случае могут только специальные макросы или надстройки для Excel. Про одну из таких макро-функций на VBA я уже писал, а здесь хочется рассказать про еще один вариант решения подобной задачи - надстройку Fuzzy Lookup от компании Microsoft.
Эта надстройка существует с 2011 года и совершенно бесплатно скачивается с сайта Microsoft. Системные требования: Windows 7 или новее, Office 2007 или новее, соответственно. После установки у вас в Excel появляется одноименная вкладка с единственной кнопкой на ней:
Нажатие на эту кнопку включает специальную панель в правой части окна Excel, где и задаются все настройки поиска:
Сразу хочу отметить, что эта надстройка умеет работать только с умными таблицами, поэтому все исходные таблицы нужно конвертировать в умные с помощью сочетания Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home - Format as Table) :
Алгоритм действий при работе с надстройкой Fuzzy Lookup следующий:
После анализа мы получаем таблицу, где каждому элементу ключевого столбца из первой таблицы подобрано максимально похожее значение из второй:
Нюансы и подводные камни
- Точность подбора можно регулировать с помощью ползунка Similarity Threshold в нижней части панели Fuzzy Lookup. Чем правее его положение, тем строже будет поиск, и - как следствие - тем меньше результатов надстройка будет находить. Если сдвинуть его влево, то результатов станет больше, но возрастет риск ошибочного совпадения. Тут все зависит от вашей конкретной ситуации - экспериментируйте.
- На больших таблицах поиск может занимать приличное количество времени (до нескольких десятков секунд), хотя многое, конечно, зависит от мощности вашего компьютера. Как вариант, для ускорения в настройках (кнопка Configure в нижней части панели) можно попробовать включить параметр UseApproximateIndexing в разделе Global Settings.
- Перед нажатием на кнопку Goне забудьте выделить пустую ячейку, начиная с которой вы хотите вывести результаты. Если случайно вы оставите активную ячейку где-нибудь в исходных данных, то надстройка выведет итоговую таблицу прямо поверх них, и вы их потеряете. Причем отмена последнего действия будет невозможна, а кнопка Undo в нижней части панели не всегда срабатывает почему-то.
- Для вывода столбца с коэффициентом подобия FuzzyLookup.Similarity необходимо, чтобы у вашего Excel была точка в качестве десятичного разделителя (целой и дробной части). Если это не так, то эту настройку временно можно поменять через Файл - Параметры - Дополнительно (File - Options - Advanced) .
- Fuzzy Lookup - это не обычная надстройка, написанная на VBA (как мой PLEX, например), а COM-надстройка. Разница в том, что она устанавливается как отдельная программа, т.е. вам нужны соответствующие права на установку ПО на вашем компьютере. Дома, ясное дело, проблем не будет, а вот многим корпоративным пользователям, скорее всего, придется обращаться к вашим айтишникам. После установки отключать и подключать ее в дальнейшем можно на вкладке Разработчик - Надстройки COM (Developer - COM Add-ins) .
В любом случае, при всех имеющихся минусах, эта надстройка однозначно стоит того, чтобы находиться в арсенале любого продвинутого пользователя Microsoft Excel.
Иногда нам нужно не только выполнить точный поиск, но и выполнить нечеткий поиск, как показано на скриншоте ниже. Собственно, в Excel функция «Найти и заменить» несколько помогает при нечетком поиске.
Нечеткий поиск с помощью функции "Найти и заменить"
Предположим, у вас есть диапазон A1: B6, как показано на скриншоте ниже, вы хотите нечеткую строку поиска «яблоко» без учета регистра или «приложение» в этом диапазоне. Вы можете применить функцию «Найти и заменить» для обработки задания.
Искать без учета регистра
1. Выберите диапазон, который хотите найти, нажмите Ctrl + F ключи для включения Найти и заменить функции, введите строку, которую вы хотите найти, в текстовое поле Найти.
2. Нажмите Доступные опции чтобы развернуть диалоговое окно, снимите флажок Учитывать регистр вариант, но проверьте Соответствие всему содержимому ячейки опцию.
3. Нажмите Найти все, строки перечислены без учета регистра.
Найдите часть строки
1. Выберите диапазон и нажмите Ctrl + F ключи для включения Найти и заменить функции и введите строку детали, которую вы хотите найти, в Найти то, что текстовое поле, снимите флажок Соответствие всему содержимому ячейки вариант, при необходимости также можно снять отметку Учитывать регистр.
2. Нажмите Найти все, перечислены ячейки, содержащие строку.
Нечеткий поиск одного или нескольких значений с помощью удобного инструмента
Если вам нужно найти приблизительно одно значение или узнать все приблизительные значения одновременно, вы можете использовать Нечеткий поиск особенность Kutools for Excel.
После бесплатная установка Kutools for Excel, сделайте следующее:
Найдите примерно одно значение
Предположим, вы хотите найти значение «приложение» в диапазоне A1: A7, но количество различных символов не может быть больше 2, а количество символов должно быть больше 1.
1. Нажмите Кутулс > Найдите > Нечеткий поиск для Нечеткий поиск панель.
2. На всплывающей панели выполните следующие действия:
1) Выберите диапазон, который вы использовали для поиска, вы можете проверить Указанный чтобы зафиксировать диапазон поиска.
2) Проверить Найти по указанному тексту флажок.
3) Введите значение, по которому вы хотите выполнить нечеткий поиск, в поле Текст текстовое окно.
4) Задайте нужные критерии поиска.
3. Нажмите Найдите кнопку, затем щелкните стрелку вниз, чтобы развернуть список и просмотреть результаты поиска.
Нечеткий поиск нескольких значений
Предположим, вы хотите найти все приблизительные значения в диапазоне A1: B7, вы можете сделать следующее:
1. Нажмите Кутулс > Найдите > Нечеткий поиск для Нечеткий поиск панель.
2. в Нечеткий поиск панели, выберите диапазон поиска, а затем укажите необходимые критерии поиска.
3. Нажмите Найдите кнопку, чтобы перейти к просмотру результатов поиска, затем щелкните стрелку вниз, чтобы развернуть список.
Быстрое разделение данных на несколько листов на основе столбцов или фиксированных строк в Excel
Образец файла
Прочие операции (статьи)
Найдите наибольшее отрицательное значение (меньше 0) в Excel
Найти наибольшее значение из диапазона очень легко для большинства пользователей Excel, но как насчет поиска наибольшего отрицательного значения (меньше 0) из диапазона данных, смешанного с отрицательными и положительными значениями?
Найдите наименьший общий знаменатель или наибольший общий делитель в Excel
Все мы можем помнить, что нас просили вычислить наименьший общий знаменатель или наибольший общий знаменатель некоторых чисел, когда мы учимся. Но если их десять и более и какие-то большие числа, эта работа будет сложной.
Пакетный поиск и замена определенного текста в гиперссылках в Excel
В Excel вы можете выполнить пакетную замену определенной текстовой строки или символа в ячейках другим с помощью функции «Найти и заменить». Однако в некоторых случаях вам может потребоваться найти и заменить определенный текст в гиперссылках, исключая другие форматы содержимого.
Применение функции обратного поиска или поиска в Excel
Как правило, мы можем применить функцию поиска или поиска для поиска определенного текста слева направо в текстовой строке по определенному разделителю. Если вам нужно отменить функцию поиска, чтобы найти слово, начинающееся в конце строки, как показано на следующем снимке экрана, как вы могли бы это сделать?
Читайте также: