ข้ามไปที่เนื้อหาหลัก
Office

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

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

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

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

หมายเหตุ: คุณสามารถทำการคำนวณได้เร็วขึ้นด้วยตารางข้อมูลและ Visual Basic for Applications (VBA) สำหรับข้อมูลเพิ่มเติมให้ดูที่ตารางข้อมูลของ Excel If: การคำนวณที่เร็วขึ้นด้วย VBA

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

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

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

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

เรียนรู้เพิ่มเติมในบทความบทนำสู่การวิเคราะห์สิ่งที่เกิดขึ้น

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

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

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

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

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

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

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

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

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

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

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

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

ทำตามขั้นตอนเหล่านี้:

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

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

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

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

      ถ้าคุณต้องการตรวจสอบเอฟเฟ็กต์ของค่าต่างๆบนสูตรอื่นๆให้ใส่สูตรเพิ่มเติมในเซลล์ทางด้านขวาของสูตรแรก

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

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

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

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

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

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

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

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

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

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

  1. ให้ทำอย่างใดอย่างหนึ่งต่อไปนี้:

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

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

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

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

  4. ทำอย่างใดอย่างหนึ่งต่อไปนี้:

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

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

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

ทำตามขั้นตอนเหล่านี้:

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

    ในตัวอย่างต่อไปนี้ซึ่งค่าเริ่มต้นของสูตรจะถูกใส่ในเซลล์ B3, B4 และ B5 คุณสามารถพิมพ์สูตร= การชำระเงิน (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 ) ให้คลิก If การวิเคราะห์ > ตารางข้อมูล(ในกลุ่มเครื่องมือข้อมูลหรือกลุ่มการคาดการณ์ของ Excel 2016 )

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

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

  8. คลิก ตกลง.

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

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

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

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

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

  1. ทำอย่างใดอย่างหนึ่งต่อไปนี้:

    • ใน Excel 2007 ให้คลิกปุ่ม Microsoft Office รูปปุ่ม คลิกตัวเลือก Excelจากนั้นคลิกประเภทสูตร

    • ในเวอร์ชันอื่นๆทั้งหมดให้คลิกตัวเลือก>ไฟล์>สูตร

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

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

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

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

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

Excel Solver

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

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

ตารางข้อมูลคือช่วงของเซลล์ B2: D8 คุณสามารถเปลี่ยนค่าใน B4, จำนวนเงินกู้และการชำระเงินรายเดือนในคอลัมน์ D จะอัปเดตโดยอัตโนมัติ การใช้อัตราดอกเบี้ย๓.๗๕% D2 จะส่งกลับค่าการชำระเงินรายเดือนของ $๑,๐๔๒.๐๑โดยใช้สูตรนี้: = การชำระเงิน (C2/12, $B $๓, $B $๔)

ช่วงของเซลล์ B2:D8 คือตารางข้อมูล

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

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

ในเวิร์กบุ๊กแบบสดนี้เซลล์ D2 มีสูตรการชำระเงิน=การชำระเงิน (C2/12, $B $๓, $B $๔) เซลล์ B3 เป็นเซลล์ตัวแปรที่คุณสามารถเชื่อมต่อระยะเวลาที่แตกต่างกัน (จำนวนของระยะเวลาการชำระเงินรายเดือน) ในเซลล์ D2 ฟังก์ชันการชำระเงินจะเสียบอยู่ในอัตราดอกเบี้ย 3.75%/12, ๓๖๐เดือนและเงินกู้ $๒๒๕,๐๐๐และคำนวณการชำระเงินรายเดือน $๑,๐๔๒.๐๑

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

ในเวิร์กบุ๊กแบบสดนี้เซลล์ C3 ประกอบด้วยสูตรการชำระเงิน=การชำระเงิน ($B $ 3/12, $B $ 2, B4) ซึ่งใช้สองเซลล์ตัวแปร, B2 และ B3 ในเซลล์ C2 ฟังก์ชันการชำระเงินจะเสียบในอัตราดอกเบี้ย 3.875%/12, ๓๖๐เดือนและเงินกู้ $๒๒๕,๐๐๐และคำนวณการชำระเงินรายเดือน $๑,๐๕๘.๐๓

ต้องการความช่วยเหลือหรือไม่

คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community, ขอความช่วยเหลือใน Answers community, หรือแนะนำฟีเจอร์ใหม่หรือการปรับปรุงบน Excel User Voice ได้เสมอ

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

ขยายทักษะ Office ของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×