ピボットテーブルを数式で参照する際におせっかいなGETPIVOTDATA関数が自動挿入設定をOFFにする方法 [ピボットテーブル基礎]
AさんAさん

ピボットテーブルで構成比を数式で入力しようとすると、GETPIVOTDATA関数が挿入されてしまい、いちいち直すのが面倒です。。

GETPIVOTDATA関数が挿入されないようにすることはできないでしょうか?

森田森田

できますよ!

Excelのオプション」ダイアログ経由でGETPIVOTDATA関数が挿入されないように設定を変更することが可能です!

では、詳細を解説していきますね。

解説動画:【ピボットテーブル#7】集計表へ計算を追加する方法4選+α - レポートへ比率や差異の比較結果を加える

この記事の内容は下記の動画でも解説しています。
コメント欄の「数式(四則演算)」の時間の部分をクリックすると該当の解説へジャンプできますよ!

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • Excelでデータの集計・分析作業を行うことが多い方
  • ピボットテーブルを実務で使う機会がある方
  • ピボットテーブルを数式で参照することがある方

前提条件

この記事はピボットテーブルの概要を理解していることが前提です。
→まず、以下の記事で概要を把握することをおすすめします。

数式でピボットテーブルレポートのセルを参照するとGETPIVOTDATA関数が自動挿入されてしまう

デフォルトでは、数式でピボットテーブルレポートのいずれかのセルを参照しようとすると、GETPIVOTDATA関数が自動的に挿入される設定となっています。

しかし、次のように構成比を数式で計算しようとする場合、分子を相対参照にして一つの数式をコピペで使い回したいのですが、GETPIVOTDATA関数だと絶対参照扱いとなり、コピペしにくいです。

これではおせっかいな状態のため、GETPIVOTDATA関数が自動的に挿入されない設定へ変更しておくと良いです。

GETPIVOTDATA関数が必要な際は、適宜入力すれば実務で困ることはほぼないのでご安心ください。

では、GETPIVOTDATA関数の自動挿入設定の変更手順を確認していきましょう。

GETPIVOTDATA関数の自動挿入設定の変更手順

今回の前提として、すでにピボットテーブルの集計条件がセットされた状態からスタートします。

GETPIVOTDATA関数の自動挿入設定の変更は、以下の3ステップとなります。

STEP1】リボン「ファイル」タブをクリック

まず、リボン「ファイル」タブをクリック(①)します。

STEP2】「Excelのオプション」ダイアログを起動

続いて、「その他」をクリック(②)し、「オプション」をクリック(③)してください。

Excelのバージョンによっては「その他」が「オプション」で表記されている場合があります。この場合は「オプション」のみクリックしてください。

STEP3】「ピボットテーブル参照にGetPivotData関数を使用する」のチェックをOFF

Excelのオプション」ダイアログが起動します。

あとは「数式」をクリック(④)し、「ピボットテーブル参照にGetPivotData関数を使用する」のチェックをOFF(⑤)にしたら、OK」をクリック(⑥)すれば完了です。

数式の設定時にGETPIVOTDATA関数が挿入されなければOK

数式挿入時に普通のセルと同じように設定することができました!

もちろん相対参照の設定もできるため、コピペが簡単ですね。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ピボットテーブル_GETPIVOTDATA関数自動挿入設定.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ファイルを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. リボン「ファイル」タブをクリック
  2. 「その他」をクリック
  3. 「オプション」をクリック
  4. 「数式」をクリック
  5. 「ピボットテーブル参照にGetPivotData関数を使用する」のチェックをOFF
  6. OK」をクリック

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

意外とデフォルトの設定のままでは「おせっかい」と言えるものもありますので、困ったらネット検索してみてください。

意外と「Excelのオプション」ダイアログなどで設定変更が可能だったりするケースは多いものです。

なお、ピボットテーブルの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

GETPIVOTDATA関数は個人的に実務で活躍したことがないです。

ただ、ピボットテーブルレポートのサイズが変わっても、条件に合致したセルの値を参照できるので、ピボットテーブルレポートを参照してVLOOKUP関数的に使うといったケースで有効かもですね。

他にもGETPIVOTDATA関数の有効な使い方があれば、ぜひコメントなどで教えてくださいm(__)m