offset関数を利用してピポッドテーブルの参照範囲を自動で修正を行う

Question

###・実現したいこと
ピポッドテーブルの参照範囲を、
「名前の定義」にoffset関数を利用した参照範囲を指定することで、データの増減があっても自動変更を行うことが出来る。
というのを他サイトでしり自分なりに試したのですが、上手に出来ません。

最終的に行いたいことは作成済みのピポッドテーブルのグラフを、
マクロボタンを押すことで、

参照範囲のシートデータを書き換え ←解決済み
参照範囲を再設定 ←今回質問で実現したいこと
グラフに参照範囲の変更を反映する ←解決済み

ということが行いたいです。

データの書き換えを行う上で、行の増減が発生するため、
参照範囲は必ず再設定を行う必要がございます。
※参照範囲上に空白を含めたくないため、あらかじめ範囲を多めに取っておく方法は避けたいです。

参照範囲の元となるシートデータを変更するコードまでは掛けたのですが、
参照範囲の再設定の方法で行き詰っています。

offset関数の設定方法がいまいち分からず質問をさせていただいた次第であります。

offset関数の限らずこんな方法もあるよというヒントでも構いませんのでご教授いただけたら幸いです。

よろしくお願い致します。

###発生している問題・エラーメッセージ
想定した値が返らず、「＃VALUE」が表示される

###該当のソースコード
=OFFSET($A$1,0,0,COUNTA($A:$A),4)

4列固定
行は変動あり
というのが条件になります。

###補足情報(言語/FW/ツール等のバージョンなど)
EXCEL 2013 使用

Answer 1

「名前の定義」にoffset関数を利用した参照範囲を指定することで、データの増減があっても自動変更を行うことが出来る。

おそらく参考にされた記事の内容は、
①OFFSETとCOUNTAで自動的に参照範囲が変更される「名前」を作成する
②ピボットグラフのデータ参照として、①で作成した「名前」を使用する
③参照データに行追加が行われた後、ピボットグラフで「データの更新」をすることで追加データがグラフ範囲に反映される
というものではないでしょうか？

私もこのような記事を参考に、実際にピボットグラフを作成してみましたが、私の環境（Excel2010）では、
・固定範囲(Sheet1|$A$1:$D$10)で作成した「名前」では問題なく作成できる
・可変範囲を指定した「名前」ではエラーが発生し、ピボットグラフが作成できない
エラー内容：「そのピボットテーブルのフィールド名は正しくありません。…」
という状況でした。

別案

offset関数の限らずこんな方法もあるよというヒントでも構いませんのでご教授いただけたら幸いです。

名前の定義で実現する方法ではありませんが、別案として以下のような方法で可変データに対応できましたのでご紹介します。
⇒参考サイト

①対象データ範囲をテーブル化する
 ⇒「挿入」⇒「テーブル」からデータ範囲(=$A$1:$D$10)を入力

②ピボットグラフのデータ範囲を設定する
 <新規ピボットテーブルを変更する場合>
 ⇒「挿入」⇒「ピボットグラフ」からテーブルまたは範囲の設定に①で作成したテーブル名を設定する

 <既存のピボットテーブルを変更する場合>
 ⇒対象ピボットテーブル上で[ALT]+[D]キー押下⇒[P]キー押下でピボットテーブルウィザードの3/3頁を表示
 ⇒「戻る」ボタンで2/3頁を表示し、データ範囲の設定に①で作成したテーブル名を設定する
 
③参照データに行追加が行われた後、ピボットグラフで「データの更新」をすることで追加データがグラフ範囲に反映される

以上のような流れで可変データ範囲に対応できると思います。

ボタン押下でデータ更新する場合、VBAではピボットグラフのデータ更新のみ行えばいいと思います。

'ピボットテーブルの更新
Worksheets("Sheet1").PivotTables(1).RefreshTable

今回のニーズに合っているかわかりませんが、参考になれば幸いです。

Comments

[mimatama]

jawa様

参考サイトのご提示ありがとうございます。
似たようなページを見ていたので、
テーブル名称を設定するという方法は試してはいたのですが、
行が追加された場合には対応が出来たのですが、
行が減少する場合は対応が出来ませんでした。(やり方が間違っているのでしょうか？)
そのため別案で、、、と思い調べた結果offset関数にたどり着いた次第であります・・・

[jawa]

テーブルの中身を減らす際、DELETEで中身だけクリアしてもテーブルのデータ範囲は変更されませんので、不要データ行は削除する必要があります。
旧データをDELETEでクリア⇒新データを張り付け、のような操作を行っているのであれば、旧データを全行削除⇒新データを貼り付けのような手順にすることでテーブル範囲も変更されると思います。

上記の操作が面倒であるとか、運用にそぐわないようであれば、ttyp03さん提示のVBAでデータ範囲を再設定する方が現実的になってきそうです。

[mimatama]

おっしゃるとおり”旧データをDELETEでクリア⇒新データを張り付け”という手順で進めていました。
データ範囲として設定されている不要行の削除が必要だったことですね。
コードに不要行を削除するコードを取り入れてみます。

そこは盲点でした・・・
一度試して結果は後ほど報告いたします。

[mimatama]

Do Until Sheets("計算シート").Cells(3, 1) = ""
Sheets("計算シート").Cells(3, 1).Select
Selection.ListObject.ListRows(2).Delete

Loop

データ張替え前にデータリストの2行目以降を削除するという処理を入れることで解決できました。
ご指摘をいただいたおかげで解決することが出来ました。
大変助かりました。
このたびはありがとうございました。

Answer 2

VBAからは以下のような感じでデータソースを設定することができました。

With Worksheets("Sheet1").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1")
    .SourceData = "Sheet1!R2C2:R13C3"
    .RefreshTable
End With

シート名とピボットテーブル名は適宜修正してください。
SourceDataに渡す範囲はRC形式でしたので、上記例ですと、B2:C13とイコールになります。
あとはこの数を可変させればよいと思います。

Comments

[mimatama]

ttyp03様
コードのご提示ありがとうございます。

早速コードを反映させてみましたが、
「Worksheet クラスの PivotTables プロパティが取得できません」
とのエラーがでて動きませんでした。

表示されているシートに対象のものがなかった場合に表示されるエラーとの事だったので、
シートを切り替えるコードを入れてみましたが同じエラーが出てしまい動きませんでした。
※ボタンを押した時点では別のシートがアクティブのため

Worksheets("Sheet1").=ピポッドテーブルのあるシート名
PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1")=ピポッドテーブルのテーブルの名前定義
という解釈でよろしかったでしょうか？

ピポッドテーブル用のテーブル名は「協力テーブル」と定義
ピポッドテーブルのあるシート名は「作図者工数」
集計用のシート「計算シート」
としています。

下記に書き換えたコードを記載しておきます。

Sheets("作図者工数").Select
ActiveSheet.ChartObjects("グラフ 1").Activate
With WorkSheets("作図者工数").PivotTables("協力テーブル")
.SourceData = "計算シート!R2C2:R13C3"　
.RefreshTable
End With

ちなみに載頂いたコードの内容は
「Sheet1にあるピポッドテーブルのソースデータを"Sheet1!R2C2:R13C3"の範囲に
変更し、グラフを更新する
という認識でよかったでしょうか？
コードだけではまだ作業内容が具体的にイメージできず・・・

[ttyp03]

Worksheets("Sheet1")
→ 作成済みのピボットテーブルがあるシート
PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1")
→ ピボットテーブルツール→オプションにあるピボットテーブル名（Excel2010の場合）
.SourceData = "Sheet1!R2C2:R13C3"
→ ピボットテーブルが参照するデータの範囲（ピボットテーブルツール→オプション→データソースの変更）

認識としては正しいと思います。
しかしエラーの内容から判断すると、作図者工数シートにはピボットテーブルが存在していないように思えます。
またSourceDataに設定する範囲も、実際に値が入っているセルを指定しないとエラーになるようです。
グラフに関しては今確認してみましたが、上記コードで更新されましたので大丈夫の様です。

[ttyp03]

ちなみにWorksheetを直接操作しているので、別のシートがアクティブだからとかは関係ないです。
なので、修正いただいたコードの最初の２行は不要です。

[jawa]

ちょっとかみ合っていない気がしたので、横から失礼しますm(__)m

ttyp03さん提示のコードは、ボタンを押したときにExcelVBAの処理でピボットグラフの参照データを新しいデータ範囲に設定しなおし、グラフを再描画するというものです。
この方法では、VBAの処理の中で最新のデータ範囲を確認し、その範囲をピボットグラフの参照データとして再設定することになります。(サンプルコードでは常に固定範囲"Sheet1!R2C2:R13C3"となっています。)

VBAの中でデータ範囲を特定するので、「名前の定義」で動的に変動するデータ範囲を定義しておく必要はなくなります。
※.PivotTables("？？？")の”？？？”に指定するのは、「名前の定義」で作成した名前ではなく、ピボットテーブル自体の名前です。エラー原因はココかと推測。

一方、mimatamaさんが調べられていた「名前の定義」を利用する方法は、ExcelVBAが使えない人でもExcelの機能でデータ範囲を自動で変更できるようにする方法です。
Excel関数を駆使した、少しマニアックな方法ともいえます。

ピボットグラフが参照するデータ範囲が自動的に変動するので、データ範囲の再設定は不要になります。
最新データの反映には「データの更新」が必要ですが、これもピボットグラフの右クリックメニューからできますので、VBAは基本不要となります。

どちらがいいということでもなく、方法はいくつかあるので目的に合った一番使いやすい方法を選択してください、ということです。

[ttyp03]

いや、やろうとしていることは間違っていないと思うので大丈夫だと思いますよ（たぶん）
肝心のSourceDataの設定にたどり着く前にエラーが発生しているだけだと思います（たぶん）

jawaさんの回答にあるテーブルを使う方法はなかなかいいですね。
あまりテーブルは使わないので知らなかったのですが、名前が付けられるのですね。
ただテーブルだとデザインに影響するのが懸念ですが、特に制限がなければこれでいいような気がしますね。
マクロ側はRefreshTableだけで済みますし。