ピポットテーブルの参照範囲を、追加・削除に自動対応で作成

最終更新日：2013-06-09

ピポットテーブルの参照範囲を、追加・削除に自動対応で作成

ピボットテーブルの参照範囲を可変に設定し、データの追加・削除に自動で対応させます。

以下の表で説明します。

	A	B	C
1	番号	点数	金額
2	2	12	200
3	2	13	300
4	3	21	250
5	4	24	350
6	5	28	280
7	6	20	160
8	7	6	180
9	5	16	290

名前を定義します。

シート「リスト」を選択します。
Ctrl+F3で名前定義を起動する。メニュー等からの起動はバージョン毎に違います。
「名前」に「データ1」と入力。
2007以降の場合、範囲は「ブック」、2003にはありません。
「参照範囲」に「=$A$1:INDEX($C:$C,COUNTA($A:$A))」
または、「=OFFSET($A$1,0,0,COUNTA($A:$A),3)」、どちらでも良い。
「OK」

ピボットテーブルの設定です。

ピボットテーブルを起動する。
テーブル/範囲に「データ1」を入力。F3を押すと名前定義の一覧から選択できます。
他の設定はご自由にどうぞ

以上で完成です。

データの追加・削除に自動的に対応されます。
もちろん、ピボットテーブルの「更新」は必要です。
もし、「更新」も自動にしたい場合は、マクロを作成します。ここの下部で簡単に説明しています。

説明が必要な部分は、

=$A$1:INDEX($C:$C,COUNTA($A:$A)
=OFFSET($A$1,0,0,COUNTA($A:$A),3)

だけだと思います。

まず、INDEXは、INDEX(配列,行番号,列番号)です。

配列の中の行番号と列番号で指定される要素を返します、配列が列指定なので、列番号は省略しています。
また、COUNTAは指定範囲の、空白でないセルの個数を返します。
つまり、A1からC列の入力されているセルの最終セルの範囲を指定しています。

さらに、OFFSETは、OFFSET(基準, 行数, 列数, 高さ, 幅)です。

基準のセルから指定の行数と列数だけシフトした位置にある、高さと幅のセル範囲を返す関数です。
つまり、A1から3列データ行数の範囲になります。

マクロで「更新」を自動化

データのあるワークシートのシートモジュールに以下を作成します。


  
    
      Private Sub Worksheet_Change(ByVal Target As Range)<BR>

　　Application.EnableEvents = False<BR>

　　Worksheets(ピボットテーブルのあるシート名).PivotTables(ピボットテーブル名).PivotCache.Refresh<BR>

　　Application.EnableEvents = True<BR>

End Sub

EnableEvents = Falseを忘れると、イベントが連鎖してしまいます。

これで、データを変更すると直ちにピボットテーブルも更新されます。