اكسل

عد القيم الفريدة بالمعايير

Count Unique Values With Criteria

صيغة Excel: عد القيم الفريدة بالمعاييرصيغة عامة
= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>0))
ملخص

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



 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,C6:C15=H6,'')))>0))

التي تُرجع 3 ، نظرًا لوجود ثلاثة أسماء فريدة في B6: B15 مرتبطة بمشروع Omega.

ملاحظة: تتطلب هذه الصيغة صيغ الصفيف الديناميكية ، متاح فقط في اكسل 365 . مع إصدار قديم من Excel ، يمكنك استخدام ملفات صيغ بديلة أكثر تعقيدًا .





تفسير

في الأساس ، تستخدم هذه الصيغة الدالة UNIQUE لاستخراج القيم الفريدة ، وتطبق الدالة FILTER المعايير.

اكسل # n / a إذا

العمل من الداخل الى الخارج وظيفة التصفية تُستخدم لتطبيق المعايير واستخراج الأسماء المرتبطة بمشروع 'أوميغا' فقط:



 
 FILTER (B6:B15,C6:C15=H6) // Omega names only

النتيجة من FILTER هي ملف مجموعة مصفوفة مثله:

 
{'Jim''Jim''Carl''Sue''Carl'}

بعد ذلك ، ملف وظيفة فريدة يستخدم لإزالة التكرارات:

 
 UNIQUE ({'Jim''Jim''Carl''Sue''Carl'})

مما ينتج عنه مصفوفة جديدة مثل هذا:

 
{'Jim''Carl''Sue'} // after UNIQUE

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

 
= SUM (--( LEN ({'Jim''Carl''Sue'})>0))

ال وظيفة LEN يحصل على طول كل عنصر في القائمة ، ويعيد مصفوفة أطوال:

 
 LEN ({'Jim''Carl''Sue'}) // returns {343}

بعد ذلك ، نتحقق مما إذا كانت الأطوال أكبر من الصفر:

 
 LEN ({343)>0 // returns {TRUETRUETRUE}

واستخدم ملف خطأ مزدوج لفرض قيم TRUE و FALSE على 1 و 0:

 
--({TRUETRUETRUE}) // returns {111}

أخيرًا ، نجمع النتائج بامتداد دالة SUM :

كيفية حماية مستندات Excel بكلمة مرور
 
= SUM ({111}) // returns 3

يتم تسليم هذه المصفوفة مباشرة إلى دالة COUNTA ، والتي تُرجع العدد النهائي:

 
= COUNTA ({'Jim''Carl''Sue'}) // returns 3

لاحظ أنه نظرًا لأننا نتحقق من طول كل عنصر يتم إرجاعه بواسطة UNIQUE ، يتم تجاهل الخلايا الفارغة أو الفارغة التي تفي بالمعايير. هذه الصيغة ديناميكية وستتم إعادة الحساب فورًا إذا تم تغيير بيانات المصدر.

عد فريد بمعايير متعددة

لحساب القيم الفريدة بناءً على معايير متعددة ، يمكن توسيع منطق 'التضمين' داخل عامل التصفية. على سبيل المثال ، لحساب الأسماء الفريدة لمشروع أوميغا في يونيو فقط ، استخدم:

 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,(C6:C15=H6)*(D6:D15='june'))))>0))

هذا مثال على استخدام المنطق البوليني لتطبيق أكثر من شرط. النهج هو وأوضح بمزيد من التفصيل هنا .

من يعرف كيف يرسم منطقة القيمة على مخطط / es؟

لمزيد من التفاصيل ، انظر هذا الفيديو التدريبي: كيفية التصفية بمعايير متعددة .

كونتا

من الممكن كتابة صيغة أبسط ترد على ملف دالة COUNTA . ومع ذلك ، هناك تحذير مهم وهو أن COUNTA ستعيد 1 عندما لا توجد قيم مطابقة. هذا لأن الدالة FILTER تُرجع خطأً عندما لا تتطابق أي بيانات مع المعايير ، وينتهي الأمر بحساب هذا الخطأ بواسطة دالة COUNTA. تبدو صيغة COUNTA الأساسية كما يلي:

 
= COUNTA ( UNIQUE ( FILTER (B6:B15,C6:C15=H6)))

مرة أخرى ، ستُرجع هذه الصيغة 1 في حالة عدم وجود بيانات مطابقة. سيتضمن أيضًا الخلايا الفارغة التي تفي بالمعايير. الصيغة القائمة على LEN و SUM هي خيار أفضل.

لا توجد مصفوفات ديناميكية

إذا كنت تستخدم إصدارًا قديمًا من Excel بدون دعم الصفيف الديناميكي ، فيمكنك استخدام ملف صيغة أكثر تعقيدًا . لمزيد من المناقشة العامة حول بدائل المصفوفة الديناميكية ، راجع: بدائل صيغ الصفيف الديناميكية .

صيغ الصفيف الديناميكية متوفرة في مكتب 365 فقط. المؤلف ديف برونز


^