كيف تجمع الخلايا المرئية فقط وفقًا لمعايير محددة في Excel؟
في Excel، يمكن للمستخدمين عادةً جمع الخلايا بناءً على معايير محددة باستخدام دالة SUMIFS. لكن عند التعامل مع البيانات المُرشَّحة، يؤدي تطبيق SUMIFS مباشرةً إلى تضمين كلٍّ من الخلايا المرئية والمخفية في الحساب، ما ينتج غالبًا نتائج غير صحيحة إذا كنت بحاجة إلى جمع الخلايا المرئية فقط (أي غير المُرشَّحة) التي تطابق معايير معيّنة، كما يظهر في لقطة الشاشة أدناه.
اجمع الخلايا المرئية فقط استنادًا إلى معيار واحد أو أكثر باستخدام عمود مساعد
اجمع الخلايا المرئية فقط استنادًا إلى معيار واحد أو أكثر باستخدام صيغة
اجمع الخلايا المرئية فقط استنادًا إلى المعايير باستخدام كود VBA
من الاحتياجات الشائعة في التقارير اليومية وسير عمل تحليل البيانات تجميع البيانات بدقة ضمن الجداول المُرشَّحة، مثل حساب إجمالي مبيعات منتج أو فئة معيَّنة بعد تطبيق مرشحات محددة. وإذا لم يُنفَّذ ذلك بشكل صحيح، فقد تظهر نتائج تضم بيانات لم تكن تنوي تضمينها، لذا يُعد استخدام تقنيات تجمع فقط البيانات المرئية التي تراها على شاشتك أمرًا بالغ الأهمية.
تقدم هذه المقالة عدة طرق عملية تناسب سيناريوهات متنوعة ومستويات كفاءة مختلفة، ولكل منها مزاياها وقيودها المحتملة. يمكنك اختيار الحل الأنسب بناءً على حجم ورقة العمل الخاصة بك، وهيكل بياناتك، وعاداتك التشغيلية. فيما يلي خطوات مفصلة لكل حل، إلى جانب شرح للأخطاء الشائعة وطرق تحسين عملية الحساب للحصول على نتائج أكثر موثوقية.
اجمع الخلايا المرئية فقط استنادًا إلى معيار واحد أو أكثر باستخدام عمود مساعد
تُعد إحدى أكثر الطرق بديهيةً وثباتًا لجمع الخلايا المرئية وفق معايير محددة هي استخدام عمود مساعد يُطبّق قيمة العودة فقط على الصفوف المرئية، ثم تطبيق دالة SUMIFS مع الشروط المطلوبة. وتبرز فعالية هذه الطريقة بشكل خاص إذا كان جدول البيانات الخاص بك يخضع باستمرار لمرشحات مختلفة، أو إذا احتجت إلى إعداد عمليات حسابية يستطيع زملاؤك فهمها أو تعديلها بسهولة.
المزايا:سهل الإعداد؛ تبقى جميع المنطق والحسابات مرئية في ورقة العمل؛ الأفضل للجداول الصغيرة والمتوسطة؛ وقوي جدًّا عند الحاجة إلى تعديل الصيغ أو مراجعتها.
القيود:ينشئ أعمدة إضافية؛ وقد تحتاج إلى تحديث الصيغ إذا تغيّر تخطيط الصفوف؛ كما قد يصبح الاستخدام المكثف مرهقًا مع مجموعات البيانات الكبيرة جدًّا.
على سبيل المثال، لجمع قيم الطلبات الخاصة بالمنتج «Hoodie» فقط في نطاق التصفية:
1. أدخل أو انسخ الصيغة التالية في عمود فارغ بجانب مجموعة البيانات الخاصة بك (مثل الخلية E2، بافتراض أن العمود D يحتوي على القيم):
اسحب مقبض التعبئة لأسفل لتطبيق هذه الصيغة على جميع صفوف نطاق البيانات الخاص بك. ستُرجع الصيغة القيمة من العمود D إذا كان الصف مرئيًّا، وستُرجع 0 إذا كان الصف مخفيًّا بسبب التصفية.
2. بعد إنشاء القيم المساعدة في العمود E، استخدم دالة SUMIFS لجمع القيم المرئية فقط وفقًا لمعاييرك. على سبيل المثال، لجمع القيم الخاصة بـ «Hoodie» في العمود A:
يمكنك إضافة معايير إضافية ببساطة عن طريق توسيع وسيطات الدالة SUMIFS بالتنسيق التالي:=SUMIFS(نطاق_المجموع، نطاق_المعيار1، معيار1، [نطاق_المعيار2، معيار2]، [نطاق_المعيار3، معيار3]، ...). تأكد دائمًا من صحة نطاقاتك لضمان المحاذاة الدقيقة والحصول على النتائج المتوقعة!
تنبيه: إذا أعدت ترتيب الصفوف أو أدخلت صفوفًا جديدة أو حذفت بعضها بعد إعداد صيغك، فتأكد من مراجعة جميع المراجع للتحقق من توافقها مع هيكل بياناتك. قد تنشأ بعض الأخطاء نتيجة نطاقات غير مُحاذاة أو نسيان تحديث خلايا المعايير.
جمع الخلايا المرئية فقط استنادًا إلى المعايير باستخدام صيغة
إذا كنت تفضّل حلاً يعتمد على الصيغ ولا يتطلب إضافة أعمدة مساعدة، يمكنك استخدام تركيبة من الدوال SUMPRODUCT وSUBTOTAL وOFFSET وROW وMIN لجمع الخلايا المرئية وفقًا لمعايير محددة. تُعد هذه الطريقة المثالية لمستخدمي Excel ذوي الخبرة والذين يرتاحون للعمل مع صيغ المصفوفات، وهي مفيدة بشكل خاص عندما تريد الحفاظ على ورقتك نظيفة وخالية من الأعمدة الإضافية.
المزايا:لا حاجة لأعمدة إضافية في ورقة العمل؛ صيغة مرنة وديناميكية تُحدَّث فورًا عند تطبيق المرشحات أو تغيير المعايير.
القيود:قد تكون الصيغ صعبة القراءة أو التصحيح، خاصةً لمن ليسوا على دراية بوظائف المصفوفات؛ وقد يتباطأ الأداء في الجداول الكبيرة جدًّا.
انسخ أو أدخل الصيغة التالية في خلية فارغة (على سبيل المثال، لجمع الخلايا المرئية الخاصة بـ «Hoodie» في A2:A12، مع القيمة الفعلية في D2:D12، والمعيار في A17):
بعد إدخال الصيغة، اضغطEnterللحصول على النتيجة المطلوبة، كما هو موضح أدناه:
انتبه: هذه الطريقة حساسة للنطاقات المحددة—فقد تؤدي النطاقات غير المتطابقة أو المتداخلة إلى أخطاء أو نتائج غير متوقعة. لذا، اختبر الحالات الحدية بدقة، خاصةً عند تغيير التصفية لعدد الصفوف المرئية أو مواضعها.
اجمع الخلايا المرئية فقط استنادًا إلى المعايير باستخدام كود VBA
يُعدّ استخدام VBA خيارًا مرنًا للمستخدمين المتقدمين لجمع الخلايا المرئية فقط وفقًا لمعايير محددة، خاصةً عند التعامل مع سيناريوهات معقدة أو مجموعات بيانات ضخمة، حيث قد تواجه الصيغ القياسية اختناقات في الأداء، أو عندما تتضمّن معايير العد منطقًا متعدد الشروط يصعب التعبير عنه بصيغة واحدة. يستطيع VBA أن يمرّ على كل صف مرئي، ويختبر شروطك المخصصة، ويحسب المجموع بكفاءة عالية. وتبرز فائدة هذه الطريقة بشكل خاص في مهام التقارير المتكررة أو عند أتمتة حسابات الملخصات.
المزايا:تتعامل بسلاسة مع مجموعات البيانات الكبيرة، والمعايير المتعددة أو الديناميكية، والمنطق المعقد؛ وتنفّذ العمليات بسرعة فائقة حتى عند التعامل مع آلاف الصفوف؛ كما تقلل من احتمالية وقوع أخطاء ناتجة عن التعديلات اليدوية في الصيغ.
القيود:تتطلب هذه الميزة تمكين وحدات الماكرو؛ وقد لا يكون بعض المستخدمين على دراية بلغة VBA أو لا يمتلكون الصلاحيات الكافية لتشغيلها؛ كما أن إجراء أي تغييرات يتطلب الوصول إلى محرر الماكرو. لذا، ننصحك دائمًا بعمل نسخة احتياطية قبل تشغيل أكواد VBA على مجموعات البيانات المهمة.
1. للبدء، افتحمحرر VBAبالنقر علىأدوات المطور > Visual Basic. في النافذة التي تظهر، انتقل إلىإدراج > وحدة نمطية (Module)، ثم الصق الكود التالي في الوحدة النمطية الجديدة:
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) لتنفيذ الكود. بعد التنفيذ، سيظهر لك مربّع حوار يطلب منك تحديد نطاق المعايير (مثل أسماء منتجاتك)، ونطاق القيم الذي تريد جمعه، والقيمة التي ترغب في استخدامها كمرشّح (مثل «Hoodie»). وسيقوم الماكرو بجمع الصفوف المرئية فقط التي تستوفي معيارك وعرض النتيجة في رسالة منبثقة.
نصائح عملية:استخدم كود VBA هذا عندما تحتاج غالبًا إلى إعادة حساب المجاميع بعد تغيير بياناتك أو مرشّحاتك. ويمكنك توسيع كود VBA ليتعامل مع معايير متعددة عن طريق إضافة المزيد من مطالبات الإدخال أو الشروط المنطقية.
استكشاف الأخطاء وإصلاحها:تأكد دائمًا من أن النطاقات التي تحددها للمعايير والقيم تحتوي على نفس عدد الصفوف وتنتمي إلى نفس الأعمدة الموجودة في بياناتك المُرشَّحة. إذا أبلغ الكود عن خطأ أو لم يُرجع المجموع المتوقع، فراجع إعدادات المرشِّح والتحديد النشط مرة أخرى.
اقتراحات ختامية:إذا كنت تُجري تحليلات بيانات تتطلب حسابات متكررة للخلايا المرئية فقط، فإن حفظ هذا الماكرو في مصنف الماكرو الشخصي (Personal Macro Workbook) يمكن أن يُسرّع إعداد تقاريرك اليومية. وإذا لم يظهر مربّع الحوار، فتأكد من إعدادات الماكرو وأذونات الأمان الخاصة بك.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل