المجموع حسب المجموعة

= IF (A2=A1,'', SUMIF (A:A,A2,B:B))

لبيانات الإجمالي الفرعي حسب المجموعة أو التسمية ، مباشرة في جدول ، يمكنك استخدام صيغة تستند إلى الدالة SUMIF.

تسليط الضوء على كل صف في التفوق

في المثال الموضح ، الصيغة في D5 هي:

 
= IF (B5=B4,'', SUMIF (B:B,B5,C:C))

ملاحظة: يجب فرز البيانات حسب عمود التجميع للحصول على نتائج معقولة.

يعتمد إطار عمل هذه الصيغة على IF ، التي تختبر كل قيمة في العمود B لمعرفة ما إذا كانت مماثلة للقيمة الموجودة في 'الخلية أعلاه'. عندما تتطابق القيم ، لا تُرجع الصيغة أي شيء (''). عندما تختلف القيم ، تستدعي الدالة IF SUMIF:

 
 SUMIF (B:B,B5,C:C)

في كل صف يتم فيه تشغيل SUMIF بواسطة IF ، يحسب SUMIF مجموع كل الصفوف المتطابقة في العمود C (C: C). المعايير المستخدمة بواسطة SUMIF هي قيمة الصف الحالي للعمود B (B5) ، والتي تم اختبارها مقابل كل العمود B (B: B).

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

أداء

قد يبدو استخدام مرجع عمود كامل فكرة سيئة ، لأن الإصدارات الحالية من Excel تحتوي على أكثر من مليون صف. ومع ذلك ، أظهر الاختبار أن Excel يقوم فقط بتقييم البيانات الموجودة في 'النطاق المستخدم' (A1 إلى عنوان تقاطع آخر عمود تم استخدامه وآخر صف مستخدم) مع هذا النوع من الصيغة.

يمتلك تشارلز ويليامز في Fast Excel ملف مقالة جيدة حول هذا الموضوع ، مع مجموعة كاملة من نتائج التوقيت.

لتسلسل البيانات النصية ، يمكنك استخدام عامل تشغيل العلامة النجمية (*).

لماذا حول الجداول المحورية؟

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