العمل مع Pivot Tables في Microsoft Excel

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

ملاحظة: يتم كتابة هذه المقالة باستخدام Excel 2010 (Beta). تغير مفهوم PivotTable قليلاً على مر السنين ، ولكن طريقة إنشاء واحد تغيرت تقريبًا في كل تكرار لـ Excel. إذا كنت تستخدم إصدار Excel ليس عام 2010 ، فتوقع شاشات مختلفة عن تلك التي تراها في هذه المقالة.

قليلا من التاريخ

في الأيام الأولى من برامج جداول البيانات ، حكمت Lotus 1-2-3 المجثم. كانت هيمنته كاملة لدرجة أن الناس ظنوا أنها مضيعة للوقت بالنسبة لمايكروسوفت لإزعاج تطوير برامج جداول البيانات الخاصة بها (إكسل) للتنافس مع لوتس. يعد الفلاش إلى 2010 ، وهيمنة Excel لسوق جدول البيانات أكبر من Lotus في أي وقت مضى ، في حين أن عدد المستخدمين الذين ما زالوا يشغلون Lotus 1-2-3 يقترب من الصفر. كيف حدث هذا؟ ما تسبب في مثل هذا الانقلاب الدرامي من ثروات?

ويرى محللو الصناعة أن هناك عاملين: أولاً ، قرر لوتس أن هذه الواجهة الجديدة المزيفة للواجهة الرسومية المعروفة باسم "ويندوز" كانت عبارة عن عباءة عابرة لا تنطلق أبدا. رفضوا إنشاء إصدار Windows من Lotus 1-2-3 (لبضع سنوات ، على أي حال) ، متوقعين أن إصدار DOS الخاص بهم من البرنامج هو كل ما يحتاجه أي شخص. طورت Microsoft بشكل طبيعي برنامج Excel حصريًا لنظام التشغيل Windows. ثانيًا ، طورت Microsoft ميزة لبرنامج Excel لم يقدمها Lotus في 1-2-3 ، أي الجداول المحورية.  تم اعتبار ميزة PivotTables ، الحصرية لبرنامج Excel ، مفيدة للغاية لدرجة أن الأشخاص كانوا على استعداد لتعلم حزمة برمجية جديدة بالكامل (Excel) بدلاً من التمسك ببرنامج (1-2-3) لم يكن لديهم ذلك. هذه الميزة ، جنبا إلى جنب مع سوء تقدير نجاح ويندوز ، كانت عقدة الموت لوتس 1-2-3 ، وبداية نجاح مايكروسوفت اكسل.

فهم PivotTables

فما هو PivotTable ، بالضبط?

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

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

افحص البيانات الموضحة أدناه:

لاحظ أن هذا هو ليس مسودة بيانات. في الواقع ، هو بالفعل ملخص من نوع ما. في الخلية B3 يمكننا أن نرى 30،000 دولار ، وهو ما يبدو أنه إجمالي مبيعات جيمس كوك لشهر يناير. فأين هي البيانات الخام؟ كيف وصلنا إلى رقم 30،000 دولار؟ أين توجد القائمة الأصلية لمعاملات البيع التي تم الحصول عليها من هذا الرقم؟ من الواضح أنه في مكان ما ، يجب أن يكون شخص ما قد واجه مشكلة في جمع كل معاملات البيع خلال الأشهر الستة الماضية في الملخص الذي نراه أعلاه. كم من الوقت نفترض أن هذا استغرق؟ ساعة؟ عشرة?

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

إذا كان علينا تتبع القائمة الأصلية لمعاملات البيع ، فقد يبدو الأمر كالتالي:

قد تفاجأ عندما تعلم أنه باستخدام ميزة PivotTable في Excel ، يمكننا إنشاء ملخص مبيعات شهري مماثل لما هو مذكور أعلاه في بضع ثوانٍ ، مع بضع نقرات بالماوس فقط. يمكننا القيام بذلك - وأكثر من ذلك بكثير!

كيفية إنشاء PivotTable

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

لذلك نبدأ Excel ... ونقوم بتحميل هذه القائمة ...

بمجرد فتح القائمة في Excel ، فنحن مستعدون لبدء إنشاء PivotTable.

انقر فوق أي خلية واحدة داخل القائمة:

ثم من إدراج انقر فوق علامة التبويب PivotTable أيقونة:

ال قم بإنشاء PivotTable يظهر مربع يطرح عليك سؤالين: ما البيانات التي يجب أن تستند إليها PivotTable الجديدة وأين يجب إنشاؤها؟ نظرًا لأننا نقرنا بالفعل على خلية داخل القائمة (في الخطوة أعلاه) ، تم بالفعل تحديد القائمة الكاملة المحيطة بتلك الخلية ($ A $ 1: $ G $ 88 على ال المدفوعات ورقة ، في هذا المثال). لاحظ أنه يمكننا تحديد قائمة في أي منطقة أخرى من أي ورقة عمل أخرى ، أو حتى بعض مصدر البيانات الخارجية ، مثل جدول قاعدة بيانات Access ، أو حتى جدول قاعدة بيانات MS-SQL Server. نحتاج أيضًا إلى تحديد ما إذا كنا نريد إنشاء PivotTable الجديد على الجديد ورقة العمل ، أو على موجود واحدة. في هذا المثال سنختار الجديد واحد:

يتم إنشاء ورقة العمل الجديدة لنا ، ويتم إنشاء PivotTable فارغة في ورقة العمل:

يظهر مربع آخر أيضا: إن قائمة حقول PivotTable.  سيتم عرض قائمة الحقول هذه عندما نضغط على أي خلية داخل PivotTable (أعلاه):

قائمة الحقول الموجودة في الجزء العلوي من المربع هي في الواقع مجموعة رؤوس الأعمدة من ورقة عمل البيانات الأولية الأصلية. تسمح لنا المربعات الفارغة الأربعة الموجودة في الجزء السفلي من الشاشة باختيار الطريقة التي نود أن تقوم بها PivotTable بتلخيص البيانات الأولية. حتى الآن ، لا يوجد شيء في هذه المربعات ، لذلك PivotTable فارغ. كل ما نحتاج إليه هو سحب الحقول من القائمة أعلاه وإفلاتها في المربعات السفلية. ثم يتم إنشاء PivotTable تلقائيًا لمطابقة الإرشادات الخاصة بنا. إذا أخطأنا ، فنحن نحتاج فقط إلى سحب الحقول إلى المكان الذي أتت منه و / أو سحبه الجديد الحقول لأسفل لاستبدالها.

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

لاحظ أنه (أ) تم وضع علامة على حقل "المبلغ" في قائمة الحقول ، وإضافة "مجموع المبلغ" إلى القيم المربع ، مشيرًا إلى أن عمود القيمة قد تم جمعه.

إذا قمنا بفحص PivotTable نفسه ، فسنجد بالفعل مجموع كل قيم "المبلغ" من ورقة عمل البيانات الأولية:

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

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

الآن, أخيرا ، تبدأ الأمور في إثارة اهتمامك! يبدأ PivotTable الخاص بنا بالظهور ... .

من خلال بضع نقرات ، أنشأنا جدولًا يستغرق وقتًا طويلاً للقيام به يدويًا.

اذا، ماذا يمكننا ان نفعل ايضا؟ حسنًا ، لقد اكتملت PivotTable منطقيًا. لقد قمنا بإنشاء ملخص مفيد لبيانات المصدر الخاصة بنا. علمت الاشياء الهامة بالفعل! بالنسبة لبقية المقالة ، سنفحص بعض الطرق التي يمكن بها إنشاء PivotTables أكثر تعقيدًا ، والطرق التي يمكن تخصيص PivotTables لها.

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

الذي يشبه هذا:

ابتداء من الحصول عليها للغاية بارد!

دعونا نجعلها ثلاثة-جدول الأبعاد. ما يمكن أن يبدو مثل هذا الجدول مثل؟ حسنا، لنرى…

اسحب عمود "الحزمة" / العنوان إلى تصفية التقرير صندوق:

لاحظ أين ينتهي الأمر ... .

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

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

إذا اتضح ، على سبيل المثال ، أننا نريد فقط أن نرى تحقق وبطاقة الائتمان المعاملات (بمعنى عدم وجود معاملات نقدية) ، عندئذٍ يمكننا إلغاء تحديد "النقد" من عناوين الأعمدة. انقر على سهم القائمة المنسدلة بجوار تسميات العمود, وإلغاء تحديد "النقد":

دعونا نرى كيف يبدو ذلك ... كما ترون ، ذهب "النقد".

التنسيق

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

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

أولاً ، نحدد موقع إدخال "مجموع المبلغ" في القيم المربع ، وانقر عليه. تظهر قائمة. نحن نختار إعدادات حقل القيمة ... من القائمة:

ال إعدادات حقل القيمة يظهر مربع.

انقر على تنسيق الرقم زر ، والمعيار مربع تنسيق الخلايا يبدو:

من الفئة قائمة ، حدد (قل) محاسبة, وإسقاط عدد المنازل العشرية إلى 0. انقر فوق حسنا عدة مرات للعودة إلى PivotTable ...

كما ترون ، تم تنسيق الأرقام بشكل صحيح كمبالغ بالدولار.

بينما نحن بصدد موضوع التنسيق ، لنقوم بتنسيق PivotTable بالكامل. هناك بضعة طرق لفعل هذا. دعونا نستخدم واحدة بسيطة ...

انقر على أدوات / تصميم PivotTable التبويب:

ثم قم بإسقاط السهم في أسفل يمين أنماط PivotTable قائمة لرؤية مجموعة واسعة من الأنماط المدمجة:

اختر أي شخص يستأنف ، وانظر إلى النتيجة في PivotTable الخاصة بك:

خيارات أخرى

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

أولاً ، لنقم بإزالة عمود "طريقة الدفع" من تسميات العمود المربع (قم بسحبه مرة أخرى إلى قائمة الحقول) ، واستبدله بعمود "Date Booked":

كما ترى ، فإن هذا يجعل PivotTable عديم الفائدة على الفور ، مما يمنحنا عمودًا واحدًا لكل تاريخ حدثت فيه المعاملة - جدول واسع جدًا!

لإصلاح هذا ، انقر بزر الماوس الأيمن على أي تاريخ وحدد مجموعة… من قائمة السياق:

يظهر مربع التجميع. نحن نختار الشهور وانقر فوق موافق:

فويلا! ا كثير جدول أكثر فائدة:

(بالمناسبة ، هذا الجدول متطابق تقريبًا مع الجدول المعروض في بداية هذه المقالة - ملخص المبيعات الأصلي الذي تم إنشاؤه يدويًا.)

شيء آخر رائع يجب معرفته هو أنه يمكن أن يكون لديك أكثر من مجموعة من عناوين الصفوف (أو عناوين الأعمدة):

... والتي تبدو هكذا ... .

يمكنك عمل شيء مشابه مع عناوين الأعمدة (أو حتى مرشحات التقرير).

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

أولاً ، انقر فوق "مجموع المبلغ" ، وحدد إعدادات حقل القيمة ... من قائمة السياق التي تظهر:

في ال تلخيص حقل القيمة بواسطة قائمة في إعدادات حقل القيمة المربع ، حدد معدل:

بينما نحن هنا ، دعونا تغيير الاسم المخصص, من "متوسط ​​المبلغ" إلى شيء أكثر اختصارًا. اكتب شيئًا مثل "متوسط":

انقر حسنا, ونرى كيف يبدو. لاحظ أن جميع القيم تتغير من إجمالي المجاميع إلى المتوسطات ، وقد تغير عنوان الجدول (الخلية العلوية اليسرى) إلى "متوسط":

إذا كنا نحب ، يمكننا حتى أن نحصل على مبالغ ، ومتوسطات ، وأعداد (التهم = عدد المبيعات هناك) كلها على نفس PivotTable!

فيما يلي الخطوات للحصول على شيء مثل هذا في مكان (بدءاً من PivotTable فارغة):

1. اسحب "مندوب مبيعات" في تسميات العمود
2. اسحب حقل "المبلغ" لأسفل في القيم المربع ثلاث مرات
3. في حقل "المبلغ" الأول ، قم بتغيير اسمها المخصص إلى "الإجمالي" وتنسيق الرقم له محاسبة (0 خانات عشرية)
4. بالنسبة للحقل "Amount" الثاني ، قم بتغيير اسمه المخصص إلى "Average" ، إلى الدالة الخاصة به معدل وانها تنسيق الرقم ل محاسبة (0 خانات عشرية)
5. بالنسبة إلى حقل "المبلغ" الثالث ، قم بتغيير اسمه إلى "حساب" ووظائفه عد
6. اسحب تلقائيا خلق حقل من تسميات العمود إلى تسميات صف

إليك ما انتهى به الأمر:

الإجمالي والمتوسط ​​والاعتماد على نفس PivotTable!

استنتاج

هناك العديد من الميزات والخيارات للعديد من PivotTables التي تم إنشاؤها بواسطة Microsoft Excel - الكثير منها في قائمة مثل هذا. لتغطية كامل إمكانات PivotTables ، سيكون مطلوبًا وجود كتاب صغير (أو موقع ويب كبير). يمكن للقراء الشجعان و / أو العبقريين استكشاف PivotTables بسهولة تامة: ببساطة انقر بزر الماوس الأيمن على كل شيء ، واطلع على الخيارات المتاحة لك. هناك أيضًا علامتا الشريط: أدوات / خيارات PivotTable و التصميم.  لا يهم إذا قمت بخطأ - من السهل حذف PivotTable والبدء من جديد - إمكانية مستخدمي DOS القديم لـ Lotus 1-2-3.

إذا كنت تعمل في Office 2007 ، فقد ترغب في مراجعة المقالة حول كيفية إنشاء PivotTable في Excel 2007.

لقد قمنا بتضمين مصنف Excel يمكنك تنزيله لممارسة مهارات PivotTable الخاصة بك. يجب أن تعمل مع كافة إصدارات Excel من 97 فصاعداً.

تحميل مصنفنا مصنف Excel