كيفية استخدام شروط متعددة في Excel COUNTIF و COUNTIFS

يحتوي Excel على العديد من الوظائف حيث يحتاج المستخدم إلى تحديد شرط أو أكثر للحصول على النتيجة.على سبيل المثال ، إذا كنت تريد حساب عدد الخلايا استنادًا إلى معايير متعددة ، فيمكنك استخدام الدالتين COUNTIF أو COUNTIFS في Excel.

يغطي هذا البرنامج التعليمي طرقًا مختلفة لاستخدام معايير فردية أو متعددة في وظائف COUNTIF و COUNTIFS في Excel.

بينما سأركز بشكل أساسي على وظائف COUNTIF و COUNTIFS في هذا البرنامج التعليمي ، يمكن أيضًا استخدام كل هذه الأمثلة مع وظائف Excel الأخرى التي تأخذ معايير متعددة مثل الإدخال (مثل SUMIF و SUMIFS و AVERAGEIF و AVERAGEIFS).

مقدمة عن وظائف Excel COUNTIF و COUNTIFS

دعونا نفهم أولاً كيفية استخدام وظائف COUNTIF و COUNTIFS في Excel.

وظيفة Excel COUNTIF (بمعيار واحد)

تعمل وظيفة Excel COUNTIF بشكل أفضل عندما تريد حساب عدد الخلايا استنادًا إلى معيار واحد.إذا كنت تريد العد بناءً على معايير متعددة ، فاستخدم الدالة COUNTIFS.

بناء الجملة

= COUNTIF (النطاق ، الشرط)

معلمات الإدخال

• نطاق -  نطاق الخلايا التي تريد حسابها.
• حالة -المعايير التي يجب تقييمها مقابل نطاق الخلايا المراد حسابها.

دالة Excel COUNTIFS (بمعايير متعددة)

تعمل وظيفة Excel COUNTIFS بشكل أفضل عندما تريد حساب عدد الخلايا بناءً على معايير متعددة.

بناء الجملة

= COUNTIFS (نطاق_المعايير 1 ، معايير 1 ، [نطاق_المعايير 2 ، معايير 2] ...)

معلمات الإدخال

• نطاق_المعايير 1 - نطاق الخلايا الذي تريد تقييمه مقابل المعيار 1.
• معايير 1 - المعيار الذي تريد تقييمه هو معيار_الترتيب 1 لتحديد الخلايا المراد تقييمها.
• [نطاق_المعايير 2] - نطاق الخلايا الذي تريد تقييمه مقابل المعيار 2.
• [معايير 2] - المعيار الذي تريد تقييمه هو معيار _range2 لتحديد الخلايا المطلوب عدها.

الآن دعونا نلقي نظرة على بعض الأمثلة على استخدام معايير متعددة في وظيفة COUNTIF في Excel.

استخدم الشرط NUMBER في دالة Excel COUNTIF

# 1 عد الخلايا عندما يساوي المعيار قيمة

للحصول على عدد الخلايا حيث تكون معلمة الشرط مساوية للقيمة المحددة ، يمكنك إدخال الشرط مباشرة أو استخدام مرجع خلية يحتوي على الشرط.

يوجد أدناه مثال حيث نحسب الخلايا التي تحتوي على الرقم 9 (مما يعني أن معلمة الشرط تساوي 9).ها هي الصيغة:

= COUNTIF ($ B $ 2: $ B $ 11، D3)

في المثال أعلاه (في الصورة) ، يكون المعيار في الخلية D3.يمكنك أيضًا إدخال الشروط مباشرةً في الصيغ.على سبيل المثال ، يمكنك أيضًا استخدام:

= COUNTIF ($ B $ 2: $ B $ 11,9،XNUMX)

# 2 عد الخلايا عندما يكون الشرط أكبر من القيمة

للحصول على عدد الخلايا ذات القيمة الأكبر من القيمة المحددة ، نستخدم عامل التشغيل أكبر من (">").يمكننا استخدامه مباشرة في الصيغ أو يمكننا استخدام مراجع الخلايا مع الشروط.

عندما نستخدم عامل تشغيل في شرط ما في Excel ، نحتاج إلى وضعه داخل علامتي اقتباس مزدوجتين.على سبيل المثال ، إذا كان الشرط أكبر من 10 ، فسنحتاج إلى إدخال "> 10" كشرط (انظر الصورة أدناه):

ها هي الصيغة:

= COUNTIF ($ B $ 2: $ B $ 11، "> 10)

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

= COUNTIF ($ B $ 2: $ B $ 11، D3)

يمكن أن يكون هذا هو الحال أيضًا عندما تريد أن يكون الشرط في خلية ولكن لا تريد استخدامه مع عامل التشغيل.على سبيل المثال ، قد ترغب في أن يكون الرقم في الخلية D3 هو 10 بدلاً من> 10.

في هذه الحالة ، تحتاج إلى إنشاء معلمة شرطية ، وهي مزيج من عامل التشغيل ومرجع الخلية (انظر الصورة أدناه):

= COUNTIF ($ B $ 2: $ B $ 11، ”>” & D3)

ملاحظة: عندما تقوم بدمج عوامل التشغيل ومراجع الخلايا ، يتم دائمًا تضمين عوامل التشغيل بين علامتي اقتباس.عوامل التشغيل ومراجع الخلية متصلة بواسطة علامة العطف (&).

# 3 عد الخلايا عندما يكون المعيار أقل من قيمة

للحصول على عدد الخلايا ذات القيمة الأقل من القيمة المحددة ، نستخدم العامل أقل من ("<").يمكننا استخدامه مباشرة في الصيغ أو يمكننا استخدام مراجع الخلايا مع الشروط.

عندما نستخدم عامل تشغيل في شرط ما في Excel ، نحتاج إلى وضعه داخل علامتي اقتباس مزدوجتين.على سبيل المثال ، إذا كان المعيار هو أن الكمية يجب أن تكون أقل من 5 ، فسنحتاج إلى إدخال "<5" كمعيار (انظر الصورة أدناه):

= COUNTIF ($ B $ 2: $ B $ 11، ”<5”)

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

= COUNTIF ($ B $ 2: $ B $ 11، D3)

أيضًا ، قد ترغب في أن يكون الشرط في الخلية ، ولكن ليس مع عامل التشغيل.على سبيل المثال ، قد ترغب في أن تحتوي الخلية D3 على الرقم 5 بدلاً من <5.

في هذه الحالة ، تحتاج إلى إنشاء معلمة شرطية هي مزيج من عامل التشغيل ومرجع الخلية:

= COUNTIF ($ B $ 2: $ B $ 11، "<" & D3)

ملاحظة: عندما تقوم بدمج عوامل التشغيل ومراجع الخلايا ، يتم دائمًا تضمين عوامل التشغيل بين علامتي اقتباس.عوامل التشغيل ومراجع الخلية متصلة بواسطة علامة العطف (&).

# 4 عد الخلايا بمعايير متعددة - بين قيمتين

للحصول على عدد القيم بين قيمتين ، نحتاج إلى استخدام شروط متعددة في دالة COUNTIF.

فيما يلي طريقتان للقيام بذلك:

الطريقة الأولى: استخدم الدالة COUNTIFS

يمكن أن تأخذ الدالة COUNTIFS شروطًا متعددة كوسيطات وتعداد الخلايا فقط إذا كانت جميع الشروط صحيحة.لحساب عدد الخلايا التي تحتوي على قيم بين قيمتين محددتين ، مثل 5 و 10 ، يمكننا استخدام وظيفة COUNTIFS التالية:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

ملاحظة: لا تحسب الصيغة أعلاه الخلايا التي تحتوي على 5 أو 10.إذا كنت تريد تضمين هذه الخلايا ، فاستخدم عامل التشغيل أكبر من أو يساوي (> =) وأقل من أو يساوي (<=).ها هي الصيغة:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

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

يمكنك أيضًا استخدام مجموعة من مراجع الخلايا وعوامل التشغيل (إدخال عوامل التشغيل مباشرة في الصيغ).عند دمج عوامل التشغيل ومراجع الخلايا ، يتم دائمًا إحاطة عوامل التشغيل بعلامات اقتباس مزدوجة.عوامل التشغيل ومراجع الخلية متصلة بواسطة علامة العطف (&).

الطريقة 2: استخدم دالتين COUNTIF

إذا كانت لديك شروط متعددة ، يمكنك استخدام COUNTIFS أو إنشاء مجموعة من وظائف COUNTIF.الصيغة التالية ستفعل الشيء نفسه:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

في الصيغة أعلاه ، نجد أولاً عدد الخلايا بقيمة أكبر من 5 ثم نطرح عدد الخلايا ذات القيمة الأكبر من 10.سيعطينا هذا النتيجة 5 (أي بقيم أكبر من 5 وأقل من أو تساوي 10).

إذا كنت تريد أن تحتوي الصيغة على كل من 5 و 10 ، فاستخدم الصيغة التالية بدلاً من ذلك:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

إذا كنت تريد أن تستبعد الصيغة "5" و "10" من العدد ، فاستخدم الصيغة التالية:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

يمكنك تضمين هذه الشروط في الخلايا واستخدام مراجع الخلايا ، أو يمكنك استخدام مجموعة من عوامل التشغيل ومراجع الخلايا.

استخدم شروط TEXT في وظائف Excel

# 1 عد الخلايا عندما يكون الشرط مساويا للنص المحدد

لحساب عدد الخلايا التي تحتوي على تطابق تام مع نص محدد ، يمكننا ببساطة استخدام هذا النص كشرط.على سبيل المثال ، في مجموعة البيانات (الموضحة أدناه) ، إذا أردت حساب جميع الخلايا المسماة Joe ، فيمكنني استخدام الصيغة التالية:

= COUNTIF ($ B $ 2: $ B $ 11، "Joe")

نظرًا لأن هذه سلسلة نصية ، فأنا بحاجة إلى وضع شرط النص بين علامتي اقتباس.

يمكنك أيضًا تضمين شرط في خلية ثم استخدام مرجع الخلية هذا (كما هو موضح أدناه):

= COUNTIF (مليار مليار دولار أمريكي: 2 مليار دولار أمريكي ، E11)

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

# 2 عد الخلايا عندما لا يساوي الشرط النص المحدد

على غرار ما رأيناه في المثال أعلاه ، يمكنك أيضًا حساب الخلايا التي لا تحتوي على نص محدد.للقيام بذلك ، نحتاج إلى استخدام عامل التشغيل لا يساوي (<>).

بافتراض أنك تريد حساب جميع الخلايا التي لا تحتوي على اسم JOE ، فإليك صيغة يمكنها القيام بذلك:

= COUNTIF ($ B $ 2: $ B $ 11، ”<> Joe”)

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

= COUNTIF (مليار مليار دولار أمريكي: 2 مليار دولار أمريكي ، E11)

يمكن أن يكون هذا هو الحال أيضًا عندما تريد أن يكون الشرط في خلية ولكن لا تريد استخدامه مع عامل التشغيل.على سبيل المثال ، قد ترغب في تسمية الخلية D3 باسم Joe بدلاً من <> Joe.

في هذه الحالة ، تحتاج إلى إنشاء معلمة شرطية ، وهي مزيج من عامل التشغيل ومرجع الخلية (انظر الصورة أدناه):

= COUNTIF ($ B $ 2: $ B $ 11، ”<>” & E3)

عند دمج عوامل التشغيل ومراجع الخلايا ، يتم دائمًا إحاطة عوامل التشغيل بعلامات اقتباس مزدوجة.عوامل التشغيل ومراجع الخلية متصلة بواسطة علامة العطف (&).

استخدام شروط DATE في Excel COUNTIF و COUNTIFS وظائف

يقوم Excel بتخزين التواريخ والأوقات كأرقام.لذا يمكننا استخدامه كرقم.

# 1 عد الخلايا عندما يكون الشرط مساويا للتاريخ المحدد

للحصول على عدد الخلايا التي تحتوي على تاريخ محدد ، سنستخدم عامل التشغيل يساوي (=) جنبًا إلى جنب مع التاريخ.

للعمل مع التواريخ ، أوصي باستخدام وظيفة DATE ، لأنها تقضي على أي فرصة لخطأ قيمة التاريخ.لذلك ، على سبيل المثال ، إذا كنت أرغب في استخدام تاريخ 2015 سبتمبر 9 ، فيمكنني استخدام وظيفة DATE مثل هذا:

= DATE (2015,9,1،XNUMX،XNUMX)

على الرغم من الاختلافات الإقليمية ، سترجع هذه الصيغة نفس التاريخ.على سبيل المثال ، سيكون 01/09 هو 2015 سبتمبر 2015 بموجب صيغة التاريخ الأمريكية ، و 9 يناير 1 بموجب صيغة تاريخ المملكة المتحدة.ومع ذلك ، ستُرجع هذه الصيغة دائمًا 2105 سبتمبر 9.

إليك معادلة حساب عدد الخلايا التي تحتوي على تاريخ 02-09-2015:

= COUNTIF (دولار أسترالي 2: 11 دولارًا أستراليًا ، DATE (2015,9,2،XNUMX،XNUMX))

# 2 عد الخلايا عندما تكون الحالة قبل التاريخ المحدد أو بعده

لحساب الخلايا التي تحتوي على تواريخ قبل أو بعد تاريخ محدد ، يمكننا استخدام عوامل أقل / أكبر من.

على سبيل المثال ، إذا أردت حساب جميع الخلايا التي تحتوي على تواريخ بعد 2015 سبتمبر 9 ، فيمكنني استخدام الصيغة التالية:

= COUNTIF ($ A $ 2: $ A $ 11، ”>” & DATE (2015,9,2،XNUMX،XNUMX))

وبالمثل ، يمكنك أيضًا حساب عدد الخلايا قبل تاريخ محدد.إذا كنت تريد تضمين التواريخ في الجرد ، فاستخدم عامل التشغيل "يساوي" جنبًا إلى جنب مع عوامل التشغيل "أكبر من / أقل من".

يمكنك أيضًا استخدام مراجع الخلايا التي تحتوي على تواريخ.في هذه الحالة ، تحتاج إلى استخدام علامة العطف (&) لدمج العامل (ضمن علامتي اقتباس مزدوجتين) مع التاريخ.

انظر المثال أدناه:

= COUNTIF (دولار أسترالي 2: 11 دولارًا أمريكيًا ">" & F3)

# 3 عد الخلايا بمعايير متعددة - بين تاريخين

للحصول على عدد القيم بين قيمتين ، نحتاج إلى استخدام شروط متعددة في دالة COUNTIF.

يمكننا القيام بذلك بطريقتين - وظيفة COUNTIFS واحدة أو وظيفتين COUNTIF.

الطريقة الأولى: استخدم الدالة COUNTIFS

يمكن أن تأخذ الدالة COUNTIFS شروطًا متعددة كوسيطات وتعداد الخلايا فقط إذا كانت جميع الشروط صحيحة.لحساب عدد الخلايا ذات القيم بين تاريخين محددين ، مثل 9 سبتمبر و 2 سبتمبر ، يمكننا استخدام وظيفة COUNTIFS التالية:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

لا تحسب الصيغة أعلاه الخلايا التي تحتوي على التاريخ المحدد.إذا كنت تريد أيضًا تضمين هذه التواريخ ، فاستخدم أكبر من أو يساوي (> =) وأقل من أو يساوي (<=).ها هي الصيغة:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

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

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

الطريقة 2: استخدم دالة COUNTIF

إذا كان لديك شروط متعددة ، يمكنك استخدام دالة COUNTIFS أو إنشاء مجموعة من وظيفتين COUNTIF.الصيغة التالية ستفي بالغرض أيضًا:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

في الصيغة أعلاه ، نجد أولاً عدد الخلايا التي يكون تاريخها بعد 9 سبتمبر ، ثم نطرح عدد الخلايا التي يكون تاريخها بعد 2 سبتمبر.سيعطي هذا نتيجة 9 (أي عدد الخلايا ذات التواريخ بعد 7 سبتمبر وفي أو قبل 7 سبتمبر).

إذا كنت لا تريد أن تحسب الصيغة كلاً من 9 و 2 سبتمبر ، فاستخدم الصيغة التالية بدلاً من ذلك:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

إذا كنت تريد استبعاد تاريخين من الحساب ، فاستخدم الصيغة التالية:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

بالإضافة إلى ذلك ، يمكنك تعيين التواريخ القياسية في الخلايا واستخدام مراجع الخلايا (والعوامل في علامات الاقتباس المزدوجة مع علامة العطف).

استخدام أحرف البدل في شروط الدالتين COUNTIF و COUNTIFS

هناك ثلاثة أحرف البدل في Excel:

1. * (النجمة) - يمثل أي عدد من الأحرف.على سبيل المثال ، قد يعني ex * excel ، excels ، مثال ، خبير ، إلخ.
2. ? (علامة استفهام) - يمثل حرفًا واحدًا.على سبيل المثال ، قد يعني ترومب ترمب أو ترامب.
3. ~ (تلدة) - يُستخدم لتحديد أحرف البدل (~ ، * ،؟) في النص.

يمكنك استخدام الدالة COUNTIF مع أحرف البدل لحساب عدد الخلايا عند فشل وظائف العد المضمنة الأخرى.على سبيل المثال ، افترض أن لديك مجموعة بيانات تبدو كالتالي:

لنأخذ الآن بعض الأمثلة:

# 1 عد الخلايا التي تحتوي على نص

لحساب عدد الخلايا التي تحتوي على نص ، يمكننا استخدام حرف البدل * (علامة النجمة).نظرًا لأن العلامات النجمية تمثل أي عدد من الأحرف ، فسيتم عد جميع الخلايا التي تحتوي على أي نص.ها هي الصيغة:

= COUNTIFS ($ C $ 2: $ C $ 11، "*")

ملاحظة: تتجاهل الصيغة أعلاه الخلايا التي تحتوي على أرقام وخلايا فارغة وقيم منطقية ، ولكنها تحسب الخلايا التي تحتوي على فاصلات عليا (وبالتالي تظهر فارغة) أو التي ربما تم تضمينها كجزء من الصيغة.

فيما يلي برنامج تعليمي مفصل حول التعامل مع الحالات ذات السلاسل الفارغة أو الفواصل العليا.

# 2 عد الخلايا غير الفارغة

إذا كنت تفكر في استخدام وظيفة COUNTA ، فكر مرة أخرى.

جربه ، قد يخيب ظنك.ستعد COUNTA أيضًا الخلايا التي تحتوي على سلاسل فارغة (عادةً ما يتم إرجاعها بواسطة الصيغ كـ = "" ، أو عندما يقوم الأشخاص فقط بإدخال الفاصلات العليا في الخلايا).يبدو أن الخلايا التي تحتوي على سلاسل فارغة فارغة ولكنها ليست فارغة ، لذلك يتم حسابها بواسطة الدالة COUNTA.

ستعد COUNTA أيضًا الخلايا التي تحتوي على سلاسل فارغة (عادةً ما يتم إرجاعها بواسطة الصيغ كـ = "" ، أو عندما يقوم الأشخاص فقط بإدخال الفاصلات العليا في الخلايا).يبدو أن الخلايا التي تحتوي على سلاسل فارغة فارغة ولكنها ليست فارغة ، لذلك يتم حسابها بواسطة الدالة COUNTA.

لذلك إذا كنت تستخدم الصيغة = COUNTA (A1: A11) ، تقوم بإرجاع 11 عندما يجب أن تُرجع 10.

هنا الإصلاح:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

دعونا نفهم هذه الصيغة بتفصيلها:

# 3 عد الخلايا التي تحتوي على نص معين

لنفترض أننا نريد حساب جميع الخلايا التي يبدأ فيها اسم مندوب المبيعات بـ J.يمكن تحقيق ذلك بسهولة باستخدام أحرف البدل في دالة COUNTIF.ها هي الصيغة:

= COUNTIFS ($ C $ 2: $ C $ 11، "J *") 

يحدد الشرط J * أن النص في الخلية يجب أن يبدأ بـ J ويمكن أن يحتوي على أي عدد من الأحرف.

إذا كنت تريد حساب عدد الخلايا التي تحتوي على أحرف في أي مكان في النص ، فقم بإحاطة هذه الخلايا بعلامة النجمة.على سبيل المثال ، إذا كنت تريد حساب عدد الخلايا التي تحتوي على الحرف "أ" ، فاستخدم * أ * كشرط.

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