ピボットテーブルのデータソース変更方法

Question

###Excel2013 ピボットテーブルのデータソース変更について

シート1 にシート2の集計データを纏めたピボットテーブルを作成しました。

シート1 にシート2をベースとして扱い、コピーしてシート3 にシート4を作成します。
その時、シート3のピボットテーブルの参照先をシート４に変えたいのですが、分析⇒データソースの変更という方法ではなくシート３のセルB1に記載されたシート名を参照しに行くようにしたいのですが、方法はあるのでしょうか？

よろしくお願いします。

Answer 1

以前、参考になりそうな質問がありましたのでご紹介します。
⇒参考

上記の質問のttyp03さんのアドバイスが参考になりそうです。
ご確認ください。

Comments

[hiratk]

参考サイトのご提示ありがとうございます。
目的のことと少し違っているようでした。参照範囲の自動変更というよりかは、参照シートの自動変更というのやりたかったです。
分析⇒データソースの変更からでもできるのでそこからやるか、別の集計の方法を考えたいと思います。

[jawa]

確かに質問内容は「参照範囲の自動変更」でしたが、ttyp03さんのアドバイスにある方法が「参照範囲を任意に変更する」マクロだったのでご紹介させていただきました。

今回はB1セルで指定したシートを参照したいということでしたので、以下のように少し手を加えればいいと思います。
```
Sub PvtFetch()
'アクティブシートからピボットテーブルを取得
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(1)

'アクティブなシートのB1セルからシート名を取得する
Dim strShtNm As String
strShtNm = ActiveSheet.Range("B1")

'指定シートの参照範囲を設定
Dim rng As Range
Set rng = Sheets(strShtNm).Range("A1:C14") 'ここでは仮にA1～C14の範囲としています

With pvt
'ピボットテーブルの参照範囲を変更
.SourceData = rng.CurrentRegion.Address(ReferenceStyle:=xlR1C1, External:=True)
'ピボットテーブルを更新
.RefreshTable
End With
End Sub
```

コピー後のピボットテーブルのあるシートからこのマクロを実行すると、ピボットテーブルの参照範囲をB1セルで指定したシートのA1：C14の範囲に変更します。

[jawa]

思い切りVBAマクロでのアドバイスをご紹介してしまいましたが、今回の質問はExcelだけだったのですね（汗

マクロを使用しない方法をちょっと模索してみましたが、INDIRECTを使った名前定義が使えるかもしれません。
手順は以下の通りです。

①「数式」-「名前の定義」から新規に名前を作成します。（ここでは仮に"PVT_DATA"と命名）
②「PVT_DATA」の参照範囲として、=INDIRECT($B$1&"!A1:C14")　を設定します。
③ピボットテーブルのあるシートでピボットテーブルをクリックし、「オプション」-「データソースの変更」から「テーブルまたは範囲」の指定で先ほど作成した名前定義「PVT_DATA」を指定します。
④このシートをコピーして、B1セルの内容を変更します。
⑤ピボットテーブルを右クリック⇒「更新」すると、B1セルのシートに参照先が変わります。

参考になれば幸いです。