ピボットテーブルはデータベースの値を集計する “場所” であり、データ ソースにはない値を自由に間に挟むことはできません。
デザインに凝ったり自由な位置にコメント用のセルを配置したりということはできません。またピボットグラフだと思うようなグラフを作ることができない、なんてこともあるでしょう。
それではピボットグラフではデザインやほかの情報との組み合わせをしたレポートの作成をあきらめなければならないか？といったらそうでもありません。
ピボットテーブルをデータ ソースとレポートの中間に配置する集計場所として利用するのです。
データを関数などで処理するよりも容易でありながら、セル範囲の柔軟性を活かして自由度高くレポートを作成できます。
ここで使うピボットテーブル
ここでは、下図の左側にあるテーブルに蓄積されている商品ごとの出荷数量の記録をデータソースとして、右側にあるピボットテーブルを作成しています。
・[合計 / 数量] フィールド (値フィールド)・・・データソースの [数量] 列の数値を合計した結果を表示
・[商品] フィールド：データソースの [商品] 列の文字列を行ラベルとして表示
・[受注] フィールド：データソースの [受注] 列の文字列をフィルターとして表示
ピボットテーブルに作成しているフィールドは 3 つ。
念のため確認。
値フィールドである [合計 / 数量] フィールドのデータソースは「数量」、集計方法が「合計」であることから、フィールド名が「合計/ 数量」と設定されており、フィールドの上部にラベルとしても表示されています。
ここではフィールド名を変更せずに進めます。
GETPIVOTDATA 関数の概要
ピボットテーブルの集計値をコピーしてほかのセルに貼り付けてしまうと、更新に対応できなくなっていまいます。また、セル参照にしてしまうと更新やフィルターによってセルの位置が変わることに対応できません。
このような理由からも GETPIVOTDATA 関数を活用します。
GETPIVOTDATA 関数はピボットテーブルの値フィールドの集計値を取得 (参照) してセルに表示する関数です。
こんな風に↓ピボットテーブルの値フィールドの集計値をほかのセルに表示できます。なお、データ (集計値) をゲットしてくる関数ですから、行ラベルや列ラベル、フィルターのアイテム (項目) はゲットしてきません。
GETPIVOTDATA 関数の構文は次の通り。
引数で どこにあるピボットテーブルの、どの値フィールドの、どんなデータを取得したいのかを指定します。
=GETPIVOTDATA(フィールド名 , ピボットテーブル , [フィールド1 ,アイテム1] ,…)
同じシートのセル H3 にあるピボットテーブルの [合計 / 数量] フィールドの値のうち、商品がAのデータを取得せよ、という数式はこちら↓。
=GETPIVOTDATA("合計 / 数量" , $H$3 , "商品" , "A")
ピボットテーブルを更新したりフィルターを実行したりしたときにセルの位置が変わる可能性が高いので、”どこ” ではなく “こういう条件の” というように指定します。
ピボットテーブルは名前ではなくピボットテーブルの左上など、更新やフィルターの実行をしても変わる可能性の低いセル番地を指定します。指定したセルが含まれるピボットテーブル、という意味と理解してよいです。
なお、自然に考えると真っ先に「どこにあるピボットテーブルの」がくると思うけれど、1 つ目の引数として値フィールドの名前を指定します。
また、下図では値フィールドの名前が ”数量” となっていますが、値フィールドが 1 つしかないピボットテーブルの場合は「合計 / 数量」ではなくデータソース名を指定しても OK なので意味は同じです。
GETPIVOTDATA 関数の数式作成
構文にしたがってすべて手入力して数式を作ることができますが、GETPIVOTDATA 関数の数式はクリック操作で作成できるため、この関数に関してはすべてを手入力で作ることはほぼしていません。
-
値フィールドの集計値を表示したいセルの先頭に「=」 (等号記号。もちろん半角。) を入力し、そのまま・・・
-
ピボットテーブルの該当するセルをクリックすると、GETPIVOTDATA 関数の数式が作成されます。
値フィールドが 1 つしかないピボットテーブルなのでフィールド名に「合計 / 数量」ではなく「数量」と指定されています。
-
数式を確定すると指定した集計値が表示されていることを確認できます。
この流れが GETPIVOTDATA 関数の数式を作成するときの基本中の基本です。
ちょっと寄り道。
ほとんどないと思うけれど、クリックをしたときに GETPIVOTDATA 関数の数式ではなくセル番地が指定された場合は、数式の作成を中断してピボットテーブルの設定を確認してください。
参照するピボットテーブルを選択して、リボンの [ピボットテーブル分析] タブの [ピボットテーブル] グループの [オプション] をクリックして [GetPivotData の生成] にチェックをつけてください。ここがオフだとセル参照になります。
条件とするフィールドとアイテムの指定
=GETPIVOTDATA("合計 / 数量" , $H$3 , "商品" , "A")
この数式では [商品] フィールドが「A」 と指定されています。いわば条件です。
この「A」の部分はセル参照でも問題ないですし、ここで扱っているように、B も C も D も・・・とした表を作成するのなら、1 つ 1 つをクリックして作成するのも手間なのでセル参照に変更してセルをコピーするべきでしょう。
セル参照に変更したらセルをコピーしてすべての商品の集計値のゲットが完了です。
データソースの変更とピボットテーブルの更新
データソースとなるテーブルにレコードが追加されたり、値が変更されたりしたらピボットテーブルを更新します。
もちろん GETPIVOTDATA 関数の数式の結果も更新されます。
フィルターの活用
ピボットテーブルを使わずに受注が確定しているデータだけを対象に、商品ごとに数量を算出したかったら SUMIF 関数や SUMIFS 関数を使います。
SUMIF 関数や SUMIFS 関数は苦手だな、という場合はピボットテーブルでフィルターをかけっぱなしにしておくのも手です。
フィルターの条件はピボットテーブルを更新してもクリアされないので、更新だけ行えば対象の集計値だけを GETPIVOTDATA 関数を使って表示しておくことができます。
ピボットテーブルと別表のシートを分ける場合
データソースとなるテーブルとピボットテーブル、GETPIVOTDATA 関数によって集計値を表示したい別表、これらを別のシートに配置したいこともあります。なんならそちらのほうが多いでしょう。
GETPIVOTDATA 関数の数式を作るときから別のシートを用意していてクリックして数式を作成したときは、自動的にシート名が追加された状態で指定されます。
最初は同じシートに作っていた、という場合はピボットテーブルのセル番地の前にシート名を追加してください。
手入力するときは「シート1!」の「!」をお忘れなく。
ピボットテーブルの中に上司がコメントを入れたいといっています、どうやったらできますか？と質問をいただくことがあります。
ピボットテーブルには元データにない値は表示できません。仕組み的にも値エリア (値フィールドのある場所) は編集できないのです。
できないものはできないのだから、ピボットテーブルのなかでやろうとせずに別表を用意しましょう。グラフの作成をしたいという方もGETPIVOTDATA 関数を使って是非グラフ用の別表を用意してください。
ピボットテーブルがゴールではなく、関数などを使わずに一次計算をしてくれる場所、と考えると活用の幅が広がると思います。
石田 かのこ
