Kategorisieren Sie Text in Google Sheets basierend auf übereinstimmendem Teiltext im Kategorieindex
Ich möchte mit VLOOKUP einen Teil der Wörter in einer Spalte im Hauptblatt mit einem Index von Schlüsselwörtern / Kategorien abgleichen, in denen Teilwörter zur Vereinfachung in einem anderen Blatt gespeichert sind.
Hier ist ein Beispielarbeitsblatt: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing
- Das Ausgabenblatt enthält die Daten, wie wir sie erhalten, in den Spalten A bis C.
- In Spalte D möchten wir diesen Ausgaben automatisch eine Kategorie zuweisen. Wenn wir dies zum Beispiel mit genauem Text in Spalte B tun
BANK VISA PREAUTH PYMT
, ist es einfach. Die VLOOKUP-Formel in Spalte D funktioniert, indem diese genaue Referenz von der Registerkarte "Kategorien" abgerufen wird (Zelle A1 ist genau so formuliert). Diese Formel funktioniert:=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
- Die Herausforderung besteht darin, dass unsere Ausgaben ähnlicher Art nicht immer dem genauen Text folgen. Zum Beispiel:
SEND E-TFR US***ke5
undSEND E-TFR US***xja
. Daher möchten wir nur den Text abgleichenSEND E-TFR
.
Dies bedeutet, dass der VLOOKUP mit Partials in der ersten Spalte der Registerkarte "Kategorien" übereinstimmen muss. Wie passt man die VLOOKUP-Formel an, um dies zu erreichen?
PS. Meine Frage ist dieser sehr ähnlich, mit dem entscheidenden Unterschied, Teilzeichenfolgen anpassen zu wollen.
Kategorisieren Sie Zellen in Google Spreadsheets
EDIT: Beantwortet.
Antworten
In diesem Fall VLOOKUP
kann nicht verwendet werden, da einige der Kategorien kyes in den zu vergleichenden Daten enthalten sind. Andererseits ist der Fall komplexer als die Beschreibung im Hauptteil der Frage, da es einige Kategorien gibt eine teilweise Übereinstimmung zwischen ihnen (dh BANK VISA PREAUTH PYMT
und Bank Visa
) und weil die Übereinstimmungen ohne Berücksichtigung der Groß- und Kleinschreibung durchgeführt werden sollten ( BANK MUTUAL FUNDS
sollte als kategorisiert werden Mutual Funds
)
Hier ist die vorgeschlagene Formel
=ArrayFormula(ARRAY_CONSTRAIN(
SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE({
IF(
TRIM(B2:B9)=TRIM(TRANSPOSE(Categories!A1:A3)),Categories!B1:B3&"|",),
TRANSPOSE(IF(IFERROR(SEARCH(TRIM(Categories!A4:A8),TRANSPOSE(TRIM(B2:B9))),),Categories!B4:B8&"|",))
}),,1000000))),"|"),
COUNTA(B2:B9),1))
B2:B9
sind die Zellen mit den Werten, die mit den Kategorietasten verglichen werden sollen.HINWEIS: Es wird davon ausgegangen, dass in diesem Bereich keine leeren Zellen vorhanden sind.
Categories!A1:A3
sind die Zellen, die die Schlüssel enthalten, die vollständig übereinstimmen sollen, undCategories!B1:B3
sind die entsprechenden KategorienCategories!A4:A8
sind die Zellen, die die Schlüssel enthalten, die teilweise übereinstimmen sollen, undCategories!B4:B8
sind die entsprechenden Kategorien
Danke an @Ruben für die Antwort. Schließlich entschieden wir uns für einen einfacheren Ansatz. Nur um stattdessen den regulären Ausdruck der Suchzeichenfolge in VLOOKUP zu überprüfen und diese Teilzeichenfolgen in den Schlüsselindizes auf der Registerkarte Kategorien zu haben.
Wenn also eine Ausgabe diesen Text in der Zelle B1 hat:
SEND E-TFR US***ke5
SEND E-TFR US***abc
SEND E-TFR US***xyz
...
Da dies zu Problemen mit genauen Übereinstimmungen führte, kürzen wir die Zeichenfolge einfach auf den Teilstring, der vor dem zweiten Leerzeichen auftritt:
REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")
Um uns genau das zu geben:
SEND E-TFR
Und unsere erneuerte VLOOKUP-Funktion:
=IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "")
Der Kategorienschlüsselindex bezieht sich dann auf den abgeschnittenen Text für die Suche:

Welches passt also und füllt die richtige Kategorie:

Eine Testdatei, um dies zu sehen, ist: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing
Vielen Dank!