Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Сравнение текста по части предложения

Довольно часто возникает проблема сравнения двух строк(ячеек) по части текста. Если точнее - по совпадению слов. Чем больше слов в двух строках совпадает - тем больше они считаются похожими. Так, к примеру текст "Защитная пленка iPhone" и текст "Защитная пленка для Samsung GalaxyII" совпадут только на 40%, а "шла маша по шоссе" и "маша по шоссе шла" - на 100%.
Я не имею ввиду сейчас случаи вроде двух строк: "привет" и "превет". Для подобного сравнения можно написать решения различные, но скорость их выполнения как правило оставляет желать лучшего, да и точность такого сравнения тоже не на высоте, если не использовать всевозможные справочники
На деле подобная задача встречается достаточно часто и предположу, что данная статья может быть полезна очень многим. Итак, как ни жаль, но подобную задачу невозможно решить без применения Visual Basic for Applications(VBA). Решение, которое я предложу - функция пользователя. Поэтому прежде чем его использовать настоятельно рекомендую прочесть следующие статьи:


Собственно сам код функции:

Option Explicit
Option Compare Text
'---------------------------------------------------------------------------------------
' Procedure : CompareTxt
' DateTime  : 10.03.2015 22:46
' Author    : The_Prist(Щербаков Дмитрий)
'             WebMoney - R298726502453; Яндекс.Деньги - 41001332272872
'             http://www.excel-vba.ru
' Purpose   : Сравнивает две строки по совпадению отдельных слов. Выводит процент, саму строку и номер строки
'             s1       - исходный текст(ссылка на ячейку или текст)
'             mass     - диапазон значений для сравнения с исходным текстом(ссылка на ячейку или текст)
'             sDelim   - разделитель слов в тексте. По умолчанию пробел
'             lFstLast - указатель, выводить первое или последнее подходящее совпадение.
'                        По умолчанию 0(последнее максимально совпадающее).
'                        Если указать 1 - будет выбрано первое подходящее(в котором совпадают все слова)
'             lShowAllInfo - указатель на результат. Допускается четыре значения:
'                            -1 - показывается вся информация: Процент совпадения строк, Найденное значение,
'                                 Номер строки в указанном диапазоне в которой найдено значение
'                             1 - Выводится только процент совпадения строк
'                             2 - выводится только значение
'                             3 - выводится только номер строки с найденным значением
'                             По умолчанию применяется -1(вся информация)
'             Синтаксис:
'                    =CompareTxt(A1;B1:B100)         - с разделителем по умолчанию
'                    =CompareTxt(A1;B1:B100;"-")     - с разделителем короткое тире(-)
'                    =CompareTxt(A1;B1:B100;"-";;2)  - с разделителем короткое тире(-) и выводом только значения
'---------------------------------------------------------------------------------------
Function CompareTxt(s1 As String, mass As Range, Optional sDelim As String = " ", Optional lFstLast As Long = 0, Optional lShowAllInfo As Long = -1)
    Dim as1, as2, l1 As Long, l2 As Long, lr As Long
    Dim asStr2
    Dim s As String, s2 As String, lp, lTmpCom As Long, lResCom As Long
    Dim lResR As Long, sResS As String, v
 
    as1 = Split(s1, sDelim)
    asStr2 = mass.Value
    If Not IsArray(asStr2) Then ReDim asStr2(1 To 1, 1 To 1): asStr2(1, 1) = mass.Value
 
    For lr = 1 To UBound(asStr2, 1)
        as2 = Split(asStr2(lr, 1), sDelim)
        lResCom = 0
        For l1 = LBound(as1) To UBound(as1)
            s = as1(l1)
            For l2 = LBound(as2) To UBound(as2)
                If as2(l2) = s Then
                    lResCom = lResCom + 1
                    Exit For
                End If
            Next l2
        Next l1
        If lTmpCom < lResCom Then
            lTmpCom = lResCom
            lResR = lr
            sResS = asStr2(lr, 1)
            lp = lp + 1
        End If
        If lFstLast Then
            If lTmpCom >= (UBound(as1) + 1) Then
                Exit For
            End If
        End If
    Next lr
    v = (lTmpCom / (UBound(as1) + 1)) * 100
    Select Case lShowAllInfo
    Case -1
        CompareTxt = "Процент совпадения: " & v & "; Значение: " & sResS & "; Строка в массиве mass: " & lResR
    Case 1 'только процент
        CompareTxt = v
    Case 2 'только значение строки
        CompareTxt = sResS
    Case 3 'только номер строки
        CompareTxt = lResR
    End Select
End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(Alt+F11) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Shift+F3), отыскав её в категории Определенные пользователем (User Defined Functions).
Функция ищет указанное значение(s1) в массиве значений(mass) и выводит максимально подходящее значение. Максимально подходящее, естественно, полное совпадение - то, которое совпадает на 100%. Если же полного совпадения среди значений массива(mass) не будет найдено, то будет выведено значение с максимальным процентом совпадения. В таких случаях всегда можно указать последним аргументом(lShowAllInfo) -1 или 3, чтобы посмотреть номер строки в указанном диапазоне(mass) и сверить уже глазами подходит это значение или нет.
Синтаксис:
=CompareTxt(A1;B1:B100) - с разделителем по умолчанию
=CompareTxt(A1;B1:B100;"-") - с разделителем короткое тире(-)
=CompareTxt(A1;B1:B100;"-";;2) - с разделителем короткое тире(-) и выводом только значения

Аргументы:
s1 - исходный текст(ссылка на ячейку или текст)
mass - диапазон значений для сравнения с исходным текстом(ссылка на ячейку или текст)
sDelim - разделитель слов в тексте. По умолчанию пробел.
lFstLast - указатель, выводить первое или последнее подходящее совпадение. По умолчанию 0(последнее максимально совпадающее). Если указать 1 - будет выбрано первое подходящее(в котором совпадают все слова)
lShowAllInfo - указатель на результат. Допускается четыре значения:

  • -1 - показывается вся информация: Процент совпадения строк, Найденное значение, номер строки в указанном диапазоне в которой найдено значение
  • 1 - Выводится только процент совпадения строк
  • 2 - выводится только значение
  • 3 - выводится только номер строки с найденным значением. По умолчанию применяется -1(вся информация)

Ниже функция в файле с примерами использования:

  Tips_Macro_ComparePart.xls (50,5 KiB, 3 799 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 6 комментариев
  1. Дима:

    Благодарю за макрос. Проблема в том, что у меня он работает только по диапазону не более 118 значений (все вычисления):
    =CompareTxt(B2;$A$2:$A$119;;;1)

    Как только я ввожу диапазон $A$2:$A$120 и более, то сразу макрос по данной формуле перестаёт работать и выдаёт #ЗНАЧ во всех графах где прописываю такое (уже не работает):
    =CompareTxt(B2;$A$2:$A$120;;;1)

    Почему так происходит, ведь мне необходимо делать выборку в более 4000 ячеек? т.е. диапазон нужен такой $A$2:$A$5000
    Что делать чтобы его увеличить более чем на 118 ячеек? Стандартные формулы работают с диапазоном более 10000
    Моя система: MS EXEL 2010, Windows 7, Visual Basic version 1590 VBA: Retail 7.0.1590

  2. Дима:

    Проблему решил! В данных, где была выборка (столбец А), при изначальном копировании значений в эти ячейки, некоторые слова с плюсом вначале (например: "+как найти") встали в таком синтаксисе: "=+как найти", т.е. прилепился знак равно (=) и, из-за этого вместо нормальных значений встало: "!ИМЯ" (типа этого). Как раз первое такое значение было на строке 120.

    Эти знаки равно убрал и всё заработало как нужно, благодарю.

  3. Вадим:

    Красава! просто, красава. только здесь
    v = (lTmpCom / (UBound(as1) + 1)) * 100
    "+1" - лишнее (или нет?), но на 100 умножать точно нецелесообразно - неудобно потом работать со значением, если речь о проценте

    • Вадим, спасибо за отзыв.
      "+1" не лишнее, т.к. массивы в VBA начинаются с нуля.
      По поводу процентов: не соглашусь с "точно нецелесообразно". Для Вас нецелесообразно, а для кого-то может быть более удобно, чем без этого :) Сделано специально, чтобы при выводе в качестве результата всей информации показывалось именно число процентов: "Процент совпадения: 12; Значение: тест; Строка в массиве mass: 4". В ином случае процент совпадения может вводить в заблуждение цифрами вроде 0,12.
      Полагаю, если кому-то это неудобно(например, если используется метод вывода 1 и формат ячеек Процентный) - всегда в конце можно разделить на 100, удалить это из кода вовсе или добавить только для вывода процентов отдельно деление на 100 обратно:
      Case 1 'только процент
      CompareTxt = v/100

      • Вадим:

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

        п.с. если есть возможность - оптимизируйте сайт, долго грузятся страницы, что чревато

      • Вадим:

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

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти