حساب الكلمات المحددة في نطاق في Excel

يشرح هذا المقال صيغة لحساب عدد مرات ظهور كلمة محددة في نطاق من الخلايا في Excel.

كيفية حساب الكلمات المحددة في نطاق من الخلايا في Excel؟

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

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

المعاملات

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

لنفترض أنك تريد حساب عدد مرات كلمة “tool” في النطاق B5:B8، يمكنك القيام بذلك كما يلي.

حدد خلية فارغة، وأدخل الصيغة التالية فيها ثم اضغط على مفتاح Enter.

نصيحة: يمكنك نسخ الصيغة ولصقها مباشرة في الخلية المحددة.

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

ملاحظات:

• 1. يرجى تغيير النطاق B5:B8 والكلمة “tool” بما يناسب احتياجاتك؛
• 2. إذا لم يتم العثور على الكلمة، سيتم إرجاع 0؛
• 3. الصيغة حساسة لحالة الأحرف؛
• 4. إذا كنت تريد حساب كلمة محددة في نطاق من الخلايا مع تجاهل حالة الأحرف، يرجى استخدام دالة UPPER داخل SUBSTITUTE كما هو موضح في الصيغة أدناه:
• =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER("tool"),"")))/LEN("tool"))
• 

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

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

• 1. LEN(B5:B8): تقوم دالة LEN بحساب الطول الإجمالي للسلسلة النصية لكل خلية في النطاق B5:B8، وسيتم إرجاعها كأرقام صفيف {47;23;23;15}؛
• 2. LEN(SUBSTITUTE(B5:B8,"tool","")): هنا تقوم دالة SUBSTITUTE بإزالة الكلمة المحددة “tool” من كل خلية في النطاق B5:B8، وبعد ذلك تقوم دالة LEN بحساب الطول لكل خلية في النطاق B5:B8 بدون الكلمة المحددة “tool”. يتم إرجاع الأرقام كصفيف {35;19;19;15}؛
• 3. (LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")): الآن أصبحت هذه الجزء من الصيغة {47;23;23;15}-{35;19;19;15}. بعد الطرح، يكون الناتج {12;4;4;0}؛
• 4. LEN("tool"): تقوم دالة LEN هنا بحساب الطول للكلمة “tool”، وتعيد الناتج 4؛
• 5. LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"): كما ذُكر أعلاه، ستظهر هذه الجزء كـ {12;4;4;0}/4. يتم قسمة 4 على كل رقم في الصفيف والحصول على النتيجة {3;1;1;0}؛
• 6. SUMPRODUCT({3;1;1;0}): تقوم دالة SUMPRODUCT بجمع الأرقام في الصفيف وإرجاع العدد الإجمالي للكلمة “tool” في النطاق B5:B8.

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

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