تقوم الوظيفة الإضافية Excel Solver بإجراء تحسين رياضي. يستخدم هذا عادةً لتلائم النماذج المعقدة للبيانات أو لإيجاد حلول تكرارية للمشكلات. على سبيل المثال ، قد ترغب في ملاءمة منحنى من خلال بعض نقاط البيانات باستخدام معادلة. يمكن أن يجد Solver الثوابت في المعادلة التي توفر أفضل ملاءمة للبيانات. تطبيق آخر حيث يكون من الصعب إعادة ترتيب نموذج لجعل المخرجات المطلوبة موضوع معادلة.
أين يوجد Solver في Excel؟
الوظيفة الإضافية Solver مضمنة في Excel ولكن لا يتم تحميلها دائمًا كجزء من التثبيت الافتراضي. للتحقق مما إذا كان قد تم تحميله ، حدد علامة التبويب DATA وابحث عن رمز Solver في قسم Analysis
إذا لم تتمكن من العثور على Solver ضمن علامة التبويب DATA ، فستحتاج إلى تحميل الوظيفة الإضافية:
-
حدد علامة التبويب FILE ثم حدد Options.
Image -
في مربع الحوار Options حدد Add-Insمن علامات التبويب الموجودة على الجانب الأيسر.
Image -
في الجزء السفلي من النافذة ، حدد وظائف Excel الإضافية من القائمة المنسدلة إدارة وحدد اذهب …
Image -
حدد خانة الاختيار بجوار الوظيفة الإضافية Solver وحدد OK.
Image -
يجب أن يظهر الأمر Solver الآن في علامة التبويب DATA. أنت جاهز لاستخدام Solver.
Image
استخدام Solver في Excel
لنبدأ بمثال بسيط لفهم ما يفعله Solver. تخيل أننا نريد معرفة نصف القطر الذي سيعطي دائرة مساحتها 50 وحدة مربعة. نحن نعرف معادلة مساحة الدائرة (A=pi r2). يمكننا بالطبع إعادة ترتيب هذه المعادلة لإعطاء نصف القطر المطلوب لمنطقة معينة ، ولكن على سبيل المثال دعنا نتظاهر بأننا لا نعرف كيف نفعل ذلك.
أنشئ جدول بيانات بنصف القطر في B1 واحسب المنطقة في B2 باستخدام المعادلة =pi ()B1 ^ 2.
يمكننا ضبط القيمة يدويًا في B1 حتى يظهر B2قيمة قريبة بدرجة كافية من 50. اعتمادًا على مدى دقتنا يجب أن يكون ، قد يكون هذا نهجًا عمليًا. ومع ذلك ، إذا أردنا أن نكون دقيقين للغاية ، فسوف يستغرق الأمر وقتًا طويلاً لإجراء التعديلات المطلوبة.في الواقع ، هذا هو أساسًا ما يفعله Solver. يقوم بإجراء تعديلات على القيم في خلايا معينة ، ويتحقق من القيمة في الخلية المستهدفة:
- حدد DATA علامة التبويب و Solver ، لتحميل معلمات Solverمربع الحوار
-
تعيين الهدف خلية لتكون المنطقة ، B2. هذه هي القيمة التي سيتم التحقق منها ، وضبط الخلايا الأخرى حتى تصل هذه القيمة إلى القيمة الصحيحة.
Image -
حدد الزر لـ قيمة:وقم بتعيين القيمة 50. هذه هي القيمة التي يجب أن تحققها B2.
Image -
في المربع الذي يحمل العنوان بتغيير الخلايا المتغيرة: أدخل الخلية التي تحتوي على نصف القطر ، B1.
Image -
اترك الخيارات الأخرى كما هي افتراضيًا وحدد حل. يتم إجراء التحسين ، ويتم ضبط قيمة B1 حتى تصبح B2 50 ويتم عرض مربع الحوار Solver Results.
Image -
حدد موافقللحفاظ على الحل.
Image
يوضح هذا المثال البسيط كيفية عمل الحل. في هذه الحالة ، كان من الممكن أن نحصل على الحل بسهولة بطرق أخرى. بعد ذلك سنلقي نظرة على بعض الأمثلة التي يقدم فيها Solver حلولاً يصعب إيجادها بأي طريقة أخرى.
تركيب نموذج معقد باستخدام الوظيفة الإضافية في برنامج Excel Solver
يحتوي Excel على وظيفة مضمنة لإجراء الانحدار الخطي ، وملاءمة خط مستقيم عبر مجموعة من البيانات. يمكن أن تكون العديد من الوظائف غير الخطية الشائعة خطية مما يعني أنه يمكن استخدام الانحدار الخطي لتناسب وظائف مثل الأسي.بالنسبة للوظائف الأكثر تعقيدًا ، يمكن استخدام Solver لتنفيذ "تصغير المربعات الصغرى". في هذا المثال ، سننظر في ملاءمة معادلة بالصيغة ax ^ b + cx ^ dعلى البيانات الموضحة أدناه.
وهذا يتضمن الخطوات التالية:
- رتب مجموعة البيانات باستخدام قيم x في العمود A وقيم y في العمود B.
- قم بإنشاء 4 قيم معامل (أ ، ب ، ج ، د) في مكان ما على جدول البيانات ، يمكن إعطاء هذه القيم بداية عشوائية.
-
قم بإنشاء عمود من قيم Y المجهزة ، باستخدام معادلة من النموذج ax ^ b + cx ^ d والتي تشير إلى المعاملات التي تم إنشاؤها في الخطوة 2 وقيم x في العمود A. لاحظ أنه من أجل نسخ الصيغة لأسفل في العمود ، يجب أن تكون الإشارات إلى المعاملات مطلقة بينما يجب أن تكون المراجع إلى قيم x نسبية.
Image -
على الرغم من أنه ليس ضروريًا ، يمكنك الحصول على مؤشر مرئي لمدى ملاءمة المعادلة من خلال رسم كلا العمودين y مقابل قيم x على مخطط مبعثر XY واحد. من المنطقي استخدام علامات لنقاط البيانات الأصلية ، حيث إنها قيم منفصلة مع ضوضاء ، واستخدام خط للمعادلة الملائمة.
Image -
بعد ذلك ، نحتاج إلى طريقة لقياس الفرق بين البيانات والمعادلة المجهزة. الطريقة القياسية للقيام بذلك هي حساب مجموع تربيع الفروق. في العمود الثالث ، لكل صف ، يتم طرح قيمة البيانات الأصلية لـ Y من قيمة المعادلة المجهزة ، ويتم تربيع النتيجة. لذلك ، في D2 ، يتم إعطاء القيمة بواسطة =(C2-B2) ^ 2ثم يتم حساب مجموع كل هذه القيم المربعة. نظرًا لأن القيم تربيع ، يمكن أن تكون موجبة فقط.
Image -
أنت الآن جاهز لإجراء التحسين باستخدام Solver. هناك أربعة معاملات يجب تعديلها (أ ، ب ، ج ، د). لديك أيضًا قيمة هدف واحدة لتقليل مجموع الاختلافات المربعة. قم بتشغيل برنامج الحل ، كما هو مذكور أعلاه ، وقم بتعيين معلمات الحل للإشارة إلى هذه القيم ، كما هو موضح أدناه.
Image -
قم بإلغاء تحديد الخيار جعل المتغيرات غير المقيدة غير سلبية، فهذا من شأنه أن يجبر جميع المعاملات على أخذ قيم موجبة.
Image -
حدد Solveوراجع النتائج. سيتم تحديث المخطط لإعطاء مؤشر جيد على جودة الملاءمة. إذا لم ينتج الحل عن توافق جيد في المحاولة الأولى ، يمكنك محاولة تشغيله مرة أخرى. إذا تحسن الملاءمة ، فحاول الحل من القيم الحالية.خلاف ذلك ، يمكنك محاولة تحسين الملاءمة يدويًا قبل الحل.
Image - بمجرد الحصول على الملاءمة الجيدة يمكنك الخروج من الحل.
حل نموذج تكراري
في بعض الأحيان توجد معادلة بسيطة نسبيًا تعطي مخرجات من حيث بعض المدخلات. ومع ذلك ، عندما نحاول عكس المشكلة ، لا يمكن إيجاد حل بسيط. على سبيل المثال ، تُعطى الطاقة التي تستهلكها السيارة تقريبًا بواسطة P=av + bv ^ 3حيث v هي السرعة ، و a معامل لمقاومة التدحرج و b هو معامل لـ الديناميكا الهوائية. على الرغم من أن هذه معادلة بسيطة تمامًا ، إلا أنه ليس من السهل إعادة الترتيب لإعطاء معادلة السرعة التي ستصل إليها السيارة لمدخل طاقة معين. ومع ذلك ، يمكننا استخدام Solver لإيجاد هذه السرعة بشكل متكرر. على سبيل المثال ، أوجد السرعة المحققة بمدخل طاقة 740 وات.
-
قم بإعداد جدول بيانات بسيط بالسرعة والمعاملات a و b والقوة المحسوبة منهم.
Image -
قم بتشغيل Solver وأدخل القوة ، B5 ، كهدف. عيّن قيمة موضوعية 740 وحدد السرعة ، B2 ، كخلايا متغيرة لتغييرها. حدد حللبدء الحل.
Image -
يقوم المحقق بضبط قيمة السرعة حتى تقترب القوة من 740 ، مما يوفر السرعة التي نطلبها.
Image - حل النماذج بهذه الطريقة يمكن أن يكون في كثير من الأحيان أسرع وأقل عرضة للخطأ من قلب النماذج المعقدة.
قد يكون فهم الخيارات المختلفة المتاحة في الحل صعبًا للغاية.إذا كنت تواجه صعوبة في الحصول على حل معقول ، فمن المفيد غالبًا تطبيق شروط حدية على الخلايا القابلة للتغيير. هذه هي القيم المحددة التي لا ينبغي تعديلها بعد ذلك. على سبيل المثال ، في المثال السابق ، يجب ألا تقل السرعة عن الصفر وسيكون من الممكن أيضًا تعيين حد أعلى. ستكون هذه سرعة أنت متأكد تمامًا من أن السيارة لا يمكن أن تسير أسرع منها. إذا كنت قادرًا على تعيين حدود للخلايا المتغيرة القابلة للتغيير ، فإنه يجعل أيضًا الخيارات الأخرى الأكثر تقدمًا تعمل بشكل أفضل ، مثل التشغيل المتعدد. سيؤدي هذا إلى تشغيل عدد من الحلول المختلفة ، بدءًا من القيم الأولية المختلفة للمتغيرات.
قد يكون اختيار طريقة الحل أمرًا صعبًا أيضًا. Simplex LP مناسب فقط للنماذج الخطية ، إذا لم تكن المشكلة خطية ، فستفشل برسالة مفادها أن هذا الشرط لم يتم الوفاء به. كلا الطريقتين الأخريين مناسبتان للطرق غير الخطية. GRG Nonlinear هو الأسرع ولكن يمكن أن يعتمد الحل بشكل كبير على ظروف البداية الأولية.إنه يتمتع بالمرونة التي لا تتطلب متغيرات لتعيين الحدود. غالبًا ما يكون الحل التطوري هو الأكثر موثوقية ولكنه يتطلب من جميع المتغيرات أن يكون لها كل من الحدود العلوية والسفلية ، والتي قد يكون من الصعب العمل عليها مسبقًا.
الوظيفة الإضافية Excel Solver هي أداة قوية للغاية يمكن تطبيقها على العديد من المشاكل العملية. للوصول بشكل كامل إلى قوة Excel ، حاول الجمع بين Solver ووحدات ماكرو Excel.
