エクセルで複数のシートからデータを集計したい。

質問

エクセルで複数のシートから、あるデータを取り出し、別のシートに集計したいのですが、関数を入力すると莫大な量になってしまいました。
関数とは別の方法で集計する方法をご教授お願いします。

1日～31日のシートがあります。

シート1日内容は
A 　　B　　 　C 　　 D　　　 　 E　　　　
1 　NO 　原因(1)　原因(2)　　原因(3)　　
2　101　　　10　　　　　　　15　　 （b2+c2+d2）　
3　105　　　20　　　40　　　　　　　(b3+c3+d2)
4　108　　　　　　　20　　　100 (b4+c4+d4)

といった内容です。

1日から31日のセルA2などは固定した数値でありません。
なんで、例えばA3に101が入っている場合があります。

集計シートは
A 　　B　　 　C 　　 D　　　 　 E　　　　
1 　NO 　原因(1)　原因(2)　　原因(3)　　
2　101　　　　　　　　　　　　　　 （b2+c2+d2）　
3　102　　　　　　　　　　　　　　　(b3+c3+d2)
4　103　　　　　　　　　　　　　 (b4+c4+d4)

のようになってます。

B2に=SUMIF('1日'!A2:A4,A2,'1日'!b2:b4)+SUMIF('2日'!A2:A4,A2,'2日'!b4:b4)+・・・・・が31日のシート分まで作成するとファイルが重くなりすぎてしまい困っております。

よろしくお願いします。

回答No.3

_Kyle · ベストアンサー

#2です。

■１．[データの統合]＞[統合元データとリンク]する場合について

[統合元データとリンクする]をチェックした場合、
行番号の左側に[１]とか[２]とか[＋]とか表示されていると思います。

その[２]をクリックして、非表示になっている行を表示すれば判ると思いますが、
[統合元データとリンクする]機能は、
統合時点の元データの状況に応じて、
【元データを参照する行を挿入して数式を貼る】機能です。

統合時点で存在した数値を変更した場合には、貼られた数式が新しい値を返しますが、
統合時点で空白であったセルに追記したり、
元シートに行を追加した場合には対応しません。
--------------------------------------------------------
■２．[データの統合]＞[統合元データとリンク]しない場合について

元シートに行を追加したり、空白セルに追記したりする場合には、
[統合元データとリンクする]をチェックしない方が良いです。

集計表全体を選択して、[データ]＞[統合]＞[OK]とすれば、
最初と同じ設定でデータが更新されます。
--------------------------------------------------------
■３．[ピボットテーブル]の更新について

ピボットテーブルの場合も、２と同様に、
【手動で更新すれば自動で計算してくれる】機能です。

集計表を右クリックして[データの更新]をクリックすればデータが更新されます。
--------------------------------------------------------
■４．マクロを使う場合について

下記は【集計シートを開く度に自動的に[データの統合]をやり直す】サンプルマクロです。

集計シートのシートモジュールに記述してください。
集計シートの見出し行、見出し列はあらかじめ用意されているという前提です。
Excel2003で一応動作確認しましたが、
もし動作しなくても、あるいは動作に不満があっても
アフターケアは致しかねます^^;;

'-----------------↓　ココカラ　↓-----------------
Private Sub Worksheet_Activate()
　Dim myAry As Variant

　myCel = "A1"　'←集計表の(見出を含めた)左上隅セルを指定

　ReDim myAry(30)
　myPth = "'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]"
　Set myRng = ActiveSheet.Range(myCel).CurrentRegion
　For i = 1 To 31
　　myAry(i - 1) = myPth & i & "日'!" & myRng.Address(, , xlR1C1)
　Next i
　myRng.Consolidate myAry, xlSum, True, True, False
End Sub
'-----------------↑　ココマデ　↑-----------------

長くなってしまいました。
これで解決に至るかどうかわかりませんが、私としては最終回答ということで。
以上ご参考まで。長乱文陳謝。

お礼

ご回答ありがとうございます。
マクロを使用して解決いたしました。

初めてマクロを使用して感激しております。
本当にありがとうございました。

回答No.2

_Kyle

ん～、「複数シートにある表について、Ａ列の項目名(No)を基準に串刺し集計を行う」ということですよね。

通常は、

【データの統合】
http://www.eurus.dti.ne.jp/~yoneyama/Excel/tougou.html
http://www.h6.dion.ne.jp/~yakichan/nyumon/03/nyumon3-41.html

あるいは

【ピボットテーブル】
http://www.atmarkit.co.jp/fwin2k/win2ktips/407pivokushi/pivokushi.html
http://www11.plala.or.jp/koma_Excel/contents6/mame6040/mame604001.html

を使う場面ではないかと思いますが…。

ご参考まで。

補足

_Kyle様

ご回答ありがとうございます。
データ統合を試してみましたが、OKを押すと不要な列が挿入されてしまいました。
また、自動計算をしたいので、「統合元データとリンクする」にチェックを入れましたが、
自動に計算してくれません・・・。

ピボットテーブルは自動で計算できるのでしょうか？

回答No.1

noname#99913

A列の数字(コード？)は何種類あるのでしょうか。もし有限なら、各シートの下のほうに、SUMIF関数なりを使って種類ごとに合計を取り、集計シートで串刺し計算をしたらどうでしょうか。
　どうしても関数を使いたくないのであれば、マクロを使う方法がありますが……。

補足

kozirou54様

ご返答ありがとうございます。
A列のコードは318程あります。
現在ファイルのサイズが940KBでして、１MB 以内にファイルを完成したいと考えております。

>マクロを使う方法がありますが……。
マクロを使う方法をご教授いただけませんか。

よろしくお願いします。