Kategoriziranje niza na temelju nekih riječi bio je jedan od mojih osnovnih zadataka u analizi podataka. Na primjer, u anketi ako pitate ljude što im se sviđa na određenom pametnom telefonu, isti će odgovori imati različite riječi. Za kameru mogu koristiti riječi poput fotografija, video zapisa, selfija itd. Sve podrazumijeva kameru. Stoga je vrlo važno kategorizirati rečenice prije, kako bi se dobile neke smislene informacije.
U ovom ćemo članku naučiti kako kategorizirati u excelu pomoću ključnih riječi.
Uzmimo primjer ankete o kojoj smo govorili.
Primjer: Kategoriziranje podataka prikupljenih iz ankete u Excelu
Dakle, napravili smo anketu o našem novom pametnom telefonu xyz. Pitali smo naše klijente što im se sviđa na xyz telefonu i zabilježili njihov odgovor u excelu. Sada moramo znati kome se svidio naš LED zaslon, zvučnik i kamera.
Za to smo pripremili popis ključnih riječi koje se mogu odnositi na kategoriju, kao što možete vidjeti na donjoj slici. Da razumijem, držao sam ga malim.
Povratne informacije su u rasponu A2: A9, ključne riječi su u E2: E10, a kategorija u F2: F10.
Opća formula za stvaranje kategorija bit će:
{= INDEX (Kategorija, MATCH (TRUE, ISNUMBER (SEARCH (riječi, rečenica)), 0))}
Obratite pažnju na uvijene zagrade, to je formula niza. Potrebno ga je unijeti pomoću CTRL+SHIFT+ENTER.
Kategorija: To je raspon koji sadrži popis kategorija. Rečenice ili povratne informacije bit će kategorizirane prema tim vrijednostima. U našem slučaju to je F2: F10.
Riječi: to je raspon koji sadrži popis ključnih riječi ili izraza. Oni će se pretraživati u rečenice. Ovdje je E2: E10.
Rečenica: rečenica će biti kategorizirana. To je jedna relativna ćelija.
Budući da sada znamo svaku varijablu i funkciju koja se koristi za kategorizaciju u Excelu, implementirajmo je na našem primjeru.
U ćeliju B2 napišite ovu formulu i pritisnite CTRL+SHIFT+ENTER.
{= INDEKS ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (TRAŽI ($ E $ 2: $ E $ 10, A2)), 0))}
Kopirajte formulu da vidite kategoriju svake povratne informacije.
Moramo navesti riječi i kategoriju fiksne, ne smiju se mijenjati dok kopiramo formulu, stoga sam dao apsolutnu referencu ključnih riječi i kategorija. Iako želimo da se rečenice mijenjaju dok prepisujemo formulu, zato sam upotrijebio relativnu referencu kao A2. Ovdje možete pročitati razumijevanje o referenciranju u excelu.
Sada možete pripremiti izvještaj koliko se korisnika svidjelo LED ekranu, zvučniku i kameri.
Kako radi?
Jezgra formule je ISNUMBER (TRAŽI($ E $ 2: $ E $ 10, A2)): Ovdje sam to detaljno objasnio. Funkcija SEARCH pretražuje svaku vrijednost ključnih riječi ($ E $ 2: $ E $ 10) u rečenici A2. Vraća niz pronađenih mjesta riječi ili #VRIJEDNOST (jer riječ nije pronađena). Na kraju ćemo za ovaj primjer imati niz od 9 elemenata. {#VREDNOST!; 5; #VREDNOST!;#VRIJEDNOST!;#VRIJEDNOST!;#VRIJEDNOST!;#VRIJEDNOST!;#VRIJEDNOST!;#VRIJEDNOST!}. Zatim koristimo ISNUMBER funkciju za pretvaranje ovog niza u korisne podatke. Pretvara ga u niz TRUE i FALSE. {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Sada je sve jednostavno podudaranje indeksa. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): MATCH funkcija traži TRUE, u rezultiralom nizu i vraća indeks prvo pronađenog TRUE. što je 2 za ovaj slučaj.
INDEKS($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Zatim funkcija INDEX gleda na 2. mjesto u kategoriji ($ F $ 2: $ F $ 10) koji je LED zaslon. Konačno, ova formula kategorizira ovaj tekst ili povratnu informaciju kao LED zaslon.
Omogućavanje osjetljivosti na velika i mala slova:
Da bi ova funkcija bila osjetljiva na velika i mala slova, koristite funkciju FIND umjesto funkcije SEARCH. Funkcija FIND prema zadanim postavkama razlikuje velika i mala slova.
{= INDEKS (Kategorija, MATCH (TRUE, ISNUMBER (FIND (riječi,rečenica)),0))}
Slabe točke:
1.Ako se dvije ključne riječi nalaze u istoj rečenici, rečenica će biti kategorizirana prema prvoj ključnoj riječi na popisu.
Snimanje teksta unutar druge riječi. Pretpostavimo da tražimo LAD u rasponu. Tada će se brojati riječi koje sadrže LAD. Na primjer, ljestve će se računati za LAD budući da u sebi sadrži LAD. Zato pripazite na to. Najbolja je praksa normalizirati vaše podatke što je više moguće.
Dakle, ovo je bio brzi vodič o tome kako kategorizirati podatke u Excelu. Pokušao sam to objasniti što jednostavnije. Javite mi ako imate bilo kakvih nedoumica u vezi ovog članka ili bilo kojih članaka vezanih uz Excel.
Preuzmi datoteku:
Kategoriziranje niza na temelju ključnih riječiKako provjeriti sadrži li ćelija specifičan tekst u Excelu
Kako provjeriti popis tekstova u nizu u Excelu
Preuzmite podudaranje dva kriterija COUNTIFS u Excelu
Preuzmite BROJKE S ILI Za više kriterija u Excelu
Popularni članci:
50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti tFunkcija VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.