ตารางข้อมูลคือช่วงของเซลล์ที่คุณสามารถเปลี่ยนค่าในเซลล์บางเซลล์และหาคำตอบที่แตกต่างกันของปัญหาได้ ตัวอย่างที่ดีของตารางข้อมูลใช้ฟังก์ชัน PMT กับจำนวนเงินกู้และอัตราดอกเบี้ยที่แตกต่างกันเพื่อคำนวณจำนวนเงินที่เหมาะสมในสินเชื่อจำนองบ้าน การทดลองกับค่าที่แตกต่างกันเพื่อสังเกตการเปลี่ยนแปลงที่สอดคล้องกันในผลลัพธ์เป็นงานทั่วไปในการวิเคราะห์ข้อมูล
ภาพรวม
ใน 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
การคำนวณตารางข้อมูล
เมื่อใดก็ตามที่เวิร์กชีตมีการคำนวณใหม่ ตารางข้อมูลใดๆ จะคำนวณใหม่ด้วย ถึงแม้ว่าจะไม่มีการเปลี่ยนแปลงใดๆ กับข้อมูลก็ตาม เมื่อต้องการเพิ่มความเร็วในการคำนวณของเวิร์กชีตที่มีตารางข้อมูล คุณสามารถเปลี่ยนตัวเลือกการคำนวณเพื่อคำนวณเวิร์กชีตใหม่โดยอัตโนมัติ แต่ไม่ใช่ตารางข้อมูล เมื่อต้องการเรียนรู้เพิ่มเติม ให้ดูส่วนเร่งความเร็วการคำนวณในเวิร์กชีตที่มีตารางข้อมูล
สร้างตารางข้อมูลแบบตัวแปรเดียว
ตารางข้อมูลตัวแปรเดียวมีค่าที่ป้อนเข้าไม่ว่าจะในคอลัมน์เดียว (ที่วางแนวคอลัมน์) หรือข้ามแถว (ที่วางแนวแถว) สูตรใดๆ ในตารางข้อมูลตัวแปรเดียวจะต้องอ้างอิงไปยังเซลล์ที่ใช้ป้อนข้อมูลเพียงเซลล์เดียวเท่านั้น
ทำตามขั้นตอนต่อไปนี้:
พิมพ์รายการของค่าที่คุณต้องการแทนที่เซลล์ที่ใช้ ไม่ว่าจะลงมาหนึ่งคอลัมน์หรือข้ามหนึ่งแถว เว้นแถวและคอลัมน์ว่างไว้สองสามรายการที่ด้านใดด้านหนึ่งของค่า
เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้:
ถ้าตารางข้อมูลอยู่ในแนวคอลัมน์ (ค่าตัวแปรของคุณอยู่ในคอลัมน์) ให้พิมพ์สูตรในเซลล์หนึ่งแถวด้านบนและหนึ่งเซลล์ทางด้านขวาของคอลัมน์ของค่า ตารางข้อมูลแบบตัวแปรเดียวนี้เป็นแนวคอลัมน์ และสูตรจะอยู่ในเซลล์ D2
ถ้าคุณต้องการตรวจสอบผลกระทบของค่าต่างๆ ในสูตรอื่น ให้ใส่สูตรเพิ่มเติมในเซลล์ทางด้านขวาของสูตรแรก
ถ้าตารางข้อมูลเป็นแนวแถว (ค่าตัวแปรของคุณอยู่ในแถว) ให้พิมพ์สูตรในเซลล์หนึ่งคอลัมน์ทางด้านซ้ายของค่าแรก และหนึ่งเซลล์ด้านล่างแถวของค่า
ถ้าคุณต้องการตรวจสอบผลกระทบของค่าต่างๆ ในสูตรอื่น ให้ใส่สูตรเพิ่มเติมในเซลล์ด้านล่างสูตรแรก
เลือกช่วงของเซลล์ที่มีสูตรและค่าที่คุณต้องการแทนที่ ในรูปภาพด้านบน ช่วงนี้คือ C2:D5
บนแท็บ ข้อมูล ให้เลือก ตารางข้อมูลการวิเคราะห์>แบบ What-If (ในกลุ่ม เครื่องมือข้อมูล หรือกลุ่ม การพยากรณ์ ของ Excel 2016)
เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้:
ถ้าตารางข้อมูลเป็นคอลัมน์ที่วางแนว ให้ใส่การอ้างอิงเซลล์สําหรับเซลล์ที่ใส่ในเขตข้อมูลเซลล์ที่ใส่คอลัมน์ ในรูปภาพด้านบน เซลล์ที่ใช้คือ B3
ถ้าตารางข้อมูลอยู่ในแนวแถว ให้ใส่การอ้างอิงเซลล์สำหรับเซลล์ที่ใช้ในเขตข้อมูลเซลล์ที่ใช้แถว
หมายเหตุ
หลังจากที่คุณสร้างตารางข้อมูลของคุณ คุณอาจต้องการเปลี่ยนรูปแบบของเซลล์ผลลัพธ์ ในรูปภาพ เซลล์ผลลัพธ์จะถูกจัดรูปแบบเป็นสกุลเงิน
เพิ่มสูตรลงในตารางข้อมูลแบบตัวแปรเดียว
สูตรที่ใช้ในตารางข้อมูลแบบตัวแปรเดียวจะต้องอ้างอิงไปยังเซลล์ที่ใช้เดียวกัน
ให้ทำตามขั้นตอนต่อไปนี้
เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้:
- ถ้าตารางข้อมูลเป็นแนวคอลัมน์ ให้ใส่สูตรใหม่ในเซลล์ว่างทางด้านขวาของสูตรที่มีอยู่ในแถวบนสุดของตารางข้อมูล
- ถ้าตารางข้อมูลเป็นแนวแถว ให้ใส่สูตรใหม่ในเซลล์ว่างด้านล่างสูตรที่มีอยู่ในคอลัมน์แรกของตารางข้อมูล
เลือกช่วงของเซลล์ที่มีตารางข้อมูลและสูตรใหม่
บนแท็บ ข้อมูล ให้เลือกตารางข้อมูลการวิเคราะห์>แบบ What-If (ในกลุ่ม เครื่องมือข้อมูล หรือกลุ่ม การพยากรณ์ ของ Excel 2016)
ให้เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้
- ถ้าตารางข้อมูลเป็นแนวคอลัมน์ ให้ใส่การอ้างอิงเซลล์สำหรับเซลล์ที่ใช้ในกล่องเซลล์ที่ใช้คอลัมน์
- ถ้าตารางข้อมูลเป็นแนวแถว ให้ใส่การอ้างอิงเซลล์สำหรับเซลล์ที่ใช้ในกล่องเซลล์ที่ใช้แถว
สร้างตารางข้อมูลแบบสองตัวแปร
ตารางข้อมูลแบบสองตัวแปรใช้สูตรที่ประกอบด้วยรายการค่าที่ป้อนเข้าสองรายการ สูตรต้องอ้างอิงไปยังเซลล์ที่ใช้ที่แตกต่างกันสองเซลล์
ทำตามขั้นตอนต่อไปนี้:
- ในเซลล์บนเวิร์กชีต ให้ใส่สูตรที่อ้างอิงไปยังเซลล์ที่ใช้สองเซลล์
ในตัวอย่างต่อไปนี้ ซึ่งใส่ค่าเริ่มต้นของสูตรในเซลล์ B3, B4 และ B5 ให้คุณพิมพ์สูตร =PMT(B3/12,B4,-B5) ในเซลล์ C2 - พิมพ์รายการค่าที่ป้อนเข้าหนึ่งรายการในคอลัมน์เดียวกันใต้สูตร
ในกรณีนี้ ให้พิมพ์อัตราดอกเบี้ยที่แตกต่างกันในเซลล์ C3, C4 และ C5 - ใส่รายการที่สองในแถวเดียวกันกับสูตรที่อยู่ทางขวา
พิมพ์ระยะเวลาเงินกู้ (ในหน่วยเดือน) ในเซลล์ D2 และ E2 - เลือกช่วงของเซลล์ที่มีสูตร (C2) ทั้งแถวและคอลัมน์ของค่า (C3:C5 และ D2:E2) และเซลล์ที่คุณต้องการหาค่าจากการคำนวณ (D3:E5)
ในกรณีนี้ ให้เลือกช่วง C2:E5 - บนแท็บ ข้อมูล ในกลุ่ม เครื่องมือข้อมูล หรือกลุ่ม การคาดการณ์ (ใน Excel 2016) ให้เลือก ตารางข้อมูลการวิเคราะห์>แบบ What-If (ในกลุ่ม เครื่องมือข้อมูล หรือกลุ่ม การพยากรณ์ ของ Excel 2016)
- ในเขตข้อมูลเซลล์ที่ใช้แถว ให้ใส่การอ้างอิงไปยังเซลล์ที่ใช้สำหรับค่าที่ป้อนเข้าในแถว
พิมพ์เซลล์ B4 ในกล่องเซลล์ที่ใช้แถว - ในเขตข้อมู เซลล์ที่ใช้คอลัมน์ ให้ใส่การอ้างอิงไปยังเซลล์ที่ใช้สำหรับค่าที่ป้อนเข้าในคอลัมน์
พิมพ์ B3 ในกล่องเซลล์ที่ใช้คอลัมน์ - เลือก ตกลง
ตัวอย่างของตารางข้อมูลแบบสองตัวแปร
ตารางข้อมูลแบบสองตัวแปรสามารถแสดงให้เห็นว่าการรวมกันของอัตราดอกเบี้ยและระยะเวลาของเงินกู้จะมีผลต่อการชำระเงินการจำนองรายเดือนอย่างไร ในรูปภาพที่นี่ เซลล์ C2 มีสูตรการชำระเงิน =PMT(B3/12,B4,-B5) ซึ่งใช้เซลล์ที่ใช้สองเซลล์ คือ B3 และ B4
เร่งความเร็วการคำนวณในเวิร์กชีตที่มีตารางข้อมูล
เมื่อคุณตั้งค่าตัวเลือกการคำนวณนี้ จะไม่มีการคำนวณตารางข้อมูลเกิดขึ้นเมื่อมีการคำนวณใหม่ในทั้งเวิร์กบุ๊ก เมื่อต้องการคำนวณตารางข้อมูลของคุณใหม่ด้วยตนเอง ให้เลือกสูตรของตารางนั้น แล้วกด F9
ทำตามขั้นตอนเหล่านี้เพื่อปรับปรุงประสิทธิภาพการคำนวณ:
เลือกสูตรตัวเลือก>ไฟล์>
ในส่วนตัวเลือกการคำนวณ ให้เลือกอัตโนมัติ
เคล็ดลับ
หรือบนแท็บสูตร ให้เลือกลูกศรบนตัวเลือกการคำนวณ แล้วเลือกอัตโนมัติ
มีอะไรต่อไป
คุณสามารถใช้เครื่องมือ Excel อื่นๆ เพื่อดำเนินการวิเคราะห์แบบ What-if ถ้าคุณมีเป้าหมายเฉพาะหรือชุดข้อมูลตัวแปรที่มีขนาดใหญ่ขึ้น
การค้นหาเป้าหมาย
ถ้าคุณทราบผลลัพธ์ที่คาดว่าจะได้จากสูตร แต่ไม่ทราบค่าที่ป้อนเข้าที่สูตรต้องการเพื่อให้ได้ผลลัพธ์ดังกล่าว ให้ใช้ฟีเจอร์การค้นหาค่าเป้าหมาย ดูบทความใช้การค้นหาค่าเป้าหมายเพื่อค้นหาผลลัพธ์ที่คุณต้องการโดยการปรับค่าที่ป้อน
Excel Solver
คุณสามารถใช้ add-in ของ Excel Solver เพื่อค้นหาค่าที่เหมาะสมที่สุดสำหรับชุดของตัวแปรป้อนเข้า Solver ทำงานกับกลุ่มของเซลล์ (เรียกว่าตัวแปรการตัดสินใจ หรือเซลล์ตัวแปร) ที่ใช้ในการคำนวณสูตรในเซลล์วัตถุประสงค์และข้อจำกัด Solver จะปรับค่าในเซลล์ตัวแปรการตัดสินใจเพื่อให้เป็นไปตามขีดจำกัดของเซลล์ข้อจำกัด และสร้างผลลัพธ์ที่คุณต้องการสำหรับเซลล์วัตถุประสงค์ เรียนรู้เพิ่มเติมในบทความนี้: กำหนดและแก้ไขปัญหาโดยใช้ Solver
ต้องการความช่วยเหลือเพิ่มเติมไหม
คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ