Один из моих друзей работает в аналитической компании в сфере здравоохранения. Он часто рассказывает мне о некоторых реальных проблемах, с которыми он сталкивается при работе с данными в Excel.
Много раз я конвертировал его запросы в учебные пособия по Excel на этом сайте, так как это могло быть полезно и для других моих читателей.
Это тоже одно из таких пособий.
Мой друг позвонил мне на прошлой неделе со следующей проблемой:
В столбце Excel есть адресные данные, и я хочу идентифицировать / фильтровать ячейки, в которых в адресе есть повторяющиеся текстовые строки (слова).
Вот аналогичный набор данных, в котором он хотел отфильтровать ячейки, в которых есть повторяющаяся текстовая строка (те, которые отмечены красными стрелками):
Что делает это трудным, так это то, что в этих данных нет последовательности. Поскольку это компиляция набора данных, созданного торговыми представителями вручную, в наборе данных могут быть вариации.
Учти это:
- Любая текстовая строка может повторяться в этом наборе данных. Например, это может быть название района, название города или и то, и другое.
- Слова разделяются пробелом, и нет никакого единообразия в том, будет ли название города там после шести или восьми слов.
- Таких записей тысячи, и необходимо отфильтровать те записи, в которых есть повторяющиеся текстовые строки.
Рассмотрев множество вариантов (например, текст в столбцы и формулы), я наконец решил использовать VBA для этого.
Поэтому я создал специальную функцию VBA («IdDuplicate») для анализа этих ячеек и выдачи мне ИСТИНА, если в текстовой строке есть повторяющееся слово, и ЛОЖЬ, если повторений нет (как показано ниже):
Эта настраиваемая функция анализирует каждое слово в текстовой строке и проверяет, сколько раз оно встречается в тексте. Если счетчик больше 1, возвращается ИСТИНА; иначе он вернет ЛОЖЬ.
Кроме того, он был создан только для подсчета слов длиной более трех символов.
Когда у меня есть данные ИСТИНА / ЛОЖЬ, я могу легко отфильтровать все записи, которые ИСТИННЫ.
Теперь позвольте мне показать вам, как это сделать в Excel.
Код VBA для пользовательской функции
Это делается путем создания пользовательской функции в VBA. Затем эту функцию можно использовать как любую другую функцию рабочего листа в Excel.
Вот код VBA для него:
Функция IdDuplicates (rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Шаг -1 Если Len (StringtoAnalyze (i)) <minWordLen Then GoTo SkipA For j = 0 To i - 1 Если StringtoAnalyze (j) = StringtoAnalyze (i), то IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: конец функции
Спасибо Уолтеру за предложение лучшего подхода к этому коду!
Как использовать этот код VBA
Теперь, когда у вас есть код VBA, вам нужно поместить его в серверную часть Excel, чтобы он мог работать как обычная функция рабочего листа.
Ниже приведены шаги по размещению кода VBA на серверной части:
- Перейдите на вкладку Разработчик.
- Щелкните Visual Basic (вы также можете использовать сочетание клавиш ALT + F11)
- В открывшейся серверной части редактора VB щелкните правой кнопкой мыши любой из объектов книги.
- Перейдите в «Вставить» и нажмите «Модуль». Это вставит объект модуля для книги.
- В окне кода модуля скопируйте и вставьте указанный выше код VBA.
Когда у вас есть код VBA в серверной части, вы можете использовать функцию «IdDuplicates» как любую другую обычную функцию рабочего листа.
Эта функция принимает один единственный аргумент, который является ссылкой на ячейку, в которой находится текст.
Результат функции - ИСТИНА (если в ней есть повторяющиеся слова) или ЛОЖЬ (если нет дубликатов). Когда у вас есть этот список ИСТИНА / ЛОЖЬ, вы можете отфильтровать те, которые имеют ИСТИНА, чтобы получить все ячейки, в которых есть повторяющиеся текстовые строки.
Примечание: я создал код только для того, чтобы учитывать те слова, длина которых превышает три символа. Это гарантирует, что если в текстовой строке есть слова длиной 1, 2 или 3 символа (например, 12 A, K G M или L D A), они будут проигнорированы при подсчете дубликатов. Если хотите, можете легко изменить это в коде.
Эта функция будет доступна только в книге, в которую вы скопировали код в модуле. Если вы хотите, чтобы это было доступно и в других книгах, вам необходимо скопировать и вставить этот код в эти книги. В качестве альтернативы вы также можете создать надстройку (включение которой сделает эту функцию доступной во всех книгах в вашей системе).
Кроме того, не забудьте сохранить эту книгу с расширением .xlsm (поскольку в ней есть код макроса).
