كيف يمكنك جمع القيم في Excel دون تضمين المجاميع الفرعية أو باستثنائها؟
عند العمل مع مجموعات البيانات في Excel، من الشائع تنظيم أرقامك وتحليلها عبر حساب المجاميع الفرعية لمختلف المجموعات أو الفئات. لكن بعد إضافة هذه المجاميع الفرعية، قد تحتاج إلى حساب المجموع الكلي الذي يستثني تلك القيم لتجنب العد المزدوج في النتيجة النهائية. والمشكلة هنا أن جمع جميع الأرقام مباشرةً يشمل المجاميع الفرعية ضمن الإجمالي العام، ما يؤدي إلى نتيجة أعلى من القيمة الفعلية. ولحلّ هذا التحدي، عليك اعتماد طريقة تجمع البيانات الأصلية فقط مع استبعاد صفوف المجاميع الفرعية. في هذا المقال، نقدّم لك عدة طرق عملية وفعّالة لمعالجة هذه المشكلة، لتتمكن من الحصول على نتائج دقيقة بسلاسة في مختلف السيناريوهات.
جمع القيم دون المجاميع الفرعية باستخدام دالة SUM في 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) على نطاق محدود، ويُستخدم غالبًا كجزء من ميزة 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 لجمع الصفوف المرئية فقط (غير الفرعية)
إذا سمح هيكل مجموعة بياناتك بتصفية صفوف المجاميع الفرعية (على سبيل المثال، وجود تسمية أو كلمة مفتاحية أو نمط يُميّزها)، فيمكنك استخدام ميزة التصفية المضمنة في Excel مع دالة SUBTOTAL لجمع صفوف البيانات المرئية فقط (غير المُرشَّحة). هذه التقنية عملية في الحالات التي تحتوي فيها المجاميع الفرعية على مؤشر أو تسمية ثابتة.
1. انقر في أي مكان داخل مجموعة بياناتك، ثم انتقل إلى علامة التبويببياناتوانقرتصفيةلتمكين عوامل التصفية لأعمدتك.
2. انقر على القائمة المنسدلة في عمود المساعدة، ثم ألغِ تحديد «مجموع فرعي» لإخفاء صفوف المجاميع الفرعية وعرض بياناتك الأصلية فقط.
3. في خلية منفصلة، أدخل الصيغة التالية لجمع الصفوف المرئية (المُرشَّحة) فقط.
=SUBTOTAL(9,B2:B21)ستتجاهل دالة SUBTOTAL (برقم دالة 9 للجمع) أي صفوف مخفية، وتعيد المجموع الخاص بالصفوف المرئية حاليًّا فقط (أي صفوف البيانات غير الفرعية التي احتفظت بها بعد التصفية). هذا الحل سريع وديناميكي—فإذا أعدت تطبيق عوامل التصفية أو وسّعت مجموعة بياناتك، فستُحدّث الصيغة وفقًا لذلك.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل