同じピボットテーブルから、条件ごとに複数表に分離して管理する:GETPIVOTDATA関数

同じピボットテーブルから、条件ごとに複数表に分離して管理する:GETPIVOTDATA関数


前回『GETPIVOTDATA関数の構文解説 – ピボットテーブルからデータを抽出する- DL有 –』の構文を解説しました。

作成済みのピボットテーブルからデータを抽出すると、次のようなメリットがあります:

  • 異なる集計方法を同時に適用:
    同じデータセットから複数の集計方法を同時に適用し、それぞれの結果を独立した表の表示
  • 複数のピボットテーブルのデータを統合:
    複数のピボットテーブルからデータを抽出し、一つの包括的な表にまとめる
  • 非標準形式のレポートを作成:
    ピボットテーブルが提供する標準的なレイアウトに収まらない特定の形式や構造のレポートを作成
  • 高度なカスタマイズが可能:
    特定の条件に基づいて動的に変わるセルのハイライトや特定のデータポイントの強調表示など、
    ピボットテーブルの標準機能では対応しにくい高度なカスタマイズが可能

本記事は「異なる集計方法を同時に適用」の具体例として、条件ごとに複数表に分離して管理する方法に関して紹介します。
なお、本記事の末尾に、解答付きダウンロードファイルを用意してあります。

GETPIVOTDATA関数の基本

GETPIVOTDATA関数は、ピボットテーブルから特定の条件にマッチするデータを抽出するExcelの関数です。
具体的には、以下のように使用します:

関数の構文

=GETPIVOTDATA(データフィールド,ピボットテーブル,[フィールド1], [アイテム1],[フィールド2], [アイテム2],..)
※[]の引数は省略可能

  • データフィールド: 抽出したいデータの名前(例: “売上”)
  • ピボットテーブル: ピボットテーブル内のセルを参照(例: “A1″)
  • [フィールド1]: ピボットテーブルのフィールド名
  • [アイテム1]: 抽出したいデータに関連するフィールド内のアイテム

GETPIVOTDATA関数の語源や引数などの詳しく知りたい方は
GETPIVOTDATA関数の構文解説 – ピボットテーブルからデータを抽出する- DL有 –
を拝読ください。

具体的な使用シナリオ

具体的な使用シナリオ

条件ごとに複数の表に分離して管理

GETPIVOTDATA関数を使用することで、
同じデータセットから複数の条件ごとにデータを抽出し、それぞれ独立した表として管理する例を紹介します。

例はわかりやすく、また応用しやすいように簡単にしています

ピボットテーブルされた表

元のデータ表に対して、次のピボットテーブルが容易されているとします。
(元のデータ表が気になる方は、巻末のダウンロードファイルをご覧ください)

同じピボットテーブルから、条件ごとに複数表に分離して管理する: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関数

最後に

GETPIVOTDATA関数を使用することで、
同じピボットテーブルから条件ごとにデータを抽出し、
それぞれの結果を独立した表として管理することができます。

解答付きサンプルデータ

解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。


—————————————
参考記事
—————————————

原理原則に関する記事

Excel関数自体の原理原則に関する記事です。こちらを参照すると、個別の関数の理解がさらに深まるでしょう。

構文に関する記事

Excel関数の引数と戻り値は何だっけ?という時に、役立つ記事です。

数式の原則|Excel関数にはそれぞれ構文がある

Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…

数式の原則|Excel関数の引数とは

Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。

ダイアログボックスの使い方に関する記事

Excel関数名は何だっけ?VLOOKUP関数の使い方は何だっけ?という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。

数式の原則|Excelの検索ダイアログから、関数を探す | データ分析ドットコム

Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。

数式の原則|Excelのダイアログに沿って、関数を使う

Excelのダイアログに使えば、関数の使い方を調べることができます。言わば、関数のガイドラインのようなものです。

参照形式に関する記事

関数式をコピーする時はどうしたらいい?
$マークは、どう付けたらいいだろう?
といった悩みをお持ちの方は以下をご覧ください。

数式の原則|絶対参照/相対参照/複合参照とは

絶対参照/相対参照/複合参照とは何かをどこよりも分かりやすく解説します。

数式の原則|絶対参照/相対参照/複合参照の使い方

「絶対参照」「相対参照」「複合参照」は、どうやって使うのかを紹介します。 ポイントは、「$マークは、直後にあるモノを固定する」です。