Как выделить повторяющийся текст/слова в ячейке Excel
В учебнике показано, как выделить повторяющиеся слова или текстовые строки в ячейке с помощью VBA.
Условное форматирование Excel позволяет выделять дубликаты всеми возможными способами: с 1-м вхождением или без него, в одном столбце или нескольких столбцах, последовательных повторяющихся ячейках и целых строках на основе идентичных значений в ключевом столбце. Но, как обычно, есть одно «но». Правила условного форматирования работают на уровне ячеек, в то время как вы можете выделить повторяющийся текст, а не целые ячейки. Это можно сделать только с помощью макросов. Даже если у вас нет опыта работы с VBA, не спешите закрывать эту страницу. Здесь вы найдете готовые к использованию примеры кода и подробные инструкции по их использованию в своих рабочих листах.
Выделите повторяющиеся слова в ячейке, игнорируя регистр текста
В этом примере показано, как закрасить повторяющиеся слова или текстовые строки в ячейке красным цветом шрифта, как показано на рисунке ниже. Обратите внимание, что строчные и прописные буквы считаются одними и теми же символами. Например, апельсин, АПЕЛЬСИН а также Апельсин считаются одним и тем же словом.
Код макроса следующий:
Public Sub HighlightDupesCaseInsensitive() Dim Cell As Range Dim Delimiter As StringDelimiter = InputBox(«Введите разделитель, который разделяет значения в ячейке», «Разделитель», «, «) Для каждой ячейки в Application.Selection Call HighlightDupeWordsInCell(Cell, Delimiter, False) Next End Sub Sub HighlightDupeWordsInCell (ячейка как диапазон, необязательный разделитель как строка = » «, необязательный регистрозависимый как логическое значение = True) Затемнить текст как строку Затемнить слова () как строку Затемнить слово как строку Dim wordIndex, matchCount, positionInText как целое число, если С учетом регистра Тогда слова = Разделить (Cell.Value, Delimiter) Другие слова = Разделить (LCase (Cell.Value), Разделитель) End If For wordIndex = LBound (слова) To UBound (слова) — 1 слово = слова (wordIndex) matchCount = 0 For nextWordIndex = wordIndex + 1 To UBound(words) Если word = words(nextWordIndex) Then matchCount = matchCount + 1 End If Next nextWordIndex Если matchCount > 0 Then text = «» For Index = LBound(words) To UBound(слова) текст = текст & слова(индекс) If (слова(индекс) = слово) Then Cell.Characters(Len(text) — Len(word) + 1, Len(word)).Font.Color = vbRed End If text = text & Разделитель Next End If Next wordIndex End Sub
Выделение повторяющегося текста в ячейке с учетом регистра
В большинстве случаев мы склонны игнорировать регистр букв при работе с текстовыми записями в Excel. Однако при определенных обстоятельствах регистр текста имеет значение. Например, если вы имеете дело с идентификаторами, паролями или другими подобными записями, такие строки, как 1-АА, 1-аа а также 1-Аа не дублируются и не должны быть выделены:
В этом случае используйте следующую версию кода:
Public Sub HighlightDupesCaseSensitive() Dim Cell As Range Dim Delimiter As StringDelimiter = InputBox(«Введите разделитель, который разделяет значения в ячейке», «Разделитель», «, «) Для каждой ячейки в Application.Selection Call HighlightDupeWordsInCell(Cell, Delimiter, True) Next End Sub Sub HighlightDupeWordsInCell (ячейка как диапазон, необязательный разделитель как строка = » «, необязательный регистрозависимый как логическое значение = True) Затемнить текст как строку Затемнить слова () как строку Затемнить слово как строку Dim wordIndex, matchCount, positionInText как целое число, если С учетом регистра Тогда слова = Разделить (Cell.Value, Delimiter) Другие слова = Разделить (LCase (Cell.Value), Разделитель) End If For wordIndex = LBound (слова) To UBound (слова) — 1 слово = слова (wordIndex) matchCount = 0 For nextWordIndex = wordIndex + 1 To UBound(words) Если word = words(nextWordIndex) Then matchCount = matchCount + 1 End If Next nextWordIndex Если matchCount > 0 Then text = «» For Index = LBound(words) To UBound(слова) текст = текст & слова(индекс) If (слова(индекс) = слово) Then Cell.Characters(Len(text) — Len(word) + 1, Len(word)).Font.Color = vbRed End If text = text & Разделитель Next End If Next wordIndex End Sub
Как использовать макросы для выделения повторяющихся слов в Excel
Если вы новичок в использовании VBA, приведенные ниже пошаговые инструкции помогут вам в этом. Опытные пользователи могут просто выбрать ссылку для скачивания и пропустить остальное 🙂
Добавьте код в книгу
Вы начинаете со вставки кода макроса в книгу Excel. Вот как:
- Откройте книгу, в которой вы хотите выделить дубликаты.
- Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
- На левой панели щелкните правой кнопкой мыши Эта рабочая тетрадь и выберите Вставлять > Модуль из контекстного меню.
- Вставьте код в окно кода.
- Чтобы сохранить макрос для будущего использования, обязательно сохраните книгу как файл .xlsm с поддержкой макросов.
Кроме того, вы можете скачать образец рабочей тетради и запустить макрос оттуда. Образец книги содержит следующие макросы:
- HighlightDupesCaseInsensitive — затеняет дубликаты внутри ячейки, игнорируя регистр букв.
- HighlightDupesCaseSensitive — выделяет дубликаты в ячейке с учетом регистра букв.
Дополнительные сведения см. в разделе Как вставить код VBA в Excel.
Запустить макрос
С кодом, добавленным в вашу рабочую книгу или загруженным и открытым файлом примера, запустите макрос следующим образом:
- На листе выберите ячейки, в которых вы хотите выделить повторяющийся текст. Это может быть один диапазон или несколько несмежных диапазонов.
- Нажмите Alt + F8.
- Выберите интересующий макрос и нажмите «Выполнить».
- Макрос попросит указать разделитель, разделяющий значения в выбранных ячейках. Предустановленный разделитель (в нашем случае запятая и пробел) появится в поле ввода автоматически. В зависимости от ваших потребностей вы можете оставить разделитель по умолчанию или ввести другой, а затем нажать «ОК».
Через мгновение все повторяющиеся строки в выбранных ячейках будут заштрихованы красным цветом (или другим цветом шрифта, установленным в вашем коде).
Кончик. Чтобы быстро удалить дубликаты в ячейке, вы можете использовать Remove Duplicate Substrings, один из многих инструментов для экономии времени, включенных в наш Ultimate Suite.
Как настроить код под свои нужды
С этими примечаниями по использованию и самыми базовыми знаниями VBA (или просто точно следуя приведенным ниже инструкциям) вы можете легко изменить коды в точном соответствии с вашими потребностями.
Поместите на тот же модуль
Как вы могли заметить, оба макроса (HighlightDupesCaseSensitive а также HighlightDupesCaseInsensitive) звоните HighlightDupeWordsInCell функция. Разница между двумя макросами выше только в третьем параметре (CaseSensitive), который передается в указанную функцию.
Для поиска с учетом регистра устанавливается значение TRUE:
Вызов HighlightDupeWordsInCell(Cell, Delimiter, True)
Для поиска без учета регистра устанавливается значение FALSE:
Вызов HighlightDupeWordsInCell(Cell, Delimiter, False)
Для работы макросов код HighlightDupeWordsInCell функция должна быть помещена в тот же модуль, что и макрос.
Разделитель
При запуске макрос попросит указать разделитель, разделяющий слова/строки в выбранных ячейках. Разделителем по умолчанию является запятая и пробел («,»), и он предустановлен в поле ввода:
Delimiter = InputBox(«Укажите разделитель, разделяющий значения в ячейке», «Разделитель», «, «)
В своем коде вы можете использовать любые другие символы в качестве предопределенного разделителя.
Цвет
По умолчанию HighlightDupeWordsInCell функциональные тени дублируются красным цветом шрифта. Цвет определяется в этой строке:
Cell.Characters(positionInText, Len(word)).Font.Color = vbRed
Здесь vbRed — это своего рода цветовая константа VBA. Чтобы дубликаты отображались другим цветом, вы можете заменить vbRed другой константой, такой как vbGreen, vbYellow, vbBlue и т. д. Список поддерживаемых цветовых констант можно найти здесь. здесь.
Вот как выделить повторяющиеся слова в ячейках Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
Примеры кода для выделения дубликатов в ячейке (файл .xlsm)
Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)
