Kategorisering af streng baseret på nogle ord var en af mine grundlæggende opgaver inden for dataanalyse. For eksempel i en undersøgelse, hvis du spørger folk, hvad de kan lide ved en bestemt smartphone, vil de samme svar have forskellige ord. Til kamera kan de bruge ordene som fotos, videoer, selfies osv. De indebærer alle kameraer. Så det er meget vigtigt at kategorisere sætninger før, for at få nogle meningsfulde oplysninger.
I denne artikel lærer vi, hvordan man kategoriserer i excel ved hjælp af søgeord.
Lad os tage eksemplet på en undersøgelse, vi talte om.
Eksempel: Kategoriser data indsamlet fra en undersøgelse i Excel
Så vi har lavet en undersøgelse om vores nye smartphone xyz. Vi har spurgt vores kunder, hvad de kan lide ved xyz -telefonen og fanget deres svar i excel. Nu skal vi vide, hvem der kunne lide vores LED -skærm, højttaler og kamera.
Til dette har vi udarbejdet en liste over søgeord, der kan referere til en kategori, som du kan se i billedet herunder. For at forstå, har jeg holdt det lille.
Tilbagemeldingerne er i område A2: A9, søgeordene er i E2: E10 og kategorien er i F2: F10.
Den generiske formel til oprettelse af kategorier vil være:
{= INDEX (Kategori, MATCH (SAND, ISNUMBER (SØG (ord, sætning)), 0))}
Bemærk de krøllede seler, det er en matrixformel. Skal indtastes ved hjælp af CTRL+SKIFT+ENTER.
Kategori: Det er området, der indeholder en liste over kategorier. Sætninger eller tilbagemeldinger vil blive kategoriseret efter disse værdier. I vores tilfælde er det F2: F10.
Ord: det er området, der indeholder en liste over søgeord eller sætninger. Disse vil blive søgt i sætninger. Her er det E2: E10.
Dømme: det er sætningen, der vil blive kategoriseret. Det er en enkelt relativ celle.
Da vi nu kender hver variabel og funktion, der bruges til kategorisering i excel, lad os implementere det på vores eksempel.
Skriv denne formel i celle B2, og tryk på CTRL+SKIFT+ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0))}
Kopier formlen ned for at se kategorien for hver feedback.
Vi skal liste med ord og kategori fast, de bør ikke ændre sig, når vi kopierer formlen ned, derfor har jeg givet absolut reference til søgeord og kategorier. Selvom vi vil have, at sætninger ændres, når vi kopierer formlen ned, er det derfor, jeg har brugt relativ reference til som A2. Du kan læse forstå om referencer i excel her.
Nu kan du udarbejde en rapport om, hvor mange brugere der kan lide LED -skærm, højttaler og kamera.
Hvordan det virker?
Kernen i formlen er ISNUMBER (SØG($ E $ 2: $ E $ 10, A2)): Jeg har forklaret det detaljeret her. SEARCH -funktionen søger efter hver værdi af søgeord ($ E $ 2: $ E $ 10) i sætning af A2. Det returnerer en matrix med fundet placering af ord eller #VÆRDI (for ordet findes ikke). Endelig vil vi have en matrix med 9 elementer til dette eksempel. {#VÆRDI!; 5; #VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI!;#VÆRDI! Dernæst bruger vi ISNUMBER Function til at konvertere dette array til nyttige data. Det konverterer det til en række af SAND og FALSK. {FALSK; SAND; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}.
Nu er alt simpelt indeksmatch. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): MATCH -funktionen søger efter TRUE, i resulteret array og returnerer indekset for først fundet TRUE. hvilket er 2 for denne sag.
INDEKS($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Dernæst ser INDEX -funktionen på 2. position i kategorien ($ F $ 2: $ F $ 10) som er LED -skærm. Endelig kategoriserer denne formel denne tekst eller feedback som LED -skærm.
Gør det sagfølsomt:
For at gøre denne funktion store og små bogstaver følsomme, skal du bruge FIND -funktionen i stedet for SEARCH -funktionen. FIND -funktionen er som standard forskellig mellem store og små bogstaver.
{= INDEX (Kategori, MATCH (SAND, ISNUMBER (FIND (ord,dømme)),0))}
De svage punkter:
1.Hvis to af søgeordene findes i samme sætning, vil sætningen blive kategoriseret efter det første søgeord på listen.
Fange teksten inden for et andet ord. Antag, at vi søger efter LAD i et område. Derefter tælles ord, der indeholder LAD. For eksempel tælles stiger for LAD, da den indeholder LAD i den. Så vær forsigtig med det. Bedste praksis er at normalisere dine data så meget som muligt.
Så dette var en hurtig vejledning om, hvordan man kategoriserer data i excel. Jeg prøvede at forklare det så enkelt som jeg kan. Lad mig vide, hvis du er i tvivl om denne artikel eller excel -relaterede artikler.
Download fil:
Klassificer streng baseret på søgeordSådan kontrolleres, om cellen indeholder specifik tekst i Excel
Sådan kontrolleres en liste over tekster i streng i Excel
Få COUNTIFS Two Criteria Match i Excel
Få COUNTIFS med ELLER til flere kriterier i Excel
Populære artikler:
50 Excel -genvej til at øge din produktivitet : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges than VLOOKUP -funktion i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges funktionen COUNTIF i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.