BIツール不可の事情からExcelで解決！財務ダッシュボード作成の実例紹介
えくせるサイトウ
企業の財務報告には、正確で効率的なデータ分析が求められます。
しかし、BIツールの導入が難しい企業も多いでしょう。
そのような企業にとって、ExcelのGETPIVOTDATA関数は非常に有用です。
本記事では、このGETPIVOTDATA関数を使用して、
簡単に疑似ダッシュボードを作成する方法を紹介します。
目次
中小企業の財務報告におけるGETPIVOTDATA関数の活用事例
中小企業の財務チームは、報告プロセスの煩雑さに頭を悩ませていました。
BIツールの導入が難しかったため、Excelで完結する方法を模索しました。
ローンチ段階では、複数のピボットテーブルを管理していました。
しかし、
一貫性を保つのが難しくなり、データの整合性に問題が生じました。
報告の信頼性が低下し、意思決定に支障をきたす可能性がありました。
そこで、GETPIVOTDATA関数を導入し、ピボットテーブルの管理を一新しました。
1つのピボットテーブルで管理でき、これらの課題が解決しました。
本記事では、簡素化した内容を紹介します。
Excelで簡易的な表を作成します。
その後、デザインを追加したり、グラフを記載することも可能です。この企業はグラフが不要な要件でした。
下図は疑似ダッシュボードのイメージです。
GETPIVOTDATA関数を使用した財務ダッシュボードの作成手順
ローデータから財務ダッシュボードを作成する流れです。
- ローデータの準備:
- 売上、費用、利益に関するデータを収集します。
- ピボットテーブルの作成:
- ローデータから1つのピボットテーブルを作成します。
- GETPIVOTDATA関数によるダッシュボードの作成:
- 抽出したデータを使って、GETPIVOTDATA関数を使用して、必要な情報を表示します。
Code language: HTTP (http)ステップ1：ローデータの準備
対象のローデータを準備します。
ここでは、売上、費用、利益に関するサンプルのローデータです。
全データはダウンロードしたい方は、巻末にダウンロードファイルがあります。
ローデータ ※一部省略
| 年 | 月 | 予実1 | 部門 | 売上額 | 費用額 | 利益額 |
|---|---|---|---|---|---|---|
| 2023 | 1月 | 実績 | 営業 | 480000 | 190000 | 290000 |
| 2023 | 1月 | 実績 | マーケ | 290000 | 145000 | 145000 |
| 2023 | 1月 | 実績 | 開発 | 390000 | 175000 | 215000 |
| … | … | … | … | … | … | … |
| 2023 | 12月 | 実績 | 営業 | 520000 | 205000 | 315000 |
| 2023 | 12月 | 実績 | マーケ | 310000 | 150000 | 160000 |
| 2023 | 12月 | 実績 | 開発 | 410000 | 190000 | 220000 |
| 2024 | 1月 | 実績 | 営業 | 490000 | 195000 | 295000 |
| 2024 | 1月 | 実績 | マーケ | 295000 | 148000 | 147000 |
| 2024 | 1月 | 実績 | 開発 | 395000 | 175000 | 220000 |
| … | … | … | … | … | … | … |
| 2024 | 12月 | 実績 | 営業 | 560000 | 220000 | 340000 |
| 2024 | 12月 | 実績 | マーケ | 320000 | 155000 | 165000 |
| 2024 | 12月 | 実績 | 開発 | 430000 | 200000 | 230000 |
| 2023 | 1月 | 予測 | 営業 | 480000 | 190000 | 290000 |
| 2023 | 1月 | 予測 | マーケ | 290000 | 145000 | 145000 |
| 2023 | 1月 | 予測 | 開発 | 390000 | 175000 | 215000 |
| … | … | … | … | … | … | … |
| 2023 | 12月 | 予測 | 営業 | 520000 | 205000 | 315000 |
| 2023 | 12月 | 予測 | マーケ | 310000 | 150000 | 160000 |
| 2023 | 12月 | 予測 | 開発 | 410000 | 190000 | 220000 |
| 2024 | 1月 | 予測 | 営業 | 490000 | 195000 | 295000 |
| 2024 | 1月 | 予測 | マーケ | 295000 | 148000 | 147000 |
| 2024 | 1月 | 予測 | 開発 | 395000 | 175000 | 220000 |
| … | … | … | … | … | … | … |
| 2024 | 12月 | 予測 | 営業 | 560000 | 220000 | 340000 |
| 2024 | 12月 | 予測 | マーケ | 320000 | 155000 | 165000 |
| 2024 | 12月 | 予測 | 開発 | 430000 | 200000 | 230000 |
ステップ2：ピボットテーブルの作成
先程のサンプルデータを基に、Excelで1つのピボットテーブルを作成します。
ピボットテーブルでは、
「部門別の売上額、費用額、利益額の合計」でデータを集計し、
列は「予実」、「年」、「月」でデータを分けます。
ピボットテーブルのフィールドリストは次の通りに設定します。
- フィルター
- フィルターは未設定
- 列
- 「予実」
- 「年」
- 「月」
- 行
- 「部門」
- Σ 値
- 値
- 合計/「売上額」
- 合計/「費用額」
- 合計/「利益額」
詳細な手順や他の関数の使い方については、以下のリンクをご参照ください。
すると、下図のようなピボットテーブルが作成されます。
ステップ3：GETPIVOTDATA関数によるダッシュボードの作成
GETPIVOTDATA関数を使い、必要なデータを抽出し、下図の疑似ダッシュボード(再掲)を作成します。
作成手順は、各タブに記載しています。
前年同期売上実績比較
「部門×年別 売上額」「部門×年別 費用額」「部門×年別 利益額」の3つで構成されている「前年同期売上実績比較」を作成します。
3つのタブに、各集計の手順を明記しています。
- F29に、以下の数式を入力します
これにより、特定の条件に基づいて売上額を取得し、表を作成できます
=GETPIVOTDATA(“売上額”,$A$3,”予実”,$C$27,”部門”,$E29,”年”,F$28)
- $A$3以外は、相対参照形式（固定範囲と動的範囲の組み合わせ）です
これにより、数式をコピーしても各部門や各年のデータを適切に取得できます- 各セル参照は以下の通りです
- $A$3が「ピボットテーブルの基点セル」
- $C$27が「予実」フィールドのセル
- $E29が「部門」フィールドのセル
- F$28が「年」フィールドのセル
- 各セル参照は以下の通りです
- F29に入力した数式を、F29からG31までコピーします
数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則｜GIF付！数式のコピー方法 で詳しく解説しています。
- K29に、以下の数式を入力します。
これにより、特定の条件に基づいて費用額を取得し、表を作成できます。
=GETPIVOTDATA(“費用額”,$A$3“予実”,$C$27,”部門”,$J29,”年”,K$28)
- $A$3以外は、相対参照形式（固定範囲と動的範囲の組み合わせ）です
これにより、数式をコピーしても各部門や各年のデータを適切に取得できます- 各セル参照は以下の通りです
- $A$3が「ピボットテーブルの基点セル」
- $C$27が「予実」フィールドのセル
- $J29が「部門」フィールドのセル
- K$28が「年」フィールドのセル
- 各セル参照は以下の通りです
- K29に入力した数式を、K29からL31までコピーします
数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則｜GIF付！数式のコピー方法 で詳しく解説しています。
- P29に、以下の数式を入力します。
これにより、特定の条件に基づいて利益額を取得し、表を作成できます。
=GETPIVOTDATA(“利益額”,$A$3,”予実”,$C$27,”部門”,$O29,”年”,P$28)
- $A$3以外は、相対参照形式（固定範囲と動的範囲の組み合わせ）です
これにより、数式をコピーしても各部門や各年のデータを適切に取得できます- 各セル参照は以下の通りです
- $A$3,「ピボットテーブルの基点セル」
- $C$27が「予実」フィールドのセル
- $O29が「部門」フィールドのセル
- P$28が「年」フィールドのセル
- 各セル参照は以下の通りです
- P29に入力した数式を、P29からQ31までコピーします。
数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則｜GIF付！数式のコピー方法 で詳しく解説しています。
今年度 月次実績推移
下図のような、「今年度 月次実績推移」を作成します。
作成手順
- F38に、以下の数式を入力します。
これにより、特定の条件に基づいて売上額を取得し、表を作成できます。
=GETPIVOTDATA(“売上額”,$A$3“予実”,$C$35,”部門”,$E38,”年”,$F$36,”月”,F$37)
- $A$3以外は、相対参照形式（固定範囲と動的範囲の組み合わせ）です
これにより、数式をコピーしても各部門や各年のデータを適切に取得できます- 各セル参照は以下の通りです
- $A$3が「ピボットテーブルの基点セル」
- $C$35が「予実」フィールドのセル
- $E38が「部門」フィールドのセル
- $F$36が「年」フィールドのセル
- F$37が「月」フィールドのセル
- 各セル参照は以下の通りです
- E38に入力した数式を、E38からG50までコピーします
数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則｜GIF付！数式のコピー方法 で詳しく解説しています。
24年度 予算vs実績比較:
「部門×年別 売上額」「部門×年別 費用額」「部門×年別 利益額」の3つで構成されている「24年度 予算vs実績比較」を作成します。
3つのタブに、各集計の手順を明記しています。
- F45に、以下の数式を入力します。
これにより、特定の条件に基づいて売上額を取得し、表を作成できます。
=GETPIVOTDATA(“売上額”,$A$3“予実”, F$44,”部門”,$E45,”年”,”2024″)
[関数引数]ダイアログボックスの使い方は割愛しました。以下に詳しく記載しています。
- $A$3が「ピボットテーブルの基点セル」、
F$44が「予実」フィールドのセル、
$E45が「部門」フィールドのセル、
“2024”が「年」フィールド、
を指しています。
F$44と$E45は、相対参照形式（固定範囲と動的範囲の組み合わせ）で、
数式をコピーしても各部門や各月のデータを適切に取得できます。 - F45に入力した数式を、F45からG47までコピーします。
数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則｜GIF付！数式のコピー方法 で詳しく解説しています。
- F45に、以下の数式を入力します。
これにより、特定の条件に基づいて売上額を取得し、表を作成できます。
=GETPIVOTDATA(“売上額”,$A$3“予実”, F$44,”部門”,$E45,”年”,”2024″)
[関数引数]ダイアログボックスの使い方は割愛しました。以下に詳しく記載しています。
- $A$3が「ピボットテーブルの基点セル」、
F$44が「予実」フィールドのセル、
$E45が「部門」フィールドのセル、
“2024”が「年」フィールド、
を指しています。
F$44と$E45は、相対参照形式（固定範囲と動的範囲の組み合わせ）で、
数式をコピーしても各部門や各月のデータを適切に取得できます。 - F45に入力した数式を、F45からG47までコピーします。
数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則｜GIF付！数式のコピー方法 で詳しく解説しています。
- P45に、以下の数式を入力します。
これにより、特定の条件に基づいて利益額を取得し、表を作成できます。
=GETPIVOTDATA(“売上額”,$A$3“予実”,P$44,”部門”,$O45,”年”,”2024″)
[関数引数]ダイアログボックスの使い方は割愛しました。以下に詳しく記載しています。
- $A$3が「ピボットテーブルの基点セル」、
P$44が「予実」フィールドのセル、
$O45が「部門」フィールドのセル、
“2024”が「年」フィールド、
を指しています。
P$44と$O45は、相対参照形式（固定範囲と動的範囲の組み合わせ）で、
数式をコピーしても各部門や各予実のデータを適切に取得できます。 - P45に入力した数式を、P45からQ47までコピーします。
数式をコピーすることで、同じ数式を繰り返し入力する手間を省き、時間を大幅に節約できます。イメージが付かない方は、数式の原則｜GIF付！数式のコピー方法 で詳しく解説しています。
SUMIFS関数等でも可能
ローデータからSUMIFS関数を使ってデータを集計する方法もあります。
その方法については、いずれ紹介したいと考えています。
ピボットテーブルを起点とし、GETPIVOTDATA関数を使用した本例は、
スライサーやフィルター等を活用でき、より直感的な操作です。
（SUMIFS関数でも、セルと連動させて同様の操作を行えますが、ピボットテーブルを使用した方が直感的に感じられると思います）
最後に
GETPIVOTDATA関数を使用することで、
Excelのピボットテーブルから必要なデータを簡単に抽出し、効率的にデータ分析を行うことができます。
この手法を活用することで、セクションを「企業事例」のように手軽に疑似ダッシュボードを作成し、
ビジネスの意思決定に役立てることができます。ぜひ試してみてください。
解答付きサンプルデータ
解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。
- 予実とは、予測と実績の略称 ↩︎
