Kategoryzuj tekst w Arkuszach Google na podstawie pasującego częściowego tekstu w indeksie kategorii
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
- Arkusz wydatków zawiera dane w takiej postaci, w jakiej je otrzymujemy, w kolumnach od A do C.
- 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 PYMT
bę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),"")
- 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***ke5
iSEND E-TFR US***xja
. W związku z tym chcielibyśmy dopasować tylko tekstSEND 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
W tym przypadku VLOOKUP
nie 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 PYMT
i Bank Visa
) oraz ponieważ dopasowania powinny być wykonywane bez rozróżniania wielkości liter ( BANK MUTUAL FUNDS
należ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:A3
to komórki zawierające klucze, które powinny być w pełni dopasowane iCategories!B1:B3
odpowiadające im kategorieCategories!A4:A8
to komórki zawierające klucze, które powinny być częściowe, iCategories!B4:B8
odpowiadające im kategorie
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!