カテゴリインデックスの一致する部分的なテキストに基づいて、Googleスプレッドシートのテキストを分類します

Nov 25 2020

メインシートの列の単語の一部をVLOOKUPで、部分的な単語が別のシートに簡単に保存されるキーワード/カテゴリのインデックスと一致させたいと考えています。

ワークシートの例を次に示します。 https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

経費シートには、受け取ったデータがA列からC列にあります。
列Dでは、これらの経費にカテゴリを自動割り当てします。たとえば、列Bの正確なテキストを使用してこれを行うとBANK VISA PREAUTH PYMT、簡単です。列DのVLOOKUP数式は、[カテゴリ]タブからこの正確な参照を取得することで機能します（セルA1は正確にこのように表現されています）。この式は機能します：=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
課題は、同様のタイプの経費が常に正確なテキストに従うとは限らないことです。例：SEND E-TFR US***ke5およびSEND E-TFR US***xja。そのため、テキストだけを一致させたいと思いますSEND E-TFR。

これは、VLOOKUPが[カテゴリ]タブの最初の列のパーシャルと一致する必要があることを意味します。これを実現するために、VLOOKUP式をどのように調整しますか？

PS。私の質問はこれと非常に似ていますが、部分文字列を一致させたいという重要な違いがあります。
Googleスプレッドシートでセルを分類する

編集：答えた。

回答

2 Rubén Nov 25 2020 at 11:31

この場合VLOOKUP、一致するデータ内に一部のカテゴリkyesが含まれているため使用できませんが、一部のカテゴリがあるため、質問の本文に含まれている説明よりも大文字と小文字が複雑です。それら（つまりBANK VISA PREAUTH PYMTとBank Visa）の間の部分的な一致、および一致は大文字と小文字を区別せずに実行する必要があるため（BANK MUTUAL FUNDSとして分類する必要がありますMutual Funds）

これが提案された式です

=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 カテゴリキーと比較する必要がある値を持つセルです。

注：この範囲には空白のセルがないことを前提としています。
Categories!A1:A3完全一致を行う必要があるキーを含むセルでCategories!B1:B3あり、対応するカテゴリです
Categories!A4:A8部分一致を行う必要があるキーを含むセルでCategories!B4:B8あり、対応するカテゴリです

2 PKHunter Nov 26 2020 at 01:35

答えてくれた@Rubenに感謝します。最終的には、より単純なアプローチを取ることにしました。代わりに、VLOOKUPで検索文字列の正規表現を確認し、[カテゴリ]タブのキーインデックスにそれらの部分文字列を含めます。

したがって、経費のセルB1にこのテキストがある場合：

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

完全一致で問題が発生していたため、文字列を2番目のスペースの前にある部分文字列に切り捨てるだけです。

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

これだけを与えるには：

SEND E-TFR

そして、更新されたVLOOKUP関数：

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

次に、カテゴリキーインデックスは、ルックアップ用に切り捨てられたテキストを参照します。

したがって、これは適切なカテゴリに一致し、データを入力します。

これが機能することを確認するためのテストファイルは次のとおりです。 https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing