كيفية جمع القيم دون إدراج المجاميع الفرعية في Excel؟
عند العمل مع مجموعات البيانات في Excel، من الشائع تنظيم وتحليل الأرقام عن طريق حساب المجاميع الفرعية لمجموعات أو فئات مختلفة. ومع ذلك، بعد إضافة هذه المجاميع الفرعية، قد ترغب في حساب المجموع الكلي الذي يستثني قيم المجاميع الفرعية لمنع أي حساب مزدوج في المجموع النهائي. التحدي هنا هو أنه عند جمع كل الأرقام مباشرةً، يتم تضمين المجاميع الفرعية في المجموع الإجمالي، مما يؤدي إلى نتيجة أكبر من المطلوب. لتجنب ذلك، تحتاج إلى طريقة لجمع البيانات الأصلية مع استبعاد صفوف المجاميع الفرعية. في هذه المقالة، نقدم عدة طرق عملية لحل هذه المشكلة حتى تتمكن من حساب النتائج بدقة في سيناريوهات مختلفة.
جمع القيم بدون المجاميع الفرعية باستخدام دالة الجمع في Excel
إحدى الطرق المستخدمة على نطاق واسع لحساب المجاميع الفرعية للمجموعات هي إدراج دالة SUM داخل أو أسفل كل مجموعة ذات صلة. ومع ذلك، عندما تريد حساب المجموع الكلي للقائمة بأكملها مع تجاهل صفوف المجاميع الفرعية لهذه المجموعات، غالبًا ما يتطلب الأمر خطوة إضافية. إليك الطريقة التي يمكنك استخدامها لتحقيق ذلك:
1. استخدم دالة SUM داخل كل مجموعة لتوليد المجاميع الفرعية الخاصة بها، كما هو موضح في هذا اللقطة الشاشة:
2. بعد ذلك، لإيجاد المجموع الكلي بدون تضمين تلك الصفوف الفرعية، أدخل الصيغة التالية في خلية فارغة حيث تريد أن تظهر النتيجة:
=SUM(B2:B21)/2
اضغط Enter للحصول على المجموع الصحيح. تعمل هذه الصيغة لأنها تفترض أنك أضفت المجاميع الفرعية باستخدام دالة SUM فور انتهاء كل مجموعة ذات صلة، مما يضاعف مجموع البيانات الخام. القسمة على 2 تزيل المجموع المكرر. يرجى ملاحظة أن هذه الطريقة مناسبة بشكل أفضل عندما تكون مجموعة البيانات منظمة بحيث يكون مجموع القيم الأصلية والمجاميع الفرعية معًا ضعف مجموع القيم الأصلية بالضبط.
إذا كانت بنية بياناتك أكثر تعقيدًا، أو كان عدد صفوف المجاميع الفرعية يختلف، فقد تقدم الأساليب البديلة الموضحة أدناه حلولاً أكثر دقة ومرونة.
جمع القيم بدون المجاميع الفرعية باستخدام دالة SUBTOTAL في Excel
توفر دالة SUBTOTAL في Excel طريقة مضمنة لتنفيذ العمليات الحسابية على الصفوف المرشحة أو المرئية فقط. وهذا يجعلها مفيدة بشكل خاص عند العمل مع القوائم التي تحتوي على المجاميع الفرعية، حيث يمكن تكوين صيغ SUBTOTAL لتجاهل نتائج SUBTOTAL الأخرى داخل النطاق، وبالتالي منع أي حساب مزدوج للقيم.
لاستخدام دالة SUBTOTAL في هذا السياق، اتبع الخطوات التالية:
1. أدخل دالة SUBTOTAL لكل مجموعة كما يلي:
=SUBTOTAL(9,B2:B10)
يطبق هذا عملية الجمع (رقم الدالة 9) على النطاق المحدد ويتم استخدامه غالبًا كجزء من ميزة Data > Subtotal المدمجة في Excel.
2. بالنسبة للمجموع الكلي الذي يستثني المجاميع الفرعية الداخلية، أدخل الصيغة التالية في الخلية المختارة:
=SUBTOTAL(9,B2:B21)
اضغط Enter لجمع قيم البيانات تلقائيًا، مع تجاهل أي دوال SUBTOTAL متداخلة ضمن النطاق. هذا النهج مناسب للقوائم التي تستخدم دالة SUBTOTAL لملخصات المجموعات، ويقضي على خطر جمع المجاميع الفرعية عدة مرات.
كن على دراية بأن دالة SUBTOTAL تعمل بشكل أفضل عندما يتم توليد الخلايا الفرعية باستخدام SUBTOTAL بدلاً من SUM. أيضًا، إذا قمت بتصفية أو إخفاء الصفوف، يمكن تعيين SUBTOTAL لجمع البيانات المرئية فقط (غير المخفية)، وهو مفيد في حالات التقارير الديناميكية.
كود VBA - جمع القيم باستثناء صفوف المجاميع الفرعية
إذا كانت بنية بياناتك معقدة أو تريد طريقة آلية لجمع صفوف البيانات الخام فقط (استثناء المجاميع الفرعية)، يمكنك استخدام ماكرو VBA بسيط. يتيح هذا النهج تحديد الصفوف التي تتطابق مع معاييرك برمجيًا وجمعها فقط، مثل الاستناد إلى تنسيق معين، وجود صيغ، أو خصائص تمييزية أخرى.
على سبيل المثال، إذا كانت صفوف المجاميع الفرعية يتم حسابها باستخدام صيغ (مثل SUM أو SUBTOTAL)، وتحتوي صفوف البيانات الخام فقط على قيم ثابتة، يمكنك تعيين كود VBA لجمع الخلايا فقط في عمود معين والتي لا تحتوي على صيغ. إليك الطريقة:
1. انقر فوق أدوات المطور > Visual Basic لفتح نافذة Microsoft Visual Basic for Applications.
2. في النافذة الجديدة، انقر فوق إدراج > وحدة والصق الكود التالي في الوحدة:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. انقر فوق زر 
تشغيل لتنفيذ الكود. ستظهر رسالة تطلب منك تحديد النطاق الذي يحتوي على بياناتك. سيقوم الماكرو بعد ذلك بجمع الخلايا فقط في تحديدك التي لا تحتوي على صيغ، مما يتجاوز بشكل فعال صفوف المجاميع الفرعية النموذجية.
التصفية - استخدام الفلتر ودالة SUBTOTAL لجمع الصفوف المرئية فقط (غير المجاميع الفرعية)
إذا كانت مجموعة البيانات الخاصة بك تسمح لك بتصفية صفوف المجاميع الفرعية (على سبيل المثال، هناك علامة أو كلمة رئيسية أو نمط لتحديدها)، يمكنك استخدام ميزة Filter المدمجة في Excel مع دالة SUBTOTAL لجمع صفوف البيانات المرئية فقط (غير المصفاة). هذه التقنية عملية في الحالات التي تكون فيها المجاميع الفرعية لها مؤشر أو علامة متسقة.
1. انقر في أي مكان في مجموعة البيانات الخاصة بك، ثم انتقل إلى بيانات التبويب وانقر على تصفية لتمكين مرشحات الأعمدة.
2. انقر على القائمة المنسدلة في عمود المساعد الخاص بك، وألغِ تحديد "Subtotal" لإخفاء صفوف المجاميع الفرعية وعرض البيانات الأصلية فقط.
3. في خلية منفصلة، أدخل الصيغة التالية لجمع الصفوف المرئية فقط (المصفاة).
=SUBTOTAL(9,B2:B21)ستتجاهل دالة SUBTOTAL (برقم الدالة 9 لـ SUM) أي صفوف مخفية، وستعيد المجموع الكلي للصفوف المرئية فقط (أي الصفوف غير المجاميع الفرعية التي احتفظت بها بعد التصفية). هذا الحل سريع وديناميكي - إذا قمت بإعادة تطبيق المرشحات أو توسيع مجموعة البيانات، ستتم تحديث الصيغة وفقًا لذلك.
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...
Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير
- تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
- افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!
جميع إضافات Kutools. مثبت واحد
حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.
- حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
- مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
- الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
- تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد