Die Kategorisierung von Strings anhand einiger Wörter war eine meiner grundlegenden Aufgaben in der Datenanalyse. Wenn Sie beispielsweise in einer Umfrage Personen fragen, was ihnen an einem bestimmten Smartphone gefällt, werden die gleichen Antworten aus verschiedenen Wörtern bestehen. Für Kamera können sie Wörter wie Fotos, Videos, Selfies usw. verwenden. Sie alle bedeuten Kamera. Daher ist es sehr wichtig, Sätze vorher zu kategorisieren, um aussagekräftige Informationen zu erhalten.
In diesem Artikel erfahren Sie, wie Sie in Excel mithilfe von Schlüsselwörtern kategorisieren.
Nehmen wir das Beispiel einer Umfrage, über die wir gesprochen haben.
Beispiel: Kategorisieren von Daten aus einer Umfrage in Excel
Also haben wir eine Umfrage zu unserem neuen Smartphone xyz gemacht. Wir haben unsere Kunden gefragt, was ihnen an xyz phone gefällt und ihre Antwort in Excel festgehalten. Jetzt müssen wir wissen, wem unser LED-Bildschirm, Lautsprecher und Kamera gefallen hat.
Dafür haben wir eine Liste von Schlüsselwörtern vorbereitet, die sich auf eine Kategorie beziehen können, wie Sie in der folgenden Abbildung sehen können. Zum Verständnis habe ich es klein gehalten.
Die Rückmeldungen sind im Bereich A2:A9, Schlüsselwörter sind in E2:E10 und Kategorie ist in F2:F10.
Die allgemeine Formel zum Erstellen von Kategorien lautet:
{=INDEX(Kategorie,MATCH(TRUE,ISNUMBER(SUCHE(Wörter,Satz)),0))}
Beachten Sie die geschweiften Klammern, es handelt sich um eine Matrixformel. Muss mit STRG+UMSCHALT+EINGABE eingegeben werden.
Kategorie: Es ist der Bereich, der eine Liste von Kategorien enthält. Sätze oder Rückmeldungen werden nach diesen Werten kategorisiert. In unserem Fall ist es F2:F10.
Wörter: es ist der Bereich, der eine Liste von Schlüsselwörtern oder Phrasen enthält. Diese werden im gesucht Sätze. Hier ist es E2:E10.
Satz: es ist der Satz, der kategorisiert wird. Es ist eine einzelne relative Zelle.
Da wir jetzt jede Variable und Funktion kennen, die zur Kategorisierung in Excel verwendet wird, implementieren wir sie in unserem Beispiel.
Schreiben Sie in Zelle B2 diese Formel und drücken Sie STRG+UMSCHALT+EINGABETASTE.
{=INDEX($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0))}
Kopieren Sie die Formel, um die Kategorie jedes Feedbacks anzuzeigen.
Wir müssen eine Liste von Wörtern und Kategorien festlegen, sie sollten sich nicht ändern, wenn wir die Formel abschreiben, daher habe ich den absoluten Bezug zu Schlüsselwörtern und Kategorien angegeben. Obwohl wir möchten, dass sich die Sätze beim Kopieren der Formel ändern, habe ich den relativen Bezug von A2 verwendet. Lesen Sie hier, wie Sie in Excel referenzieren.
Jetzt können Sie einen Bericht erstellen, wie viele Benutzer LED-Bildschirm, Lautsprecher und Kamera mögen.
Wie es funktioniert?
Der Kern der Formel ist ISNUMBER(SEARCH($E$2:$E$10,A2)): Ich habe es hier ausführlich erklärt. Die SEARCH-Funktion durchsucht jeden Wert von Schlüsselwörtern ($E$2:$E$10) im Satz von A2. Es gibt ein Array der gefundenen Position des Wortes or . zurück #WERT (für das Wort nicht gefunden). Schließlich haben wir für dieses Beispiel ein Array von 9 Elementen. {#WERT!;5;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!}. Als nächstes verwenden wir die ISNUMBER-Funktion, um dieses Array in nützliche Daten umzuwandeln. Es wandelt es in ein Array von TRUE und FALSE um. {FALSCH;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}.
Jetzt ist alles ein einfacher Index-Match. MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0): Die MATCH-Funktion sucht im resultierenden Array nach TRUE und gibt den Index des zuerst gefundenen TRUE zurück. was in diesem Fall 2 ist.
INDEX($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0)): Als nächstes betrachtet die INDEX-Funktion die 2. Position in der Kategorie ($F$2:$F$10) das ist LED-Bildschirm. Schließlich kategorisiert diese Formel diesen Text oder Feedback als LED-Bildschirm.
Groß-/Kleinschreibung beachten:
Um diese Funktion zwischen Groß- und Kleinschreibung zu unterscheiden, verwenden Sie die Funktion FIND anstelle der Funktion SEARCH. Die FIND-Funktion unterscheidet standardmäßig zwischen Groß- und Kleinschreibung.
{=INDEX(Kategorie,VERGLEICH(WAHR,ISNUMMER(FINDEN(Wörter,Satz)),0))}
Die Schwachstellen:
1.Wenn zwei Schlüsselwörter im selben Satz gefunden werden, wird der Satz nach dem ersten Schlüsselwort in der Liste kategorisiert.
Erfassen des Textes in einem anderen Wort. Angenommen, wir suchen nach LAD in einem Bereich. Dann werden Wörter, die LAD enthalten, gezählt. Zum Beispiel werden Leitern für KOP gezählt, da sie KOP enthalten. Seien Sie also vorsichtig. Best Practice ist es, Ihre Daten so weit wie möglich zu normalisieren.
Dies war also ein kurzes Tutorial zum Kategorisieren von Daten in Excel. Ich habe versucht, es so einfach wie möglich zu erklären. Bitte lassen Sie es mich wissen, wenn Sie Zweifel an diesem Artikel oder anderen Artikeln zu Excel haben.
Download-Datei:
String basierend auf Schlüsselwörtern kategorisierenSo überprüfen Sie, ob die Zelle in Excel bestimmten Text enthält
So überprüfen Sie eine Liste von Texten in String in Excel
Holen Sie sich die COUNTIFS Zwei-Kriterien-Übereinstimmung in Excel
Holen Sie sich die ZÄHLENWENN mit ODER für mehrere Kriterien in Excel
Populäre Artikel :
50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Wie benutzt manDie SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.