[DAX] 第７章 予算vs実績比較 - 予算バージョン比較
目標
今回は、拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第７章 予算vs実績比較」のデータを使って、予算の２点バージョン比較を行います。
例えば、年間の予算計画は、会計期間中、四半期ごと月ごとに見直される運用があり、それぞれのバージョン毎にどこが変化したのかを知る必要があります。今回は、目標としてスライサーで任意の２つのバージョンを選ぶと、それぞれの売上予算と差異を表示できるようにします。（テストデータの関係上、会計年度を使って比較します。）
なお、バージョンとなる項目は数値項目であることに注意してください。
とりこむステップ
とりこむステップでは、予算Excelファイルのファイル名である会計年度をバージョンという新項目として追加します。
拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』のP301、仕上げと予算データのとりこみで、会計年度を削除せずに残します。
削除された列１ステップを選択し、数式バーで「"会計年度",」を削除して以下のように変更してください。
変更前
変更後
次に、会計年度をバージョンにリネームしてください。
閉じて読み込む実行後、予算テーブルにバージョンが追加されるのを確認してください。
これでとりこむステップは完了です。
ならべるステップ
ならべるステップでは、予算のバージョン比較をするためのピボットテーブルを作ります。そのとき、バージョンはピボットテーブルの中に置くのではなく、スライサーにする点に注意してください。敢えてピボットテーブルの絞り込みは緩めにしておくのがポイントです。
最初に予算のバージョン比較シートを追加します。
次に、B3セルに移動して挿入メニューからピボットテーブルを追加します。ピボットテーブルを追加するときは、このブックのデータ モデルを使用するが選択されていることに注意してください。
次に、分析（ピボットテーブル分析）メニューで更新→すべて更新を実行し、ピボットテーブルのフィールド→すべて→F_予算を確認して、項目にバージョンが追加されたのを確認します。
次に、ピボットテーブルに項目を以下のように並べます。
列： カレンダー [会計四半期]、カレンダー [月]
（「会計年度」は追加しません）
行： 商品カテゴリー [商品カテゴリー]
値： F_予算 [売上予算]
スライサー： F_予算 [バージョン]
これでならべるステップは完了です。
ただし、この段階ではピボットテーブルの各セルの値はすべての予算バージョンの合計を表示しています。なぜなら、各セルの値にはバージョンに関する絞り込み（フィルター）が効いていないため、すべてのバージョンの売上予算合計となっているためです。
バージョンの絞り込み条件（フィルター・コンテキスト）
| バージョン |
|---|
| 2016 |
| 2017 |
| 2018 |
試しにスライサーで2017を選択して、売上予算を確認してください。
これでバージョン2017の売上予算が表示されました。
バージョンの絞り込み条件（フィルター・コンテキスト）
| バージョン |
|---|
| 2017 |
次に、Ctrlキーを押しながら、2016と2018を選択してください。
これでバージョン2016と2018の合計を表示することができました。
バージョンの絞り込み条件（フィルター・コンテキスト）
| バージョン |
|---|
| 2016 |
| 2018 |
次のかぞえるステップでは、このようにして合計された2つのバージョンをそれぞれ切り出し、別のメジャーで表示します。
かぞえるステップ
今度はメジャーを使って、スライサーで選択された２つの予算のバージョンを比較します。
選択されたスライサーから最大値・最小値を持ってくる
まずは、スライサーで複数選択されたバージョンの最大値と最小値を持ってきます。バージョンは数値項目なのでそれぞれ以下のように定義します。
- 最大値 ⇒ 最新バージョン
- 最小値 ⇒ 前回バージョン
選択された二つのバージョンは数字なのでそれぞれMAX関数、MIN関数で特定することができます。さっそくF_予算テーブルに以下2つのメジャーを作ってピボットテーブルに追加してください。
最新バージョン :
= MAX('F_予算'[バージョン])
前回バージョン :
= MIN('F_予算'[バージョン])
スライサーの選択を解除して数値の中身を確認すると、以下のようになっています。
- 前回バージョン：2016
- 最新バージョン：2018
| バージョン | メジャー | 数式 |
|---|---|---|
| 2016 | 前回バージョン | MIN('F_予算'[バージョン]) |
| 2017 | ||
| 2018 | 最新バージョン | MAX('F_予算'[バージョン]) |
ここで試しに、スライサーの選択を2017と2018にしてください。今度は、最新バージョンと前回バージョンが以下のように変化します。
- 前回バージョン：2017
- 最新バージョン：2018
| バージョン | メジャー | 数式 |
|---|---|---|
| 2017 | 前回バージョン | MIN('F_予算'[バージョン]) |
| 2018 | 最新バージョン | MAX('F_予算'[バージョン]) |
これで選択された複数のスライサーから最新バージョンと前回バージョンの値を持ってくることができました。
選択されたスライサーの最大値・最小値で売上予算を絞り込む
複数選択したバージョンから最大値、最小値を持ってくることができたので、各値セルの中のフィルター・コンテキストを書き換えて、それぞれのバージョンの売上予算を出すメジャーを作ります。
拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』のP251の当期売上累計メジャーの式を参考にして以下2つのメジャーを作り、ピボットテーブルに追加します。
売上予算（前回）:
=CALCULATE (
[売上予算],
FILTER( 'F_予算', 'F_予算'[バージョン] = MIN('F_予算'[バージョン]))
)
売上予算（最新）:
=CALCULATE (
[売上予算],
FILTER( 'F_予算', 'F_予算'[バージョン] = MAX('F_予算'[バージョン]))
)
先ほど作成した前回バージョン、最新バージョンのメジャーは不要なので外しました。適当にスライサーを選択して、前回と最新の値を正しく取得していることを確認してください。なお、3つ以上スライサーが選ばれている場合、その中の最小値と最大値を持ってきます。
本文P251の式との違いは、'F_予算'にALL関数が使われていない点です。
既に、この値セルには四半期、月、商品カテゴリーの絞り込みを受けた売上予算の合計値が入っており、その絞り込み条件（フィルター・コンテキスト）を尊重しているためです。
その合計の中からスライサーの値を使った更なる絞り込みで前回、最新の売上予算を取得しています。
ここまでできたので、仕上げに2つのバージョンの違いである売上予算差異メジャーを追加します。
売上予算差異:
=[売上予算（最新）]-[売上予算（前回）]
最後に、以下4点の変更を加えてかぞえるステップの仕上げです。
- 売上予算メジャーを外す
- Σ 値を「行」に移動
- 支店名のスライサーを追加
- P273の条件付き書式のテクニックを使って、書式を変える
バージョンだけでなく、支店名も組み合わせてインタラクティブに数値を比較することができます。
これでかぞえるステップは完了です。
えがくステップ
最後にグラフを追加して完成です。
ピボットグラフを作るときは本文（P.157）にあるように、ピボットテーブルからではなく、ゼロから作るのがポイントです。
フィールドリストは以下のように選択してください。
ピボットグラフができたら、デザインメニューのグラフの種類の変更で、組み合わせを選んでください。データ系列に使用するグラフの種類と軸を選択してください：は以下の設定にしてください。
次に本文（P161）を参考にして、ピボットグラフをスライサーに結び付けます。（事前に、ピボットテーブル、ピボットグラフには適切な名前を付けておいてください。）
最後にレイアウトを整えてえがくステップは完了です。
これで、スライサーを選択することで好きな2点間の予算バージョン比較ができるようになりました。
