Excel 2019 (ตอนที่ 28): การวิเคราะห์แบบ What-if (การวิเคราะห์แบบ If-Then)

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

แสวงหาเป้าหมาย

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

ตัวอย่างที่ 1 การใช้ Goal Seek

สมมติว่าคุณลงทะเบียนเรียนในชั้นเรียน ขณะนี้คุณมี 65 คะแนนและคุณต้องได้คะแนนอย่างน้อย 70 คะแนนจึงจะผ่านวิชานี้ โชคดีที่คุณมีงานชิ้นสุดท้ายที่สามารถช่วยเพิ่มเกรดเฉลี่ยของคุณได้ คุณสามารถใช้ Goal Seek เพื่อดูว่าคุณต้องการคะแนนเท่าใดในแบบฝึกหัดสุดท้ายจึงจะผ่านวิชานั้นๆ

ในภาพด้านล่าง คุณจะเห็นว่าคะแนนของแบบฝึกหัด 4 ครั้งแรกคือ 58, 70, 72 และ 60 แม้จะไม่รู้ว่าคะแนนของแบบฝึกหัดที่ 5 จะเป็นเท่าใด แต่คุณสามารถเขียนสูตรหรือฟังก์ชันเพื่อคำนวณได้ คะแนนสุดท้ายที่ต้องการ ในกรณีนี้ งานแต่ละงานจะมีการถ่วงน้ำหนักเท่ากัน ดังนั้นสิ่งที่เราต้องทำคือเฉลี่ยคะแนนทั้งห้าโดยป้อน=AVERAGE(B2:B6) เมื่อใช้การค้นหาเป้าหมาย เซลล์ B6 จะแสดงคะแนนขั้นต่ำที่จำเป็นสำหรับการมอบหมายครั้งสุดท้าย

1. เลือกเซลล์ที่มีค่าที่คุณต้องการเปลี่ยน เมื่อใดก็ตามที่คุณใช้ Goal Seek คุณจะต้องเลือกเซลล์ที่มีสูตรหรือฟังก์ชันอยู่แล้ว ตัวอย่างเช่น เราจะเลือกเซลล์ B7 เนื่องจากมีสูตร=AVERAGE(B2:B6)

2. จาก แท็บ ข้อมูลคลิก คำสั่ง การวิเคราะห์แบบ What-If จากนั้นเลือกการค้นหาเป้าหมายจากเมนูแบบเลื่อนลง

3. กล่องโต้ตอบจะปรากฏขึ้นพร้อมกับสามฟิลด์ ฟิลด์แรกSet cell:จะมีผลลัพธ์ที่ต้องการ ในตัวอย่าง มีการเลือกเซลล์ B7

ฟิลด์ที่สองTo value:เป็นผลลัพธ์ที่ต้องการ ตัวอย่างเช่น เราจะป้อน70เนื่องจากเราจำเป็นต้องได้รับตัวเลขนั้นอย่างน้อยจึงจะผ่านชั้นเรียน

ฟิลด์ที่สามโดยการเปลี่ยนเซลล์:คือเซลล์ที่ Goal Seek จะวางคำตอบ ตัวอย่างเช่น เราจะเลือกเซลล์B6เนื่องจากเราต้องการกำหนดคะแนนที่เราต้องให้ได้ในแบบฝึกหัดสุดท้าย

4. เมื่อเสร็จแล้ว คลิกตกลง

5. กล่องโต้ตอบจะบอกคุณว่า Goal Seek สามารถค้นหาคำตอบได้หรือไม่ คลิกตกลง

6. ผลลัพธ์จะปรากฏในเซลล์ที่ระบุ ในตัวอย่างนี้ Goal Seek คำนวณว่าต้องมีคะแนนอย่างน้อย 90 ในงานมอบหมายขั้นสุดท้ายจึงจะผ่าน

ตัวอย่างที่ 2 การใช้ Goal Seek

สมมติว่าคุณกำลังวางแผนกิจกรรมและต้องการเชิญผู้คนให้ได้มากที่สุดโดยไม่ต้องใช้งบประมาณเกิน 500 ดอลลาร์ คุณสามารถใช้ Goal Seek เพื่อดูว่าจะเชิญคนได้กี่คน ในตัวอย่างด้านล่าง เซลล์ B5 มีสูตร=B2+B3*B4เพื่อคำนวณต้นทุนรวมของการจองบวกต้นทุนต่อคน

1. เลือกเซลล์ที่มีค่าที่คุณต้องการเปลี่ยน ตัวอย่างเช่น เราจะเลือกเซลล์ B5

2. จาก แท็บ ข้อมูลคลิก คำสั่ง การวิเคราะห์แบบ What-If จากนั้นเลือกการค้นหาเป้าหมายจากเมนูแบบเลื่อนลง

3. กล่องโต้ตอบจะปรากฏขึ้นพร้อมกับสามฟิลด์ ฟิลด์แรกSet cell:จะมีผลลัพธ์ที่ต้องการ ในตัวอย่าง มีการเลือกเซลล์ B5

ฟิลด์ที่สองTo value:เป็นผลลัพธ์ที่ต้องการ ตัวอย่างเช่น ฉันจะป้อน 500 เพราะฉันต้องการใช้จ่ายเพียง 500 ดอลลาร์เท่านั้น

ฟิลด์ที่สามโดยการเปลี่ยนเซลล์:คือเซลล์ที่ Goal Seek จะวางคำตอบ ตัวอย่างเช่น ฉันจะเลือกเซลล์ B4 เพราะฉันต้องการทราบว่าฉันสามารถเชิญแขกได้กี่คนโดยไม่ต้องใช้จ่ายเกิน 500 ดอลลาร์

4. เมื่อเสร็จแล้ว คลิกตกลง

5. กล่องโต้ตอบจะบอกคุณว่า Goal Seek สามารถค้นหาคำตอบได้หรือไม่ คลิกตกลง

6. ผลลัพธ์จะปรากฏในเซลล์ที่ระบุ ในตัวอย่างนี้ Goal Seek คำนวณคำตอบได้ประมาณ18.62 ในกรณีนี้ คำตอบสุดท้ายต้องเป็นจำนวนเต็ม ดังนั้นจึงจำเป็นต้องปัดเศษคำตอบขึ้นหรือลง เนื่องจากการปัดเศษจะเกินงบประมาณ ตัวอย่างจึงปัดเศษลงเป็นแขก18 คน

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

การวิเคราะห์แบบ What-if ประเภทอื่นๆ

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

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

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