GETPIVOTDATA関数で簡単データ抽出!BIツール不要のExcel財務ダッシュボード

ExcelのGETPIVOTDATA関数を活用して、BIツール並みのデータ分析力を身につけましょう。今すぐダウンロードして、実践的なデータ抽出技術を学び始めましょう!

企業の財務報告には、正確で効率的なデータ分析が求められます。
しかし、BIツールの導入が難しい企業も多いでしょう。
そのような企業にとって、ExcelのGETPIVOTDATA関数は非常に有用です。

本記事では、このGETPIVOTDATA関数を使用して、
簡単に疑似ダッシュボードを作成する方法を紹介します。

中小企業の財務報告におけるGETPIVOTDATA関数の活用事例

中小企業の財務チームは、報告プロセスの煩雑さに頭を悩ませていました。

BIツールの導入が難しかったため、Excelで完結する方法を模索しました。

ローンチ段階では、複数のピボットテーブルを管理していました。
しかし、
一貫性を保つのが難しくなり、データの整合性に問題が生じました。
報告の信頼性が低下し、意思決定に支障をきたす可能性がありました。

そこで、GETPIVOTDATA関数を導入し、ピボットテーブルの管理を一新しました。
1つのピボットテーブルで管理でき、これらの課題が解決しました。

本記事では、簡素化した内容を紹介します。
Excelで簡易的な表を作成します。
その後、デザインを追加したり、グラフを記載することも可能です。この企業はグラフが不要な要件でした。


下図は疑似ダッシュボードのイメージです。

GETPIVOTDATA関数を使用した財務ダッシュボードの作成手順

ローデータから財務ダッシュボードを作成する流れです。

  1. ローデータの準備:
    • 売上、費用、利益に関するデータを収集します。
  2. ピボットテーブルの作成:
    • ローデータから1つのピボットテーブルを作成します。
  3. GETPIVOTDATA関数によるダッシュボードの作成:
    • 抽出したデータを使って、GETPIVOTDATA関数を使用して、必要な情報を表示します。


ステップ1:ローデータの準備

対象のローデータを準備します。
ここでは、売上、費用、利益に関するサンプルのローデータです。

全データはダウンロードしたい方は、巻末にダウンロードファイルがあります。

ローデータ ※一部省略

予実1部門売上額費用額利益額
20231月実績営業480000190000290000
20231月実績マーケ290000145000145000
20231月実績開発390000175000215000
202312月実績営業520000205000315000
202312月実績マーケ310000150000160000
202312月実績開発410000190000220000
20241月実績営業490000195000295000
20241月実績マーケ295000148000147000
20241月実績開発395000175000220000
202412月実績営業560000220000340000
202412月実績マーケ320000155000165000
202412月実績開発430000200000230000
20231月予測営業480000190000290000
20231月予測マーケ290000145000145000
20231月予測開発390000175000215000
202312月予測営業520000205000315000
202312月予測マーケ310000150000160000
202312月予測開発410000190000220000
20241月予測営業490000195000295000
20241月予測マーケ295000148000147000
20241月予測開発395000175000220000
202412月予測営業560000220000340000
202412月予測マーケ320000155000165000
202412月予測開発430000200000230000

ステップ2:ピボットテーブルの作成

先程のサンプルデータを基に、Excelで1つのピボットテーブルを作成します。

ピボットテーブルでは、
「部門別の売上額、費用額、利益額の合計」でデータを集計し、
列は「予実」、「年」、「月」でデータを分けます。

ピボットテーブルのフィールドリストは次の通りに設定します。


  • フィルター
    • フィルターは未設定
    • 「予実」
    • 「年」
    • 「月」
    • 「部門」
    • Σ 値
    • 合計/「売上額」
    • 合計/「費用額」
    • 合計/「利益額」


すると、下図のようなピボットテーブルが作成されます。

ステップ3:GETPIVOTDATA関数によるダッシュボードの作成

GETPIVOTDATA関数を使い、必要なデータを抽出し、下図の疑似ダッシュボード(再掲)を作成します。
作成手順は、各タブに記載しています。


前年同期売上実績比較

「部門×年別 売上額」「部門×年別 費用額」「部門×年別 利益額」の3つで構成されている「前年同期売上実績比較」を作成します。
3つのタブに、各集計の手順を明記しています。


  1. F29に、以下の数式を入力します
    これにより、特定の条件に基づいて売上額を取得し、表を作成できます

=GETPIVOTDATA(“売上額”,$A$3,”予実”,$C$27,”部門”,$E29,”年”,F$28)

  1. $A$3以外は、相対参照形式(固定範囲と動的範囲の組み合わせ)です
    これにより、数式をコピーしても各部門や各年のデータを適切に取得できます
    • 各セル参照は以下の通りです
      • $A$3が「ピボットテーブルの基点セル」
      • $C$27が「予実」フィールドのセル
      • $E29が「部門」フィールドのセル
      • F$28が「年」フィールドのセル
  2. F29に入力した数式を、F29からG31までコピーします

数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則|GIF付!数式のコピー方法 で詳しく解説しています。



  1. K29に、以下の数式を入力します。
    これにより、特定の条件に基づいて費用額を取得し、表を作成できます。

=GETPIVOTDATA(“費用額”,$A$3“予実”,$C$27,”部門”,$J29,”年”,K$28)

  1. $A$3以外は、相対参照形式(固定範囲と動的範囲の組み合わせ)です
    これにより、数式をコピーしても各部門や各年のデータを適切に取得できます
    • 各セル参照は以下の通りです
      • $A$3が「ピボットテーブルの基点セル」
      • $C$27が「予実」フィールドのセル
      • $J29が「部門」フィールドのセル
      • K$28が「年」フィールドのセル
  2. K29に入力した数式を、K29からL31までコピーします

数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則|GIF付!数式のコピー方法 で詳しく解説しています。



  1. P29に、以下の数式を入力します。
    これにより、特定の条件に基づいて利益額を取得し、表を作成できます。

=GETPIVOTDATA(“利益額”,$A$3,”予実”,$C$27,”部門”,$O29,”年”,P$28)

  1. $A$3以外は、相対参照形式(固定範囲と動的範囲の組み合わせ)です
    これにより、数式をコピーしても各部門や各年のデータを適切に取得できます
    • 各セル参照は以下の通りです
      • $A$3,「ピボットテーブルの基点セル」
      • $C$27が「予実」フィールドのセル
      • $O29が「部門」フィールドのセル
      • P$28が「年」フィールドのセル

  2. P29に入力した数式を、P29からQ31までコピーします。

数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則|GIF付!数式のコピー方法 で詳しく解説しています。


今年度 月次実績推移

下図のような、「今年度 月次実績推移」を作成します。

作成手順


  1. F38に、以下の数式を入力します。
    これにより、特定の条件に基づいて売上額を取得し、表を作成できます。

=GETPIVOTDATA(“売上額”,$A$3“予実”,$C$35,”部門”,$E38,”年”,$F$36,”月”,F$37)

  1. $A$3以外は、相対参照形式(固定範囲と動的範囲の組み合わせ)です
    これにより、数式をコピーしても各部門や各年のデータを適切に取得できます
    • 各セル参照は以下の通りです
      • $A$3が「ピボットテーブルの基点セル」
      • $C$35が「予実」フィールドのセル
      • $E38が「部門」フィールドのセル
      • $F$36が「年」フィールドのセル
      • F$37が「月」フィールドのセル
  2. E38に入力した数式を、E38からG50までコピーします

数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則|GIF付!数式のコピー方法 で詳しく解説しています。


24年度 予算vs実績比較:

「部門×年別 売上額」「部門×年別 費用額」「部門×年別 利益額」の3つで構成されている「24年度 予算vs実績比較」を作成します。
3つのタブに、各集計の手順を明記しています。


  1. F45に、以下の数式を入力します。
    これにより、特定の条件に基づいて売上額を取得し、表を作成できます。

=GETPIVOTDATA(“売上額”,$A$3“予実”, F$44,”部門”,$E45,”年”,”2024″)

[関数引数]ダイアログボックスの使い方は割愛しました。以下に詳しく記載しています。

  1. $A$3が「ピボットテーブルの基点セル」、
    F$44が「予実」フィールドのセル、
    $E45が「部門」フィールドのセル、
    “2024”が「年」フィールド、
    を指しています。
    F$44$E45は、相対参照形式(固定範囲と動的範囲の組み合わせ)で、
    数式をコピーしても各部門や各月のデータを適切に取得できます。
  2. F45に入力した数式を、F45からG47までコピーします。

数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則|GIF付!数式のコピー方法 で詳しく解説しています。



  1. F45に、以下の数式を入力します。
    これにより、特定の条件に基づいて売上額を取得し、表を作成できます。

=GETPIVOTDATA(“売上額”,$A$3“予実”, F$44,”部門”,$E45,”年”,”2024″)

[関数引数]ダイアログボックスの使い方は割愛しました。以下に詳しく記載しています。

  1. $A$3が「ピボットテーブルの基点セル」、
    F$44が「予実」フィールドのセル、
    $E45が「部門」フィールドのセル、
    “2024”が「年」フィールド、
    を指しています。
    F$44$E45は、相対参照形式(固定範囲と動的範囲の組み合わせ)で、
    数式をコピーしても各部門や各月のデータを適切に取得できます。
  2. F45に入力した数式を、F45からG47までコピーします。

数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則|GIF付!数式のコピー方法 で詳しく解説しています。



  1. P45に、以下の数式を入力します。
    これにより、特定の条件に基づいて利益額を取得し、表を作成できます。

=GETPIVOTDATA(“売上額”,$A$3“予実”,P$44,”部門”,$O45,”年”,”2024″)

[関数引数]ダイアログボックスの使い方は割愛しました。以下に詳しく記載しています。

  1. $A$3が「ピボットテーブルの基点セル」、
    P$44が「予実」フィールドのセル、
    $O45が「部門」フィールドのセル、
    “2024”が「年」フィールド、
    を指しています。
    P$44$O45は、相対参照形式(固定範囲と動的範囲の組み合わせ)で、
    数式をコピーしても各部門や各予実のデータを適切に取得できます。
  2. P45に入力した数式を、P45からQ47までコピーします。

数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則|GIF付!数式のコピー方法 で詳しく解説しています。


SUMIFS関数等でも可能

ローデータからSUMIFS関数を使ってデータを集計する方法もあります。
その方法については、いずれ紹介したいと考えています。

ピボットテーブルを起点とし、GETPIVOTDATA関数を使用した本例は、
スライサーやフィルター等を活用でき、より直感的な操作です。
SUMIFS関数でも、セルと連動させて同様の操作を行えますが、ピボットテーブルを使用した方が直感的に感じられると思います)

最後に

GETPIVOTDATA関数を使用することで、
Excelのピボットテーブルから必要なデータを簡単に抽出し、効率的にデータ分析を行うことができます。
この手法を活用することで、セクションを「企業事例」のように手軽に疑似ダッシュボードを作成し、
ビジネスの意思決定に役立てることができます。ぜひ試してみてください。

解答付きサンプルデータ

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

  1. 予実とは、予測と実績の略称 ↩︎