انتقل إلى المحتوى الرئيسي

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 AI: إحداث ثورة في تحليل البيانات على أساس: التنفيذ الذكي   |  إنشاء التعليمات البرمجية  |  إنشاء صيغ مخصصة  |  تحليل البيانات وإنشاء الرسوم البيانية  |  استدعاء وظائف Kutools...
الميزات الشعبية: البحث عن التكرارات أو تمييزها أو تحديدها  |  حذف الصفوف الفارغة  |  دمج الأعمدة أو الخلايا دون فقدان البيانات  |  جولة بدون صيغة 
سوبر فيلوكوب: معايير متعددة  |  قيمة متعددة  |  عبر الأوراق المتعددة  |  بحث غامض
حال. قائمة منسدلة: قائمة منسدلة سهلة  |  القائمة المنسدلة التابعة  |  قائمة منسدلة متعددة التحديد
مدير العمود: إضافة عدد محدد من الأعمدة  |  نقل الأعمدة  |  تبديل حالة رؤية الأعمدة المخفية  |  قارن الأعمدة ب حدد نفس الخلايا وخلايا مختلفة 
الميزات المميزة: التركيز على الشبكة  |  عرض تصميم  |  شريط الفورمولا الكبير  |  مدير المصنفات والأوراق | مكتبة الموارد (النص السيارات)  |  منتقي التاريخ  |  اجمع أوراق العمل  |  تشفير/فك تشفير الخلايا  |  إرسال رسائل البريد الإلكتروني عن طريق القائمة  |  سوبر تصفية  |  مرشح خاص (تصفية غامق / مائل / يتوسطه خط ...) ...
أفضل 15 مجموعة أدوات:  12 نص الأدوات (إضافة نص, إزالة الأحرف ...)  |  +50 رسم الأنواع (مخطط جانت ...)  |  40+ عملي الصيغ (احسب العمر على أساس تاريخ الميلاد ...)  |  19 إدخال الأدوات (أدخل رمز الاستجابة السريعة, إدراج صورة من المسار ...)  |  12 تحويل الأدوات (أرقام إلى كلمات, نتيجة تحويل عملة ...)  |  7 دمج وتقسيم الأدوات (الجمع بين الصفوف المتقدمة, تقسيم خلايا إكسل ...)  |  ... و اكثر

Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...

الوصف

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

  • ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
  • قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
  • يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
  • يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations