حساب تكرار النصوص المحددة في كامل مصنف Excel

من السهل علينا حساب عدد مرات ظهور نص أو قيمة عددية معينة في نطاق من الخلايا باستخدام دالة COUNTIF. ومع ذلك، لحساب تكرار النصوص المحددة في كامل المصنف، يتطلب الأمر صيغة أكثر تعقيدًا. يهدف هذا المقال إلى توضيح صيغة تستند إلى دوال SUMPRODUCT وCOUNTIF وINDIRECT لحل هذه المشكلة.

كيفية حساب التكرارات في كامل المصنف؟

لنفترض أن لديك 4 أوراق عمل (Sheet2 وSheet3 وSheet4 وSheet5) في مصنفك، وتريد حساب عدد مرات ظهور النص المحدد “Laptop” عبر هذه الأوراق الأربعة، يمكنك القيام بما يلي لتحقيق ذلك.

الصيغة العامة

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&range1&”‘!”&range2),criteria))

المعاملات

كيفية استخدام هذه الصيغة؟

1. ستحتاج أولاً إلى إنشاء ورقة جديدة وإدراج جميع أسماء الأوراق في نطاق من الخلايا. في هذه الحالة، قمت بإدراج جميع أسماء الأوراق في النطاق B7:B10.

2. حدد خلية فارغة لعرض النتيجة.

3. أدخل واحدة من الصيغ أدناه واضغط على مفتاح Enter للحصول على النتيجة.

إذا كنت قد أدخلت بالفعل النص المحدد في خلية (مثل B3)، يمكنك الإشارة إلى هذه الخلية في الصيغة كما يلي:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

أو اكتب مباشرة النص المحدد محاطًا بعلامات اقتباس مزدوجة في الصيغة كما يلي:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),"Laptop"))

ملاحظة: هنا النطاق A1:Z10000 هو نطاق اعتباطي، يمكنك تغييره ليتناسب مع بياناتك.

كيف تعمل هذه الصيغة؟

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

• "'"&B7:B10&"'!A1:Z10000": هنا يتم ربط كل اسم ورقة في المصفوفة عن طريق التسلسل مع النطاق A1:Z10000، وستحصل على مصفوفة كالتالي {"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}.
• INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): تقوم دالة INDIRECT بالإشارة إلى النطاقات في الأوراق الأربعة.
• COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): تقوم دالة COUNTIF بعد عدد مرات ظهور القيمة المحددة في كل ورقة وتعيد مصفوفة {13;13;13;13}. وهذا يعني أن النص المحدد يظهر 13 مرة في كل ورقة.
• SUMPRODUCT({13;13;13;13}): تقوم دالة SUMPRODUCT بجمع جميع الأرقام في المصفوفة وتعيد النتيجة النهائية وهي 52.

الدوال ذات الصلة

الصيغ ذات الصلة