عد عدد الخلايا لا يساوي العديد من القيم في Excel
في Excel ، قد تحصل بسهولة على عدد الخلايا التي لا تساوي قيمة معينة باستخدام وظيفة COUNTIF ، ولكن هل سبق لك أن حاولت حساب الخلايا التي لا تساوي العديد من القيم؟ على سبيل المثال ، أرغب في الحصول على العدد الإجمالي للمنتجات في العمود A ولكن مع استبعاد العناصر المحددة في C4: C6 كما هو موضح أدناه لقطة الشاشة. في هذه المقالة ، سأقدم بعض الصيغ لحل هذه المهمة في Excel.
- عد عدد الخلايا لا يساوي العديد من القيم مع الصيغة 1
- عد عدد الخلايا لا يساوي العديد من القيم مع الصيغة 2
عد عدد الخلايا لا يساوي العديد من القيم مع الصيغة 1
لحساب عدد الخلايا التي لا تساوي العديد من القيم المحددة ، يمكنك استخدام مجموعة الدالات MATCH و ISNA و SUMPRODUCT ، وبناء الجملة العام هو:
= SUMPRODUCT ( -- ( ISNA ( MATCH ( range, exclude_values, 0)))
- range: قائمة الخلايا التي تريد عدها ؛
- exclude_values: القيم المطلوب تجاهلها من قائمة النطاق.
1. أدخل أو انسخ الصيغة أدناه في خلية فارغة حيث تريد إخراج النتيجة:
=SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))
2. ثم اضغط أدخل مفتاح للحصول على النتيجة الصحيحة ، انظر الصورة:
شرح الصيغة:
- MATCH (A2: A13، C2: C4,0،XNUMX): تُرجع دالة MATCH هذه موضع كل قيم متطابقة كرقم ، وتُرجع قيمة الخطأ # N / A في حالة عدم العثور عليها. هذا يعني أنه عندما تكون القيم الموجودة في A2: A13 مساوية للقيم الموجودة في C2: C4 ، فإنها ستُرجع رقمًا ، وإلا فإنها تُرجع خطأ #NA. النتيجة هنا هي: {1؛ # N / A؛ # N / A؛ 1؛ 3؛ 2؛ # N / A؛ # N / A ؛ 3 ؛ # N / A ؛ # N / A ؛ # N / A ؛}.
- ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): تفرض دالة ISNA هذه على قيم الخطأ لتصبح TRUE والأرقام لتصبح FASLE ، لذلك ستحصل على هذا: {FALSE؛ TRUE؛ TRUE؛ FALSE؛ FALSE؛ FALSE؛ TRUE؛ TRUE؛ FALSE؛ TRUE؛ TRUE؛ TRUE}.
- - (ISNA (MATCH (A2: A13، C2: C4,0،XNUMX)))) = - ({FALSE؛ TRUE؛ TRUE؛ FALSE؛ FALSE؛ FALSE؛ TRUE؛ TRUE؛ FALSE؛ TRUE؛ TRUE؛ TRUE}): يحول عامل التشغيل - السالب المزدوج جميع القيم الحقيقية إلى 1 ثانية والخطأ إلى 0 ثانية على النحو التالي: SUMPRODUCT (- (ISNA (MATCH (A2: A13، C2: C4,0،0)))) = SUMPRODUCT ({1؛ 1؛ 0؛ 0؛ 0؛ 1؛ 1؛ 0؛ 1؛ 1؛ 1؛ XNUMX؛}).
- ثم يتم استخدام الدالة SUMPRODUCT لجمع الأرقام في المصفوفة وستحصل على النتيجة: 7.
عد عدد الخلايا لا يساوي العديد من القيم مع الصيغة 2
لحل هذه المهمة في Excel ، يمكنك أيضًا تطبيق مجموعة وظائف COUNTA و SUMPRODUCT و COUNTIF ، وبناء الجملة العام هو:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude_values))
- range: قائمة الخلايا التي تريد عدها ؛
- exclude_values: القيم المطلوب تجاهلها من قائمة النطاق.
1. الرجاء إدخال الصيغة أدناه أو نسخها في خلية فارغة لوضع النتيجة:
=COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))
2. ثم اضغط أدخل مفتاح للحصول على النتيجة كما هو موضح في لقطة الشاشة التالية:
شرح الصيغة:
- COUNTA (A2: A13): تُرجع الدالة COUNTA هذه عدد جميع الخلايا غير الفارغة في النطاق A2: A13. وستحصل على النتيجة: 12.
- COUNTIF (A2: A13، C2: C4): تحسب وظيفة COUNTIF هذه الخلايا الموجودة في النطاق A2: A13 التي تساوي المعيار في C2: C4 ، لذلك ستحصل على هذه النتيجة: {2 ؛ 1 ؛ 2} مما يعني ظهور Apple مرتين ، وظهر Grape مرة واحدة وظهر Lemon مرتين.
- SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): تجمع هذه الدالة SUMPRODUCT كل العناصر في المصفوفة وترجع النتيجة النهائية: 5.
- COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 : يتم طرح النتيجة (5) للدالة SUMPRODUCT من عدد جميع الخلايا غير الفارغة (12) للحصول على النتيجة النهائية التي تحتاجها.
ملاحظة: كلا الصيغتين أعلاه ليستا حساسة لحالة الأحرف.
الوظيفة النسبية المستخدمة:
- MATCH:
- تبحث الدالة MATCH عن قيمة محددة في نطاق من الخلايا ، وتُرجع الموضع النسبي لهذه القيمة.
- إسنا:
- يتم استخدام دالة ISNA للتحقق مما إذا كانت الصيغة ترجع الخطأ # N / A.
- SUMPRODUCT:
- يمكن استخدام الدالة SUMPRODUCT لمضاعفة عمودين أو أكثر من المصفوفات معًا ، ثم الحصول على مجموع المنتجات.
- COUNTIF:
- دالة COUNTIF هي دالة إحصائية في Excel تُستخدم لحساب عدد الخلايا التي تفي بمعيار.
- COUNTA:
- ترجع الدالة COUNTA عدد الخلايا باستثناء الخلايا الفارغة.
المزيد من المقالات:
- عدد الخلايا يساوي إحدى القيم العديدة في Excel
- لنفترض أن لدي قائمة بالمنتجات في العمود A ، الآن ، أريد الحصول على العدد الإجمالي لمنتجات محددة من Apple و Grape و Lemon المدرجة في النطاق C4: C6 من العمود A كما هو موضح أدناه. عادةً ، في Excel ، لن تعمل وظائف COUNTIF و COUNTIFS البسيطة في هذا السيناريو. في هذه المقالة ، سأتحدث عن كيفية حل هذه الوظيفة بسرعة وسهولة مع الجمع بين وظائف SUMPRODUCT و COUNTIF.
- عد عدد الخلايا بين قيمتين / تواريخ في Excel
- هل سبق لك أن حاولت الحصول على أو حساب عدد الخلايا بين رقمين أو تواريخ معينة في Excel كما هو موضح أدناه؟ ستتحدث هذه المقالة عن بعض الصيغ المفيدة للتعامل معها.
- عد عدد الخلايا ليس بين رقمين معطى في Excel
- يعد حساب عدد الخلايا بين رقمين مهمة شائعة بالنسبة لنا في Excel ، ولكن في حالة معينة ، قد ترغب في حساب عدد الخلايا ليس بين رقمين معينين. على سبيل المثال ، لدي قائمة منتجات بالمبيعات من الاثنين إلى الأحد ، والآن ، أحتاج إلى الحصول على عدد الخلايا التي لا تقع بين الأرقام المنخفضة والعالية المحددة كما هو موضح أدناه. في هذه المقالة ، سأقدم بعض الصيغ للتعامل مع هذه المهمة في Excel.
أفضل أدوات إنتاجية المكتب
كوتولس ل إكسيل - يساعدك على التميز من بين الحشود
Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...
علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)
- ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
- قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
- يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
- يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.
