การคำนวณหลายผลลัพธ์โดยใช้ตารางข้อมูล

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

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

ชนิดของการวิเคราะห์แบบ What-If    

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

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

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

เรียนรู้เพิ่มเติมในบทความ บทนําสู่การวิเคราะห์ What-If

สร้างตารางข้อมูลตัวแปรเดียวหรือสองตัวแปร ขึ้นอยู่กับจํานวนของตัวแปรและสูตรที่คุณต้องการทดสอบ

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

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

ในภาพประกอบต่อไปนี้ เซลล์ D2 มีสูตรการชําระเงิน =PMT(B3/12,B4,-B5) ซึ่งอ้างอิงไปยังเซลล์ข้อมูลเข้า B3

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

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

ในภาพประกอบต่อไปนี้ เซลล์ C2 มีสูตรการชําระเงิน =PMT(B3/12,B4,-B5) ซึ่งใช้เซลล์ป้อนข้อมูลสองเซลล์ คือ B3 และ B4

 

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

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

ตารางข้อมูลตัวแปรเดียวมีค่าที่ป้อนเข้าไม่ว่าจะในคอลัมน์เดียว (ที่วางแนวคอลัมน์) หรือในแถว (แนวแถว) สูตรใดๆ ในตารางข้อมูลตัวแปรเดียวต้องอ้างอิงถึงเพียงหนึ่ง เซลล์ที่ใช้ เท่านั้น

ทำตามขั้นตอนต่อไปนี้:

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

2. เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้:

   • ถ้าตารางข้อมูลอยู่ใน แนวคอลัมน์ (ค่าตัวแปรของคุณอยู่ในคอลัมน์) ให้พิมพ์สูตรในเซลล์ด้านบนหนึ่งแถวและหนึ่งเซลล์ทางด้านขวาของคอลัมน์ของค่า ตารางข้อมูลตัวแปรเดียวนี้เป็นคอลัมน์ที่วางแนว และสูตรจะอยู่ในเซลล์ D2
     
     
     
     ถ้าคุณต้องการตรวจสอบผลกระทบของค่าต่างๆ ในสูตรอื่น ให้ใส่สูตรเพิ่มเติมในเซลล์ทางด้านขวาของสูตรแรก

   • ถ้าตารางข้อมูลจัด แนวเป็นแถว (ค่าตัวแปรของคุณอยู่ในแถว) ให้พิมพ์สูตรในเซลล์หนึ่งคอลัมน์ทางด้านซ้ายของค่าแรก และหนึ่งเซลล์ที่อยู่ใต้แถวของค่า
     
     ถ้าคุณต้องการตรวจสอบผลกระทบของค่าต่างๆ ในสูตรอื่น ให้ใส่สูตรเพิ่มเติมในเซลล์ ด้านล่าง สูตรแรก

3. เลือกช่วงของเซลล์ที่มีสูตรและค่าที่คุณต้องการแทนที่ ในรูปภาพด้านบน ช่วงนี้คือ C2:D5

4. บนแท็บ ข้อมูล ให้คลิก การวิเคราะห์แบบ What-If > ตารางข้อมูล (ในกลุ่ม เครื่องมือข้อมูล หรือกลุ่ม การพยากรณ์ ของ Excel 2016 )

5. เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้:

   • ถ้าตารางข้อมูลเป็นคอลัมน์ที่วางแนว ให้ใส่ การอ้างอิงเซลล์ สําหรับเซลล์ข้อมูลเข้าในเขตข้อมูลเซลล์ที่ใส่คอลัมน์ ในรูปภาพด้านบน เซลล์ที่ใส่คือ B3

   • ถ้าตารางข้อมูลอยู่ในแนวแถว ให้ใส่การอ้างอิงเซลล์สําหรับเซลล์ที่ใส่ในเขตข้อมูลเซลล์ที่ใส่แถว

     หมายเหตุ: หลังจากที่คุณสร้างตารางข้อมูลของคุณ คุณอาจต้องการเปลี่ยนรูปแบบของเซลล์ผลลัพธ์ ในรูปภาพ เซลล์ผลลัพธ์จะถูกจัดรูปแบบเป็นสกุลเงิน

สูตรที่ใช้ในตารางข้อมูลตัวแปรเดียวจะต้องอ้างอิงไปยังเซลล์ที่ใช้ป้อนข้อมูลเดียวกัน

ให้ทำตามขั้นตอนต่อไปนี้

1. เลือกทําอย่างใดอย่างหนึ่งต่อไปนี้:

   • ถ้าตารางข้อมูลอยู่ในแนวคอลัมน์ ให้ใส่สูตรใหม่ในเซลล์ว่างทางด้านขวาของสูตรที่มีอยู่ในแถวบนสุดของตารางข้อมูล

   • ถ้าตารางข้อมูลอยู่ในแนวแถว ให้ใส่สูตรใหม่ในเซลล์ว่างด้านล่างสูตรที่มีอยู่ในคอลัมน์แรกของตารางข้อมูล

2. เลือกช่วงของเซลล์ที่มีตารางข้อมูลและสูตรใหม่

3. บนแท็บ ข้อมูล ให้คลิก การวิเคราะห์แบบ What-If > ตารางข้อมูล (ในกลุ่ม เครื่องมือข้อมูล หรือกลุ่ม การพยากรณ์ ของ Excel 2016 )

4. เลือกทําอย่างใดอย่างหนึ่งต่อไปนี้

   • ถ้าตารางข้อมูลเป็นคอลัมน์ที่วางแนว ให้ใส่การอ้างอิงเซลล์สําหรับเซลล์ข้อมูลเข้าในกล่อง เซลล์ที่ใส่คอลัมน์

   • ถ้าตารางข้อมูลจัดแนวเป็นแถว ให้ใส่การอ้างอิงเซลล์สําหรับเซลล์ที่ใส่ในกล่อง เซลล์ป้อนค่าของแถว

ตารางข้อมูลแบบตัวแปรสองตัวใช้สูตรที่ประกอบด้วยรายการค่าป้อนเข้าสองรายการ สูตรต้องอ้างอิงไปยังเซลล์ป้อนเข้าที่แตกต่างกันสองเซลล์

ทำตามขั้นตอนต่อไปนี้:

1. ในเซลล์บนเวิร์กชีต ให้ใส่สูตรที่อ้างอิงไปยังเซลล์ที่ใส่ข้อมูลสองเซลล์

   ในตัวอย่างต่อไปนี้ ซึ่งใส่ค่าเริ่มต้นของสูตรในเซลล์ B3, B4 และ B5 ให้คุณพิมพ์สูตร =PMT(B3/12,B4,-B5) ในเซลล์ C2

2. พิมพ์รายการค่าป้อนเข้าหนึ่งรายการในคอลัมน์เดียวกัน ใต้สูตร

   ในกรณีนี้ ให้พิมพ์อัตราดอกเบี้ยที่แตกต่างกันในเซลล์ C3, C4 และ C5

3. ใส่รายการที่สองในแถวเดียวกันกับสูตรที่อยู่ทางขวา

   พิมพ์เงื่อนไขการกู้ยืม (ในหน่วยเดือน) ในเซลล์ D2 และ E2

4. เลือกช่วงของเซลล์ที่มีสูตร (C2) ทั้งแถวและคอลัมน์ของค่า (C3:C5 และ D2:E2) และเซลล์ที่คุณต้องการหาค่าจากการคํานวณ (D3:E5)

   ในกรณีนี้ ให้เลือกช่วง C2:E5

5. บนแท็บ ข้อมูล ในกลุ่ม เครื่องมือข้อมูล หรือ กลุ่ม การคาดการณ์ (ใน Excel 2016 ) ให้คลิก การวิเคราะห์แบบ What-If > ตารางข้อมูล (ในกลุ่ม เครื่องมือข้อมูล หรือ กลุ่ม การพยากรณ์ ของ Excel 2016 )

6. ในเขตข้อมูล เซลล์ป้อนค่าแถว ให้ใส่การอ้างอิงไปยังเซลล์ข้อมูลเข้าสําหรับค่าที่ป้อนลงในแถว
   พิมพ์เซลล์ B4 ในกล่องเซลล์ป้อนค่าของแถว

7. ในเขตข้อมูล เซลล์ป้อนเข้าคอลัมน์ ให้ใส่การอ้างอิงไปยังเซลล์ป้อนข้อมูลสําหรับค่าป้อนเข้าในคอลัมน์
   พิมพ์ B3 ในกล่องเซลล์ป้อนเข้าคอลัมน์

8. คลิก ตกลง

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

ตารางข้อมูลแบบแปรผันสองตัวแปรสามารถแสดงให้เห็นว่าการรวมกันของอัตราดอกเบี้ยและเงื่อนไขการกู้จะมีผลต่อการจํานองรายเดือนอย่างไร ในรูปภาพที่นี่ เซลล์ C2 มีสูตรการชําระเงิน =PMT(B3/12,B4,-B5) ซึ่งใช้เซลล์ป้อนข้อมูลสองเซลล์ คือ B3 และ B4

เมื่อคุณตั้งค่าตัวเลือกการคํานวณนี้ จะไม่มีการคํานวณตารางข้อมูลเกิดขึ้นเมื่อมีการคํานวณใหม่ในทั้งเวิร์กบุ๊ก เมื่อต้องการคํานวณตารางข้อมูลของคุณใหม่ด้วยตนเอง ให้เลือกสูตรของตารางนั้น แล้วกด F9

ทําตามขั้นตอนเหล่านี้เพื่อปรับปรุงประสิทธิภาพการคํานวณ:

1. คลิก ตัวเลือก > ไฟล์ > สูตร

2. ในส่วน ตัวเลือกการคํานวณ ภายใต้ คํานวณ ให้คลิก อัตโนมัติยกเว้นตารางข้อมูล

   เคล็ดลับ: หรือ บนแท็บ สูตร ให้คลิกลูกศรบน ตัวเลือกการคํานวณ แล้วคลิก อัตโนมัติยกเว้นตารางข้อมูล (ในกลุ่ม การคํานวณ )

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

การค้นหาเป้าหมาย

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

Excel Solver

คุณสามารถใช้ Add-in Solver ของ Excel เพื่อค้นหาค่าที่เหมาะสมที่สุดสําหรับชุดของตัวแปรป้อนเข้า Solver ทํางานกับกลุ่มของเซลล์ (เรียกว่าตัวแปรการตัดสินใจ หรือเซลล์ตัวแปร) ที่ใช้ในการคํานวณสูตรในเซลล์วัตถุประสงค์และข้อจํากัด Solver จะปรับค่าในเซลล์ตัวแปรการตัดสินเพื่อให้เป็นไปตามขีดจํากัดของเซลล์ข้อจํากัด และสร้างผลลัพธ์ที่คุณต้องการสําหรับเซลล์วัตถุประสงค์ เรียนรู้เพิ่มเติมในบทความนี้: กําหนดและแก้ไขปัญหาโดยใช้ Solver