Частичное совпадение текста в excel
Прошу помощи с нетривиальной задачей. В интернете много искал, но решения не нашел. Суть в том, что нужно сравнить два вида записей адресов. Один записывался роботом, второй записывался человеком. Во всех адресах можно найти совпадения, например, по улице.
1. Необходимо сравнить тексты из двух ячеек, найти в них общие последовательности символов. Столбец 1 (первый лист) и Столбец 1 (второй лист).
2. Если есть совпадение, то вывести из первой таблицы данные из 2 и 3 столбца.
Сложность в том, что у нас нет эталонного текста для сравнения, нужно найти именно частичное совпадение в двух разных ячейках. Стоит также понимать, что интересует именно совпадение именно по адресу, а не по цифре дома. Соответственно последовательность совпавших символов должна быть больше 3 (как мне кажется).
Прошу помощи с нетривиальной задачей. В интернете много искал, но решения не нашел. Суть в том, что нужно сравнить два вида записей адресов. Один записывался роботом, второй записывался человеком. Во всех адресах можно найти совпадения, например, по улице.
1. Необходимо сравнить тексты из двух ячеек, найти в них общие последовательности символов. Столбец 1 (первый лист) и Столбец 1 (второй лист).
2. Если есть совпадение, то вывести из первой таблицы данные из 2 и 3 столбца.
Сложность в том, что у нас нет эталонного текста для сравнения, нужно найти именно частичное совпадение в двух разных ячейках. Стоит также понимать, что интересует именно совпадение именно по адресу, а не по цифре дома. Соответственно последовательность совпавших символов должна быть больше 3 (как мне кажется). Alexnef
Прошу помощи с нетривиальной задачей. В интернете много искал, но решения не нашел. Суть в том, что нужно сравнить два вида записей адресов. Один записывался роботом, второй записывался человеком. Во всех адресах можно найти совпадения, например, по улице.
1. Необходимо сравнить тексты из двух ячеек, найти в них общие последовательности символов. Столбец 1 (первый лист) и Столбец 1 (второй лист).
2. Если есть совпадение, то вывести из первой таблицы данные из 2 и 3 столбца.
Сложность в том, что у нас нет эталонного текста для сравнения, нужно найти именно частичное совпадение в двух разных ячейках. Стоит также понимать, что интересует именно совпадение именно по адресу, а не по цифре дома. Соответственно последовательность совпавших символов должна быть больше 3 (как мне кажется). Автор - Alexnef
Дата добавления - 18.09.2020 в 18:06
Чтобы поисковые функции Excel: ВПР, ГПР и ПОИСКПОЗ выполняли точный поиск с точным совпадением искомого и проверяемого значения ячеек или возвращали ошибку, в последнем третьем их аргументе должно быть указано – ЛОЖЬ или 0. При этом независимо отсортирован ли просматриваемый список значений или нет.
Формула приблизительного поиска неточных совпадений в Excel
Поисковые функции Excel предназначенные для выборки значений из таблиц позволяют находить данные также если необходимо найти приблизительное значение. Но только в сортированных списках значений таблицы по возрастанию.
Ниже на рисунке проиллюстрировано метод расчета суммы налога. Таблица процентных ставок налога не содержит всевозможные варианты, а только некоторые определенные их границы пределов. Сначала необходимо определить, в котором диапазоне границ налоговой ставки будет находится зарплата того или иного сотрудника. А далее следует использовать полученную информацию из найденного наиболее близкого значения для налоговых расчетов:
Формула использует три функции ВПР для считывания 3-х значений с таблицы. В последнем аргументе каждой функции находится логическое значение ИСТИНА. Это значит, что необходимо найти приблизительное значение, а необязательно точное совпадение.
Чтобы любая поисковая функция выборки в Excel: ВПР, ГПР либо ПОИСКПОЗ со значением ИСТИНА в третьем аргументе возвращала правильный результат вычисления, данные в просматриваемом столбце (в данном примере это диапазон ячеек B2:B9) должны быть отсортированы по возрастанию. Функция ВПР поочередно проверяет все значения и закончит поиск, если следующее значение будет больше чем искомое. После чего поиск прекращается. Вот почему нужно сортировать исходный диапазон по возрастанию. Таким способом найдено наибольшее значение, которое одновременно с тем меньше чем искомое.
Внимание! Поиск приблизительного соответствия значений с помощью поисковых функций Excel не предоставляют возможность находить максимально приближенное значение. Возвращается лишь только наибольшее число, которое является меньшим от искомого. Даже если очередное проверяемое значение является максимально приблизительным к искомому значению.
Внимание! Если данные в просматриваемом функцией столбце не отсортированы по возрастанию, поисковая функция при выборке вернет не ошибку, а только лишь ошибочный результат (что еще хуже ошибки)! Функции, предназначенные для приблизительного поиска соответствий, используют в своих алгоритмах бинарный метод. Согласно этому алгоритму поиск начинается от середины столбца, а в процессе происходит проверка находится ли искомое значение в верхней или нижней части. Когда определена более подходящая часть столбца, она снова делится на половину и снова проверяется от своей середины. Данный процесс повторяется пока не будет найден результат.
Поэтому бинарный поиск по неотсортированным диапазонам значений с большой вероятностью может привести к тому, что поисковая функция выберет неправильную половину просматриваемого столбца и возвратит ошибочное значение (при этом без кода ошибки).
В выше приведенном примере функция ВПР закончит поиск на второй строке просматриваемого столбца так как число 1023 является наибольшим числом, которое меньше от искомого числа 2003,89. Условно формулу можно разделить на 3, которые последовательно выполняют следующие операции:
- Первая функция ВПР возвращает базовую налоговую ставку с третьего столбца таблицы, то есть число 69,80.
- Следующая функция ВПР ищет тоже самое приблизительное значение для числа 2003,89, но уже по первому столбцу «Зарплата от». После чего найденное приближенное значение вычитаемое от искомого.
- Третья функция ВПР возвращает процентную ставку с четвертого столбца таблицы. Полученная ставка умножается на чистую зарплату netto – после всех вычетов, а результат прибавляется к базовой ставке.
Когда все функции ВПР возвратят свои результаты, выполняются следующие арифметические вычисления с числами:
Поиск данных с приблизительным совпадением выполняется существенно быстрее чем при точном поиске. При точном совпадении поисковая функция должна проверять по очереди содержимое каждой ячейки в просматриваемом столбце. Если вы уверенны что исходные данные просматриваемого столбца отсортированы по возрастанию, можно ускорить точный поиск указав в третьем аргументе поисковой функции значение ИСТИНА. В случаи приблизительного совпадения значений, всегда будет найдено точное значение с точным совпадением с искомым. Главное, чтобы оно действительно фактически присутствовало в списке значений, а сам список было отсортирован по возрастанию.
Пример формулы для приблизительного поиска ИНДЕКС и ПОИСКПОЗ в Excel
Любые поисковые функции для выборки можно заменить формулой из комбинации функций ИНДЕКС и ПОИСКПОЗ. Последний аргумент функции ПОИСКПОЗ позволяет переключатся между приблизительными и точным поиском, подобно как в функциях ВПР и ГПР. Но отличительным преимуществом функции ПОИСКПОЗ является возможность выполнять поиск с приблизительным совпадением при отсортированных значениях по убыванию.
Ниже на рисунке приведена та же таблица с налоговыми ставками, но отсортирована по убыванию. Новая формула в ячейке … использует формулу функций ИНДЕКС и ПОИСКПОЗ возвращает правильный результат:
В тоже время новая формула в ячейке D17 возвращает правильный итоговый результат вычислений.
В отличии от других поисковых функций последним аргументом функции ПОИСКПОЗ может быть и отрицательное число, а точнее одно из трех вариантов: -1, 0, 1:
- Отрицательное значение -1 используется в случаях работы с данными отсортированными по убыванию. Функция возвращает из просматриваемого столбца наименьшее значение, которое является большим по отношению к искомому. Нельзя использовать отрицательную единицу с минусом (-1) в третьем аргументе для поисковых функций выборки данных ВПР и ГПР. Там нет соответственного режима работы функции.
- Значение 0 используется для обработки неотсортированных списков данных, с целью поиска точного совпадения значений с искомым. Поведение функции ПОИСКПОЗ с нулевым значением в третьем аргументе (0) – соответствует поведению функций ВПР и ГПР с тратим аргументом равному ЛОЖЬ или 0.
- Значение 1 применяется к спискам данных отсортированных по возрастанию. В таком случае функция возвращает из просматриваемого столбца наибольшее значение, которое меньше от искомого. Положительное число 1 в третьем аргументе функции ПОИСКПОЗ работает аналогично как ИСТИНА или 1 для функций ВПР и ГПР в этом же аргументе.
Так как функция ПОИСКПОЗ с последним аргументом равным отрицательному число -1 ищет значение больше чем искомое к возвращаемому результату следует добавить число +1, чтобы получить правильный номер строки для функции ИНДЕКС.
Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.
Примеры использования функции ПОИСКПОЗ в Excel
Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).
Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» - «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.
Например, массив содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ("яблоко";;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.
Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.
Формула для поиска неточного совпадения текста в Excel
Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.
Вид исходной таблицы данных:
Для нахождения позиции текстовой строки в таблице используем следующую формулу:
- D2&"*" – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
- B:B – ссылка на столбец B:B, в котором выполняется поиск;
- 0 – поиск точного совпадения.
Из полученного значения вычитается единица для совпадения результата с id записи в таблице.
Сравнение двух таблиц в Excel на наличие несовпадений значений
Пример 2. В Excel хранятся две таблицы, которые на первый взгляд кажутся одинаковыми. Было решено сравнить по одному однотипному столбцу этих таблиц на наличие несовпадений. Реализовать способ сравнения двух диапазонов ячеек.
Вид таблицы данных:
Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):
Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:
Как видно, третьи элементы списков не совпадают.
Поиск ближайшего большего знания в диапазоне чисел Excel
Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.
Вид исходной таблицы данных:
Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):
Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.
Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):
Как найти частичное совпадение строк в Excel?
Например, есть диапазон данных, а столбец E - это список имен учащихся, столбцы F и G - класс и оценка учащихся, а столбец H - рейтинг оценок учащихся, теперь у вас есть заданная имя «Джек», которое является частичной строкой «Джеки», как вы можете быстро найти частичное совпадение строки и вернуть рейтинг «Джеки», как показано ниже.
Частичное совпадение строк ВПР в Excel
Здесь у меня есть несколько формул, которые помогут вам найти частичное совпадение строк в Excel.
1. Выберите пустую ячейку, чтобы ввести частичную строку, которую вы хотите найти. Смотрите скриншот:
2. Выберите другую ячейку, в которую вы поместите искомое значение, и введите эту формулу. = ВПР ($ K $ 1 & "*", $ E $ 1: $ H $ 14,4, FALSE) , Нажмите Enter ключ, чтобы получить значение. Смотрите скриншот:
Наконечник:
1. В формуле K1 - это ячейка, содержащая частичную строку, E1: H14 - диапазон данных, 4 - значение подстановки в столбце Forth диапазона. Вы можете изменить их по своему усмотрению.
2. Если вы просто хотите найти, какое имя частично совпадает с заданным именем, вы также можете использовать эту формулу =INDEX($E$2:$E$14,MATCH($K$1&"*",E2:E14,0)) . (E2: E14 - это список столбцов, из которого вы хотите выполнить поиск, k1 - это заданное имя, вы можете изменить его по своему усмотрению.)
Найдите первое частичное совпадение
В некоторых случаях необходимо получить позицию первого частичного совпадения, содержащего определенное число в диапазоне числовых значений в Excel. В этом случае МАТЧ и ТЕКСТ формула, содержащая звездочку (*), подстановочный знак, который соответствует любому количеству символов, окажет вам услугу. И если вам также нужно знать точное значение в этой позиции, вы можете добавить ИНДЕКС функцию к формуле.
Получить позицию первого частичного совпадения
Чтобы получить позиция первого частичного совпадения, содержащего «345» из диапазона номеров, как показано выше, формула ПОИСКПОЗ и ТЕКСТ вместе с подстановочными знаками поможет вам: чтобы сопоставить любые числа, содержащие 345, вы поместите число 345 между двумя звездочками (*). Однако операция преобразует числовое значение в текстовое значение. Итак, вам придется использовать функцию ТЕКСТ для преобразования чисел в диапазоне чисел в текст. Только в этом случае функция ПОИСКПОЗ сможет правильно найти положение частичного совпадения.
Общий синтаксис
=MATCH("*"& number &"*",TEXT( lookup_array ,"0"),0)
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- номер: Номер, который вы указали для поиска его первого частичного совпадения.
- искомый_массив: Диапазон числовых значений, из которого требуется получить позицию первого частичного совпадения.
Чтобы получить позиция первого номера совпадения, содержащего «345» , скопируйте или введите приведенные ниже формулы в ячейку E6 и нажмите Ctrl + Shift + Enter чтобы получить результат:
= ПОИСКПОЗ ("*" & 345 &"*",ТЕКСТ( B5: B16 , "0"), 0)
Или используйте ссылку на ячейку, чтобы сделать формулу динамической:
= ПОИСКПОЗ ("*" & E5 &"*",ТЕКСТ( B5: B16 , "0"), 0)
√ Примечание: чтобы склеить ссылку / номер ячейки и текст, вы должны добавить между ними амперсанд (&). И текст должен быть заключен в двойные кавычки.
Пояснение формулы
=INDEX( "*"&345&"*" , TEXT(B5:B16,"0") ,0)
- ТЕКСТ (B5: B16, «0») : Функция ТЕКСТ преобразует все числовые значения в B5: B16 в текст с кодом формата "0". Итак, мы получим такой текстовый массив: .
Щелкните здесь, чтобы узнать больше о функции ТЕКСТ. - ПОКАЗАТЕЛЬ( "*" & 345 & "*" , ТЕКСТ (B5: B16, «0») , 0) = ИНДЕКС ( "*" & 345 & "*" , , 0): Значение поиска "*" & 345 & "*" может соответствовать любым текстовым строкам, содержащим строку «345», независимо от того, какая позиция 345 находится в текстовых строках. В match_type 0 просит функцию ПОИСКПОЗ найти позицию первого точного искомого значения в массиве. Итак, МАТЧ вернется 5.
Получить первое частичное совпадение
Чтобы получить первый частичный номер совпадения на основе позиции, предоставленной MATCH, как показано ниже, мы можем обратить внимание на функцию INDEX.
Общий синтаксис
=INDEX(MATCH( return_range , "*"&number&"*" ,TEXT( lookup_array ,"0"),0))
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, из которого вы хотите, чтобы комбинационная формула возвращала первое частичное совпадение.
- номер: Номер, который вы указали для поиска его первого частичного совпадения.
- искомый_массив: Диапазон числовых значений, из которого требуется получить первое частичное совпадение.
Чтобы получить номер первого совпадения, содержащий «345», скопируйте или введите приведенные ниже формулы в ячейку E7 и нажмите Ctrl + Shift + Enter чтобы получить результат:
= ИНДЕКС (ПОИСКПОЗ ( B5: B16 , "*" & 345 & "*" ,ТЕКСТ( B5: B16 , "0"), 0))
Или используйте ссылку на ячейку, чтобы сделать формулу динамической:
= ИНДЕКС (ПОИСКПОЗ ( B5: B16 , "*" & E5 & "*" ,ТЕКСТ( B5: B16 , "0"), 0))
Пояснение формулы
=INDEX( B5:B16 , MATCH("*"&E5&"*", TEXT(B5:B16,"0") ,0) )
- ПОИСКПОЗ ("*" & E5 & "*", ТЕКСТ (B5: B16, «0») , 0) = 5 :Нажмите, чтобы увидеть подробный процесс .
- ПОКАЗАТЕЛЬ( B5: B16 , ПОИСКПОЗ ("*" & E5 & "*", ТЕКСТ (B5: B16, «0») , 0) ) = ИНДЕКС ( B5: B16 , 5 ): Функция ИНДЕКС возвращает 5th значение в возвращаемом диапазоне B5: B16, Которая является 34545.
Связанные функции
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
Функция ТЕКСТ преобразует значение в текст с заданным форматом в Excel.
Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Связанные формулы
Чтобы получить позицию первого частичного совпадения, содержащего определенную текстовую строку в диапазоне в Excel, вы можете использовать формулу ПОИСКПОЗ с подстановочными знаками - звездочкой (*) и вопросительным знаком (?).
Чтобы найти наиболее близкое соответствие искомому значению в числовом наборе данных в Excel, вы можете использовать функции ИНДЕКС, ПОИСКПОЗ, АБС и МИН вместе.
В некоторых случаях может потребоваться поиск ближайшего или приблизительного значения соответствия на основе нескольких критериев. С помощью комбинации функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы можете быстро сделать это в Excel.
В этом руководстве мы поговорим о том, как искать приблизительное совпадение на основе нескольких критериев, перечисленных в столбцах и строках электронной таблицы Excel, с помощью функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Читайте также: