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

公開日：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.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

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

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

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