KutoolsforOffice — حل واحد، خمس أدوات قوية.تحقيق المزيد بجهد أقل.

كيفية حساب أو جمع الخلايا بناءً على تصفية بمعايير في Excel؟

المؤلفشياويانغتاريخ التعديل

في الاستخدام اليومي لتحليل البيانات في Excel، من الشائع أنك ستحتاج إلى حساب مجاميع أو عدٍّ يأخذان بعين الاعتبار الصفوف المُرشَّحة فقط، خاصةً عند التعامل مع قوائم طويلة أو تقارير تتطلب تركيزك على أجزاء محددة من البيانات. فدوال Excel الأساسية مثل COUNTA وSUM تكون مباشرة وواضحة عند تطبيقها على نطاقات غير مُرشَّحة، لكن بمجرد تطبيق مرشِّحات—مثل إخفاء صفوف معينة أو تضييق العرض وفقًا لمعايير معيّنة—قد تشمل هذه الدوال القياسية الصفوف المخفية في نتائجها، ما يؤدي إلى مجاميع أو أعداد غير دقيقة. ولحساب المجاميع والعدّ بدقة تامة بحيث تعكس المرشِّحات التي طبّقتها—بما في ذلك أي شروط أو معايير إضافية—يقدّم هذا البرنامج التعليمي مجموعةً من الحلول العملية المصممة خصيصًا لتلبية احتياجات سيناريوهات ومستويات مهارات متنوعة.

حساب / جمع الخلايا استنادًا إلى تصفية باستخدام الصيغ

حساب / جمع الخلايا استنادًا إلى تصفية باستخدام Kutools لـ Excel

حساب / جمع الخلايا استنادًا إلى تصفية بمعايير معيّنة باستخدام الصيغ

رمز VBA - عدّ أو جمع الخلايا المرئية تلقائيًا استنادًا إلى التصفية والمعايير عبر ماكرو مخصّص

جدول بيانات محوري - استخدام جدول بيانات محوري لتلخيص (العد/الجمع) البيانات المُرشَّحة، بما في ذلك حسب المعايير، مع خيارات تصفية تفاعلية

حساب / جمع الخلايا استنادًا إلى تصفية باستخدام الصيغ

يوفر Excel دالة SUBTOTAL للبيانات المُرشَّحة، مما يمكّنك من العد أو الجمع بدقة للخلايا المرئية فقط مع تجاهل الخلايا المخفية. وهي ميزة بالغة الفائدة عند العمل مع مجموعات بيانات تم ترشيحها وفق قيم أو شروط معيّنة، إذ تضمن دالة SUBTOTAL أن عمليات الحساب تتكيف تلقائيًا مع أي تغيير في شروط التصفية، فتحافظ بذلك على دقة تحليلك.

لحساب عدد الخلايا في نطاق التصفية، استخدم الصيغة التالية في خلية تريد أن تظهر فيها النتيجة (على سبيل المثال، D1):

=SUBTOTAL(3, C6:C19)

هنا،C6:C19 هو نطاق البيانات المُرشَّح الذي تريد حسابه. بعد إدخال الصيغة، اضغطEnter، وستُعيد النتيجة عدد الخلايا المرئية (المُرشَّحة) فقط في هذا النطاق.

لقطة شاشة لصيغة SUBTOTAL المستخدمة لعد الخلايا في البيانات المُرشَّحة في Excel

لجمع القيم في نطاق التصفية، أدخل الصيغة التالية (على سبيل المثال، في D2):

=SUBTOTAL(9, C6:C19)

تجمع هذه الصيغة الخلايا المرئية فقط بعد التصفية. اضغطEnter لرؤية المجموع.

لقطة شاشة لصيغة SUBTOTAL المستخدمة لجمع الخلايا في البيانات المُرشَّحة في Excel

نصائح:الرقم الأول في دالة SUBTOTAL هو معلمةfunction_num التي تحدّد نوع الحساب: حيث يشير الرقم 3 إلى COUNTA (عدّ القيم غير الفارغة)، ويشير الرقم 9 إلى SUM. تأكد دائمًا من أن المرشّح الخاص بك نشط وصحيح قبل الاعتماد على هذه النتائج. وإذا تغيّر نطاق البيانات، فعدّل مراجع الخلايا وفقًا لذلك. وتُعاد حساب الصيغة تلقائيًا عند تغيير المرشّح أو مسحه.

حساب / جمع الخلايا استنادًا إلى تصفية باستخدام Kutools لـ Excel

باستخدامKutools لـ Excel، يمكن للمستخدمين الاستفادة من دوال متخصصة—COUNTVISIBLE وSUMVISIBLE—للحصول فورًا على نتائج العدّ والجمع استنادًا إلى الخلايا المرئية فقط (أي المُرشَّحة وغير المخفية)، مما يتجاوز قيود صيغ Excel القياسية. ويُعدّ هذا الحل مثاليًّا لتحليل البيانات المُرشَّحة بشكل متكرر، إذ يوفّر الوقت ويقلّل الأخطاء اليدوية.

Kutools لـ Excelيقدّم أكثر من 300 ميزة متقدمة لتبسيط المهام المعقدة، مما يعزز الإبداع والكفاءة.مدمج مع إمكانات الذكاء الاصطناعي، يقوم Kutools بأتمتة المهام بدقة، مما يجعل إدارة البيانات سهلة بلا جهد.معلومات تفصيلية عن Kutools لـ Excel...         نسخة تجريبية مجانية...

بعد تثبيتKutools لـ Excel، أدخل الصيغ التالية في ورقة العمل لحساب النتائج من الخلايا المُرشَّحة (على سبيل المثال، في D1 أو D2):

لحساب الخلايا المُرشَّحة، استخدم:

=COUNTVISIBLE(C6:C19)

لجمع الخلايا المرئية المُرشَّحة، استخدم:

=SUMVISIBLE(C6:C19)

لقطة شاشة تُظهر استخدام دالتي COUNTVISIBLE وSUMVISIBLE في Excel

نصائح:تعمل هذه الدوال مع الصفوف المخفية يدويًّا والمرشَّحة على حدٍّ سواء، مما يضمن أن تعكس عمليات الحساب ما يظهر أمامك بدقة. ويمكنك أيضًا الوصول إلى هذه الميزات بسهولة من خلال قائمةKutools: انقرKutools > الوظائف المحسنة > Statistical & Math > AVERAGEVISIBLE / COUNTVISIBLE / SUMVISIBLE. ويوفّر لك ذلك وصولاً فوريًّا إلى دوال تلخيص قوية لمجموعات البيانات المرشَّحة!

لقطة شاشة تُظهر كيفية الوصول إلى دالات Kutools مثل AVERAGEVISIBLE وSUMVISIBLE

ملاحظات:إذا قمت بتغيير المرشّح أو إخفاء صفوف، فسيتم تحديث الوظائف المحسّنة تلقائيًا. هذه الصيغ متاحة فقط بعد تثبيت Kutools، وليست متوفرة في تثبيتات Excel القياسية.

Kutools لـ Excel- عزِّز Excel بقوة أكثر من 300 أداة أساسية، لتجعل عملك أسرع وأسهل، واستفد من ميزات الذكاء الاصطناعي لمعالجة البيانات بشكل أكثر ذكاءً وزيادة الإنتاجية.احصل عليه الآن

حساب / جمع الخلايا استنادًا إلى تصفية بمعايير معيّنة باستخدام الصيغ

في الممارسة العملية، قد تحتاج إلى عدّ أو جمع البيانات المُرشَّحة بناءً على شرط إضافي—مثل جمع الصفوف التي يظهر فيها اسم معيّن فقط. فبينما تساعدك التصفية على تضييق نطاق البيانات بصريًّا، يتيح لك استخدام الصيغ إجراء هذه العمليات الحسابية فورًا دون الحاجة إلى تعديل المرشّحات باستمرار. فيما يلي صيغ مفيدة للتعامل مع مثل هذه السيناريوهات.

لقطة شاشة للبيانات المُرشَّحة مع معايير العد والجمع في Excel

حساب الخلايا استنادًا إلى بيانات مُرشَّحة بمعايير معيّنة:

لحساب الخلايا المرئية (المُرشَّحة) التي تتطابق مع شرط معيّن—مثل تلك التي تحتوي على الاسم «Nelly»—أدخل الصيغة التالية في خلية (على سبيل المثال، D1):

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)), --(B6:B19="Nelly"))

هنا، يمثّلB6:B19 نطاق البيانات، و«Nelly» هو معيارك. بعد التصفية، ستقوم هذه الصيغة بحساب الصفوف المرئية فقط التي تستوفي الشرط المحدد. اضغطEnter، وستعرض الخلية العدد.

لقطة شاشة تُظهر نتيجة عد الخلايا المُرشَّحة باستخدام المعيار 'Nelly' في Excel

جمع الخلايا استنادًا إلى بيانات مُرشَّحة بمعايير معيّنة:

إذا كنت بحاجة أيضًا إلى جمع العناصر—مع نفس المعيار—فاستخدم هذه الصيغة المتقدمة (على سبيل المثال، أدخلها في D2):

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),(B6:B19="Nelly")*(C6:C19))

في هذه الصيغة، يُمثّلB6:B19 عمود المعايير، وC6:C19 عمود المبالغ، بينما يظل«Nelly» المعيار. وتُرجع الصيغة مجموع القيم المقابلة فيC6:C19 حيث يتحقّق الشرط **وتكون الصفوف مرئية**. اضغطEnter لتأكيد النتيجة وعرض المجموع.

لقطة شاشة تُظهر نتيجة جمع الخلايا المُرشَّحة باستخدام المعيار 'Nelly' في Excel

نصائح:عند إدخال هذه الصيغ، تأكد من أن النطاق والمعايير تتطابق مع بياناتك المُرشَّحة. فالصيغ تحترم التغييرات في المرشِّحات ديناميكيًّا وتعمل على عرض المجاميع أو الأعداد المحدَّثة. وللمعايير الأخرى، ما عليك سوى استبدال «Nelly» حسب الحاجة.

رمز VBA - عدّ أو جمع الخلايا المرئية تلقائيًا استنادًا إلى التصفية والمعايير عبر ماكرو مخصّص

يقدّم VBA للمستخدمين المتمرسين في الماكرو طريقة مرنة لحساب أو جمع الخلايا المرئية فقط مع إمكانية تضمين معايير—وهو مفيد جدًّا إذا كانت مرشّحاتك تتغيّر كثيرًا أو كنت بحاجة إلى أتمتة هذه العمليات الحسابية. وعلى عكس الصيغ، يمكن للماكرو معالجة مجموعات البيانات الكبيرة بسرعة، ويمكنك تخصيص السلوك ليتناسب مع احتياجاتك المحددة.

السيناريوهات المناسبة:موصى به للمستخدمين الذين يتعاملون مع جداول مُرشَّحة كبيرة ويتطلب عملهم عمليات حساب مخصصة لا تدعمها الصيغ القياسية مباشرةً. ومن أبرز المزايا الأتمتة والمرونة، في حين أن العيوب المحتملة تشمل الجهد المبدئي المطلوب للإعداد والحاجة إلى تمكين الماكرو.

احتياطات:احفظ عملك دائمًا قبل تشغيل نصوص VBA. الماكرو متاح فقط في إصدارات سطح المكتب من Excel، وليس في الإصدارات الإلكترونية أو المحمولة.

1. انقرDeveloper Tools > Visual Basic. في نافذةMicrosoft Visual Basic for Applications المفتوحة، انقرInsert > Module، والصق الكود التالي في لوحة الوحدة النمطية:

Sub SumOrCountVisibleCellsWithCriteria()
    Dim CriteriaCol As Range
    Dim DataCol As Range
    Dim Criteria As String
    Dim Total As Double
    Dim Count As Long
    Dim i As Integer
    Dim LastRow As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set CriteriaCol = Application.InputBox("Select criteria column (e.g. B6:B19)", xTitleId, Type:=8)
    Set DataCol = Application.InputBox("Select data/sum column (for sum, e.g. C6:C19; same as criteria for count)", xTitleId, Type:=8)
    Criteria = Application.InputBox("Enter criteria (e.g. Nelly)", xTitleId, "", Type:=2)
    
    Total = 0
    Count = 0
    LastRow = CriteriaCol.Rows.Count
    
    For i = 1 To LastRow
        If Not CriteriaCol.Rows(i).EntireRow.Hidden Then
            If CriteriaCol.Cells(i, 1).Value = Criteria Then
                Total = Total + DataCol.Cells(i, 1).Value
                Count = Count + 1
            End If
        End If
    Next i
    
    MsgBox "Sum: " & Total & vbCrLf & "Count: " & Count, vbInformation, xTitleId
End Sub

2. انقر زرزر التشغيل لتنفيذ الماكرو. ستظهر نافذة حوار تطلب منك تحديد عمود المعايير وعمود الجمع/العدّ، ثم تحديد المعيار المطلوب (مثل الاسم). وبعد الانتهاء، يعرض الماكرو المجموع والعدد للخلايا المرئية المتوافقة مع معاييرك.

نصائح:يجمع هذا الماكرو بين العدّ والجمع ضمن البيانات المرئية المُرشَّحة. غيّر تحديداتCriteriaCol وDataCol لتناسب احتياجات تحليلك المختلفة. تأكد دائمًا من أن الأعمدة المحددة تتوافق مع إعدادات التصفية الخاصة بك. وإذا كنت تريد العدّ فقط (وليس الجمع)، فاختر نفس النطاق لكلا المدخلين.

استكشاف الأخطاء وإصلاحها:إذا ظهر خطأ وقت التشغيل، فتأكد من أن النطاق الذي حددته يتطابق تمامًا في الحجم مع النطاق الآخر، وأن معاييرك تطابق النص الموجود في الخلايا بدقة. وبالنسبة لمجموعات البيانات الكبيرة، قد يختلف الأداء؛ لذا ننصحك بتصفية الصفوف الأساسية قبل تشغيل الماكرو.

جدول بيانات محوري - استخدام جدول بيانات محوري لتلخيص (العد/الجمع) البيانات المُرشَّحة، بما في ذلك حسب المعايير، مع خيارات تصفية تفاعلية

جدول بيانات محوري أداة متعددة الاستخدامات وتفاعلية في Excel لتلخيص كميات كبيرة من البيانات، بما في ذلك النتائج المُرشَّحة. فهي تتيح التجميع والعدّ والجمع بسهولة حسب المعايير (مثل الاسم أو الفئة)، وتسمح المرشّحات المدمجة بتغيير البيانات المعروضة بسرعة.

السيناريوهات القابلة للتطبيق:مثالية للحالات التي تحتاج فيها إلى ملخصات ديناميكية، أو تجميع مرن حسب حقول مختلفة، أو عندما تريد استكشاف النتائج بشكل تفاعلي من خلال تغيير المعايير. ومن أبرز مزاياها: سهولة الاستخدام وإعادة الحساب الفوري عبر وظيفة السحب والإفلات.

تعليمات الاستخدام:

1. حدد نطاق البيانات الذي قمت بفلترته، مع التأكد من تضمينه جميع الأعمدة التي ستُحلِّلها (بما في ذلك العناوين الرئيسية).

2. انتقل إلىإدراج > جدول بيانات محوري. في مربع الحوار، تأكد من أن الجدول أو النطاق صحيح، ثم اختر مكان إدراج الجدول المحوري (ورقة جديدة أو ورقة عمل موجودة).

3. في قائمة حقول الجدول المحوري، اسحب حقل المعيار (مثل «الاسم») إلى منطقةالصفوف، واسحب الحقل المستهدف (مثل «مبلغ الطلب») إلى منطقةالقيم. بشكل افتراضي، سيتم تلخيص البيانات باستخدام الجمع، ويمكنك النقر عليه لتغيير طريقة التلخيص إلى العد أو غيره من الخيارات حسب الحاجة.

4. استخدم القوائم المنسدلة المضمنة لتصفية جدولك المحوري وعرض العناصر المحددة فقط (مثل تصفية «نيلي»)، أو طبّق معايير متعددة لتركيز العرض على البيانات ذات الصلة.

5. سيتم تحديث جدولك المحوري فورًا ليعرض المجموع و/أو العدد للعناصر المرئية وفق المعايير المحددة. ويمكنك إعادة ترتيب الحقول، أو إضافة المزيد من المرشحات، أو تنسيق الجدول لتحسين وضوحه وسهولة قراءته.

نصائح:لا يستجيب الجدول المحوري مباشرةً لمرشحات ورقة العمل، بل يحتوي على أدوات تصفية خاصة به—أكثر قوةً ومرونةً بكثير! ولتحليلٍ متقدم، استخدم أشرطة التحكم (Slicers) أو حقولًا محسوبة إضافية. ولا تنسَ تحديث الجدول المحوري بعد تحديث البيانات الأصلية.

استكشاف الأخطاء وإصلاحها:إذا لم تتطابق النتائج مع توقعاتك، فتحقق من الحقول التي اخترتها وتأكد من أن نطاق المصدر يشمل جميع البيانات ذات الصلة. وإذا كانت بياناتك تفتقر إلى عناوين واضحة، فأضف العناوين قبل إنشاء الجدول المحوري.

اقتراحات الملخص:تستهدف حلول هذا الدليل احتياجات محددة—بدءًا من الصيغ السريعة، مرورًا بالأتمتة، ووصولًا إلى التحليل التفاعلي. اختر دالةSUBTOTAL أو الوظائف المحسّنة للحصول على إجماليات وأعداد فورية من البيانات المُرشَّحة، أو استخدم صيغًا متقدمة لتحقيق نتائج تعتمد على معايير محددة، أو الحلول البرمجية (ماكرو) لأتمتة المهام المتكررة، أو الجداول المحورية لإنشاء ملخصات مرنة للغاية وقابلة للاستكشاف. تأكد دائمًا من صحة مراجع الخلايا والمعايير لتجنب نتائج غير دقيقة. ولرفع الكفاءة إلى أقصى حد، نظّم بياناتك باستخدام عناوين واضحة وتنسيق متسق.

أفضل أدوات الإنتاجية لمكتبتك

🤖KUTOOLS AI مساعد: ثوّر تحليل البيانات استنادًا إلى:التنفيذ الذكي   |  إنشاء الكود|  إنشاء الصيغ المخصصة  |  تحليل البيانات وإنشاء المخططات البيانية|  استدعاء الوظائف المحسنة…
الميزات الشائعة:البحث، التمييز أو وضع علامة على المكررات   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |  التقريب بدون استخدام الصيغة...
بحث متقدم:VLookup متعدد المعايير  |  VLookup متعدد القيم  |   VLookup عبر أوراق متعددة   |   مطابقة غامضة....
قائمة منسدلة متقدمة:إنشاء قائمة منسدلة بسرعة   |  قائمة منسدلة تابعة   |  قائمة منسدلة متعددة الاختيار....
مدير الأعمدة:إضافة عدد محدد من الأعمدة|نقل الأعمدة|تبديل حالة ظهور الأعمدة المخفية|مقارنة النطاقات والأعمدة...
ميزات مميزة:التركيز على الشبكة   |  عرض التصميم   |شريط الصيغ المحسن   | مدير الدفتر والورقة   |  مكتبة الموارد(نص تلقائي)|  أداة اختيار التاريخ   |  تجميع ورقات العمل  |  تشفير/فك تشفير الخلايا   | إرسال رسائل البريد الإلكتروني حسب القائمة   |  مرشح متقدم   |   تصفية خاصة(تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...) ...
أفضل 15 مجموعات الأدوات:12 أدواتالنصوص(إضافة نص،حذف الأحرف المحددة، ...)|   50+أنواعالمخططات البيانية(مخطط جانت، ...)|   40+ صيغعملية(حساب العمر بناءً على تاريخ الميلاد، ...)|   19 أدواتالإدراج(إدراج رمز QR،إدراج صورة من المسار، ...)|   12 أدواتالتحويل(تحويل إلى كلمات،تحويل العملة، ...)|   7 أدواتدمج وتقسيم(دمج متقدم للصفوف،تقسيم الخلايا، ...)|... وأكثر من ذلك
استخدم Kutools باللغة التي تفضلها – يدعم الإنجليزية والإسبانية والألمانية والفرنسية والصينية و40+ لغات أخرى!

عزِّز مهاراتك في 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.

ExcelWordOutlookTabsPowerPoint
  • حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
  • برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
  • يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
  • تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
  • أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل