経費を記載した Excel ファイルがあり (支出額は 1 つの列に記入)、次の列には複数の単語で構成された短い説明があります。説明を「簡略化」し、各説明に 1 つか 2 つの単語を割り当て、その説明を別の列に並べたいと考えています。問題は、説明が「統一」されていないことです。たとえば、「ビジネス ランチ」、「レストラン XXX でのビジネス ディナー」、「ジャーナリストとのコーヒー」などの文字列があり、これらの説明に「食べ物」というラベルを割り当てたいと考えています。同様のパターンに従うさまざまなカテゴリもあります。
私の考えは、別のテーブル (別のシートに) を作成することです。1 つの列には「コーヒー」、「ランチ」、「ディナー」などのキーワードがあり、その隣の列には割り当てたいラベル (「食べ物」) があります。近似一致の vlookup 関数を使用しましたが、間違った結果が返されました。何らかの理由で、リスト内の単語の順序が結果に影響しているようで、部分一致 (文字列の 1 つの単語が完全一致) があっても、vlookup はそれを無視して別のものを返します。たとえば、「ホテル xxx の駐車場」があり、テーブルに「駐車場」と「旅費」のペアがある場合、vlookup は「食べ物」ラベルを返します。
この問題の解決を手伝っていただけますか？（別のアプローチを提案していただけますか？）
答え1
FIND()and/or関数が必要ですSEARCH()。使用法:
FIND(find_text, within_text)
2 番目のテキスト文字列内の最初のテキスト文字列の開始位置を返します
(位置 1 から開始)
したがって、FIND("lunch", "lunch with customer")は1を返し、FIND("lunch", "business lunch")10を返します。最初の文字列が2番目の文字列に見つからない場合、これは#VALUE!エラー値を返します。 は、大文字と小文字が区別される点と、大文字と小文字が区別されない点を除いて、SEARCH()に似ています。したがって、FIND()FIND()SEARCH()
FIND("lunch", "Lunch with customer")戻ります#VALUE!
が
SEARCH("lunch", "Lunch with customer")1を返します
SEARCH()大文字と小文字を区別しない を使用すると仮定します。
次のような配列を設定する必要があります。
これを別のシートで行う方がよいでしょう。これを と呼びますKey-Sheet。次に、データ シートで、自由形式の説明が列 にある場合A
(セル から始まるA1)、セル に次の内容を入力しますB1。
=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))
Ctrl+ Shift+を押してEnter「配列数式」にします。（数式バーに中括弧で囲んで表示されます。）説明：
SEARCH('Key-Sheet'!$A$1:$A$7,$A1)–Aキーシートの列の各キーワード（「コーヒー」、「ランチ」、「ディナー」など）を、Aデータシートの現在の行の列の説明で検索します（例：「ビジネスランチ」）。これにより、配列{#VALUE!;10;#VALUE!; … } を含みます（7 つの要素（この例では）、キーワードごとに 1 つ。2 番目は にある「lunch」の結果を示しています'Key-Sheet'!A2）。IFERROR(…,LEN($A1)+1)–#VALUE!値を に置き換えます15。これは であるため、LEN("business lunch")+1からの有効な戻り値になることはできませんSEARCH()(実際、 からの有効な戻り値よりも大きいSEARCH()) が、 は有効な数値です。したがって、配列は {15;10;15; … } になります。MIN(…)– 配列から最小値を抽出します。この例では、 です10。一般に、これが からの (最初の) 正常な戻り値になりますSEARCH()。=MATCH(…, …)– の 2 番目のパラメータは、上記の最初の箇条書きと同じであることに注意してください。したがって、配列 { ; ; ; … } でMATCH()を検索します。これは の位置 (2) を返します。これは、データ シート (“ビジネス ランチ”) にキー シートの 2 行目にある “ランチ” が含まれているという事実に対応しています。10#VALUE!10#VALUE!10A1
B経費カテゴリを取得するには、キーシートの列にインデックスを付けるだけです。セルC1を に設定します=OFFSET('Key-Sheet'!$B$1,B1-1,0)。(配列数式である必要はありません。)
注意: (上で予告したように) 経費の説明に複数のキーワードが含まれている場合、最初のキーワードのみが検索されます。
中間値を気にしたくない場合は、次のように計算するだけでよい。
=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)
これする配列数式である必要があります。
PSFIND()およびSEARCH()関数にはオプションの 3 番目の引数があります。
SEARCH(find_text, within_text, [start_num])
それで
SEARCH("cigar", "Sometimes a cigar is just a cigar.")13を返す
が
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)29を返す
それを使用する理由が見当たりません。
答え2
Tyson が言ったように、「近い/おおよその」一致は言葉で表現されるものではありません。ヘルプ ファイルを引用すると、次のようになります。
If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
つまり、「1,2,5,8,12」で値「7」を検索すると、返される値は「5」となり、これは 7 より大きくない 7 に最も近い値になります。
ある種の大規模なプログラミングと個々の単語の評価、および文法分析を行わずに、望むことを実現する簡単な方法はありません。
最初にデータを入力するときに、何らかの「カテゴリ コード」を入力するように自分自身を訓練し、その後、「01-食べ物と飲み物」、「誕生日に上司をディナーに連れて行った」などの「追加の詳細」にメモ列を使用することです。
すでに大量のデータがあり、これを実行するのが難しい場合は、いくつかのトリックを実行して処理を高速化できます (ただし、手動で整理する必要はまだたくさんあります)。
まず、説明に「park」という単語が含まれているかどうかをチェックし、見つからない場合は 0、見つかった場合は 1 を返す列を追加します。たとえば、「=If(Search("park",A1)>1,1,0)」のようになります (その後、数式をデータのすべての行に自動的にコピーします)。次に、その列でテーブル全体を並べ替えて、データを 2 つのグループに分割します。「park」が含まれている説明と含まれていない説明です。たとえば、「food」が含まれている説明用に別の列を追加します。次に、「food」と「park」の間で (両方の列を使用して) 4 つのグループに並べ替えることができます。どちらの単語も含まれていない説明、「food」が含まれている説明、「park」が含まれている説明、両方が含まれている説明です。
これを繰り返して行うことで、明らかにいずれかのカテゴリに属するグループをすばやく分類し、カテゴリ コードでマークして、それ以降は、すべてが分類されるまで追加の単語検索を行う際にそれらのグループを無視することができます。