Категоризирането на низ въз основа на някои думи беше една от основните ми задачи при анализа на данни. Например, в проучване, ако попитате хората какво им харесва в конкретен смарт телефон, същите отговори ще имат различни думи. За камера те могат да използват думите като снимки, видеоклипове, селфита и т.н. Всички те предполагат камера. Така че е много важно да категоризирате изреченията преди, за да получите някаква смислена информация.
В тази статия ще научим как да категоризираме в Excel с помощта на ключови думи.
Нека вземем примера с анкетата, за която говорихме.
Пример: Категоризиране на данни, събрани от проучване в Excel
И така, направихме проучване за нашия нов смартфон xyz. Попитахме нашите клиенти какво харесват за xyz телефона и уловихме отговора им в excel. Сега трябва да знаем кой харесва нашия LED екран, високоговорител и камера.
За тази цел сме подготвили списък с ключови думи, които може да се отнасят до категория, както можете да видите на изображението по -долу. За да разбера, съм го запазил малък.
Обратната връзка е в диапазон A2: A9, ключовите думи са в E2: E10 и Категория е във F2: F10.
Общата формула за създаване на категории ще бъде:
{= INDEX (Категория, MATCH (TRUE, ISNUMBER (ТЪРСЕНЕ (думи, изречение))), 0))}
Обърнете внимание на къдравите скоби, това е формула за масив. Трябва да се въведе с помощта на CTRL+SHIFT+ENTER.
Категория: Това е диапазонът, който съдържа списък с категории. Присъдите или отзивите ще бъдат категоризирани по тези стойности. В нашия случай това е F2: F10.
Думи: това е диапазонът, който съдържа списък с ключови думи или фрази. Те ще бъдат търсени в изречения. Ето го E2: E10.
Присъда: изречението ще бъде категоризирано. Това е единична относителна клетка.
Тъй като сега знаем всяка променлива и функция, използвана за категоризация в Excel, нека я приложим в нашия пример.
В клетка В2 напишете тази формула и натиснете CTRL+SHIFT+ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (ТЪРСЕНЕ ($ E $ 2: $ E $ 10, A2)), 0))}
Копирайте формулата, за да видите категорията на всяка обратна връзка.
Трябва да изброим думите и категорията фиксирани, те не трябва да се променят, докато копираме формулата, затова дадох абсолютна препратка към ключови думи и категории. Въпреки че искаме изреченията да се променят, докато копираме формулата, затова използвах относителна препратка като A2. Можете да прочетете разбиране за препратките в excel тук.
Сега можете да подготвите отчет колко потребители харесват LED екран, високоговорител и камера.
Как работи?
Ядрото на формулата е ISNUMBER (ТЪРСЕНЕ($ E $ 2: $ E $ 10, A2)): Обясних го подробно тук. Функцията SEARCH търси всяка стойност на ключовите думи ($ E $ 2: $ E $ 10) в изречение А2. Той връща масив от намерено местоположение на дума или #ЦЕННОСТ (за думата не е намерена). Накрая ще имаме масив от 9 елемента за този пример. {#VALUE!; 5; #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}. След това използваме ISNUMBER функция, за да преобразуваме този масив в полезни данни. Той го преобразува в масив от TRUE и FALSE. {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Сега всичко е просто съвпадение на индекса. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): функцията MATCH търси TRUE, в получения масив и връща индекса на първото намерено TRUE. което е 2 за този случай.
ИНДЕКС ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (ТЪРСЕНЕ ($ E $ 2: $ E $ 10, A2)), 0)): След това функцията INDEX разглежда втората позиция в категорията ($ F $ 2: $ F $ 10) което е LED екран. Накрая тази формула категоризира този текст или обратна връзка като LED екран.
Осигуряване на чувствителност към буквите:
За да направите тази функция чувствителна към регистъра, използвайте функцията FIND вместо функцията SEARCH. Функцията FIND е чувствителна към регистъра по подразбиране.
{= INDEX (Категория, MATCH (TRUE, ISNUMBER (FIND (думи,присъда)),0))}
Слабите точки:
1. Ако две ключови думи се намират в едно и също изречение, изречението ще бъде категоризирано според първата ключова дума в списъка.
Заснемане на текста в друга дума. Да предположим, че търсим LAD в диапазон. Тогава думите, съдържащи LAD, ще бъдат преброени. Например, стълбите ще се броят за LAD, тъй като съдържа LAD в него. Така че бъдете внимателни. Най -добрата практика е да нормализирате данните си, доколкото е възможно.
Така че това беше бърз урок за това как да категоризирате данните в Excel. Опитах се да го обясня възможно най -просто. Моля, уведомете ме, ако имате някакви съмнения относно тази статия или статии, свързани с Excel.
Свали файл:
Категоризирайте низа въз основа на ключови думиКак да проверите дали клетката съдържа специфичен текст в Excel
Как да проверите списък с текстове в низ в Excel
Вземете съвпадението COUNTIFS Two Criteria в Excel
Вземете COUNTIFS с ИЛИ за множество критерии в Excel
Популярни статии:
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.