2015年04月05日

GETPIVOTDATA関数でピボットテーブルのデータを引用(取得)してみた /Excel/ピボットテーブル

Excelのピボットテーブルで集計されたデータをピボットテーブルの外から"GETPIVOTDATA関数"を使って引用(取得)してみます。

例えばピボットテーブルがあります。
営業の担当者名と商品毎の受注金額のクロス集計表になっています。
20150405_101-1.JPG

ちなみに、フィールドリストはこうなってます。
20150405_101-2.JPG

ここで、セル"B2"に"加藤 泰江"さんの"アメリカンクラッカー"の受注金額"\5,400"、つまりセル"C9"の値を引用してみます。
セル"B2"で"="(イコール)を入力して、セル"C9"を選択すると、自動的に"GETPIVOTDATA関数"が入力されます。
20150405_102.JPG
関数の引数は、ピボットテーブルの値"金額"(A6)の"氏名"が"加藤 泰江"さん"商品名"が"アメリカンクラッカー"と言う意味にまります。

んで、結果は"5400"になりました。
つまり、セル"C9"を引用したのではなく、"加藤 泰江"さんの"アメリカンクラッカー"を引用したわけです。
20150405_103.JPG

なので、行ラベルの並び順を変えてみても、、、
20150405_104.JPG

加藤さんのデータは"C25"に移動してしまいましたが、ちゃんと追っかけています。
20150405_105.JPG

また、今度はピボットテーブルの集計を変えてみます。
行ラベルに"氏名"と"商品名"をレイアウトしてみます。
20150405_107.JPG

すると、このようなピボットテーブルになりました。
20150405_108.JPG

が、これもきっちり"B916"の値を表示しています。
20150405_109.JPG

今度は、ピボットテーブルから"商品名"を削除して、"氏名"と全ての商品の受注金額の"合計"だけにしてみます。
20150405_110.JPG

すると、引用すべきデータがピボットテーブルに表示されてないため、エラー"#REF"が表示されました。
20150405_111.JPG

引数にセルを指定する
また、"氏名"や"商品名"の引数は"加藤 泰江"や"アメリカンクラッカー"等のテキストデータではなく、通常の関数の様にテキストが入力されたセルを指定することももちろんできます。
こんな一覧表を作って、"氏名"と"商品名"の引数をセルにすると、、
20150405_112.JPG

"加藤 泰江"さんと"森上 偉久馬"さんのの"アメリカンクラッカー"と"インドカレーパン"の金額が引用できました。
20150405_113.JPG

GETPIVOTDATA関数の解除
このように、ピボットテーブルの外から"="でピボットテーブルのセルを選択すると自動的に"GETPIVOTDATA関数"が入力されてしまうのですが、通常通りセルを引用することもできます。
「ピボットテーブルツール」、「オプション」、「ピボットテーブル」から「GetPivotDataの生成」のチェックを外します。
20150405_114.JPG

セル"D2"で"="でセル"C9"を選択すると、通常通り"=C9"と入力されました。
20150405_115.JPG

GETPIVOTDATA関数の応用
さて、この"GETPIVOTDATA関数"でピボットテーブルの値を引用することによって、ピボットテーブルの中で計算出来ないような式や関数、つまり"集計フィールドの挿入"が出来ないような計算を、ピボットテーブルの外から行うことが出来、データソースのが更新されて、ピボットテーブルの値が更新されても、常に最新の計算結果を求めることができます。
例えば、加藤さんと森上さんのデータにそれぞれ異なる係数を掛けて合計するとか、、、
20150405_116.JPG

ピボットテーブルの"集計フィールドの追加"で金額に係数を掛けることは可能ですが、氏名毎に異なる係数を指定することはできません。
20150405_117.JPG

また、ピボットテーブルのレイアウトでは適当ではないような場合に、独自の表を作っておいてそこにピボットテーブルのデータを引用するなどの応用が考えられます。例えば、ピボットテーブルの結果を1カ月のカレンダー形式で表示させるとか?
色々応用がありそうですね。。


関連ページ
新しい集計フィールドを追加する


posted by haku1569 at 18:37| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。