حساب القيم في تقرير PivotTable

لحساب القيم في 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 لحساب حقول القيمة التي تحتوي على بيانات رقمية ودالة التلخيص Count لحساب حقول البيانات التي تحتوي على نص. يمكنك اختيار دالة تلخيص مختلفة، مثل Average أو Max أو Min، لإجراء مزيد من التحليل والتخصيص لبياناتك. يمكنك أيضاً إنشاء الصيغ الخاصة بك التي تستخدم عناصر من التقرير أو بيانات ورقة عمل أخرى عن طريق إنشاء حقل محسوب أو عنصر محسوب داخل حقل.

يمكنك إنشاء صيغ فقط في التقارير التي تستند إلى بيانات مصدر غير OLAP. لا يمكنك استخدام الصيغ في التقارير التي تستند إلى قاعدة بيانات OLAP. عند استخدام صيغ في PivotTables‏‏، عليك معرفة المزيد حول القواعد التالية الخاصة ببناء جملة الصيغة وسلوك الصيغة:

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

• اسمي الحقل والعنصر    يستخدم Excel اسمي الحقل والعنصر لتحديد تلك العناصر في تقرير في الصيغ. في المثال التالي، تستخدم البيانات الموجودة في نطاق c3: c9 اسم الحقل مشتقات الألبان. يمكن أن يقوم عنصر محتسب في حقل النوع الذي يقدر المبيعات لمنتج جديد استناداً إلى مبيعات مشتقات الألبان باستخدام صيغة مثل مشتقات الألبان= * 115%.

  

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

• صيغ تعمل على إجمالي المجموع، وليس السجلات الفردية    تعمل صيغ الحقول المحسوبة على مجموع البيانات الأساسية لأي حقول في الصيغة. على سبيل المثال، تقوم صيغة الحقل المحسوب =المبيعات * 1.2‏ بضرب مجموع المبيعات لكل نوع ومنطقة في 1.2؛ ولا يتم ضرب كل قيمة بيع فردية في 1.2 وبعد ذلك يتم جمع المبالغ المضروبة.

  تعمل صيغ العناصر المحسوبة على السجلات الفردية. على سبيل المثال، تقوم صيغة العنصر المحسوب = مشتقات الألبان * 115% بضرب كل المبيعات الفردية لمشتقات الألبان في 115%، ثم يتم تلخيص المبالغ المضروبة معاً في منطقة القيم.

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

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

• أسماء الحقول في مراجع العناصر    يمكنك تضمين اسم الحقل في مرجع عنصر ما. يجب أن يكون اسم العنصر في أقواس مربعة، على سبيل المثال المنطقة [شمال]. استخدام هذا التنسيق لتجنب أخطاء #NAME؟ عندما يحمل عنصران في حقلين مختلفين نفس الاسم في تقرير. على سبيل المثال، إذا احتوى تقرير على عنصر يُسمى "لحوم" في حقل "النوع" وعنصر آخر يُسمى "لحوم" في حقل "الفئة"، يمكنك منع أخطاء #NAME؟ بالإشارة إلى العناصر النوع [لحوم] والفئة [لحوم].

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

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

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

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

للاطلاع على طريقة عرض المبيعات إذا تم زيادتها بمقدار 10 بالمائة، يمكنك إنشاء حقل محسوب في PivotTable المقترن الذي يستخدم صيغة مثل =المبيعات * 110%.

تظهر النتيجة مباشرة في PivotChart، كما هو موضح في التخطيط التالي:

للاطلاع على علامة بيانات منفصلة للمبيعات في منطقة "شمال" ناقص تكلفة نقل بقيمة 8 بالمائة، يمكنك إنشاء عنصر محسوب في حقل "المنطقة" باستخدام صيغة مثل =شمال – (شمال * 8%).

سيكون المخطط الناتج بالشكل التالي:

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

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

بالنسبة للعناصر المحسوبة، يمكنك إدخال صيغ مختلفة في كل خلية تلو الأخرى. على سبيل المثال، إذا احتوى عنصر محسوب باسم OrangeCounty على صيغة =Oranges * .25‏ خلال كافة الأشهر، يمكنك تغيير الصيغة إلى =Oranges *.5‏ لكل من يونيو ويوليو وأغسطس.

إذا كان لديك عدة عناصر محسوبة أو صيغ، يمكنك ضبط ترتيب الحساب.

إضافة حقل محسوب

1. انقر فوق PivotTable.

   يؤدي ذلك إلى عرض أدوات PivotTable، مع إضافة علامتي التبويب تحليل وتصميم.

2. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق حقل محسوب.

   

3. في مربع الاسم، اكتب اسماً للحقل.

4. في مربع الصيغة، أدخل صيغة للحقل.

   لاستخدام البيانات من حقل آخر في الصيغة، انقر فوق الحقل في مربع الحقول ثم انقر فوق إدراج حقل. على سبيل المثال، لحساب 15% من عمولة لكل قيمة في حقل المبيعات، يمكنك إدخال = Sales * 15%‏.

5. انقر فوق إضافة.

إضافة عنصر محسوب إلى حقل

1. انقر فوق PivotTable.

   يؤدي ذلك إلى عرض أدوات PivotTable، مع إضافة علامتي التبويب تحليل وتصميم.

2. إذا تم تجميع العناصر في الحقل، في علامة التبويب تحليل، من مجموعة المجموعة، انقر فوق إلغاء التجميع.

   

3. انقر فوق الحقل حيث تريد إضافة عنصر محسوب.

4. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق عنصر محسوب.

   

5. اكتب اسماً للعنصر المحسوب في مربع الاسم.

6. في مربع الصيغة، أدخل صيغة للعنصر.

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

7. انقر فوق إضافة.

أدخل صيغ مختلفة في كل خلية تلو الأخرى.

1. انقر فوق الخلية التي تريد تغيير الصيغة فيها.

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

2. في شريط الصيغة، اكتب التغييرات في الصيغة.

ضبط ترتيب العمليات الحسابية لعدة عناصر محسوبة أو صيغ.

1. انقر فوق PivotTable.

   يؤدي ذلك إلى عرض أدوات PivotTable، مع إضافة علامتي التبويب تحليل وتصميم.

2. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق ترتيب الحل.

   

3. انقر فوق صيغة ثم فوق نقل لأعلى أو نقل لأسفل.

4. تابع حتى تصبح الصيغ بالترتيب الذي تريد حسابها من خلاله.

يمكنك عرض قائمة بكافة الصيغ التي تُستخدم في PivotTable الحالي.

1. انقر فوق PivotTable.

   يؤدي ذلك إلى عرض أدوات PivotTable، مع إضافة علامتي التبويب تحليل وتصميم.

2. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق قائمة الصيغ.

   

قبل تحرير صيغة، حدد ما إذا كانت هذه الصيغة في حقل محسوب أو عنصر محسوب. إذا كانت الصيغة في عنصر محسوب، حدد أيضاً ما إذا كانت الصيغة هي الصيغة الوحيدة للعنصر المحسوب.

بالنسبة للعناصر المحسوبة، يمكنك تحرير الصيغ الفردية لخلايا معينة لعنصر محسوب. على سبيل المثال، إذا احتوى عنصر محسوب باسم OrangeCalc على صيغة =Oranges * .25‏ خلال كافة الأشهر، يمكنك تغيير الصيغة إلى =Oranges *.5‏ لكل من يونيو ويوليو وأغسطس.

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

1. انقر فوق PivotTable.

   يؤدي ذلك إلى عرض أدوات PivotTable، مع إضافة علامتي التبويب تحليل وتصميم.

2. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق قائمة الصيغ.

   

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

   على سبيل المثال، قد يكون لديك صيغة افتراضية لعنصر محسوب يُسمى MyItem وصيغة أخرى لهذا العنصر محددة باسم مبيعات يناير MyItem‏. في PivotTable، ستجد هذه الصيغة في خلية "المبيعات" لصف "MyItem" وعمود "يناير".

4. تابع من خلال استخدام أحد أساليب التحرير التالية.

تحرير صيغة حقل محسوب

1. انقر فوق PivotTable.

   يؤدي ذلك إلى عرض أدوات PivotTable، مع إضافة علامتي التبويب تحليل وتصميم.

2. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق حقل محسوب.

   

3. في مربع الاسم ، حدد الحقل المحسوب الذي تريد تغيير الصيغة فيه.

4. في مربع الصيغة ،قم بتحرير الصيغة.

5. انقر فوق تعديل.

تحرير صيغة واحدة لعنصر محسوب

1. انقر فوق الحقل الذي يحتوي على العنصر المحسوب.

2. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق عنصر محسوب.

   

3. في مربع الاسم ، حدد العنصر المحسوب.

4. في مربع الصيغة، قم بتحرير الصيغة.

5. انقر فوق تعديل.

تحرير صيغة فردية لخلية محددة لعنصر محسوب

1. انقر فوق الخلية التي تريد تغيير الصيغة فيها.

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

2. في شريط الصيغة، اكتب التغييرات في الصيغة.

   تلميح: إذا كان لديك عدة عناصر محسوبة أو صيغ، يمكنك ضبط ترتيب الحساب. للحصول على مزيد من المعلومات، راجع ضبط ترتيب العمليات الحسابية لعدة عناصر محسوبة أو صيغ.

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

   تظهر الحقول المحسوبة في قائمة حقول PivotTable. تظهر العناصر المحسوبة كعناصر داخل الحقول الأخرى.

2. نفّذ أحد الإجراءات التالية:

   • لحذف حقل محسوب، انقر فوق أي مكان في PivotTable.

   • لحذف عنصر محسوب، في PivotTable، انقر فوق الحقل الذي يحتوي على العنصر الذي تريد حذفه.

     يؤدي ذلك إلى عرض أدوات PivotTable، مع إضافة علامتي التبويب تحليل وتصميم.

3. في علامة التبويب تحليل، في مجموعة العمليات الحسابية، انقر فوق حقول وعناصر ومجموعات، ثم انقر فوق عنصر محسوب أو حقل محسوب.

   

4. في مربع الاسم ، حدد الحقل أو العنصر الذي تريد حذفه.

5. انقر فوق حذف.