สลับระหว่างชุดค่าต่างๆ โดยใช้สถานการณ์สมมติ

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

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

นอกจากเครื่องมือทั้งสามนี้ คุณสามารถติดตั้ง Add-in ที่จะช่วยคุณดําเนินการวิเคราะห์ What-If เช่น Solver Add-in Add-in Solver จะคล้ายกับ การค้นหาค่าเป้าหมาย แต่สามารถรองรับตัวแปรได้มากขึ้น คุณยังสามารถสร้างการคาดการณ์โดยใช้จุดจับเติมและคําสั่งต่างๆ ที่มีอยู่แล้วภายใน Excel สำหรับรูปแบบขั้นสูง คุณสามารถใช้ Analysis ToolPak Add-in

สมมติว่าคุณต้องการสร้างงบประมาณแต่รายได้ของคุณไม่แน่นอน เมื่อใช้สถานการณ์สมมติ คุณสามารถกําหนดค่าที่เป็นไปได้ที่แตกต่างกันสําหรับรายได้ แล้วสลับระหว่างสถานการณ์สมมติเพื่อทําการวิเคราะห์แบบ What-If

ตัวอย่างเช่น สมมติว่าสถานการณ์งบประมาณกรณีที่เลวร้ายที่สุดของคุณคือ รายได้รวม $50,000 และต้นทุนของสินค้าที่ขายได้ $13,200 เหลือ $36,800 ในกําไรขั้นต้น เมื่อต้องการกําหนดค่าชุดนี้เป็นสถานการณ์สมมติ คุณต้องใส่ค่าในเวิร์กชีตก่อน ตามที่แสดงในภาพประกอบต่อไปนี้:

เซลล์ที่เปลี่ยนแปลงมีค่าที่คุณพิมพ์ ในขณะที่เซลล์ผลลัพธ์มีสูตรที่ยึดตามเซลล์ที่เปลี่ยนแปลง (ในเซลล์ภาพประกอบ B4 นี้มีสูตร =B2-B3)

จากนั้นคุณสามารถใช้กล่องโต้ตอบ ตัวจัดการสถานการณ์สมมติ เพื่อบันทึกค่าเหล่านี้เป็นสถานการณ์สมมติ ไปที่แท็บ ข้อมูล > What-If Analysis > ตัวจัดการสถานการณ์สมมติ > เพิ่ม

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

การป้องกัน – คุณยังสามารถป้องกันสถานการณ์ของคุณได้ ดังนั้นในส่วน การป้องกัน ให้เลือกตัวเลือกที่คุณต้องการ หรือยกเลิกการเลือกหากคุณไม่ต้องการการป้องกันใดๆ

• เลือก ป้องกันการเปลี่ยนแปลง เพื่อป้องกันการแก้ไขสถานการณ์สมมติเมื่อเวิร์กชีตได้รับการป้องกัน

• เลือก ซ่อน เพื่อป้องกันไม่ให้แสดงสถานการณ์สมมติเมื่อเวิร์กชีตได้รับการป้องกัน

ตอนนี้สมมติว่าสถานการณ์งบประมาณกรณีที่ดีที่สุดของคุณคือรายได้รวม $150,000 และต้นทุนของสินค้าที่ขายได้ $26,000 เหลือ $124,000 ในกําไรรวม เมื่อต้องการกําหนดค่าชุดนี้เป็นสถานการณ์สมมติ ให้คุณสร้างสถานการณ์สมมติอื่น ตั้งชื่อว่า ตัวพิมพ์ที่ดีที่สุด และใส่ค่าที่แตกต่างกันสําหรับเซลล์ B2 (150,000) และเซลล์ B3 (26,000) เนื่องจากกําไรขั้นต้น (เซลล์ B4) เป็นสูตร - ความแตกต่างระหว่างรายได้ (B2) และต้นทุน (B3) คุณจึงไม่ได้เปลี่ยนเซลล์ B4 สําหรับสถานการณ์สมมติสําหรับกรณีที่ดีที่สุด

หลังจากที่คุณบันทึกสถานการณ์สมมติ สถานการณ์สมมติจะพร้อมใช้งานในรายการสถานการณ์สมมติที่คุณสามารถใช้ในการวิเคราะห์แบบ What-if ของคุณ เมื่อกําหนดค่าในภาพประกอบก่อนหน้า ถ้าคุณเลือกที่จะแสดงสถานการณ์สมมติตัวพิมพ์ที่ดีที่สุด ค่าในเวิร์กชีตจะเปลี่ยนไปคล้ายกับภาพประกอบต่อไปนี้:

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

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

เมื่อคุณเลือกที่จะผสาน ตัวจัดการสถานการณ์สมมติจะโหลด ตัวช่วยสร้างสถานการณ์สมมติการผสาน ซึ่งจะแสดงรายการเวิร์กชีตทั้งหมดในเวิร์กบุ๊กที่ใช้งานอยู่ รวมทั้งแสดงรายการเวิร์กบุ๊กอื่นๆ ที่คุณอาจเปิดในขณะนั้น ตัวช่วยสร้างจะบอกจํานวนสถานการณ์สมมติที่คุณมีบนเวิร์กชีตต้นฉบับแต่ละเวิร์กชีตที่คุณเลือก

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

เมื่อต้องการเปรียบเทียบสถานการณ์สมมติต่างๆ คุณสามารถสร้างรายงานที่สรุปสถานการณ์สมมติเหล่านั้นในหน้าเดียวกันได้ รายงานสามารถแสดงรายการสถานการณ์สมมติแบบเคียงข้างกันหรือนําเสนอสถานการณ์สมมติเหล่านั้นใน รายงาน PivotTable

รายงานสรุปสถานการณ์สมมติที่ยึดตามสถานการณ์สมมติตัวอย่างสองตัวอย่างก่อนหน้าจะมีลักษณะดังนี้:

คุณจะสังเกตเห็นว่า Excel ได้เพิ่ม ระดับการจัดกลุ่ม ให้คุณโดยอัตโนมัติ ซึ่งจะขยายและยุบมุมมองเมื่อคุณคลิกตัวเลือกต่างๆ

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