การวิเคราะห์แบบ 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.

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

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

ขั้นตอนที่ 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 ในรายการแบบเลื่อนลง