GETPIVOTDATA関数の使い方|ピボットテーブルからデータ取得

Excel関数

Excel(エクセル)のGETPIVOTDATA関数の使い方|ピボットテーブルからデータ取得

ピボットテーブルをより便利に活用するための関数GETPIVOTDATAの使い方を初心者にも分かりやすく図解しています。

使用関数:GETPIVOTDATA

GETPIVOTDATA(ゲットピボットデータ)関数とは?

Excelのピボットテーブルはデータベースから切り口の異なる集計表を素早く作成できる便利機能で、GETPIVOTDATAはこのピボットテーブルのデータを抜き出すための関数です。

ピボットテーブルもGETPIVOTDATAも難しいし、よく解らないと食わず嫌いになっている人は結構多いと思います。ですが、一度コツを呑み込んでしまうと案外手軽に操作できることに気付くはずです。膨大なデータベースから魔法のように様々なクロス表を作ってくれるピボットテーブルは作業効率を格段に上げてくれます。そして、GETPIVOTDATA関数はピボットテーブルで作成した集計表の更に細かいデータを参照するのに手軽に使えます。

ピボットテーブルで集計表を作成

取り敢えずピボットテーブルで集計する手順を簡単に説明しておきます。

ピボットテーブルで集計する元表の任意のセルを選択して、「挿入」タブの左端にある「ピボットテーブル」をクリックします。
元表の任意のセルを選択して「挿入」→「ピボットテーブル」をクリック

「テーブルまたは範囲からのピボットテーブル」ダイアログが開いたら、「テーブル/範囲」に表のセル範囲、またはテーブル名か範囲に定義した名前が表示されているのを確認してEnterを押します。
「テーブルまたは範囲からのピボットテーブル」ダイアログの設定

画面の右側に「ピボットテーブルのフィールド」作業ウィンドウが開きます。

チェックボックスの付いたフィールド名(列項目)の一覧があり、下部には「フィルター」「列」「行」「値」の4ボックスがあります。

この内、「列」「行」「値」のボックスにフィールド名をドラッグして集計する項目を指定します。

例えば「日付ごとの売上データを集計」したいのでフィールド「日付」を「行」ボックスにドラッグドロップし、更に「メニューごとの売上データを集計」したいのでフィールド「メニュー」を「列」ボックスにドラッグドロップします。最後に集計する数値の「合計」フィールドを「値」ボックスにドラッグドロップして指定が完了します。
「ピボットテーブルのフィールド」でフィールド名を指定

ピボットテーブルが作成されました。行ラベル・列ラベルを適宜書き換えて、必要ならデザインを変更します。
ピボットテーブルが作成された

ピボットテーブルはフィールドを「行」「列」ボックスに追加すれば、もっと複雑な集計表も作れます。ピボットテーブルの作成・編集・分析に関わる詳細はこちらから

ピボットテーブルの使い方・初心者へ完全ナビ
ピボットテーブルの使い方を初心者向けに分かりやすく図解。切り口の異なった集計表に作り変えることができ、多方向からの分析が可能。フィールドの階層・グループ化、集計フィールドと集計アイテムの挿入、エラーへの対処法など、よく分かるプロセスで学習ナビ。

GETPIVOTDATA関数を自動で簡単に入力する方法

ピボットテーブルで集計表を作成したら、集計値をGETPIVOTDATA関数で取得してみましょう。GETPIVOTDATA関数の入力はとても簡単です。

ここでは「2月1日の総計」を取り出すことにして、取得した値を表示させるセルに「=」を入力します。
集計値を表示させるセルに=を入力

続けて、カットの総計のF5セルをクリックするだけで自動的にGETPIVOTDATA関数が入力されます。
取得する集計値のセルをクリックしてGETPIVOTDATA関数を自動で入力する

「2月1日の総計」が取得できました。
GETPIVOTDATA関数で「2月1日の総計」を取得

GETPIVOTDATA関数の引数と書式

GETPIVOTDATA関数をダイアログで組み立て、引数を確認してみましょう。

「数式」→「関数ライブラリ」→「検索/行列」からGETPIVOTDATAを選択します。
関数ライブラリの「検索/行列」からGETPIVOTDATAを選択

GETPIVOTDATA関数の書式の構成はこうなります。「データフィールド」と「ピボットテーブル」が必須、後は任意です。
GETPIVOTDATA関数の書式の構成

第一引数「データフィールド」

取得するデータのフィールド名を文字列で指定します。この例では「”合計”」と入力します。
GETPIVOTDATA関数の引数「データフィールド」を指定

第二引数「ピボットテーブル」

データを取得するピボットテーブルを指定します。セル及びセル範囲を参照することも可能で、その場合はF4キーで絶対参照にしておきます。この例ではピボットテーブルの最初のセルをクリックで指定します。
GETPIVOTDATA関数の引数「ピボットテーブル」を指定

ここまでが必須の引数で、この時点で数式を確定させるとピボットテーブル右下にある「データ全体の総計」が取得されます。
GETPIVOTDATA関数の必須の引数で総計が取得

ここからの引数は「フィールド」と「アイテム」のセットを指定していくことになります。更に細かい集計データを取得する場合の指定です。

第三引数「フィールド」

取得する詳細データのフィールド名を文字列かセル参照で指定します。この例では「フィールド1」にセル参照で指定します。
GETPIVOTDATA関数の引数「フィールド1」を指定

第四引数「アイテム」

取得する詳細データのアイテム名(フィールドに属する項目名)を文字列かセル参照で指定します。この例では「アイテム1」にセル参照で指定します。
GETPIVOTDATA関数の引数「アイテム1」を指定

GETPIVOTDATA関数で、より詳細な集計データが取得できました。
GETPIVOTDATA関数で詳細な集計データ取得

複数の条件を指定してGETPIVOTDATA関数でクロス位置のデータを取得する

複数の「フィールド」と「アイテム」のセットを指定することで、集計行と集計列のクロスした位置の集計データを取得できます。

集計行は「日付」フィールド配下に「2月1日~2月5日」のアイテムが並び、集計列には「メニュー」フィールド配下に「カット、カラー・・・」などの4アイテムが並んでいます。

「日付」のアイテムの一つ「2月2日」と「メニュー」のアイテムの一つ「カラー」が交差する位置のデータを取得します。「2月2日のカラーの売上合計」を取得するということですね。
GETPIVOTDATA関数で交差する位置のデータを取得するフィールドとアイテム

GETPIVOTDATA関数のダイアログを以下のように指定します。

  • データフィールド:”合計” 集計する値が属するフィールド名
  • ピボットテーブル:$A$3 対象のピボットテーブルのセルをクリックで参照し、F4で絶対参照
  • フィールド1:”日付” アイテム1が属するフィールド名
  • アイテム1:B12 「2月2日」と入ったセルを参照
  • フィールド2:”メニュー” アイテム2が属するフィールド名
  • アイテム2:B13 「カラー」と入ったセルを参照

GETPIVOTDATA関数で交差する位置のデータを取得するダイアログでの指定

「2月2日」と「カラー」の複数条件でデータが取得できました。
GETPIVOTDATA関数で複数条件のデータ取得

別シートにGETPIVOTDATA関数とピボットテーブルを置く場合の指定

GETPIVOTDATA関数をピボットテーブルとは別のシートに入力する場合、第二引数「ピボットテーブル」はシート名付きで指定する必要があります。

例えば、Sheet2にあるピボットテーブルのA2セルを第二引数「ピボットテーブル」に指定するなら、「Sheet2!$A$2」と記述します。
別シートにGETPIVOTDATA関数を入力した場合のダイアログの指定

ピボットテーブルの元データの変更とGETPIVOTDATA関数の更新

元データが変更されても、直ちにGETPIVOTDATA関数が自動更新されるわけではありません。ピボットテーブルを更新することで、GETPIVOTDATA関数も更新されます。

ピボットテーブルのセルを選択すると「ピボットテーブル分析」タブが出現します。これをクリックして「データ」グループの「更新」→「すべて更新」をクリックします。
「ピボットテーブル分析」タブ→「更新」→「すべて更新」をクリック

元データを変更した時はピボットテーブルの更新も行って最新の状況を反映させておきましょう。

GETPIVOTDATA関数の不具合やエラー

GETPIVOTDATA関数を自動入力する際にうまく動作しない、ピボットテーブルの元表を変更したらエラーが表示された、など不具合が生じた場合の要因と対処法です。

GETPIVOTDATA関数の自動入力ができない

GETPIVOTDATA関数で取得できるのは集計値だけです。フィールド名やラベル名を取得しようとしてもセル番地が参照されるだけでGETPIVOTDATA関数は動作しません。
GETPIVOTDATA関数で取得できるのは集計値の範囲のみ

フィールド名の変更でエラーが表示された

ピボットテーブルの元表のフィールド名を変更し、ピボットテーブルを更新すると、GETPIVOTDATA関数には#REF!エラーが表示されます。

この場合はまず、ピボットテーブルのフィールド名を元表と一致させます。「ピボットテーブルのフィールド」のボックスにドロップされたフィールド名をクリックして展開するプルダウンから「フィールドの設定」をクリックしてダイアログを開き、元表と一致した名義に変更します。
ピボットテーブルのフィールドの設定でフィールド名を変更

GETPIVOTDATA関数の該当するフィールド名を数式バーで修正するか、数式の入ったセルを選択した状態で「関数の挿入」ボタンを押してダイアログを開き、修正します。
GETPIVOTDATA関数の数式を修正

このように、フィールド名の変更はピボットテーブルにもGETPIVOTDATA関数にも影響が大きく、修正に手間がかかります。できれば慎重に行って下さい。GETPIVOTDATA関数の数式を組み立てる時も、フィールド名を正確に入力しないとエラーになります。

GETPIVOTDATA関数の使い方 まとめ
  • GETPIVOTDATAはピボットテーブルから集計値を取り出す関数です
  • セルに=を入力後、ピボットテーブルの集計値のセルをクリックすればGETPIVOTDATA関数は自動で入力できます
  • GETPIVOTDATA関数の必須の引数「データフィールド」「ピボットテーブル」だけ指定すると右下端の総計が返ります
  • GETPIVOTDATA関数で細部のデータを取得する場合は任意の引数「フィールド」「アイテム」をセットで条件指定します
  • 「フィールド」「アイテム」の条件を複数指定することでクロス位置の集計値が取得できます
  • ピボットテーブルの元の数値データを変更した時はピボットテーブルを更新します。それに連動してGETPIVOTDATA関数も更新されます
  • GETPIVOTDATA関数で取得できるのは集計値だけで、フィールド名やラベル名を取得しようとしても動作しません
  • ピボットテーブルの元表のフィールド名を変更すると、GETPIVOTDATA関数の結果はエラーになります
タイトルとURLをコピーしました