عندما تحتوي ورقة عمل Excel على صفوف مخفية أو بيانات مصفاة أو بيانات مجمعة ، استخدم وظيفة Excel الفرعية. يمكن أن تتضمن الدالة SUBTOTAL القيم المخفية في العمليات الحسابية أو تستبعدها. بالإضافة إلى العثور على إجمالي مجموعة البيانات ، يمكن لبرنامج Excel حساب المتوسط والحد الأقصى والحد الأدنى والانحراف المعياري والتباين لبياناتك. إليك كيفية إدراج المجاميع الفرعية في Excel.
تنطبق الإرشادات الواردة في هذه المقالة على Excel لـ Microsoft 365 و Excel 2019 و Excel 2016.
تركيب الدالة الفرعية
استخدم الدالة SUBTOTAL في Excel لتلخيص القيم في ورقة العمل بطرق مختلفة. يكون مفيدًا بشكل خاص عندما تحتوي ورقة العمل الخاصة بك على صفوف مخفية تريد تضمينها في الحساب.
صيغة الدالة SUBTOTAL هي: SUBTOTAL (function_num، ref1، ref2،…)
الوسيطة function_numمطلوبة وتحدد نوع العملية الحسابية لاستخدامها في المجموع الفرعي. يمكن للدالة SUBTOTAL إضافة أرقام وحساب متوسط قيمة الأرقام المحددة والعثور على الحد الأقصى والحد الأدنى للقيم في النطاق وحساب عدد القيم في النطاق المحدد والمزيد.
تتجاهل الدالة SUBTOTAL الخلايا التي لا تحتوي على بيانات وخلايا ذات قيم غير رقمية.
هذه الوسيطة عبارة عن رقم وتعتمد على ما إذا كنت تريد تضمين صفوف مخفية في النتيجة أو استبعاد الصفوف المخفية من النتيجة. قد يتم إخفاء أو إخفاء هذه الصفوف يدويًا بواسطة مرشح.
تتضمن وسيطات function_num:
| مهمة الوظيفة | function_num | function_num |
|---|---|---|
| (يتضمن القيم المخفية) | (باستثناء القيم المخفية) | |
| متوسط | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| ماكس | 4 | 104 |
| MIN | 5 | 105 |
| المنتج | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| مجموع | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
تتضمن وسيطات المرجع function_num من 1 إلى 11 القيم الموجودة في الصفوف المخفية فقط عند استخدام الأمر Hide لإخفاء الصفوف. عند استخدام الأمر Filter ، لا تتضمن العمليات الحسابية الفرعية نتائج التصفية المخفية.
الوسيطة ref1مطلوبة. هذه هي الخلايا المستخدمة لحساب نتائج الوسيطة function_num المحددة. يمكن أن تكون هذه الوسيطة قيمة أو خلية مفردة أو نطاق من الخلايا.
الوسيطات ref2 ،…الوسائط اختيارية. هذه خلايا إضافية تم تضمينها في الحساب.
استخدم الدالة SUBTOTAL مع الصفوف المخفية
يمكن إدخال وظائف Excel يدويًا أو بمساعدة مربع حوار وسيطات الوظيفة. لتوضيح كيفية إدخال الوظيفة يدويًا باستخدام شريط الصيغة ، يستخدم المثال التالي الوسيطة COUNT function_num لحساب عدد القيم في الصفوف المرئية وفي كل من الصفوف المرئية والمخفية.
لاستخدام الدالة SUBTOTAL لحساب عدد الصفوف في ورقة العمل:
- ابدأ بورقة عمل تحتوي على صفوف متعددة من البيانات.
- حدد الخلية التي ستحتوي على عدد الصفوف المرئية.
-
في شريط الوظائف ، أدخل =المجموع الفرعي. أثناء الكتابة ، يقترح Excel وظيفة. انقر نقرًا مزدوجًا فوق دالة SUBTOTAL.
لاستخدام مربع حوار وسيطات الدالة لإدخال الدالة SUBTOTAL ، انتقل إلى Formulas وحدد Math &Trig> المجموع الفرعي.
Image -
في القائمة المنسدلة التي تظهر ، انقر نقرًا مزدوجًا فوق الوسيطة 102 - COUNTfunction_num.
Image -
اكتب فاصلة (،).
Image -
في ورقة العمل ، حدد الخلايا المراد تضمينها في الصيغة.
Image -
اضغط على أدخللرؤية النتيجة في الخلية التي حددتها في الخطوة 2.
Image - حدد الخلية التي ستحتوي على عدد الصفوف المرئية والمخفية.
- في شريط الوظائف ، أدخل =المجموع الفرعي. أثناء الكتابة ، يقترح Excel وظيفة. انقر نقرًا مزدوجًا فوق دالة SUBTOTAL.
- في القائمة المنسدلة التي تظهر ، انقر نقرًا مزدوجًا فوق الوسيطة 2 - COUNTfunction_num ، ثم اكتب فاصلة (،).
-
في ورقة العمل ، حدد الخلايا المراد تضمينها في الصيغة ، ثم اضغط أدخل.
Image -
إخفاء عدة صفوف من البيانات. في هذا المثال ، تم إخفاء الصفوف التي تقل مبيعاتها عن 100000 دولار فقط.
Image
استخدم الدالة الفرعية مع البيانات المصفاة
يتجاهل استخدام الدالة SUBTOTAL في البيانات التي تمت تصفيتها البيانات الموجودة في الصفوف التي تمت إزالتها بواسطة الفلتر. في كل مرة تتغير معايير التصفية ، تعيد الوظيفة الحساب لإظهار الإجمالي الفرعي للصفوف المرئية.
لاستخدام الدالة SUBTOTAL لمعرفة الاختلافات في نتائج الحساب أثناء تصفية البيانات:
-
إنشاء صيغ فرعية. على سبيل المثال ، قم بإنشاء الصيغ لتحديد الإجمالي الفرعي والقيم المتوسطة للبيانات التي تمت تصفيتها.
لا يهم إذا كنت تستخدم الوسيطة function_num للصفوف المرئية أو المخفية. توفر كلتا الوسيطتين نفس النتيجة في البيانات المصفاة.
Image - حدد أي خلية في مجموعة البيانات.
-
انتقل إلى Home ، ثم حدد Sort &Filter> Filter.
Image -
استخدم الأسهم المنسدلة لتصفية بيانات ورقة العمل.
Image -
لاحظ كيف تتغير القيم في كل مرة تحدد فيها معايير مرشح مختلفة.
Image
استخدم الدالة SUBTOTAL مع البيانات المجمعة
عندما يتم تجميع البيانات ، هناك طريقة لتطبيق دالة SUBTOTAL على كل مجموعة فردية ثم حساب الإجمالي الكلي لمجموعة البيانات بأكملها.
- حدد أي خلية في مجموعة البيانات.
-
حدد البيانات> المجموع الفرعي لفتح مربع الحوار المجموع الفرعي.
Image - حدد عند كل تغيير فيسهم القائمة المنسدلة واختر التجميع الذي سيتم حساب كل إجمالي فرعي له.
- حدد استخدم الوظيفةسهم القائمة المنسدلة واختر function_num.
- في أضف المجموع الفرعي لقائمة، حدد العمود الذي سيتم تطبيق الصيغة.
-
حدد موافق.
Image -
يتم إدراج المجاميع الفرعية لكل مجموعة بيانات ، ويتم إدراج الإجمالي الكلي في الجزء السفلي من مجموعة البيانات.
Image - لتغيير function_num ، حدد أي خلية في مجموعة البيانات وحدد Data> Subtotal. ثم حدد اختياراتك في مربع الحوار Subtotal.
