新社会人のためのExcelデータ分析|ゴールシーク編

新社会人のスキル向上
スポンサーリンク

前回に続いてExcelデータ分析についてまとめていきます。

前回はデータテーブルと言って、複数の条件を変えたときに結果がどうなるのかを試算するツールについてご紹介しました。

今回ご紹介するのはゴールシーク

名前から推測できる方もいるでしょうか、ゴールシークは、ある計算結果に必要な値を逆算することができる機能です。

今回はこのゴールシークについて、基本的な使い方と、実践例をみていきましょう。

ゴールシークとは

ゴールシークとは

ゴールシークとは、数値の目標を先に設定し、その結果を得るために必要な条件を逆算するときに使います。

自動計算なので速く正確に逆算結果を出すことができます。

これからご紹介する例は煩雑な計算ではありませんが、複雑な数式を逆算したいときには非常に便利です。

ゴールシークの使い道の例

結果から逆算すると言いましたが、具体的にはどのような場面で使えるのでしょうか。

たとえば、

・予算を超えないためには人件費にどれぐらいかけられるのか?

・赤字にならないためには最低いくつ販売すればいいのか?

・30年で1000万貯めるには毎月いくら貯金すればいいのか?

・借入金を24か月以内に返したい場合は月々の返済額はいくらになるのか?

・税込み価格が1200円のとき、消費税はいくらなのか?

などといったことを自動で計算することができます。

※逆算したい条件が複数ある場合はソルバーというツールを利用します。これについてはまた別記事でまとめています。

使い方|基本編

ゴールシークは前回のデータテーブルよりさらに簡単に使うことができます。

ぜひ基本的な使い方をマスターしていってください!

まず関係式を表す元となる表を作り、その元表をもとにゴールシークを実行する、という流れです。

それでは次項で詳しく見ていきましょう。

関係式を表す表を作る

ゴールシークを使うためには、もとになる計算式が必要です。

単価と売上個数で売上金額が決まる、という例で考えていきましょう。

単価130円、売上個数10個という条件での売上金額は、C4=C2*C3=1300円になります。

※ここは必ず数式を入れます。1300とベタ打ちしただけでは逆算できません。

ゴールシークを実行する

計算式ができたらいよいよゴールシークを使っていきます。

逆算して出したい結果のセル(ここではC4)を選択して、

データWhat-If分析ゴールシーク

からゴールシークを実行します。

ゴールシークをクリックするとボックスが表示されます。

このように、数値入力セル(=出したい結果)、目標値、変化させるセルの3つを入力します。

※ここでは暗算が難しいように目標値は12345円にしてみました。

入力しOKをクリックするとこのように数字が入力されていきます。

目標の売上金額を達成するための売上個数を求めることができました!

使い方|応用編

上の例では逆算の計算式を考えるのもそこまで難しくありませんね。

そこで次はもう少し難しい計算式を考えてみましょう。

ゴールシークは計算式が複雑であれば複雑であるほど便利さを発揮します。

関係式を表す表を作る

ローンの定期支払額を算出する、PMT関数を使ってみたいと思います。

PMT関数は、利率が一定であると仮定し、元利均等の毎月の支払額を計算する関数です。

PMT ( 利率 , 期間 , 現在価値 )

と表します。

今回はこのように、金利2%、期間5年、借入額は1300万として計算式を作りました。

C5=(C2/12,C3*12,-C4)

※C2とC3は年ではなく月に直すため、12で割ったりかけたりしています。

返済額は毎月227861円と出ました。

ゴールシークを実行する

それではゴールシークを実行していきましょう。

今回は毎月の返済額の予算が20万のときに借ることができる金額を求めてみましょう。

数式入力セルが返済額です。目標値を20万にして、返済額を変化させましょう。

OKをクリックすると

1141万ほど借りることができるとわかりました。

このように、関数を利用していて逆算が難しいときにも一瞬で数値を出すことができます。




注意点

ゴールシーク使用時の注意事項を2つ挙げます。

変化させるセルは数式

一つ目は、変化させるセルは必ず数式を入力するということです。

ベタ打ちの数字では計算することができません。

始めの例のように130×10が瞬時に計算できてしまう場合、直接数字を打ち込みたくなるかもしれませんが計算式を入力するよう気を付けてください。

答えが収束しない

二つ目は、答えがなかなか出てこないときです。

答えが複数あり収束しないとき、ゴールシークの計算がなかなか終わらない場合があります。

その場合は

ファイルオプション数式

から、最大反復回数をできるだけ大きく、変化の最大値をできるだけ小さく変更してみましょう。

※デフォルトでは最大反復回数が100、変化の最大値が0.001になっています。

まとめ

今回は、逆算に使えるツール、ゴールシークについてまとめてきました。

ゴールシークは応用がきき、いろいろな場面で使うことのできる機能なので、ぜひ実際に活用してみてください。

前回の繰り返しになりますが、一度自分の手を動かして使い方を確認してみることをお勧めします。

見るのと実際にやるのでは全然違うからです。

私も実際にやってみて、理解しているようで理解できていなかったことに気づくことができました。

ぜひ、簡単な例で実践してみてください。

※私がExcelについて参考にした本はこちら。

実際のビジネスシーンでExcelを使えるようにすることを目指した「実用」に特化した本です。

Excelを用いた業務をする新社会人は必ずもっておきたい一冊です。


新社会人のためのExcelの基本について書いた記事はこちらから。

コメント

  1. […] 新社会人のためのExcelデータ分析|ゴールシーク編前回に続いてExcelデータ… […]

タイトルとURLをコピーしました