Windows 10 & Office 2016 Excel 2016

ゴールシークを使って目標を達成するための値を求める(数式の逆算)

投稿日:2017年6月23日 更新日:

Excelのゴールシークという機能を使うと、目標値を設定して特定のセルの値を変化させて、目標達成に必要な値を求めることができます。

[データ]タブの[ゴールシーク]

ここでは、例として年間100万円の貯蓄を目標として、毎月いくらの副収入があれば達成できるかというシミュレーションを行います。

ゴールシークのアニメーション

表の作成

表は、下のように作成します。お給料から毎月5,000円の貯金をして、年2回のボーナスでは10万円ずつ貯蓄できるという試算です。

変化させるセルは、副収入の値であるセル[E4]です。目標値となるセルは、セル[G5]です。

表の作成

数式の確認

数式は下のようになっています。

下の画像は、[数式]タブの[ワークシート分析]グループにある[数式の表示]をクリックした状態です。

[数式の表示]を再度クリックすると、数式は非表示になり元の状態になります。

数式の表示

ゴールシークの実行

[データ]タブの[データツール]グループにある[What-If 分析]をクリックしてメニューの中から[ゴールシーク]をクリックします。

ポップヒントには、以下のように記されています。

ゴールシーク

必要な値に対し、適切な入力を探します。

ゴールシークの実行

[ゴールシーク]ダイアログボックスが表示されます。

参考表内をクリックしたり、範囲選択する必要はありません。

  • 数式入力セル]には、目標値を求める数式が入力されているセルを指定します。
  • 目標値]には、目標値を直接入力します。
  • 変化させるセル]には、値を変化させるセルを指定します。

設定を確認したら、[OK]ボタンをクリックします。

ゴールシークダイアログボックス

結果は下のようになります。

年間100万円貯蓄するには、毎月およそ6万円の副収入が必要ということになります。

ゴールシークの結果

ゴールシークがうまくいかない場合は反復計算の設定を調整

数式によっては、ゴールシークは解答が見つからずに何度も計算を繰り返すことがあります。

反復計算の設定をしておくと、計算を中断させたりすることができます。

[Excelのオプション]ダイアログボックスの表示

設定するには、[ファイル]タブをクリックして[オプション]をクリックします。

[Excelのオプション]ダイアログボックスを表示するには、キーボードから[Alt]→[F]→[T]を押してもOKです。

キーボードから[Alt]→[F]→[T]

[Alt]または[F10]キーを使ってキーヒントを表示してリボンへアクセス

[Alt]キーや[F10]キーを使って、キー操作でタブを切り替えたり、目的のコマンドへ移動したりすることができます。 まず、[Alt]キーを ...

続きを見る

計算方法の設定

[Excelのオプション]ダイアログボックスの[数式]タブにある[反復計算を行う]から設定します。

既定では、反復回数が100回に達した時か、誤差が0.001以内になると計算がストップします。

最大反復回数を大きくして、変化の最大値を小さくすると精度が上がりますが、精度を上げると計算に時間がかかることになりますので、少しずつ調整してみてください。

[Excelのオプション]ダイアログボックスの[数式]タブ-[計算方法の設定]

参考ゴールシークについては、以下の記事でも解説しています。

ゴールシーク(逆算シミュレーション)

ゴールシークを使って、損益分岐点を求めることもできます。

損益分岐点をExcelの分析ツール[ゴールシーク]で求めてみよう

損益分岐点とは、売上と費用が等しくなるポイントのことです。 Excelには[ゴールシーク]という機能があり、ボタンをクリックするだけで、解を ...

続きを見る

また、[ゴールシーク]のボタンの位置は、Excelのバージョンによって変わっています。以下の記事を参考にしてください。

ゴールシークのボタンの位置

関連[What-If 分析]のメニューにある[シナリオの登録と管理]と[データテーブル]については、以下の記事で解説しています。

[シナリオの登録と管理]で数式の結果をシミュレーション

Excelには[What-If 分析]ツールというのがあり、複数の異なる値を入力して結果をシミュレーションすることができます。 [データ]タ ...

続きを見る

データテーブルを使って複数の計算結果を同時に表示する

Excelの[データ]タブの[What-If 分析]の中にある[データテーブル]を使うと、複数の計算結果を同時に一覧形式で表示できます。 様 ...

続きを見る

[ソルバー]という分析ツールもあります。

ソルバーを使用して最適値を求める

Excelには、ソルバーという機能があります。 ソルバーとは、数式の計算結果を目標値として設定して、その結果を得るために複数の制約条件を指定 ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Windows 10 & Office 2016 Excel 2016

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.