اساسيات لجداول المحورية Pivot Tables
صفحة 1 من اصل 1
اساسيات لجداول المحورية Pivot Tables
من طرف baty السبت ديسمبر 02, 2023 1:17 am
من الرائع في برنامج اكسل أنّه يتيح إمكانية إدخال الكمية التي تريدها من البيانات مهما كانت كبيرة لتملأ الملايين من الخلايا، وكذلك إمكانية عرض هذه البيانات بالعديد من الطرق المختلفة. لكن في جداول البيانات الكبيرة جدا سيكون من الصعب تحليل جميع المعلومات في الورقة، وهنا يأتي دور الجداول المحورية Pivot Tables لتساعدك في تنظيم بياناتك، تلخيصها، وتحليلها.
كما هو واضح من الاسم، الجداول المحورية هي جداول أيضا تحتوي على أعمدة وصفوف، لكنها توفّر خاصية التلاعب بالبيانات وترتيبها بعدة طرق بسرعة وسهولة.
على سبيل المثال جدول البيانات أدناه:
لو أردنا معرفة قيم المبيعات لكل "صنف"، يمكننا تنظيم البيانات وتلخيصها البيانات باستخدام الفرز والتصفية Sort & Filter أو المجاميع الفرعية Subtotals، لكن هذا في حالة كون الجدول صغيرا.
والحقيقة هي أنّ هذا الجدول يحتوي على أكثر من 1400 صف مما يجعل عملية تلخيص البيانات صعبة بدون استخدام الجداول المحورية:
عندما نقوم بتحويل البيانات إلى جدول محوري سيكون بإمكاننا التركيز على ملخص الجدول بدلا من التعامل مع الكم الهائل من البيانات ككل:
نلاحظ في الصورة أعلاه كيف تحوّل الجدول المتكون من 1403 صف إلى جدول مبسّط بأقل من 20 صف. يتكونّ الجدول المحوري من 4 مناطق: منطقة الأعمدة Columns، منطقة المرشحات (عوامل التصفية) Filters، منطقة الصفوف Rows، ومنطقة القيم.
يمكننا التحكم بالبيانات التي تظهر في كل منطقة من هذه المناطق بكل سهولة. فإذا أردنا أن نقوم بعرض مجموع المبيعات حسب الربع في منطقة الصفوف، ونقل الأشهر إلى منطقة المرشحات، ببساطة ننقر على حقل "الربع" ونسحبه من منطقة المرشحات إلى منطقة الصفوف. وبالمثل ننقر على حقل "شهر الطلب" ونسحبه من منطقة الصفوف إلى منطقة المرشحات، وبذلك نحصل على طريقة مختلفة لتحليل بياناتنا:
كما يمكننا التحكم في الحقول fields التي نرغب في إظهارها/إخفائها في الجدول بتأشير اسم الحقل من قسم الحقول الجانبي (أسماء الحقول هي نفسها عناوين الأعمدة للجدول الأصلي الذي قمنا بتحويله إلى جدول محوري):
وللتركيز على جزء أصغر من البيانات لتحليلها بشكل مفصّل يمكننا استخدام أمر التصفية، أو ما يُسمى بمقسم طريقة العرض Slicers والتي سنأتي إلى شرحها لاحقا في هذا المقال.
إذا فالجداول المحورية هي طريقة أكثر مرونة لعرض البيانات يمكن إعادة هيكلتها بسهولة مع العديد من الخيارات التي تمكننا من تنظيم تلك البيانات، تلخيصها وتحليلها بشكل سهل وسريع.
كيفية إنشاء الجداول المحورية
لنعد إلى الجدول الأصلي؛ مجموعة من الأعمدة والصفوف التي تعرض قيم المبيعات لعدد من الأصناف وحسب الأشهر وأرباع السنة. لتحويل هذا الجدول إلى جدول محوري ننقر على إحدى الخلايا داخل الجدول ثم نذهب إلى تبويب إدراج Insert:
في خانة جداول Tables نلاحظ وجود أمرين لإنشاء الجدول المحوري: PivotTable و Recommended PivotTable.
سننقر الأمر الأول (PivotTable) لإنشاء الجدول (أو يمكننا استخدام الاختصار Alt+N+V). سيظهر مربع الحوار التالي:
نؤشر الخيار تحديد جدول أو نطاق Select a table or range ونحدد الجدول (أو جزء منه) الذي نريد تحويله إلى جدول محوري (وهو محدد تلقائيا هنا لأننا قمنا بتحديد خلية داخله مسبقا).
إذا رغبنا في استيراد بيانات من ملف خارجي، كقاعدة بيانات Access مثلا، نحدد الخيار استخدام مصدر بيانات خارجي Use an external data source.
وكذلك لدينا خيار إنشاء الجدول في ورقة عمل جديدة بتحديد الخيار New Worksheet أو إنشائه في الورقة الحالية Existing Worksheet وتحديد الخلية التي نريده أن يبدأ منها من حقل Location.
يُحدد الخيار الأخير، إضافة هذه البيانات إلى نموذج البيانات Add this data to the data model، إذا كانت البيانات علائقية Relational (أي مقسمة على عدة جداول)، وفي هذه الحالة يمكن إضافة الجداول المتعددة إلى نموذج بيانات في اكسل، إنشاء علاقات بينها، ثم استخدام نموذج البيانات لإنشاء PivotTable.
بعد تحديد جميع الخيارات المرغوبة، ننقر على موافق OK. في الورقة الجديدة ستتم إضافة placeholder للجدول المحوري بالإضافة إلى لوحة جانبية تحتوي على جميع الحقول الموجودة التي يمكن إضافتها إلى الجدول، ومصدرها هو الجدول الأصلي.
سنقوم ببناء الجدول بسحب الحقول التي نرغب في إضافتها إلى المنطقة المرغوبة. فإذا أردنا تصفية الجدول ككل حسب الأشهر، سنقوم بسحب حقل "شهر الطلب" إلى منطقة Filters، وستتم إضافة عامل التصفية مباشرة إلى الورقة:
وسنقوم بسحب حقل "الربع" إلى منطقة الأعمدة وحقل "الصنف" إلى منطقة الصفوف:
نلاحظ أن الجدول ما زال فارغا، وذلك لأننا لم نحدد بعد الحقل الذي نضعه في منطقة القيم. كما نلاحظ أن البرنامج يقوم بإضافة صف وعمود الإجمالي الكلي Grand Total تلقائيا. سنقوم بسحب "المبيعات" إلى منطقة القيم:
يمكننا في أي وقت تغيير هيكلية الجدول بسحب الحقول ونقلها إلى المكان الآخر، وهذا ما يميز الجدول المحوري؛ المرونة التامة في التحكم طريقة عرض البيانات.
الطريقة الأخرى لإنشاء الجدول المحوري هو أمر Recommended PivotTable الذي يقترح مجموعة من الخيارات المناسبة لبياناتك. يمكنك استخدام هذا الأمر إذا لم تكن ترغب في ببناء الجدول يدويا، أو إذا لم تكن تعرف بالضبط كيف تعرض بياناتك بالشكل المناسب. بنفس الطريقة، نحدد خلية داخل جدول البيانات العادي ثم ننقر على Recommended PivotTable:
في مربع الحوار تُعرض العديد من طرق العرض المختلفة للبيانات. انقر على المصغرات لمعاينتها بصورة أكبر في جزء المعاينة. عندما تقرر الشكل المناسب لك، انقر على موافق OK.
سيتم إنشاء الجدول المحوري في ورقة جديدة:
في الشكل الذي اخترناه، يُعرض حقلا "ربع" و "شهر الطلب" كصفوف، وعدد الطلبات في منطقة القيم. ولا توجد حقول في منطقتي الأعمدة والمرشحات (وهذا يعني أنّه ليس بالضرورة استخدام جميع المناطق وإضافة الحقول إليها). يمكننا مواصلة العمل على هذا الترتيب أو تغييره يدويا حسب ما يناسبنا.
تنسيق الجداول المحورية
بعد أن تعرّفنا على كيفية إنشاء الجداول المحورية وكيفية التلاعب في هيكلية الجدول بنقل الحقول من منطقة إلى أخرى، لنستعرض المزيد من الخيارات الخاصة بتنسيق الجدول المحوري.
عندما ننقر على أي خلية داخل الجدول سنلاحظ ظهور التبويبين السياقيين تصميم Design وتحليل Analyze:
يحتوي تبويب تصميم على العديد من الخيارات التنسيقية، منها مجموعة أنماط PivotTable Styles تشابه أنماط الجداول العادية. نمرر الفأرة فوق أي من هذه الأنماط لعرض معاينة مباشرة، وعندما نحدد النمط المناسب ننقر عليه لاختياره:
كما يحتوي على خيار تمييز الصفوف (أو الأعمدة) بتنسيق خاص لتسهيل تمييز البيانات ومتابعتها. نحدد أحد الخيارين Banded Rows أو Banded Columns لتطبيق التنسيق الخاص:
وبإمكاننا تطبيق تنسيق خاص لعناوين الصفوف/ الأعمدة بتأشير الخيارين Row Headers/ Column Headers:
وإذا كنا نفضّل المزيد من المساحات البيضاء في الجدول يمكننا إضافة صفوف فارغة تحت كل مجموعة عناصر من خيار Insert Blank Line after Each Item:
كما أنّ هناك المزيد من الخيارات الخاصة بتخطيط الجدول يمكننا الوصول إليها من أمر تخطيط التقرير Report Layout:
في النموذج المضغوط Show in Compact Form (وهو التخطيط الافتراضي) تُعرض عناصر الحقول المختلفة في منطقة الصفوف في عمود واحد (مثلا، في الجدول أدناه تُعرض عناصر "الصنف" و "شهر الطلب" في عمود A)، كما تكون عناوين الصفوف والأعمدة عمومية ("Row Labels" و "Column Labels"):
بالإمكان استخدام النموذج المضغوط إذا كنا بحاجة إلى المزيد من المساحة لعرض القيم. وكذلك بالإمكان استخدام أيقونات +/- عند عناوين الصفوف لعرض/إخفاء التفاصيل المندرجة تحت كل عنوان:
وفي نموذج مخطط تفصيلي Show in Outline Form يتم تخصيص عمود لكل حقل من حقول منطقة الصفوف (مثلا، في الجدول أدناه خصص العمود A لحقل "الصنف" وعمود B لحقل "شهر الطلب)، كما تُعرض تسميات الحقول الحقيقة (مثلا، "الصنف" بدلا من "Row Labels" و"شهر الطلب" بدلا من "Column Labels"):
أما الخيار الأخير، النموذج الجدولي Show in Tabular Form، فهو يشابه النموذج السابق تقريبا، ولكنه يعرض قيم المجاميع (Total) أسفل المجموعات وليس أعلاها:
يمكننا اختيار إي نموذج من النماذج المتاحة، فالأمر يعتمد على التفضيل الشخصي للمستخدم.
من الخيارات الأخرى المتوفرة إمكانية إخفاء قيم الإجمالي الكلي Grand Total للصفوف والأعمدة Off for rows and columns، أو إظهارها للصفوف فقط On for rows only أو للأعمدة فقط On for columns only:
وكذلك إمكانية إخفاء قيم المجاميع الفرعية Subtotals، أو تحديد موضع عرضها في أسفل مجموعات العناصر Show all subtotals at bottom of group أو أعلاها Show all subtotals at top of group:
بالإضافة إلى ذلك، يمكننا الوصول إلى بعض الخيارات التنسيقية من تبويب تحليل Analyze. مثلا، في الجدول أدناه، يمكننا تغيير تنسيق مجموع المبيعات في منطقة القيم بإضافة رمز العملة.
أولا، نحدد إحدى الخلايا التي تحتوي على مجموع المبيعات، ونتأكد من أننا حددنا الحقل المطلوب من مربع الحقل النشط Active Field، ثم ننقر على إعدادات الحقل Field Settings:
نلاحظ في مربع الحقل النشط أنّ الحقل المحدد هو حقل "المبيعات Sum of" وهو ما نريده.
من مربع الحوار "إعدادات حقل القيم" يمكننا إدخال اسم مخصص للحقل Custom Name، ويفضّل أن يكون مختلفا عن اسم المصدر Source Name لكيلا تختلط أسماء الحقول.
كما يمكننا تغيير الدالة التي نريد استخدامها لتلخيص البيانات. الدالة الافتراضية هي الجمع Sum، ونستطيع استخدام الدوال الأخرى كالمتوسط، الضرب، إلخ:
ولتغيير تنسيق الأرقام في منطقة القيم، ننقر على تنسيق الرقم Number Format. في مربع الحوار تنسيق الخلايا ننقر على الخيار عملة Currency، ثم نحدد نوع العملة وعدد الفواصل العشرية في الرقم، ثم موافق OK:
وسيتم تنسيق الأرقام في منطقة القيم بصيغة عملة:
سنتابع في الدرس القادم كيفية إنشاء وتنسيق المخططات المحورية في برنامج اكسل.
baty- Admin
- المساهمات : 22
تاريخ التسجيل : 01/06/2021
رد: اساسيات لجداول المحورية Pivot Tables
من طرف baty السبت ديسمبر 02, 2023 1:18 am
تحليلات ماذا-لو هي من الخصائص الرائعة والمفيدة لعمل توقعات دقيقة ومعرفة التأثيرات التي تحدث على النتائج نتيجة تغيير في قيم المدخلات.
تندرج تحت تحليلات ماذا-لو في اكسل ثلاث أدوات؛ السيناريوهات 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]
baty- Admin
- المساهمات : 22
تاريخ التسجيل : 01/06/2021
baty يعجبه هذا الموضوع
صفحة 1 من اصل 1
صلاحيات هذا المنتدى:
تستطيع الرد على المواضيع في هذا المنتدى