エクセルでWhat-If分析って使ってますか？ 自分の仕事では使う場所がないとか、聞いたこともないという人もいるようなので、エクセルのWhat-If分析と、Power BIでのWhat-if分析について説明します。
エクセルのWhat-If分析
エクセルのWhat-If分析は、以下の３つが使用できます。
- ゴールシーク
- データテーブル
- シナリオ
ゴールシーク
指定された計算式の中の値を変えて条件を満たす値を逆算してくれるという機能です。
上記のような状態で、売上の項目には「=C4*C5」という計算式が入っています。そして、売り上げを１６万円にするためには、客数がどのくらい必要かを算出します。
上記のように、ゴールシークのダイアログの中に計算式が書かれたセルと目標値、そして求めたい値となるセルを指定します。
結果は、客数が３２０人必要となりました。
こんなの、ゴールシーク使わなくても計算すればいいじゃん！
おっしゃる通りです！どんな数式でも、最適な変数の値を求める式を作成することは可能です。
しかし、普段、平方根や素因数分解が使われてる数式を書いている人は大丈夫でしょうが、たぶん大多数の人は逆算のために頭を使いたくありません。
例えば、１００万円を１０年預ける場合、複利計算と言って利息にも利息が付くので、以下のような表になります。
C6からC15までのセルには、複利計算の式が入ります。複利計算は利息にも翌年に利息が付いていくので、順々に上段の値を使って計算していく方法もありますが、元本と利率と年数で算出する式があります。
元本 \times (1 + 年間利率) ^ {運用年}
目標額２００万円になるのはいつなのか知るには、このテーブルをずーッと下の方へ広げていけばいいのですが、ゴールシークを使えば広げずに求めることができます。
約70年ですね。
元本を増やして35年で２００万円にしたいなら、年を３５にしておいて、変化させるセルを元本のセルにします。
元本を１４１万円くらいにすれば、年利１％で２００万円になりそうですね。
Power BIのWhat-if
Power BIでは、なぜかiが小文字です。
まず、Power Queryで年数０から３５までのテーブルを作成します。
let
Source =Table.FromList(
{0..35},
Splitter.SplitByNothing(),
type table[年数 = Int32.Type]
)
in
Source
次に元本と利率のパラメータを作成します。
２つのパラメータは、読み込みを有効にしておきます。
パラメータは、通常Power Queryの中で使用するため、レポートで使用できないようになっています。読み込みを有効にするにチェックを入れることで、レポート内やDAXで使用できるようになります。
年数に、以下のメジャーを２つ作ります。
複利 = MAX('元本'[元本]) * ( 1 + [変動利率 値] ) ^ '年数'[年数]
複利 = MAX('元本'[元本]) * (1 + MAX('利率'[利率])) ^ MAX('年数'[年数])
これを折れ線グラフで表示します。
このグラフの利率をWhat-ifで分析します。
以下のようなWhat-ifパラメータのダイアログが表示されるので、変化を見たい値の設定を行います。
画面上に以下のようなスライサーが表示されます。
DAX式を、What-ifパラメータを使用するように変更します。
単利 = MAX('元本'[元本]) + MAX('元本'[元本]) * [変動利率 値] * MAX('年数'[年数])
複利 = MAX('元本'[元本]) * (1 + [変動利率 値]) ^ MAX('年数'[年数])
【注意】What-ifパラメータを使うためには、計算列ではなくメジャーを使う必要があります。計算列を使うと値が固定してしまうため、スライサーで選択された値を取得することができません。
利率を3.0%にした時、以下のように表示されます。
利率を変えてやると、グラフの値も変化します。
GENERATESERIES
What-ifパラメータを作成すると、GENERATESERIESを使ってテーブルが作成されます。
変動利率 = GENERATESERIES(CURRENCY(0.01), CURRENCY(0.2), CURRENCY(0.01))
SELECTEDVALUE
What-ifパラメータを作成すると、先のテーブルに加えて、以下のメジャーが作成されます。
変動利率 値 = SELECTEDVALUE('変動利率'[変動利率])
スライサーの位置を変更して指し示す値をSELECTEDVALUEで取得します。DAX式の中で、このメジャーを指定してやることになります。
複数のWhat-ifパラメータ
What-ifパラメータは１つだけでなく、複数を設定して変化を見ることができます。
以下は、毎年の貯金額と利率のパラメータを作成した例です。
目標値を定数戦で表示させてやると、到達時期がわかりやすくなります。