วิธีใช้การค้นหาเป้าหมายใน Excel

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

ตัวอย่างเช่น สมมติว่าคุณได้คะแนนรวม 75 คะแนนในวิชาหนึ่ง และคุณต้องได้คะแนนอย่างน้อย 90 เพื่อจะได้เกรด S ในวิชานั้น โชคดีที่คุณมีการทดสอบครั้งสุดท้ายที่อาจช่วยให้คุณเพิ่มคะแนนเฉลี่ยได้ คุณสามารถใช้ Goal Seek เพื่อค้นหาว่าคุณต้องการคะแนนเท่าใดในการทดสอบครั้งสุดท้ายจึงจะได้เกรด S

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

ส่วนประกอบของฟังก์ชันค้นหาเป้าหมาย

ฟังก์ชัน Goal Seek ประกอบด้วยพารามิเตอร์ 3 ตัว ได้แก่:

• ตั้งค่าเซลล์ – นี่คือเซลล์ที่ป้อนสูตร ระบุเซลล์ที่คุณต้องการผลลัพธ์ที่ต้องการ
• มูลค่า – นี่คือเป้าหมาย / มูลค่าที่ต้องการ ที่คุณต้องการจากการดำเนินการค้นหาเป้าหมาย
• โดยการเปลี่ยนเซลล์ – ระบุเซลล์ที่ต้องการปรับค่าเพื่อให้ได้ผลลัพธ์ที่ต้องการ

วิธีใช้การแสวงหาเป้าหมายใน Excel: ตัวอย่างที่ 1

เพื่อสาธิตวิธีการทำงานด้วยตัวอย่างง่ายๆ ลองนึกภาพว่าคุณเปิดแผงขายผลไม้ ในภาพหน้าจอด้านล่าง เซลล์ B11 (ด้านล่าง) จะแสดงจำนวนเงินที่คุณต้องจ่ายในการซื้อผลไม้สำหรับแผงขายของคุณ และเซลล์ B12 จะแสดงรายได้ทั้งหมดของคุณจากการขายผลไม้เหล่านั้น และเซลล์ B13 จะแสดงเปอร์เซ็นต์ของกำไร (20%)

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

คุณใช้สูตรต่อไปนี้ในเซลล์ B13 เพื่อคำนวณเปอร์เซ็นต์กำไร:

=(B12-B11)/B12

ตอนนี้ คุณต้องการคำนวณส่วนต่างกำไรเพื่อให้เปอร์เซ็นต์กำไรของคุณเป็น 30% คุณสามารถทำได้ด้วย Goal Seek ใน Excel

สิ่งแรกที่ต้องทำคือเลือกเซลล์ที่มีค่าที่คุณต้องการปรับ ทุกครั้งที่คุณใช้ Goal Seek คุณต้องเลือกเซลล์ที่มีสูตรหรือฟังก์ชัน ในกรณีของเรา เราจะเลือกเซลล์ B13 เนื่องจากมีสูตรคำนวณเปอร์เซ็นต์

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

กล่องโต้ตอบการค้นหาเป้าหมายจะปรากฏขึ้นพร้อม 3 ฟิลด์:

• ตั้งค่าเซลล์ – ป้อนการอ้างอิงเซลล์ที่มีสูตร (B13) นี่คือเซลล์ที่จะมีผลลัพธ์ที่คุณต้องการ

• มูลค่า – ป้อนผลลัพธ์ที่ต้องการที่คุณต้องการบรรลุ (30%)

• โดยการเปลี่ยนเซลล์ – แทรกการอ้างอิงเซลล์สำหรับค่าอินพุตที่คุณต้องการเปลี่ยน (B12) เพื่อให้ได้ผลลัพธ์ที่ต้องการ เพียงคลิกที่เซลล์หรือป้อนการอ้างอิงเซลล์ด้วยตนเอง เมื่อคุณเลือกเซลล์ Excel จะเพิ่มเครื่องหมาย '$' ก่อนตัวอักษรของคอลัมน์และหมายเลขแถวเพื่อทำให้เป็นเซลล์สัมบูรณ์

เมื่อเสร็จแล้ว คลิก 'ตกลง' เพื่อทดสอบ

จากนั้นกล่องโต้ตอบ "สถานะการค้นหาเป้าหมาย" จะปรากฏขึ้นและแจ้งให้คุณทราบหากพบวิธีแก้ไขดังที่แสดงด้านล่าง หากพบวิธีแก้ปัญหา ค่าอินพุตใน 'เซลล์ที่เปลี่ยนแปลง (B12)' จะถูกปรับเป็นค่าใหม่ นี่คือสิ่งที่เราต้องการ ในตัวอย่างนี้ การวิเคราะห์ระบุว่า เพื่อให้คุณบรรลุเป้าหมายกำไร 30% คุณต้องมีรายได้ 1635.5 ดอลลาร์ (B12)

คลิก 'ตกลง' และ Excel จะเปลี่ยนค่าของเซลล์หรือคลิก 'ยกเลิก' เพื่อยกเลิกโซลูชันและคืนค่าเดิม

ค่าอินพุต (1635.5) ในเซลล์ B12 คือสิ่งที่เราพบโดยใช้ Goal Seek เพื่อให้บรรลุเป้าหมาย (30%)

สิ่งที่ต้องจำเมื่อใช้ Goal Seek

• Set Cell ต้องมีสูตรที่ขึ้นอยู่กับเซลล์ 'By Changing Cell' เสมอ
• คุณสามารถใช้ Goal Seek กับค่าอินพุตเซลล์เดียวเท่านั้นในแต่ละครั้ง
• 'โดยการเปลี่ยนเซลล์' ต้องมีค่าไม่ใช่สูตร
• หาก Goal Seek ไม่พบวิธีแก้ปัญหาที่ถูกต้อง ระบบจะแสดงค่าที่ใกล้เคียงที่สุดที่สามารถสร้างได้และบอกคุณว่าข้อความ "การแสวงหาเป้าหมายอาจไม่พบวิธีแก้ปัญหา"

จะทำอย่างไรเมื่อ Excel Goal Seek ไม่ทำงาน

อย่างไรก็ตาม หากคุณแน่ใจว่ามีวิธีแก้ไข มีบางสิ่งที่คุณสามารถลองแก้ไขปัญหานี้ได้

ตรวจสอบพารามิเตอร์และค่าการค้นหาเป้าหมาย

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

การปรับการตั้งค่าการวนซ้ำ

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

หากต้องการเปลี่ยนการตั้งค่าการคำนวณซ้ำ ให้คลิก 'ไฟล์' จากรายการแท็บ จากนั้นคลิก 'ตัวเลือก' ที่ด้านล่าง

ในหน้าต่างตัวเลือกของ Excel คลิก 'สูตร' ที่บานหน้าต่างด้านซ้ายมือ

ในส่วน "ตัวเลือกการคำนวณ" ให้เปลี่ยนการตั้งค่าเหล่านี้:

• การวนซ้ำสูงสุด – ระบุจำนวนโซลูชันที่เป็นไปได้ที่ excel จะคำนวณ ยิ่งจำนวนสูงเท่าไหร่ก็ยิ่งทำซ้ำได้มากขึ้นเท่านั้น ตัวอย่างเช่น หากคุณตั้งค่า 'Maximum Iterations' เป็น 150 Excel จะทดสอบ 150 วิธีแก้ปัญหาที่เป็นไปได้
• การเปลี่ยนแปลงสูงสุด – แสดงถึงความถูกต้องของผลลัพธ์ ตัวเลขที่น้อยกว่าให้ความแม่นยำสูงกว่า ตัวอย่างเช่น หากค่าเซลล์อินพุตของคุณเท่ากับ '0' แต่ Goal Seek หยุดคำนวณที่ '0.001' การเปลี่ยนค่านี้เป็น '0.0001' ควรแก้ปัญหาได้

เพิ่มค่า 'Maximum Iterations' หากคุณต้องการให้ Excel ทดสอบวิธีแก้ปัญหาที่เป็นไปได้มากขึ้น และลดค่า 'Maximum Change' หากคุณต้องการผลลัพธ์ที่แม่นยำยิ่งขึ้น

ภาพหน้าจอด้านล่างแสดงค่าเริ่มต้น:

ไม่มีการอ้างอิงแบบวงกลม

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

ตัวอย่างการแสวงหาเป้าหมายของ Excel 2

สมมติว่าคุณกำลังยืมเงิน '$25000' จากใครบางคน พวกเขาให้ยืมเงินคุณในอัตราดอกเบี้ย 7% ต่อเดือนเป็นระยะเวลา 20 เดือน ซึ่งจะทำให้ชำระคืน '1327 ดอลลาร์' ต่อเดือน แต่คุณสามารถจ่ายได้เพียง 1,000 ดอลลาร์ต่อเดือนเป็นเวลา 20 เดือนพร้อมดอกเบี้ย 7% Goal Seek สามารถช่วยคุณค้นหาจำนวนเงินกู้ที่สร้างการชำระเงินรายเดือน (EMI) ที่ '$1000'

ป้อนตัวแปรอินพุตสามตัวที่คุณจะต้องใช้ในการคำนวณ PMT ของคุณ เช่น อัตราดอกเบี้ย 7% ระยะเวลา 20 เดือน และเงินต้น $25,000

ป้อนสูตร PMT ต่อไปนี้ในเซลล์ B5 ซึ่งจะให้จำนวน EMI เท่ากับ $1,327.97

ไวยากรณ์ของฟังก์ชัน PMT:

=PMT(อัตราดอกเบี้ย/12, ระยะเวลา, เงินต้น)

สูตร:

=PMT(B3/12,B4,-B2)

เลือกเซลล์ B5 (เซลล์สูตร) ​​และไปที่ Data -> What If Analysis -> Goal Seek

ใช้พารามิเตอร์เหล่านี้ในหน้าต่าง 'การแสวงหาเป้าหมาย':

• ตั้งค่าเซลล์ – B5 (เซลล์ที่มีสูตรคำนวณ EMI)
• มูลค่า – 1,000 (ผลสูตร/เป้าหมายที่คุณต้องการ)
• โดยการเปลี่ยนเซลล์ – B2 (นี่คือจำนวนเงินกู้ที่คุณต้องการเปลี่ยนแปลงเพื่อให้บรรลุเป้าหมาย)

จากนั้นกด 'ตกลง' เพื่อเก็บวิธีแก้ปัญหาที่พบหรือ 'ยกเลิก' เพื่อยกเลิก

การวิเคราะห์บอกคุณว่าจำนวนเงินสูงสุดที่คุณสามารถยืมได้คือ $18825 หากคุณต้องการเกินงบประมาณของคุณ

นั่นเป็นวิธีที่คุณใช้ Goal Seek ใน Excel