MS Excel - Zuweisen von „Kategorien“ basierend auf Schlüsselwörtern

Question

Ich habe eine Excel-Datei mit Ausgaben (der ausgegebene Betrag steht in einer Spalte) und in der nächsten Spalte habe ich eine kurze Beschreibung, die meist aus mehreren Wörtern besteht. Ich möchte die Beschreibung „vereinfachen“ und jeder Beschreibung ein oder zwei Wörter zuweisen, die in einer anderen Spalte daneben stehen würden. Das Problem besteht darin, dass die Beschreibung nicht „einheitlich“ ist. Ich kann beispielsweise Zeichenfolgen wie „Geschäftsessen“, „Geschäftsessen im Restaurant XXX“, „Kaffee mit Journalisten“ usw. haben und möchte diesen Beschreibungen die Bezeichnung „Essen“ zuweisen. Es gibt auch verschiedene Kategorien, die einem ähnlichen Muster folgen.

Meine Idee war, eine weitere Tabelle (auf einem anderen Blatt) zu erstellen – in einer Spalte habe ich Schlüsselwörter wie „Kaffee“, „Mittagessen“, „Abendessen“ und in der Spalte daneben habe ich die Bezeichnungen, die ich zuweisen möchte, nämlich „Essen“. Ich habe die Vlookup-Funktion mit ungefährer Übereinstimmung verwendet, aber sie gibt mir falsche Ergebnisse zurück. Aus irgendeinem Grund scheint die Reihenfolge der Wörter in der Liste die Ergebnisse zu beeinflussen, und selbst wenn eine teilweise Übereinstimmung vorliegt (genau in einem Wort der Zeichenfolge), ignoriert die Vlookup-Funktion diese und gibt etwas anderes zurück. Beispiel: Ich habe „Parken am Hotel xxx“ und in der Tabelle habe ich das Paar „Parken“ – „Reisekosten“, die Vlookup-Funktion gibt die Bezeichnung „Essen“ zurück.

Können Sie mir bei der Lösung dieses Problems helfen? (Gibt es einen anderen Ansatz, den Sie vorschlagen würden?)

Answer 1

Sie möchten die FIND()und/oder- SEARCH()Funktion. Verwendung:

FIND(find_text, within_text)
                                                                gibt die Startposition der ersten Textzeichenfolge
                                                                innerhalb der zweiten Textzeichenfolge zurück (beginnend bei Position 1)

Gibt also FIND("lunch", "lunch with customer")1 zurück und FIND("lunch", "business lunch")gibt 10 zurück. Wenn die erste Zeichenfolge nicht in der zweiten gefunden wird, gibt dies einen #VALUE!Fehlerwert zurück.  SEARCH()ist wie, FIND()außer dass FIND()zwischen Groß- und Kleinschreibung unterschieden wird und SEARCH()nicht. Also

FIND("lunch", "Lunch with customer")gibt zurück , gibt #VALUE!
aber
SEARCH("lunch", "Lunch with customer")1 zurück

Ich gehe davon aus, dass Sie die Version verwenden möchten SEARCH(), bei der die Groß-/Kleinschreibung nicht beachtet wird.

Sie möchten ein Array wie das folgende einrichten:

                                                       

Es ist wahrscheinlich besser, dies in einem separaten Blatt zu tun; nennen wir es Key-Sheet. Dann auf Ihrem Datenblatt: Wenn Ihre Freiformbeschreibung in der Spalte steht A (beginnend in Zelle A1), geben Sie Folgendes in Zelle ein B1:

=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))

und drücken Sie Ctrl+ Shift+ Enter, um daraus eine „Array-Formel“ zu machen. (Sie wird in der Formelleiste in Klammern angezeigt.) Erklärung:

• SEARCH('Key-Sheet'!$A$1:$A$7,$A1)– für jedes Schlüsselwort aus der Spalte Ades Schlüsselblatts („Kaffee“, „Mittagessen“, „Abendessen“ usw.) suchen Sie danach in der Beschreibung in der aktuellen Zeile, Spalte A, des Datenblatts (z. B. „Geschäftsessen“). Dadurch wird einAnordnungenthält { #VALUE!; 10; #VALUE!; … } (sieben Elemente (in diesem Beispiel), eines pro Schlüsselwort; das zweite zeigt das Ergebnis für „Mittagessen“, das in liegt 'Key-Sheet'!A2).
• IFERROR(…,LEN($A1)+1)– Ersetzen Sie #VALUE!Werte durch 15, was, da LEN("business lunch")+1, unmöglich ein gültiger Rückgabewert von sein kann SEARCH()(und tatsächlich höher ist als jeder mögliche gültige Rückgabewert von SEARCH()), aber eine gültige Zahl ist. Unser Array ist jetzt also { 15; 10; 15; … }.
• MIN(…)– Extrahieren Sie den Minimalwert aus dem Array: in diesem Beispiel 10. Im Allgemeinen ist dies die (erste) erfolgreiche Rückgabe von SEARCH().
• =MATCH(…, …)– Beachten Sie, dass der zweite Parameter MATCH()derselbe ist wie der erste Punkt oben. Wir suchen also 10im Array { #VALUE!; 10; #VALUE!; … }. Dies gibt die Position von zurück 10, die 2 ist, was der Tatsache entspricht, dass A1auf dem Datenblatt („Business Lunch“) „Lunch“ steht, das sich in der 2. Zeile des Key-Sheets befindet.

Um die Ausgabenkategorie zu erhalten, müssen Sie lediglich die Spalte Bdes Key-Sheets indizieren. Setzen Sie die Zelle C1auf =OFFSET('Key-Sheet'!$B$1,B1-1,0). (Dies muss keine Array-Formel sein.)

                               

Beachten Sie (wie oben angedeutet), dass, wenn eine Ausgabenbeschreibung mehrere Schlüsselwörter enthält, nur das erste gefunden wird.

Wenn Sie sich nicht mit dem Zwischenwert beschäftigen möchten, können Sie einfach berechnen

=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)

Dastutmuss eine Array-Formel sein.

---

PS: Die Funktionen FIND()und SEARCH()haben ein optionales drittes Argument:

SEARCH(find_text, within_text, [start_num])

Also

SEARCH("cigar", "Sometimes a cigar is just a cigar.")gibt 13 zurück , gibt
aber
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)29 zurück

Ich sehe keinen Grund, warum Sie es verwenden sollten.

Answer 2

Wie Tyson sagte, ist die Übereinstimmung „nahe/ungefähr“ nicht für Wörter gedacht. Um die Hilfedatei zu zitieren:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Das bedeutet, wenn Sie in „1,2,5,8,12“ nach dem Wert „7“ suchen, wäre der zurückgegebene Wert „5“. Dies ist der Wert, der 7 am nächsten kommt und nicht größer als 7 ist.

Ohne umfangreiche Programmierung und Auswertung einzelner Wörter sowie Grammatikanalyse gibt es keine einfache Möglichkeit, das gewünschte Ergebnis zu erzielen.

Sie sollten sich angewöhnen, bei der ursprünglichen Dateneingabe eine Art „Kategoriencode“ einzugeben und dann eine Memo-Spalte für „zusätzliche Einzelheiten“ zu verwenden, z. B. „01 – Essen und Trinken“, „Habe den Chef zum Geburtstag zum Essen eingeladen“.

Wenn Sie bereits über große Datenmengen verfügen, bei denen dies möglicherweise schwierig ist, können Sie mit ein paar Tricks die Dinge beschleunigen (obwohl Sie dennoch viele Dinge manuell sortieren müssen).

Fügen Sie zunächst eine Spalte hinzu, die die Beschreibung auf das Wort „Park“ prüft und 0 zurückgibt, wenn es nicht gefunden wurde, und 1, wenn es gefunden wurde, etwa „=If(Search(„Park“, A1)>1,1,0)“ (und kopieren Sie die Formel dann automatisch in alle Zeilen Ihrer Daten). Anschließend können Sie die gesamte Tabelle nach dieser Spalte sortieren, sodass Ihre Daten in zwei Gruppen aufgeteilt werden: Beschreibungen mit „Park“ und solche ohne. Fügen Sie eine weitere Spalte hinzu, beispielsweise für Beschreibungen mit „Essen“. Anschließend können Sie zwischen „Essen“ und „Park“ (mithilfe beider Spalten) in vier Gruppen sortieren: Beschreibungen ohne beide Wörter, solche mit „Essen“, solche mit „Park“ und solche mit beiden.

Durch wiederholtes Ausführen dieser Methode können Sie schnell Gruppen aussortieren, die eindeutig der einen oder anderen Kategorie zuzuordnen sind, diese mit einem Kategoriecode kennzeichnen und sie von da an bei weiteren Wortsuchvorgängen ignorieren, bis alles kategorisiert ist.