الجدول المحوري (Pivot Table) في الاكسل خطوة بخطوة

يجمّع الجدول المحوري صفوف جدول حسب فئة، مثل Region، ويحسب مجموع رقم، مثل Sales، لكل مجموعة، دون صيغ. لإنشاء واحد، انقر خلية في البيانات، واذهب إلى إدراج > PivotTable، واضغط موافق، واسحب Region إلى الصفوف وSales إلى القيم. الورقة أدناه ليست جدولًا محوريًا: إنها تبني الملخص نفسه بالصيغ، لتشاهد المجاميع وهي تتغير.

الصيغإعادة الضبط

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تعرض UNIQUE كل منطقة مرة واحدة وتجمعها SUMIF: North بقيمة 455، وSouth بقيمة 305، وEast بقيمة 170، أي 49% و33% و18% من المجموع الكلي 930. غيّر C3 إلى 185 فيتبعه مجموع South والنسب الثلاث فورًا. والجدول المحوري سيعرض الأرقام نفسها، لكن فقط بعد أن تحدّثه.

كيف تنشئ جدولًا محوريًا

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

1. انقر أي خلية في البيانات.
2. اذهب إلى إدراج > PivotTable (وفي بعض الإصدارات إدراج > PivotTable > من جدول/نطاق).
3. يملأ الاكسل النطاق. اختر ورقة عمل جديدة واضغط موافق.
4. يظهر جدول محوري فارغ مع جزء حقول PivotTable على الجانب، يعرض عناوين أعمدتك.
5. اسحب Region إلى مربع الصفوف وSales إلى مربع القيم. يعرض الجدول المحوري كل منطقة مرة واحدة مع Sum of Sales بجانبها، وصف الإجمالي الكلي.
6. لتغيير ما يُعرض، اسحب الحقول بين المربعات أو خارج الجزء.

إذا لم تكن متأكدًا من أين تبدأ، يعرض إدراج > PivotTables الموصى بها بضعة تخطيطات جاهزة لبياناتك. وعلى Mac القائمة نفسها: إدراج > PivotTable.

الصفوف والأعمدة والقيم وعوامل التصفية

في جزء حقول PivotTable أربعة مربعات، وكل جدول محوري هو اختيار لأي عمود يذهب إلى أي مربع:

• الصفوف: الفئات على الجانب، صف لكل قيمة مميزة (Region).
• الأعمدة: الفئات في الأعلى، عمود لكل قيمة مميزة (Product).
• القيم: الأرقام المراد حسابها لكل تركيبة. المجموع هو الافتراضي لعمود رقمي؛ والعدد والمتوسط والحد الأقصى والحد الأدنى وغيرها في إعدادات حقل القيمة.
• عوامل التصفية: حقل يُصفّى حسبه الجدول المحوري كله، ويُعرض كقائمة منسدلة فوقه.

مع Region في الصفوف، وProduct في الأعمدة، وSales في القيم، يبدو الجدول المحوري للبيانات السابقة هكذا:

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

تعرض نسخة الصيغ لهذا التخطيط المناطق نزولًا بالدالة UNIQUE، والمنتجات عرضًا بالصيغة TRANSPOSE(UNIQUE())، وتحسب كل خلية في الشبكة بصيغة SUMIFS واحدة تأخذ القائمتين كشرطين:

الصيغإعادة الضبط

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تمتد E2 بالقيم North وSouth وEast نزولًا، وتمتد F1 بالقيمتين Apple وPear عرضًا، وتملأ SUMIFS في F2 الشبكة 3 في 2 بينهما: مجموع واحد لكل زوج من منطقة ومنتج. غيّر B5 من Apple إلى Pear فتتغير خليتا East. والترتيب هنا هو ترتيب الظهور الأول للقيم؛ أما الجدول المحوري فيرتب تسمياته أبجديًا.

العدد أو المتوسط أو النسبة المئوية بدلًا من المجموع

في الجدول المحوري، انقر الحقل في مربع القيم واختر إعدادات حقل القيمة. تبدّل علامة التبويب تلخيص القيم حسب بين المجموع والعدد والمتوسط والحد الأقصى والحد الأدنى؛ وتحوّل علامة التبويب إظهار القيم كـ الأرقام إلى % من الإجمالي الكلي و**% من إجمالي العمود** ومجموع تراكمي وغيرها. ولكل منها صيغة مقابلة مباشرة:

الصيغإعادة الضبط

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

لدى North ثلاثة طلبات بمتوسط 151.7، ولدى South ثلاثة بمتوسط 101.7، ولدى East طلبان بمتوسط 85.0. وعمود % من الإجمالي الكلي موجود في الورقة الأولى في هذه الصفحة.

تصفية الملخص حسب منتج واحد

يضع مربع عوامل التصفية قائمة منسدلة فوق الجدول المحوري. ونسخة الصيغ خلية فيها قائمة منسدلة مع SUMIFS، التي تضيف شرطًا آخر إلى SUMIF. اختر منتجًا في F1:

الصيغإعادة الضبط

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

مع اختيار Apple، تعرض North القيمة 215، وSouth القيمة 150، وEast القيمة 60. اختر Pear فتتغير إلى 240 و155 و110. راجع SUMIFS لمزيد من الشروط وصفحة القائمة المنسدلة لمعرفة طريقة إضافة القائمة في الاكسل.

تحديث الجدول المحوري

يحتفظ الجدول المحوري بنسخة من البيانات المصدر (ذاكرة PivotTable المؤقتة) ولا يعيد الحساب عندما تتغير خلية في المصدر. بعد تعديل البيانات:

• انقر بزر الماوس الأيمن في أي مكان من الجدول المحوري واختر تحديث، أو اضغط Alt+F5 على Windows.
• يحدّث بيانات > تحديث الكل (Ctrl+Alt+F5) كل جدول محوري في المصنف.
• للتحديث في كل مرة يُفتح فيها الملف، انقر الجدول المحوري بزر الماوس الأيمن، واختر خيارات PivotTable، وفي علامة التبويب بيانات حدد تحديث البيانات عند فتح الملف.

الصفوف الجديدة المضافة تحت نطاق المصدر لا تُضمَّن، حتى بعد التحديث. غيّر النطاق من تحليل PivotTable > تغيير مصدر البيانات، أو الأفضل أن تحوّل المصدر إلى جدول قبل إنشاء الجدول المحوري: حدد البيانات واضغط Ctrl+T، أو استخدم إدراج > جدول. يكبر الجدول عندما تضيف صفوفًا، ويلتقطها الجدول المحوري عند التحديث التالي.

جمع كل المناطق بصيغة واحدة

الصيغإعادة الضبط

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

دورك: في F2، اجمع مبيعات كل منطقة مذكورة في E2:E4 بصيغة واحدة.

تحققعرض الإجابة

تمتد الإجابة بالقيم 455 و305 و170. إعطاء SUMIF القائمة E2:E4 كلها كشرط يعيد مجموعًا واحدًا لكل منطقة، فلا شيء تنسخه إلى الأسفل. ولا يحتوي Excel 2019 وما قبله على UNIQUE ولا على الامتداد: اكتب المناطق في E2:E4 وانسخ =SUMIF($A$2:$A$9,E2,$C$2:$C$9) إلى الأسفل. وبدون علامات $ تنزل النطاقات مع كل صف وتخرج المجاميع خاطئة.

دالتا GROUPBY وPIVOTBY: جدول محوري بصيغة واحدة

يحتوي Excel لـ Microsoft 365 على دالتين تبنيان ملخصًا كاملًا من صيغة واحدة وتعيدان الحساب كأي صيغة، دون تحديث. وتحتاجان إلى اشتراك Microsoft 365 حالي. للبيانات السابقة:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

تأخذ GROUPBY حقل الصفوف، والقيم، والدالة (SUM أو COUNTA أو AVERAGE أو MAX أو PERCENTOF). وتضيف PIVOTBY حقل أعمدة بينهما. وكلتاهما ترتبان التسميات وتضيفان صفوف المجاميع، مثل الجدول المحوري.

جدول محوري أم صيغ: أيهما تستخدم

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