【googlesheets】A列とB列で部分一致しているものの抽出または判別をしたいです。

Question

#達成したいこと
A列とB列で部分一致しているデータを抽出または判別したい。

#試したこと
=ARRAYFOMULA(IF(COUNTIF(A:A,B:B)>=1,"O","X"))

完全一致の場合はこの式で判別ができたのですが、
列によってスペースが入ったり、表記が若干違う場合、
どのようにしたら判別できるでしょうか。
※例）A列:NightVision　B列:NightVision EA 6.3 MT4

ここでは文字数を指定して3文字以上の文字列が一致しているとき、「一致している」としたいです。
例えば、NightVisionの場合、「Nig」も「ght」も〇。

写真のように一覧で表示できるようにしたいです。

Comments

[sawa]

"Google スプレッドシート" のタグが適しているかと思います。

あと部分一致というアバウトな表現では無理です。
NightVision　と NightEye もNigthで部分一致ですし、
NightVision　と Negotiation だって 冒頭のNとか最後の ionが部分一致ですから。

[Por]

アドバイスありがとうございます。
Google スプレッドシートのタグを追加しました。

部分一致については3文字以上が同じ場合、一致しているとして判別出来るようにしたいと思っています。
もしよろしければ、ご教示いただきたく思います。

Answer 1

結局シート関数で出来ることは総当たり処理になってくるんで、式つくれても動きが重くて行数や文字数によっては使い物にならなかったりします。あと3文字の一致はたぶん誤検知も多いと思いますよ。

それを踏まえた上で3文字部分一致の考え方と式ですが

=ARRAYFOMULA(IF(COUNTIF(A:A,B:B)>=1,"O","X"))

↑ 完全一致の式がA列を範囲、B列を条件としている = A列に重複する物がある B列の横に 〇 なので、
これに倣って考えると、

B列内のセルの文字列の連続する3文字を先頭から順番にチェックしていき、
そのどれかがA列のいずれかのセルの文字列に含まれる場合 B列の横に〇をつける

という結果を返す式を作ることになります。

登場する関数が多いので、ここからの細かい解説を飛ばしますが、
最終的に以下で出来ると思います。

=ARRAYFORMULA(IF(B:B="",,IF(IF(LEN(B:B)<3,COUNTIF(A:A,"*"&B:B&"*"),MMULT(IF(LEN(MID(B:B,SEQUENCE(1,MAX(LEN(B:B))-2),3))<3,0,COUNTIF(A:A,"*"&MID(B:B,SEQUENCE(1,MAX(LEN(B:B))-2),3)&"*")),SEQUENCE(MAX(LEN(B:B))-2,1,1,0)))>0,"O","X")))

Comments

[Por]

教えていただきありがとうございました。
読み解くのにはもう少し時間がかかりそうですが、一旦やりたかったことは達成しました。
初めて使う関数も登場して大変勉強になっています。

また、下記のように3のところをC1で代用し、C1に数字を入れることで一致する文字数を変更しながら使おうと思ったのですが、使い方として合っていますでしょうか。

‘‘‘=ARRAYFORMULA(IF(B:B="",,IF(IF(LEN(B:B)<C1,COUNTIF(A:A,"*"&B:B&"*"),MMULT(IF(LEN(MID(B:B,SEQUENCE(1,MAX(LEN(B:B))-(C1-1)),C1))<C1,0,COUNTIF(A:A,"*"&MID(B:B,SEQUENCE(1,MAX(LEN(B:B))-(C1-1)),C1)&"*")),SEQUENCE(MAX(LEN(B:B))-(C1-1),1,1,0)))>0,"O","X")))‘‘‘

[sawa]

その理解でOKです。3文字一致を調整するなら、セル参照にすると良さそうですね。

[Por]

ありがとうございます．いつもご回答いただきありがとうございます．
引き続きよろしくお願いいたします．