Kategoriziranje niza na podlagi nekaterih besed je bila ena mojih osnovnih nalog pri analizi podatkov. Na primer, v anketi, če vprašate ljudi, kaj jim je všeč pri določenem pametnem telefonu, bodo isti odgovori imeli različne besede. Za kamero lahko uporabijo besede, kot so fotografije, videoposnetki, samoportreti itd. Vse pomenijo kamero. Zato je zelo pomembno, da stavke kategorizirate že prej, da dobite nekaj pomembnih informacij.
V tem članku se bomo naučili, kako kategorizirati v Excelu z uporabo ključnih besed.
Vzemimo primer ankete, o kateri smo govorili.
Primer: Kategoriziranje podatkov, zbranih iz ankete v Excelu
Tako smo naredili raziskavo o našem novem pametnem telefonu xyz. Naše stranke smo vprašali, kaj jim je všeč pri telefonu xyz, in njihov odziv zabeležili v Excelu. Zdaj moramo vedeti, komu so bili všeč naš LED zaslon, zvočnik in kamera.
Za to smo pripravili seznam ključnih besed, ki se lahko nanašajo na kategorijo, kot lahko vidite na spodnji sliki. Da bi razumeli, sem ga ohranil majhnega.
Povratne informacije so v razponu A2: A9, ključne besede so v E2: E10, kategorija pa v F2: F10.
Splošna formula za ustvarjanje kategorij bo:
{= INDEX (Kategorija, MATCH (TRUE, ISNUMBER (ISKANJE (besede, stavek)), 0))}
Upoštevajte kodraste oklepaje, to je matrična formula. Vnesti ga je treba s CTRL+SHIFT+ENTER.
Kategorija: To je obseg, ki vsebuje seznam kategorij. Stavki ali povratne informacije bodo razvrščene po teh vrednostih. V našem primeru je to F2: F10.
Besede: obseg vsebuje seznam ključnih besed ali besednih zvez. Te bodo iskali v stavki. Tukaj je E2: E10.
Stavek: stavek bo kategoriziran. Gre za eno samo relativno celico.
Ker zdaj poznamo vsako spremenljivko in funkcijo, ki se uporablja za kategorizacijo v Excelu, jo izvedimo na našem primeru.
V celico B2 napišite to formulo in pritisnite CTRL+SHIFT+ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (ISKANJE ($ E $ 2: $ E $ 10, A2)), 0))}
Kopirajte formulo, če si želite ogledati kategorijo vsake povratne informacije.
Moramo navesti besede in kategorijo, ki se ne smejo spreminjati, ko kopiramo formulo, zato sem navedel absolutno referenco ključnih besed in kategorij. Čeprav želimo, da se stavki spremenijo, ko kopiramo formulo, sem zato uporabil relativno sklicevanje na A2. Razumevanje sklicevanja v Excelu lahko preberete tukaj.
Zdaj lahko pripravite poročilo, koliko uporabnikom je všeč LED zaslon, zvočnik in kamera.
Kako deluje?
Jedro formule je ISNUMBER (ISKANJE($ E $ 2: $ E $ 10, A2)): Tukaj sem podrobno razložil. Funkcija SEARCH išče po vsaki vrednosti ključnih besed ($ E $ 2: $ E $ 10) v stavku A2. Vrne polje najdene lokacije besede oz #VREDNOST (za besedo ni mogoče najti). Končno bomo za ta primer imeli niz 9 elementov. {#VREDNOST!; 5; #VREDNOST!;#VREDNOST!;#VREDNOST!;#VREDNOST!;#VREDNOST!;#VREDNOST!;#VREDNOST!}. Nato uporabimo funkcijo ISNUMBER za pretvorbo tega niza v uporabne podatke. Pretvori ga v niz TRUE in FALSE. {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Zdaj je vse preprosto ujemanje indeksov. MATCH (TRUE, ISNUMBER (ISKANJE ($ E $ 2: $ E $ 10, A2)), 0): funkcija MATCH išče TRUE, v rezultatski matriki in vrne indeks prve najdene TRUE. kar je 2 v tem primeru.
KAZALO ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (ISKANJE ($ E $ 2: $ E $ 10, A2)), 0)): Nato funkcija INDEX gleda na drugo mesto v kategoriji ($ F $ 2: $ F $ 10) ki je LED zaslon. Končno ta formula kategorizira to besedilo ali povratne informacije kot LED zaslon.
Naj bo občutljivo na velike in male črke:
Če želite, da je ta funkcija občutljiva na velike in male črke, uporabite funkcijo FIND namesto funkcije SEARCH. Funkcija FIND privzeto razlikuje velike in male črke.
{= INDEX (Kategorija, MATCH (TRUE, ISNUMBER (NAJDI (besede,stavek)),0))}
Slabe točke:
1. Če dve ključni besedi najdemo v istem stavku, bo stavek kategoriziran glede na prvo ključno besedo na seznamu.
Zajem besedila v drugo besedo. Predpostavimo, da iščemo LAD v obsegu. Nato se bodo štele besede, ki vsebujejo LAD. Lestve se bodo na primer štele za LAD, saj v njem vsebuje LAD. Zato bodite previdni pri tem. Najboljša praksa je čim bolj normalizirati vaše podatke.
To je bila torej hitra vadnica o tem, kako kategorizirati podatke v Excelu. Poskušal sem ga čim bolj preprosto razložiti. Sporočite mi, če dvomite o tem članku ali člankih, povezanih z Excelom.
Prenesite datoteko:
Kategorizirajte niz na podlagi ključnih besedKako preveriti, ali celica vsebuje določeno besedilo v Excelu
Kako preveriti seznam besedil v nizu v Excelu
Pridobite ujemanje dveh meril COUNTIFS v Excelu
Pridobite štetje z OR za več meril v Excelu
Priljubljeni članki:
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.