كيفية إنشاء جدول محوري في Excel: برنامج تعليمي خطوة بخطوة

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

الخبر السار: تعلم كيفية إنشاء جدول محوري في Excel أسهل بكثير مما كنت تعتقد.

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

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

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

ما هي الجداول المحورية المستخدمة؟

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

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

فيما يلي سبعة سيناريوهات افتراضية حيث يمكن أن يكون الجدول المحوري حلاً:

1. مقارنة إجمالي مبيعات المنتجات المختلفة.

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

الآن ، تخيل أن ورقة عمل المبيعات الشهرية تحتوي على آلاف وآلاف الصفوف. قد يستغرق الفرز من خلالهم جميعًا يدويًا مدى الحياة. باستخدام الجدول المحوري ، يمكنك تجميع جميع أرقام المبيعات للمنتج 1 والمنتج 2 والمنتج 3 تلقائيًا - وحساب المبالغ الخاصة بكل منها - في أقل من دقيقة.

2. إظهار مبيعات المنتج كنسب مئوية من إجمالي المبيعات.

تعرض الجداول المحورية بشكل طبيعي إجماليات كل صف أو عمود عند إنشائها. لكن هذا ليس الرقم الوحيد الذي يمكنك إنتاجه تلقائيًا.

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

باستخدام الجدول المحوري ، يمكنك تكوين كل عمود لمنحك النسبة المئوية للعمود من إجمالي الأعمدة الثلاثة ، بدلاً من إجمالي العمود فقط. إذا بلغ إجمالي مبيعات ثلاثة منتجات 200000 دولار أمريكي ، على سبيل المثال ، وكان المنتج الأول قد حقق 45000 دولار أمريكي ، فيمكنك تحرير جدول محوري ليقول بدلاً من ذلك إن هذا المنتج ساهم بنسبة 22.5٪ من إجمالي مبيعات الشركة.

لإظهار مبيعات المنتج كنسب مئوية من إجمالي المبيعات في جدول محوري ، ما عليك سوى النقر بزر الماوس الأيمن فوق الخلية التي تحمل إجمالي المبيعات وتحديد إظهار القيم باسم> ٪ من الإجمالي الكلي .

3. الجمع بين البيانات المكررة.

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

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

4. الحصول على عدد الموظفين للإدارات المنفصلة.

تساعد الجداول المحورية في حساب الأشياء التي لا يمكنك العثور عليها بسهولة في جدول Excel الأساسي تلقائيًا. أحد هذه الأشياء هو عد الصفوف التي تشترك جميعها في شيء مشترك.

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

5. إضافة القيم الافتراضية للخلايا الفارغة.

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

يمكنك بسهولة تخصيص جدول محوري لملء الخلايا الفارغة بقيمة افتراضية ، مثل $ 0 ، أو TBD (لـ "يتم تحديدها"). بالنسبة لجداول البيانات الكبيرة ، تعد القدرة على تمييز هذه الخلايا بسرعة ميزة مفيدة عندما يقوم العديد من الأشخاص بمراجعة نفس الورقة.

لتنسيق الخلايا الفارغة للجدول المحوري تلقائيًا ، انقر بزر الماوس الأيمن فوق الجدول وانقر فوق خيارات PivotTable. في النافذة التي تظهر ، حدد المربع المسمى Empty Cells As وأدخل ما تريد عرضه عندما لا تحتوي الخلية على قيمة أخرى.

كيفية إنشاء جدول محوري

1. أدخل بياناتك في مجموعة من الصفوف والأعمدة.
2. فرز البيانات الخاصة بك عن طريق سمة معينة.
3. قم بتمييز الخلايا الخاصة بك لإنشاء الجدول المحوري الخاص بك.
4. قم بسحب حقل وإسقاطه في منطقة "تسميات الصفوف".
5. اسحب حقل وأسقطه في منطقة "القيم".
6. صقل حساباتك.

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

الخطوة الأولى. أدخل بياناتك في مجموعة من الصفوف والأعمدة.

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

على سبيل المثال ، لإنشاء جدول Excel لبيانات أداء منشور المدونة ، قد يكون لديك عمود يسرد كل "أهم الصفحات" ، وعمود يسرد "النقرات" لكل عنوان URL ، وعمود يسرد "مرات الظهور" لكل منشور ، وما إلى ذلك. (سنستخدم هذا المثال في الخطوات التالية.)

الخطوة 2. فرز البيانات الخاصة بك حسب سمة معينة.

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

لفرز بياناتك ، انقر فوق علامة التبويب " البيانات " في شريط التنقل العلوي وحدد رمز الفرز الموجود أسفلها. في النافذة التي تظهر ، يمكنك اختيار فرز بياناتك حسب أي عمود تريده وبأي ترتيب.

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

حدد موافق في الجزء السفلي الأيمن من نافذة الفرز ، وسوف تعيد ترتيب كل صف في ورقة Excel بنجاح حسب عدد المشاهدات التي تلقاها كل منشور في المدونة.

الخطوة 3. قم بتمييز الخلايا الخاصة بك لإنشاء جدول محوري.

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

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

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

ملاحظة: إذا كنت تستخدم إصدارًا سابقًا من Excel ، فقد تكون "جداول PivotTable" ضمن الجداول أو البيانات على طول شريط التنقل العلوي ، بدلاً من "إدراج". في جداول بيانات Google ، يمكنك إنشاء جداول محورية من قائمة البيانات المنسدلة على طول شريط التنقل العلوي.

الخطوة 4. قم بسحب وإسقاط حقل في منطقة "تسميات الصفوف".

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

على سبيل المثال ، لنفترض أنك تريد تنظيم مجموعة من بيانات التدوين حسب عنوان المنشور. للقيام بذلك ، ما عليك سوى النقر فوق حقل "أهم الصفحات" واسحبه إلى منطقة "تسميات الصفوف".

ملاحظة: قد يبدو الجدول المحوري مختلفًا بناءً على إصدار Excel الذي تستخدمه. ومع ذلك ، تظل المبادئ العامة كما هي.

الخطوة 5. قم بسحب وإفلات حقل في منطقة "القيم".

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

بالتمسك بمثال بيانات التدوين ، لنفترض أنك تريد تلخيص مشاهدات منشور المدونة حسب العنوان. للقيام بذلك ، ما عليك سوى سحب حقل "طرق العرض" إلى منطقة القيم.

الخطوة 6. صقل حساباتك.

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

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

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

عندما تصنف بياناتك حسب رغبتك ، احفظ عملك واستخدمه كما يحلو لك.

حفر أعمق باستخدام الجداول المحورية

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

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

ملاحظة المحرر: تم نشر هذا المنشور في الأصل في ديسمبر 2018 وتم تحديثه للشمول.