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

ใน 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 ถ้าคุณมีเป้าหมายเฉพาะหรือชุดข้อมูลตัวแปรที่มีขนาดใหญ่ขึ้น

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

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

Excel Solver

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