النقاط الرئيسية
لاستخدام دالة SUBTOTAL، أنشئ صيغة باستخدام الصيغة التالية: SUBTOTAL(function_number, reference 1, reference 2,…). يمكنك أيضًا استخدام ميزة Subtotal من خلال تحديد الصفوف، ثم النقر فوق علامة التبويب “بيانات”. في القائمة المنسدلة “المخطط التفصيلي”، حدد “Subtotal”.
إن الحصول على إجماليات فرعية لمجموعات من العناصر ذات الصلة أمر سهل في Microsoft Excel. في الواقع، لديك طريقتان للقيام بذلك. يمكنك استخدام دالة SUBTOTAL أو ميزة Subtotal، أيهما يعمل بشكل أفضل. سنوضح لك كيفية القيام بذلك.
ما هو المجموع الجزئي في Excel؟
كما ذكرنا، تسمح لك SUBTOTAL في Excel بتجميع العناصر نفسها أو العناصر ذات الصلة في قائمة واستخدام دالة لحساب القيم. على سبيل المثال، يمكنك استخدامها لجمع المبيعات حسب الشهر أو متوسط الدرجات حسب الطالب. يمكنك أيضًا إضافة المخزون حسب المنتج أو حساب عدد الفواتير المستحقة في الأسبوع المقبل.
باستخدام وظيفة SUBTOTAL عن طريق إنشاء صيغة أو استخدام ميزة Subtotal، يمكنك الحصول على الحسابات التي تحتاجها في بضع خطوات فقط.
استخدم دالة المجموع الفرعي في برنامج Excel
بإمكانك إنشاء صيغة باستخدام دالة SUBTOTAL في Excel مع المرونة اللازمة لتضمين أو استبعاد الصفوف التي قمت بإخفائها.
صيغة الصيغة هي
SUBTOTAL(function_number, reference 1, reference 2,...) حيث تكون الحجتان الأوليتان مطلوبتين. يمكنك استخدام مراجع خلايا إضافية أو نطاقات مسماة للحجج المتبقية حسب الحاجة.
ال
function_number تسمح لك الوسيطة بإدراج إحدى الدوال الـ 11 باستخدام الرقم المقابل لها. تتضمن الدوال الـ 11 الأولى الصفوف المخفية، بينما تستبعد الدوال الـ 11 الثانية الصفوف المخفية. لذا، ما عليك سوى إدراج الرقم الخاص بالدالة التي تحتاجها والطريقة التي تريد بها التعامل مع الصفوف المخفية.
وظيفة | تضمين الصفوف المخفية | استبعاد الصفوف المخفية |
متوسط | 1 | 101 |
عدد | 2 | 102 |
كونتا | 3 | 103 |
الأعلى | 4 | 104 |
مين | 5 | 105 |
منتج | 6 | 106 |
التطوير القياسي | 7 | 107 |
تطوير التكنولوجيا | 8 | 108 |
مجموع | 9 | 109 |
حكم الفيديو المساعد | 10 | 110 |
فارب | 11 | 111 |
كمثال على وظيفة SUBTOTAL، سنقوم بجمع إجمالي المبيعات في نطاق الخلايا من B2 إلى B4 باستخدام هذه الصيغة:
=SUBTOTAL(9,B2:B4)
بالنسبة للمثال التالي، قمنا بإخفاء الصفين 4 و5. باستخدام هذه الصيغة الأولى، يمكننا الحصول على المجموع باستخدام الرقم 9 للوسيطة الأولى لتضمين البيانات المخفية.
=SUBTOTAL(9,B2:B6)
الآن، سوف نقوم باستبعاد تلك الصفوف المخفية من إجمالينا، باستخدام الرقم 109 لحجتنا الأولى.
=SUBTOTAL(109,B2:B6)
بمجرد حصولك على المجاميع الفرعية، يمكنك استخدام دالة SUBTOTAL مرة أخرى للحصول على المجموع الكلي في الأسفل. أو يمكنك استخدام دالة SUM لإضافة المجاميع الفرعية.
للحصول على مثال آخر، سنستخدم الدالة المتوسطة لتضمين الصفوف المخفية 3 و4 باستخدام هذه الصيغة:
=SUBTOTAL(1,C2:C6)
وبعد ذلك، سوف نستبعد الصفوف المخفية بهذه الصيغة:
=SUBTOTAL(101,C2:C6)
ملاحظات حول دالة المجموع الفرعي
فيما يلي بعض الأمور التي يجب وضعها في الاعتبار عند استخدام الدالة SUBTOTAL في Excel:
- تعمل الوظيفة للأعمدة أو النطاقات الرأسية، وليس الصفوف أو النطاقات الأفقية.
- إذا كان لديك إجماليات فرعية متداخلة في منطقة وسيطة المرجع، فسوف يتجاهلها Excel في نتيجة الإجمالي الفرعي.
- عند استخدام مرشح، يتم استبعاد البيانات التي تمت تصفيتها من نتيجة المجموع الفرعي.
استخدم ميزة المجموع الفرعي
هناك طريقة أخرى لاستخدام دالة SUBTOTAL في برنامج Excel وهي استخدام ميزة Subtotal. وبهذه الطريقة، يمكنك إضافة الحسابات وتجميع العناصر تلقائيًا في نفس الوقت. يستخدم برنامج Excel دالة SUBTOTAL لإنجاز هذه المهمة نيابةً عنك. دعنا نلقي نظرة على بعض الأمثلة.
باستخدام ميزة المجموع الفرعي لمبيعاتنا حسب الشهر، يمكننا تجميع البيانات شهريًا ثم جمع كل مجموعة بمجموع كلي في الأسفل.
حدد جميع الصفوف التي تريد تجميعها وإجماليها. انتقل إلى علامة التبويب “البيانات” واختر “إجمالي فرعي” في القائمة المنسدلة “المخطط التفصيلي”.
عند فتح مربع المجموع الفرعي، اختر كيفية إضافة المجموع الفرعي.
- عند كل تغيير في : حدد العمود الذي تريد استخدامه للتجميع. هنا، اخترنا الشهر حتى يتم إنشاء مجموعة جديدة عند تغير الشهر في الورقة.
- استخدام الوظيفة: حدد الوظيفة التي تريد استخدامها، مثل المجموع أو المتوسط أو الحد الأدنى أو الحد الأقصى أو خيار آخر. في مثالنا، حددنا المجموع.
- إضافة المجموع الفرعي إلى: حدد المربع الذي تريد حساب المجموع الفرعي فيه. في مثالنا، اخترنا المبيعات.
يمكنك تحديد المربعات الموجودة في الأسفل للعناصر الإضافية التي تريدها، ثم انقر فوق “موافق”.
ستشاهد بعد ذلك تحديث بياناتك للمجموعة و قم بجمع الصفوف وإنشاء إجمالي كلي في الأسفل. استخدم أزرار الجمع والطرح والرقم لتقليص المجموعات أو توسيعها لعرضها بشكل أسهل.
كمثال آخر، سنستخدم درجات الطلاب. سنستخدم ميزتي المجموع الفرعي والمجموعة لعرض متوسط الدرجة لكل طالب. وإليك العملية.
قم بتحديد الصفوف، وانتقل إلى “البيانات”، ثم اختر “المجموع الفرعي” في القائمة المنسدلة “المخطط التفصيلي”.
في مربع المجموع الفرعي، سنختار “طالب” في القائمة المنسدلة “تغيير” و”متوسط” في قائمة الوظائف. ثم سنحدد المربع الخاص بـ “الدرجة”. انقر فوق “موافق”.
لدينا الآن صفوف مجمعة حسب الطلاب مع متوسط الدرجات لكل طالب ومتوسط عام في الأسفل. مرة أخرى، يمكنك استخدام الأزرار الموجودة على اليسار لتقليص المجموعات وتوسيعها.
إذا قررت إلغاء تجميع الصفوف بعد استخدام ميزة المجموع الفرعي، تعود الصفوف إلى وضعها الطبيعي. ومع ذلك، تظل الصفوف التي تحتوي على دالة المجموع الفرعي التي يستخدمها Excel متاحة لك للاستمرار في استخدامها أو حذفها ببساطة إذا كنت تفضل ذلك.
قد تصبح ميزة المجموع الفرعي معقدة إذا كنت تخطط لاستخدام العديد من المجموعات. ومع ذلك، فإن هذه الأمثلة الأساسية من شأنها أن تساعدك على البدء إذا كنت مهتمًا بهذه الوظيفة.
من الأمور التي يجب ملاحظتها أنه لا يمكنك إضافة إجمالي فرعي لجدول Excel باستخدام هذه الميزة. إذا كانت بياناتك موجودة في جدول، فيمكنك إما إدراج صيغة Excel للإجمالي الفرعي كما هو موضح سابقًا أو تحويل الجدول إلى نطاق خلايا لاستخدام الميزة. إذا اخترت الخيار الأخير، فستفقد وظيفة الجدول.
الآن بعد أن تعرفت على كيفية إدراج الإجماليات الفرعية في Excel، تعرف على كيفية إزالة الصفوف المكررة.