دالة الـSUBTOTAL في الإكسيل: تعريفها وكيفية استخدامها وأمثلة عملية

عند التعامل مع كميات كبيرة من البيانات في برنامج الإكسيل، تصبح الحاجة ملحة لأداة قوية تتجاوز وظائف الجمع والعد التقليدية، خاصة عند تصفية البيانات أو إخفاء بعض الصفوف. هنا يأتي دور دالة SUBTOTAL، وهي إحدى الدوال المحورية في الإكسيل التي تتيح إجراء عمليات حسابية متنوعة على نطاق من البيانات، مع تجاهل الصفوف التي تم إخفاؤها أو تصفيتها بذكاء.

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

تتميز دالة SUBTOTAL بمرونتها، حيث يمكنها أداء 11 عملية حسابية مختلفة (مثل الجمع، حساب المتوسط، العد، وغيرها) بناءً على رقم مرجعي يتم تحديده.

الصيغة العامة للدالة هي:

=SUBTOTAL(function_num, ref1, [ref2], ...)

حيث تمثل كل وسيطة ما يلي:

• function_num (رقم الدالة): هو رقم مرجعي (من 1 إلى 11 أو من 101 إلى 111) يحدد العملية الحسابية التي سيتم إجراؤها (إلزامي).

• ref1, [ref2] (النطاق): هو نطاق الخلايا الذي تريد تطبيق العملية الحسابية عليه (مطلوب نطاق واحد على الأقل).

يكمن سر قوة دالة SUBTOTAL في الوسيط function_num. هناك مجموعتان من الأرقام، ولكل منها سلوك مختلف:

• الأرقام من 1 إلى 11: تقوم هذه المجموعة بإجراء الحسابات على جميع الخلايا في النطاق، بما في ذلك الصفوف المخفية يدويًا، ولكنها تتجاهل الصفوف التي تم إخفاؤها عبر التصفية (Filter).

• الأرقام من 101 إلى 111: تقوم هذه المجموعة بإجراء الحسابات على الخلايا الظاهرة فقط، حيث تتجاهل الصفوف المخفية يدويًا والصفوف التي تم إخفاؤها عبر التصفية معًا.

مثال 1: حساب عدد الخلايا
لنفترض أن لديك الأرقام 7, 25, 21.69, 35.88 في النطاق D2:D5. لحساب عدد الخلايا التي تحتوي على أرقام في هذا النطاق، نستخدم الصيغة:

=SUBTOTAL(2, D2:D5)

بما أن الرقم المرجعي 2 يمثل دالة COUNT، فإن الناتج سيكون 4.

مثال 2: حساب المتوسط الحسابي
بنفس البيانات السابقة، لحساب المتوسط الحسابي، نستخدم الصيغة:

=SUBTOTAL(1, D2:D5)

بما أن الرقم المرجعي 1 يمثل دالة AVERAGE، فإن الناتج سيكون 22.3925.

مثال 3: حساب المجموع
لنفترض أن لديك الأرقام 120, 10, 150, 23 في النطاق A2:A5. لحساب مجموع هذه الأرقام، نستخدم الصيغة:

=SUBTOTAL(9, A2:A5)

بما أن الرقم المرجعي 9 يمثل دالة SUM، فإن الناتج سيكون 303.

مثال 4: قوة الدالة مع التصفية
تخيل أن لديك جدول مبيعات، وقمت بتصفيته لعرض مبيعات مدينة "الرياض" فقط.

• إذا استخدمت =SUM(C2:C100)، ستحصل على مجموع مبيعات كل المدن (بما في ذلك المخفية).

• إذا استخدمت =SUBTOTAL(9, C2:C100)، ستحصل على مجموع مبيعات مدينة "الرياض" فقط، لأن الدالة تتجاهل تلقائيًا الصفوف التي أخفتها التصفية.

ميزة إضافية: تتجاهل دالة SUBTOTAL أي دوال SUBTOTAL أخرى موجودة داخل نطاقها، مما يمنع الحساب المزدوج عند عمل إجماليات فرعية وإجماليات كلية.