وظيفة COUNTIFS، كيفية استخدام وظيفة عدد الخلايا وفقًا لشروط متعددة في Excel

تحتوي وظيفة COUNTIFS في Excel على العديد من التطبيقات العملية. إليك ما تحتاج إلى معرفته حول استخدام الدالة COUNTIFS في Excel .

يعد Microsoft Excel أحد برامج جداول البيانات الشهيرة في العالم ويحتوي على العديد من الميزات المفيدة. إن ظهور البرمجيات يجعل إدخال البيانات وحساب سلسلة من الأرقام أسهل من أي وقت مضى. وعند استخدام برنامج Excel، فأنت بالتأكيد بحاجة إلى معرفة وظيفة COUNTIFS .

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

• كيفية مقارنة البيانات في عمودين في Excel
• كيفية استخدام الدالة DSUM لحساب المبالغ ذات الشروط المعقدة في Excel
• كيفية استخدام الدالة SUMIF لحساب المبالغ الشرطية في Excel
• استخدام وظيفة VLOOKUP وأمثلة محددة

تعليمات لاستخدام الدالة COUNTIFS في Excel

بناء جملة الدالة COUNTIFS هو =COUNTIFS(criteria_range1, ومعايير1, [criteria_range2, ومعايير2],...) .

هناك:

• Criteria_range1 هو نطاق التحديد الأول الذي يحتاج إلى إحصائيات، وهي قيمة مطلوبة.
• Criteria1 هو شرط مطبق على معيار التحديد_النطاق1، ويمكن أن تكون القيمة المطلوبة خلية أو تعبيرًا أو نصًا.
• [criteria_range2, المعايير2] عبارة عن أزواج تحديد وشروط إضافية، مما يسمح بما يصل إلى 127 زوجًا من التحديد والشروط.

لاحظ عند استخدام الدالة COUNTIFS:

• يجب أن تحتوي التحديدات الإضافية على نفس عدد الصفوف والأعمدة الموجودة في منطقة Standard_range1، ويمكن أن تكون منفصلة عن بعضها البعض.
• إذا كان شرط التحديد يشير إلى خلية فارغة، فإن الدالة COUNTIFS تعتبر القيمة 0 تلقائيًا.
• يمكن استخدام الحروف . لاستبدال حرف معين، تحل العلامة * محل سلسلة كاملة من الأحرف. إذا كنت بحاجة للعثور على علامة؟ أو علامة * حقيقية، اكتب ~ أمام هذا الحرف.
• لاحظ أن الدالة COUNTIFS ليست حساسة لحالة الأحرف.
• بشكل عام، يجب وضع القيم النصية بين علامتي اقتباس، لكن الأرقام لا تفعل ذلك. ومع ذلك، عند تضمين عامل منطقي مع رقم، يجب وضع الرقم والعامل بين علامتي اقتباس كما هو موضح أدناه:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

ملحوظة : الشروط الإضافية يجب أن تتبع نفس القواعد.

• عند استخدام قيمة من خلية أخرى في شرط ما، يجب أن يكون مرجع الخلية متسلسلاً مع عامل التشغيل عند استخدامه. في المثال أدناه، ستقوم COUNTIFS بحساب القيم في A1:A10 التي تكون أقل من القيمة الموجودة في الخلية B1. لاحظ أن عامل التشغيل أقل من (وهو النص) موجود بين علامتي اقتباس، ولكن ليس هناك حاجة إلى مرجع الخلية:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

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

• يمكن لـ COUNTIFS حساب الخلايا الفارغة أو غير الفارغة. تحسب الصيغ الموجودة أدناه الخلايا الفارغة وغير الفارغة في النطاق A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• أسهل طريقة لاستخدام COUNTIFS مع التواريخ هي الإشارة إلى تاريخ صالح في خلية أخرى تحتوي على مرجع خلية. على سبيل المثال، لحساب الخلايا في A1:A10 التي تحتوي على تاريخ أكبر من التاريخ في B1، يمكنك استخدام صيغة مثل هذه:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

مثال لكيفية استخدام الدالة COUNTIFS في Excel

1. ورقة البيانات رقم 1

لدينا جدول البيانات أدناه لتقديم بعض الطلبات إلى الجدول.

مثال 1: حساب عدد الموظفين الذكور بـ 25 يوم عمل.

في الخلية التي نحتاج إلى إدخال النتيجة فيها، أدخل الصيغة =COUNTIFS(C2:C7،"Male"،D2:D7،25) واضغط على Enter.

هناك:

• C2:C7 هي منطقة إلزامية ذات عدد واحد مع جنس الموظف.
• الجنوب هو شرط منطقة العد 1.
• C2:C7 هي منطقة العد الثانية ليوم عمل الموظف.
• 25 هو شرط العد 2 المنطقة.

ونتيجة لذلك، أصبح لدينا موظفان، نام، ولديهما 25 يوم عمل.

مثال 2: احسب عدد الموظفين الذكور الذين يكون يوم إجازتهم 0.

في مربع إدخال الصيغة، أدخل =COUNTIFS(C2:C7،"Nam"،E2:E7,0) ثم اضغط على Enter.

تظهر النتائج موظفًا واحدًا لديه 0 أيام إجازة.

مثال 3: احسب عدد الموظفين الذكور الذين لديهم 25 يوم عمل ولديهم أيام إجازة أقل من أو تساوي يومين.

نقوم بإدخال الصيغة =COUNTIFS(C2:C7،"Male"،D2:D7،25،E2:E7،"<> ثم اضغط على Enter.

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

2. ورقة البيانات رقم 2

مثال 1: حساب إجمالي المنتجات التايوانية التي يقل سعر الوحدة فيها عن 200000 دونج فيتنامي.

في مربع إدخال النتيجة، نقوم بإدخال صيغة الحساب =COUNTIFS(B2:B7،"*Taiwan"،C2:C7،"<> ثم اضغط على Enter.

وكانت النتيجة منتجين تايوانيين مستوفيين للمتطلبات.

مثال 2: احسب إجمالي المنتجات التايوانية بأسعار وحدات تتراوح بين 100000 دونج فيتنامي و150000 دونج فيتنامي.

في مربع إدخال النتيجة، أدخل الصيغة =COUNTIFS(B2:B7،"*Taiwan"،C2:C7،">100000"،C2:C7،"<> ثم اضغط على Enter.

ونتيجة لذلك، سيكون هناك منتجان تايوانيان بسعر يتراوح بين 100000 دونج فيتنامي إلى 150000 دونج فيتنامي.

كيفية استخدام الدالة COUNTIFS مع SUM في Excel

تعد الدالة COUNTIFS في الأساس إصدارًا أكثر تعقيدًا من الدالة COUNTIFS. الميزة الرئيسية التي يتمتع بها COUNTIFS على COUNTIF هي أنه يدعم شروطًا ونطاقات متعددة.

ومع ذلك، يمكنك أيضًا تحديد نطاق وشرط واحد للدالة COUNTIFS، تمامًا كما فعلت مع الدالة COUNTIFS.

أحد الأشياء المهمة التي يجب أن تفهمها حول الدالة COUNTIFS قبل استخدامها هو أنها تقوم بما هو أكثر من مجرد جمع نتائج الخلايا التي تفي بمعايير كل نطاق من الخلايا.

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

يمكنك فهم وظيفة وظيفة COUNTIFS بشكل أفضل من خلال دراسة المثال أدناه.

مثال على وظيفة COUNITFS في Excel

بالإضافة إلى لون التفاح، يوجد أيضًا عمود يصف حجمها. الهدف النهائي في هذا المثال هو حساب عدد التفاحات الحمراء الكبيرة.

1. حدد الخلية التي تريد عرض النتائج فيها. (في هذا المثال، ستعرض المقالة العدد الكبير من التفاح الأحمر في الخلية E3).

2. انتقل إلى شريط الصيغة وأدخل الصيغة أدناه:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. باستخدام هذا الأمر، تتحقق الصيغة من الخلايا من A3 إلى A11 بحثًا عن الحالة "أحمر". يتم بعد ذلك اختبار الخلايا التي تجتاز هذا الاختبار مرة أخرى في النطاق من B3 إلى B11 للحالة "الكبيرة" .

4. اضغط على زر الإدخال.

5. الآن، سيقوم برنامج Excel بحساب عدد التفاحات الحمراء الكبيرة.

نتائج نموذجية لوظيفة COUNTIFS في Excel

لاحظ كيف تحسب الصيغة الخلايا التي تحتوي على السمات الحمراء والكبيرة . تأخذ الصيغة الخلايا من A3 إلى A11 وتختبرها للعثور على النتيجة الحمراء. يتم بعد ذلك اختبار الخلايا التي تجتاز هذا الشرط مرة أخرى مقابل الشرط التالي في النطاق الثاني، وهو في هذه الحالة الحالة الكبيرة.

في الختام، فإن النطاقات والشروط بعد النطاق والشرط الأول تضيق بشكل متزايد مرشح العد وليست مستقلة عن بعضها البعض. وبالتالي، فإن النتيجة النهائية للوصفة هي التفاح الأحمر والكبير. يمكنك حساب عدد التفاحات الحمراء أو الكبيرة من خلال دمج الدالة COUNTIF مع الدالة SUM .

1. حدد الخلية التي تريد عرض نتائج الصيغة فيها. (في هذا المثال، ستستخدم المقالة الخلية E6).

2. أدخل الصيغة أدناه:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

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

4. اضغط على زر الإدخال.

5. سيقوم Excel الآن بحساب وعرض عدد التفاحات الكبيرة أو الحمراء.

قم بدمج الدالة COUNTIF مع الدالة SUM

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

كيفية إصلاح الأخطاء الشائعة عند استخدام الدالة COUNTIFS في Excel

لا يعمل COUNTIFS عند حساب القيم النصية

عند حساب السلاسل النصية، يجب إدراجها داخل علامات الاقتباس. وإلا، فلن تتمكن الدالة COUNTIFS من حساب السلسلة النصية وستعيد القيمة 0. في صورة المثال التالية، لم يتم وضع السلسلة النصية بين علامات الاقتباس. ولذلك، ترجع هذه الصيغة 0.

لإصلاح هذا الخطأ، تحتاج فقط إلى إعادة كتابة الصيغة بالضبط: =COUNTIFS(E5:E12, "Car")

COUNTIFS لا يعمل لأن مرجع النطاق خاطئ

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

لنفترض في هذا المثال أننا نريد حساب مبيعات السيارات في أوستن. الصيغة التي تم إدخالها هي =COUNTIFS(E5:E12،"Car"،D5:D11،"Austin"). وبالنظر عن كثب إلى الصيغة، ستلاحظ أن نطاق المعيار الأول هنا هو E5:E12 ولكن نطاق المعيار الثاني هو D5:D11. عدد الخلايا في النطاق لهذا المعيار ليس هو نفسه.

الآن اضغط على Enter ، وستعيد الصيغة #VALUE! .

أعد كتابة الصيغة بشكل صحيح كما يلي: =COUNTIFS(E5:E12,"Car",D5:D12,"Austin")

لا تعمل COUNTIFS بسبب وجود خطأ في الصيغة

إذا لم يتم إدراج الصيغة بشكل صحيح، فلن تعمل الدالة COUNTIFS. عند استخدام أي عامل رياضي، مثل أكبر من (>) وأقل من (<) ويساوي = "" (=")" و "not = " يساوي = ""> <> ) ، يتم استخدام كل من المعامل والمعامل يجب إدخال المعيار الرقمي داخل نفس المعادلة. على سبيل المثال، إذا كنت تريد العثور على مبيعات أكبر من 100000 دولار، فعليك إدراج الصيغة التالية:

=COUNTIFS(F5:F12,">" 100000)

هنا، يتم إدخال العامل فقط داخل المعادلة، ولا يوجد معيار رقمي.

إذا قمت بالضغط على Enter، فسيظهر مربع حوار رسالة Microsoft Excel: "هناك مشكلة في هذه الصيغة".

من أجل حل المشكلة. اكتب الصيغة الدقيقة:

=COUNTIFS(F5:F12,">100000")

لقد قمنا الآن بإدخال كل من العامل والمعايير داخل الأقواس. الآن ستعيد هذه الصيغة الكمية المطلوبة.

اضغط دخول.

ونتيجة لذلك، سوف تتلقى مبلغ مبيعات أكبر من 100000 دولار أمريكي.

أتمنى لك النجاح!