同じピボットテーブルから、条件ごとに複数表に分離して管理する:GETPIVOTDATA関数
前回『GETPIVOTDATA関数の構文解説 – ピボットテーブルからデータを抽出する- DL有 –』の構文を解説しました。
作成済みのピボットテーブルからデータを抽出すると、次のようなメリットがあります:
- 異なる集計方法を同時に適用:
同じデータセットから複数の集計方法を同時に適用し、それぞれの結果を独立した表の表示 - 複数のピボットテーブルのデータを統合:
複数のピボットテーブルからデータを抽出し、一つの包括的な表にまとめる - 非標準形式のレポートを作成:
ピボットテーブルが提供する標準的なレイアウトに収まらない特定の形式や構造のレポートを作成 - 高度なカスタマイズが可能:
特定の条件に基づいて動的に変わるセルのハイライトや特定のデータポイントの強調表示など、
ピボットテーブルの標準機能では対応しにくい高度なカスタマイズが可能
本記事は「異なる集計方法を同時に適用」の具体例として、条件ごとに複数表に分離して管理する方法に関して紹介します。
なお、本記事の末尾に、解答付きダウンロードファイルを用意してあります。
GETPIVOTDATA関数の基本
GETPIVOTDATA関数は、ピボットテーブルから特定の条件にマッチするデータを抽出するExcelの関数です。
具体的には、以下のように使用します:
関数の構文:
=GETPIVOTDATA(データフィールド,ピボットテーブル,[フィールド1], [アイテム1],[フィールド2], [アイテム2],..)
※[]の引数は省略可能
- データフィールド: 抽出したいデータの名前(例: “売上”)
- ピボットテーブル: ピボットテーブル内のセルを参照(例: “A1″)
- [フィールド1]: ピボットテーブルのフィールド名
- [アイテム1]: 抽出したいデータに関連するフィールド内のアイテム
GETPIVOTDATA関数の語源や引数などの詳しく知りたい方は
GETPIVOTDATA関数の構文解説 – ピボットテーブルからデータを抽出する- DL有 –
を拝読ください。
具体的な使用シナリオ
具体的な使用シナリオ
条件ごとに複数の表に分離して管理
GETPIVOTDATA関数を使用することで、
同じデータセットから複数の条件ごとにデータを抽出し、それぞれ独立した表として管理する例を紹介します。
例はわかりやすく、また応用しやすいように簡単にしています
ピボットテーブルされた表
元のデータ表に対して、次のピボットテーブルが容易されているとします。
(元のデータ表が気になる方は、巻末のダウンロードファイルをご覧ください)
GETPIVOTDATAされた表
「ピボットテーブルされた表」に対して、GETPIVOTDATA関数を使用し、
「月別売上の表」「地域別売上の表」という独立した複数表を作成します。
以下のような関数式を使用します:
月別売上の表
月別の売上を抽出します。
下図のように関数式を入力して、関数式をコピーします。
=GETPIVOTDATA(“売上”,$A$3, $G$5, $G6)
- データフィールド: “売上” (抽出したいデータの名前)
- ピボットテーブル: $A$3 (ピボットテーブル内のセルを参照)
- フィールド: “月” に該当するセルとして$G$5を指定(ピボットテーブルのフィールド名)
- アイテム: “1月” or “2月” or “3月”に該当するセルとして、$G6を指定 (抽出したいデータに関連するフィールド内のアイテム)
地域別売上の表
ピボットテーブルから地域別の売上を抽出します。
下図のように関数式を入力して、関数式をコピーします。
=GETPIVOTDATA(“売上”,$A$3, $G$5, $G6)
- データフィールド: “売上” (抽出したいデータの名前)
- ピボットテーブル: $A$3 (ピボットテーブル内のセルを参照)
- フィールド: “地域” に該当するセルとして$G$5を指定(ピボットテーブルのフィールド名)
- アイテム:”東” or “西に該当するセルとして、$G6を指定 (抽出したいデータに関連するフィールド内のアイテム)
最後に
GETPIVOTDATA関数を使用することで、
同じピボットテーブルから条件ごとにデータを抽出し、
それぞれの結果を独立した表として管理することができます。
解答付きサンプルデータ
解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。
参考記事:
Excel関数を操作するための基本概念です。基本概念を理解すると、各関数の理解がより深まります。
構文に関する記事
Excel関数の引数と戻り値は何だっけ?という時に、役立つ記事です。
ダイアログボックスの使い方に関する記事
Excel関数名は何だっけ?VLOOKUP関数の使い方は何だっけ?という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。
参照形式に関する記事
関数式をコピーする時はどうしたらいい? $マークは、どう付けたらいいだろう?
といった悩みをお持ちの方は以下をご覧ください。