年齢別に何を買ったかをカウントするには? (ピボットテーブルの作成方法)?
Question 35.2 Previous Next
はじめまして、VBAについて教えてください。
例)年齢別に何を買ったかをカウントしたいんですが...、excel2000でWS1とWS2と、シートが分かれています。
ws1集計前
ws1
ws2
ws2

ws2の 0 & 1 はフラグになっています。ws2の物品番号と年齢から判別して、ws1の
にカウントしたい。


上記の場合だと、
ws1集計後
Ws1 集計後

となります。どうかよろしくお願いします。
Answer   Copyright (C) 2000.11.29 永井善王
いろいろな方法が考えられるでしょうが、ピボットテーブルを利用すると簡単かと思います。
ピボットテーブル
左図がそのピボットテーブルで、手操作でも作れます。質問にある「Ws1 集計後」の図と同じですね。

ピボットテーブルを手操作で作成する方法を、簡単におさらいしてみましょう。すでにご存知の方法で行っても結構です。いろいろあるようですから。

ピボットテーブルの作成方法
1. マウスで 「Ws2」シートの A1:D7セル範囲を選択する
2. [データ]メニュー[ピボットテーブルとピボットグラフレポート]をクリックする

3. 「ピボットテーブル/ピボットグラフレポートウイザード - 1/3」ダイアログが表示されたら、オプションボタンの「Excelのリスト/データベース」と「ピボットテーブル」がオンになっていることを確認してから、[次へ]ボタンをクリックする

4. 同じく「2/3」ダイアログが表示されたら、「範囲」が正しく表示されていることを確認してから、[次へ]ボタンをクリックする
ピボットテーブル/ピボットグラフレポートウイザード - 3/3
5-1. 左図(3/3ダイアログ)が表示されたら、オプションボタンの「既存のワークシート」をオンにする

ワークシートのタブ 5-2. テキストボックスが機能するので、ピボットテーブルを作りたいワークシートのタブをクリックしてから、 そのワークシートの任意のセル(例えば A1セル)をクリックする。
5-3. 上図のようにテキストボックスに正しく表示されたことを確認してから、[次へ]ボタンをクリックする
ピボットテーブル/ピボットグラフレポートウイザード-オプション
6. 左図(オプションダイアログ)が表示されたら、「列の総計」と「行の総計」のチェックをオフにしてから、[OK]ボタンをクリックする

7. 上図(3/3ダイアログ)の[レイアウト]ボタンをクリックする
ピボットテーブル/ピボットグラフレポートウイザード-レイアウト
8. 左図(レイアウトダイアログ)が表示されたら、図のようにマウスでドラッグしてレイアウトする。その後、[OK]ボタンをクリックする

9. 上掲(3/3ダイアログ)の[完了]ボタンをクリックする
以上により、上記の5-2で指定したワークシートに、仮のピボットテーブルが作られます。次は、それを加工します。
ピボットテーブルツールボックス
10. 左図のとおり「レポートの書式設定」ボタンをクリック
オートフォーマットダイアログ
11. 左図を参考にして「テーブル1または2」をクリックしてから、[OK]ボタンをクリックする
以上により、最初の図のようなピボットテーブルに変わります。このテーブルのままでも良いならば、これで回答は終わりになります。
しかし、ws2シートのデータが日々更新され、その都度、ws1シートに集計するのであるならば、上記のようなハンド操作では面倒です。そういう場合は、マクロを組む価値があります。
ここではマクロについて解説しませんが、サンプルブックを用意しておきましたので、必要ならばダウンロードして研究してください。そのサンプルは実際に動かすことができるので、理解しやすいと思います。
ただし、一言お断りをしておきますが、ピボットテーブル関係のマクロコードはややこしいので、自動記録したものを部分修正して利用することが、賢明と思います。

なお、ws2シートのデータは日々件数が変わるでしょうから、上記の 1.で、「Ws2」シートの A1:D7セル範囲をハンド操作で選択した部分を、マクロにどう書けばよいか、および、ピボットテーブルのマクロをどう直せばよいかが知りたい場合は、下記のページを参考にしてください。
ピボットテーブルのソースデータの範囲を変数名で指定するには
ただし、このサンプルはExcel95用のため、Excel2000のマクロとは少し違います。参考にできるのは、
SourceData:= ソースデータ範囲 に関係する部分です。

 

Excel VBA Macro