集計一覧を作成する一般的な方法？

質問

Excel2003　WinXPです。

項目、大項目、月、金額というフィールドのデータベースがあり、その集計一覧を作成したいです。
項目は別に項目データベースがあり、あらかじめリンクさせてセルに入っています。
このシートのフォーマットを動かさずにセルにデータを入れるにはどうしたらいいでしょうか？

現在はデータシートをループさせ、項目、月、大項目に一致する金額を合計→データ末端まで行ったら出力シートのセルに入力、という力技を使っていますが速度が遅いです。もっといい方法はないでしょうか・・。
ピポットテーブルだと合計金額は簡単に出ますが、データが入っているものしか表示されません。基本が分かってないので皆さんがどうしていらっしゃるのか大まかな考え方でいいので知りたいです。

分かりにくいですが作りたいシートの表はこんな感じです。

分類(大項目)
　　　　　1月｜2月｜3月・・12月まで(月)
__________________________________
項目１　|　 　 |
(項目)__|合計|合計|_______________
項目２　|　 　｜ |
__＿____|合計|合計|________________

回答No.4

DoragonFang · ベストアンサー

質問されている内容がよくわからないのですが、
こういうことでいいのでしょうか？

まず、データシートがあり、次のような表になっている。
　　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ
１　　項目　　　大項目　　　月　　　金額
２　　A　　　　 あああ　　　1　　　　1000
３　　C　　　　 あああ　　　3　　　　1200
４　　B　　　　 いいい　　　2　　　　3000
５　　B　　　　 ううう　　　1　　　　4000
６　　A　　　　 いいい　　　3　　　　5000
７　　A　　　　 あああ　　　1　　　　1500
８　　C　　　　 いいい　　　3　　　 10000
９　　A　　　　 あああ　　　4　　　　1200

次にこれから、次のような集計表を作りたい。

　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　Ｅ
１　あああ　（大項目）
２
３　　　　　　月→
４　項目　　　1　　　　　2　　　　3　　　　4
５　A　　　　　25500　　 0　　　　0　　　　1200
６　B　　　　　　500　　 0　　　3400　　　　　0
７　C　　　　　　　0　　 0　　　1200　　　　　0

ということでしょうか？

単純にこれだけの表なら、集計表に関数で合計を出します。私の場合で、一般的ではないかもしれませんが。

先のデータシートがＡ列に「項目」、Ｂ列に「大項目」、Ｃ列に「月」、Ｄ列に「金額」が入っており、上記のように、２行目から実際のデータが入っているものとします。
また、集計表は、Ａ１セルに「大項目」、Ａ列４行目から「項目」が下にあらかじめ入力されているものとします。
さらに、集計表の月は、３行目Ｂ列に「１」、Ｃ列に「２」、Ｄ列に「３」・・・となっていることにします。ここは検索に使うので、データシートの月が数字で入っているのなら、同じように数字だけにします。（上表参照）

で、Ｂ４セル（集計表の合計の左上）に次の式を入れます。
なお、データシートはSheet1、集計表はSheet2というシート名とします。また、データは２８行目まで入っているとします（ここは必要に応じて変えて下さい）。

=SUMPRODUCT((Sheet1!$B$2:$B$28=Sheet2!$A$1)*(Sheet1!$A$2:$A$28=Sheet2!$A4)*(Sheet1!$C$2:$C$28=Sheet2!B$3)*Sheet1!$D$2:$D$28)

この式を、下へ項目数だけ、右へ１２月まで、合計を出すセル全部にコピーします。

あとは、大項目ごとにこの表を作ればいいんではないかと。
はずしてたら、ごめんなさい。

お礼

有難うございます！SUMPRODUCTで出力できたのですが、実は条件がもう一つあり、しかもセルが6000近くある表にコピーしてたらオプションの「計算方法」を「手動」にしないととても動かせないほど遅くなってしまいました。「再計算中」の時間もかなり長いです。
もうちょっと表が小さければ使えそうだったのですが・・。
VBAの計算式を見直して、ループをかけるときに計算方法を手動にしたら割と早くなったのでそれで行ってみようと思います。
複数条件の合計値を表示する表を作る場合
１・セルに配列計算式
２・ピポットテーブルを使ってどうにかする
３・VBA内で配列をループさせて書き込み
のどれかかなぁとは思ったのですがやはりデータ量等によって色々あるようですね。
とても参考になりました。

回答No.3

hyorono

図にすると・・・

　 　　 Ａ　　　Ｂ　　 Ｃ
1 ｜　取引先　　月　　金額
2 ｜　ａａａ　 10月　　500
3 ｜　ｂｂｂ　 9月　　300
4 ｜　ａａａ　 12月　　200
5 ｜　ｃｃｃ　 5月　　700
6 ｜　　　　　　1月
7 ｜　　　　　　2月
8 ｜　　　　　　3月
9 ｜　　　　　　4月
10｜　　　　　　・
11｜　　　　　　・
12｜　　　　　　・

と、月の列だけでいいので、1～12月までを
とりあえず入力（範囲内に）
で、その後更新
うまくいったら、この入力した部分は
消しちゃいましょう。
てな感じではいかかでしょうか？
ほんとはもっと簡単な方法があるのかもしれませんが
試してみてください。

お礼

詳しいご説明していただき有難うございます！
行(項目)の出力が難しいのでピポットテーブルは難しそうだなと思いました。
実はピポットテーブルあまり使ったことなかったのでとても参考になりました。有難うございました。

回答No.2

hyorono

複数のシートのデータをまとめることは出来ないと思います
少なくとも、私は知りません。ごめんなさい。
で、本題ですが、元もとのデータに1月～12月すべてが無いということでしょうか？
その上で、ピボットテーブルに表示させたいという事でしたら
元もとのデータの項目「月」に一度1月～12月の行を作ってあげて
（もちろんピボットテーブルを作るときに選択した範囲内で）
その後、ピボットを更新すると出来るのでは？
更新後は、その行は消してあげれば元もとのデータにも、問題ないかと・・・

補足

そうです。項目も月もデータはあったりなかったりします。
月はダミーデータっぽいのを入れたら動くのですが、
項目の部分は(データ数も）可変データなのでちょっと難しいかと・・
出力したい形式は決まっているのでダミーデータを入れると順番がおかしくなるのも困ります・・。
(データが入っているものから表示される）
考えてくださって有難うございます。

回答No.1

hyorono

ピボットテーブルで作成可能です
すべて表示したいボタン（分類＝1月～12月等）を右クリックして、フィールドの設定→データのないアイテムを表示する　にチェックを入れれば表示します
1月・2月・5月・・・
　　　↓
1月・2月・3月・4月・5月・・・

補足

有難うございます。
やってみましたがデータベース内の「月」フィールドに1月しか入っていない場合はやはり1月しか表示されません。
「月」、「項目」のフィールドは他のシートから引っ張って来たいのですがそんなことは可能なのでしょうか。