كيفية جمع الخلايا المرئية فقط بناءً على معايير في Excel؟
في Excel، يمكن للمستخدمين عادةً جمع الخلايا بناءً على معايير محددة باستخدام دالة SUMIFS. ومع ذلك، عند التعامل مع البيانات المُرشحة، فإن تطبيق SUMIFS ببساطة سيشمل كلًا من الخلايا المرئية والمخفية في الحساب. غالبًا ما يؤدي هذا إلى نتائج غير صحيحة إذا كنت بحاجة إلى جمع الخلايا المرئية فقط (أي غير المُرشحة) التي تتطابق مع معايير معينة، كما هو موضح في لقطة الشاشة أدناه.
جمع الخلايا المرئية فقط بناءً على معيار واحد أو أكثر باستخدام عمود مساعد
جمع الخلايا المرئية فقط بناءً على معيار واحد أو أكثر باستخدام صيغة
من الضروري في سير العمل اليومية الخاصة بالتقارير وتحليل البيانات أن يتم تجميع البيانات بدقة في الجداول المُرشحة، مثل حساب مبالغ المبيعات لمنتج معين أو فئة بعد تطبيق بعض الفلاتر. القيام بذلك بشكل غير صحيح قد يؤدي إلى مجاميع تتضمن بيانات لم تكن تنوي تضمينها، لذلك من المهم استخدام تقنيات تقوم بجمع البيانات المرئية فقط التي تراها على شاشتك.
يقدم هذا المقال عدة طرق عملية تناسب سيناريوهات مختلفة ومستويات الكفاءة المختلفة، لكل منها مزاياها وقيودها المحتملة. يمكنك اختيار الحل الذي يناسب حجم ورقة العمل الخاص بك، وهيكل البيانات، وعادات التشغيل. يتم تقديم خطوات مفصلة لكل حل أدناه، بالإضافة إلى تفسيرات للأخطاء المحتملة وطرق لتحسين عملية الحساب للحصول على نتائج أكثر موثوقية.
جمع الخلايا المرئية فقط بناءً على معيار واحد أو أكثر باستخدام عمود مساعد
واحدة من الأساليب الأكثر بديهية واستقرارًا لجمع الخلايا المرئية بناءً على معايير محددة هي استخدام عمود مساعد يقوم بإرجاع القيم فقط للصفوف المرئية، ثم الاستفادة من دالة SUMIFS بشروطك المطلوبة. هذا فعال بشكل خاص إذا كان مجموعة البيانات الخاصة بك تُرشَّح بطرق مختلفة بشكل متكرر أو إذا كنت بحاجة إلى إعداد حسابات يستطيع الزملاء فهمها أو تعديلها بسهولة.
المزايا: سهل الإعداد؛ جميع المنطق والحسابات تبقى مرئية في ورقة العمل؛ الأفضل للجداول الصغيرة والمتوسطة؛ قوي عند الحاجة إلى ضبط أو مراجعة الصيغ.
القيود: ينشئ أعمدة إضافية؛ قد تحتاج إلى تحديث الصيغ إذا تغير تخطيط الصفوف؛ الاستخدام المفرط قد يصبح مرهقًا في مجموعات البيانات الكبيرة جدًا.
على سبيل المثال، لجمع قيم الطلبات فقط لمنتج "هودي" في نطاق مُرشح:
1. أدخل أو انسخ الصيغة التالية في عمود فارغ بجانب مجموعة البيانات الخاصة بك (على سبيل المثال، في الخلية E2، بافتراض أن D هو عمود القيم):
اسحب مقبض التعبئة لأسفل لملء هذه الصيغة عبر جميع الصفوف في نطاق البيانات الخاص بك. ستقوم هذه الصيغة بإرجاع القيمة من العمود D إذا كان الصف مرئيًا و0 إذا كان الصف مخفيًا بواسطة الترشيح.
2. بعد إنشاء القيم المساعدة في العمود E، استخدم دالة SUMIFS لجمع القيم المرئية فقط بناءً على معاييرك. على سبيل المثال، لجمع القيم لـ "هودي" في العمود A:
يمكنك إضافة المزيد من المعايير عن طريق توسيع وسيطات SUMIFS بالصيغة =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], ...). تحقق دائمًا من نطاقاتك لضمان المحاذاة الصحيحة والنتائج المتوقعة.
كن على علم: إذا قمت بإعادة ترتيب أو إدراج أو حذف صفوف بعد إعداد الصيغ الخاصة بك، تحقق مرتين للتأكد من أن جميع المراجع لا تزال تطابق هيكل بياناتك. أحيانًا يمكن أن تحدث أخطاء نتيجة لتداخلات النطاقات أو نسيان تحديث خلايا المعايير الخاصة بك.
جمع الخلايا المرئية فقط بناءً على معايير باستخدام صيغة
إذا كنت تفضل حلًا يعتمد على الصيغة بدون الحاجة إلى إضافة أعمدة مساعدة، يمكنك استخدام مزيج من SUMPRODUCT، SUBTOTAL، OFFSET، ROW، وMIN لجمع الخلايا المرئية وفقًا لمعايير محددة. هذا النهج هو الأفضل لمستخدمي Excel ذوي الخبرة والمألوفين بصيغ المصفوفات، وهو مفيد بشكل خاص عندما ترغب في الحفاظ على نظافة الورقة بدون أعمدة إضافية.
المزايا: لا حاجة لأعمدة إضافية في ورقة العمل؛ مرنة وديناميكية؛ يتم تحديث الصيغة فورًا أثناء الترشيح أو تغيير المعايير.
القيود: يمكن أن تكون الصيغ معقدة للقراءة أو تصحيح الأخطاء، خاصةً بالنسبة لأولئك غير المألوفين بوظائف المصفوفات؛ قد يؤدي الأداء إلى البطء في الجداول الكبيرة جدًا.
انسخ أو أدخل الصيغة التالية في خلية فارغة (على سبيل المثال، لجمع الخلايا المرئية لـ "هودي" في A2:A12، مع القيم الفعلية في D2:D12، والمعايير في A17):
بعد إدخال الصيغة، اضغط Enter للحصول على النتيجة المطلوبة، كما هو موضح أدناه:
انتبه: هذا النهج حساس للنطاقات المحددة - النطاقات غير المتطابقة أو المتداخلة قد تؤدي إلى أخطاء أو نتائج غير متوقعة. اختبر الحالات الحدية، خاصةً عندما يؤدي الترشيح إلى تغيير عدد أو موقع الصفوف المرئية.
جمع الخلايا المرئية فقط بناءً على معايير باستخدام كود VBA
بالنسبة للمستخدمين المتقدمين، يوفر استخدام VBA طريقة مرنة لجمع الخلايا المرئية فقط حسب معايير محددة، خاصة عند التعامل مع سيناريوهات معقدة أو مجموعات بيانات كبيرة حيث قد تعاني الصيغ القياسية من مشاكل في الأداء أو عندما تتضمن معايير العد منطقًا متعدد الشروط يصعب التعبير عنه في صيغة واحدة. يمكن لـ VBA التكرار عبر كل صف مرئي، اختبار شروطك، وحساب المجموع بكفاءة. هذا مناسب بشكل خاص لمهام التقرير المتكررة أو عند أتمتة حسابات الملخص.
المزايا: يمكن التعامل بسهولة مع مجموعات البيانات الكبيرة، ومعايير متعددة أو ديناميكية ومنطق معقد؛ العملية تنفذ بسرعة حتى مع آلاف الصفوف؛ تقليل مخاطر الأخطاء الناتجة عن تغييرات الصيغ اليدوية.
القيود: يتطلب تمكين الماكروز؛ قد لا يكون بعض المستخدمين على دراية بـ VBA أو لديهم صلاحيات كافية؛ تتطلب التغييرات الوصول إلى محرر الماكروز. قم دائمًا بعمل نسخة احتياطية قبل تشغيل VBA على مجموعات البيانات الهامة.
1. للبدء، افتح محرر VBA بالنقر فوق أدوات المطور > Visual Basic. في النافذة التي تظهر، اذهب إلى إدراج > وحدة نمطية، والصق الكود التالي في الوحدة الجديدة:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. انقر فوق 
زر "تشغيل" (أو اضغط F5) لتنفيذ الكود. سيظهر مربع حوار يطالبك بتحديد كل من نطاق المعايير (مثل أسماء المنتجات)، نطاق القيم ليتم جمعها، وما هي القيمة التي تريدها كمرشح (مثل "هودي"). ستقوم الماكرو بجمع الصفوف المرئية فقط حيث تتحقق الشروط الخاصة بك وستعرض النتيجة في رسالة منبثقة.
نصائح عملية: استخدم كود VBA هذا عندما تحتاج غالبًا إلى إعادة حساب المجاميع بعد تغيير بياناتك أو الفلاتر. يمكنك توسيع كود VBA أكثر للعمل مع معايير متعددة عن طريق إضافة المزيد من مطالبات الإدخال أو الشروط المنطقية.
حل المشكلات: تأكد دائمًا أن النطاقات التي تختارها للمعايير والقيم لها نفس عدد الصفوف وتنتمي إلى نفس الأعمدة مثل بياناتك المُرشحة. إذا أبلغ الكود عن خطأ أو لم يُرجع المجموع المتوقع، تحقق مرتين من إعدادات الفلتر واختيارك الحالي.
اقتراحات ملخصة: لتحليل البيانات الذي يتطلب حسابات متكررة للخلايا المرئية فقط، يمكن حفظ هذه الماكرو في كتاب الماكرو الشخصي الخاص بك لتسريع التقارير اليومية. إذا لم يظهر مربع الحوار، تحقق من إعدادات الماكرو وأذونات الأمان.
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في 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 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد