شرح دالة SUBTOTAL في إكسل: حساب الخلايا الظاهرة فقط
تعلم كيفية استخدام دالة SUBTOTAL في إكسل لجمع الخلايا الظاهرة بعد التصفية، والفرق بين 9 و109 وتفادي تكرار الجمع بسهولة.
بقلم Ayoub Farid · · 2 دقائق قراءة
تقوم الدالة SUM بجمع كافة الصفوف، حتى تلك التي قمت بإخفائها عبر التصفية (الفلتر). في المقابل، تحسب الدالة SUBTOTAL الصفوف الظاهرة أمامك فقط، مما يجعلها الخيار المثالي لحساب المجاميع في أي جدول بيانات يحتوي على تصفية.
الصيغة العامة (Syntax)
=SUBTOTAL(function_num, ref1, ...)| الدالة | تشمل الصفوف المخفية يدوياً | تتجاهل الصفوف المخفية يدوياً |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| SUM | 9 | 109 |
يتجاهل كلا الخيارين الصفوف التي تم إخفاؤها بواسطة التصفية (الفلتر). وتتميز سلسلة الأرقام 100 بأنها تتجاهل أيضاً الصفوف التي أخفيتها يدوياً (عبر النقر بزر الفأرة الأيمن > إخفاء).
مثال عملي
إذا كانت قيم المبيعات في النطاق D2:D500، يمكنك كتابة الصيغة التالية أعلى الجدول:
=SUBTOTAL(9,D2:D500)عند تصفية النتائج لعرض منطقة "الشمال" فقط، سيعرض المجموع مبيعات الشمال وحدها. وبمجرد إلغاء التصفية، سيعود لعرض الإجمالي بالكامل.
عد الصفوف الظاهرة فقط
=SUBTOTAL(3,A2:A500)تُظهر لك هذه المعادلة عدد الصفوف المطابقة لمعايير التصفية الحالية.
تتجاهل دوال SUBTOTAL الأخرى تلقائياً
إذا كان العمود يحتوي على صفوف فرعية تستخدم الدالة SUBTOTAL لكل مجموعة، فإن صيغة SUBTOTAL للإجمالي الكلي ستتخطى هذه الصفوف تلقائياً لتفادي تكرار الحساب مرتين. (يُدرج خيار Data > Subtotal صفوف المجموعات هذه بصورة آلية).
دالة AGGREGATE: دالة SUBTOTAL مع معالجة الأخطاء
=AGGREGATE(9,7,D2:D500)الرقم 9 يعني دالة SUM، والرقم 7 يعني تجاهل الصفوف المخفية بالإضافة إلى تجاهل خلايا الأخطاء.
جداول إكسل تقوم بالمهمة تلقائياً
داخل أي جدول إكسل منسق (بالضغط على Ctrl+T)، يمكنك تفعيل صف الإجمالي عبر Table Design > Total Row ليقوم بإدراج دوال إكسل من نوع SUBTOTAL تلقائياً دون تدخل منك.
الأسئلة الشائعة
كيف أقوم بجمع الخلايا الظاهرة فقط في إكسل؟
استخدم =SUBTOTAL(9,range) (أو استخدم 109 لتجاهل الصفوف المخفية يدوياً أيضاً).
ما الفرق بين 9 و109 في دالة SUBTOTAL؟
يتجاهل الخياران الصفوف المخفية بالتصفية، بينما يتجاهل الرقم 109 أيضاً أي صفوف تم إخفاؤها يدوياً.
لماذا لا يتغير ناتج الدالة SUM بعد تطبيق التصفية؟
لأن الدالة SUM تجمع كل الصفوف بما فيها المخفية، لذا ينصح باستخدام دالة SUBTOTAL بدلاً منها.
هل تعمل دالة SUBTOTAL في جداول بيانات Google Sheets؟
نعم، وتعمل بنفس أرقام الدوال تماماً.