Kategoryzuj tekst w Arkuszach Google na podstawie pasującego częściowego tekstu w indeksie kategorii

Nov 25 2020

Chcę dopasować za pomocą funkcji WYSZUKAJ.PIONOWO część słów w kolumnie w arkuszu głównym do indeksu słów kluczowych / kategorii, w których częściowe słowa są przechowywane dla ułatwienia w innym arkuszu.

Oto przykładowy arkusz roboczy: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

  1. Arkusz wydatków zawiera dane w takiej postaci, w jakiej je otrzymujemy, w kolumnach od A do C.
  2. W kolumnie D chcielibyśmy automatycznie przypisać kategorię do tych wydatków. Jeśli zrobimy to na przykład z dokładnym tekstem w kolumnie B, BANK VISA PREAUTH PYMTbędzie to łatwe. Formuła WYSZUKAJ.PIONOWO w kolumnie D działa, uzyskując to dokładne odniesienie z karty „Kategorie” (komórka A1 ma dokładnie takie sformułowanie). Ta formuła działa:=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
  3. Wyzwanie polega na tym, że nasze wydatki tego typu nie zawsze będą zgodne z dokładnym tekstem. Na przykład: SEND E-TFR US***ke5i SEND E-TFR US***xja. W związku z tym chcielibyśmy dopasować tylko tekst SEND E-TFR.

Oznacza to, że WYSZUKAJ.PIONOWO musi pasować do części podrzędnych w pierwszej kolumnie karty „Kategorie”. Jak dostosować formułę WYSZUKAJ.PIONOWO, aby to osiągnąć?

PS. Moje pytanie jest dość podobne do tego z istotnym rozróżnieniem, jakim jest chęć dopasowania podciągów.
Kategoryzuj komórki w Arkuszach kalkulacyjnych Google

EDYCJA: Z odpowiedzią.

Odpowiedzi

2 Rubén Nov 25 2020 at 11:31

W tym przypadku VLOOKUPnie można użyć, ponieważ niektóre kategorie kyes są zawarte w danych do dopasowania, z drugiej strony sprawa jest bardziej złożona niż opis zawarty w treści pytania, ponieważ istnieją pewne kategorie, które są częściowe dopasowanie między nimi (tj. BANK VISA PREAUTH PYMTi Bank Visa) oraz ponieważ dopasowania powinny być wykonywane bez rozróżniania wielkości liter ( BANK MUTUAL FUNDSnależy klasyfikować jako Mutual Funds)

Oto proponowana formuła

=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 to komórki, które mają wartości, które należy porównać z kluczami kategorii.

    UWAGA: Zakłada się, że w tym zakresie nie będzie pustych komórek.

  • Categories!A1:A3to komórki zawierające klucze, które powinny być w pełni dopasowane i Categories!B1:B3odpowiadające im kategorie

  • Categories!A4:A8to komórki zawierające klucze, które powinny być częściowe, i Categories!B4:B8odpowiadające im kategorie

2 PKHunter Nov 26 2020 at 01:35

Dzięki @Ruben za odpowiedź. Ostatecznie zdecydowaliśmy się na prostsze podejście. Zamiast tego wystarczy sprawdzić wyrażenie regularne ciągu wyszukiwania w WYSZUKAJ.PIONOWO i umieścić te podciągi w indeksach kluczy na karcie Kategorie.

Jeśli więc wydatek ma ten tekst w komórce B1:

SEND E-TFR US***ke5
SEND E-TFR US***abc
SEND E-TFR US***xyz
...

Co powodowało problemy z dokładnymi dopasowaniami, po prostu skracaliśmy ciąg do podłańcucha, który występuje przed drugą spacją:

REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")

Aby dać nam tylko to:

SEND E-TFR

I nasza odnowiona funkcja WYSZUKAJ.PIONOWO:

=IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "")

Indeks klucza kategorii odwołuje się następnie do tego obciętego tekstu na potrzeby wyszukiwania:

Który zatem pasuje i zapełnia odpowiednią kategorię:

Plik testowy, aby zobaczyć, jak to działa, to: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

Dzięki!