先輩～！ピボットテーブルの数字を別の場所に表示したくて、=B5ってやったら、なんか変な関数が勝手に出てきました…！
すっごく長い数式で…コピペもできないし、ちょっと使いづらいです…
あぁ、それはGETPIVOTDATA関数だね。ピボットテーブルから特定のデータを正確に引っ張るための関数だよ。
確かにクセはあるけど、使いどころを選べば超便利だよ。向いてる場面と、そうじゃない場面を知っておけば安心！
Excelのピボットテーブルを使って集計まではできたけど、「そのデータを別の計算に使いたい！」と思ったとき、セル参照がうまくいかずに困った経験はありませんか？
そんなときに現れるのがGETPIVOTDATA関数。自動で出てくるけれど、「何これ？」「邪魔…？」と思ってOFFにしてしまった人も多いかもしれません。
今回は、このGETPIVOTDATA関数のメリット・デメリット、活用できる場面、さらには自動挿入をオフにする方法まで、まるっと解説します！
GETPIVOTDATA関数って何？
Excelのピボットテーブルを使っていると、セルに「=B5」って入力したはずなのに、なぜか長〜い数式が自動で出てきたことはありませんか？
たとえば、こんな感じです
=GETPIVOTDATA("売上", $A$3, "商品", "りんご")これが GETPIVOTDATA（ゲット・ピボット・データ）関数。
名前の通り、ピボットテーブルから特定のデータを取り出すための関数です。
自動で入力される関数
ピボットテーブルの中のセルを「=」をつけて参照しようとすると…
Excelが気を利かせて（？）このGETPIVOTDATA関数を自動で入力してくれます。
これは、ただのセル番地（たとえば「=B5」）だと、ピボットの並び順が変わったときに正しい値を参照できなくなる可能性があるためです。
GETPIVOTDATAは「項目名」と「値」などの組み合わせでデータを指定するので、レイアウトが変わっても値を取り違えにくいというメリットがあります。
基本構文と簡単な例
GETPIVOTDATA関数の基本的な構文は、以下のようになっています。
=GETPIVOTDATA(データフィールド名, ピボットテーブルのセル,
[フィールド名1, アイテム名1],
[フィールド名2, アイテム名2], …)例）
=GETPIVOTDATA("売上",$A$3,
"担当者名","クモダ リリ",
"取引先","株式会社ザク",
"年 (日付)",2021)- 「売上」：取得したいフィールド名（集計値）
- 「$A$3」：ピボットテーブルのどこかのセル
値を取り出すピボットテーブルを指定するためのもので、ピボットテーブル内のどこかのセル - 「”担当者名”,”クモダ リリ”」「”取引先”,”株式会社ザク”」「”年 (日付)”,2021」
：フィールド名とアイテム名の組み合わせ
この数式は、「$A$3」セルを含むピボットテーブル内の
「担当者名」＝「クモダ リリ」 and 「取引先」＝「株式会社ザク」 and 「年 (日付)」＝「2021」
に対応する売上データを取得します。
メリット：ピンポイントで正確なデータを取得できる！
GETPIVOTDATA関数は「項目名」と「値」などの組み合わせでデータを指定するので、レイアウトが変わっても値を取り違えにくいというメリットがあります。
レイアウトが変わっても平気！
セル参照とGETPIVOTDATA関数の違いを見てみましょう。
ピボットテーブルにフィルターをかけてみます。
このように普通の数式で「=C11」みたいにセル参照していると、ピボットテーブルの並びをちょっと変えただけで、参照先がズレてしまうことがありますよね。
でも、GETPIVOTDATA関数は「担当者名」「取引先」「年 (日付)」などのフィールド名で指定するから、どこに表示されていても関係なし！
- 他の人がピボットの順番を変えた
- 並べ替えやフィルターをかけた
このような場合でも正しい値を参照できるのは、実務ではかなりありがたいポイントです。
デメリット：柔軟性がなくて扱いづらい…？
GETPIVOTDATA関数、たしかに「正確性」はピカイチ。
でも実際に使ってみると、「あれ、思ったよりめんどくさいかも…？」と感じることも。
ここでは「便利なんだけどちょっと扱いづらい…」と感じてしまう理由＝デメリットをご紹介します。
条件がちょっとでもズレると、値が取れない
GETPIVOTDATA関数は、フィールド名とアイテム名をピタッと一致させないと動かないという厳しめ仕様です。
- フィールド名が「担当者名」ではなく「担当者」になっていたらNG
- アイテム名が「株式会社ザク」から社名変更で変わってしまったらNG
- データがフィルターで非表示になっていたら、そもそも存在しない扱いに
- 小計や総計を参照していた時に、その小計や総計を非表示にしたらNG
：例１：フィルターで「担当者名」が「クモダ リリ」のデータを非表示にすると「#REF!」
例２：「年 (日付)」ごとの各取引先の「小計」を非表示にすると「#REF!」
などなど、ちょっとした違いで#REF!エラーになってしまうことも。
引数が長くてごちゃごちゃする
GETPIVOTDATA関数、フィールドが増えるたびに引数がズラ〜ッと伸びていき、数式がどんどん長くなるのが難点。
=GETPIVOTDATA("売上",$A$12,"担当者名","クモダ リリ","取引先","株式会社ザク","年 (日付)",2021)この数式も割と長い…
目で見て確認しづらいし、手打ちで修正しようとするとどこがどの引数かわかりにくい！
コピーやオートフィルが効かない！
普通の数式なら、1行下にコピーしたら1行分ズレてくれる。
でもGETPIVOTDATA関数は、「指定した条件に一致するデータ」を取ってくる固定型なので…
コピーしても数式の内容がそのまま複製されてしまうんです。
つまり、「数式をコピーして別の商品の売上を取りたい」みたいな使い方ができません。
GETPIVOTDATA関数の使いどころ
ちょっとクセのある関数ですが、使いどころはあります。
見た目を整えた報告書や、「簡易ダッシュボード」や「売上モニター」などにピボットテーブルから必要な数字だけを抽出して表示したいときは、集計ミスや参照ミスの心配がなく使えます。
関数の一部（抽出条件の[アイテム名]など）をセル参照にすることで参照する値を切り替えたり、ピボットテーブルのフィルターに連動させることができます。
「ピボットテーブルのままだと見た目がゴチャつくから、報告書用にきれいに並べ直したい」
そんなとき、GETPIVOTDATA関数を使えば、ピボットから正確な値だけを引っ張ってこれるので…
- 欲しい順番に並べ替える
- 他の資料のレイアウトにあわせる
…といった調整が可能です！
また、フィルターと連動するため、スライサーと組み合わせれば、フィルターに合わせて自動で切り替わるようにもできます。
自動挿入をOFFにするには？
GETPIVOTDATA関数、使いどころによっては便利だけど、「勝手に出てきて邪魔…」なんて思ったこともあると思います。
カンタンに自動挿入OFFにできるので手順をご紹介します。
メニューの「ピボットテーブル分析」→「オプション」横の▼→「GetPivotDataの生成」のチェックを外せばOKです。
これで、ピボットテーブルのセルを参照しても、=GETPIVOTDATA(...) ではなく =B5 などの普通のセル参照になります。
「Excelのオプション」で変更する方法もあります。
- [ファイル] タブ → [オプション] を開きます
- 左側のメニューから [数式] を選択
- 「ピボットテーブル参照に GetPivotData 関数を使用する」のチェックを外す
- [OK] をクリックして設定完了！
ただ、「Excelのオプション」からは手順も多く面倒なので、１つ目の「ピボットテーブル分析」から変更する方法でいいんじゃないかと思います。
まとめ
今回は、ピボットテーブルから特定の値を正確に取得できる「GETPIVOTDATA関数」について解説してきました。
正直ちょっとクセが強い関数が、「合う場面」で使えば非常に強力で安心感のある集計ツールになります。
- 「この数値、絶対ズレちゃダメ」
- 「更新されても正確に値を取ってきてほしい」
という場面では、ぜひGETPIVOTDATA関数を思い出してください。
ピボットテーブルを使いこなす上で、「選択肢の1つ」として知っておくことが、業務効率アップ＆残業ゼロにきっとつながるはずです！
よーし、クセモノ関数でも味方にしちゃえば怖くないですねっ！
