Het categoriseren van strings op basis van enkele woorden was een van mijn basistaken bij data-analyse. Als u bijvoorbeeld in een enquête mensen vraagt wat ze leuk vinden aan een bepaalde smartphone, zullen dezelfde antwoorden verschillende woorden bevatten. Voor camera kunnen ze woorden gebruiken als foto's, video's, selfies enz. Ze impliceren allemaal camera. Het is dus erg belangrijk om zinnen eerder te categoriseren om zinvolle informatie te krijgen.
In dit artikel zullen we leren hoe u in Excel kunt categoriseren met behulp van trefwoorden.
Laten we het voorbeeld nemen van een enquête waar we het over hadden.
Voorbeeld: categoriseer gegevens die zijn verzameld uit een enquête in Excel
Daarom hebben we een enquête gehouden over onze nieuwe smartphone xyz. We hebben onze klanten gevraagd wat ze leuk vinden aan xyz phone en hebben hun antwoord vastgelegd in Excel. Nu moeten we weten wie ons LED-scherm, luidspreker en camera leuk vond.
Hiervoor hebben we een lijst met trefwoorden opgesteld die naar een categorie kunnen verwijzen, zoals u in onderstaande afbeelding kunt zien. Om het te begrijpen heb ik het klein gehouden.
De terugkoppelingen bevinden zich in het bereik A2:A9, trefwoorden zijn in E2:E10 en categorie is in F2:F10.
De generieke formule om categorieën te maken is:
{=INDEX(Categorie,MATCH(TRUE,ISNUMBER(SEARCH(woorden,zin)),0))}
Let op de accolades, het is een matrixformule. Moet worden ingevoerd met CTRL+SHIFT+ENTER.
Categorie: Het is het bereik dat een lijst met categorieën bevat. Zinnen of feedback worden gecategoriseerd op basis van deze waarden. In ons geval is dat F2:F10.
Woorden: het is het bereik dat een lijst met trefwoorden of woordgroepen bevat. Deze worden doorzocht in de zinnen. Hier is het E2:E10.
Zin: het is de zin die zal worden gecategoriseerd. Het is een enkele relatieve cel.
Omdat we nu elke variabele en functie kennen die wordt gebruikt voor categorisatie in Excel, laten we deze in ons voorbeeld implementeren.
Schrijf deze formule in cel B2 en druk op CTRL+SHIFT+ENTER.
{=INDEX($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0))}
Kopieer de formule naar beneden om de categorie van elke feedback te zien.
We moeten een lijst met woorden en een vaste categorie maken, ze zouden niet moeten veranderen als we de formule kopiëren, daarom heb ik een absolute verwijzing naar trefwoorden en categorieën gegeven. Hoewel we willen dat zinnen veranderen als we de formule kopiëren, daarom heb ik relatieve verwijzing van als A2 gebruikt. U kunt hier meer lezen over verwijzen in Excel.
Nu kunt u een rapport opstellen hoeveel gebruikers het LED-scherm, de luidspreker en de camera leuk vinden.
Hoe het werkt?
De kern van de formule is ISNUMBER(SEARCH($E$2:$E$10,A2)): Ik heb het hier uitgebreid uitgelegd. De SEARCH-functie doorzoekt elke waarde van trefwoorden ($E$2:$E$10) in zin van A2. Het retourneert een array van gevonden locaties van woord of #WAARDE (voor het woord niet gevonden). Ten slotte hebben we een array van 9 elementen voor dit voorbeeld. {#WAARDE!;5;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!}. Vervolgens gebruiken we de ISNUMBER-functie om deze array om te zetten in bruikbare gegevens. Het zet het om in een array van TRUE en FALSE. {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
Nu, alles is een eenvoudige indexmatch. MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0): de MATCH-functie zoekt naar TRUE, in de resulterende array en retourneert de index van de eerst gevonden TRUE. dat is 2 voor dit geval.
INHOUDSOPGAVE($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0)): Vervolgens kijkt de INDEX-functie naar de 2e positie in de categorie ($F$2:$F$10) dat is een LED-scherm. Ten slotte categoriseert deze formule deze tekst of feedback als LED-scherm.
Het hoofdlettergevoelig maken:
Om deze functie hoofdlettergevoelig te maken, gebruikt u de FIND-functie in plaats van de SEARCH-functie. De FIND-functie is standaard hoofdlettergevoelig.
{=INDEX(Categorie,OVEREENKOMST(WAAR,ISGETAL(VINDEN(woorden,zin)),0))}
De zwakke punten:
1.Als twee trefwoorden in dezelfde zin worden gevonden, wordt de zin gecategoriseerd op basis van het eerste trefwoord in de lijst.
De tekst vastleggen in een ander woord. Stel dat we zoeken naar LAD in een bereik. Dan worden woorden met LAD geteld. Ladders worden bijvoorbeeld geteld voor LAD omdat er LAD in zit. Wees er dus voorzichtig mee. Best practice is om uw gegevens zoveel mogelijk te normaliseren.
Dit was dus een korte tutorial over het categoriseren van gegevens in Excel. Ik heb geprobeerd het zo simpel mogelijk uit te leggen. Laat het me weten als je twijfels hebt over dit artikel of Excel-gerelateerde artikelen.
Download bestand:
Tekenreeks categoriseren op basis van trefwoordenHoe te controleren of cel specifieke tekst bevat in Excel
Hoe een lijst met teksten in string in Excel te controleren?
Haal de COUNTIFS Two Criteria Match in Excel
Verkrijg de AANTAL.ALS met OF voor meerdere criteria in Excel
Populaire artikels :
50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.