What-If Analysis ใน Excel: Goal Seek, Scenario Manager และ Data Tables

สิ่งที่ต้องระบุมีสามอย่าง:

• Set cell: เซลล์ที่มีสูตรของคุณ (เอาต์พุต)
• To value: ผลลัพธ์เป้าหมายที่ต้องการ
• By changing cell: เซลล์อินพุตที่อนุญาตให้ Excel ปรับค่าได้

มาดูการใช้งานจริงกัน 

คุณทำธุรกิจขายสมุดโน้ต รายได้ปัจจุบันคือ $25,000 แต่เป้าหมายคือ $50,000 ราคาขายต่อหน่วยคงที่ $25 ดังนั้นต้องรู้ว่าต้องขายกี่หน่วยจึงจะถึงเป้า 

ทำตามนี้:

1. ไปที่ Data > What-If Analysis > Goal Seek

2. ในช่อง Set cell ให้คลิกเซลล์ B4 (เซลล์สูตรรายได้)

3. ในช่อง To value พิมพ์ 50000

4. ใน By changing cell ให้เลือก B2 (Units Sold)

5. คลิก OK

Goal Seek จะปรับ B2 จาก 1,000 เป็น 2,000 หน่วย — จำนวนพอดีที่จะทำให้รายได้ $50,000 ที่ราคา $25 ต่อหน่วย

ใช้ Goal Seek เพื่อให้ได้ผลลัพธ์เฉพาะเจาะจง ภาพโดยผู้เขียน

วิธีใช้ Scenario Manager ใน Excel

Scenario Manager เป็นเครื่องมือ What-If Analysis ที่ให้บันทึกและสลับระหว่างชุดค่าอินพุตหลายชุดในสเปรดชีตเดียว แต่ละชุดเรียกว่า “ฉากทัศน์” 

สามารถสร้างได้ตามต้องการและสลับดูผลได้ทันทีเพื่อเปรียบเทียบ 

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

ต้องระบุสามอย่าง:

• Scenario name: ชื่อระบุแต่ละชุดสมมติฐาน
• Changing cells: เซลล์อินพุตที่ต่างกันในแต่ละฉากทัศน์
• Values: ตัวเลขเฉพาะสำหรับแต่ละฉากทัศน์

การสร้างฉากทัศน์

สมมติว่ากำลังวางแผนยอดขายรายไตรมาสของธุรกิจสมุดโน้ต

กำไรปัจจุบันคือ $15,000 ต้องการจำลองสามฉากทัศน์: กรณีดีที่สุด แย่ที่สุด และคาดหวัง 

วิธีสร้างฉากทัศน์มีดังนี้:

1. ไปที่ Data > What-If Analysis > Scenario Manager

2. ในกล่องโต้ตอบ Scenario Manager คลิก Add

3. ใน Scenario name พิมพ์ Best Case.

4. ใน Changing cells ให้เลือก B2:B4.

5. จะมีกล่องโต้ตอบ Scenario Value ปรากฏขึ้น ในช่องที่ให้มา ใส่ค่าเหล่านี้:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. คลิก OK. 

ทำซ้ำขั้นตอนนี้กับอีกสองฉากทัศน์โดยใช้ค่าด้านล่าง:

สร้างฉากทัศน์สามกรณี ภาพโดยผู้เขียน

หากต้องการสลับฉากทัศน์ ให้เลือกจากรายการแล้วคลิก Show Excel จะสลับค่าให้ทันที และกำไรจะอัปเดตตาม

คลิก Show เพื่อดูค่าของแต่ละฉากทัศน์ ภาพโดยผู้เขียน

แก้ไขฉากทัศน์

เลือกฉากทัศน์ใดก็ได้จากรายการแล้วคลิก Edit สามารถเปลี่ยนชื่อ เปลี่ยนช่วงเซลล์ที่ครอบคลุม หรืออัปเดตค่าได้ จะไม่มีอะไรเปลี่ยนในสเปรดชีตจนกว่าจะคลิก Show

สร้างรายงานฉากทัศน์

Scenario Manager ยังสามารถสร้างรายงานเปรียบเทียบฉากทัศน์ทั้งหมดแบบเคียงข้างกันได้ด้วย 

ขั้นตอนการสร้างรายงานฉากทัศน์:

1. ใน Scenario Manager ให้คลิก Summary

2. ใน Scenario Summary ให้เลือก Scenario Summary

3. ใน Result cells ให้เลือก B7 (เซลล์กำไร)

4. คลิก OK

Excel จะสร้างตารางสรุปในชีตใหม่ แสดงทั้งสามฉากทัศน์แบบเคียงข้างกัน

สร้างรายงานสรุปฉากทัศน์ ภาพโดยผู้เขียน

วิธีใช้ Data Tables ใน Excel

Data Tables ช่วยทำการคำนวณซ้ำ ๆ ในช่วงค่าของอินพุตโดยอัตโนมัติ แทนที่จะเปลี่ยนอินพุตเอง บันทึกเอาต์พุต แล้วทำซ้ำ แค่กำหนดช่วงครั้งเดียว จากนั้น Excel จะเติมผลลัพธ์ทั้งหมดให้ในครั้งเดียว

ต้องระบุสามอย่าง:

• Input range: ค่าที่ต้องการทดสอบ
• Row/column input cell: เซลล์ในสูตรที่ค่าดังกล่าวจะเข้าไปแทน
• Formula cell: เซลล์ผลลัพธ์ที่ตารางยึดเป็นแกนกลาง

Data Table แบบตัวแปรเดียว

Data Table แบบตัวแปรเดียวใช้ทดสอบว่าการเปลี่ยนอินพุตหนึ่งตัวส่งผลต่อผลลัพธ์ของสูตรอย่างไร

ตัวอย่างเช่น มีสูตรค่างวดเงินกู้และอยากดูว่าค่างวดรายเดือนเปลี่ยนอย่างไรเมื่ออัตราดอกเบี้ยตั้งแต่ 5% ถึง 10%

วิธีสร้าง Data Table แบบตัวแปรเดียว:

1. ตั้งค่าชุดข้อมูล สิ่งสำคัญ: ในชุดข้อมูลของฉัน ที่เซลล์ E1, ตรงเหนืออัตราดอกเบี้ยตัวแรก ไม่ใช่ถัดจากหัวคอลัมน์ ฉันใส่ =B5 ถ้าปล่อย E1 ว่างไว้ หรือใส่อ้างอิงสูตรไว้ที่เซลล์อื่น ตารางข้อมูลจะรันได้แต่จะคืนค่าเป็นศูนย์ทั้งหมดแทนที่จะเป็นค่างวด

สร้าง Data Table แบบตัวแปรเดียว ภาพโดยผู้เขียน

2. จากนั้นเลือกช่วงทั้งหมด D1:E9 (รวม E1)

3. ไปที่ Data > What-If Analysis > Data Table

4. ปล่อยช่อง Row input cell ว่างไว้

5. ใน Column input cell ให้เลือก B4 (อัตราดอกเบี้ย)

6. คลิก OK.

Excel จะเติมตารางด้วยผลลัพธ์ที่คำนวณได้สำหรับค่าทุกค่า สังเกตว่าค่างวดไม่ได้เพิ่มขึ้นเท่า ๆ กัน ช่วง 3% เป็น 4% เพิ่ม $49 แต่ช่วง 9% เป็น 10% เพิ่ม $61 การเปลี่ยนอัตราดอกเบี้ยยิ่งสูง ผลกระทบยิ่งแรง

ผลลัพธ์ที่คำนวณได้สำหรับแต่ละค่าอินพุต ภาพโดยผู้เขียน

Data Table แบบสองตัวแปร

Data Table แบบสองตัวแปรใช้ทดสอบว่าการเปลี่ยนอินพุตสองตัวพร้อมกันส่งผลต่อผลลัพธ์ของสูตรอย่างไร

เช่น โมเดลการตั้งราคาอาจขึ้นกับทั้งราคาสินค้าและจำนวนหน่วยที่ขาย Data Table แบบสองตัวแปรจะแสดงกำไรตามระดับราคาและปริมาณขายต่าง ๆ ได้ในมุมมองเดียว

ก่อนอื่น ตั้งค่าชุดข้อมูลให้พร้อม 

ดูชุดข้อมูลที่ฉันใช้:

• ที่เซลล์ D1 ใส่ =B5 นี่คืออ้างอิงสูตร และต้องอยู่มุมซ้ายบนของกริด ไม่ใช่ถัดจากป้ายกำกับ

• ตามแถวที่ 1 เริ่มจาก E1 ใส่ค่าจำนวนหน่วยที่ขายที่ต้องการทดสอบ: 500, 1000, 1500, 2000

• ตามคอลัมน์ D เริ่มจาก D2 ใส่ระดับราคาที่ต้องการทดสอบ: $20, $25, $30, $35

ตั้งค่าชุดข้อมูลสำหรับ Data Table แบบสองตัวแปร ภาพโดยผู้เขียน

วิธีสร้าง Data Table แบบสองตัวแปร:

1. เลือกช่วงทั้งหมด D1:H5.

2. ไปที่ Data > What-If Analysis > Data Table

3. ในช่อง Row input cell ให้เลือก B3 (จำนวนหน่วยที่ขาย)

4. ในช่อง Column input cell ให้เลือก B2 (ราคาต่อหน่วย)

5. คลิก OK.

Excel จะเติมค่ากำไรสำหรับทุกคู่ของราคาและจำนวนหน่วยที่ขาย

ผลลัพธ์ที่คำนวณได้สำหรับค่าอินพุต ภาพโดยผู้เขียน

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

เปรียบเทียบ Goal Seek, Scenario Manager และ Data Tables

Goal Seek, Scenario Manager และ Data Tables ต่างก็เป็นส่วนหนึ่งของชุดเครื่องมือ What-If Analysis ใน Excel แต่แก้ปัญหาคนละแบบ 

นี่คือการเปรียบเทียบแบบย่อระหว่างกัน 

What-If Analysis กับ Solver ต่างกันอย่างไร

ทั้ง What-If Analysis และ Solver ช่วยให้สำรวจผลลัพธ์ต่าง ๆ ใน Excel ได้ แต่แก้ปัญหาคนละประเภท

• What-If Analysis คือการทดสอบสมมติฐาน เปลี่ยนอินพุตหนึ่งตัวหรือมากกว่าและดูว่าผลลัพธ์เปลี่ยนอย่างไร เป้าหมายคือทำความเข้าใจผลลัพธ์ที่เป็นไปได้ ไม่ใช่หาค่าที่ดีที่สุด
• Solver ทำงานต่างออกไป กำหนดเป้าหมาย เช่น เพิ่มกำไรสูงสุดหรือลดต้นทุนต่ำสุด แล้วมันจะค้นหาชุดอินพุตที่ตอบโจทย์นั้นที่สุด ไม่ได้ทดสอบสมมติฐานทีละตัว แต่แก้หา “ค่าสุดเหมาะสม”

ตัวอย่างเช่น ผู้กู้เงินอาจใช้ Goal Seek เพื่อหาวงเงินกู้ที่ทำให้ค่างวดรายเดือน $500 ส่วนบริษัทผู้ผลิตอาจใช้ Solver เพื่อหาส่วนผสมการผลิตที่ให้กำไรสูงสุดโดยยังอยู่ในข้อจำกัดแรงงานและวัสดุ 

หมายเหตุ: Solver ไม่ได้เป็นส่วนหนึ่งของเครื่องมือ What-If Analysis มาตรฐาน แต่เป็นส่วนเสริม (add-in) แยกต่างหาก ใน Excel หลายเวอร์ชันต้องไปเปิดใช้งานเองที่ Excel Options ก่อนจึงจะเห็นบนแท็บ Data

แนวทางปฏิบัติที่ดีที่สุดสำหรับ What-If Analysis

แนวทางที่ควรทำเมื่อใช้งาน What-If Analysis มีดังนี้: 

1. แยกสมมติฐานออกจากสูตร

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

ตัวอย่างเช่น สูตรนี้อัปเดตได้ยาก:

=(50*1000)-10000

แต่เวอร์ชันนี้ดูแลง่ายกว่ามาก:

=(B2*B3)-B4

เมื่อสมมติฐานเปลี่ยน ก็อัปเดตแค่เซลล์อินพุต แทนที่ต้องไปแก้สูตรทั่วทั้งเวิร์กบุ๊ก

2. ตั้งชื่อฉากทัศน์ให้ชัดเจน

ชื่อฉากทัศน์ควรบอกผู้อ่านได้ทันทีว่าแทนอะไร “Scenario 1,” “Scenario 2,” “Scenario 3” จะไม่ช่วยอะไรในอีกหกเดือนข้างหน้า ตั้งชื่อให้สื่อความหมายจริง เช่น “Best Case: เติบโต 15%” 

3. ตรวจสอบความถูกต้องของอินพุต

ความน่าเชื่อถือของ What-If Analysis เทียบเท่าความน่าเชื่อถือของอินพุต ก่อนรันวิเคราะห์ ควรตรวจว่า:

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

การพยากรณ์รายได้โดยอิงยอดขายเพิ่ม 500% อาจถูกต้องทางเทคนิค แต่ก็ไร้ประโยชน์ต่อการตัดสินใจ

4. บันทึกสมมติฐาน

โมเดลที่วันนี้เข้าใจได้ชัดเจน ไม่ได้แปลว่าอีกสามเดือนข้างหน้าจะเข้าใจได้เหมือนเดิม 

โน้ตสั้น ๆ ข้างแต่ละสมมติฐาน แม้แค่ “อิงจากผลจริงไตรมาส 3” ก็ช่วยลดการเดาในภายหลัง การบันทึกช่วยให้คนอื่นเข้าใจที่มาของตัวเลข และทำให้อัปเดตในอนาคตง่ายขึ้น

ปัญหาที่พบบ่อยกับ What-If Analysis

ปัญหา What-If Analysis ส่วนใหญ่มาจากการตั้งค่าเวิร์กชีต ไม่ใช่ตัวเครื่องมือเอง หาก Goal Seek, Scenario Manager หรือ Data Tables ให้ผลไม่คาดคิด ให้ตรวจจุดเสี่ยงไม่กี่อย่างต่อไปนี้ ก็มักจะแก้ได้

1. สูตรไม่อัปเดต 

เปลี่ยนค่าอินพุตแล้วไม่เกิดอะไรขึ้น ปกติแปลว่าโหมดคำนวณของ Excel ถูกสลับเป็น Manual ไว้ 

วิธีแก้: ไปที่ Formulas > Calculation Options และตรวจให้ตั้งเป็น Automatic หากตั้งอยู่แล้ว ให้กด F9 เพื่อบังคับคำนวณใหม่

2. อ้างอิงเซลล์ผิด

เครื่องมือ What-If Analysis พึ่งพาการอ้างอิงเซลล์ที่ถูกต้อง หากเลือกเซลล์ผิด ผลลัพธ์จะผิดแม้ Excel จะคำนวณสำเร็จก็ตาม 

เช่น Goal Seek อาจไปเปลี่ยนเซลล์อินพุตผิดตัว Data Table อาจอ้างอิงแถวหรือคอลัมน์อินพุตผิด Scenario Manager อาจเก็บค่าให้กับเซลล์ผิด

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

3. เข้าใจข้อจำกัดของ Goal Seek ไม่ถูกต้อง

หลายคนคาดหวังว่า Goal Seek จะช่วยแก้ปัญหาที่มีตัวแปรเปลี่ยนหลายตัวได้ ซึ่งทำไม่ได้ Goal Seek ปรับได้ครั้งละอินพุตเดียวเพื่อให้ถึงค่าเป้าหมาย 

เช่น Goal Seek หาปริมาณขายที่ทำให้กำไร $15,000 ได้ แต่ไม่สามารถปรับปริมาณขาย ราคา และต้นทุนพร้อมกันให้ถึงเป้านั้นได้

วิธีแก้: ใช้ Goal Seek เมื่อมีตัวแปรต้องเปลี่ยนเพียงตัวเดียว ใช้ Scenario Manager หรือ Solver เมื่อมีหลายตัวแปรเกี่ยวข้อง 

4. โมเดลฉากทัศน์ที่ซับซ้อนเกินไป

ตามทฤษฎี Scenario Manager รองรับได้สูงสุด 32 เซลล์ที่เปลี่ยนต่อฉากทัศน์ แต่ทำได้ไม่ได้แปลว่าควรทำ 

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

ข้อคิดส่งท้าย 

ทุกสเปรดชีตย่อมมีสมมติฐานซ่อนอยู่ ไม่ว่าจะเป็นอัตราการเติบโต ราคา หรืออัตราดอกเบี้ย

What-If Analysis ไม่ได้ลบสมมติฐานเหล่านั้นออกไป แต่มันแสดงให้เห็นว่าจะเกิดอะไรขึ้นถ้าสมมติฐานนั้นผิด ซึ่งก็คือแก่นของการตัดสินใจที่ดีส่วนใหญ่

ถ้าเพิ่งเริ่มต้น อย่าเริ่มจากโมเดลที่มีห้าตัวแปรและซ้อนฉากทัศน์สามชั้น เริ่มจากเล็ก ๆ ก่อน เช่น อินพุตหนึ่งตัว เอาต์พุตหนึ่งตัว ใช้ Goal Seek หนึ่งครั้ง และเรียนรู้ว่าค่าอินพุตกับผลลัพธ์เคลื่อนไหวสัมพันธ์กันอย่างไร เรื่องซับซ้อนค่อยตามมาภายหลังเมื่อพื้นฐานแน่นแล้ว