أحد المتطلبات الشائعة عند العمل مع مجموعات البيانات الكبيرة هو حساب عدد المرات التي تظهر فيها كلمة أو قيمة معينة.
قد تحتاج إلى معرفة عدد المرات التي تظهر فيها الكلمة في خلية أو عمود أو ورقة عمل بأكملها.
يمكن أن تكون هذه المشكلة من نوعين. قد تحتاج إلى حساب عدد الخلايا التي تحتوي على كلمة معينة ، أو عدد المرات التي تظهر فيها كلمة معينة في خلية.
كيفية حساب عدد المرات التي تظهر فيها الكلمة في Excel
عد كلمة معينة في نطاق باستخدام COUNTIF
تتمثل المهمة الرئيسية لوظيفة COUNTIF في حساب عدد مرات استيفاء الشرط.
أحد الاستخدامات الأكثر شيوعًا لهذه الوظيفة هو مطابقة قيمة معينة لقيم الخلية في نطاق. يمكن أن تكون هذه القيمة سلسلة أو رقمًا.
صيغة الدالة COUNTIF هي:
COUNTIF ( النطاق ، الحالة )
هنا،
النطاق هو نطاق الخلايا الذي تريد المطابقة والعدد منه
الشرط هو الشرط الذي يجب الوفاء به من أجل حساب الخلية على أنها مطابقة
ما ستفعله الوظيفة أعلاه هو البحث عن جميع الخلايا في النطاق المحدد وإحصاء جميع الخلايا التي تطابق الشرط المحدد . بمجرد الانتهاء من البحث في جميع الخلايا في النطاق ، سيعيد عدد الخلايا المتطابقة.
دعونا نطبق هذه الوظيفة على مثال بسيط. لنفترض أننا نريد حساب عدد المرات التي يظهر فيها اسم "بيتر" في النطاق A2: A10 كما هو موضح أدناه:
لتطبيق وظيفة COUNTIF للمشكلة المذكورة أعلاه ، إليك الخطوات التي يجب اتباعها:
حدد الخلية التي تريد كتابة العدد فيها (الخلية D3 في حالتنا).
في هذه الخلية ، اكتب الصيغة:
COUNTIF (A2: A10، "Peter") =
اضغط على مفتاح العودة.
سيعطي هذا عدد المرات التي تظهر فيها كلمة " بيتر " في نطاق الخلايا A2: A10.
ملاحظة : تحسب هذه الطريقة فقط عدد الخلايا التي تحتوي بالضبط على كلمة "Peter". إذا كانت إحدى الخلايا ، على سبيل المثال ، تحتوي على الكلمات "Peter Peter" في خلية واحدة ، فلن تعتبرها وظيفة COUNTIF تطابقًا ، وبالتالي لن تحسب الخلية.
حساب عدد المرات التي تظهر فيها كلمة في النطاق
ماذا لو احتجت إلى معرفة عدد المرات التي تظهر فيها نفس الكلمة في سلاسل في خلايا متعددة ؟
لهذا ، سوف تحتاج إلى التفاف الدالة SUMPRODUCT حول الصيغة أعلاه. لذلك ، إذا كان لديك نطاق من الخلايا للعمل عليها ، فيجب أن تكون صيغتك:
SUMPRODUCT (LEN ( cell_range ) -LEN (SUBSTITUTE ( cell_range ، word ، ""))) / LEN ( word ))
تضمن الدالة SUMPRODUCT حصولك على صفيف يحتوي على عدد كل خلية في النطاق.
لنفترض أن لديك المجموعة التالية من السلاسل النصية (A2: A3) وتريد معرفة عدد المرات التي تظهر فيها كلمة " سعيد " في كل منها:
ما عليك سوى اتباع هذه الخطوات:
حدد الخلية التي تريد كتابة العدد بها (الخلية B6 في هذه الحالة).
في هذه الخلية ، اكتب الصيغة:
SUMPRODUCT ((LEN (A2: A3) -LEN (البديل (A2: A3، "happy"، ""))) / LEN ("happy"))
بدلاً من تحديد الكلمة المراد استبدالها ، يمكنك أيضًا تحديد مرجع الخلية التي تحتوي على الكلمة ، مثل هذا:
SUMPRODUCT ((LEN (A2: A3) -LEN (SUBSTITUTE (A2: A3، A6، ""))) / LEN (A6))
اضغط على مفتاح العودة.
سيعطي هذا عدد المرات التي تظهر فيها كلمة "سعيد" في النطاق A2: A3.
إذا كنت تريد أيضًا نسخ نفس الصيغة لحساب عدد تكرارات الكلمات الأخرى في نفس النطاق ، فيمكنك إصلاح المرجع إلى خلية النطاق A2: A3 عن طريق إضافة علامة $:
SUMPRODUCT ((LEN ($ A $ 2: $ A $ 3) -LEN (SUBSTITUTE ($ A $ 2: $ A $ 3، A6، "))) / LEN (A6))
الآن ، يمكنك بسهولة نسخ الصيغة للكلمات الأخرى عن طريق سحب مقبض التعبئة لأسفل.
