การวิเคราะห์แบบ What-if เป็นแนวทางที่มีประสิทธิภาพในการสำรวจความไม่แน่นอนและทดสอบสถานการณ์ต่างๆ ก่อนทำการตัดสินใจที่สำคัญ ช่วยให้บรรลุผลทางธุรกิจหรือการวิจัย เช่น “จะเกิดอะไรขึ้นหากราคาสูงขึ้น” หรือ “เราจะได้กำไรเท่าไรหากต้นทุนลดลง” เครื่องมือวิเคราะห์ What-If ของ Excel ช่วยให้คุณสำรวจความเป็นไปได้ต่างๆ โดยไม่ต้องเขียนสูตรใหม่
ในบทช่วยสอนนี้ เราจะแสดงเทคนิคการวิเคราะห์แบบ What-If สามเทคนิคเพื่อคาดการณ์และจำลองสถานการณ์ใน Excel
เราจะสำรวจเทคนิคการวิเคราะห์แบบ What-If ที่จำเป็นสามประการ:
- แสวงหาเป้าหมาย: ทำงานย้อนหลังเพื่อค้นหาอินพุตที่จำเป็นสำหรับผลลัพธ์เฉพาะ
- ตารางข้อมูล: วิเคราะห์ว่าตัวแปรหนึ่งหรือสองตัวส่งผลต่อผลลัพธ์ของคุณอย่างไร
- ตัวจัดการสถานการณ์: บันทึกและเปรียบเทียบชุดสมมติฐานหลายชุด
1. การค้นหาเป้าหมาย:ค้นหาข้อมูลที่ป้อนเพื่อให้ได้ผลลัพธ์ที่ต้องการ
Goal Seek เหมาะสำหรับโมเดลวิศวกรรมย้อนกลับ โดยจะปรับค่าอินพุตเดียวเพื่อให้ได้เอาต์พุตเฉพาะ ช่วยตอบคำถามเช่น “ฉันต้องใช้ค่าอินพุตเท่าใดเพื่อให้ได้ผลลัพธ์ที่ต้องการ”
Goal Seek ใช้การวนซ้ำเพื่อเปลี่ยนหนึ่งเซลล์ (อินพุต) จนกว่าเซลล์ที่ขึ้นกับสูตร (เอาต์พุต) จะถึงค่าที่คุณต้องการ เหมาะอย่างยิ่งสำหรับการเพิ่มประสิทธิภาพง่ายๆ แต่จำกัดไว้เพียงตัวแปรเดียว
เมื่อใดควรใช้การค้นหาเป้าหมาย:
- กำหนดปริมาณการขายที่จำเป็นเพื่อให้บรรลุเป้าหมายกำไร
- คำนวณอัตราดอกเบี้ยที่จำเป็นเพื่อให้เป็นไปตามข้อจำกัดในการชำระคืนเงินกู้
- ค้นหาจุดคุ้มทุนของผลิตภัณฑ์หรือบริการ
- ระบุคะแนนที่จำเป็นสำหรับการสอบปลายภาคเพื่อให้ได้เกรดที่แน่นอน
มาดูกันว่า:“เราต้องขายกี่หน่วยจึงจะได้กำไร 20,000 ดอลลาร์”
ขั้นตอน:
- ตั้งค่าโมเดลของคุณใน Excel (เช่น ป้อนรายละเอียดผลิตภัณฑ์และสูตรกำไร)
- แทรกสูตรต่อไปนี้เพื่อคำนวณกำไร
- ไปที่ข้อมูล แท็บ>> เลือก การวิเคราะห์แบบ What-If>> เลือก แสวงหาเป้าหมาย
- ในกล่องโต้ตอบ:
- ตั้งค่าเซลล์: เลือกเซลล์ผลลัพธ์ (เช่น E2 สูตรกำไร)
- มูลค่า: ป้อนผลลัพธ์เป้าหมาย (เช่น เป้าหมายกำไร 20,000)
- โดยการเปลี่ยนเซลล์: เลือกเซลล์อินพุตที่จะปรับ (เช่น B2 , หน่วยที่ขาย)
- คลิก ตกลง
- Excel วนซ้ำโดยอัตโนมัติเพื่อปรับมูลค่าการขายจนกว่าจะถึงกำไรเป้าหมาย
ข้อมูลเชิงลึก: ใช้การค้นหาเป้าหมายเมื่อสูตรของคุณทำงานย้อนกลับจากเป้าหมายที่ทราบ (เช่น จุดคุ้มทุนหรือยอดขายเป้าหมาย)
- ข้อดี: รวดเร็วและตรงไปตรงมาสำหรับปัญหาตัวแปรเดียว
- ข้อเสีย: ไม่รองรับหลายตัวแปร อาจไม่มาบรรจบกันหากแบบจำลองนั้นซับซ้อน
2. ตารางข้อมูล:ดูว่าตัวแปรหนึ่งหรือสองตัวส่งผลต่อผลลัพธ์อย่างไร
ตารางข้อมูลช่วยให้คุณเห็นว่าการเปลี่ยนแปลงตัวแปรอินพุตหนึ่งหรือสองตัวส่งผลต่อผลลัพธ์ของคุณอย่างไร โดยแสดงความเป็นไปได้ทั้งหมดในตารางเดียว ซึ่งจะสร้างการวิเคราะห์ความอ่อนไหวที่แสดงช่วงของผลลัพธ์ที่เป็นไปได้
- ตารางตัวแปรเดียว: เปลี่ยนแปลงหนึ่งอินพุตในแถวหรือคอลัมน์ ทดสอบค่าที่แตกต่างกันสำหรับอินพุตเดียว
- ตารางสองตัวแปร: เปลี่ยนแปลงสองอินพุต (หนึ่งแถว หนึ่งคอลัมน์) เพื่อสร้างเมทริกซ์ คำนวณโมเดลใหม่สำหรับชุดค่าผสมแต่ละชุดและช่วยให้เห็นภาพแนวโน้ม
ตารางข้อมูลแบบตัวแปรเดียว
มาดูกันว่าราคาต่อหน่วยที่แตกต่างกันส่งผลต่อกำไรอย่างไร กำไรจะเปลี่ยนแปลงอย่างไรหากราคาต่อหน่วยเพิ่มขึ้นจาก $40 เป็น $70
ขั้นตอน:
- ตั้งค่าโมเดลของคุณ
- ป้อนราคาเหล่านี้ในแนวตั้งในคอลัมน์เดียว
- ในเซลล์ที่อยู่ติดกับราคาแรก (เช่น B5) ให้อ้างอิงเซลล์ผลลัพธ์กำไรหลัก (เช่น E2)
- เลือกช่วงตารางทั้งหมด รวมถึงส่วนหัว (เช่น A5:B12)
- ไปที่ข้อมูล แท็บ>> เลือก การวิเคราะห์แบบ What-If>> เลือก ตารางข้อมูล
- ปล่อย เซลล์ที่ป้อนแถว ว่างเปล่า
- ใน เซลล์ป้อนข้อมูลของคอลัมน์ ให้เลือกเซลล์ราคาต่อหน่วยเดิม (A2)
- คลิก ตกลง
- Excel เติมกำไรสำหรับแต่ละราคาโดยอัตโนมัติ
การตีความ:
- คอลัมน์ผลลัพธ์จะแสดงความอ่อนไหวของกำไรต่อราคา
- ราคาที่สูงขึ้นมักจะเพิ่มผลกำไร จนถึงจุดที่ความต้องการลดลง (หากจำลองไว้)
ตารางข้อมูลสองตัวแปร
มาดูกัน:“จะเกิดอะไรขึ้นถ้าทั้งราคาต่อหน่วยและหน่วยที่ขายแตกต่างกันไป”
ขั้นตอน:
- ป้อนราคาในแนวตั้ง (คอลัมน์ซ้าย) และหน่วยที่ขายในแนวนอน (แถวบนสุด)
- ในเซลล์ด้านซ้ายบนของตาราง (เหนือ 40 และด้านซ้ายของ 800) ให้อ้างอิงเซลล์ผลลัพธ์กำไรหลัก (เช่น ป้อน =E2 ใน D5)
- เลือกทั้งตาราง รวมถึงส่วนหัว (เช่น D5:H9)
- ไปที่ข้อมูล แท็บ>> เลือก การวิเคราะห์แบบ What-If>> เลือก ตารางข้อมูล
- ในเซลล์ที่ป้อนแถว ให้เลือกหน่วยที่ขาย (B2)
- ใน เซลล์ป้อนข้อมูลของคอลัมน์ ให้เลือกราคาต่อหน่วย (A2)
- คลิก ตกลง
- Excel สร้างเมทริกซ์แบบเต็มที่แสดงผลกำไรภายใต้ทุกราคา–การขายรวมกัน
หมายเหตุ: สำหรับตารางข้อมูล เซลล์อินพุตจะต้องอยู่บนแผ่นงานเดียวกันกับตารางข้อมูล สูตรที่ใช้ในการเติมตารางสามารถอยู่บนแผ่นงานอื่นโดยการอ้างอิง
ข้อมูลเชิงลึก: ตารางข้อมูลเหมาะที่สุดสำหรับการทดสอบความไวอย่างรวดเร็ว เช่น ความยืดหยุ่นของราคาหรือการวิเคราะห์กำไรเชิงปริมาณ
- ข้อดี: แสดงภาพช่วง; จัดการตัวแปรได้สูงสุดสองตัวอย่างมีประสิทธิภาพ
- ข้อเสีย: สามารถใช้คอมพิวเตอร์อย่างเข้มข้นสำหรับตารางขนาดใหญ่ ผลลัพธ์จะคงที่จนกว่าจะรีเฟรช
3. ตัวจัดการสถานการณ์:เปรียบเทียบโมเดล "What-If" หลายตัว
ตัวจัดการสถานการณ์ช่วยให้คุณสามารถบันทึกและเปรียบเทียบชุดค่าอินพุตต่างๆ (สถานการณ์) โดยไม่ต้องเขียนทับสูตร คุณสามารถสลับระหว่างสถานการณ์เหล่านั้นได้อย่างรวดเร็วหรือสร้างรายงานสรุปโดยเปรียบเทียบสถานการณ์ทั้งหมดแบบเทียบเคียงกัน เหมาะสำหรับสถานการณ์ที่ซับซ้อนซึ่งมีตัวแปรหลายตัว เช่น การคาดการณ์ที่ดีที่สุด/แย่ที่สุด
เมื่อใดจึงควรใช้:
- เปรียบเทียบกรณีที่ดีที่สุด กรณีที่เลวร้ายที่สุด และการคาดการณ์ทางธุรกิจที่มีแนวโน้มมากที่สุด
- ประเมินตัวเลือกเชิงกลยุทธ์ที่แตกต่างกันด้วยสมมติฐานที่เปลี่ยนแปลงหลายประการ
- นำเสนอสถานการณ์ต่างๆ แก่ผู้มีส่วนได้ส่วนเสียในรูปแบบที่ชัดเจนและเป็นระเบียบ
- ติดตามเวอร์ชันต่างๆ ของโมเดลเมื่อเวลาผ่านไป
มาสร้างการคาดการณ์ทางธุรกิจด้วยสามสถานการณ์:กรณีที่ดีที่สุด กรณีฐาน และกรณีที่เลวร้ายที่สุด
ขั้นตอน:
- ไปที่ข้อมูล แท็บ>> เลือก การวิเคราะห์แบบ What-If>> เลือก ตัวจัดการสถานการณ์
- คลิก เพิ่ม
- ชื่อสถานการณ์: ตั้งชื่อสถานการณ์แรก (เช่น กรณีที่ดีที่สุด)
- ในการเปลี่ยนเซลล์ เลือกราคาต่อหน่วย (A2) หน่วยที่ขาย (B2) ต้นทุนคงที่ (C2) และ ต้นทุนผันแปร (D2)
- คลิก ตกลง
- ป้อนค่าสำหรับกรณีที่ดีที่สุด สถานการณ์
- ราคาต่อหน่วย: 65
- หน่วยที่ขาย: 1600
- ต้นทุนคงที่: 8500
- ต้นทุนผันแปร: 25
- คลิก ตกลง
- คลิก เพิ่ม อีกครั้งและทำซ้ำสำหรับ “กรณีฐาน”
- คลิก เพิ่ม อีกครั้งและทำซ้ำสำหรับ "กรณีที่เลวร้ายที่สุด"
- เลือกสถานการณ์ใดก็ได้>> คลิก แสดง เพื่อใช้ค่าทันที
- คลิก สรุป เพื่อแสดงทุกกรณี
- เลือก สรุปสถานการณ์
- เลือกเซลล์กำไรเป็น "เซลล์ผลลัพธ์" (เช่น E2)
- คลิก ตกลง
- Excel สร้างแผ่นงานสรุปสถานการณ์ใหม่โดยเปรียบเทียบผลลัพธ์ของสถานการณ์ทั้งหมด
ข้อมูลเชิงลึก: คุณสามารถใช้การวิเคราะห์ Scenario Manager What-If เพื่อคาดการณ์และสร้างแบบจำลองสถานการณ์ใน Excel เป็นหนึ่งในเครื่องมือในการตัดสินใจที่ดีที่สุด (เช่น การลงทุน การจัดทำงบประมาณ หรือการประเมินความเสี่ยง) ซึ่งต้องบันทึกและทบทวนสมมติฐานหลายประการร่วมกัน
- ข้อดี: จัดการตัวแปรหลายตัว เปรียบเทียบกับรายงานได้ง่าย
- ข้อเสีย: การตั้งค่าด้วยตนเอง จำกัดการเปลี่ยนแปลงเซลล์ไว้ที่ 32 เซลล์ต่อสถานการณ์
ดาวน์โหลดสมุดแบบฝึกหัด
บทสรุป
บทช่วยสอนนี้แสดงวิธีการใช้เทคนิคการวิเคราะห์แบบ What-If สามเทคนิคเพื่อคาดการณ์และจำลองสถานการณ์จำลองใน Excel การวิเคราะห์แบบ What-If เปลี่ยนความไม่แน่นอนจากอุปสรรคให้เป็นโอกาสโดยการสำรวจความเป็นไปได้ต่างๆ ด้วย Goal Seek, Data Tables และ Scenario Manager ไม่ว่าคุณจะวางแผนงบประมาณธุรกิจ ประเมินตัวเลือกการลงทุน หรือจำลองผลลัพธ์ของโครงการ เครื่องมือ Excel เหล่านี้จะช่วยให้คุณเข้าใจว่าการเปลี่ยนแปลงในสมมติฐานของคุณส่งผลต่อผลลัพธ์ของคุณอย่างไร
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!