COUNTIFS باستخدام منطق OR لعدة معايير في Excel

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

• عد الخلايا باستخدام شروط OR في Excel
  • عد الخلايا باستخدام شرط OR بواسطة دالتي SUM وCOUNTIFS
  • عد الخلايا باستخدام شرط OR بواسطة دالة SUMPRODUCT
• عد الخلايا باستخدام عدة مجموعات من شروط OR في Excel
  • عد الخلايا باستخدام مجموعتين من شروط OR بواسطة دالتي SUM وCOUNTIFS
  • عد الخلايا باستخدام عدة مجموعات من شروط OR بواسطة دالة SUMPRODUCT

عد الخلايا باستخدام شروط OR في Excel

على سبيل المثال، لدي نطاق بيانات كما هو موضح في لقطة الشاشة أدناه، وأرغب الآن في عدّ عدد المنتجات التي تكون «Pencil» أو «Ruler». وسأعرض هنا صيغتين فعّالتين لأداء هذه المهمة في Excel.

عد الخلايا باستخدام شرط OR بواسطة دالتي SUM وCOUNTIFS

في Excel، لعد الخلايا باستخدام عدة شروط OR، يمكنك استخدام دالتي SUM وCOUNTIFS مع ثابت مصفوفة، والصيغة العامة هي:

• range: يحتوي نطاق البيانات على المعايير التي تريد العد منها؛
• criterion1, criterion2, criterion3…: الشروط التي تريد العد بناءً عليها.

لعد عدد المنتجات التي تكون «Pencil» أو «Ruler»، يُرجى نسخ الصيغة أدناه أو إدخالها في خلية فارغة، ثم الضغط على مفتاحEnterللحصول على النتيجة:

شرح الصيغة:

=SUM(COUNTIFS(B2:B13,{«Pencil»,«Ruler»}))

• {«Pencil»,«Ruler»}: أولًا، ضع جميع الشروط في ثابت مصفوفة بالشكل التالي: {«Pencil»،«Ruler»}، وافصل العناصر بفواصل.
• COUNTIFS(B2:B13,{«Pencil»,«Ruler»}): ستُرجع دالة COUNTIFS هذه عدد مرات ظهور كلٍّ من «Pencil» و«Ruler»، لتكون النتيجة كالتالي: {2،3}.
• SUM(COUNTIFS(B2:B13,{«Pencil»,«Ruler»}))=SUM({2,3}): أخيرًا، تقوم دالة SUM بجمع جميع العناصر في المصفوفة وتعيد النتيجة: 5.

نصائح: يمكنك أيضًا استخدام مراجع الخلايا للمعايير، يُرجى تطبيق صيغة المصفوفة أدناه، ثم الضغط على مفاتيحCtrl + Shift + Enterمعًا للحصول على النتيجة الصحيحة:

عد الخلايا باستخدام شرط OR بواسطة دالة SUMPRODUCT

إليك صيغةً أخرى تم إنشاؤها بواسطة دالة SUMPRODUCT، ويمكنها مساعدتك في عد الخلايا باستخدام منطق OR أيضًا. والصيغة العامة هي:

• range: يحتوي نطاق البيانات على المعايير التي تريد العد منها؛
• criterion1, criterion2, criterion3…: الشروط التي تريد العد استنادًا إليها.

يُرجى نسخ الصيغة التالية أو إدخالها في خلية فارغة، ثم الضغط على مفتاحEnterلإرجاع النتيجة:

شرح الصيغة:

=SUMPRODUCT(1*(B2:B13={«Pencil»,«Ruler»}))

• B2:B13={«Pencil»,«Ruler»}: تقارن هذه الصيغة كلًا من المعيارين «Pencil» و«Ruler» مع خلايا النطاق B2:B13. فإذا تحقّق أحد المعيارين، تُرجع القيمة TRUE، وإلا تُرجع FALSE، لتظهر النتيجة على النحو التالي: {TRUE،FALSE؛FALSE،FALSE؛FALSE،FALSE؛FALSE،TRUE؛FALSE،FALSE؛TRUE،FALSE؛FALSE،FALSE؛FALSE،TRUE؛FALSE،FALSE؛FALSE،FALSE؛FALSE،TRUE؛FALSE،FALSE}.
• 1*(B2:B13={«Pencil»,«Ruler»}): يحوّل الضرب القيم المنطقية — TRUE وFALSE — إلى 1 و0، فتحصل على نتيجة كالتالي: {1،0؛0،0؛0،0؛0،1؛0،0؛1،0؛0،0؛0،1؛0،0؛0،0؛0،1؛0،0}.
• SUMPRODUCT(1*(B2:B13={«Pencil»,«Ruler»}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): في النهاية، تقوم دالةSUMPRODUCTبجمع جميع الأرقام في المصفوفة، تحصل على النتيجة: 5.

عد الخلايا باستخدام عدة مجموعات من شروط OR في Excel

أحيانًا، قد تحتاج إلى عد الخلايا بناءً على مجموعتين أو أكثر من شروط «OR»، وفي هذه الحالة، يمكنك استخدام دالتي SUM وCOUNTIFS مع ثابت مصفوفة، أو دالة SUMPRODUCT مع دالتي ISNUMBER وMATCH.

عد الخلايا باستخدام مجموعتين من شروط OR بواسطة دالتي SUM وCOUNTIFS

للتعامل مع مجموعتين فقط من معايير OR، ما عليك سوى إضافة ثابت مصفوفة آخر إلى صيغة COUNTIFS.

على سبيل المثال، لدي نطاق البيانات كما هو موضح في لقطة الشاشة أدناه، وأريد الآن عد الأشخاص الذين طلبوا «Pencil» أو «Ruler» وكانت الكمية إما 200.

يُرجى إدخال الصيغة التالية أو نسخها في خلية، ثم الضغط على مفتاحEnterللحصول على النتيجة، انظر لقطة الشاشة:

: في الصيغة، يجب استخدام فاصلة منقوطة في ثابت المصفوفة الثاني لإنشاء مصفوفة عمودية.

عد الخلايا باستخدام عدة مجموعات من شروط OR بواسطة دالة SUMPRODUCT

تعمل الصيغة أعلاه فقط مع مجموعتين من معايير OR، ولكن إذا احتجتَ إلى العد وفقًا لمعايير إضافية، فستساعدك صيغة SUMPRODUCT المتقدمة التي تدمج دالتي ISNUMBER وMATCH.

بأخذ البيانات أدناه كمثال، لعد الأشخاص الذين طلبوا «Pencil» أو «Ruler» وكان الحالة إما «Delivered» أو «In transit» ووقّعها «Bob» أو «Eko»، يجب عليك تطبيق صيغة معقدة.

يُرجى نسخ الصيغة أدناه أو إدخالها في خلية فارغة، ثم الضغط على مفتاحEnterللحصول على الحساب، انظر لقطة الشاشة:

شرح الصيغة:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,«In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,«Eko»},0)))

ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0)):

• MATCH(B2:B13,{«Pencil»,«Ruler»},0): تُستخدم دالةMATCH هنا لمقارنة كل خلية في النطاقB2:B13 مع قيم مصفوفة الثوابت{«Pencil», «Ruler»}فعند العثور على تطابق، تُرجع الدالة الموقع النسبي للقيمة داخل المصفوفة؛ وإلا، تُظهر خطأً. ونتيجةً لذلك، ستحصل على مصفوفة نتائج كالتالي: {1؛#N/A؛#N/A؛2؛#N/A؛1؛#N/A؛2؛1؛#N/A؛2؛#N/A}.
• ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): تقوم دالةISNUMBER بتحويل الأرقام إلى القيمةTRUE، والقيم الخطأ إلىFALSEليصبح الناتج كالتالي: {TRUE؛ FALSE؛ FALSE؛ TRUE؛ FALSE؛ TRUE؛ FALSE؛ TRUE؛ TRUE؛ FALSE؛ TRUE؛ FALSE}.

يمكن تطبيق المنطق أعلاه أيضًا على التعبيرين الثاني والثالث من ISNUMBER.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,«Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,«In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,«Eko»},0))):

• بعد ذلك، تُضرب هذه المصفوفات الثلاث معًا داخل دالة SUMPRODUCT، التي تقوم تلقائيًا بتحويل قيم TRUE وFALSE إلى 1 و0 كجزء من العملية الحسابية، على النحو التالي: SUMPRODUCT({1؛0؛0؛1؛0؛1؛0؛1؛1؛0؛1؛0} * {1؛1؛0؛0؛1؛1؛0؛1؛1؛0؛1؛1} * {1؛0؛0؛0؛0؛0؛0؛1؛0؛0؛1؛0}) = SUMPRODUCT({1؛0؛0؛0؛0؛0؛0؛1؛0؛0؛1؛0}).
• في النهاية، تقوم دالة SUMPRODUCT بجمع جميع الأرقام في المصفوفة للحصول على النتيجة: 3.

الدالة النسبية المستخدمة:

مقالات إضافية: