Категоризација низа на основу неких речи била је један од мојих основних задатака у анализи података. На пример, у анкети ако питате људе шта им се свиђа на одређеном паметном телефону, исти одговори ће имати различите речи. За камеру могу користити речи попут фотографија, видео записа, селфија итд. Све подразумевају камеру. Зато је веома важно категоризовати реченице пре, да бисте добили неке смислене информације.
У овом чланку ћемо научити како категорисати у екцелу помоћу кључних речи.
Узмимо пример анкете о којој смо говорили.
Пример: Категоризујте податке прикупљене из анкете у Екцелу
Дакле, урадили смо анкету о нашем новом паметном телефону киз. Питали смо наше клијенте шта им се свиђа код киз телефона и забележили њихов одговор у екцелу. Сада морамо знати коме се допао наш ЛЕД екран, звучник и камера.
За ово смо припремили листу кључних речи које се могу односити на категорију, као што можете видети на слици испод. Да разумем, држао сам га малим.
Повратне информације су у опсегу А2: А9, кључне речи су у Е2: Е10, а категорија у Ф2: Ф10.
Општа формула за креирање категорија биће:
{= ИНДЕКС (Категорија, МАТЦХ (ТРУЕ, ИСНУМБЕР (СЕАРЦХ (речи, реченица)), 0))}}
Обратите пажњу на увијене заграде, то је формула низа. Потребно је унети помоћу ЦТРЛ+СХИФТ+ЕНТЕР.
Категорија: То је опсег који садржи листу категорија. Реченице или повратне информације биће категорисане према овим вредностима. У нашем случају то је Ф2: Ф10.
Речи: то је опсег који садржи листу кључних речи или фраза. Они ће се претраживати у реченице. Ево га Е2: Е10.
Реченица: то је реченица која ће бити категорисана. То је једна релативна ћелија.
Пошто сада знамо сваку променљиву и функцију која се користи за категоризацију у Екцелу, применимо је на нашем примеру.
У ћелију Б2 напишите ову формулу и притисните ЦТРЛ+СХИФТ+ЕНТЕР.
{= ИНДЕКС ($ Ф $ 2: $ Ф $ 10, МАТЦХ (ТРУЕ, ИСНУМБЕР (СЕАРЦХ ($ Е $ 2: $ Е $ 10, А2)), 0))}
Препишите формулу да видите категорију сваке повратне информације.
Морамо навести речи и категорију фиксних, не би требало да се мењају док преписујемо формулу, стога сам дао апсолутну референцу кључних речи и категорија. Иако желимо да се реченице мењају док преписујемо формулу, зато сам користио релативну референцу као А2. Овде можете прочитати разумевање о референцирању у екцелу.
Сада можете припремити извештај колико се корисника свиђа ЛЕД екран, звучник и камера.
Како то ради?
Језгро формуле је ИСНУМБЕР (ТРАЖИ($ Е $ 2: $ Е $ 10, А2)): Овде сам то детаљно објаснио. Функција СЕАРЦХ претражује сваку вредност кључних речи ($ Е $ 2: $ Е $ 10) у реченици А2. Враћа низ пронађених локација речи или #ВАЛУЕ (јер реч није пронађена). Коначно ћемо имати низ од 9 елемената за овај пример. {#ВАЛУЕ!; 5; #ВАЛУЕ!;#ВАЛУЕ!;#ВАЛУЕ!;#ВАЛУЕ!;#ВАЛУЕ!;#ВАЛУЕ!;#ВАЛУЕ!}. Затим користимо ИСНУМБЕР функцију за претварање овог низа у корисне податке. Претвара га у низ ТРУЕ и ФАЛСЕ. {ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ}.
Сада је све једноставно подударање индекса. МАТЦХ (ТРУЕ, ИСНУМБЕР (СЕАРЦХ ($ Е $ 2: $ Е $ 10, А2)), 0): МАТЦХ функција тражи ТРУЕ, у резултатском низу и враћа индекс првог пронађеног ТРУЕ. што је 2 за овај случај.
ИНДЕКС ($ Ф $ 2: $ Ф $ 10, МАТЦХ (ТРУЕ, ИСНУМБЕР (СЕАРЦХ ($ Е $ 2: $ Е $ 10, А2)), 0)): Затим, ИНДЕКС функција гледа на 2. место у категорији ($ Ф $ 2: $ Ф $ 10) који је ЛЕД екран. Коначно, ова формула категорише овај текст или повратну информацију као ЛЕД екран.
Омогућавање осетљивости на велика и мала слова:
Да би ова функција била осетљива на велика и мала слова, користите функцију ФИНД уместо функције СЕАРЦХ. Функција ФИНД подразумевано разликује велика и мала слова.
{= ИНДЕКС (Категорија, МАТЦХ (ТРУЕ, ИСНУМБЕР (ФИНД (речи,реченица)),0))}
Слабе тачке:
1.Ако се две кључне речи налазе у истој реченици, реченица ће бити категоризована према првој кључној речи на листи.
Снимање текста унутар друге речи. Претпоставимо да тражимо ЛАД у опсегу. Тада ће се бројати речи које садрже ЛАД. На пример, Лествице ће се рачунати за ЛАД пошто у себи садржи ЛАД. Зато буди пажљив у вези с тим. Најбоља пракса је нормализација ваших података што је више могуће.
Дакле, ово је био брзи водич о томе како категоризирати податке у Екцелу. Покушао сам да то објасним што је једноставније могуће. Молимо вас да ме обавестите ако имате било каквих недоумица у вези овог чланка или било којих чланака везаних за Екцел.
Скини докуменат:
Категоризујте низ на основу кључних речиКако да проверите да ли ћелија садржи одређени текст у Екцелу
Како проверити листу текстова у низу у Екцелу
Набавите подударање два критеријума ЦОУНТИФС у Екцелу
Набавите ЦОУНТИФС са ИЛИ за више критеријума у Екцелу
Популарни чланци:
50 Екцел пречица за повећање ваше продуктивности : Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи тФункција ВЛООКУП у Екцелу : Ово је једна од најчешће кориштених и најпопуларнијих функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција ЦОУНТИФ у програму Екцел : Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел : Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.