การวิเคราะห์แบบ What-If ด้วยตารางข้อมูล

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

ตารางข้อมูลมีสองประเภท -

ตารางข้อมูลตัวแปรเดียว
ตารางข้อมูลสองตัวแปร

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

ตารางข้อมูลตัวแปรเดียว

คุณสามารถใช้ตารางข้อมูลตัวแปรเดียวหากคุณต้องการดูว่าค่าที่แตกต่างกันของตัวแปรหนึ่งในสูตรอย่างน้อยหนึ่งสูตรจะเปลี่ยนผลลัพธ์ของสูตรเหล่านั้นอย่างไร กล่าวอีกนัยหนึ่งด้วยตารางข้อมูลตัวแปรเดียวคุณสามารถกำหนดได้ว่าการเปลี่ยนอินพุตหนึ่งรายการจะเปลี่ยนจำนวนเอาต์พุตอย่างไร คุณจะเข้าใจสิ่งนี้ด้วยความช่วยเหลือของตัวอย่าง

Example

มีวงเงินกู้ 5,000,000 ระยะเวลา 30 ปี คุณต้องการทราบการชำระเงินรายเดือน (EMI) สำหรับอัตราดอกเบี้ยที่หลากหลาย คุณอาจสนใจที่จะทราบจำนวนดอกเบี้ยและเงินต้นที่ต้องจ่ายในปีที่สอง

การวิเคราะห์ด้วยตารางข้อมูลตัวแปรเดียว

การวิเคราะห์ด้วยตารางข้อมูลตัวแปรเดียวต้องทำในสามขั้นตอน -

Step 1 - ตั้งค่าพื้นหลังที่ต้องการ

Step 2 - สร้างตารางข้อมูล

Step 3 - ทำการวิเคราะห์

ให้เราเข้าใจขั้นตอนเหล่านี้โดยละเอียด -

ขั้นตอนที่ 1: ตั้งค่าพื้นหลังที่ต้องการ

สมมติว่าอัตราดอกเบี้ย 12%
แสดงรายการค่าที่ต้องการทั้งหมด
ตั้งชื่อเซลล์ที่มีค่าเพื่อให้สูตรมีชื่อแทนการอ้างอิงเซลล์
ตั้งค่าการคำนวณสำหรับ EMI ดอกเบี้ยสะสมและเงินต้นสะสมด้วยฟังก์ชัน Excel - PMT, CUMIPMT และ CUMPRINC ตามลำดับ

แผ่นงานของคุณควรมีลักษณะดังนี้ -

คุณจะเห็นว่าเซลล์ในคอลัมน์ C ถูกตั้งชื่อตามที่กำหนดในเซลล์ที่เกี่ยวข้องในคอลัมน์ D

ขั้นตอนที่ 2: สร้างตารางข้อมูล

พิมพ์รายการค่าเช่นอัตราดอกเบี้ยที่คุณต้องการแทนที่ในเซลล์อินพุตลงคอลัมน์ E ดังนี้ -

ดังที่คุณสังเกตเห็นมีแถวว่างเหนือค่าอัตราดอกเบี้ย แถวนี้มีไว้สำหรับสูตรที่คุณต้องการใช้

พิมพ์ฟังก์ชันแรก (PMT) ในเซลล์หนึ่งแถวด้านบนและอีกหนึ่งเซลล์ทางด้านขวาของคอลัมน์ค่า พิมพ์ฟังก์ชันอื่น ๆ (CUMIPMT and CUMPRINC) ในเซลล์ทางด้านขวาของฟังก์ชันแรก

ตอนนี้สองแถวเหนือค่าอัตราดอกเบี้ยมีลักษณะดังนี้ -

ตารางข้อมูลมีลักษณะดังที่ระบุด้านล่าง -

ขั้นตอนที่ 3: ทำการวิเคราะห์ด้วยเครื่องมือตารางข้อมูลการวิเคราะห์แบบ What-If

เลือกช่วงของเซลล์ที่มีสูตรและค่าที่คุณต้องการแทนที่เช่นเลือกช่วง - E2: H13
คลิกแท็บ DATA บน Ribbon
คลิก What-if Analysis ในกลุ่ม Data Tools
เลือกตารางข้อมูลในรายการแบบเลื่อนลง

Data Table กล่องโต้ตอบปรากฏขึ้น

คลิกไอคอนในช่องเซลล์อินพุตคอลัมน์
คลิกเซลล์ Interest_Rateซึ่งก็คือ C2

คุณจะเห็นว่าเซลล์อินพุตคอลัมน์ถูกนำมาเป็น $ C $ 2 คลิกตกลง

ตารางข้อมูลเต็มไปด้วยผลลัพธ์ที่คำนวณสำหรับแต่ละค่าอินพุตดังที่แสดงด้านล่าง -

หากคุณสามารถจ่าย EMI ได้ 54,000 คุณสามารถสังเกตได้ว่าอัตราดอกเบี้ย 12.6% เหมาะสำหรับคุณ

ตารางข้อมูลสองตัวแปร

คุณสามารถใช้ตารางข้อมูลสองตัวแปรหากคุณต้องการดูว่าค่าที่แตกต่างกันของตัวแปรสองตัวในสูตรจะเปลี่ยนผลลัพธ์ของสูตรนั้นอย่างไร กล่าวอีกนัยหนึ่งคือด้วยตารางข้อมูลแบบสองตัวแปรคุณสามารถกำหนดได้ว่าการเปลี่ยนอินพุตสองอินพุตจะเปลี่ยนแปลงเอาต์พุตเดียวอย่างไร คุณจะเข้าใจสิ่งนี้ด้วยความช่วยเหลือของตัวอย่าง

Example

มีเงินกู้ 50,000,000. คุณต้องการทราบว่าการรวมกันของอัตราดอกเบี้ยและระยะเวลาเงินกู้ที่แตกต่างกันจะส่งผลต่อการชำระเงินรายเดือน (EMI) อย่างไร

การวิเคราะห์ด้วยตารางข้อมูลสองตัวแปร

การวิเคราะห์ด้วยตารางข้อมูลสองตัวแปรจำเป็นต้องทำในสามขั้นตอน -

Step 1 - ตั้งค่าพื้นหลังที่ต้องการ

Step 2 - สร้างตารางข้อมูล

Step 3 - ทำการวิเคราะห์

ขั้นตอนที่ 1: ตั้งค่าพื้นหลังที่ต้องการ

สมมติว่าอัตราดอกเบี้ย 12%
แสดงรายการค่าที่ต้องการทั้งหมด
ตั้งชื่อเซลล์ที่มีค่าเพื่อให้สูตรมีชื่อแทนการอ้างอิงเซลล์
ตั้งค่าการคำนวณสำหรับ EMI ด้วยฟังก์ชัน Excel - PMT.

แผ่นงานของคุณควรมีลักษณะดังนี้ -

ขั้นตอนที่ 2: สร้างตารางข้อมูล

ประเภท =EMI ในเซลล์ F2

พิมพ์รายการแรกของค่าอินพุตเช่นอัตราดอกเบี้ยลงในคอลัมน์ F โดยเริ่มจากเซลล์ด้านล่างสูตรเช่น F3
พิมพ์รายการที่สองของค่าอินพุต ได้แก่ จำนวนการชำระเงินในแถวที่ 2 โดยเริ่มจากเซลล์ทางด้านขวาของสูตรเช่น G2

ตารางข้อมูลมีลักษณะดังนี้ -

ทำการวิเคราะห์ด้วยตารางข้อมูลเครื่องมือวิเคราะห์ What-If

เลือกช่วงของเซลล์ที่มีสูตรและชุดค่าสองชุดที่คุณต้องการแทนที่เช่นเลือกช่วง - F2: L13
คลิกแท็บ DATA บน Ribbon
คลิก What-if Analysis ในกลุ่ม Data Tools
เลือกตารางข้อมูลจากรายการแบบเลื่อนลง

กล่องโต้ตอบตารางข้อมูลจะปรากฏขึ้น

คลิกไอคอนในกล่องใส่เซลล์แถว
คลิกเซลล์ NPERซึ่งก็คือ C3
อีกครั้งคลิกไอคอนในกล่องเซลล์ป้อนข้อมูลแถว
จากนั้นคลิกไอคอนในกล่องเซลล์อินพุตคอลัมน์
คลิกเซลล์ Interest_Rate ซึ่งก็คือ C2
อีกครั้งให้คลิกไอคอนในกล่องเซลล์อินพุตคอลัมน์

คุณจะเห็นว่าเซลล์อินพุตแถวถูกนำมาเป็น $ C $ 3 และเซลล์อินพุตคอลัมน์ถูกนำมาเป็น $ C $ 2 คลิกตกลง

ตารางข้อมูลจะเต็มไปด้วยผลลัพธ์จากการคำนวณสำหรับการรวมกันของค่าอินพุตสองค่า -

หากคุณสามารถจ่าย EMI ได้ 54,000 อัตราดอกเบี้ย 12.2% และ 288 EMI เหมาะสำหรับคุณ ซึ่งหมายความว่าระยะเวลาของเงินกู้จะเป็น 24 ปี

การคำนวณตารางข้อมูล

ตารางข้อมูลจะคำนวณใหม่ทุกครั้งที่มีการคำนวณเวิร์กชีตที่มีอยู่แม้ว่าจะไม่มีการเปลี่ยนแปลงก็ตาม ในการเพิ่มความเร็วในการคำนวณในแผ่นงานที่มีตารางข้อมูลคุณต้องเปลี่ยนตัวเลือกการคำนวณเป็นAutomatically Recalculate แผ่นงาน แต่ไม่ใช่ตารางข้อมูลตามที่ระบุในส่วนถัดไป

เร่งการคำนวณในแผ่นงาน

คุณสามารถเร่งการคำนวณในแผ่นงานที่มีตารางข้อมูลได้สองวิธี -

จากตัวเลือกของ Excel
จาก Ribbon

จากตัวเลือกของ Excel

คลิกแท็บไฟล์บน Ribbon
เลือกตัวเลือกจากรายการในบานหน้าต่างด้านซ้าย

กล่องโต้ตอบตัวเลือกของ Excel จะปรากฏขึ้น

จากบานหน้าต่างด้านซ้ายเลือก Formulas.
เลือกตัวเลือก Automatic except for data tables ภายใต้ Workbook Calculationในส่วนตัวเลือกการคำนวณ คลิกตกลง

จาก Ribbon

คลิกแท็บ FORMULAS บน Ribbon
คลิก Calculation Options ในกลุ่มการคำนวณ
เลือก Automatic Except for Data Tables ในรายการแบบเลื่อนลง