من الرائع في برنامج اكسل أنّه يتيح إمكانية إدخال الكمية التي تريدها من البيانات مهما كانت كبيرة لتملأ الملايين من الخلايا، وكذلك إمكانية عرض هذه البيانات بالعديد من الطرق المختلفة. لكن في جداول البيانات الكبيرة جدا سيكون من الصعب تحليل جميع المعلومات في الورقة، وهنا يأتي دور الجداول المحورية 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:
وسيتم تنسيق الأرقام في منطقة القيم بصيغة عملة:
سنتابع في الدرس القادم كيفية إنشاء وتنسيق المخططات المحورية في برنامج اكسل.
أفضل التعليقات
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.