المجموع الفرعي مقابل الإجمالي في Microsoft Excel

لسنوات عديدة ، استخدمت الوظيفة الفرعية في Microsoft Excel لإنشاء مجموعات فرعية مرئية بسهولة في الجزء العلوي من أوراق العمل الخاصة بي. ومع ذلك ، عندما واجهت الركام ، أصبح هذا وظيفتي لتحقيق نفس النتيجة ولكن بمزيد من المرونة.

قبل أن أشرح كذلك ، دعونا نذكر أنفسنا كيف تعمل الوظيفة الفرعية.

الوظيفة الفرعية

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

متعلق ب

كيفية استخدام الوظيفة الفرعية في Microsoft Excel

راجع المجموع الفرعي للقيم مع وظيفة Excel البسيطة.

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

هذا هو بناء الجملة:

=SUBTOTAL(a,b,c)

أين

• أ (مطلوب) هو رقم يمثل الوظيفة التي تريد استخدامها في الحساب ،
• ب (مطلوب) هو المدى الأول من الخلايا إلى المجموع الفرعي ، و
• ج (اختياري) يمثل أول ما يصل إلى 252 نطاقات أخرى إلى نطاق فرعي ، مفصولة بفاصلة.

للحجة أ، 1 أو 101 = المتوسط ​​، 2 أو 102 = العد ، 3 أو 103 = conta ، 4 أو 104 = أقصى ، 5 أو 105 = دقيقة ، 6 أو 106 = المنتج ، 7 أو 107 = stdev ، 8 أو 108 = stdev.p ، 9 أو 109 = sum ، 10 أو 110 = var ، و 11 أو 111 = varp.

في هذا المثال ، تم استخدام الوظيفة الفرعية لحساب متوسط ​​القيم في العمود B ، مع وجود الصف 6 (Team E) مخفي. يتضمن المجموع الفرعي في الخلية E1 الصف المخفي (الحجة أ = 1) ، في حين أن المجموع الفرعي في الخلية E2 لا (الوسيطة أ = 101).

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

متعلق ب

كيفية فرز وتصفية البيانات في Excel

لدى Microsoft Excel خيارات فرز الطاقة والتصفية. إليك كيفية استخدامها في جدول البيانات الخاص بك.

في هذا المثال الأخير ، يمكنك أن ترى أن الدالة الفرعية تُرجع خطأ إذا كان هناك خطأ في النطاق.

لتجنب هذه المشكلات ، يمكنك استخدام الوظيفة الإجمالية بدلاً من ذلك.

الوظيفة الإجمالية

تشبه الوظيفة الإجمالية الوظيفة الفرعية ، ولكن هناك المزيد من الخيارات لنوع التجميع الذي تريد إنشاؤه. أيضًا ، لديك خيار أوسع للأشياء التي يمكنك استبعادها من النتيجة ، ويمكنك جعل Excel تجاهل الأخطاء في بياناتك.

بمعنى آخر ، بالمقارنة مع الوظيفة الفرعية ، يكون الركام أكثر قوة ومرونة.

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

متعلق ب

كيفية استخدام وظيفة Excel الإجمالية لتحسين الحسابات

تفوت الصفوف المخفية أو الأخطاء أو الوظائف المتداخلة في صيغك.

بناء الجملة للشكل المرجعي للوظيفة الإجمالية هو:

=AGGREGATE(a,b,c,d)

أين

• أ (مطلوب) هو رقم يمثل الوظيفة التي تريد استخدامها في الحساب ،
• ب (مطلوب) هو رقم يحدد ما تريد أن يتجاهله الحساب ،
• ج (مطلوب) هو نطاق الخلايا التي سيتم تطبيق الوظيفة عليها ، و
• د (اختياري) هو الأول من 252 وسيطات إضافية تحدد نطاقات أخرى.

إذا كنت تعمل مع المصفوفات ، فإن الدالة الكلية تتبع بناء جملة مختلف قليلاً:

=AGGREGATE(a,b,c,d)

أين

• أ (مطلوب) هو رقم يمثل الوظيفة التي تريد استخدامها في الحساب ،
• ب (مطلوب) هو رقم يحدد ما تريد أن يتجاهله الحساب ،
• ج (مطلوب) هو مجموعة القيم التي سيتم تطبيق الوظيفة عليها ، و
• د هي الحجة الثانية المطلوبة من قبل وظائف المصفوفة مثل كبيرة ، صغيرة ، مئوية ، وغيرها.

الفرق الأول للملاحظة بين المجموعتين الفرعيين والجريين هو أن هذا الأخير لديه المزيد من الخيارات للبسيطة أ: 1 = المتوسط ​​، 2 = العد ، 3 = counta ، 4 = max ، 5 = min ، 6 = product ، 7 = stdev.s ، 8 = stdev.p ، 9 = sum ، 10 = var.s ، 11 = var.p ، 12 = median ، 13 = mode.sngl ، 14 = Quark ، 15 = small ، 16 = compal.inc ، 17 = Quarile.Inc ، 18 = Quartile.

ومع ذلك ، حيث يتفوق المجموع الفرعي حقًا في قدرته على استبعاد قيم معينة من النتيجة (الوسيطة ب):

لذلك ، دعنا نراها في العمل.

في هذا المثال ، الكتابة:

=AGGREGATE(1,7,Team_Scores(Score))

في الخلية E2 إرجاع المتوسط ​​(الحجة أ = 1) من القيم المرئية في العمود ب ، متجاهلة القيمة المخفية في الصف 8 والخطأ في الصف 7 (الوسيطة ب = 7).

متعلق ب

كيفية إخفاء الخلايا والصفوف والأعمدة في Excel

قد تكون هناك أوقات تريد فيها إخفاء المعلومات في خلايا معينة أو إخفاء صفوف أو أعمدة كاملة في ورقة عمل Excel.

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

من ناحية أخرى ، الكتابة:

=AGGREGATE(1,6,Team_Scores(Score))

في الخلية E3 إرجاع المتوسط ​​(الحجة أ = 1) من بين جميع القيم في العمود ب ، بما في ذلك القيمة على الصف 8 الذي يتم ترشيحه واستبعاد الخطأ (الوسيطة ب = 6).

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

هذه المرة ، في الخلية E3 ، يتم استخدام الوظيفة الإجمالية لتوليد الوسيط (الوسيطة أ = 12) ، واحدة من العمليات الحسابية العديدة لا تسمح الوظيفة الفرعية:

=AGGREGATE(12,6,Team_Scores(Score))

في المثال الأخير ، الحجة د تم استخدامه لتوليد ثاني أكبر قيمة:

=AGGREGATE(14,6,Team_Scores(Score),2)

ملخص: المجموع الفرعي مقابل الإجمالي في Excel

فيما يلي ملخص لوظائف Microsoft Excel الفرعية والمجموعة الإجمالية:

حتى لو كنت تعتقد أنك استقرت على وظيفة تناسبك ، تحقق دائمًا من أنه لا توجد وظيفة أخرى تقوم بالمهمة بشكل أفضل! تقدم Microsoft في كثير من الأحيان وظائف جديدة يتم ترقياتها من أدوات كبار السن-على سبيل المثال ، أستخدم الآن Xlookup بدلاً من Vlookup ، و Xmatch سهلة الاستخدام أكثر بكثير من المطابقة.