Å kategorisere streng basert på noen ord var en av mine grunnoppgaver i dataanalyse. For eksempel, i en undersøkelse hvis du spør folk hva de liker med en bestemt smarttelefon, vil de samme svarene ha en rekke ord. For kamera kan de bruke ordene som bilder, videoer, selfies etc. De alle innebærer kamera. Så det er veldig viktig å kategorisere setninger før, for å få litt meningsfull informasjon.
I denne artikkelen lærer vi hvordan du kategoriserer i excel ved hjelp av søkeord.
La oss ta eksemplet på undersøkelsen vi snakket om.
Eksempel: Kategoriser data samlet fra en undersøkelse i Excel
Så, vi har gjort en undersøkelse om vår nye smarttelefon xyz. Vi har spurt kundene våre hva de liker med xyz -telefonen og fanget svaret deres i excel. Nå må vi vite hvem som likte LED -skjermen, høyttaleren og kameraet vårt.
For dette har vi utarbeidet en liste over søkeord som kan referere til en kategori, som du kan se i bildet under. For å forstå, har jeg holdt det lite.
Tilbakemeldingene er i område A2: A9, søkeordene er i E2: E10 og kategorien er i F2: F10.
Den generiske formelen for å lage kategorier vil være:
{= INDEX (Kategori, MATCH (TRUE, ISNUMBER (SEARCH (ord, setning)), 0))}
Legg merke til de krøllete selene, det er en matriseformel. Må skrives inn med CTRL+SKIFT+ENTER.
Kategori: Det er området som inneholder liste over kategorier. Setninger eller tilbakemeldinger vil bli kategorisert etter disse verdiene. I vårt tilfelle er det F2: F10.
Ord: det er området som inneholder liste over søkeord eller setninger. Disse vil bli søkt i setninger. Her er det E2: E10.
Setning: det er setningen som skal kategoriseres. Det er en enkelt relativ celle.
Siden vi nå kjenner hver variabel og funksjon som brukes for kategorisering i excel, la oss implementere den på vårt eksempel.
Skriv denne formelen i celle B2, og trykk CTRL+SKIFT+ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0))}
Kopier ned formelen for å se kategorien for hver tilbakemelding.
Vi trenger en liste over ord og kategorier som er faste, de bør ikke endres når vi kopierer ned formelen, derfor har jeg gitt absolutt referanse til søkeord og kategorier. Selv om vi vil at setninger skal endres når vi kopierer ned formelen, er det derfor jeg har brukt relativ referanse til som A2. Du kan lese forstå om referanse i excel her.
Nå kan du utarbeide en rapport om hvor mange brukere som liker LED -skjerm, høyttaler og kamera.
Hvordan det fungerer?
Kjernen i formelen er ISNUMBER (SEARCH($ E $ 2: $ E $ 10, A2)): Jeg har forklart det i detalj her. SEARCH -funksjonen søker etter hver verdi av søkeord ($ E $ 2: $ E $ 10) i setning til A2. Det returnerer en rekke funnet plassering av ord eller #VERDI (for ordet ikke funnet). Til slutt vil vi ha en rekke med 9 elementer for dette eksemplet. {#VERDI!; 5;#VERDI!;#VERDI!;#VERDI!;#VERDI!;#VERDI!;#VERDI! Deretter bruker vi ISNUMBER Function for å konvertere denne matrisen til nyttige data. Den konverterer den til en rekke SANN og FALSK. {FALSK; SANN; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}.
Nå er alt enkelt indeksmatch. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): MATCH -funksjonen ser etter TRUE, i resultatt array og returnerer indeksen for først funnet TRUE. som er 2 for denne saken.
INDEKS ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Deretter ser INDEX -funksjonen på andre posisjon i kategorien ($ F $ 2: $ F $ 10) som er LED -skjerm. Til slutt kategoriserer denne formelen denne teksten eller tilbakemeldingen som LED -skjerm.
Gjør det saksfølsomt:
For å gjøre denne funksjonen mellom store og små bokstaver, bruk FIND -funksjonen i stedet for SEARCH -funksjonen. FIND -funksjonen er som standard skift mellom store og små bokstaver.
{= INDEKS (Kategori, MATCH (TRUE, ISNUMBER (FINN (ord,setning)),0))}
De svake punktene:
1.Hvis to av søkeordene er funnet i samme setning, blir setningen kategorisert i henhold til det første søkeordet på listen.
Fange teksten i et annet ord. Anta at vi søker etter LAD i et område. Da vil ord som inneholder LAD telles. For eksempel vil stiger telles for LAD siden den inneholder LAD i den. Så vær forsiktig med det. Beste praksis er å normalisere dataene dine så mye som mulig.
Så dette var en rask opplæring om hvordan du kategoriserer data i excel. Jeg prøvde å forklare det så enkelt som jeg kan. Vennligst gi meg beskjed hvis du er i tvil om denne artikkelen eller om Excel -relaterte artikler.
Last ned fil:
Kategoriser streng etter søkeordHvordan sjekke om cellen inneholder spesifikk tekst i Excel
Hvordan sjekke en liste over tekster i streng i Excel
Få COUNTIFS Two Criteria Match i Excel
Få TELLINGENE MED ELLER For flere kriterier i Excel
Populære artikler:
50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du tVLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
