ลืมการค้นหาเป้าหมายไปได้เลย — เครื่องมือ Excel นี้ทำได้มากกว่านั้นมาก

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

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

ข้อจำกัดของการค้นหาเป้าหมายและแนวทางที่ดีกว่าในการก้าวไปข้างหน้า

การหาคำตอบย้อนหลังและการแก้สมการตัวแปรเดียวไม่ใช่สิ่งที่เพียงพอเสมอไป

ลองจินตนาการว่าคุณกำลังขอสินเชื่อ 15,000 ดอลลาร์พร้อมดอกเบี้ยประจำปี 7% คุณรู้อยู่แล้วว่าคุณสามารถจ่ายได้เท่าไหร่: 300 ดอลลาร์ต่อเดือน Goal Seek เหมาะสำหรับงานประเภทนี้ โดยการใช้ฟังก์ชัน PMT และชี้ Goal Seek ไปยังเซลล์ระยะเวลา คุณสามารถให้มันหาช่วงเวลาที่ต้องชำระเงินพอดีเพื่อทำให้การจ่ายเงิน 300 ดอลลาร์นั้นเป็นจริง

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

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

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

เนื่องจาก Solver เป็นโปรแกรมเสริมของ Excel คุณจะต้องใช้เวอร์ชัน Excel บนเดสก์ท็อปเพื่อใช้งาน ใน Windows คุณสามารถเปิดใช้งานได้โดยไปที่แท็บ แฟ้ม เลือก ตัวเลือก และจากนั้นเปิด โปรแกรมเสริม จากแถบด้านข้าง ใต้ โปรแกรมเสริมของแอปพลิเคชันที่ไม่ทำงาน ให้เลือก Solver Add-in คลิก ไป ข้างกล่อง จัดการ ทำเครื่องหมาย Solver Add-in ในกล่องโต้ตอบ และยืนยันด้วย ตกลง หากมีข้อความแจ้ง ให้อนุญาต Excel ติดตั้งเครื่องมือนี้

บน Mac ให้เปิดเมนู เครื่องมือ เลือก โปรแกรมเสริม Excel ทำเครื่องหมายที่ โปรแกรมเสริมตัวแก้ปัญหา ในตัวเลือกที่มีอยู่ และคลิก ตกลง

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

กรณีที่คุณสามารถใช้ตัวแก้ปัญหา

จัดการงบประมาณ การตั้งราคา การผลิต และอื่นๆ อีกมากมายด้วย Solver

สมมติว่าคุณมีงบประมาณการโฆษณาทั้งหมด 20,000 ดอลลาร์ที่คุณต้องการแบ่งออกเป็นไตรมาสต่างๆ จำนวนเงินที่คุณใช้ในแต่ละช่วงเวลาส่งผลโดยตรงต่อยอดขายของคุณและในที่สุดก็กำไรของคุณ คุณสามารถใช้ Solver เพื่อปรับงบประมาณรายไตรมาสเหล่านั้นเป็นหลายเซลล์ตัวแปร ทำให้มันสามารถคำนวณกำไรสูงสุดที่เป็นไปได้พร้อมกับรับประกันว่าการจัดสรรทั้งหมดจะไม่เกินขีดจำกัด 20,000 ดอลลาร์

เพื่อให้สิ่งนี้ทำงานได้ ให้ตั้งค่าเป้าหมายของคุณไปที่เซลล์ที่มีผลกำไรรวมของคุณและเลือก Max ภายใต้ By Changing Variable Cells ให้เลือกสี่เซลล์ที่แทนงบประมาณของแต่ละไตรมาส จากนั้น เพิ่มข้อจำกัดโดยเลือกเซลล์ที่รวมค่าของแต่ละไตรมาส ตั้งค่าตัวดำเนินการเป็นน้อยกว่าหรือเท่ากับ และกำหนดขีดจำกัดเป็น 20,000 ในกรณีของฉัน ฉันใช้วิธีการแก้ปัญหา GRG Nonlinear เพราะการประมาณกำไรของฉันรวมถึงสมการรากที่สอง เช่น:

=10*SQRT(B2)

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

คุณยังสามารถพึ่งพา Solver สำหรับสถานการณ์ที่ซับซ้อนมากขึ้นได้ ตัวอย่างเช่น หากคุณต้องการบรรลุเป้าหมายรายได้เฉพาะโดยการขายสินค้า คุณอาจจำเป็นต้องปรับจำนวนหน่วยที่ขาย ราคาต่อหน่วย และต้นทุนต่อหน่วยพร้อมกัน Solver จะช่วยให้คุณควบคุมตัวแปรเหล่านี้ทั้งหมดพร้อมทั้งใช้ข้อจำกัดที่เป็นจริงได้ คุณสามารถมั่นใจได้ว่าจำนวนหน่วยที่ขายเป็นจำนวนเต็ม ดังนั้นคุณจะไม่ลงเอยด้วยค่าที่ไม่สมจริง เช่น 706.5 หน่วย และรักษาราคาให้อยู่ในช่วงที่กำหนด เช่น 3.00 ดอลลาร์ถึง 4.00 ดอลลาร์

ในการทำให้ได้เป้าหมายรายได้เฉพาะ เช่น 5,000 ให้ตั้งค่าเป้าหมายไปยังเซลล์ที่มีรายได้รวม ซึ่งอาจใช้สูตรเช่น:

=I2*(I3-I4)

จากนั้น กำหนดวัตถุประสงค์ให้เป็นค่าคงที่ (Value Of) และป้อนเป้าหมายของคุณ จากนั้นเลือกเซลล์สำหรับจำนวนหน่วยที่ขายและราคาที่เป็น By Changing Variable Cells หลังจากนั้น เพิ่มข้อจำกัดทั้งสามนี้เพื่อให้ผลลัพธ์ที่ได้สมจริง:

ตั้งค่าช่องที่มีหน่วยของคุณเป็น int
กำหนดราคาแต่ละหน่วยสูงสุดที่ 4.00 โดยการเลือก
ตั้งราคาขั้นต่ำต่อหน่วยที่ 3.00 โดยการเลือก >= และเพิ่ม 3.00 เป็นข้อจำกัด

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

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

ตัวแก้สมการคุ้มค่าที่จะเพิ่มลงในชุดเครื่องมือของคุณ

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