ピボットテーブルにさせる仕事を集計だけにしてあげる
EXCELもピボットテーブルをサクサク組むようになってくると、もう中級者って感じですよね(^o^)
でも、元データからピボットテーブル組んでも、ピボットテーブルって見た目があんまりイケてなかったりして、資料の中でデザインが浮いちゃうな～って悩みを抱えているひともいるんじゃないでしょうか？
ピボットテーブルは、項目の並び順とか塗りつぶしとかの書式設定が、うまくいかないんですよね。
分かります。
そんな時どうするか？ですが。
頑張ってピボットテーブルでやっても良いんですが、本来ピボットテーブルは“集計する専門家”なので、表示させる仕事までさせるのは諦めましょう。ピボットテーブルが可哀相です。
次の図のように、ピボットテーブルにやってもらっていた、「集計」と「表示」の2つの仕事を、別々に分けてあげましょう！
この時、ピボットテーブルで集計したデータを、動的に持ってくるのに活躍するのが”GETPIVOTDATA 関数”というわけです。
GETPIVOTDATA 関数
GETPIVOTDATA(データ フィールド, ピボットテーブル, [フィールド 1, アイテム 1, フィールド 2, アイテム 2], …)
引数
| 引数 | 必須 | 説明 |
|---|---|---|
| data_field | 必須 | 取得するデータを含むピボットテーブル フィールドの名前です。 これは引用符で囲む必要があります。 |
| pivot_table | 必須 | ピボットテーブル内のセル、セル範囲、または名前付きセル範囲を参照します。 この情報は取り出すデータを含むピボットテーブルを確定するために使用されます。 |
| field1, item1, field2, item2… | 任意 | 取得するデータを示す、1 ～ 126 個のフィールド名とアイテム名のペア。 ペアは任意の順序で指定できます。 日付と数値以外の項目のフィールド名と名前は、引用符で囲む必要があります。OLAP ピボットテーブルの場合、アイテムにはディメンションのソース名とアイテムのソース名を含めできます。 OLAP ピボットテーブル用のフィールドとアイテムのペアは次のようになります。”[品目]”,”[品目].[すべての品目].[食品].[調理済み]” |
ヘルプで確認すると、上記のように書かれています。
意味わからんですね(笑)
安心して下さい。GETPIVOTDATA関数は、ピボット範囲外で「=」を入力したあと、ピボットテーブル内のセルを選択すると自動で生成されるようになっています。
次のサンプルで、試してみます。
dataシート、pivotシート、viewシートの3つのシートがある、EXCELファイルです。
dataシートは、ピボットテーブルの元になるデータです。(サンプルなので特に意味のあるデータではないです。)
pivotシートに、元データのピボットテーブルが組んであります。行に「名前」、列に「カテゴリ」、値は「数値」です。
viewシートは、何も入っていません。このシートにピボットテーブルからデータを引っ張ってきたいと思っています。
まずは、自動的にGETPIVOTDATA関数を生成させてみましょう。
pivotシートで、ピボットテーブル範囲外の場所に「=」を入力して、ピボットテーブルの範囲内のセルを選択します。
動画にあるように、自動的にGETPIVOTDATA関数が生成されていることが分かります。
動画では、「稲垣 薫」のカテゴリ「B」のセルを選択したので、数式をEnterで決定すると、その該当数字「214」が表示されています。
では、数式がどうなっているのか確認しましょう！
出来上がった数式は次です。
=GETPIVOTDATA(“記録”,$A$3,”名前”,”稲垣 薫”,”カテゴリ”,”B”)
ちょっとややこしいですが、実際のピボットテーブルと対比させると次のようになっています。
①”記録”は、データフィールドです。今回データ(値)は、”記録”を指定しているので、そのフィールド名である”記録”となっています。もし別の値を指定していたらその値になります。
②”$A$3″は、ピボットテーブルの場所になります。一番左上のセル参照になっていますね
③”名前”は、データを特定するためのフィールド名です。そのすぐ後ろの”稲垣 薫”とセットですね。「名前」が「稲垣 薫」のデータという意味です。
④”カテゴリ”も、データを特定するためのフィールドです。同じくすぐ後ろの”B”とセットになります。
つまり、$A$3のところにある、記録を集計したピボットテーブルの、名前が稲垣薫で、カテゴリがBのデータをもってこい！ということになりますね(^o^)
感の良い人は既に分かったと思いますが、”稲垣薫”の部分と、”B”の部分を参照で設定することで、コピペでババっともって来れるようになります！
それでは、viewシートに名前とカテゴリの順番を並び替えた表の元を作ります。
ここに、pivotテーブルのデータをもってきます。
ベースは、先程自動で作った数式です。数式をコピーしてそのまま、viewシートのB2セルに貼り付けます。
エラーになりますが大丈夫です。
シートが変わったので、”$A$3″の参照セルがおかしくなってますね。
「pivot!$A$3」に変更します。
「214」が表示されましたね。
でもこのままではコピペすると、全部214になってしまいます。。。
名前とカテゴリが、「稲垣 薫」と「B」になっちゃってます。。。
ここを参照設定に変更しましょう。
“稲垣 薫”を$A2に、”B”をB$1に変更します。$がついているのは、コピペした時にずれないようにしています。
完成した数式はこちらです。
=GETPIVOTDATA(“記録”,pivot!$A$3,”名前”,$A2,”カテゴリ”,B$1)
これをコピペしてみましょう！
出来上がったものはこちらです。
元のピボットテーブルがこちらなので、ちゃんと合ってますね(^o^)
このようにすることで、見た目も順番も自由に動かすことが出来ます！
後は、自由に書式設定などをして下さい♪
まとめ
GETPIVOTDATA関数を紹介しました。今回紹介した使用法以外にもアイデア次第でいろいろなことに使えますよ(^o^)
データ・集計・表示、それぞれのシートにそれぞれの仕事をさせる。この考え方を理解することで、様々な処理が可能になります！
しっかり覚えて下さい！！
おまけ
GETPIVOTDATA関数が自動的に生成されないよ(;_;)
って方は、オプションの「GetPivotDataの生成」に✓が入っているか確認して下さい！
デフォルトでは入っていますが、✓が外れていると自動的に生成されません。
コメント