Kategorisieren Sie Text in Google Sheets basierend auf übereinstimmendem Teiltext im Kategorieindex

Nov 25 2020

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

  1. Das Ausgabenblatt enthält die Daten, wie wir sie erhalten, in den Spalten A bis C.
  2. 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),"")
  3. Die Herausforderung besteht darin, dass unsere Ausgaben ähnlicher Art nicht immer dem genauen Text folgen. Zum Beispiel: SEND E-TFR US***ke5und SEND E-TFR US***xja. Daher möchten wir nur den Text abgleichen SEND 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

2 Rubén Nov 25 2020 at 11:31

In diesem Fall VLOOKUPkann 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 PYMTund Bank Visa) und weil die Übereinstimmungen ohne Berücksichtigung der Groß- und Kleinschreibung durchgeführt werden sollten ( BANK MUTUAL FUNDSsollte 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:A3sind die Zellen, die die Schlüssel enthalten, die vollständig übereinstimmen sollen, und Categories!B1:B3sind die entsprechenden Kategorien

  • Categories!A4:A8sind die Zellen, die die Schlüssel enthalten, die teilweise übereinstimmen sollen, und Categories!B4:B8sind die entsprechenden Kategorien

2 PKHunter Nov 26 2020 at 01:35

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!