دالة SUBTOTAL في Excel
دوال التجميع والجمع الشرطي
الغرض من الدالة
تقوم دالة SUBTOTAL بحساب إجمالي فرعي (Subtotal) لمجموعة من البيانات في قائمة أو قاعدة بيانات. الميزة الرئيسية لهذه الدالة هي قدرتها على تضمين أو استبعاد الصفوف المخفية يدوياً أو الصفوف التي تم إخفاؤها بواسطة التصفية (Filter)، مما يجعلها مثالية للتقارير الديناميكية.
صيغة الدالة (Syntax)
- function_num: رقم يحدد الدالة التجميعية المراد تطبيقها. (مطلوب).
- 1-11: تتضمن الصفوف المخفية يدوياً.
- 101-111: تستبعد الصفوف المخفية يدوياً (لكن لا تستبعد الصفوف المخفية بواسطة التصفية).
- أمثلة: 1 لـ AVERAGE، 2 لـ COUNT، 9 لـ SUM.
- ref1, [ref2], ...: نطاق (أو نطاقات) الخلايا التي سيتم حساب الإجمالي الفرعي لها. (مطلوب).
أمثلة عملية
المثال 1: جمع القيم المرئية في قائمة مفلترة
لدينا قائمة مبيعات. نريد حساب مجموع المبيعات الظاهرة فقط بعد تطبيق فلتر (تصفية) على البيانات.
| A | |
|---|---|
| 1 | المبيعات |
| 2 | 100 |
| 3 | 50 |
| 4 | (صف مخفي بالفلتر) 200 |
| 5 | 75 |
| 6 | المجموع المرئي |
| 7 | 225 |
الصيغة المستخدمة:
=SUBTOTAL(9, A2:A5)
توضيح: function_num = 9 يحدد دالة SUM. إذا تم إخفاء الخلية A4 بواسطة التصفية، فإن الدالة ستقوم بجمع القيم المرئية فقط (100 + 50 + 75 = 225).
المثال 2: حساب متوسط القيم مع تجاهل الصفوف المخفية يدوياً
لدينا قائمة درجات. نريد حساب متوسط الدرجات مع تجاهل أي صفوف قمنا بإخفائها يدوياً (وليس بالفلتر).
| A | |
|---|---|
| 1 | الدرجات |
| 2 | 80 |
| 3 | 90 |
| 4 | (صف مخفي يدوياً) 70 |
| 5 | 85 |
| 6 | المتوسط (تجاهل المخفي يدوياً) |
| 7 | 85 |
الصيغة المستخدمة:
=SUBTOTAL(101, A2:A5)
توضيح: function_num = 101 يحدد دالة AVERAGE ويتجاهل الصفوف المخفية يدوياً. إذا كان الصف 4 مخفياً يدوياً، فإن الدالة ستحسب متوسط (80, 90, 85) وهو 85.