عد القيم الرقمية الفريدة استنادًا إلى معايير في Excel

في ورقة عمل Excel، قد تواجه صعوبة في عد القيم الرقمية الفريدة استنادًا إلى شرط معيّن. على سبيل المثال، كيف يمكنني عد القيم الفريدة للكمية (Qty) الخاصة بالمنتج «T-shirt» من التقرير الموضح في لقطة الشاشة أدناه؟ في هذه المقالة، سأعرض لك بعض الصيغ الفعّالة لتحقيق هذه المهمة في Excel.

• عد القيم الرقمية الفريدة استنادًا إلى معايير في Excel 2019 و2016 والإصدارات السابقة
• عد القيم الرقمية الفريدة استنادًا إلى معايير في Excel 365

عد القيم الرقمية الفريدة استنادًا إلى معايير في Excel 2019 و2016 والإصدارات السابقة

في Excel 2019 والإصدارات السابقة، يمكنك دمج دوال SUM وFREQUENCY وIF لإنشاء صيغة لـ عدد القيم الفريدة في نطاق استنادًا إلى معايير، والصيغة العامة هي:

• criteria_range: نطاق الخلايا الذي سيتم مطابقته مع المعيار الذي حددته؛
• criteria: الشرط الذي تريد أن تقوم عدد القيم الفريدة في نطاق بناءً عليه؛
• range: نطاق الخلايا الذي يحتوي على القيم الفريدة التي تريد عدها.

يرجى تطبيق الصيغة أدناه في خلية فارغة، ثم اضغط على مفاتيحCtrl + Shift + Enterللحصول على النتيجة الصحيحة، انظر لقطة الشاشة:

شرح الصيغة:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))

• IF(A2:A12=E2,C2:C12): تُرجع دالة IF هذه القيمة من العمود C إذا كان المنتج في العمود A هو «T-shirt»، وتكون النتيجة مصفوفة كالتالي: {FALSE؛300؛500؛FALSE؛400؛FALSE؛300؛FALSE؛FALSE؛FALSE؛350}.
• FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): تُستخدم دالةFREQUENCYلعدّ كل قيمة رقمية في مصفوفة البيانات، وترجع النتيجة على الشكل التالي: {0؛2؛1؛1؛1؛0؛0؛0؛0؛0؛0؛0}.
• --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): يختبر ما إذا كانت كل قيمة في المصفوفة أكبر من 0، ليُنتج النتيجة التالية: {FALSE؛TRUE؛TRUE؛TRUE؛TRUE؛FALSE؛FALSE؛FALSE؛FALSE؛FALSE؛FALSE؛FALSE}. بعد ذلك، يحوّل الرمز السلبي المزدوج قيم TRUE وFALSE إلى 1 و0 على التوالي، مما يُعطي مصفوفة نهائية كالتالي: {0؛1؛1؛1؛1؛0؛0؛0؛0؛0؛0؛0}.
• SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): أخيرًا، استخدم دالةSUMلجمع هذه القيم والحصول على العدد الإجمالي: 4.

نصائح:

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

عد القيم الرقمية الفريدة استنادًا إلى معايير في Excel 365

في Excel 365، يمكن أن يساعدك الجمع بين دوال ROWS وUNIQUE وFILTER في عد القيم الرقمية الفريدة استنادًا إلى معايير، والصيغة العامة هي:

• range: نطاق الخلايا التي تحتوي على القيم الفريدة المراد عدها.
• criteria_range: نطاق الخلايا الذي سيتم مطابقته مع المعيار الذي حددته؛
• criteria: الشرط الذي تريد أن تقوم عدد القيم الفريدة في نطاق بناءً عليه؛

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

شرح الصيغة:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

• A2:A12=E2: يتحقّق هذا التعبير من وجود القيمة المدوَّنة في الخلية E2 ضمن النطاق A2:A12، وينتج عنه هذه النتيجة: {FALSE؛TRUE؛TRUE؛FALSE؛TRUE؛FALSE؛TRUE؛FALSE؛FALSE؛FALSE؛TRUE}.
• FILTER(C2:C12,A2:A12=E2): تُستخدم دالة FREQUENCY لعدّ كل قيمة رقمية في مصفوفة القائمة، وتُرجع النتيجة على الشكل التالي: {0؛2؛1؛1؛1؛0؛0؛0؛0؛0؛0؛0}.
• UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): هنا، تُستخدم دالةUNIQUEلاستخراج القيم الفريدة من مصفوفة القائمة، لتحصل على هذه النتيجة: {300؛500؛400؛350}.
• ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): تُرجع دالةROWSعدد الصفوف استنادًا إلى نطاق الخلايا أو المصفوفة هو 4.

نصائح:

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

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

الدالة ذات الصلة المستخدمة:

مقالات أخرى: