حساب القيم الفريدة بناءً على معايير في Excel

لحساب القيم الفريدة فقط بناءً على معيار محدد في عمود آخر، يمكنك تطبيق صيغة صفيف مستندة إلى وظائف SUM وFREQUENCY وMATCH وROW. يساعدك هذا الدليل خطوة بخطوة على التعامل مع أكثر الاستخدامات تعقيدًا للصيغة.

كيفية حساب القيم الفريدة بناءً على معايير في Excel؟

كما هو موضح في جدول المنتجات أدناه، هناك بعض المنتجات المكررة التي تم بيعها من نفس المتجر في تواريخ مختلفة. الآن، أريد الحصول على عدد فريد للمنتجات التي تم بيعها من المتجر A، يمكنك تطبيق الصيغة التالية.

الصيغ العامة

{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}

المعايير

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

كيفية استخدام هذه الصيغ؟

1. حدد خلية فارغة لوضع النتيجة.

2. أدخل الصيغة التالية فيها ثم اضغط على مفاتيح Ctrl + Shift + Enter معًا للحصول على النتيجة.

=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))

ملاحظات: في هذه الصيغة، E3:E16 هو النطاق الذي يحتوي على القيمة التي يتم مقارنتها بالمعايير، H3 يحتوي على المعايير، D3:D16 هو النطاق الذي يحتوي على القيم الفريدة التي تريد حسابها، وD3 هي الخلية الأولى من D3:D16. يمكنك تغييرها حسب الحاجة.

كيف تعمل هذه الصيغة؟

{=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))}

• IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)):

• IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;},{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): الآن، لكل قيمة TRUE في المصفوفة 1، سنحصل على الموقع المقابل في المصفوفة 2، ولـ FALSE، سنحصل على FALSE. هنا ستحصل على مصفوفة جديدة مثل هذه {1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE}.
• ROW(D3:D16)-ROW(D3)+1: هنا تقوم وظيفة ROW بإرجاع رقم الصف المرجعي D3:D16 وD3، وستحصل على {3;4;5;6;7;8;9;10;11;12;13;14;15;16}-{3}+1.
• كل رقم في المصفوفة يطرح الرقم 3 ثم يضيف 1 وأخيرًا يعيد {1;2;3;4;5;6;7;8;9;10;11;12;13;14}.
• FREQUENCY({1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): هنا تقوم وظيفة FREQUENCY بإرجاع تكرار كل رقم في المصفوفة المعطاة: {2;1;2;0;0;0;0;0;0;0;0;0;0;0}.
• =SUM(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):

الوظائف ذات الصلة

الصيغ ذات الصلة