エクセル：数字の部分一致と完全一致

質問

B1のセルにＡ1のセルの文字が含まれるか判定をするため、以下の関数を使用しました。

=IF(COUNTIF(Ｂ1,"*"&A1&"*"),0,1)

例えば、Ａ1＝400のとき、
Ｂ1=q400
Ｂ1=400 1
などの場合は、０（＝含まれる）という判定がでるのですが
Ｂ1=400
Ｂ1=14000
などの時は、1（=含まれない）となります。
完全一致である400、および14000も「400」という文字列が含まれるので同じ結果を返したいのですができません。
原因はＡ１が文字ではなく数字だからということはわかるのですが。
ちなみにＡ１には文字も入力される場合があるので、どの場合でも含むかの判定を出したいです。
アドバイスお願いします。

回答No.1

ushi2015 · ベストアンサー

こんにちは

=IF(ISERROR(FIND(A1,B1,1)),1,0)

で、どうでしょうか？

お礼

ありがとうございます。
うまくいきました。助かりました。

ISERRORという関数は初めて使います。
説明を読むと・・・、なんか難しいですね。
「#N/A 以外のエラーが・・・・」を判定するのですか。
A1がB1にあればFIND(A1,B1,1)の結果がエラーにならないので、ISERROR(FIND(A1,B1,1))の判定がFALSEになって、IFが0を返すという理屈でしょうか。

回答No.8

kagakusuki

>判定の結果として「TRUE」か「FALSE」で表記される関数に、「+0」を加えることで、結果が数字で表記されるという理解でよろしいでしょうか。

　はい、その通りです。

回答No.7

kagakusuki

回答No.5です。
や>末尾の+0がいらないんじゃないかと思って省いたら成立しないのですね。
>+0はどういう意味でしょうか。

　FIND関数は、

FIND(検索文字列, 対象)

という形式で記述される関数で、「対象」の所に入力されている文字列の中に、「検索文字列」が含まれている場合には、「対象」の先頭の文字から数えて何文字目の位置に「検索文字列」があるのかを返す関数ですが、「対象」の所に入力されている文字列の中に、「検索文字列」が含まれていない場合にはエラーとなります。
　一方、ISERR()やISERROR()、ISNUMBER()等のIS関数が返す値は「条件が成り立つ場合」のTRUEか「条件が成り立たない場合」のFALSEの2通りしかありません。
　ISERR()やISERROR()は(　)内の計算がエラーになる場合にTRUE、エラーにならない場合にFALSEを返す関数です。(因みに、ISNUMBER()は(　)内の値が数値の場合にTRUE、数値ではない場合にFALSEを返す関数です)
　ExcelではTRUEを表すのに数値の1を、FALSEを表すのに数値の0を用いていて、表示の上ではTRUEやFALSEと表示しています。
　つまり、TRUEやFALSEも計算の数値として使う事が出来るのです。
　そこでISERR(FIND(A1,B1))に0を足してやりますと、
「A1セルに入力されている文字列」が「B1セルに入力されている文字列」の中に存在する場合は、「FIND(A1,B1)の計算結果」は数値となってエラーとはならないため、ISERR(FIND(A1,B1))の結果はFALSEとなり、そこに0を足すのでセルに表示される値は0に0を足した値である0となり、
「A1セルに入力されている文字列」が「B1セルに入力されている文字列」の中に存在しない場合は、「FIND(A1,B1)の計算結果」はエラーとなるため、ISERR(FIND(A1,B1))の結果はTRUEとなり、そこに0を足すのでセルに表示される値は1に0を足した値である1が表示される訳です。

お礼

判定の結果として「TRUE」か「FALSE」で表記される関数に、「+0」を加えることで、結果が数字で表記されるという理解でよろしいでしょうか。

回答No.6

bunjii

>ちなみにＡ１には文字も入力される場合があるので、どの場合でも含むかの判定を出したいです。
COUNTIF関数は範囲の値を数式内で加工できませんのでFIND関数を使うと良いでしょう。
=IF(FIND(A1,B1&A1)<LEN(B1),1,0)

回答No.5

kagakusuki

B1のセルにＡ1のセルの文字が含まれている場合には0を、含まれていない場合には1を表示させるだけでしたら、

=ISERR(FIND(A1,B1))+0

だけでも十分です。

お礼

ありがとうございます。

そんなにシンプルに成立するのに驚きです。
末尾の+0がいらないんじゃないかと思って省いたら成立しないのですね。
+0はどういう意味でしょうか。

回答No.4

dogs_cats

C1に下記式を入力するとB1を文字列に出来ます。
=B1&""

D1式
=IF(COUNTIF(C1,"*"&A1&"*"),0,1)

検索元データを全て文字に変換する一手間をかけてあげれば良いだけです。
数値にワイルドカードの概念はないでしょう。

お礼

ありがとうございます。

数式で文字列に出来るんですね、知りませんでした。
けっこう使えそうです。

回答No.3

naoto0216

B列の書式を「文字列」としてみて下さい。

お礼

ありがとうございます

文字列にするとちゃんと判定されました。
COUNTIF関数が理解できてませんでしたね。

回答No.2

MackyNo1

COUNTIF関数は通常の関数とは仕様が異なり、第二引数は文字列で指定することになっていますので、数値の4と文字列数字の4は区別しません(どちらでも検索対象になる）。
これに対して、検索文字にワイルドカードを使用した場合は、逆に文字列のみが検索対象となります。
どうしても今の数式のままで表示させたいなら、B列を選択して、データタブの区切り位置で「次へ」「次へ」で「文字列」にチェックを入れ「完了」して一括して文字列数字に変換することになります。

一般に、このようなケースではFIND関数で評価するほうが合理的です。

=IF(ISNUMBER(FIND(A1,B1)),0,1)

お礼

ありがとうございます

回答No.1さんも同じ要領ですが、FINDを使って、判定結果が1でも2でもいくつでも「数値が出る」＝TRUEという判定にして数値を返すという方法論を知りませんでした。
確かに合理的です。
COUNTIF関数の説明、大変勉強になりました。