حل وظيفة في Excel. تفعيل ، استخدام الحالة مع لقطات الشاشة

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

ما هو البحث عن حلول

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

كيفية تشغيل ميزة "البحث عن حل"

على الرغم من الفعالية ، فإن الخيار المعني ليس في مكان بارز على شريط الأدوات أو قائمة السياق. معظم المستخدمين الذين يعملون في Excel لا يدركون وجوده. بشكل افتراضي ، يتم تعطيل هذه الوظيفة ، لعرضها ، قم بتنفيذ الإجراءات التالية:

1. افتح "ملف" من خلال النقر على الاسم المناسب.
2. انقر فوق قسم "الإعدادات".
3. ثم حدد القسم الفرعي "الوظائف الإضافية". سيتم عرض جميع الوظائف الإضافية للبرنامج هنا ، وسيظهر نقش "الإدارة" أدناه. على الجانب الأيمن منها ستكون هناك قائمة منبثقة حيث يجب عليك تحديد "Excel Add-ins". ثم انقر فوق "انتقال".
   

   

4. سيتم عرض نافذة إضافية "الوظائف الإضافية" على الشاشة. حدد المربع بجوار الوظيفة المطلوبة وانقر فوق "موافق".
5. ستظهر الوظيفة المطلوبة على الشريط الموجود على يمين قسم "البيانات".

حول النماذج

ستكون هذه المعلومات مفيدة للغاية لأولئك الذين هم فقط على دراية بمفهوم "نموذج التحسين". قبل استخدام "البحث عن حل" ، يوصى بدراسة المواد الخاصة بأساليب بناء النماذج:

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

مهام التحسين النموذجية:

• تحديد خطة الإنتاج ، التي يكون خلالها الربح من بيع البضائع المحررة هو الحد الأقصى.
• تحديد خرائط النقل ، والتي يتم خلالها تقليل تكاليف النقل إلى الحد الأدنى.
• ابحث عن توزيع عدة ماكينات لأنواع مختلفة من العمل حتى يتم تقليل تكاليف الإنتاج.
• تحديد أقصر وقت لإنجاز العمل.

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

المرحلة التحضيرية

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

ستكون مهمتنا هي حساب الخصم الذي يتم بموجبه مضاعفة مبالغ بيع المنتجات المختلفة. يرتبط هذان العنصران بصيغة مكتوبة على النحو التالي: = D13 * $ G $ 2. حيث يتم كتابة الكمية الإجمالية للتنفيذ في D13 ، و $ G $ 2 هو عنوان العنصر المطلوب.

استخدام الوظيفة وإعدادها

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

1. تحتاج إلى التبديل إلى قسم "البيانات" والنقر فوق "البحث عن حل".

2. سيتم فتح "الخيارات" ، حيث يتم تعيين الإعدادات المطلوبة. في السطر "تحسين وظيفة الهدف": يجب تحديد الخلية حيث يتم عرض مجموع الخصومات. من الممكن وصف الإحداثيات بنفسك أو الاختيار من المستند.

3. بعد ذلك ، عليك الانتقال إلى إعدادات المعلمات الأخرى. في قسم "إلى:" ، من الممكن تعيين الحد الأقصى والحد الأدنى أو رقم دقيق.

4. ثم يتم ملء الحقل "تغيير قيم المتغيرات:". هنا يتم إدخال بيانات الخلية المرغوبة ، والتي تحتوي على قيمة محددة. يتم تسجيل الإحداثيات بشكل مستقل أو يتم النقر فوق الخلية المقابلة في المستند.

5. ثم يتم تحرير علامة التبويب "وفقًا للقيود:" ، حيث يتم تعيين القيود على البيانات المطبقة. على سبيل المثال ، يتم استبعاد الكسور العشرية أو الأرقام السالبة.

6. بعد ذلك ، تفتح نافذة تسمح لك بإضافة قيود في العمليات الحسابية. يحتوي السطر الأولي على إحداثيات خلية أو نطاق كامل. وفقًا لشروط المهمة ، تتم الإشارة إلى بيانات الخلية المطلوبة ، حيث يتم عرض مؤشر الخصم. ثم يتم تحديد علامة المقارنة. يتم تعيينه على "أكبر من أو يساوي" بحيث لا تكون القيمة النهائية بعلامة ناقص. "الحد" المحدد في السطر 3 هو 0 في هذه الحالة. من الممكن أيضًا وضع حد مع "إضافة". الخطوات التالية هي نفسها.

7. عند اكتمال الخطوات المذكورة أعلاه ، يظهر الحد المعين في السطر الأكبر. يمكن أن تكون القائمة كبيرة وستعتمد على مدى تعقيد العمليات الحسابية ، ومع ذلك ، في حالة معينة ، يكون الشرط 1 كافيًا.

8. بالإضافة إلى ذلك ، من الممكن تحديد الإعدادات المتقدمة الأخرى. يوجد في الجانب الأيمن السفلي خيار "خيارات" يتيح لك القيام بذلك.

9. في الإعدادات ، يمكنك تعيين "دقة التحديد" و "حدود الحل". في حالتنا ، ليست هناك حاجة لاستخدام هذه الخيارات.

10. عند اكتمال الإعدادات ، تبدأ الوظيفة نفسها - انقر فوق "البحث عن حل".

11. بعد أن يقوم البرنامج بإجراء الحسابات المطلوبة وإصدار الحسابات النهائية في الخلايا المطلوبة. ثم يتم فتح نافذة بالنتائج ، حيث يتم حفظ / إلغاء النتائج ، أو يتم تكوين معلمات البحث وفقًا لمعامل جديد. عندما تفي البيانات بالمتطلبات ، يتم حفظ الحل الموجود. إذا حددت مربع حوار "الرجوع إلى خيارات البحث عن الحلول" مقدمًا ، فسيتم فتح نافذة بها إعدادات الوظيفة.

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

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

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

14. عند استلام الخصم المطلوب ، يبقى تطبيقه لحساب مقدار الخصومات لكل عنصر. لهذا الغرض ، تم تمييز العنصر الأولي للعمود "مبلغ الخصم" ، تتم كتابة الصيغة «= D2 * $ G $ 2» واضغط على "دخول". يتم وضع علامات الدولار بحيث لا تتغير G2 عندما تمتد المعادلة إلى الخطوط المجاورة.

15. سيتم الآن الحصول على مبلغ الخصم للعنصر الأولي. ثم يجب عليك تحريك المؤشر فوق زاوية الخلية ، عندما يصبح "زائد" ، يتم الضغط على LMB وتمتد الصيغة إلى الخطوط المطلوبة.
16. بعد ذلك ، ستكون الطاولة جاهزة أخيرًا.

تحميل / حفظ خيارات البحث

هذا الخيار مفيد عند تطبيق خيارات القيد المتنوعة.

1. في قائمة "خيارات الباحث عن الحلول" ، انقر فوق تحميل / حفظ.
2. أدخل النطاق الخاص بمنطقة النموذج وانقر فوق حفظ أو تحميل.

عند حفظ النموذج ، يتم إدخال مرجع إلى خلية واحدة لعمود فارغ حيث سيتم وضع نموذج التحسين. أثناء تحميل النموذج ، يتم إدخال مرجع إلى النطاق الكامل الذي يحتوي على نموذج التحسين.

المهم! لحفظ آخر الإعدادات في قائمة "خيارات الحل" ، يتم حفظ المصنف. تحتوي كل ورقة فيها على خيارات Solver الإضافية الخاصة بها. بالإضافة إلى ذلك ، من الممكن تعيين أكثر من مهمة واحدة للورقة بالنقر فوق الزر "تحميل أو حفظ" من أجل حفظ المهام الفردية.

مثال بسيط على استخدام Solver

من الضروري تحميل الحاوية بالحاويات بحيث تكون كتلتها القصوى. الخزان حجمه 32 متر مكعب. م. صندوق ممتلئ بوزن 20 كجم ، حجمه 0,15 متر مكعب. م. صندوق - 80 كجم و 0,5 متر مكعب. م. يجب ألا يقل العدد الإجمالي للحاويات عن 110 قطعة. يتم تنظيم البيانات على النحو التالي:

تم تمييز متغيرات النموذج باللون الأخضر. يتم تمييز الوظيفة الموضوعية باللون الأحمر. القيود: أصغر عدد من الحاويات (أكبر من أو يساوي 110) وبالوزن (= SUMPRODUCT (B8: C8، B6: C6) - الوزن الإجمالي الفارغ في الحاوية.

عن طريق القياس ، نعتبر الحجم الإجمالي: = SUMPRODUCT (B7: C7، B8: C8). هذه الصيغة ضرورية لوضع حد للحجم الإجمالي للحاويات. بعد ذلك ، من خلال "البحث عن حل" ، يتم إدخال الروابط إلى العناصر ذات المتغيرات والصيغ والمؤشرات نفسها (أو روابط إلى خلايا معينة). بالطبع ، عدد الحاويات عدد صحيح (وهو أيضًا قيد). نضغط على "البحث عن حل" ، ونتيجة لذلك نجد مثل هذا العدد من الحاويات عندما تكون الكتلة الإجمالية القصوى ويتم أخذ جميع القيود في الاعتبار.

البحث عن حل فشل في إيجاد الحلول

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

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

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

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

في المثال أعلاه ، مؤشر الحجم الأقصى هو 16 مترًا مكعبًا. م بدلاً من 32 ، لأن مثل هذا التقييد يتعارض مع مؤشرات الحد الأدنى لعدد المقاعد ، لأنه سيتوافق مع عدد 16,5،XNUMX متر مكعب. م.

وفي الختام

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