Att kategorisera sträng baserat på några ord var en av mina grunduppgifter inom dataanalys. Till exempel, i en undersökning om du frågar folk vad de tycker om en viss smart telefon, kommer samma svar att ha olika ord. För kameran kan de använda orden som foton, videor, selfies etc. De innebär alla kamera. Så det är mycket viktigt att kategorisera meningar innan, för att få lite meningsfull information.
I den här artikeln lär vi oss hur man kategoriserar i excel med hjälp av sökord.
Låt oss ta exemplet på undersökningen vi pratade om.
Exempel: Kategorisera data som samlats in från en undersökning i Excel
Så, vi har gjort en undersökning om vår nya smartphone xyz. Vi har frågat våra kunder vad de gillar med xyz -telefonen och fångat deras svar i excel. Nu behöver vi veta vem som gillade vår LED -skärm, högtalare och kamera.
För detta har vi utarbetat en lista med sökord som kan hänvisa till en kategori, som du kan se i bilden nedan. För att förstå har jag hållit det litet.
Återkopplingarna ligger i intervallet A2: A9, sökorden finns i E2: E10 och kategorin är i F2: F10.
Den generiska formeln för att skapa kategorier kommer att vara:
{= INDEX (Kategori, MATCH (SANT, ISNUMBER (SÖK (ord, mening)), 0))}
Notera de lockiga hängslen, det är en matrisformel. Måste anges med CTRL+SKIFT+ENTER.
Kategori: Det är intervallet som innehåller en lista över kategorier. Meningar eller feedback kommer att kategoriseras efter dessa värden. I vårt fall är det F2: F10.
Ord: det är intervallet som innehåller en lista med sökord eller fraser. Dessa kommer att sökas i meningar. Här är det E2: E10.
Mening: det är meningen som kommer att kategoriseras. Det är en enda relativ cell.
Eftersom vi nu känner till varje variabel och funktion som används för kategorisering i excel, låt oss implementera den på vårt exempel.
Skriv denna formel i cell B2 och tryck på CTRL+SKIFT+ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0))}
Kopiera ner formeln för att se kategorin för varje feedback.
Vi måste lista med ord och kategori fast, de ska inte ändras när vi kopierar ner formeln, därför har jag gett absolut referens till sökord och kategorier. Medan vi vill att meningar ska ändras när vi kopierar ner formeln, är det därför jag har använt relativ referens till som A2. Du kan läsa förstå om att referera i excel här.
Nu kan du förbereda en rapport om hur många användare som gillar LED -skärm, högtalare och kamera.
Hur det fungerar?
Kärnan i formeln är ISNUMBER (SÖK($ E $ 2: $ E $ 10, A2)): Jag har förklarat det i detalj här. SEARCH -funktionen söker efter varje värde av sökord ($ E $ 2: $ E $ 10) i meningen A2. Det returnerar en uppsättning av hittad plats för word eller #VÄRDE (för ordet hittades inte). Slutligen kommer vi att ha en uppsättning med 9 element för detta exempel. {#VÄRDE!; 5;#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!;#VÄRDE!}. Därefter använder vi ISNUMBER Function för att konvertera denna array till användbar data. Den omvandlar den till en uppsättning av SANT och FALSKT. {FALSKT; SANT; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT
Nu är allt enkelt indexmatchning. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): MATCH -funktionen söker efter TRUE, i resultatmatris och returnerar indexet för först hittat TRUE. vilket är 2 för detta fall.
INDEX($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Därefter tittar INDEX -funktionen på den andra positionen i kategorin ($ F $ 2: $ F $ 10) som är LED -skärm. Slutligen kategoriserar denna formel denna text eller feedback som LED -skärm.
Gör det skiftlägeskänsligt:
För att göra denna funktion skiftlägeskänslig, använd FIND -funktionen istället för SEARCH -funktionen. Funktionen FIND är skiftlägeskänslig som standard.
{= INDEX (Kategori, MATCH (SANT, ISNUMBER (HITTA (ord,mening)),0))}
De svaga punkterna:
1.Om två av sökorden finns i samma mening kommer kategorin att kategoriseras efter det första sökordet i listan.
Fånga texten inom ett annat ord. Antag att vi söker efter LAD i ett sortiment. Då räknas ord som innehåller LAD. Till exempel kommer stegar att räknas för LAD eftersom den innehåller LAD i den. Så var försiktig med det. Bästa praxis är att normalisera dina data så mycket som möjligt.
Så detta var en snabb handledning om hur man kategoriserar data i excel. Jag försökte förklara det så enkelt jag kan. Meddela mig om du har några tvivel om den här artikeln eller Excel -relaterade artiklar.
Nedladdning fil:
Kategorisera sträng baserat på sökordSå här kontrollerar du om cellen innehåller specifik text i Excel
Så här kontrollerar du en lista med texter i sträng i Excel
Få COUNTIFS Two Criteria Match i Excel
Skaffa RÄCKNINGARNA MED ELLER För flera kriterier i Excel
Populära artiklar:
50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder than VLOOKUP -funktion i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Så här använder du funktionen COUNTIF i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.