كيفية استخدام تحليلات ماذا-لو What-if Analysis للتنبؤ بالتغييرات التي تحدث على البيانات في Microsoft Excel


Huda AlMashta

تحليلات ماذا-لو هي من الخصائص الرائعة والمفيدة لعمل توقعات دقيقة ومعرفة التأثيرات التي تحدث على النتائج نتيجة تغيير في قيم المدخلات.

excel-what-if-analysis.png.f35db72c24318

تندرج تحت تحليلات ماذا-لو في اكسل ثلاث أدوات؛ السيناريوهات Scenarios وجداول البيانات Data Tables التي تتشابه من حيث مبدأ العمل. حيث تحدد النتائج المتوقعة بصورة دقيقة اعتمادا على مجموعة من المدخلات. والأداة الثالثة هي الاستهداف Goal Seek التي يعاكس عملها عمل الأداتين سابقتي الذكر. حيث تقوم بتحديد قيم المدخلات اعتمادا على نتيجة معيّنة.

الاستهداف Goal Seek

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

مثال: لنفترض أننا نعمل على عدد من المشاريع خلال الشهر:

56c5a868268d8_1-goalseek1.png.f599123353

وفي هذا الشهر هدفنا تحقيق ربح مقداره 1750$. المطلوب هو معرفة مقدار الربح الذي يجب أن نحققه من "المشروع 10". عند استخدام الاستهداف، نحتاج إلى تحديد خلية تحتوي على الصيغة أو الدالة التي يتم التنبؤ بالمدخلات على أساسها. سنستخدم هنا دالة الجمع SUM لإيجاد إجمالي الربح لجميع المشاريع (من ضمنها "المشروع 10" حتى وإن كانت قيمته مجهولة، وسيتم اعتبارها صفر لأنّ الخلية B10) فارغة:

56c5a868f0b4e_2-.png.cb5bf97c2453d571d26

كما يظهر من نتيجة الجمع أننا حققنا لحد الآن ربحا مقداره 1537$، لكن ما مقدار الربح الذي يجب تحقيقه من المشروع العاشر للوصول إلى 1750$؟ سنستخدم الاستهداف لمعرفة ذلك.

نُحدّد الخلية التي نريد تغيير قيمتها، وهي الخلية B11 في هذا المثال التي تحتوي على المجموع الذي نريده تغييره إلى 1750$، ثم نذهب إلى تبويب:

بيانات Data > تحليل ماذا لو What if analysis > الاستهداف Goal Seek

56c5a86a9507c_3-.thumb.png.6e0f552ba5946

في حقل تعيين الخلية Set Cell، نحدد الخلية التي تحتوي على الصيغة التي نريد تحليلها، وهي الخلية B11 التي حددنها في الخطوة السابقة. في حقل إلى القيمة To Value نحدد القيمة التي نريد أن تقوم الصيغة بإرجاعها، أي الهدف، وهي 1750 في هذا المثال. وفي حقل بتغيير الخلية By changing cell نحدد الخلية التي نريد تغيير قيمتها لحين الوصول إلى الهدف، وهي الخلية B10 في هذا المثال:

56c5a86c1df0d_4-.thumb.png.d41bdffc0645e

بعد أن ننقر موافق OK، سيقوم البرنامج تحليل الصيغة وتجربة عدة قيم حتى يصل إلى القيمة المستهدفة. وكما يبدو من نتيجة هذا المثال، نحتاج إلى تحقيق ربح مقداره 213$ من المشروع العاشر كي يصبح إجمالي الربح لهذا الشهر مساوٍ لـ 1750$:

56c5a86d28c57_5-.png.432919a6f00ca04815b

ننقر موافق OK لحفظ النتيجة، أو إلغاء Cancel لإلغاء عملية الاستهداف.

جداول البيانات Data Tables

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

جداول البيانات بمتغير واحد

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

مثال: لقد تم إيجاد مقدار العمولة بضرب النسبة المئوية للعمولة في المبيعات. المطلوب هو حساب العمولة إذا تغيّرت نسبة العمولة حسب القيم المدخلة (10%، 11%، 12%...)

56c5a872a629b_6--1.png.8c581e76d73a17bc2

نلاحظ في شريط الصيغة أنّ الصيغة تحتوي على متغيّرين؛ B3 وهي المبيعات، وB4 وهي نسبة العمولة، لكن متغيّرا واحدا هو الذي سنقوم بتحليله، وهو نسبة العمولة.

سنقوم أولا بنسخ الصيغة إلى الخلية B8 بجعلها مساوية للخلية B5 التي تحتوي على الصيغة الأصلية:

56c5a8738e42e_7-.png.c722b61303baa551365

نقوم بتحديد الجدول في ورقة العمل ثم نذهب إلى تبويب:

بيانات Data > تحليلات ماذا-لو What-if analysis > جداول البيانات Data Table

56c5a875effa2_8-.thumb.png.421f31abaa91f

ما نريده هنا هو استبدال قيم نسبة العمولة في الخلية B4 بالقيم الموجودة في الخلايا من A8 إلى A14، لذلك سنحدد هذه الخلية (B4) في حقل Column input cell:

56c5a8770f313_9-columninputcell.png.3c66

ننقر موافق OK وسنحصل على النتائج بلمحة!

56c5a877926f7_10-.png.994aa0cd4c7c8b4692

جداول البيانات بمتغيرين

بنفس مبدأ عمل الجداول ذات المتغير الواحد، تُستخدم لمعرفة التأثير على النتائج الذي يحدث عند تغيّر قيمة متغيّرين في الصيغة.

مثال: في الجدول أدناه نريد معرفة قيمة العمولة فيما إذا تغيّرت قيم نسبة العمولة والمبيعات أيضا:

56c5a884221ea_11-.thumb.png.654d2afbe3d3

سنقوم بنسخ الصيغة من الخلية B5 إلى الخلية التي تمثل تقاطع صف المبيعات وعمود نسبة العمولة (A8):

56c5a885264ea_12-.thumb.png.cd9636cb9a70

نحدد الخلايا من A8 حتى الخلية K14، ثم نذهب إلى تبويب:

بيانات Data > تحليلات ماذا-لو What-if analysis > جداول البيانات Data Table

56c5a887af2af_13-.thumb.png.b21b1cd13dc3

في حقل Row input table سنحدد الخلية B3 لأنّنا نريد استبدال القيم في هذه الخلية بقيم المبيعات في الصف 8. وفي حقل Column input table سنحدد الخلية B4 لأننا نريد استبدال القيم في هذه الخلية بقيم نسبة العمولة في العمود A:

56c5a8890b7c7_14-.thumb.png.f51f0ec44754

ننقر موافق OK وسيمتلئ الجدول على الفور:

56c5a88aa96f8_15-.thumb.png.dfc3616051a8

ملاحظة: جداول البيانات تعمل مع متغيّرين فقط، لكن تحلل العدد الذي تريده من قيم هذه المتغيّرات. أما السيناريوهات فيمكن أن تعمل مع أي عدد من المتغيّرات لكن لا تستوعب سوى 32 من قيم هذه المتغيّرات.





تفاعل الأعضاء


السلام عليكم هل يمكن الحصول على ملفات العمل لغرض التطبيق ؟ 

شارك هذا التعليق


رابط هذا التعليق
شارك على الشبكات الإجتماعية
بتاريخ On 2/11/2017 at 05:45 قال Ahmed Atif:

السلام عليكم هل يمكن الحصول على ملفات العمل لغرض التطبيق ؟ 

وعليكم السلام ورحمة الله وبركاته.

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

شارك هذا التعليق


رابط هذا التعليق
شارك على الشبكات الإجتماعية


يجب أن تكون عضوًا لدينا لتتمكّن من التعليق

انشاء حساب جديد

يستغرق التسجيل بضع ثوان فقط


سجّل حسابًا جديدًا

تسجيل الدخول

تملك حسابا مسجّلا بالفعل؟


سجّل دخولك الآن