☰

Excel で部分的な文字列比較を行うにはどうすればよいでしょうか?

tag-icon • tag-icon microsoft-excel
Excel で部分的な文字列比較を行うにはどうすればよいでしょうか?

Excel に 2 つの列があります。両方の列には、ランダムな文字が接尾辞として付けられた文字列のリストがあります。接頭辞の形式は、アンダースコアが追加された 2 つの文字列で、接尾辞にもアンダースコアが追加されます。

つまり

ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a") HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"

列 B のプレフィックスが列 A にあるかどうかを比較するにはどうすればよいですか?

編集: 文字列を 3 つのセクションに分割し、最初の 2 つのセクションを 1 つの列に結合し、VLOOKUP を使用して列が他の列にあるかどうかを確認することで、これを実行できることはわかっています。しかし、私はワンライナー関数を探していました。

答え1

セル A1 と B1 には次の内容が含まれていると仮定します。

ここに画像の説明を入力してください

列 C には、プレフィックスが等しいかどうかを返すTRUE(VERDADEIRO) または(FALSO)があります。(ポルトガル語の画像で申し訳ありません)FALSE

私が考え出した式は次のとおりです。

=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))

説明：

=EXACT(str1,str2)

文字列 str1 と str2 が等しいかどうかを比較します。列 A と B からプレフィックスを抽出する必要があります。そのためには、次を使用します。

=LEFT(text, [num_chars])

[num_chars]文字列から文字を抽出しますtext。抽出する文字数を知るために、2 番目のアンダースコア_をダミー文字に置き換えます\。

=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)

ここでの秘訣はオプションの引数です[instance]。アンダースコア文字の 2 番目の出現を置換するために、これを 2 に設定します。

=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)

この式で、ダミー文字の位置を見つけます\。すべてをまとめると、上記の式が得られます。

式を段階的に分解すると、次のようになります。 ここに画像の説明を入力してください

答え2

あなたの質問は、列 B の各文字列について、そのプレフィックスが列 A のどこかにあるかどうかを知りたいというものであると私は読みました。したがって、この回答はその問題を解決します。

1 行で取得できませんでした (列 A のすべてを参照する必要があるため)。ただし、必要なのは 1 つの追加列だけです。

まず、任意の文字列からプレフィックスを抽出する数式です。

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

  • Delimiter「_」文字です
  • 内部FIND()呼び出しは最初のアンダースコアの位置を見つけます。次に、それをFIND2 番目のアンダースコアの位置への開始点として使用します。
  • このLEFT()関数は、文字列の左から始まる文字数を返します。したがって、12 番目のアンダースコアの位置から減算して、文字列のプレフィックスを取得します。

これを使用して、すべての列 A プレフィックスのリストを取得します。次に、配列数式を使用して、列 B の各プレフィックスが列 A プレフィックスのリストに存在するかどうかを確認します。

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

  • Prefix_A列 A プレフィックスの完全なリストです。
  • これは列Aに適用された接頭辞抽出式と同じです。
  • 関数内のステートメントは値OR()の配列を返すためTRUE、配列数式にする必要があります。FALSE

この質問に答えるために私が作成したスプレッドシートのスクリーンショットを以下に示します。 列Bのプレフィックスが列Aに存在するかどうかを確認します

これらのサンプル数式を生成するために FormulaChop を使用しました (完全な開示: FormulaChop は私が作成しました)。ここ最初の数式の FormulaChop 出力のスクリーンショットです。こここの質問に答えるために私が作成したスプレッドシートへのリンクです。

関連情報