بسم الله الرحمن الرحيم
اهلا بكم متابعى موقع عالم الاوفيس
عد إجمالي الكلمات في الخلية
Count
total words in a cell
ما زلت أعمل على مشروع لتحديث العديد من الصيغ القديمة لاستخدام وظائف جديدة في Excel.
فيما يلي مثال آخر مثير للاهتمام لصيغة قديمة أكثر تعقيدًا تم استبدالها بصيغة بسيطة تعمل تمامًا كما تتوقعها.
لحساب إجمالي الكلمات في خلية، يمكنك استخدام صيغة تعتمد على الدالتين TEXTSPLIT وCOUNTA، بمساعدة الدالة
TRIM. في المثال الموضح، الصيغة الموجودة في الخلية D5، المنسوخة لأسفل، هي:
=COUNTA(TEXTSPLIT(TRIM(B5),"
"))
النتيجة في الخلية D5 هي 6. عند نسخ الصيغة لأسفل، فإنها تُرجع عدد الكلمات لكل عنوان كما هو
موضح في العمود B.
صيغة عامة
=COUNTA(TEXTSPLIT(TRIM(A1),"
"))
الشرح
في هذا المثال، الهدف هو حساب إجمالي عدد الكلمات في الخلية. لا يحتوي
برنامج Excel على وظيفة مخصصة لحساب الكلمات.
ومع ذلك، مع القليل من البراعة، يمكنك إنشاء صيغة لتنفيذ هذه المهمة باستخدام مجموعة
من الوظائف المضمنة. في الإصدارات الأحدث من Excel،
أفضل طريقة هي استخدام الدالتين TEXTSPLIT
وCOUNTA.
في الإصدارات الأقدم من برنامج Excel،
يمكنك استخدام صيغة أكثر تعقيدًا تعتمد على الدالتين SUBSTITUTE
وLEN.
تستخدم كلتا الصيغتين وظيفة TRIM لتنظيف النص المصدر قبل عد الكلمات، ويتم شرح كلتا الصيغتين بالتفصيل أدناه.
يعد هذا مثالًا رائعًا لكيفية قيام الوظائف الأحدث في Excel بتبسيط الصيغ القديمة الأكثر
تعقيدًا. على الرغم من أن خيار SUBSTITUTE
الأقدم يعمل، إلا أنه يعمل بشكل غير مباشر وليس بديهيًا جدًا. خيار
TEXTSPLIT منطقي ومباشر؛ هذا منطقي فقط.
خيار TEXTSPLIT
في الإصدارات الأحدث من Excel،
أفضل طريقة هي استخدام الدالة TEXTSPLIT
في صيغة مثل هذه:
=COUNTA(TEXTSPLIT(TRIM(B5),"
"))
وعلى مستوى عالٍ، تعمل هذه الصيغة في ثلاث خطوات متميزة ومنطقية:
- قم بتنظيف النص باستخدام TRIM (إزالة المساحة الإضافية)
- إنشاء قائمة من الكلمات مع TEXTSPLIT
- عد الكلمات في القائمة مع COUNTA
من خلال العمل من الداخل إلى الخارج، يتم استخدام الدالة TRIM أولاً لإزالة أي مسافات إضافية:
TRIM(B5) // إزالة المسافات الزائدة
سيقوم TRIM بتحويل أي مسافات إلى مسافة واحدة، وإزالة أي مسافات بادئة أو زائدة. بعد
ذلك، تقوم TRIM بإرجاع النص المقطوع مباشرةً إلى الدالة TEXTSPLIT
كوسيطة نصية:
TEXTSPLIT ("All Quiet on the Western
Front"," ")
يتم توفير المحدد في TEXTSPLIT
كمسافة واحدة (" ") حيث يتم فصل الكلمات بمسافات. النتيجة
من TEXTSPLIT هي مصفوفة تحتوي على الكلمات الست في خلية العنوان B5. يتم إرجاع هذه المصفوفة مباشرةً إلى الدالة
COUNTA كما يلي:
=COUNTA({"All","Quiet","on","the","Western","Front"})
ستقوم الدالة COUNTA بإحصاء كل من الأرقام والنصوص. نظرًا لأن المصفوفة من TEXTSPLIT تحتوي على ست كلمات، فإن COUNTA تُرجع النتيجة النهائية وهي
6. وعندما يتم نسخ الصيغة، فإنها تُرجع عدد الكلمات لكل عنوان في العمود B.
التعامل مع الخلايا الفارغة
إذا كانت الخلية فارغة، أو عندما تحتوي الخلية على أحرف مسافات فقط، فسوف
تقوم TRIM بإرجاع سلسلة فارغة
(""). لسوء الحظ، ستقوم الدالة TEXTSPLIT
بإرجاع #CALC!
خطأ إذا كان النص المصدر عبارة عن سلسلة نصية فارغة وستقوم الدالة COUNTA بحساب #CALC! الخطأ وإرجاع النتيجة غير الصحيحة للرقم 1.
وبالتالي، نحتاج إلى طريقة لمنع الصيغة من إرجاع الرقم 1 إذا كان من الممكن وجود خلايا
فارغة أو فارغة. إحدى الطرق للتعامل مع هذا الموقف هي استخدام نسخة معدلة من الصيغة
مثل هذا:
=IF(TRIM(B5)=""،0,COUNTA(TEXTSPLIT(TRIM(B5)," ")))(
هنا، نقوم أولاً بالتحقق من وجود خلية فارغة (أو خلية تحتوي على مساحة
فقط) باستخدام الدالة IF والدالة TRIM.
إذا كانت النتيجة من TRIM عبارة عن سلسلة فارغة ("")، فتُرجع الدالة IF ببساطة 0 كنتيجة نهائية. بخلاف
ذلك، تقوم IF بتشغيل الصيغة الأصلية التي تُرجع عدد الكلمات كما هو موضح أعلاه.
الإصدارات الأقدم من برنامج Excel
لا تحتوي الإصدارات الأقدم من Excel
على وظيفة TEXTSPLIT
للعمل معها، لذا فهي لا تدعم الصيغة الموضحة أعلاه. ومع ذلك، يمكنك
استخدام صيغة أكثر تعقيدًا استنادًا إلى الدالتين LEN
وSUBSTITUTE،
بمساعدة الدالة TRIM مثل هذا:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
على مستوى عالٍ، تستخدم هذه الصيغة الدالة LEN
لحساب عدد الأحرف في الخلية، بمسافات وبدون مسافات، ثم تستخدم الفرق
لمعرفة عدد الكلمات:
- قم بتنظيف النص باستخدام TRIM
- عد الأحرف في النتيجة باستخدام LEN
- قم بإزالة جميع أحرف المسافة باستخدام SUBSTITUTE
- عد الأحرف في النتيجة باستخدام LEN
- اطرح العدد الثاني من العدد الأول
- أضف 1 للحصول على عدد الكلمات النهائي
ينجح هذا لأن عدد الكلمات يساوي عدد المسافات + 1، ولكنها عملية فوضوية
وغير بديهية.
يقوم الجزء الأول من الصيغة بحساب الأحرف الموجودة في الخلية B5، بعد تنظيف المسافات:
=LEN(TRIM(B5))
داخل LEN،
تقوم وظيفة TRIM بإزالة أي مسافات إضافية بين
الكلمات، أو في بداية النص أو نهايته. يعد هذا أمرًا مهمًا نظرًا لأن أي مسافات إضافية
ستؤدي إلى تقليل عدد الكلمات. تقوم TRIM
بإرجاع النص المقطوع مباشرةً إلى الدالة LEN:
LEN("All Quiet on the Western Front")// returns 30
نظرًا لأن النص الموجود في الخلية B5
لا يحتوي على أحرف مسافات إضافية، فإن النص الذي تم تسليمه بواسطة
TRIM لم يتغير والنتيجة من LEN هي 30. عند هذه النقطة، لدينا:
=30-LEN(SUBSTITUTE(B5," ",""))+1
التعامل مع الخلايا الفارغة
الكود الموضح أعلاه سوف يُرجع 1 حتى لو كانت الخلية فارغة، أو تحتوي على
مساحة فقط. يحدث هذا لأننا نضيف 1 دون قيد أو شرط إلى النتيجة، وهو أمر منطقي فقط إذا
كان لدينا كلمة واحدة على الأقل. للوقاية من هذه المشكلة، يمكنك تعديل الصيغة كما هو
موضح أدناه:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(TRIM(B5)<>"")
لاحظ أن هذه هي الصيغة الأصلية باستثناء أننا استبدلنا +1 بتعبير:
+(TRIM(B5)<>"")
يقوم هذا الرمز أولاً بقص النص الموجود في الخلية B5، ثم يقوم باختباره لمعرفة ما إذا كانت النتيجة ليست فارغة. إذا قامت TRIM بإرجاع نص فعلي، فسيُرجع التعبير TRUE. إذا قامت TRIM بإرجاع سلسلة فارغة ("")، لأن B5 فارغ، أو يحتوي على مسافة فقط، تكون النتيجة FALSE. الحيلة هنا هي أن Excel يقيم القيمة TRUE بالقيمة 1 والخطأ بالقيمة 0 عندما يكونان مشتركين في أي عملية حسابية. ونتيجة لذلك، يضيف التعبير 1 عندما يكون هناك نص في B5 ويضيف 0 إذا كانت الخلية فارغة أو تحتوي على مسافة فقط.