エクセルで、複数のシートにまたがる検索をしたいのですが

質問

エクセルで、複数のシートにまたがる検索をしたいのですが、どなたかお教えください。

シート1に任意のデータがあります。シート50からシート60までの10枚のシートのそれぞれW1からAW999までの間に、シート1のA1のデータがあれば、シート1のC1に「○」を打ちたいのですが、どうしたらいいのでしょう？

やりかけた式は、シート1のC1に
=IF(COUNTIF('50'!$W$1:$AW$999,A1),"○","")

これでシート50のみの場合はいけます。これをシート60まで一括して検索したいのですが、どなたか教えてください。よろしくお願いいたします。

回答No.1

Cupper · ベストアンサー

=IF(
SUMPRODUCT(('50'!W1:AW999=A1)*1
+('51'!W1:AW999=A1)*1
+('52'!W1:AW999=A1)*1
+('53'!W1:AW999=A1)*1
+('54'!W1:AW999=A1)*1
+('55'!W1:AW999=A1)*1
+('56'!W1:AW999=A1)*1
+('57'!W1:AW999=A1)*1
+('58'!W1:AW999=A1)*1
+('59'!W1:AW999=A1)*1
+('60'!W1:AW999=A1)*1
)>0,"○","")

見やすいように改行と相対参照形式にしていますが一行に収めて絶対参照に置き換えてください
（メモ帳などにコピーしたあと置換で$を付けた形に置き換え、その後に一行にまとめることをお薦めします）
SUMPRODUCT関数の配列に対応する積の和を求める機能を利用し、配列を範囲で渡して条件を付けます
条件に一致すれば TRUE、一致しなければ FALSE が返ってきますので、これに 1 を掛けてそれぞれ 1、0 と置き換えます
そしてそれぞれのシートについて同様に条件判断を行い足し算しています
（このケースでSUMPRODUCT関数は値の積を行っていません）
最後に IF関数で 1 以上であれば条件を満たしたセルがあると判断して ○ を表示するようにしています

※本来 SUMPRODUCT関数は
　　A　　　B
1　単価　個数
2　1000　2
3　1100　4
4　2000　5
といった表の合計金額を求める時などに使用します
この場合
　SUMPRODUCT(A2:A4,B2:B4)
とすることで求められます

お礼

ありがとうございました。
とりあえずうまくいきました。
コピーの仕方まで細かく教えていただいて、本当に助かりました。

ただ、これでシートの数が２０以上に増えそうなのですが
そんなときにもう少しシンプルな式になればとも思うのですが・・・。