мой другЯ часто общаюсь с ним по поводу некоторых реальных проблем, с которыми он сталкивается при работе с данными в Excel.
Много раз я перевожу его запрос в учебник по Excel на этом сайте, потому что он может быть полезен и другим моим читателям.
Это тоже один из таких уроков.
Мой друг позвонил мне на прошлой неделе со следующими вопросами:
У меня есть адресные данные в столбце Excel, и я хочу идентифицировать/фильтровать ячейки, содержащие повторяющиеся текстовые строки (слова) в адресе.
Вот аналогичный набор данных, в котором он хочет отфильтровать ячейки (ячейки с красными стрелками), в которых есть повторяющиеся текстовые строки:
Что делает это трудным сейчас, так это то, что данные не согласуются.Поскольку это подборка наборов данных, созданных торговыми представителями вручную, в наборах данных могут быть расхождения.
考慮 一下 ：
- Любая текстовая строка может повторяться в этом наборе данных.Например, это может быть название региона или города, или и то, и другое.
- Слова разделены пробелами, и нет никакой согласованности в том, следует ли название города после шести или восьми символов.
- Таких записей тысячи, и нужно отфильтровать те, в которых есть повторяющиеся текстовые строки.
После рассмотрения многих вариантов, таких как текст в столбцы и формулы, я, наконец, решил использовать VBA для выполнения этой работы.
Поэтому я создал пользовательскую функцию VBA («IdDuplicate») для анализа этих ячеек и выдачи мне TRUE, если в текстовой строке есть повторяющиеся слова, и FALSE, если дубликатов нет (как показано ниже):
Эта пользовательская функция анализирует каждое слово в текстовой строке и проверяет, сколько раз оно встречается в тексте.Возвращает TRUE, если счетчик больше 1;В противном случае вернуть ЛОЖЬ.
Кроме того, он был создан только для подсчета слов, содержащих более трех символов.
Когда у меня есть данные TRUE/FALSE, я могу легко отфильтровать все записи, которые являются TRUE.
Теперь позвольте мне показать вам, как это сделать в 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, то 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, KGM или LDA), эти слова будут игнорироваться при подсчете дубликатов.При необходимости вы можете легко изменить это в коде.
Эта функция доступна только в рабочей книге, в которую вы скопировали код модуля.Если вы хотите, чтобы этот код был доступен и в других книгах, вам нужно скопировать и вставить этот код в эти книги.Кроме того, вы можете создать надстройку (включение которой сделает эту функцию доступной во всех книгах в вашей системе).
Кроме того, не забудьте сохранить эту книгу с расширением .xlsm (поскольку она содержит код макроса).
