GETPIVOTDATA 関数
【書式】
上図の表からピボットテーブルを作成したとします。
作成したピボットテーブルについて「列を広げたい」「セルの塗りつぶしを消すには」「項目の並び順を変えたい」などなど、ピボットテーブルの見栄えに関する質問をよく耳にしますが、それは、考え方が間違っています。ピボットテーブルを美しく装飾する必要はありません。なぜなら、ピボットテーブルは途中経過として作成するものだからです。もし、レイアウトや装飾に凝った"出力"の表を作成したいのなら、それは別シートで自由に作ってください。そして、ピボットテーブルで集計した結果を、その出力用の表で参照すればいいんです。それがピボットテーブルの正しい使い方です。しかし、ピボットテーブル内のセルを参照するといっても、配置するフィールドや、項目数などによって、集計結果が表示されるセルは変わります。単純に「=C13」などのようにアドレスで参照することはできません。そこで使うのがGETPIVOTDATA関数です。つまり、GETPIVOTDATA関数を知らなかったら、ピボットテーブルを活用することはできないということです。
ここでは、作成したピボットテーブルの集計結果を、下図の表で参照するというケースで解説します。なお、ピボットテーブルは[Sheet2]にあるとします。
GETPIVOTDATA関数の引数は「GETPIVOTDATA(データフィールド,ピボットテーブル,フィールド,アイテム,...)」のように指定しますが、難しく考える必要はありません。作成したピボットテーブル内のセルに対して、ピボットテーブルの外にあるセルから参照しようとすると、自動的にGETPIVOTDATA関数が作成されます。
ちなみに、このピボットテーブルには、次のようにフィールドを配置しています。
フィールドの名称を、覚えておいてください。
さて、自動的に作成されたGETPIVOTDATA関数の引数は、次の意味です。
・GETPIVOTDATA(データフィールド,ピボットテーブル,フィールド,アイテム,...)
引数「データフィールド」には、GETPIVOTDATA関数で参照するデータフィールドに配置したフィールド名を指定します。今回のケースでは、データフィールドに[数値]を配置しています。なので"数値"と指定します。では、もしデータフィールドに複数のフィールドを配置してあったら。
このとき、自動的に作成されるGETPIVOTDATA関数では、Excelが標準で設定したフィールド名が指定されます。
このままでは、別シートから参照するときに煩雑です。なので、フィールドの名前を、参照しやすい文字列に変更するといいでしょう。
さて、説明のために、またシンプルなピボットテーブルに戻します。
・GETPIVOTDATA(データフィールド,ピボットテーブル,フィールド,アイテム,...)
引数「ピボットテーブル」には、参照するピボットテーブルの場所(セル)を指定します。ピボットテーブル内のセルであれば、どこでもいいのですが、一般的にピボットテーブルは左上がセルA3として作成されることが多いので、普通はセルA3です。ただ、ここで注意が必要です。ここまでは、ピボットテーブルを作成した同じワークシート内でGETPIVOTDATA関数を入力しています。だから"同じシートの"「$A$3」となっていますが、このGETPIVOTDATA関数はこの後、別のワークシートで使います。したがって、そのときは「Sheet2!$A$3」のようにシート名をつけなければなりません。
・GETPIVOTDATA(データフィールド,ピボットテーブル,フィールド,アイテム,...)
引数「フィールド」と引数「アイテム」は、「○○フィールドのアイテムが××である」のように、参照したい要件をセットで指定します。今回のケースでは次のように指定しています。
- "名前","松岡" → [名前]フィールドが"松岡"である
- "記号","B" → [記号]フィールドが"B"である
では、このGETPIVOTDATA関数を、出力用の表に代入します。まずは、自動的に作成されるGETPIVOTDATA関数をコピーします。
コピーしたGETPIVOTDATA関数を、出力用のセルに貼り付けます。
先に書いたように、参照するピボットテーブルは別シートにあります。「$A$3」の前に"Sheet2!"のように、ピボットテーブルがあるシート名を付加します。
この貼り付けた関数を別のセルにコピーするのですが、[名前]フィールドに指定したい名前はA列に入力されています。コピーした関数で、参照先がズレないように、列だけを絶対参照にします。
同様に、[記号]フィールドに指定したいアルファベットは1行目に入力されています。こちらは、行だけを絶対参照にします。
あとは、数式をほかのセルにコピーすれば完成です。
この"出力用"の表は、どうぞ自由に装飾してください。