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

公開日：2013年5月以前　最終更新日：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を忘れると、イベントが連鎖してしまいます。

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

同じテーマ「エクセル関数応用」の記事

入力規則のリストを、追加・削除に自動対応で作成
 入力規則のリストを、２段階の絞り込みで作成1
入力規則のリストを、２段階の絞り込みで作成2
ピポットテーブルの参照範囲を、追加・削除に自動対応で作成
関数を使って行列を入れ替える方法
 複数条件で検索し、複数データを取得する方法
 関数で銀行型丸め（最近接偶数への丸め）を行う方法
 関数で他シートへ並べ替える方法（サンプル：ABC分析）
VLOOKUPを他の関数でやる方法
 誕生日一覧から、指定誕生月の人を全員取り出す
 徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.条件分岐（Select Case）｜VBA入門

ホーム
エクセル全般
エクセル関数応用
ピポットテーブルの参照範囲を、追加・削除に自動対応で作成

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.

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