excelの複数シートを検索対象にして重複行を抽出する方法

質問

ある値が重複する行を抽出したいのですが
抽出元データが600,000行近くあり、1sheetに入らず9シート程に分けました。
countifを使おうとしたのですが検索範囲に複数シート指定が出来ず（3D-参照の対象関数ではないようです）、
=COUNTIF('sheet1:sheet9'!$C:$C,C1)としてみたのですが、重複行があっても全て#VALUE!となってしまうので機能できていないようです。

具体的には
sheet1～8に下記のようなデータがあり、C列が重複する行を抽出したい。
A列：id、B列:名前、C列：メアド、D列：xx

宜しくお願い致します。

回答No.1

noname#176215 · ベストアンサー

60万件もあるなら Excelの限界を超えているように思いますが
どうしてもというのなら ピボットテーブルという手があります。
[複数のワークシート範囲]を指定して 項目の個数を集計します。

お礼

おっしゃる通り、そもそも限界を超えてましたね。
accessの存在とそれが使える（入ってる）ことをすっかり忘れていてaccessではデータ分割も不要で全データから重複行のリスト作成を無事出来ました。
教えていただいたピボットテーブルでも複数シート選択して重複数のカウントができるんですね。
ピボットテーブルあまり使っておらず知りませんでした。ありがとうございました。

回答No.2

Nouble

ちょっと確認が取れないので判らないのですが
=countif((Sheet!$C:$C=c1)*{1,0,0,0,0,0,0,0,0}+(Sheet2!$C:$C=c1)*{0,1,0,0,0,0,0,0,0}+(Sheet3!$C:$C=c1)*{0,0,1,0,0,0,0,0,0}+(Sheet4!$C:$C=c1)*{0,0,0,1,0,0,0,0,0}+(Sheet5!$C:$C=c1)*{0,0,0,0,1,0,0,0,0}+(Sheet6!$C:$C=c1)*{0,0,0,0,0,1,0,0,0}+(Sheet7!$C:$C=c1)*{0,0,0,0,0,0,1,0,0}+(Sheet8!$C:$C=c1)*{0,0,0,0,0,0,0,1,0}+(Sheet9!$C:$C=c1)*{0,0,0,0,0,0,0,0,1},1)
と、試しにしてみて下さい
駄目だったら
=SUMPRODUCT(1,(Sheet1!$C:$C*{1,0,0,0,0,0,0,0,0}+Sheet2!$C:$C*{0,1,0,0,0,0,0,0,0}+Sheet3!$C:$C*{0,0,1,0,0,0,0,0,0}+Sheet4!$C:$C*{0,0,0,1,0,0,0,0,0}+Sheet5!$C:$C*{0,0,0,0,1,0,0,0,0}+Sheet6!$C:$C*{0,0,0,0,0,6,0,0,0}+Sheet7!$C:$C*{0,0,0,0,0,0,1,0,0}+Sheet8!$C:$C*{0,0,0,0,0,0,0,1,0}+Sheet9!$C:$C*{1,0,0,0,0,0,0,0,1}=c1)*1)
としてみてください

簡単に説明すると
まず前置きとして
{1;0;1}*{1,0,0}
＝1,0,0
　0,0,0
　1,0,0
となります
同様に
{1;1;0}*{0,1,0}
＝0,1,0
　0,1,0
　0,0,0

{0;0;1}*{0,0,1}
＝0,0,0
　0,0,0
　0,0,1
ですね
なので
{1;0;1}*{1,0,0}+{1;1;0}*{0,1,0}+{0;0;1}*{0,0,1}
＝1,0,0　　0,1,0　　0,0,0
　0,0,0　＋　0,1,0　＋　0,0,0
　1,0,0　　0,0,0　　0,0,1
＝1,1,0
　0,1,0
　1,0,1
と
丁度縦方向への配列{1;0;1}と{1;1;0}と{0;0;1}を横に並べたようになると言うのを頭に入れておいてください

次に
(c1=c1)はツルー
(c1<>c1)はフェイル
(c1:c3=c1)はのおのおのに対し「=c1」かどうかを判定し
例えば{ツルー;フェイル;フェイル}と言う具合の配列数式を作ります
同様に(Sheet!$C:$C=c1)は
{c1;c2;c3;c4;c5;c6;c7;c8;…}のおのおのに対し「=c1」かどうかを判定し
{t;t;f;t;f;t;t;f;f;f;t;…}と言う具合に判定した配列数式のリストを作ります
注)tはツルーを　fはフェイルを表しています

これを先ほどの法則で
t;t;f;t;f;t;t;f;f;f;t;…
f;t;f;t;f;t;f;f;t;f;t;…
f;f;f;f;t;f;t;f;f;f;t;…
…
といった感じに2次元マトリックスに繋いだわけです
因みにエクセルでは
ツルーに1を掛けると1に
フェイルに1を掛けると0になります
なのでCOUNTIFでツルーの変わりに1の出現数を数えることで
条件一致回数を数えることに代えています

しかしこれは巨大な配列数式となるので
かなりなPCパワーが要るかも知れません
余りお勧めは出来ないかもです

お礼

丁寧なご説明ありがとうございます。
結局accessで出来たのであとでよく読んで試してみようと思います、
excel式のあれこれ教えていただいて参考になりました。