KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как классифицировать текстовые строки в Excel на основе ключевых слов?

АвторСяоянДата изменения

При работе с большими объёмами данных в Excel часто возникает необходимость классифицировать текстовые записи по ключевым словам — это упрощает отчётность и аналитику. Допустим, у вас есть список текстовых строк в столбце A, и вы хотите автоматически присваивать каждой записи категорию из столбца D: например, отнести все ячейки, содержащие «Kutools», к категории «Extendoffice», а строки с упоминанием «футбол» или «баскетбол» — к категории «Спорт». На снимке экрана ниже показан подобный сценарий. Ручная обработка таких данных быстро становится утомительной и подверженной ошибкам, особенно по мере роста объёма информации. В этой статье мы рассмотрим практичные и эффективные способы автоматической классификации текстовых строк в Excel — чтобы ускорить организацию данных и повысить качество вашего рабочего процесса.

категоризация текстовых строк на основе ключевых слов

Классификация списка текстовых строк на основе ключевых слов с помощью формул массива

Классификация текстовых строк на основе ключевых слов с помощью кода VBA

Классификация списка текстовых строк на основе ключевых слов с помощью формул массива

Вы можете использовать формулу массива, чтобы автоматически присваивать категории текстовым строкам на основе совпадающих ключевых слов. Это особенно удобно при работе с большими наборами данных, где ручная сортировка невозможна. Формулы массива открывают мощные возможности сопоставления шаблонов в Excel, обеспечивая динамическую связь между данными и группами ключевых слов. Такой подход незаменим, например, для автоматической маркировки входящих отзывов клиентов, учёта товаров или сортировки электронных писем по содержанию.

1. Расположите данные в правильном порядке: как правило, текстовые строки (подлежащие классификации) размещаются в столбце A, ключевые слова — в столбце D, а соответствующие категории — в столбце E. Пример структуры набора данных приведён на снимке экрана ниже.

пример данных

2. Перейдите в пустую ячейку рядом с исходными данными (например, начните с ячейки B2, если ваши строки находятся в диапазоне A2:A10) и введите следующую формулу массива:

=INDEX($E$2:$E$8,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$8,A2)),0))
  • Советы: В этой формуле:
  • $E$2:$E$8: диапазон, содержащий названия категорий, которые необходимо присвоить каждой текстовой строке;
  • $D$2:$D$8: столбец, содержащий ключевые слова, которые вы хотите найти в своих текстовых строках;
  • A2: ячейка с текстовой строкой, которую нужно проанализировать и классифицировать.

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

3. После ввода формулы нажмите Ctrl + Shift + Enter (вместо обычного Enter). Excel автоматически заключит формулу в фигурные скобки, указывая, что это формула массива. Таким образом для первой строки будет сгенерирована соответствующая категория. Затем воспользуйтесь маркером заполнения, чтобы протянуть формулу вниз по столбцу и автоматически классифицировать все записи в списке. См. снимок экрана:

Категоризация списка текстовых строк на основе ключевых слов с помощью формул массива

При работе с формулами массива крайне важно использовать правильную комбинацию клавиш, а при копировании формулы — следить за тем, чтобы не нарушалась структура массива. Если ваш диапазон данных расширяется, обязательно обновите диапазоны в формуле для точной классификации.

скриншот kutools for excel ai

Раскройте магию Excel с помощью KUTOOLS AI

  • Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
  • Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
  • Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
  • Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
  • Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Расширьте возможности Excel с помощью инструментов на базе ИИ.Скачать сейчаси ощутите эффективность, как никогда раньше!

Классификация текстовых строк на основе ключевых слов с помощью кода VBA

Если вам регулярно нужно классифицировать большие объёмы текстовых строк и вы ищете гибкое автоматизированное решение, VBA позволяет динамически обрабатывать данные и присваивать категории на основе совпадений ключевых слов. Этот подход идеально подходит продвинутым пользователям, стремящимся настроить собственные правила классификации или интегрировать их в другие автоматизированные рабочие процессы. Макрос VBA значительно сокращает ручной труд и обеспечивает надёжную обработку ошибок при работе с пользовательскими парами «ключевое слово — категория».

1. Откройте редактор VBA в Excel, выбрав Инструменты разработчика > Visual Basic. В открывшемся окне создайте новый модуль, последовательно выбрав Вставка > Модуль. Скопируйте и вставьте в него следующий код VBA:

Sub CategorizeByKeywords()
    Dim cell As Range
    Dim keywordRng As Range
    Dim categoryRng As Range
    Dim i As Integer
    Dim lastRowA As Long
    Dim lastRowD As Long
    Dim matchFound As Boolean
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    lastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    lastRowD = Cells(Rows.Count, "D").End(xlUp).Row
    
    Set keywordRng = Range("D2:D" & lastRowD)
    Set categoryRng = Range("E2:E" & lastRowD)
    
    For Each cell In Range("A2:A" & lastRowA)
        matchFound = False
        For i = 1 To keywordRng.Rows.Count
            If InStr(1, cell.Value, keywordRng.Cells(i, 1).Value, vbTextCompare) > 0 Then
                cell.Offset(0, 1).Value = categoryRng.Cells(i, 1).Value
                matchFound = True
                Exit For
            End If
        Next i
        
        If Not matchFound Then
            cell.Offset(0, 1).Value = "" ' Assign blank if no keyword matched
        End If
    Next cell
End Sub

2. Чтобы запустить макрос, нажмите кнопку Кнопка запуска«Выполнить» или клавишу F5 в окне VBA. Макрос обработает все записи в столбце A, начиная со строки 2, найдёт ключевые слова из столбца D и запишет соответствующую категорию из столбца E в столбец B.

Не забудьте скорректировать диапазоны ключевых слов и категорий, если ваш список превышает приведённый в примере. При необходимости настройте макрос для обработки перекрывающихся или множественных совпадений ключевых слов либо добавьте категории по умолчанию для элементов без соответствий. Всегда создавайте резервную копию данных перед запуском нового кода и сохраняйте книгу в формате с поддержкой макросов (.xlsm) для дальнейшего использования.

Скачать образец файла

Нажмите, чтобы скачать образец файла!

Другие статьи по теме классификации текста:

  • Классификация данных На основе значения в Excel
  • Допустим, вам нужно классифицировать список данных на основе значений: если значение больше 90 — присвоить категорию «Высокий», если от 60 до 90 — «Средний», а если меньше 60 — «Низкий». Как реализовать такую задачу в Excel?
  • Присвоение значения или категории на основе числового диапазона
  • В этой статье рассказывается, как присваивать определённое значение или категорию в зависимости от того, в какой ограниченный диапазон попадает число в Excel. Например: если число находится в диапазоне от 0 до 100 — присвойте ему значение 5; от 101 до 500 — значение 10; от 501 до 1000 — значение 15. Описанный в статье метод поможет вам легко решить эту задачу.
  • Расчёт или присвоение буквенной оценки в Excel
  • Присвоение буквенной оценки каждому учащемуся на основе его баллов — распространённая задача для преподавателя. Например, у меня есть шкала оценивания, согласно которой баллы от 0 до 59 соответствуют оценке F, от 60 до 69 — D, от 70 до 79 — C, от 80 до 89 — B, а от 90 до 100 — A, как показано на следующем снимке экрана. Как в Excel быстро и легко определить буквенную оценку по числовому баллу?

Лучшие инструменты для повышения продуктивности в офисе

Kutools для Excel решает большинство ваших задач и повышает продуктивность на 80 %

  • Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
  • Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
  • Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
  • Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
  • Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
  • Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
  • Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
  • Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
  • Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…
kte tab 201905
  • Включите вкладки для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна — это удобнее, чем использовать отдельные окна.
  • Повышает продуктивность на 50 % и экономит сотни кликов мышью каждый день!
officetab bottom