[Excel BI]ピボットテーブルで予算と実績を管理する

先日、ある方がPower BIで予算額と実績明細の比較と分析を行うにはどうやればいいか難しい、という話題がありました。ワークシートとセルを扱って計算する Excel ユーザーが Power BIを使い始めるときによく直面する「Excel ならできるのに Power BIだとうまくいかないケース」の一つでもあります。

ひとつ、Power BI の前に慣れ親しんだ Excel の Pivot Table だけで予算と実績を比較するピボットテーブル レポートを作ってみましょう。ワークシート関数は使いません。ただし、ピボットテーブルで「メジャー」を使ってみます。
Excelでなんでそんなに面倒なことするの？と思うかもしれませんが、Power BIとExcelを双方使えるようになるための頭の体操みたいなものです。ある意味 Power BIの世界ではワークシートでセルを自由に編集(セルに数式を埋め込む)することができる「エクセル脳」は結構邪魔になります。とはいえ Excel は使うことが多いので、ちょっとだけデータ処理の考え方を柔軟にしてみてください。

最終的に以下のようなピボットテーブル レポートを作ることを目的とします。

最終的に作成する予算と実績のピボットテーブル

単純なサンプルから目的のピボットテーブル レポートを作ってみます。

予算と明細テーブル

おおよそ、ワークシートで予算と実績を管理する場合、割り当てられた予算額の表(テーブル)があると思います。

予算テーブルのサンプル

もっと細かくても構わないのですが、まずはシンプルなケースで考えましょう。上の予算テーブルからは、事業部Aの前期(2019/1/1)の予算は100、後期(2019/7/1)の予算は50、事業部Bは前期のみ50、事業部Cは前期に10、後期に20の予算が割り当てられています。

これが、縦に部門、横に月があるクロス集計表は分析に使えません。おおよそ人間向けの確認・印刷用にクロス集計表をワークシートに作っていることが多いのですが、クロス集計表はビジネス分析では「結果の表」であって、分析用のデータではないことを肝に銘じてください。(クロス集計表変換方法はこちらを参照してください）

実績明細テーブルは以下のようなものです。明細レコードが追加されていくようなものです。

実績明細テーブルのサンプル

複数のテーブルからピボットテーブル レポートを作成する

SUMIFSなどを使わない、セルに関数式を入力しないで、ピボットテーブル レポートのみで目標の予実対比表を作るには、Excel 2013以降に導入されたリレーションシップとデータモデルを使った複数テーブルからピボットテーブルを作成する機能を使います。ちなみに「範囲」は使いません。必ずテーブルに変換してください。（ここが？と思う方は、過去のブログ投稿などを参照ください）

まずピボットテーブルは「集計が簡単」というのがメリットです。実績明細テーブルからピボットテーブル レポートを作るとすぐに以下のような集計表が作れます。

ピボットテーブル機能で集計表を作成する

同様に予算テーブルも以下のような集計表を作成することができます。

ピボットテーブル レポートによる予算集計表

この２つのピボットテーブルから予算額と実績額を比較できる表の作成ができれば、目的となる表になります。残念ながらピボットテーブルを結合する機能は(私の知る範囲)ありません。予算テーブルと実績テーブルの2つのテーブルから、両方のデータを使ったピボットテーブル レポートを作ります。

まずは、予算合計に対して、実績合計の比較表を作成してみます。複数のテーブルからピボットテーブルを作成する方法は過去に手順をおったブログを投稿していますので、こちらも参照ください。
以下は、総額を比較する手順をアニメーションGIFでとったものです。

予算と実績の合計を比較したピポットテーブルを作る

上のアニメーションGIFの最後のフレームが短いのですが、最終的には以下のピボットテーブル レポートを作成しました。予算の合計は230、それに対して実績の合計は218という表です。

予算・実績の合計の比較表

ここからがちょっとエクセル脳から「考え方」を変える必要があります。
上の表は予算額の合計と、実績額の合計を並べている表ですが、次にこれを「部門別」に分ける、と考えます。事業部Aの予算と実績、事業部Bの予算と実績、事業部Cの予算と実績にわけます。ピボットテーブルの経験のある人は、行ラベルに「事業部」をもってくると出来る！と思うでしょう。このピボットはデータモデルに追加しているので、予算と実績の両方のテーブルが使えます。そのテーブルから「部門」を行に追加すればできそうですが、思った通りの結果にはなりません。

部門を行に追加するも思った結果にならない

実績テーブルの[部門]を選ぶと、実績額は部門別になりますが、予算は全合計のままです。言い換えると実績テーブルの部門を選んで、部門別のフィルターをかけて、実績金額を分けることができたのですが、予算額はフィルターがかかっていない全合計のまま、と言えます。その逆もしかり、です。

実績は部門でフィルタがかかるが、予算はフィルタがかからない

予算を部門でフィルタすると実績は全合計のまま

マスターテーブル/DIMENSIONテーブルの追加

この予算テーブルも明細テーブルも、どちらもFactテーブルです。予算をとった部門や実績をあげている部門は事業部A,B,Cですが、もしかすると事業部Dがあるかもしれません。実績明細で予算をもっている部門がまだ実績がなく明細データがないケースは容易に考えられます。注意しなければならないのは、予算テーブルと実績テーブルの部門をリレーションシップで関連付けるのはまったく意味のないことです。
そこで部門のDimensionテーブル(マスターテーブル)を追加・作成して、各FACTテーブルとリレーションシップを貼り、Dimensionテーブルの項目でフィルターをかけてみます。

追加したシンプルな部門マスター

この部門マスターの部門名と、予算、実績テーブルの部門名でリレーションシップを作成します。

部門マスターと予算・実績テーブルとの間でリレーションシップを作成する

このリレーションシップを作成することで、予算テーブルおよび実績テーブルの集計を、部門マスターの[部門名]でフィルターをかけることが可能になります。

部門マスターの部門名で合計金額をフィルタリングする

次にやりたいのは、月別にする、四半期別に集計する、か、予算と実績から達成率や消化率といった計算結果を出す、のどちらかでしょう。今回は実績/予算で消化・達成率を出す方法をみてみます。ちなみにこれがメジャーをExcelで使うことになります。

ピボットテーブルでメジャーを追加する

Excelのピボットテーブルに詳しい人であれば「ん？メジャー？そこは集計フィールドじゃない？」と思うでしょう。ただ、複数テーブルを処理するためにデータモデルを使うと集計フィールドが使えなくなるのです。(リボンでグレイアウトされます）そのかわりにメジャーを使うことになります。なお、セルに関数式を使って消化・達成率の計算はできますが、その誘惑に負けないようがんばりましょう。（これがエクセル脳の弊害のひとつです）

すでに以下のように部門別の予算・実績テーブルができています。ここに実績合計を予算合計で割った、消化・実績の割合を計算するフィールドを追加します。

ピボットテーブルでメジャーを追加する

メジャーの数式入力欄では "[" を入力すると、計算に利用できるフィールドのリストが表示されます。表示形式もセル範囲指定の書式変更でなく[値フィールドの設定]から書式設定を行ってください。メジャーによる消化・達成率を使って、以下のピボットテーブル レポートが作成できました。

消化・達成率のメジャーを追加した予実管理表

日にち・時間はExcelやPower BIでは重要でかつ難しいエリアです。特にExcelは内部で暗黙的にカレンダーをもっているので、通常ExcelユーザーはカレンダーをDimensionテーブルとして意識していません。
先の部門別のフィルターが予算合計と実績合計に適用するときのように、日付についてもFactテーブル(予算と実績のテーブル）にある日付を使うとうまくいきません。Dimensionテーブルとしてのカレンダーテーブルを作成し、そこからFactテーブルの予算、実績テーブルの日付と関連づけて、カレンダーテーブルのフィールドを使ってフィルターします。
最終的には以下のようなピボットテーブル レポートの作成が可能です。

四半期別にした予実管理ピボットテーブル レポート

ピボットテーブル レポートなので、行ラベルの入れ替えも瞬時に可能です。

行ラベルの順序の入れ替えも容易

#NUMが気に入らない、、、とはいえ分母(予算)が0なので、計算式的にはエラーですので、このあたりは「決め」の問題で対応を考えるしかありませんね。

いかがだったでしょうか。
唯一、計算式を記入したのはメジャーの数式の部分でした。Excelのピボットテーブル機能は、オリジナルのExcelと、将来メインになるだろうPower BIとちょうど中間にあるような感じだと思います。いきなり Power BI 脳になるのも難しく、かといって Excel 脳だとなかなか Power BI の世界が異質に見えるのも確かです。

これらの機能を使ったほうがいいのは、やはりデータが動いている状況でのレポートが欲しい時です。ピボットテーブル レポートなので、実績明細が常に追加される状況では「更新」を押すだけで最新データを使ったレポートに更新されるからです。日々レポートを更新しなければならない人にとっては、これほど素晴らしい機能はありません。
さらにセルに直接数式を入力していないため、実績明細のレコードが増えて予実比較表の行数が増えても、数式を入れ直すことなく表の更新が可能になります。

ここから他の人とレポートを共有しなければならない場合は、Excelではなく Power BI を使うことを検討したほうがいいでしょう。Enterprise BI は常に他の人とのデータおよびレポートの共有が必須ですから、エンタープライズでの BI は Power BI に向かうのは必然だと考えます。

これが少しでもみなさんの参考になれば幸いです。