แม้ว่ารายการฟังก์ชันที่ยาวเหยียดของ Excel จะเป็นหนึ่งในคุณสมบัติที่ดึงดูดใจที่สุดของแอปพลิเคชันสเปรดชีตของ Microsoft แต่ก็มีส่วนสำคัญที่ไม่ค่อยได้ใช้ประโยชน์ที่ปรับปรุงฟังก์ชันเหล่านี้ เครื่องมือหนึ่งที่มักถูกมองข้ามคือการวิเคราะห์แบบ What-If

เครื่องมือ วิเคราะห์ What-If^{(What-If Analysis)}ของ Excel แบ่งออกเป็นสามองค์ประกอบหลัก ส่วนที่กล่าวถึงในที่นี้คือ คุณลักษณะ Goal Seekที่มีประสิทธิภาพซึ่งช่วยให้คุณทำงานย้อนกลับจากฟังก์ชันและกำหนดอินพุตที่จำเป็นเพื่อให้ได้ผลลัพธ์ที่ต้องการจากสูตรในเซลล์ อ่านเพื่อเรียนรู้วิธีใช้เครื่องมือค้นหาเป้าหมายการวิเคราะห์แบบ What-If ของ Excel^{(What-If Analysis Goal Seek)}

ตัวอย่างเครื่องมือหาเป้าหมายของ Excel

สมมติว่าคุณต้องการเงินกู้จำนองเพื่อซื้อบ้าน และคุณกังวลว่าอัตราดอกเบี้ยเงินกู้จะส่งผลต่อการชำระเงินรายปีอย่างไร จำนวนเงินจำนองคือ 100,000 ดอลลาร์และคุณจะชำระคืนเงินกู้ตลอดระยะเวลา 30 ปี

การใช้ฟังก์ชัน PMT^(PMT)ของ Excel ช่วยให้คุณทราบได้ว่าการชำระเงินรายปีจะเป็นอย่างไรหากอัตราดอกเบี้ยเท่ากับ 0% สเปรดชีตน่าจะมีลักษณะดังนี้:

เซลล์ที่ A2 แทนอัตราดอกเบี้ยรายปี เซลล์ที่ B2 คือระยะเวลาของเงินกู้เป็นปี และเซลล์ที่ C2 คือจำนวนเงินกู้จำนอง สูตรใน D2 คือ:

=PMT(A2,B2,C2)

และแสดงถึงการชำระเงินรายปีของการจำนอง 30 ปี 100,000 ดอลลาร์พร้อมดอกเบี้ย 0% ขอให้สังเกต^(Notice)ว่าตัวเลขใน D2 เป็นค่าลบเนื่องจากExcelถือว่าการชำระเงินนั้นเป็นกระแสเงินสดติดลบจากสถานะทางการเงินของคุณ

น่าเสียดายที่ไม่มีผู้ให้กู้จำนองคนใดจะให้คุณยืม 100,000 ดอลลาร์พร้อมดอกเบี้ย 0% สมมติว่า^(Suppose)คุณคิดหาบางอย่างและพบว่าคุณสามารถจ่ายคืน 6,000 ดอลลาร์ต่อปีในการชำระเงินจำนอง ตอนนี้คุณกำลังสงสัยว่าอัตราดอกเบี้ยสูงสุดที่คุณสามารถใช้สำหรับเงินกู้คืออะไร เพื่อให้แน่ใจว่าคุณจะไม่ต้องจ่ายเงินมากกว่า 6,000 ดอลลาร์ต่อปี

หลายคนในสถานการณ์นี้จะเริ่มพิมพ์ตัวเลขในเซลล์ A2 จนกว่าตัวเลขใน D2 จะมีมูลค่าประมาณ 6,000 ดอลลาร์ อย่างไรก็ตาม คุณสามารถทำให้Excelทำงานแทนคุณได้โดยใช้เครื่องมือค้นหาเป้าหมายการวิเคราะห์ แบบ What-If ^{(Analysis Goal Seek)}โดยพื้นฐานแล้ว คุณจะทำให้Excelทำงานย้อนกลับจากผลลัพธ์ใน D4 จนกว่าจะถึงอัตราดอกเบี้ยที่จ่ายสูงสุด 6,000 ดอลลาร์ของคุณ

เริ่มต้นด้วยการคลิกที่แท็บข้อมูล บน ^(Data)Ribbonและค้นหา ปุ่มการ วิเคราะห์แบบ What-If^{(What-If Analysis)}ในส่วนเครื่องมือข้อมูล ^{(Data Tools)}คลิกที่ ปุ่มการ วิเคราะห์แบบ What-If^{(What-If Analysis)}และเลือกเป้าหมายแสวงหา^{(Goal Seek)}จากเมนู

Excel เปิดหน้าต่างเล็ก ๆ และขอให้คุณป้อนตัวแปรเพียงสามตัวเท่านั้น ตัวแปรSet Cellต้องเป็นเซลล์ที่มีสูตร ในตัวอย่างของเรา ที่นี่คือD2 ตัวแปรTo Valueคือจำนวนที่คุณต้องการให้เซลล์ที่D2อยู่ที่ส่วนท้ายของการวิเคราะห์

สำหรับเรา มันคือ-6,000 . โปรดจำไว้ว่าExcelมองว่าการชำระเงินเป็นกระแสเงินสดติดลบ ตัวแปรBy Changing Cellคืออัตราดอกเบี้ยที่คุณต้องการให้ Excel ค้นหาสำหรับคุณ เพื่อให้การจำนอง $100,000 มีค่าใช้จ่ายเพียง $6,000 ต่อปี ดังนั้น ใช้เซลล์A2

คลิก ปุ่ม ตกลง^(OK)และคุณอาจสังเกตเห็นว่าExcelจะกะพริบตัวเลขจำนวนหนึ่งในเซลล์ที่เกี่ยวข้อง จนกระทั่งการวนซ้ำมาบรรจบกันเป็นตัวเลขสุดท้าย ในกรณีของเรา เซลล์ที่ A2 ควรอ่านได้ประมาณ 4.31%

การวิเคราะห์นี้บอกเราว่าเพื่อไม่ให้ใช้จ่ายมากกว่า 6,000 ดอลลาร์ต่อปีในการจำนอง 30 ปีมูลค่า 100,000 ดอลลาร์ คุณต้องค้ำประกันเงินกู้ไม่เกิน 4.31% หากคุณต้องการทำการวิเคราะห์แบบ What-if ต่อไป คุณสามารถลองใช้ตัวเลขและตัวแปรต่างๆ ร่วมกันเพื่อสำรวจตัวเลือกที่คุณมีเมื่อพยายามรักษาอัตราดอกเบี้ยที่ดีในการจำนอง

เครื่องมือ ค้นหาเป้าหมายการวิเคราะห์แบบ What-If^{(What-If Analysis Goal Seek)}ของ Excel เป็นส่วนเสริมที่มีประสิทธิภาพสำหรับฟังก์ชันและสูตรต่างๆ ที่พบในสเปรดชีตทั่วไป เมื่อย้อนกลับจากผลลัพธ์ของสูตรในเซลล์ คุณจะสำรวจตัวแปรต่างๆ ในการคำนวณได้ชัดเจนยิ่งขึ้น

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long list of functions is one of the moѕt enticing featurеs of Microsоft’s spreadsheet application, there a few underutilized gеms that enhance these funсtions. Оne often-overlooked tool is the What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• 2 วิธีในการใช้ฟังก์ชัน Transpose ของ Excel

• วิธีทำความเข้าใจการวิเคราะห์แบบ What-If ใน Microsoft Excel

• วิธีการลบบรรทัดว่างใน Excel

• วิธีปรับแต่งฟังก์ชันการวางของ Word

• วิธีผสานเซลล์ คอลัมน์ และแถวใน Excel

• รักษาการอ้างอิงเซลล์เมื่อคัดลอกสูตรใน Excel

• วิธีการเขียนสูตร IF/คำสั่งใน Excel

• วิธีใช้การอ้างอิงแบบสัมบูรณ์ใน Excel

• Microsoft Outlook จะไม่เปิด? 10 วิธีในการแก้ไข

• วิธีแทรกหลายแถวอย่างรวดเร็วใน Excel

• จัดกึ่งกลางข้อมูลเวิร์กชีตของคุณใน Excel สำหรับการพิมพ์

• การเรียงลำดับข้อมูลแบบหนึ่งคอลัมน์และหลายคอลัมน์ขั้นพื้นฐานในสเปรดชีต Excel

• วิธีแทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีการค้นหาใน Excel

• วิธีแทรก CSV หรือ TSV ลงในแผ่นงาน Excel

• วิธีการคำนวณคะแนน Z ใน Excel

• วิธีการสร้างแผนภูมิวงกลมใน Excel

• วิธีสร้าง Pivot Table แบบง่ายใน Excel