วิธีทำให้ Excel Solver เป็นอัตโนมัติ

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

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

เราจะทำได้อย่างไร

เราจะใช้ประโยชน์จากตัวบันทึกแมโครใน Excel เพื่อจัดการกับโค้ดจริงส่วนใหญ่ เครื่องมือมาโครบันทึกจะช่วยให้ Excel แปลงขั้นตอนที่เราต้องทำด้วยตนเองเป็นโค้ด VBA

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

นี่จะเป็นกึ่งเทคนิค แต่ด้วยรูปแบบการพัฒนานี้ เราสามารถมุ่งเน้นไปที่กระบวนการและแนวคิด แทนที่จะเน้นที่วิธีการเขียนโค้ด

การตั้งค่าเครื่องบันทึกมาโคร

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

ภาพหน้าจอด้านล่างใช้สำหรับ Mac แต่ Excel ที่ใช้พีซีจะคล้ายกัน

หากคุณยังไม่ได้ติดตั้งโปรแกรมเสริมของโปรแกรมแก้ไข คุณสามารถติดตั้งผ่านเมนูเพิ่มเติมได้ ไซต์สนับสนุนของ Microsoft มีคำแนะนำสำหรับแพลตฟอร์มทั้งหมด โหลด Solver Add-in ใน Excel

กำลังบันทึกมาโคร

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

กลับไปที่แท็บนักพัฒนาบนริบบิ้น คุณจะเห็นแผ่นกระดาษที่มีจุดสีแดง คลิกที่ปุ่มนั้นเพื่อเปิดตัวบันทึก

ตั้งชื่อมาโครของคุณและกรอกคำอธิบายหากต้องการ

เมื่อคุณกดตกลง เครื่องบันทึกจะเริ่มขึ้น

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

ตอนนี้เรามีรหัสฐานแล้ว เราก็สามารถทำความสะอาดและเตรียมมันสำหรับขั้นตอนต่อไปได้

ล้างรหัส

ตอนนี้ เราสามารถแก้ไขโค้ด ลบส่วนที่ไม่จำเป็นออก และทำให้แน่ใจว่าเราใช้เฉพาะส่วนสำคัญของการบันทึกมาโครเท่านั้น

คุณสามารถแก้ไขโค้ดผ่านตัวแก้ไข VBA ของ Excel หากต้องการเข้าถึงรหัสที่บันทึกไว้ให้คลิกปุ่มมาโคร

ไปที่มาโครของคุณในเมนูใหม่และคลิกแก้ไข

ตอนนี้เราเปิดโปรแกรมแก้ไขโค้ดแล้ว เราสามารถเริ่มปรับแต่งได้

การลบรหัสที่ไม่จำเป็น

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

การตั้งค่าการอ้างอิงตัวแก้ไข

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

ตอนนี้คุณสามารถเปลี่ยนพารามิเตอร์ของคุณ เปิดเมนูมาโคร เลือกมาโครของคุณ กดรัน และมันจะอัปเดตผลลัพธ์

มาเรียงโค้ดกับโมเดลตัวแก้ปัญหาของเรากัน คุณจะเห็นว่ามันเรียงกันอย่างไร ภาษาแตกต่างกัน แต่คุณสามารถรวมความหมายทั้งหมดเข้าด้วยกัน

ต่อไป เราจะแยกส่วนที่เราต้องเปลี่ยนและสรุปการทำงานอัตโนมัติของเรา

ล้างและทำซ้ำ – สร้างลูป

ตอนนี้เรามีตัวแก้ปัญหาการทำงานแล้ว เราต้องทำซ้ำหลายครั้ง มายกตัวอย่างและประสานแผนกัน

เราเริ่มต้นด้วยงบประมาณ 1,000 ดอลลาร์ในรุ่นแรก แต่เราต้องการทำแบบเดียวกันโดยเพิ่มทีละ 100 ดอลลาร์สูงสุด 2,500 ดอลลาร์

มาดำน้ำกันเถอะ! เรามีรายการค่าในคอลัมน์ B และผลลัพธ์ของเราในคอลัมน์ C ถ้าเราเขียนกระบวนการออกมา มันจะดูเหมือน

1. กำหนดการใช้จ่ายทั้งหมดเป็นมูลค่าใน B10
2. แก้โมเดล.
3. วางเอาต์พุตใน C10
4. เลือกค่า B ถัดไป
5. ตั้งค่าเอาต์พุตในแถว C
6. ทำซ้ำขั้นตอนที่ 2-5 สำหรับค่าทั้งหมดใน B

เริ่มต้นการวนซ้ำ

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

สำหรับตัวอย่างนี้ เราจะกำหนดจุดสิ้นสุดด้วยจำนวนแถว B10:B25 มี 16 ค่า

เราจะเริ่มการวนซ้ำและล้อมรอบโค้ดตัวแก้ปัญหาของเรา เราจะตั้งค่าตัวแปรเพื่อเก็บหมายเลขลูป (i) และผ่านแต่ละค่า โดยแก้ลูปในแต่ละครั้ง

การรีเซ็ตตัวแก้ปัญหา

เราจะเพิ่มเงื่อนไขอื่นอีกเมื่อเริ่มต้นโค้ดของเรา เนื่องจากเราต้องการรันตัวแก้ไขซ้ำในแต่ละการวนซ้ำ เราจะรีเซ็ตมันในแต่ละลูป การดำเนินการนี้จะล้างการตั้งค่าและเริ่มต้นโมเดลใหม่

เราสามารถทำได้ผ่าน SolverReset

กำลังอัปเดตการอ้างอิงเซลล์

ตอนนี้เราแก้ปัญหาได้สิบหกครั้งแต่จำเป็นต้องอัปเดตการอ้างอิงเซลล์ของเรา ในขณะที่เราอ้างอิง B10 เดิม เราจำเป็นต้องเลื่อนลงมาหนึ่งเซลล์ในการรันแต่ละครั้งเพื่ออัปเดตขีดจำกัด เราสามารถทำได้โดยใช้ฟังก์ชันออฟเซ็ต เราสามารถอัปเดต “$B$10” เพื่ออ้างอิงเซลล์ จากนั้นชดเชยด้วยแถว i (0 ในการรันครั้งแรก 1 แถวในวินาที เป็นต้น) ทุกครั้งที่ลูปทำงาน ฉันจะเพิ่มขึ้นทีละหนึ่ง

กำลังรวบรวมผลงาน

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

เราจะเรียก C10 ออฟเซ็ตด้วยจำนวนแถวที่เหมาะสม จากนั้นวางค่าของเอาต์พุตของเรา

เลี่ยงการแจ้งเตือนของตัวแก้ไข

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

ไปข้างหน้าและเพิ่มบรรทัดต่อไปนี้หลังจากโค้ดโปรแกรมแก้ปัญหาของคุณและเพิ่ม (TRUE) ลงใน SolverSolve สิ่งนี้จะเลียนแบบคุณยอมรับการเปลี่ยนแปลง

ตอนนี้เรามีรหัสเสร็จแล้ว!

ขึ้นและทำงาน

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

คุณยังสามารถเพิ่มกราฟเปรียบเทียบใดๆ ก็ได้ ในกรณีนี้ เราต้องการเปรียบเทียบอัตราการเติบโตของการใช้จ่ายกับการเติบโตของปริมาณ Conversion ตัวอย่างนี้ไม่น่าสนใจ แต่ข้อมูลจริงสามารถช่วยเปิดเผยจุดพักด้านประสิทธิภาพได้

บทสรุป

เราครอบคลุมไม่น้อย! ขอแสดงความยินดีกับการทำให้โปรแกรมแก้ไขอัตโนมัติของคุณเริ่มทำงาน ตัวอย่างที่เราพูดถึงนั้นเรียบง่ายเกินไป แต่คุณสามารถใช้แนวคิดเดียวกันนี้และนำไปใช้กับโมเดลตัวแก้ไขใดๆ ก็ได้

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

วันที่เผยแพร่: 07/30/2019