الدالة SUBTOTAL: الصيغة وكيفية استخدام الدالة SUBTOTAL في Excel

ما هي الدالة SUBTOTAL في Excel ؟ ما هي الصيغة SUBTOTAL في Excel ؟ دعونا نكتشف ذلك مع LuckyTemplates.com!

يحتوي Microsoft Excel على العديد من الوظائف المستخدمة لحساب الإجماليات، وSUBTOTAL هي إحداها. ومع ذلك، يختلف الإجمالي الفرعي عن الدالة SUM. إنه ليس مبلغًا عاديًا. بدلاً من ذلك، يتم استخدامه لحساب الإجماليات الفرعية في القوائم أو قواعد البيانات، وحساب المتوسطات، والمزيد.

المحتوى الرئيسي حول الدالة SUBTOTAL

• ما هي الدالة SUBTOTAL وفيما يتم استخدامها؟
• صيغة SUBTOTAL في Excel
• كيفية استخدام الدالة SUBTOTAL في Excel
  • 1. احسب إجمالي الصفوف التي تمت تصفيتها 
  • 2. عد الخلايا التي تمت تصفيتها والتي ليست فارغة
  • 3. تجاهل القيم في صيغ الإجمالي الفرعي المتداخلة
• الأشياء التي يجب مراعاتها قبل استخدام الدالة SUBTOTAL في Excel
• كيفية الخطأ عند تنفيذ وظيفة SUBTOTAL في Excel

ما هي الدالة SUBTOTAL وفيما يتم استخدامها؟

للوهلة الأولى، قد تتساءل: ما هو الغرض من الدالة SUBTOTAL؟ بعد كل شيء، من السهل العثور على إجمالي فرعي من نطاق من الخلايا باستخدام الوظائف الموجودة مثل SUMIF أو AVERAGE .

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

يعمل SUBTOTAL مع ميزات Excel الأخرى، مثل تصفية الخلايا. إذا قمت بتصفية جدول حسب قيمة، فسيتم تحديث الصيغة SUBTOTAL والتعرف على عامل التصفية واستبعاد الخلايا المقابلة. يعد SUBTOTAL مفيدًا أيضًا لتجاهل القيم المخفية، والتي لا تستطيع الوظائف الأخرى (مثل SUM) القيام بها.

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

صيغة SUBTOTAL في Excel

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

تحتوي الدالة SUBTOTAL على بناء الجملة =SUBTOTAL(function_num, ref1, [ref2],…) . هناك:

• Function_num: تحدد الأرقام من 1 إلى 11 ومن 101 إلى 111 الدالة التي سيتم استخدامها لحساب المجموع الفرعي.
• Ref1، Ref2، ...: خلية واحدة أو أكثر، أو نطاق من الخلايا لحساب الإجماليات الفرعية، بحد أقصى 254.

ملحوظة:

• تم تصميم الدالة SUBTOTAL لحساب الأعمدة الرأسية للبيانات.
• إذا كانت الوسيطات ref1، ref2،... تحتوي على الدالة SUBTOTAL، فسيتم تجاهلها لتجنب العد المزدوج.
• إذا كانت function_num من 1 إلى 11، فإن الدالة SUBTOTAL تحسب تضمين القيم المخفية في مجموعة البيانات لأن الصف الذي يحتوي على هذا النطاق مخفي. إذا كانت function_num من 101 إلى 111، فإن الدالة SUBTOTAL تحسب فقط القيم غير المخفية في مجموعة البيانات (لا تحسب القيم في الصفوف المخفية).
• بالنسبة لمناطق البيانات المخفية بواسطة عامل التصفية، سيتم تجاهل SUBTOTAL.

كيفية استخدام الدالة SUBTOTAL في Excel

1. احسب إجمالي الصفوف التي تمت تصفيتها 

لفهم هذا النوع من المجموع بشكل أفضل، يرجى الرجوع إلى المقالة حساب مجموع قيم القائمة المصفاة في Excel . في الأساس، ستكون الدالة SUBTOTAL في هذه الحالة:

=SUBTOTAL(9,_range)

pham_range هنا هي المنطقة التي تريد جمعها بعد تصفية البيانات.

2. عد الخلايا التي تمت تصفيتها والتي ليست فارغة

سوف نستخدم المجموع الفرعي 3 أو المجموع الفرعي 103. ومع ذلك، في حالة وجود صفوف مخفية، يجب عليك استخدام المجموع الفرعي 103 لحساب الخلايا المرئية غير الفارغة بدقة.

على سبيل المثال، سيخفي جدول البيانات أعلاه صفين 4 و5. عند استخدام SUBTOTAL 3 أو SUBTOTAL 103، فإنه سينتج نتيجتين مختلفتين.

نقوم بإدخال الصيغة ومن ثم سيعرض Excel تلقائيًا مجموعة الوظائف التي يمكنك الاختيار من بينها دون الحاجة إلى التذكر.

ستكون النتيجة عند استخدام SUBTOTAL 3 هي 3، بما في ذلك الخلايا المخفية في الصف.

أما SUBTOTAL 103، فسيعرض فقط الخلايا غير الفارغة التي نراها، متجاهلاً الصفوف المخفية.

3. تجاهل القيم في صيغ الإجمالي الفرعي المتداخلة

على سبيل المثال، سوف نقوم بحساب متوسط ​​إجمالي كيلوغرامات القماش في المستودعات A1 ​​وA2.

الصيغة المتوسطة للمستودع A2= SUBTOTAL(1,C2:C4) وتعطي النتيجة 19.

الصيغة المتوسطة للمستودع A1 = SUBTOTAL(1,C5:C7) لها النتيجة 38.

ومع ذلك، عند حساب متوسط ​​إجمالي عدد الأقمشة في مستودعين، سيتم تجاهل متوسط ​​النتائج في المستودعين. لدينا الصيغة =SUBTOTAL(1,C2:C9) وستقوم النتيجة تلقائيًا بإزالة النتيجة المتوسطة المحسوبة مسبقًا.

الأشياء التي يجب مراعاتها قبل استخدام الدالة SUBTOTAL في Excel

على الرغم من أن SUBTOTAL له مميزاته، إلا أن هناك بعض الأشياء التي يجب عليك مراعاتها قبل البدء في استخدام هذه الوظيفة، بما في ذلك ما يلي:

• كما ذكرنا سابقًا، فإن استخدام 1-11 للوسيطة function_num سيضمن أن SUBTOTAL يتضمن القيم المخفية، بينما يتجاهلها 101-111.
• سيؤدي استخدام قيمة غير 1-11 أو 101-111 إلى قيام Excel بإرجاع الخطأ #VALUE!. سيحدث هذا أيضًا لمراجع الخلايا ثلاثية الأبعاد (حيث تتم الإشارة إلى نفس الخلية، عبر أوراق متعددة، في نطاق).
• عند استخدام نطاقات الخلايا الأفقية مثل A1:D1، يتم تضمين القيم المخفية تلقائيًا (بغض النظر عن قيمة الوسيطة function_num). يعد هذا قيدًا على الدالة SUBTOTAL ولا يمكن التغلب عليه.
• سيؤدي استخدام SUBTOTAL على البيانات التي تمت تصفيتها إلى ضمان تجاهل القيم المخفية دائمًا، بغض النظر عن الوسيطة function_num المستخدمة.
• يمكن استخدام SUBTOTAL كدالة متداخلة كجزء من دالات أخرى، بما في ذلك الصيغ التي تحتوي على دالة IF.
• إذا كانت صيغة SUBTOTAL أخرى موجودة ضمن نطاق البيانات ref1، فإن SUBTOTAL يتجاهلها ويستبعدها من الحساب الإجمالي.
• يسمح برنامج Excel باستخدام ما يصل إلى 254 نطاقًا من الخلايا في صيغة SUBTOTAL، على الرغم من أن هناك حاجة إلى نطاق خلية واحد فقط حتى تعمل الصيغة.

كيفية الخطأ عند تنفيذ وظيفة SUBTOTAL في Excel

عند حساب الدالة SUBTOTAL في Excel، ستواجه غالبًا بعض الأخطاء الأساسية:

• #VALUE!: معرف الوظيفة ليس ضمن النطاق 1-11 أو 101-111 أو يحتوي على مرجع ثلاثي الأبعاد.
• #DIV/0!: يحدث عندما يجب قسمة مجموع معين على 0 (على سبيل المثال، حساب المتوسط ​​أو الانحراف المعياري لنطاق من الخلايا لا يحتوي على قيم رقمية).
• #اسم؟ اسم الدالة SUBTOTAL به خطأ إملائي.

بعض النصائح حول كيفية استخدام الدالة SUBTOTAL في Microsoft Excel والتي قد لا تعرفها

نصيحة 1: لنفترض أنك تريد التأكد من توفر جميع ألوان القمصان بجميع الأحجام في أي من المستودعات. يمكنك اتباع الخطوات التالية:

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

الخطوة 2: حدد COUNT من القائمة المنسدلة والحجم من حقل " إضافة حقل إجمالي فرعي إلى ". ثم قم بإلغاء تحديد استبدال الإجماليات الفرعية الحالية . عند النقر فوق "موافق" ، سوف تتلقى جدول البيانات التالي:

ويساعد ذلك على التأكد من أن لديك عددًا كافيًا من الأبعاد المختلفة ويمكنك تصنيف البيانات بطريقة لا تتطلب عمليات متكررة.

نصيحة 2 : من المفيد دائمًا فرز البيانات حسب العمود.

نصيحة 3: تساعدك على تضمين تسمية في الصف الأول.

نصيحة 4: إذا كنت تريد تلخيص البيانات، قم بإلغاء تحديد المربع " ملخص أسفل البيانات عند إدراج الإجمالي الفرعي ". - ملخص البيانات أدناه عند إدخال الدالة SUBTOTAL.

باختصار، ما هي وظيفة Subtotal في Excel؟

كما يوحي الاسم، يتم استخدام الدالة SUBTOTAL في Excel لحساب الإجماليات الفرعية. تقوم الدالة Excel SUBTOTAL بإرجاع النتائج المجمعة للقيم المقدمة. يمكن لـ SUBTOTAL حساب المجموع والمتوسط ​​والعدد والحد الأقصى... يمكن لبرنامج Excel هذا أيضًا تضمين أو استبعاد القيم في الصفوف المخفية.

• الغرض: حساب الإجمالي الفرعي في قائمة أو قاعدة بيانات.
• قيمة الإرجاع: رقم يمثل نوعًا من الإجمالي الفرعي.
• دعوى:
  • function_num- رقم يحدد الوظيفة التي سيتم استخدامها لحساب الإجماليات الفرعية في القائمة.
  • ref1- نطاق مسمى أو مرجع إلى إجمالي فرعي.
  • ref2- [اختياري] نطاق مسمى أو مرجع إلى إجمالي فرعي.

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

شاهد المزيد:

• دالة SUMPRODUCT في Excel: حساب المنتج الإجمالي للقيم المقابلة
• كيفية الجمع بين عمودين الاسم الكامل والاسم الأول في إكسيل دون فقدان المحتوى