MOS Excel「ゴールシーク」|目標の売上から必要な数量を逆算する使い方

記事
学び
MOS Excel「ゴールシーク」|目標の売上から必要な数量を逆算する使い方

「利益を10万円にするには、あと何個売ればいいか」を手計算で試すのは大変です。MOS Excelで問われるゴールシークは、数式の結果が目標の値になるように、入力する数値を逆算してくれる機能です。数式を入れるセルと、変化させるセルを取り違えないことがポイントです。なお、この記事の問題例は公式問題ではなく、操作仕様に沿って作ったオリジナルの練習問題です。

ゴールシークは「結果から逆算する」

ゴールシークは、「データ」タブの「What-If分析」から使います。設定は3つだけです。数式が入ったセルを指定する「数式入力セル」、その結果として欲しい値を入れる「目標値」、逆算のために変化させる入力セルを指定する「変化させるセル」です。この3点セットが試験の中心です。

たとえば単価500円の商品で売上を50,000円にしたい場合、売上の数式が入ったセルを数式入力セルに、50,000を目標値に、数量のセルを変化させるセルに指定します。実行すると、数量が100に変わり、売上が目標どおりになります。「変化させるセル」に数式のセルを指定するという説明は誤りで、変化させるのは入力値のセルです。

ソルバーやシナリオとの違い

同じWhat-If分析には、シナリオとソルバーもあります。シナリオは、複数の入力値の組み合わせに名前を付けて切り替える機能で、逆算はしません。ソルバーは、複数のセルを変化させたり、条件を付けたりできる上位の仕組みで、アドインとして読み込む場面が問われます。

見分け方の型は、変化させるセルの数と条件の有無です。1つのセルを逆算するだけならゴールシーク、値の組み合わせを保存して切り替えるならシナリオ、複数のセルや制約条件まで扱うならソルバーです。試験では、この3つを取り違えた選択肢が並びます。

うまくいかないときの典型

ゴールシークが解を見つけられない典型は、数式入力セルに数式が入っていない場合です。数式ではなく直接入力した数字のセルを指定しても、変化させるセルとの計算のつながりがないため、逆算できません。また、変化させるセルが数式の計算に含まれていない場合も、結果は変わりません。

もう1つは、目標値に到達できない組み合わせです。たとえば割引率を変化させても売上目標に届かない構造なら、解なしとして終わります。「ゴールシークはどんな目標でも必ず解を表示する」という言い切りは誤りです。あくまで、指定した1セルを動かす範囲で近づける機能です。

確認問題

問1.ゴールシークで指定する3つの項目は何か。
答え:数式入力セル、目標値、変化させるセルです。変化させるセルは入力値のセルを指定します。

問2.複数の入力値の組み合わせに名前を付けて切り替える機能はどれか。
答え:シナリオです。ゴールシークは1つのセルを逆算する機能で、組み合わせの保存はしません。

数式のつながりを先に確かめる

実行する前に、数式入力セルの計算に変化させるセルが含まれているかを確かめる習慣が大切です。つながりがなければ何度実行しても結果は変わらず、試験時間だけが過ぎてしまいます。

まとめ

MOS Excelのゴールシーク問題は、「結果から1セルを逆算する」「設定は3点セット」「シナリオは組み合わせの保存、ソルバーは複数セルと制約」の3点を押さえると安定します。

※この記事はMOS Excelの試験範囲に沿って作成した解説で、公式問題ではありません。MOSの試験は随時試験でも受けられます。対策にはココナラで販売中のMOS関連の直前ノートもご活用ください。
サービス数100万件のスキルマーケット、あなたにぴったりのサービスを探す