ข้อจำกัดของ Goal Seek และวิธีแก้ไข

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

เราสามารถแก้ไขจุดอ่อนของ Goal Seek ให้สามารถรับค่าจากเซลล์ได้โดยตรง และหาทางปรับปรุงให้ Goal Seek ทำงานหาคำตอบให้เองทันทีอย่างอัตโนมัติ โดยเริ่มจากใช้ Macro Recorder บันทึกการสั่ง Data > What-If Analysis > Goal Seek แล้วใช้งานตามตัวอย่างการคำนวณหาค่าโฆษณาในตัวอย่างที่ผ่านมา จะได้รหัส VBA ตามนี้

Sub SetGoal()

    Range("H18").GoalSeek Goal:=0.15, ChangingCell:=Range("G18")

End Sub

จากนั้นให้แก้ไขตำแหน่งอ้างอิงและค่าคงที่ในตัวรหัสให้อ้างถึงชื่อ Range Name ที่ตั้งไว้ในตารางกลายเป็นรหัสใหม่ดังนี้

Sub SetGoal()

    [MyTarget].GoalSeek Goal:=[TargetValue], ChangingCell:=[ChangeCell]

End Sub

โดยกำหนดให้ตั้งชื่อ Range Name ชื่อว่า MyTarget ให้กับเซลล์ H18 และชื่อ ChangeCell ให้กับเซลล์ G18 และเมื่อใดที่ต้องการตัวเลขตัวใหม่ ให้กรอกตัวเลขลงไปในเซลล์ที่ตั้งชื่อว่า TargetValue

หากต้องการความสะดวกมากขึ้น ควรสั่งให้ Goal Seek ทำงานเองโดยใช้ Event ก็จะได้ผลลัพธ์ทันทีโดยไม่ต้องเสียเวลามาเปิด Goal Seek ขึ้นเพื่อกรอกค่าเองอีกต่อไป เช่น หากใช้ Calculation Event ก็จะได้ผลลัพธ์ตามการคำนวณใดๆที่เกิดขึ้นในชีทนั้น

Private Sub Worksheet_Calculate()

    [MyTarget].GoalSeek Goal:=[TargetValue], ChangingCell:=[ChangeCell]

End Sub