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

0013

В этой статье мы узнаем, как категоризировать в Excel с помощью ключевых слов.

Возьмем пример опроса, о котором мы говорили.

Пример: категоризация данных, собранных в результате опроса в Excel

Итак, мы провели опрос о нашем новом смартфоне xyz. Мы спросили наших клиентов, что им нравится в телефоне xyz, и получили их ответы в формате Excel. Теперь нам нужно узнать, кому понравился наш светодиодный экран, динамик и камера.

0014

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

0015

Комментарии находятся в диапазоне A2: A9, ключевые слова находятся в E2: E10, а категория — в F2: F10.

Общая формула для создания категорий:

{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(words,sentence)),0))}

Обратите внимание на фигурные скобки, это формула массива. Необходимо ввести с помощью CTRL + SHIFT + ENTER.

Категория: это диапазон, содержащий список категорий. Предложения или отзывы будут классифицироваться по этим значениям. В нашем случае это F2: F10.

Слова: это диапазон, содержащий список ключевых слов или фраз.

Они будут искать в предложениях. Вот он E2: E10.

Приговор: это предложение, которое будет категоризировано. Это единственная относительная ячейка.

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

В ячейке B2 напишите эту формулу и нажмите CTRL + SHIFT + ENTER.

{=INDEX($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0))}

Скопируйте формулу, чтобы увидеть категорию каждого отзыва.

0016

Нам нужно зафиксировать список слов и категорий, они не должны меняться при копировании формулы, поэтому я дал абсолютную ссылку на ключевые слова и категории. Хотя мы хотим, чтобы предложения менялись при копировании формулы, поэтому я использовал относительную ссылку как A2.

Вы можете прочитать о link: / excel-generals-relative-and-absolute-reference-in-excel [здесь ссылка в excel.]

Теперь вы можете подготовить отчет, скольким пользователям понравился светодиодный экран, динамик и камера.

Как это работает?

Ядро формулы: link: / logic-formulas-the-isnumber-function-in-excel [ISNUMBER] (link: / text-excel-search-function [SEARCH] ($ E $ 2: $ E $ 10, A2)) *:

Я подробно объяснил это link: / counting-countifs-with-or-for-множественные критерии [здесь].

Функция ПОИСК ищет каждое значение ключевых слов ($ E $ 2: $ E $ 10) в предложении A2. Он возвращает массив найденного местоположения слова или #VALUE (для слова не найдено). Наконец, для этого примера у нас будет массив из 9 элементов.

\ {# VALUE!; 5; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!}.

Затем мы используем функцию ISNUMBER для преобразования этого массива в полезные данные. Он преобразует его в массив ИСТИНА и ЛОЖЬ.

\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Теперь все просто: link: / lookup-formulas-use-index-and-match-to-lookup-value [index match].

link: / lookup-formulas-excel-match-function [MATCH] (TRUE, link: / logic-formulas-the-isnumber-function-in-excel [ISNUMBER] (`link: / text-excel- функция поиска [ПОИСК] `($ E $ 2: $ E $ 10, A2)), 0):

функция ПОИСКПОЗ ищет ИСТИНА в результирующем массиве и возвращает индекс первого найденного ИСТИНА. что в данном случае равно 2.

ИНДЕКС ($ F $ 2: $ F $ 10, ПОИСКПОЗ (ИСТИНА, ЕЧИСЛО (ПОИСК ($ E $ 2: $ E $ 10, A2)), 0)): затем функция ИНДЕКС просматривает вторую позицию в категории ($ F $ 2: $ F $ 10), который является светодиодным экраном. Наконец, эта формула классифицирует этот текст или отзыв как светодиодный экран.

Делаем его чувствительным к регистру:

Чтобы сделать эту функцию чувствительной к регистру, используйте функцию НАЙТИ вместо функции ПОИСК. Функция НАЙТИ по умолчанию чувствительна к регистру.

{=INDEX(Category,MATCH(TRUE,ISNUMBER(FIND(words,sentence)),0))}

Слабые стороны:

  1. Если два ключевых слова находятся в одном предложении, предложение будет классифицировано в соответствии с первым ключевым словом в списке.

0017

Захват текста внутри другого слова. Предположим, мы ищем LAD в диапазоне. Затем будут подсчитаны слова, содержащие LAD. Например, лестницы будут учитываться для LAD, поскольку они содержат LAD. Так что будьте осторожны. Лучшая практика — максимально нормализовать ваши данные.

Итак, это был краткий урок о том, как категоризировать данные в Excel. Я попытался объяснить это как можно проще. Пожалуйста, дайте мне знать, если у вас есть какие-либо сомнения по поводу этой статьи или статей, связанных с Excel.

Скачать файл:

: Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.