المحتويات
الجداول المحورية هي واحدة من أقوى الأدوات في Excel. إنها تسمح لك بتحليل وتلخيص الملخصات المختلفة لكميات كبيرة من البيانات ببضع نقرات بالماوس. في هذه المقالة ، سنتعرف على الجداول المحورية ، ونفهم ماهيتها ، ونتعلم كيفية إنشائها وتخصيصها.
تمت كتابة هذه المقالة باستخدام Excel 2010. لم يتغير مفهوم جداول PivotTable كثيرًا على مر السنين ، ولكن طريقة إنشائها تختلف قليلاً في كل إصدار جديد من Excel. إذا كان لديك إصدار من Excel وليس 2010 ، فاستعد لأن لقطات الشاشة في هذه المقالة ستختلف عما تراه على شاشتك.
القليل من التاريخ
في الأيام الأولى لبرامج جداول البيانات ، كانت كرة قاعدة Lotus 1-2-3. كانت هيمنتها كاملة لدرجة أن جهود Microsoft لتطوير برنامجها الخاص (Excel) كبديل لوتس بدت وكأنها مضيعة للوقت. تقدم الآن بسرعة إلى عام 2010! يهيمن Excel على جداول البيانات أكثر من أي وقت مضى من قبل Lotus code في تاريخه ، وعدد الأشخاص الذين ما زالوا يستخدمون Lotus يقترب من الصفر. كيف يمكن حصول هذا؟ ما هو سبب هذا التحول الدراماتيكي للأحداث؟
يحدد المحللون عاملين رئيسيين:
- أولاً ، قرر لوتس أن منصة واجهة المستخدم الرسومية الجديدة هذه والتي تسمى Windows كانت مجرد بدعة عابرة لن تدوم طويلاً. لقد رفضوا إنشاء نسخة Windows من Lotus 1-2-3 (ولكن لبضع سنوات فقط) ، وتوقعوا أن إصدار DOS من برنامجهم سيكون كل ما يحتاجه المستهلكون. طورت Microsoft بشكل طبيعي برنامج Excel خصيصًا لنظام التشغيل Windows.
- ثانيًا ، قدمت Microsoft أداة في Excel تسمى PivotTables لم تكن متوفرة في Lotus 1-2-3. أثبتت PivotTables ، الحصرية لبرنامج Excel ، أنها مفيدة للغاية لدرجة أن الأشخاص يميلون إلى الالتزام بمجموعة برامج Excel الجديدة بدلاً من الاستمرار في Lotus 1-2-3 ، والتي لم تكن تمتلكها.
لعبت PivotTables ، جنبًا إلى جنب مع التقليل من نجاح Windows بشكل عام ، مسيرة الموت لـ Lotus 1-2-3 وأدت إلى نجاح Microsoft Excel.
ما هي الجداول المحورية؟
إذن ، ما هي أفضل طريقة لوصف ما هي جداول PivotTable؟
بعبارات بسيطة ، تعد الجداول المحورية ملخصات لبعض البيانات ، تم إنشاؤها لتسهيل تحليل هذه البيانات. بخلاف الإجماليات التي تم إنشاؤها يدويًا ، فإن Excel PivotTables تفاعلية. بمجرد إنشائها ، يمكنك تعديلها بسهولة إذا لم تقدم الصورة التي كنت تأمل فيها. ببضع نقرات بالماوس ، يمكن قلب الإجماليات بحيث تصبح عناوين الأعمدة عناوين صفوف والعكس صحيح. يمكنك القيام بالعديد من الأشياء باستخدام الجداول المحورية. بدلاً من محاولة وصف جميع ميزات الجداول المحورية بالكلمات ، من الأسهل توضيحها عمليًا ...
لا يمكن أن تكون البيانات التي تقوم بتحليلها باستخدام جداول PivotTable عشوائية. يجب أن تكون بيانات أولية ، مثل قائمة من نوع ما. على سبيل المثال ، يمكن أن تكون قائمة بالمبيعات التي أجرتها الشركة في الأشهر الستة الماضية.
انظر إلى البيانات الموضحة في الشكل أدناه:
لاحظ أن هذه ليست بيانات أولية ، حيث تم تلخيصها بالفعل. في الخلية B3 ، نرى 30000 دولار ، وهي على الأرجح النتيجة الإجمالية التي حققها جيمس كوك في يناير. أين هي البيانات الأصلية إذن؟ من أين أتى رقم 30000 دولار؟ أين القائمة الأصلية للمبيعات التي تم اشتقاق هذا الإجمالي الشهري منها؟ من الواضح أن شخصًا ما قام بعمل رائع في تنظيم وفرز جميع بيانات المبيعات للأشهر الستة الماضية وتحويلها إلى جدول الإجماليات الذي نراه. كم من الوقت تعتقد أنه استغرق؟ ساعة؟ العاشرة تماما؟
الحقيقة هي أن الجدول أعلاه ليس جدول محوري. تم تصنيعه يدويًا من بيانات خام مخزنة في مكان آخر واستغرق الأمر ساعتين على الأقل للمعالجة. يمكن إنشاء مثل هذا الجدول الموجز باستخدام الجداول المحورية في بضع ثوانٍ فقط. لنكتشف كيف ...
إذا عدنا إلى قائمة المبيعات الأصلية ، فستبدو كما يلي:
قد تندهش من أنه من قائمة الصفقات هذه بمساعدة الجداول المحورية وفي بضع ثوانٍ فقط ، يمكننا إنشاء تقرير مبيعات شهري في Excel ، والذي قمنا بتحليله أعلاه. نعم ، يمكننا فعل ذلك وأكثر!
كيف تصنع جدول محوري؟
أولاً ، تأكد من وجود بعض بيانات المصدر في ورقة Excel. قائمة المعاملات المالية هي الأكثر شيوعًا التي تحدث. في الواقع ، يمكن أن تكون قائمة بأي شيء: تفاصيل الاتصال بالموظف ، أو مجموعة أقراص مضغوطة ، أو بيانات استهلاك الوقود الخاصة بشركتك.
لذلك ، نبدأ Excel ... ونقوم بتحميل مثل هذه القائمة ...
بعد فتح هذه القائمة في Excel ، يمكننا البدء في إنشاء جدول محوري.
حدد أي خلية من هذه القائمة:
ثم في علامة التبويب إدخال (إدراج) حدد الأمر جدول محوري (جدول محوري):
سيظهر مربع الحوار قم بإنشاء PivotTable (إنشاء جدول محوري) مع سؤالين لك:
- ما البيانات التي يجب استخدامها لإنشاء جدول محوري جديد؟
- أين نضع الجدول المحوري؟
نظرًا لأننا حددنا بالفعل إحدى خلايا القائمة في الخطوة السابقة ، فسيتم تحديد القائمة بأكملها تلقائيًا لإنشاء جدول محوري. لاحظ أنه يمكننا تحديد نطاق مختلف وجدول مختلف وحتى بعض مصادر البيانات الخارجية مثل جدول قاعدة بيانات Access أو MS-SQL. بالإضافة إلى ذلك ، نحتاج إلى اختيار مكان وضع الجدول المحوري الجديد: على ورقة جديدة أو على أحد الجداول الموجودة. في هذا المثال ، سنختار الخيار - ورقة عمل جديدة (إلى ورقة جديدة):
سيقوم Excel بإنشاء ورقة جديدة ووضع جدول محوري فارغ عليها:
بمجرد النقر فوق أي خلية في الجدول المحوري ، سيظهر مربع حوار آخر: قائمة حقول PivotTable (حقول الجدول المحوري).
قائمة الحقول الموجودة أعلى مربع الحوار هي قائمة بجميع العناوين من القائمة الأصلية. تسمح لك المناطق الفارغة الأربع الموجودة أسفل الشاشة بإخبار PivotTable بالطريقة التي تريدها لتلخيص البيانات. طالما أن هذه المساحات فارغة ، فلا يوجد شيء في الجدول أيضًا. كل ما علينا فعله هو سحب العناوين من المنطقة العلوية إلى المناطق الفارغة أدناه. في الوقت نفسه ، يتم إنشاء جدول محوري تلقائيًا ، وفقًا لتعليماتنا. إذا ارتكبنا خطأً ، فيمكننا إزالة العناوين من المنطقة السفلية أو سحب الآخرين لاستبدالها.
المساحة القيم ربما تكون (المعاني) هي الأهم من بين الأربعة. يحدد العنوان الذي يتم وضعه في هذا المجال البيانات التي سيتم تلخيصها (المجموع ، المتوسط ، الحد الأقصى ، الحد الأدنى ، إلخ.) هذه هي دائمًا قيم رقمية. البيانات الموجودة تحت العنوان هي المرشح الممتاز لمكان في هذا المجال المقدار (التكلفة) من طاولتنا الأصلية. اسحب هذا العنوان إلى المنطقة القيم (قيم):
يرجى ملاحظة أن العنوان المقدار تم تعليمه الآن بعلامة اختيار ، وفي المنطقة القيم (القيم) ظهر إدخال مجموع المبلغ (المبلغ الحقل المبلغ) ، يشير إلى أن العمود المقدار لخص.
إذا نظرنا إلى الجدول المحوري نفسه ، فسنرى مجموع كل القيم من العمود المقدار الجدول الأصلي.
لذلك ، تم إنشاء أول جدول محوري لدينا! ملائم ، لكن ليس مثيرًا للإعجاب بشكل خاص. ربما نرغب في الحصول على مزيد من المعلومات حول بياناتنا أكثر مما لدينا حاليًا.
دعنا ننتقل إلى البيانات الأصلية ونحاول تحديد عمود واحد أو أكثر يمكن استخدامه لتقسيم هذا المجموع. على سبيل المثال ، يمكننا تشكيل جدولنا المحوري بطريقة يتم فيها حساب المبلغ الإجمالي للمبيعات لكل بائع على حدة. أولئك. ستتم إضافة صفوف إلى جدولنا المحوري مع اسم كل مندوب مبيعات في الشركة وإجمالي مبلغ مبيعاتهم. لتحقيق هذه النتيجة ، ما عليك سوى سحب العنوان مندوب مبيعات (مندوب مبيعات) إلى المنطقة تسميات الصفوف (سلاسل):
يصبح أكثر إثارة للاهتمام! بدأ PivotTable في التبلور ...
انظر الفوائد؟ في بضع نقرات ، أنشأنا جدولًا كان سيستغرق وقتًا طويلاً جدًا لإنشائه يدويًا.
ماذا يمكن ان نفعل ايضا؟ حسنًا ، إلى حد ما ، جدولنا المحوري جاهز. لقد أنشأنا ملخصًا مفيدًا للبيانات الأصلية. معلومات مهمة وردت بالفعل! في الجزء المتبقي من هذه المقالة ، سنلقي نظرة على بعض الطرق لإنشاء جداول PivotTable أكثر تعقيدًا ، بالإضافة إلى التعرف على كيفية تخصيصها.
إعداد PivotTable
أولاً ، يمكننا إنشاء جدول محوري ثنائي الأبعاد. لنفعل ذلك باستخدام عنوان العمود طريقة الدفع او السداد (طريقة الدفع او السداد). فقط اسحب العنوان طريقة الدفع او السداد في المنطقة تسميات العمود (الأعمدة):
نحصل على النتيجة:
تبدو رائعة جدا!
لنقم الآن بعمل جدول ثلاثي الأبعاد. كيف ستبدو مثل هذه الطاولة؟ دعونا نرى…
اسحب الرأس فئة الإشتراك (مجمع) للمنطقة مرشحات التقرير (مرشحات):
لاحظ مكانه ...
يمنحنا هذا فرصة لتصفية التقرير على أساس "أي مجمع عطلات تم الدفع مقابله". على سبيل المثال ، يمكننا أن نرى تفصيلًا حسب البائعين وطرق الدفع لجميع المجمعات ، أو في بضع نقرات بالماوس ، قم بتغيير عرض الجدول المحوري وإظهار نفس الانهيار فقط لأولئك الذين طلبوا المجمع باحثون عن الشمس.
لذا ، إذا فهمت هذا بشكل صحيح ، فيمكن تسمية الجدول المحوري لدينا بأنه ثلاثي الأبعاد. دعنا نواصل الإعداد ...
إذا اتضح فجأة أنه يجب عرض الدفع عن طريق الشيكات وبطاقة الائتمان فقط (أي الدفع غير النقدي) في الجدول المحوري ، فيمكننا عندئذٍ إيقاف تشغيل عرض العنوان النقد (نقدي). لهذا ، بجانب تسميات العمود انقر فوق السهم لأسفل وقم بإلغاء تحديد المربع في القائمة المنسدلة النقد:
دعونا نرى كيف يبدو الجدول المحوري الآن. كما ترى ، العمود النقد اختفى منها.
تنسيق جداول PivotTable في Excel
من الواضح أن PivotTables أداة قوية للغاية ، ولكن حتى الآن تبدو النتائج بسيطة ومملة بعض الشيء. على سبيل المثال ، الأرقام التي نجمعها لا تشبه المبالغ بالدولار - إنها مجرد أرقام. دعونا نصلح هذا.
من المغري أن تفعل ما اعتدت عليه في مثل هذه الحالة واختر ببساطة الجدول بأكمله (أو الورقة بأكملها) واستخدم أزرار تنسيق الأرقام القياسية على شريط الأدوات لتعيين التنسيق المطلوب. تكمن المشكلة في هذا الأسلوب في أنه إذا قمت بتغيير بنية الجدول المحوري في المستقبل (وهو ما يحدث مع فرصة بنسبة 99٪) ، فسيتم فقد التنسيق. ما نحتاجه هو طريقة لجعله دائمًا (تقريبًا).
أولاً ، لنجد الإدخال مجموع المبلغ in القيم (القيم) واضغط عليها. في القائمة التي تظهر ، حدد العنصر إعدادات حقل القيمة (خيارات حقل القيمة):
سيظهر مربع الحوار إعدادات حقل القيمة (خيارات حقل القيمة).
صحافة صيغة رقم (تنسيق الأرقام) ، سيتم فتح مربع حوار. تنسيق الخلايا (تنسيق الخلية):
من القائمة الفئة (تنسيقات الأرقام) حدد المحاسبة (مالي) واضبط عدد المنازل العشرية على صفر. الآن اضغط عدة مرات OKللعودة إلى الجدول المحوري.
كما ترى ، يتم تنسيق الأرقام كمبالغ بالدولار.
أثناء قيامنا بالتنسيق ، فلنقم بإعداد التنسيق لجدول PivotTable بأكمله. هناك عدة طرق للقيام بذلك. نستخدم الطريقة الأبسط ...
انقر على أدوات PivotTable: التصميم (العمل مع PivotTables: المُنشئ):
بعد ذلك ، قم بتوسيع القائمة بالنقر فوق السهم الموجود في الركن الأيمن السفلي من القسم أنماط PivotTable (أنماط PivotTable) لمشاهدة مجموعة واسعة من الأنماط المضمنة:
اختر أي نمط مناسب وانظر إلى النتيجة في الجدول المحوري:
إعدادات PivotTable الأخرى في Excel
تحتاج أحيانًا إلى تصفية البيانات حسب التواريخ. على سبيل المثال ، في قائمة الصفقات لدينا العديد من التواريخ. يوفر Excel أداة لتجميع البيانات حسب اليوم والشهر والسنة وما إلى ذلك. دعونا نرى كيف يتم ذلك.
قم أولاً بإزالة الإدخال. طريقة الدفع او السداد من المنطقة تسميات العمود (أعمدة). للقيام بذلك ، اسحبه مرة أخرى إلى قائمة العناوين ، وفي مكانه ، انقل العنوان تاريخ الحجز (تاريخ الحجز):
كما ترى ، جعل هذا الجدول المحوري عديم الفائدة مؤقتًا. قام Excel بإنشاء عمود منفصل لكل تاريخ تم فيه التداول. نتيجة لذلك ، حصلنا على طاولة واسعة جدًا!
لإصلاح ذلك ، انقر بزر الماوس الأيمن فوق أي تاريخ وحدد من قائمة السياق مجموعة (مجموعة):
سيظهر مربع حوار التجميع. نحن نختار أشهر (شهور) واضغط OK:
هاهو! هذا الجدول أكثر فائدة:
بالمناسبة ، هذا الجدول مطابق تقريبًا للجدول الموضح في بداية المقالة ، حيث تم تجميع إجماليات المبيعات يدويًا.
هناك نقطة أخرى مهمة للغاية تحتاج إلى معرفتها! لا يمكنك إنشاء مستويات واحدة ، ولكن عدة مستويات من عناوين الصفوف (أو الأعمدة):
... وسيبدو هكذا ...
يمكن فعل الشيء نفسه مع عناوين الأعمدة (أو حتى المرشحات).
دعنا نعود إلى الشكل الأصلي للجدول ونرى كيفية عرض المتوسطات بدلاً من المجاميع.
للبدء ، انقر فوق مجموع المبلغ ومن القائمة التي تظهر حدد إعدادات حقل القيمة (خيارات حقل القيمة):
القائمة تلخيص حقل القيمة من خلال (عملية) في مربع الحوار إعدادات حقل القيمة (خيارات حقل القيمة) حدد متوسط (متوسط):
في نفس الوقت ، بينما نحن هنا ، دعونا نتغير الاسم المخصص (اسم مخصص) مع متوسط المبلغ (مقدار الحقل المبلغ) إلى شيء أقصر. أدخل في هذا المجال شيء مثل متوسط:
صحافة OK ونرى ما سيحدث. لاحظ أن جميع القيم قد تغيرت من الإجماليات إلى المتوسطات ، وتغير رأس الجدول (في الخلية اليسرى العلوية) إلى متوسط:
إذا كنت ترغب في ذلك ، يمكنك على الفور الحصول على المبلغ والمتوسط والعدد (المبيعات) الموضوعة في جدول محوري واحد.
إليك دليل خطوة بخطوة حول كيفية القيام بذلك ، بدءًا من جدول محوري فارغ:
- اسحب الرأس مندوب مبيعات (مندوب مبيعات) إلى المنطقة تسميات العمود (أعمدة).
- اسحب العنوان ثلاث مرات المقدار (التكلفة) إلى المنطقة القيم (قيم).
- للمجال الأول المقدار تغيير العنوان إلى الإجمالي (المبلغ) ، وتنسيق الأرقام في هذا الحقل المحاسبة (الأمور المالية). عدد المنازل العشرية هو صفر.
- المجال الثاني المقدار الاسم أفيراجه ، قم بتعيين العملية لها متوسط (المتوسط) وتنسيق الأرقام في هذا الحقل يتغيران أيضًا إلى المحاسبة (مالي) مع صفر منازل عشرية.
- للمجال الثالث المقدار حدد العنوان العد التنازلي وعملية له - العد التنازلي (كمية)
- في مجلة تسميات العمود تم إنشاء حقل (الأعمدة) تلقائيًا Σ القيم (Σ القيم) - اسحبه إلى المنطقة تسميات الصفوف (خطوط)
إليكم ما سننتهي به:
إجمالي المبلغ ومتوسط القيمة وعدد المبيعات - كل ذلك في جدول محوري واحد!
وفي الختام
تحتوي الجداول المحورية في Microsoft Excel على الكثير من الميزات والإعدادات. في مثل هذه المقالة الصغيرة ، لم يكونوا قريبين من تغطيتها جميعًا. قد يتطلب الأمر كتابًا صغيرًا أو موقع ويب كبير لوصف جميع إمكانيات الجداول المحورية بشكل كامل. يمكن للقراء الجريئين والفضوليين مواصلة استكشافهم للجداول المحورية. للقيام بذلك ، ما عليك سوى النقر بزر الماوس الأيمن على أي عنصر تقريبًا من عناصر الجدول المحوري ومعرفة الوظائف والإعدادات المفتوحة. ستجد علامتي تبويب على الشريط: أدوات PivotTable: خيارات (تحليل) و تصميم (البناء). لا تخف من ارتكاب خطأ ، يمكنك دائمًا حذف PivotTable والبدء من جديد. لديك فرصة لم تكن متاحة لمستخدمي DOS و Lotus 1-2-3 لفترة طويلة.