اساسيات لجداول المحورية Pivot Tables

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



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

الاستهداف Goal Seek

[size]

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

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



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



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

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

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



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



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



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

[/size]

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

[size]

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

[/size]

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

[size]

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

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



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

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



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

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



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



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



[/size]

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

[size]

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

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



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



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

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



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



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



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

• التبليغ عن مقال