كيفية أتمتة برنامج Excel Solver

في كيفية الحصول على المزيد من Excel Solver ، قمت بتغطية تقنيات لتحسين نموذجك وتمكين تحليلك. أحد الموضوعات التي تلقيت أكثر الأسئلة حولها هو أتمتة الحل لحل قيم متعددة.

كان هناك شرح موجز في المقال السابق ، لكن لا يوجد شيء مخصص لكيفية تنفيذه من البداية إلى النهاية. في هذه المقالة سوف نتعمق من البداية إلى النهاية في تغطية المفاهيم والأساليب خطوة بخطوة. بحلول النهاية ، ستكون قادرًا على أتمتة أي من النماذج الشخصية الخاصة بك وتوسيع نطاق تحليلك.

كيف سنفعل ذلك

سنستفيد من مسجل الماكرو في Excel للتعامل مع معظم التعليمات البرمجية الفعلية. ستسمح أداة ماكرو التسجيل لبرنامج Excel بتحويل خطواتنا اليدوية إلى كود VBA.

سنحدد بعد ذلك الأجزاء الأساسية من الكود المطلوب تغييرها. ثم سننشئ حلقة لتحديد هذه القيم واستبدالها ديناميكيًا وتشغيل الحل مرة أخرى.

سيكون هذا شبه تقني ولكن من خلال هذا النمط من التطوير يمكننا التركيز على العملية والمفاهيم بدلاً من التركيز على كيفية كتابة الكود.

إعداد مسجل الماكرو

تم العثور على وظيفة تسجيل الماكرو في علامة تبويب المطور في الشريط. هذا غير متاح افتراضيًا ولكن من السهل تنفيذه. إذا انتقلت إلى خيارات الشريط ، فسترى مربع اختيار لعلامة تبويب المطور.

لقطة الشاشة أدناه خاصة بنظام Mac ولكن برنامج Excel المستند إلى الكمبيوتر الشخصي مشابه.

إذا لم تكن قد قمت بتثبيت الوظيفة الإضافية Solver ، فيمكنك تثبيتها عبر قائمة الوظائف الإضافية. يحتوي موقع دعم Microsoft على إرشادات لجميع الأنظمة الأساسية ، قم بتحميل Solver Add-in في Excel.

تسجيل الماكرو

الآن بعد اكتمال الإعداد ، نحن على استعداد للتسجيل! قم بإعداد مساحة العمل الخاصة بك بشكل مناسب مع جميع الصيغ والمراجع الخاصة بك. ستحتاج إلى إعداد كل شيء حتى النقطة قبل إنشاء نموذج الحل.

قم بالعودة إلى علامة تبويب المطور على الشريط. سترى قطعة من الورق بنقطة حمراء. انقر فوق هذا الزر لفتح المسجل.

امنح الماكرو اسمًا واملأ الوصف إذا كنت ترغب في ذلك.

بمجرد أن تضغط على "موافق" ، سيبدأ المُسجل.

انتقل من خلال خطوات إعداد حلالك وحلها. بمجرد اكتمال ذلك ، ارجع إلى علامة تبويب المطور وتوقف عن التسجيل. سيكون نفس موقع الزر الذي استخدمته لبدء التسجيل.

الآن بعد أن أصبح لدينا الكود الأساسي ، يمكننا تنظيفه وإعداده للخطوات التالية.

نظف الكود

الآن يمكننا تحرير الكود وإزالة أي أجزاء غير ضرورية والتأكد من أننا نستخدم فقط الأجزاء الأساسية من تسجيل الماكرو.

يمكنك تحرير الكود من خلال محرر VBA في Excel. للوصول إلى الرمز المسجل الخاص بك ، انقر فوق الزر وحدات الماكرو.

انتقل إلى الماكرو الخاص بك في القائمة الجديدة وانقر فوق تحرير.

الآن بعد أن فتح محرر الكود الخاص بنا ، يمكننا البدء في إجراء التعديلات.

إزالة التعليمات البرمجية غير الضرورية

إذا صادفت النقر فوق مجموعة من الأشياء الأخرى أثناء التسجيل ، فيمكنك مسحها الآن وستترك شيئًا مشابهًا لما يلي. إذا كان هناك الكثير من التعليمات البرمجية الإضافية وكنت قلقًا بشأن كسرها ، فيمكنك دائمًا إعادة التسجيل.

إعداد مراجع حلال

خطوة أخيرة قبل أن ننتقل ، انتقل إلى أدوات> مراجع وحدد Solver. إذا لم تقم بذلك ، فلن يتم تحميل الحزمة في الماكرو وستحصل على خطأ.

يمكنك الآن تغيير المعلمات الخاصة بك ، افتح قائمة وحدات الماكرو ، وحدد الماكرو ، واضغط على تشغيل وسيتم تحديث النتائج.

دعنا نصطف الكود مقابل نموذج الحل الخاص بنا. سترى كيف يصطف. اللغة مختلفة ولكن يمكنك تجميع ما تعنيه كلها معًا.

بعد ذلك ، سنعزل الأجزاء التي نحتاج إلى تغييرها وننهي التشغيل الآلي لدينا.

اشطف وكرر - بناء الحلقة

الآن بعد أن أصبح لدينا حل عمل. نحن بحاجة لتكرار ذلك عدة مرات. دعونا نضع مثالاً معًا ونثبت الخطة.

لقد بدأنا بميزانية قدرها 1000 دولار في النموذج الأول ولكننا نريد أن نفعل الشيء نفسه مقابل زيادة قدرها 100 دولار حتى 2500 دولار.

دعنا نتعمق! لدينا قائمة من القيم في العمود B ومخرجاتنا في العمود C. إذا كتبنا العملية فستبدو ،

1. اضبط إجمالي الإنفاق على القيمة في B10.
2. حل النموذج.
3. ضع الإخراج في C10.
4. اختر قيمة B التالية.
5. اضبط الإخراج في الصف C.
6. كرر الخطوات من 2 إلى 5 لجميع القيم في ب.

بدء الحلقة

الحلقات هي بنيات ترميز تكرر إجراءً ما حتى تصل إلى نقطة نهاية منطقية. ستكون الحلقة مفيدة هنا لتكرار نفس العملية ، وتشغيل الحل ، على كل قيمة حتى نصل إلى نهاية القيمة.

في هذا المثال سنحدد نقطة النهاية بعدد الصفوف. B10: يحتوي B25 على 16 قيمة.

سنبدأ الحلقة ونلفها حول كود الحل الخاص بنا. سنقوم بإعداد متغير ليحمل رقم الحلقة (i) وننتقل عبر كل قيمة ، ونحل الحلقة في كل مرة.

إعادة تعيين الحل

سنضيف شرطًا آخر في بداية الكود الخاص بنا. نظرًا لأننا نريد إعادة تشغيل الحل في كل تكرار ، فسنقوم بإعادة تعيينه في كل حلقة. سيؤدي هذا إلى مسح الإعدادات وبدء نموذج جديد.

يمكننا القيام بذلك عبر SolverReset .

تحديث مراجع الخلايا

نحل الآن ستة عشر مرة ولكننا بحاجة إلى تحديث مراجع الخلايا الخاصة بنا. بينما أشرنا في الأصل إلى B10 ، نحتاج إلى نقل خلية واحدة لأسفل كل مرة لتحديث الحدود. يمكننا القيام بذلك عن طريق وظيفة الإزاحة. يمكننا تحديث "$ B $ 10" للإشارة إلى الخلية ثم تعويضها بـ i من الصفوف (0 في التشغيل الأول ، وصف واحد في الثانية ، وهكذا). في كل مرة يتم تشغيل الحلقة ، أنا أزداد بمقدار واحد.

جمع المخرجات

سيتغير الإخراج في كل مرة لذا سنرغب في حفظه. يمكننا اتباع نفس العملية المذكورة أعلاه ولكن يتم تعويضها من C10 وتعيينها مساوية لقيمة النموذج الذي تم حله. ثم تبدأ الحلقة من جديد.

سنطلق على C10 موازنة العدد المناسب من الصفوف ثم نلصق قيمة مخرجاتنا.

تجنب إشعارات الحل

دعونا ننقذ أنفسنا من الصداع ولا نتعلم بالقدوة للحظة. تذكر عندما قمت بتشغيل برنامج الحلول وظهرت تلك القائمة تسألك عما إذا كنت ترغب في قبول الحل؟ سيظهر ذلك في كل مرة إذا لم تقم بتعطيله. تخيل أنك إذا أعددت حلقة بمئات من القيم ، فسيتعين عليك النقر فوق قبول في كل مرة!

تابع وأضف الأسطر التالية بعد رمز الحل الخاص بك وأضف (TRUE) إلى SolverSolve. سيحاكي هذا قبولك للتغييرات.

الآن لدينا الكود النهائي!

وتشغيلها

يمكننا الآن تشغيل الكود عن طريق تحديده من قائمة الماكرو وتشغيله. إذا كنت ترغب في استخدام المصنف الخاص بك كقالب ، يمكنك تعيين الماكرو إلى زر لسهولة الاستخدام. يمكنك العثور عليها في علامة تبويب المطور.

يمكنك أيضًا إضافة أي رسوم بيانية للمقارنة. في هذه الحالة ، نريد مقارنة معدل نمو الإنفاق مقابل نمو حجم التحويل. هذا المثال ليس مثيرًا للاهتمام ولكن البيانات الحقيقية يمكن أن تساعد في الكشف عن نقاط توقف الكفاءة

استنتاج

غطينا القليل جدا! تهانينا على حصولك على الحل الآلي وتشغيله. كان المثال الذي غطيناه مفرط في التبسيط ، ولكن يمكنك أن تأخذ نفس المفاهيم وتطبيقها على أي نموذج حلال.

يمكن أن يوفر هذا قدرًا هائلاً من الوقت للنماذج التي تحتاج إلى حلها عدة مرات ويشجع المستخدمين على تجربة سيناريوهات مختلفة.

تاريخ النشر الأصلي: 07/30/2019