How to use Excel that can't be heard anymore
Excelのゴールシークを使ってみたい！What is Excel's "goal seek"?
Excel is a useful tool for recording and managing money, such as a sales table.For example, I think it is common to create the following table (①) and report on sales.In this way, Excel is useful for recording and analyzing past achievements.
次のような表（①）を作成して、売上の報告をすることはよくあると思いますOn the other hand, in business, we may make a sales strategy and devise sales activities, such as "we must sell 〇〇" to achieve sales of 〇〇 yen.。In such a case, Excel's "Goal Seek" function is useful.Goal Seek is a function that determines the calculation result first and goes backwards the values for obtaining the results.
ゴールシークは、計算結果を先に決めて、その結果を得るための値を逆算する機能です
This time, I will explain how to use this goal sequ function.
Find the target sales number
When selling products with a fixed price of 1,500 yen at a sales event, let's consider the case where the sales target is set to 100,000 yen.30 % of the total sales amount will be deducted as a sales commission, and consumption tax will not be considered.Under these conditions, to achieve your goals, use the goal seek function to see how many products should be sold.
To use a goal sign, you need to prepare a table with a formula in advance.The calculation formula for sales is "unit price x 70 % = sales" considering that the sales commission is 30 % of the total sales fee.Enter the formula "= B3*B4*(1-B5)" (①) in the cell "B6".Enter a fixed value (1,500 yen here) (②) in the "Price" column of the cell "B4", and enter a temporary value (③) in the "sales number" column of the cell "B3".increase.
それぞれ「B6」には「=B3*B4*(1-B5)」（①）、「B4」の「定価」欄には決まった値（②）、「B3」の「販売数」欄には仮の値（③）を入力しておきますNow, set the sales target to 100,000 yen and calculate how many must be sold to achieve the goal.Click [Data] tab (④) → [What-IF analysis] (⑤) → [Goal Seek] (⑥).
［データ］タブ（④）→［What-If分析］（⑤）→［ゴールシーク］（⑥）をクリックThe Gaul Seek dialog box is displayed.Specify the cell "B6" in the [Motorcystic Input Cell] column.Clicking the cell "B6" will automatically enter "$ B $ 6" (⑦).Continue, enter "100000" (⑧) in the [Target value] column.Finally, in the [Change Cell] field, specify the cell "B3" that represents the number of sales you want.When you click the cell "B3", it will be automatically entered with "$ B $ 3" (⑨).When you can enter the data, click [OK] (⑩).
「B6」をクリックすると自動的に「$B$6」（⑦）と入力されます。続けて、［目標値］欄に「100000」（⑧）と入力。「B3」をクリックすると自動的に「$B$3」（⑨）と入力されます。データを入力できたら、［OK］（⑩）をクリックThen, the calculation process is performed.When the process is completed, the calculation result is entered in the cell "B3" in the "sales number" column of the sheet.Here, the cell "B3" is "95.2 ... "(⑪) is displayed.Therefore, it is clear that it is necessary to sell more than 96 to achieve 100,000 yen sales.After confirming the calculation result, click [OK] (⑫) to close the dialog box.
ここでは、セル「B3」に「95.2…」（⑪）と表示されていますね。計算結果を確認したら、［OK］（⑫）をクリックしてダイアログボックスを閉じますBy calculating the formula backwards, we were able to find the number of sales to reach the target sales.This function can be more convenient when the calculation formula becomes complicated.
Let's take advantage of the goal seek
This time, I explained how to use Excel's goal signal function.This example was relatively simple, but you can feel the power of this function as you use a complex formula.Please try it once.