في PivotTables، يمكنك استخدام دالات التلخيص في حقول القيم لدمج القيم من بيانات المصدر الأساسية. إذا لم توفر لك دالات التلخيص والعمليات الحسابية المخصصة النتائج التي تريدها، يمكنك إنشاء الصيغ الخاصة بك في الحقول المحسوبة والعناصر المحسوبة. على سبيل المثال، يمكنك إضافة عنصر محسوب بصيغة لعمولة المبيعات، والتي يمكن أن تختلف حسب كل منطقة. سوف يتضمن PivotTable العمولة تلقائياً في الإجماليات الفرعية والإجماليات الكلية.
هناك طريقة أخرى للحساب وهي استخدام المقاييس في Power Pivot، والتي تقوم بإنشائها باستخدام صيغة تعبيرات تحليل البيانات (DAX ). لمزيد من المعلومات، راجع إنشاء مقياس في Power Pivot.
توفر PivotTables طرق لحساب البيانات. تعرف على أساليب العملية الحسابية المتوفرة وكيفية تأثر العمليات الحسابية بنوع البيانات المصدر وكيفية استخدام الصيغ في PivotTables وPivotCharts.
أساليب العملية الحسابية المتوفرة
لحساب القيم في PivotTable، يمكنك استخدام أي من أنواع أساليب العملية الحسابية التالية أو كلها:
دالات التلخيص في حقول القيم تلخص البيانات الموجودة في منطقة القيم بيانات المصدر الأساسية في PivotTable. على سبيل المثال، راجع بيانات المصدر التالية:
ترجع PivotTables وPivotCharts التالية. إذا قمت بإنشاء PivotChart من البيانات في PivotTable، فإن القيم الموجودة في PivotChart تعكس العمليات الحسابية في تقرير PivotTable المقترن.
في PivotTable، يوفر حقل عمود الشهر عنصري مارس وأبريل. يوفر حقل صف المنطقة العناصر شمال وجنوب وشرق وغرب. تمثل القيمة الناتجة عند تقاطع عمود أبريل وصف شمال إجمالي إيرادات المبيعات من السجلات الموجودة في بيانات المصدر التي تحتوي على قيم الشهر التي تخص أبريل وقيم المنطقة التي تخص شمال.
في PivotChart، يمكن أن يكون حقل المنطقة عبارة عن حقل فئة يعرض شمال وجنوب وشرق وغرب كفئات. يمكن أن يكون حقل الشهر سلسلة من الحقول التي تعرض العناصر مارس وأبريل ومايو كسلسلة موضحة في وسيلة الإيضاح. يمكن أن يحتوي حقل قيم يسمى إجمالي المبيعات على علامات بيانات تمثل إجمالي الإيرادات في كل منطقة لكل شهر. على سبيل المثال، يمكن أن تمثل علامة بيانات واحدة، عند موضعها في المحور العمودي (القيمة)، إجمالي المبيعات في أبريل في منطقة شمال.
لحساب حقول القيم، تتوفر دالات التلخيص التالية لكافة أنواع بيانات المصدر باستثناء بيانات مصدر المعالجة التحليلية عبر الإنترنت (OLAP).
المفتاح الوظيفي يلخص Sum مجموع القيم. هذه هي الدالة الافتراضية للبيانات الرقمية. Count عدد قيم البيانات. تعمل دالة التلخيص Count نفس وظيفة الدالة COUNTA. Count هي الدالة الافتراضية للبيانات وليس الأرقام. Average متوسط القيم. Max أكبر قيمة. Min أصغر قيمة. Product حاصل ضرب القيم. عدد الأرقام عدد قيم البيانات التي تكون عبارة عن أرقام. تعمل دالة التلخيص Count Numbers نفس وظيفة دالة COUNT. StDev تقدير الانحراف المعياري لمحتوى، حيث تكون العينة هي مجموعة فرعية من المحتوى بأكمله. StDevp الانحراف المعياري لمحتوى، حيث يكون المحتوى عبارة عن كافة البيانات المراد تلخيصها. Var تقدير التباين لمحتوى، حيث تكون العينة هي مجموعة فرعية من المحتوى بأكمله. Varp التباين لمحتوى، حيث يكون المحتوى عبارة عن كافة البيانات المراد تلخيصها.
العمليات الحسابية المخصصة يعرض الحساب المخصص القيم استنادا إلى عناصر أو خلايا أخرى في منطقة البيانات. على سبيل المثال، يمكنك عرض القيم في حقل البيانات مجموع المبيعات كنسبة مئوية من مبيعات مارس، أو كحساب إجمالي حالي للعناصر الموجودة في حقل الشهر.
تتوفر الدالات التالية للعمليات الحسابية المخصصة في حقول القيمة.المفتاح الوظيفي النتيجة بلا حساب يعرض القيمة التي يتم إدخالها في الحقل. % من الإجمالي الكلي يعرض القيم كنسبة مئوية من الإجمالي الكلي لكافة القيم أو نقاط البيانات في التقرير. % من إجمالي الأعمدة يعرض كافة القيم الموجودة في كل عمود أو سلسلة كنسبة مئوية من الإجمالي للعمود أو السلسلة. % من إجمالي الصفوف يعرض القيمة الموجودة في كل صف أو فئة كنسبة مئوية من الإجمالي للصف أو الفئة. % من يعرض القيم كنسبة مئوية من قيمة عنصر الأساس في حقل الأساس. % من إجمالي الصفوف الأصلية يقوم بحساب القيم كما يلي:
(قيمة العنصر) / (قيمة العنصر الأصلي في الصفوف)% من إجمالي الأعمدة الأصلية يقوم بحساب القيم كما يلي:
(قيمة العنصر) / (قيمة العنصر الأصلي في الأعمدة)% من الإجمالي الأصلي يقوم بحساب القيم كما يلي:
(قيمة العنصر) / (قيمة العنصر الأصلي لأجل حقل الأساس المحدد)اختلاف عن يعرض القيم كالاختلاف عن قيمة عنصر الأساس في حقل الأساس. % اختلاف عن يعرض القيم كاختلاف النسبة المئوية عن قيمة عنصر الأساس في حقل الأساس. الإجمالي الحالي يعرض قيمة العناصر المتتابعة في حقل الأساس كإجمالي حالي. % الإجمالي الحالي يحسب قيمة العناصر المتتابعة في حقل الأساس والتي يتم عرضها كإجمالي حالي كنسبة مئوية. ترتيب من الأصغر إلى الأكبر يعرض ترتيب القيم المحددة في حقل معين، مع سرد أصغر عنصر في الحقل بالقيمة 1، وسرد كل قيمة أكبر بقيمة ترتيب أعلى. ترتيب من الأكبر إلى الأصغر يعرض ترتيب القيم المحددة في حقل معين، مع سرد أكبر عنصر في الحقل بالقيمة 1، وسرد كل قيمة أصغر بقيمة ترتيب أعلى. الفهرس يقوم بحساب القيم كما يلي:
((القيمة في خلية) x (إجمالي الإجماليات الكلية)) / ((الإجمالي الكلي للصفوف) x (الإجمالي الكلي للأعمدة))
- الصيغ إذا لم توفر الدالات الموجزة والحسابات المخصصة النتائج التي تريدها، يمكنك إنشاء الصيغ الخاصة بك في الحقول المحسوبة والعناصر المحسوبة. على سبيل المثال، يمكنك إضافة عنصر محسوب بصيغة لعمولة المبيعات، والتي يمكن أن تختلف حسب كل منطقة. سوف يتضمن التقرير العمولة تلقائياً في الإجماليات الفرعية والإجماليات الكلية.
كيفية تأثير نوع بيانات المصدر على العمليات الحسابية
تعتمد العمليات الحسابية والخيارات المتوفرة في تقرير على ما إذا كانت بيانات المصدر تأتي من قاعدة بيانات OLAP أو مصدر بيانات غير OLAP.
-
العمليات الحسابية استنادا إلى بيانات مصدر OLAP بالنسبة ل PivotTables التي تم إنشاؤها من مكعبات OLAP، يتم حساب القيم الملخصة مسبقا على خادم OLAP قبل أن يعرض Excel النتائج. لا يمكنك تغيير كيفية حساب هذه القيم المحسوبة مُسبقاً في PivotTable. على سبيل المثال، لا يمكن تغيير دالة التلخيص التي يتم استخدامها لحساب حقول البيانات أو الإجماليات الفرعية أو إضافة حقول محسوبة أو عناصر محسوبة.
علاوة على ذلك، إذا كان خادم OLAP يوفر حقولاً محسوبة، المعروفة باسم الأعضاء المحسوبين، فسترى هذه الحقول في قائمة حقول PivotTable. سيتم أيضاً عرض أي حقول وعناصر محسوبة تم إنشاؤها بواسطة وحدات الماكرو المكتوبة في Visual Basic for Applications (VBA) وتخزينها في المصنف الخاص بك، ولكن لن تتمكن من تغيير هذه الحقول والعناصر. إذا كنت بحاجة إلى أنواع إضافية من العمليات الحسابية، فاتصل بمسؤول قاعدة بيانات OLAP لديك.
بالنسبة لبيانات مصدر OLAP، يمكنك تضمين قيم العناصر المخفية أو استبعادها عند حساب الإجماليات الفرعية والإجماليات الكلية. - العمليات الحسابية استنادا إلى بيانات مصدر غير OLAP في PivotTables التي تستند إلى أنواع أخرى من البيانات الخارجية أو بيانات ورقة العمل، يستخدم Excel الدالة Sum summary لحساب حقول القيم التي تحتوي على بيانات رقمية، والدالة التلخيصية Count لحساب حقول البيانات التي تحتوي على نص. يمكنك اختيار دالة تلخيص مختلفة، مثل Average أو Max أو Min، لإجراء مزيد من التحليل والتخصيص لبياناتك. يمكنك أيضاً إنشاء الصيغ الخاصة بك التي تستخدم عناصر من التقرير أو بيانات ورقة عمل أخرى عن طريق إنشاء حقل محسوب أو عنصر محسوب داخل حقل.
استخدام الصيغ في جداول PivotTables
يمكنك إنشاء صيغ فقط في التقارير التي تستند إلى بيانات مصدر غير OLAP. لا يمكنك استخدام الصيغ في التقارير التي تستند إلى قاعدة بيانات OLAP. عند استخدام صيغ في PivotTables، عليك معرفة المزيد حول القواعد التالية الخاصة ببناء جملة الصيغة وسلوك الصيغة:
عناصر صيغة PivotTable في الصيغ التي تقوم بإنشائها للحمولات المحسوبة والعناصر المحسوبة، يمكنك استخدام عوامل التشغيل والتعبيرات كما تفعل في صيغ ورقة العمل الأخرى. يمكنك استخدام الثوابت والإشارة إلى بيانات من التقرير، ولكن لا يمكن استخدام مراجع خلايا أو أسماء معرفة. لا يمكنك استخدام دالات ورقة العمل التي تتطلب مراجع خلايا أو أسماء معرفة كوسيطات، ولا يمكنك استخدام دالات صفيف.
أسماء الحقول والعناصر يستخدم Excel أسماء الحقول والعناصر لتحديد عناصر التقرير هذه في الصيغ. في المثال التالي، تستخدم البيانات الموجودة في نطاق c3: c9 اسم الحقل مشتقات الألبان. يمكن أن يقوم عنصر محتسب في حقل النوع الذي يقدر المبيعات لمنتج جديد استناداً إلى مبيعات مشتقات الألبان باستخدام صيغة مثل مشتقات الألبان= * 115%.
ملاحظة
في PivotChart، يتم عرض أسماء الحقول في قائمة حقول PivotTable، ويمكن رؤية أسماء العناصر في كل قائمة منسدلة بالحقل. لا تخلط بين هذه الأسماء وتلك التي تراها في تلميحات المخطط، والتي تعكس أسماء نقطة البيانات والسلسلة.
تعمل الصيغ على إجماليات المجموع، وليس على سجلات فردية تعمل صيغ الحقول المحسوبة على مجموع البيانات الأساسية لأي حقول في الصيغة. على سبيل المثال، تقوم صيغة الحقل المحسوب =المبيعات * 1.2 بضرب مجموع المبيعات لكل نوع ومنطقة في 1.2؛ ولا يتم ضرب كل قيمة بيع فردية في 1.2 وبعد ذلك يتم جمع المبالغ المضروبة.
تعمل صيغ العناصر المحسوبة على السجلات الفردية. على سبيل المثال، تقوم صيغة العنصر المحسوب = مشتقات الألبان * 115% بضرب كل المبيعات الفردية لمشتقات الألبان في 115%، ثم يتم تلخيص المبالغ المضروبة معاً في منطقة القيم.المسافات والأرقام والرموز في الأسماء في اسم يتضمن أكثر من حقل واحد، يمكن أن تكون الحقول بأي ترتيب. في المثال أعلاه، الخلايا C6:D6 يمكن أن تكون 'شمال أبريل أو 'أبريل شمال'. استخدم علامات اقتباس مفردة حول الأسماء التي تعتبر أكثر من كلمة أو التي تتضمن أرقاماً أو رموزاً.
إجماليات لا يمكن أن تشير الصيغ إلى الإجماليات (على سبيل المثال، إجمالي مارس وإجمالي أبريل والإجمالي الكلي في المثال).
أسماء الحقول في مراجع العناصر يمكنك تضمين اسم الحقل في مرجع إلى عنصر. يجب أن يكون اسم العنصر في أقواس مربعة، على سبيل المثال المنطقة [شمال]. هل تستخدم هذا التنسيق لتجنب #NAME؟ الأخطاء عندما يكون لعنادين في حقلين مختلفين في تقرير نفس الاسم. على سبيل المثال، إذا كان التقرير يحتوي على عنصر يسمى اللحوم في حقل النوع وعنصر آخر يسمى اللحوم في حقل الفئة، فيمكنك منع #NAME؟ الأخطاء بالإشارة إلى العناصر باسم Type[اللحوم] و Category[اللحوم].
الإشارة إلى العناصر حسب الموضع يمكنك الرجوع إلى عنصر حسب موضعه في التقرير كما تم فرزه وعرضه حاليا. نوع[1] هو مشتقات الألبان ونوع[2] هو المأكولات البحرية. يمكنك تغيير العنصر المُشار إليه بهذه الطريقة كلما تغير موضع العناصر أو تم عرض عناصر مختلفة أو إخفاؤها. لا يتم حساب العناصر المخفية في هذا الفهرس.
يمكنك استخدام مواضع نسبية للإشارة إلى العناصر. يتم تحديد المواضع حسب العنصر المحسوب الذي يحتوي على الصيغة. إذا كان جنوب هو المنطقة الحالية، تكون المنطقة[-1] هي شمال؛ وإذا كان شمال هو المنطقة الحالية، تكون المنطقة[+ 1] هي جنوب. على سبيل المثال، يمكن أن يقوم عنصر محسوب باستخدام الصيغة =Region[-1] * 3%. إذا كان الموضع الذي حددته قبل العنصر الأول أو بعد العنصر الأخير في الحقل، ينتج عن الصيغة خطأ #REF!.
استخدام صيغ في Pivottable
لاستخدام صيغ في PivotChart، يمكنك إنشاء الصيغ الموجودة في PivotTable المقترن، حيث يمكنك الاطلاع على القيم الفردية التي تتشكل منها بياناتك، ثم يمكنك عرض النتائج بطريقة رسومية في PivotChart.
على سبيل المثال، يعرض PivotChart التالي المبيعات لكل مندوب مبيعات حسب كل منطقة:
للاطلاع على طريقة عرض المبيعات إذا تم زيادتها بمقدار 10 بالمائة، يمكنك إنشاء حقل محسوب في PivotTable المقترن الذي يستخدم صيغة مثل =المبيعات * 110%.
تظهر النتيجة مباشرة في PivotChart، كما هو موضح في التخطيط التالي:
للاطلاع على علامة بيانات منفصلة للمبيعات في منطقة "شمال" ناقص تكلفة نقل بقيمة 8 بالمائة، يمكنك إنشاء عنصر محسوب في حقل "المنطقة" باستخدام صيغة مثل =شمال – (شمال * 8%).
سيكون المخطط الناتج بالشكل التالي:
ومع ذلك، يظهر العنصر المحسوب الذي يتم إنشاؤه في حقل مندوب المبيعات كسلسلة ممثلة في وسيلة الإيضاح ويظهر في المخطط كنقطة بيانات في كل فئة.
هل تحتاج إلى مزيد من المساعدة؟
يمكنك دائما سؤال خبير في مجتمع Excel التقني أو الحصول على الدعم في المجتمعات.