من المؤكد أنك استخدمت دالة SUM آلاف المرات في جداول Excel، وهي مناسبة للمهام البسيطة والبيانات الصغيرة، لكن في بيئة العمل الواقعية، قد تكون غير دقيقة ومضللة، خاصة عند تصفية البيانات أو إخفاء بعض الصفوف. في هذا المقال، سنتعرف على دالة بديلة تُدعى SUBTOTAL، والتي تتيح حسابات مرنة وديناميكية تعكس التغييرات في البيانات بشكل صحيح.
ما المشكلة مع دالة SUM؟
دالة SUM تجمع كل القيم في نطاق محدد بغض النظر عن ما إذا كانت بعض الصفوف مخفية أو مفلترة. هذا يعني أن المجموع الناتج قد يكون أكبر من المعروض على الشاشة، مما يؤدي إلى إرباك المستخدمين ونتائج غير دقيقة في التقارير.
كيف تعمل دالة SUBTOTAL؟
دالة SUBTOTAL ذكية وتتكيف مع البيانات المعروضة فقط. عند تطبيق فلتر على الجدول، تقوم الدالة بجمع القيم المرئية فقط وتتجاهل الصفوف المخفية. كذلك، يمكنها حساب متوسط القيم، والعدد، والحد الأدنى والحد الأقصى، وغير ذلك.
صيغة الدالة هي:
-
حيث يحدد
function_numنوع العملية التي تريدها (مجموع، متوسط، تعداد، إلخ). -
عند استخدام الأرقام من 101 إلى 111، تتجاهل الدالة الصفوف المخفية.
-
عند استخدام الأرقام من 1 إلى 11، تشمل الدالة الصفوف المخفية أيضًا.
قائمة الوظائف المدعومة في SUBTOTAL:
| الرقم | الوظيفة |
|---|---|
| 101 | AVERAGE |
| 102 | COUNT |
| 103 | COUNTA |
| 104 | MAX |
| 105 | MIN |
| 106 | PRODUCT |
| 107 | STDEV |
| 108 | STDEVP |
| 109 | SUM |
| 110 | VAR |
| 111 | VARP |
مميزات إضافية لدالة SUBTOTAL
-
لا تضاعف القيم عند وجود مجموعات فرعية (subtotals) داخل الجدول، بخلاف دالة SUM التي تجمع كل المجموعات وتُضخم النتيجة.
-
يمكن استخدامها في Excel وGoogle Sheets على حد سواء.
-
تتيح تتبع دقيق وحيوي للبيانات عند تصفية الجداول أو إخفاء الصفوف.
كيفية استخدام SUBTOTAL في المثال العملي:
لحساب مجموع الخلايا من C2 إلى C15 مع تجاهل الخلايا المخفية، استخدم الصيغة:
لحساب عدد الخلايا غير الفارغة في العمود A مع تجاهل المخفية:
وبذلك تكون الحسابات ديناميكية وتتكيف تلقائيًا مع حالة التصفية، ما يحسن دقة التقارير ويسهل العمل.
الخلاصة:
باستخدام دالة SUBTOTAL بدلًا من SUM، ستتجنب الكثير من الأخطاء الناتجة عن احتساب بيانات مخفية أو مفلترة، وستجعل جداولك أكثر مرونة واحترافية. إن لم تكن تستخدم التصفية أو إخفاء الصفوف ولم تكن مهتمًا بالدقة في التحديثات، قد تظل دالة SUM مناسبة لك، لكن لمعظم المستخدمين المحترفين، لا يوجد سبب للتخلي عن SUBTOTAL.
المصدر:- MakeUseOf
