【Excel】複雑な数式を考えず売上目標から固定費と手数料を考慮した販売数を求める技

変化させる値を間違えないように注意

　［ゴールシーク］ダイアログボックスの項目名が混乱しやすいので注意してください。［数式入力セル］には、現在数式が入力されているセルを指定します。［目標値］は、その数式の目標とする値を直接指定します。ここでは「300000」としました。［変化させるセル］はそのままの意味。ここでは販売数を変化させます。

　ただし、逆算できる数式は1つだけです。ここでは変化させるセルの値を含め、数式で参照する値はすべて入力してある状態から操作します。

売上を求めるセルB5には「=B1*B2*(1-B3)-B4」という数式が入力されています。［データ］タブ（⑥）にある［What-If分析］（⑦）－［ゴールシーク］（⑧）をクリックします

［ゴールシーク］ダイアログボックスの［数式入力セル］の欄を選択して、数式の入力してあるセルB5（⑨）をクリックします

［目標値］を入力します。ここでは「300000」と入力しました（⑩）

［変化させるセル］の欄を選択して、セルB2（販売数）をクリックします（⑪）。［OK］（⑫）をクリックします

［目標値］に合わせて、販売数が変化しました（⑬）。［OK］（⑭）をクリックします

セルB2を選択すると（⑮）、「227.272727272727」と逆算されていることがわかります

　実際の商品に端数はありませんので、販売数の228個以上で、売上目標「300,000円」を達成できることがわかります。

ほかの値を変化させる

　ゴールシークの使い方を理解できたところで、ほかの値を変化させてみましょう。例えば、販売数を200個のまま、売上目標「300,000円」を達成する時の固定費を求めてみます。［数式入力セル］はセルB5、［目標値］は「300000」です。［変化させるセル］は固定費のセルB4となります。

［データ］タブから［What-If分析］－［ゴールシーク］をクリックして、［ゴールシーク］ダイアログボックスを表示しておきます。［数式入力セル］は「$B$5」、［目標値］は「300000」、［変化させるセル］は「$B$4」（⑯）となります。［OK］（⑰）をクリックします

固定費が「52000」と変化しました

ゴールシークを利用する機会は多い

　ゴールシークを使う機会は意外とあります。上記と関連する例として、原価を含めて純利益を求めたり、原価率を計算したりすることもあります。税込み価格から税抜き価格を算出する、ローン残高から月々の返済額や返済期間を求めるなど、逆算に困った時はゴールシークを思い出してください。