セル範囲内いずれかの数値が他のセル範囲に含まれるかを調べたい

質問

EXCEL2003です。
A1～H1にそれぞれ３桁の数値が入っています。（空欄もあります。）
これら範囲内の数値のいずれか一つでも他のセル範囲（次のシートのA1～A50まで）に
含まれていれば結果をJ1に表示したいのですが、可能でしょうか。

当初はJ1に、if(A1が含まれるか,"あった",if(B1が含まれるか,"あった",if(～と、
やってましたが、関数のネストレベルに引っかかってできませんでした。

MATCH関数だと一つの数値が含まれるかどうかは調べられるようですが、
検索したい数値が複数ある場合はやはりセルを分けて別々に評価するしかないのでしょうか？

回答No.8

mu2011 · ベストアンサー

ＮＯ６です。
=COUNT(A1:H1)を
=SUM(IF(LEN(A1:H1),1))又は=SUMPRODUCT((LEN(A1:H1)>0)*1)
で如何でしょうか。

お礼

ありがとうございます！

おかげさまでできました。大変助かりました。
これを機に、自分でも組めるように勉強したいです。

ありがとうございました。

回答No.7

BLUEPIXY

#4>（A1～H1の書式は文字列となっていて、３桁の数値も「111」
だったり、「'111」だったりします。）
数値に統一するつもりは無いのでしょうか？
A1:H1 だったらさした労力ではないと思いますが・
例えば、
空きセルに１といれコピーしてＡ１：Ｈ１を選択して
形式を選択して貼り付けから
値、乗算（空白を無視するにチェック）で
'111を111 に変換できます。

#4>「count(A1:H1)」の部分がシングルコーテーションだけのセルもカウントしてしまい、うまくいきません。
count の場合は、シングルコーテーションだけのセルは、カウントしません。

また、「次のシートのA1～A50」の状態も、
' だけだったり、'111 だったり、空白だったりするのでしょうか？

お礼

ありがとうございます。

実は数値と言いながら先頭にゼロがある数値であり、他のアプリで読み取る都合上、文字列で使いたいのです。
また、A1:H1ですが、それが何千行もあります・・・。

「次のシートのA1～A50」についてはどうにでもなりますが、元のシートが文字列で扱うのであれば、
やはり文字列になるかと思います。

お手数お掛けします。なにとぞ、ご教示ください。

回答No.6

mu2011

ＮＯ３です。
サイトを見るのが遅れました。次の方法は如何でしょうか。
=IF(SUM(IF(Sheet1!$A$1:$A$50=A1:H1,1))=0,"○",IF(SUM(IF(Sheet1!$A$1:$A$50=A1:H1,1))>=COUNT(A1:H1),"×","△"))

お礼

ありがとうございます。A1～H1のセルのデータ格納方法がわかり、
下記のような新たな問題が判明しました。

他の方のお礼にも書きましたが、なにとぞお知恵をご拝借ください。

A1～H1のセルには空白のセルも含まれていますが、よく見ると空白で
あってもシングルコーテーションだけが入っていたり、入って
なかったりすることがわかりました。
（A1～H1の書式は文字列となっていて、３桁の数値も「111」
だったり、「'111」だったりします。）

そのため、「SUMPRODUCT((COUNTIF(Sheet2!$A$1:$A$50,A1:H1)<>0)*1)
=COUNT(A1:H1)」の部分がシングルコーテーションだけのセルも
カウントしてしまい、うまくいきません。
（文字列セルなので、COUNTはCOUNTAに変更しました。）

この状態でも、要件を満たすことは可能でしょうか？
（３桁の文字列のあるセルだけでカウントできればいいのですが・・。
あるいは、先にすべてのシングルコーテーションだけを削除する
ことは可能でしょうか？）

回答No.5

imogasi

=SUM(IF(ISERROR(MATCH(A1:H1,Sheet3!$A$1:$A$50,0)),0,1))
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ同時押しで、Ａ１：Ｈ１内でＳｈｅｅｔ３のＡ１：Ｈ５０のどれかと一致するセル個数が出ます。
配列数式です。
一致件数が出るセルをＡ４として、別のセルに=IF(A4=8,"X",IF(A4=0,"○","△"))
式が長くなるので２段階に分けました。

お礼

ありがとうございます。A1～H1のセルには空白も含まれているので、
A4=8と評価することはできないのです。
せっかく考えていただいたのに申し訳ございません。

NO.4の方にも書きましたが、状況は下記の通りですので、なにとぞ
お知恵をご拝借ください。

A1～H1のセルには空白のセルも含まれていますが、よく見ると空白で
あってもシングルコーテーションだけが入っていたり、入って
なかったりすることがわかりました。
（A1～H1の書式は文字列となっていて、３桁の数値も「111」
だったり、「'111」だったりします。）

そのため、「SUMPRODUCT((COUNTIF(Sheet2!$A$1:$A$50,A1:H1)<>0)*1)
=COUNT(A1:H1)」の部分がシングルコーテーションだけのセルも
カウントしてしまい、うまくいきません。
（文字列セルなので、COUNTはCOUNTAに変更しました。）

この状態でも、要件を満たすことは可能でしょうか？
（３桁の文字列のあるセルだけでカウントできればいいのですが・・。
あるいは、先にすべてのシングルコーテーションだけを削除する
ことは可能でしょうか？）

回答No.4

BLUEPIXY

・一つもなければ「○」
・一つでもあれば「△」
・全部あれば「×」
=IF(SUMPRODUCT((COUNTIF(Sheet2!$A$1:$A$50,A1:H1)<>0)*1)=COUNT(A1:H1),"×",IF(SUMPRODUCT((COUNTIF(Sheet2!$A$1:$A$50,A1:H1))),"△","○"))

お礼

ありがとうございます。早速やってみたのですが・・、

A1～H1のセルには空白のセルも含まれていますが、よく見ると空白で
あってもシングルコーテーションだけが入っていたり、入って
なかったりすることがわかりました。
（A1～H1の書式は文字列となっていて、３桁の数値も「111」
だったり、「'111」だったりします。）

そのため、「SUMPRODUCT((COUNTIF(Sheet2!$A$1:$A$50,A1:H1)<>0)*1)
=COUNT(A1:H1)」の部分がシングルコーテーションだけのセルも
カウントしてしまい、うまくいきません。
（文字列セルなので、COUNTはCOUNTAに変更しました。）

この状態でも、要件を満たすことは可能でしょうか？
（３桁の文字列のあるセルだけでカウントできればいいのですが・・。
あるいは、先にすべてのシングルコーテーションだけを削除する
ことは可能でしょうか？）

回答No.3

mu2011

シート１のA1:A50をシート２のA1:H1で評価し、J1セルにその個数を設定します。
=IF(SUM(IF(Sheet1!$A$1:$A$50=A1:H1,1))>0,"あった","なかった")、入力完了時にｓｈｉｆｔ＋ｃｔｒｌ＋ｅｎｔｅｒして下さい。

お礼

ありがとうございました。できました。

無理を承知で、もう少しお教えください。
今まで一つでもあれば「あった」としましたが、
　・一つもなければ「○」
　・一つでもあれば「△」（つまりある数値とない数値が混在している）
　・全部あれば「×」（つまりある数値ばっかりだった）
と表示させることは可能でしょうか？

ＶＢでも結構です。お知恵をお貸しください。

回答No.2

BLUEPIXY

J1
=IF(SUMPRODUCT((COUNTIF(Sheet2!$A$1:$A$50,A1:H1))),"有","無")

お礼

ありがとうございました。できました。

無理を承知で、もう少しお教えください。
今まで一つでもあれば「あった」としましたが、
　・一つもなければ「○」
　・一つでもあれば「△」（つまりある数値とない数値が混在している）
　・全部あれば「×」（つまりある数値ばっかりだった）
と表示させることは可能でしょうか？

ＶＢでも結構です。お知恵をお貸しください。

回答No.1

fighting-okan

VBを使用せずでしょうか?
関数だけで評価するならば、
A列の個々に対してCOUNTIF関数を使いJ1に合計値を入れるとか。。。