نظرة عامة حول القيم والعمليات الحسابية المخصصة والصيغ في تقرير PivotTable أو PivotChart

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

في هذا المقال


كيف تحسب تقارير PivotTable وتقارير PivotChart البيانات

هناك ثلاثة أنواع من العمليات الحسابية يمكنك القيام بها في تقرير PivotTable أو تقرير PivotChart

القيم والدالات

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

البيانات المصدر التالية:

مثال على بيانات مصدر PivotTable

ينتج تقارير PivotTable و PivotChart التالية.

مثال على تقرير PivotTable مثال على تقرير PivotChart

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

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

الحسابات المخصصة

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

الصيغ

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

أعلى الصفحة أعلى الصفحة

كيف يؤثر نوع البيانات المصدر على الحسابات

تعتمد الحسابات والخيارات المتوفرة في التقرير على ما إذا كانت البيانات المصدر واردة من قاعدة بيانات OLAP أو مصدر بيانات غير OLAP.

بيانات مصدر OLAP

عمليات حسابية     بالنسبة للتقارير التي تم إنشاؤها من OLAPcubes (مكعب: بنية بيانات OLAP. يحتوي المكعب على أبعاد، مثل البلد/المنطقة/المدينة، وعلى حقول بيانات، مثل مقدار المبيعات. وتنظم الأبعاد أنواع البيانات بشكل هيكلي مع مستويات تفاصيل، وحقول بيانات لقياس الكميات.)، تكون القيم الملخصة محسوبة مسبقاً على خادم OLAP قبل أن يعرض Microsoft Office Excel النتائج. لذلك، لا يمكنك تغيير طريقة حساب هذه القيم من داخل التقرير. لا يمكنك تغيير دالة التلخيص المستخدمة في حساب حقول البيانات أو الإجماليات الفرعية، أو إضافة حقول محتسبة أو عناصر محتسبة. إذا كان خادم OLAP يوفر حقول محسوبة، تعرف بالعناصر المحسوبة، ستشاهد هذه الحقول في قائمة حقول PivotTable. ستشاهد أيضاً أي حقول وعناصر محسوبة التي يتم إنشاؤها بواسطة وحدات ماكرو التي تم كتابتها ضمن Visual Basic for Applications (Visual Basic for Applications (VBA): إصدار لغة وحدات الماكرو من Microsoft Visual Basic يُستخدم لبرمجة التطبيقات المستندة إلى Microsoft Windows ومُضمّن مع العديد من برامج Microsoft.) وتخزينها في المصنف، إلا أنه لن يكون بإمكانك تغيير هذه الحقول والعناصر. في حالة احتياجك إلى أنواع إضافية من الحسابات، اتصل بمسؤول قاعدة بيانات OLAP.

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

بيانات مصدر غير OLAP

عمليات حسابية     في التقارير المستندة إلى أنواع أخرى من البيانات الخارجية أو بيانات ورقة العمل، يستخدم Excel دالة التلخيص Sum لحساب حقول البيانات التي تحتوي على بيانات رقمية، ودالة التلخيص Count لحساب حقول القيم التي تحتوي على نص. يمكنك اختيار دالة تلخيص أخرى، مثل Average أوMax أو Min، لمزيد من التحليل والتخصيص لبياناتك. يمكنك أيضاً إنشاء الصيغ الخاصة بك التي تستخدم عناصر من بيانات التقرير أو ورقة عمل أخرى بإنشاء حقل أو عنصر محسوب داخل حقل.

أعلى الصفحة أعلى الصفحة

بناء جملة الصيغة

يمكنك إنشاء صيغ فقط في تقارير لا تستند إلى بيانات مصدر OLAP.

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

عناصر الصيغ     في الصيغ التي تقوم بإنشائها للحقول المحسوبة والعناصر المحسوبة، يمكنك استخدام عوامل تشغيل (عامل تشغيل: إشارة أو رمز يعيّن نوع الحساب ليتم إنجازه ضمن التعبير. وهناك عوامل تشغيل رياضية، وللمقارنة، ومنطقية، ومرجعية.) وتعبيرات كما في صيغ ورقة العمل الأخرى. ويمكنك استخدام الثوابت (ثابت: قيمة لا يتم حسابها مثل الرقم 210 ولذلك لا يتغير هذا الثابت. على سبيل المثال يعد الرقم 210 والنص "أرباح ربع سنوية" ثوابت. لا يعد التعبير أو القيمة الناتجة من تعبير ثابتًا. والإشارة إلى بيانات من التقرير، إلا أنه لا يمكنك استخدام مراجع الخلايا أو أسماء (الاسم: هو كلمة أو سلسلة من الأحرف تمثل خلية أو نطاق من الخلايا أو صيغة أو قيمة ثابت. استخدم أسماء سهلة "على سبيل المثال، المنتجات" للإشارة إلى نطاقات غير واضحة مثلSales!C20:C30.) معرّفة. لا يمكنك استخدام دالات ورقة العمل التي تتطلّب مراجع خلايا أو أسماء معرّفة كوسيطات، ولا يمكنك استخدام دالات صفيف.

الأسماء في التقارير     يوفر Excel الأسماء لتعريف عناصر التقرير في الصيغ. وتتألف الأسماء من أسماء الحقول والعناصر. في المثال التالي، البيانات في النطاق C3:C9 تسمى منتجات الألبان.

مثال على تقرير PivotTable

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

أمثلة     يمكن لاسم حقل محسوب مسمى "تنبؤ" أن يتنبأ بالطلبات المستقبلية بواسطة صيغة كما يلي:

=Sales * 1.2

قد يُستخدم عنصر محسوب في حقل "النوع" في تقدير المبيعات لمنتج جديد مستند إلى مبيعات "منتجات الألبان" الصيغة التالية:

=Dairy * 115%

تعمل الصيغ على إجمالي المجموع، وليس على سجلات فردية     تعمل صيغ الحقول المحسوبة على مجموع البيانات الأساسية لأية حقول في الصيغة. على سبيل المثال، فالصيغة Sales *1.2= تضرب 1.2 بمجموع المبيعات لكل نوع ومنطقة؛ ولا تضرب 1.2 بكل مبيعات فردية ومن ثمّ تجمع المبالغ المضروبة. أما صيغ العناصر المحسوبة، مع هذا، فتعمل على سجلات فردية؛ فصيغة العنصر المحسوب Dairy *115%= تضرب كل مبيعات اليومية للمأكولات البحرية بـ 115%، بعدها يجري تلخيص المبالغ المضروبة معًا في ناحية القيم.

المسافات والأرقام والرموز في الأسماء     في الاسم الذي يتضمن أكثر من حقل واحد، يمكن أن تكون الحقول في أي ترتيب. في المثال الموضح أعلاه، يمكن أن تكون الخلايا C6:D6 "أبريل شمال" أو "شمال أبريل". استخدم علامات الاقتباس المفردة حول الأسماء المؤلفة من أكثر من كلمة واحدة أو تلك التي تتضمّن أرقاماً أو رموزاً.

الإجمالي     لا يمكن أن تشير الصيغ إلى مجاميع (مثل مجموع شهر مارس وإجمالي أبريل والإجمالي الكلى في المثال).

أسماء الحقول في مراجع العناصر     يمكنك تضمين اسم الحقل الموجود في مرجع لعنصر. ويجب أن يكون اسم العنصر بين أقواس تربيعية، مثلاً المنطقة[شمال] Region[North]. استخدم هذا التنسيق لتجنّب قيم الخطأ ?NAME# عندما يكون لعنصرين في حقلين مختلفين في تقرير لهما الاسم نفسه. ففي حالة وجود عنصر مسمى "لحوم" Meat في حقل "النوع" Type وعنصر آخر مسمى "لحوم " Meat في حقل "الفئة" Category، يمكنك منع القيم الخطأ #NAME? بالإشارة إلى العناصر على هيئة Type[Meat] و Category[Meat].

الإشارة إلى عناصر حسب الموقع     يمكنك الإشارة إلى عنصر حسب موضعه في التقرير كما يتم فرزه وعرضه حالياً. النوع[1] Type[1] منتجات ألبان، والنوع[2] Type[2] مأكولات بحرية. يمكن تغيير العنصر المشار إليه بهذه الطريقة عند تغيير مواضع العناصر أو عند عرض العناصر أو إخفائها. لا يتم حساب العناصر المخفية في هذا الفهرس.

يمكنك استخدام مواضع نسبية للرجوع إلى العناصر. يتم تحديد العناصر بالنسبة إلى العنصر المحتسب الذي يحتوي على الصيغة. فإذا كان "الجنوب" هو المنطقة الحالية، تكون المنطقة [-1] هي "الشمال"؛ وإذا كان شمال هو المنطقة الحالية، تكون المنطقة [+1] هي "الجنوب". على سبيل المثال، يمكن لعنصر محتسب أن يستخدم الصيغة=المنطقة [-1] * 3% . إذا كان الموضع الذي عينته قبل العنصر الأول أو بعد العنصر الأخير في الحقل، ينتج عن الصيغة القيمة الخطأ #REF!.

أعلى الصفحة أعلى الصفحة

استخدام الصيغ في تقارير PivotChart

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

على سبيل المثال، يعرض تقرير PivotChart التالي مبيعات كل مندوب حسب المنطقة:

تقرير يظهر المبيعات لكل مندوب مبيعات حسب المنطقة

لمشاهدة ما يمكن أن تظهر عليه المبيعات إذا تم زيادتها بنسبة 10 بالمائة، يمكنك إنشاء حقل محسوب بواسطة الصيغ التالية:

=Sales * 110%

سينعكس الناتج في المخطط كما يلي:

عرض تقرير PivotChart للمبيعات التي تم زيادتها بنسبة 10 بالمائة حسب المنطقة

لمشاهدة علامة بيانات منفصلة للمبيعات في المنطقة "شمال" مطروحاً منه تكلفة نقل بنسبة 8 بالمائة، يمكنك إنشاء عنصر محسوب في الحقل منطقة بواسطة الصيغة التالية:

=North – (North * 8%)

قد يبدو الناتج كما يلي:

تقرير PivotChart ضمن عنصر محسوب.

رغم ذلك، قد يظهر عنصر محسوب تم إنشاؤه في حقل مندوب المبيعات، كسلسلة معروضة في وسيلة الإيضاح ويظهر في التخطيط كنقطة بيانات في كل فئة.

أعلى الصفحة أعلى الصفحة

 
 
يطبق على:
Excel 2007