كيفية استخدام برنامج Excel's Goal Seek and Solver لحل المتغيرات غير المعروفة

Excel قادر جدًا عندما يكون لديك جميع البيانات التي تحتاجها لحساباتك.

لكن ألن يكون من الجميل إذا كان ذلك ممكنًا حل المتغيرات غير المعروفة ؟

باستخدام Goal Seek والوظيفة الإضافية Solver ، يمكن ذلك. وسنوضح لك كيف. تابع القراءة للحصول على دليل كامل حول كيفية حل خلية واحدة باستخدام Goal Seek أو معادلة أكثر تعقيدًا باستخدام Solver.

كيفية استخدام Goal Seek في Excel

تم تضمين Goal Seek بالفعل في Excel. إنه تحت البيانات علامة التبويب في ماذا في التحليل قائمة:

في هذا المثال ، سنستخدم مجموعة بسيطة جدًا من الأرقام. لدينا ثلاثة أرباع أرقام المبيعات وهدف سنوي. يمكننا استخدام Goal Seek لمعرفة الأرقام التي يجب أن تكون في الربع الرابع لتحقيق الهدف.

كما ترى ، إجمالي المبيعات الحالية هو 114706 وحدة. إذا أردنا بيع 250000 بحلول نهاية العام ، فما هو العدد الذي نحتاجه للبيع في الربع الرابع؟ سيخبرنا برنامج Excel's Goal Seek.

إليك كيفية استخدام Goal Seek خطوة بخطوة:

انقر البيانات> تحليل ماذا لو> البحث عن الهدف . سترى هذه النافذة:
ضع جزء 'يساوي' من المعادلة في تعيين الخلية حقل. هذا هو الرقم الذي سيحاول Excel تحسينه. في حالتنا ، هذا هو الإجمالي الحالي لأرقام المبيعات في الخلية B5.
اكتب قيمة الهدف في ملف إلى قيمة حقل. نحن نبحث عن إجمالي 250000 وحدة مباعة ، لذلك سنضع '250.000' في هذا المجال.
أخبر Excel عن المتغير الذي يجب حله في عن طريق تغيير الخلية حقل. نريد أن نرى ما يجب أن تكون عليه مبيعاتنا في الربع الرابع. لذلك سنخبر Excel بحل الخلية D2. سيبدو هكذا عندما يكون جاهزًا للذهاب:
نجاح نعم لحل هدفك. عندما تبدو جيدة ، فقط اضغط نعم . سيُعلمك Excel عندما يجد Goal Seek حلاً.
انقر نعم مرة أخرى وسترى القيمة التي تحل معادلتك في الخلية التي اخترتها عن طريق تغيير الخلية .

في حالتنا ، الحل هو 135294 وحدة. بالطبع ، كان بإمكاننا إيجاد ذلك بطرح الإجمالي الحالي من الهدف السنوي. ولكن يمكن أيضًا استخدام Goal Seek في خلية يحتوي بالفعل على بيانات فيه . وهذا أكثر فائدة.

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

محرك أقراص USB الصلب لا يظهر في نظام التشغيل Windows 10

إذن Goal Seek هو ملف ميزة Excel مفيدة ، ولكن ليس كل هذا مثير للإعجاب. دعنا نلقي نظرة على أداة أكثر إثارة للاهتمام: الوظيفة الإضافية Solver.

ماذا يفعل برنامج Excel Solver؟

باختصار ، يشبه Solver ملف نسخة متعددة المتغيرات من Goal Seek . يأخذ متغير هدف واحد ويضبط عددًا من المتغيرات الأخرى حتى يحصل على الإجابة التي تريدها.

يمكن حلها للحصول على قيمة قصوى لرقم ، أو قيمة دنيا لرقم ، أو رقم محدد.

وهي تعمل ضمن قيود ، لذلك إذا تعذر تغيير متغير واحد ، أو يمكن أن يختلف فقط ضمن نطاق محدد ، فسوف يأخذ Solver ذلك في الاعتبار.

إنها طريقة رائعة لحل العديد من المتغيرات غير المعروفة في Excel. لكن العثور عليها واستخدامها ليس بالأمر السهل.

دعنا نلقي نظرة على تحميل الوظيفة الإضافية Solver ، ثم ننتقل إلى كيفية استخدام Solver في Excel 2016.

كيفية تحميل الوظيفة الإضافية Solver

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

رئيس ل ملف> خيارات> الوظائف الإضافية . ثم انقر فوق يذهب بجوار إدارة: Excel الوظائف الإضافية .

إذا كانت هذه القائمة المنسدلة تقول شيئًا آخر بخلاف 'وظائف Excel الإضافية' ، فستحتاج إلى تغييرها:

في النافذة الناتجة ، سترى بعض الخيارات. تأكد من المربع بجوار الوظيفة الإضافية Solver يتم فحصه وضربه نعم .

سترى الآن ملف حلال زر في التحليلات مجموعة من البيانات التبويب:

إذا كنت تستخدم بالفعل ملف حزمة أدوات تحليل البيانات ، سترى زر تحليل البيانات. إذا لم يكن الأمر كذلك ، فسيظهر Solver بمفرده.

الآن بعد أن قمت بتحميل الوظيفة الإضافية ، دعنا نلقي نظرة على كيفية استخدامها.

كيفية استخدام Solver في Excel

هناك ثلاثة أجزاء لأي إجراء Solver: الهدف والخلايا المتغيرة والقيود. سنستعرض كل خطوة من الخطوات.

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

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

كيفية تعيين القيود في Solver

قد تخبر Excel أن متغيرًا واحدًا يجب أن يكون أكبر من 200. عند تجربة قيم متغيرة مختلفة ، لن يدخل Excel تحت 201 مع هذا المتغير المحدد.

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

فيما يلي المشغلين المتاحين:

<= (اقل او يساوي)
= (يساوي)
=> (أكبر من أو يساوي)
int (يجب أن يكون صحيحا)
صباحا (يجب أن يكون إما 1 أو 0)
كلها مختلفة

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

أخيرًا ، أضف قيمة القيد.

من المهم أن تتذكر أنه يمكنك ذلك حدد خلايا متعددة لمرجع الخلية. إذا كنت تريد أن تحتوي ستة متغيرات على قيم تزيد عن 10 ، على سبيل المثال ، يمكنك تحديدها جميعًا وإخبار Solver بأنها يجب أن تكون أكبر من أو تساوي 11. ليس عليك إضافة قيد لكل خلية.

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

مثال على Solver

لمعرفة كيفية عمل كل هذا ، سنستخدم الوظيفة الإضافية Solver لإجراء عملية حسابية سريعة. ها هي البيانات التي بدأنا بها:

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

فيما يلي القيود التي سنستخدمها:

لا وظائف يمكن أن تقل عن أربع ساعات.
يجب أن تكون الوظيفة 2 أكثر من ثماني ساعات .
يجب أن تكون الوظيفة 5 أقل من إحدى عشرة ساعة .
يجب أن يكون إجمالي ساعات العمل يساوي 40 .

قد يكون من المفيد كتابة قيودك مثل هذه قبل استخدام Solver.

إليك كيفية إعدادنا لذلك في Solver:

أولا ، لاحظ ذلك لقد قمت بإنشاء نسخة من الجدول لذلك لا نقوم بالكتابة فوق الأصل الذي يحتوي على ساعات العمل الحالية لدينا.

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

لنضرب يحل ونرى ما سيحدث.

وجد Solver حلاً! كما ترى على يسار النافذة أعلاه ، زادت أرباحنا بمقدار 130 دولارًا. وقد تمت تلبية جميع القيود.

هل يجب أن أقوم بتعطيل مدافع الويندوز إذا كان لدي أفاست

للحفاظ على القيم الجديدة ، تأكد احتفظ بحل Solver يتم فحصه وضربه نعم .

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

يتم إنشاء التقارير في أوراق جديدة في المصنف الخاص بك وتمنحك معلومات حول العملية التي مرت بها الوظيفة الإضافية Solver للحصول على إجابتك.

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

خيارات Solver المتقدمة

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

الطريقة الأكثر وضوحًا هي طريقة الحل:

يمكنك الاختيار بين GRG Nonlinear و Simplex LP و Evolutionary. يوفر Excel شرحًا بسيطًا بشأن متى يجب عليك استخدام كل واحد. يتطلب التفسير الأفضل بعض المعرفة بالإحصاءات والانحدار.

لضبط الإعدادات الإضافية ، ما عليك سوى الضغط على خيارات زر. يمكنك إخبار Excel حول أمثلية الأعداد الصحيحة ، وتعيين قيود وقت الحساب (مفيد لمجموعات البيانات الضخمة) ، وضبط كيفية قيام أساليب حل GRG و Evolutionary بإجراء حساباتهم.

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

Goal Seek and Solver: أخذ Excel إلى المستوى التالي

الآن بعد أن أصبحت مرتاحًا لأساسيات حل المتغيرات غير المعروفة في Excel ، فإن عالمًا جديدًا تمامًا من حساب جداول البيانات مفتوح لك.

يمكن أن يساعدك Goal Seek في توفير الوقت من خلال إجراء بعض العمليات الحسابية بشكل أسرع ، ويضيف Solver قدرًا هائلاً من الطاقة إليه قدرات حساب Excel .

إنها مجرد مسألة الراحة معهم. كلما زاد استخدامك لها ، زادت فائدتها.

هل تستخدم Goal Seek أو Solver في جداول البيانات الخاصة بك؟ ما هي النصائح الأخرى التي يمكنك تقديمها للحصول على أفضل الإجابات منها؟ شارك بأفكارك في التعليقات أدناه!

يشارك يشارك سقسقة بريد الالكتروني 5 نصائح لزيادة كفاءة أجهزة VirtualBox Linux الخاصة بك

هل سئمت من الأداء الضعيف الذي تقدمه الأجهزة الافتراضية؟ إليك ما يجب عليك فعله لتعزيز أداء VirtualBox الخاص بك.

اقرأ التالي مواضيع ذات صلة

إنتاجية
جدول
مايكروسوفت اكسل
نصائح مايكروسوفت أوفيس

نبذة عن الكاتب ثم أولبرايت(تم نشر 506 مقالات)

Dann هو إستراتيجية محتوى واستشاري تسويق يساعد الشركات على توليد الطلب والعملاء المتوقعين. كما أنه يكتب مدونات حول الإستراتيجية وتسويق المحتوى على dannalbright.com.

المزيد من Dann Albright

اشترك في نشرتنا الإخبارية

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

انقر هنا للاشتراك