اكسل

عد كلمات محددة في نطاق

Count Specific Words Range

صيغة Excel: عد كلمات معينة في نطاقصيغة عامة
= SUMPRODUCT (( LEN (rng)- LEN ( SUBSTITUTE (rng,txt,'')))/ LEN (txt))
ملخص

لحساب عدد المرات التي تظهر فيها كلمة معينة (أو أي سلسلة فرعية) داخل نطاق من الخلايا ، يمكنك استخدام صيغة تستند إلى الدالات SUBSTITUTE و LEN و SUMPRODUCT. في المثال الموضح ، الصيغة في C11 هي:



 
= SUMPRODUCT (( LEN (B5:B8)- LEN ( SUBSTITUTE (B5:B8,C2,'')))/ LEN (C2))

ملاحظة: الصيغة الموجودة في هذه الصفحة تحسب i مظاهر كلمة في نطاق. على سبيل المثال ، إذا كانت الخلية تحتوي على مثيلين من الكلمة ، فستساهم بـ 2 في العدد الإجمالي. إذا كنت تريد فقط العد الخلايا التي تحتوي على كلمة معينة و انظر هذه الصيغة البسيطة على أساس وظيفة COUNTIF .

تفسير

في النسخة العامة من الصيغة ، rng يمثل النطاق المطلوب التحقق منه ، و رسالة قصيرة هي الكلمة أو السلسلة الفرعية المطلوب عدها.





كيفية إدراج صف في اختصار Excel

في المثال الموضح ، B5: B8 هو النطاق المطلوب التحقق منه ، ويحتوي C2 على النص (كلمة أو سلسلة فرعية) المطلوب حسابه.

لكل خلية في النطاق ، يزيل SUBSTITUTE السلسلة الفرعية من النص الأصلي ويحسب LEN طول النص بدون السلسلة الفرعية. ثم يُطرح هذا الرقم من طول النص الأصلي. والنتيجة هي عدد الأحرف التي تمت إزالتها بواسطة SUBSTITUTE.



بعد ذلك ، يتم تقسيم عدد الأحرف التي تمت إزالتها على طول السلسلة الفرعية. لذلك ، إذا كان طول السلسلة الفرعية أو الكلمة 5 أحرف ، وكان هناك 10 أحرف مفقودة بعد إزالتها من النص الأصلي ، فنحن نعلم أن السلسلة الفرعية / الكلمة ظهرت مرتين في النص الأصلي.

كيفية نسخ نفس الصيغة في Excel

نظرًا لأن الحساب أعلاه يتم التفافه في دالة SUMPRODUCT ، فإن النتيجة هي صفيف يحتوي على رقم لكل خلية في النطاق. تمثل هذه الأرقام عدد تكرارات السلسلة الفرعية في كل خلية. في هذا المثال ، تبدو المصفوفة كما يلي: {1101}

تعديل تسميات المحور الأفقي

أخيرًا ، يجمع SUMPRODUCT معًا كل العناصر في المصفوفة للحصول على إجمالي تكرارات السلسلة الفرعية في نطاق الخلايا.

تجاهل الحالة

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

 
= SUMPRODUCT (( LEN (rng)- LEN ( SUBSTITUTE (( UPPER (rng)), UPPER (txt),'')))/ LEN (txt))
المؤلف ديف برونز


^