Das Kategorisieren von Zeichenfolgen anhand einiger Wörter war eine meiner Hauptaufgaben bei der Datenanalyse. Wenn Sie beispielsweise in einer Umfrage Personen fragen, was sie an einem bestimmten Smartphone mögen, enthalten dieselben Antworten eine Vielzahl von Wörtern. Für die Kamera können sie Wörter wie Fotos, Videos, Selfies usw. verwenden. Sie alle implizieren Kamera. Daher ist es sehr wichtig, Sätze vorher zu kategorisieren, um aussagekräftige Informationen zu erhalten.

0013

In diesem Artikel erfahren Sie, wie Sie mithilfe von Schlüsselwörtern in Excel kategorisieren.

Nehmen wir das Beispiel einer Umfrage, über die wir gesprochen haben.

Beispiel: Kategorisieren von Daten, die aus einer Umfrage in Excel gesammelt wurden

Wir haben also eine Umfrage zu unserem neuen Smartphone xyz durchgeführt. Wir haben unsere Kunden gefragt, was sie an xyz phone mögen, und ihre Antwort in Excel erfasst. Jetzt müssen wir wissen, wem unser LED-Bildschirm, Lautsprecher und unsere Kamera gefallen haben.

0014

Zu diesem Zweck haben wir eine Liste von Schlüsselwörtern erstellt, die sich auf eine Kategorie beziehen können, wie Sie im folgenden Bild sehen können. Um zu verstehen, habe ich es klein gehalten.

0015

Die Rückmeldungen liegen im Bereich A2: A9, die Schlüsselwörter befinden sich in E2: E10 und die Kategorie in F2: F10.

Die generische Formel zum Erstellen von Kategorien lautet:

{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(words,sentence)),0))}

Beachten Sie die geschweiften Klammern, es ist eine Array-Formel. Muss mit STRG + UMSCHALT + EINGABE eingegeben werden.

Kategorie: Dies 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: Dies ist der Bereich, der eine Liste von Schlüsselwörtern oder Phrasen enthält.

Diese werden in den Sätzen gesucht. 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 für die 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.

0016

Wir müssen die Liste der Wörter und Kategorien festlegen, sie sollten sich nicht ändern, wenn wir die Formel abschreiben. Daher habe ich die Schlüsselwörter und Kategorien absolut angegeben. Während wir möchten, dass sich Sätze ändern, wenn wir die Formel abschreiben, habe ich deshalb die relative Referenz als A2 verwendet.

Sie können verstehen über link: / excel-generals-relative-und-absolute-referenz-in-excel [hier in excel referenzieren.]

Jetzt können Sie einen Bericht erstellen, wie viele Benutzer LED-Bildschirm, Lautsprecher und Kamera mögen.

Wie funktioniert es?

Der Kern der Formel ist link: / logische Formeln-die-isnumber-Funktion-in-Excel [ISNUMBER] (link: / text-excel-Suchfunktion [SEARCH] ($ E $ 2: $ E. $ 10, A2)) *:

Ich habe es ausführlich erklärt „link: / count-countifs-with-or-for-multiple -kriterien [hier]“.

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 oder #WERT zurück (für das Wort nicht gefunden). Schließlich haben wir ein Array von 9 Elementen für dieses Beispiel.

\ {# VALUE !; 5; #VALUE !; # VALUE !; # VALUE !; # VALUE !; # VALUE !; # VALUE !; # VALUE!}.

Als nächstes verwenden wir die ISNUMBER-Funktion, um dieses Array in nützliche Daten umzuwandeln. Es konvertiert es in ein Array von TRUE und FALSE.

\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Nun ist alles einfach link: / lookup-formulas-use-index-and-match-to-lookup-value [index match].

link: / lookup-formulas-excel-match-function [MATCH] (TRUE, link: / logische-formeln-die-isnumber-funktion-in-excel [ISNUMBER] (`link: / text-excel- Suchfunktion [SUCHE] `($ 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. Das ist 2 für diesen Fall.

INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SUCHE ($ E $ 2: $ E $ 10, A2)), 0)): Als nächstes betrachtet die INDEX-Funktion die 2. Position in der Kategorie ($ F $ 2: $ F $ 10) ist ein LED-Bildschirm. Schließlich kategorisiert diese Formel diesen Text oder dieses Feedback als LED-Bildschirm.

Groß- und Kleinschreibung beachten:

Verwenden Sie die Funktion FIND anstelle der Funktion SEARCH, um die Groß- und Kleinschreibung dieser Funktion zu berücksichtigen. Die FIND-Funktion unterscheidet standardmäßig zwischen Groß- und Kleinschreibung.

{=INDEX(Category,MATCH(TRUE,ISNUMBER(FIND(words,sentence)),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.

0017

Erfassen des Textes in einem anderen Wort. Angenommen, wir suchen in einem Bereich nach KOP. Dann werden Wörter mit LAD gezählt. Zum Beispiel werden Leitern für KOP gezählt, da sie KOP enthalten. Also sei vorsichtig damit. Es wird empfohlen, 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 mich wissen, wenn Sie Zweifel an diesem Artikel oder an Excel-Artikeln haben.

Datei herunterladen:

: Dies ist eine weitere wichtige Funktion des Dashboards. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.