Note: The other languages of the website are Google-translated. Back to English
English English

Countifs مع منطق OR لمعايير متعددة في Excel

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


عد الخلايا بشروط "أو" في Excel

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

عد الخلايا بشروط "أو" باستخدام الدالتين SUM و COUNTIFS

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

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • range: يحتوي نطاق البيانات على المعايير التي تحسب منها الخلايا ؛
  • criterion1, criterion2, criterion3…: الشروط التي تريد عد الخلايا بناءً عليها.

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

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


شرح الصيغة:

= SUM (COUNTIFS (B2: B13، {"Pencil"، "Ruler"}))

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

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

=SUM(COUNTIF(B2:B13,D2:D3))


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

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

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • range: يحتوي نطاق البيانات على المعايير التي تحسب منها الخلايا ؛
  • criterion1, criterion2, criterion3…: الشروط التي تريد عد الخلايا بناءً عليها.

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

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


شرح الصيغة:

= 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؛ XNUMX ، XNUMX؛ XNUMX،XNUMX؛ XNUMX،XNUMX؛ XNUMX،XNUMX؛ XNUMX،XNUMX؛ XNUMX،XNUMX}.
  • 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" والمبلغ إما <100 أو> 200.

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

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

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


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

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

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

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

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


شرح الصيغة:

=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 بثابت الصفيف المقابل. إذا تم العثور على التطابق ، فإنه يُرجع موضعًا نسبيًا للقيمة في المصفوفة ، وإلا يتم عرض قيمة خطأ. لذلك ، ستحصل على قائمة المصفوفة على النحو التالي: {1؛ # N / A؛ # N / A؛ 2؛ # N / A؛ 1؛ # N / A؛ 2؛ 1؛ # N / A؛ 2؛ # غير متاح.
  • 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؛ 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 إلى 1s و 0s كجزء من عملية الرياضيات مثل: 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.

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

  • مجموع:
  • ترجع الدالة Excel SUM مجموع القيم المتوفرة.
  • كونتيف:
  • دالة COUNTIF هي دالة إحصائية في Excel تُستخدم لحساب عدد الخلايا التي تفي بمعيار.
  • إنتاج:
  • يمكن استخدام الدالة SUMPRODUCT لمضاعفة عمودين أو أكثر من المصفوفات معًا ، ثم الحصول على مجموع المنتجات.
  • هو رقم:
  • ترجع الدالة Excel ISNUMBER القيمة TRUE عندما تحتوي الخلية على رقم ، وتعيد FALSE إذا لم تكن كذلك.
  • تطابق:
  • تبحث الدالة Microsoft Excel MATCH عن قيمة معينة في نطاق من الخلايا ، وتُرجع الموضع النسبي لهذه القيمة.

المزيد من المقالات:

  • عد القيم الرقمية الفريدة بناءً على المعايير
  • في ورقة عمل Excel ، قد تواجه مشكلة في حساب عدد القيم الرقمية الفريدة بناءً على شرط معين. على سبيل المثال ، كيف يمكنني حساب قيم Qty الفريدة للمنتج "T-shirt" من التقرير كما هو موضح أدناه؟ في هذه المقالة ، سأعرض بعض الصيغ لتحقيق هذه المهمة في Excel.
  • عد عدد الصفوف ذات معايير أو متعددة
  • لحساب عدد الصفوف ذات المعايير المتعددة في أعمدة مختلفة ، باستخدام منطق OR ، يمكن أن تساعدك الدالة SUMPRODUCT. على سبيل المثال ، لدي تقرير منتج كما هو موضح أدناه ، أريد حساب الصفوف التي يكون فيها المنتج "تي شيرت" أو اللون "أسود". كيف تتعامل مع هذه المهمة في Excel؟

أفضل أدوات إنتاجية المكتب

كوتولس ل إكسيل - يساعدك على التميز من بين الحشود

هل ترغب في إكمال عملك اليومي بسرعة وبشكل مثالي؟ يوفر Kutools for Excel 300 ميزة متقدمة قوية (الجمع بين المصنفات ، والجمع حسب اللون ، ومحتويات الخلية المنقسمة ، وتحويل التاريخ ، وما إلى ذلك ...) وتوفير 80 ٪ من الوقت لك

  • مصمم لسيناريوهات عمل 1500 ، يساعدك على حل 80٪ من مشاكل Excel.
  • قلل الآلاف من نقرات لوحة المفاتيح والماوس كل يوم ، وقم بإراحة عينيك ويديك المتعبة.
  • كن خبيرًا في برنامج Excel في 3 دقائق. لم تعد بحاجة إلى تذكر أي صيغ مؤلمة ورموز VBA.
  • نسخة تجريبية مجانية غير محدودة لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا. ترقية مجانية ودعم لمدة عامين.
شريط Excel (مع تثبيت Kutools for Excel)

علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)

  • ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
  • قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
  • يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
  • يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel) ، تمامًا مثل Chrome و Firefox و Internet Explorer الجديد.
لقطة شاشة لبرنامج Excel (مع تثبيت Office Tab)
فرز التعليقات حسب
التعليقات (0)
لا يوجد تقييم. كن أول من يقيم!
لا توجد تعليقات منشورة هنا حتى الآن
اترك تعليقاتك
النشر كضيف
×
قيم المنشور:
0   الشخصيات
المواقع المقترحة