اكسل

عد القيم النصية الفريدة في نطاق

Count Unique Text Values Range

صيغة Excel: عد القيم النصية الفريدة في نطاقصيغة عامة
= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))
ملخص

لحساب القيم النصية الفريدة في نطاق ، يمكنك استخدام صيغة تستخدم عدة وظائف: تردد و تطابق و صف و إنتاج في المثال الموضح ، الصيغة في F5 هي:



 
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))

التي تُرجع 4 ، نظرًا لوجود 4 أسماء فريدة في B5: B14.

ملاحظة: هناك طريقة أخرى لحساب القيم الفريدة وهي استخدم وظيفة COUNTIF . هذه صيغة أبسط بكثير ، ولكن يمكن أن تعمل ببطء على مجموعات البيانات الكبيرة. مع اكسل 365 ، يمكنك استخدام ملف صيغة أبسط وأسرع مرتكز على فريدة من نوعها .





تفسير

هذه الصيغة أكثر تعقيدًا من الصيغة المماثلة التي تستخدم FREQUENCY لـ حساب القيم الرقمية الفريدة لأن FREQUENCY لا يعمل مع القيم غير الرقمية. نتيجة لذلك ، يقوم جزء كبير من الصيغة ببساطة بتحويل البيانات غير الرقمية إلى بيانات رقمية يمكن لـ FREQUENCY معالجتها.

من خلال العمل من الداخل إلى الخارج ، تُستخدم وظيفة MATCH للحصول على موضع كل عنصر يظهر في البيانات:



 
 MATCH (B5:B14,B5:B14,0)

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

 
{1114466699}

لأن MATCH ترجع دائمًا موضع أول تطابق ، القيم التي تظهر أكثر من مرة في البيانات ترجع نفس الموضع. على سبيل المثال ، نظرًا لأن 'Jim' يظهر 3 مرات في القائمة ، فإنه يظهر في هذه المصفوفة 3 مرات كرقم 1.

يتم تغذية هذا المصفوفة في FREQUENCY مثل ملف data_array جدال.ال سلال_أري الحجة مبنية من هذا الجزء من الصيغة:

كيفية القيام بوظيفة الجمع في Excel
 
 ROW (B5:B14)- ROW (B5)+1)

الذي يبني أ قائمة متسلسلة من الأرقام لكل قيمة في البيانات:

 
{12345678910}

في هذه المرحلة ، يتم تكوين FREQUENCY على النحو التالي:

 
 FREQUENCY ({1114466699},{12345678910})

تُرجع الدالة FREQUENCY صفيفًا من الأرقام التي تشير إلى عدد لكل رقم في صفيف البيانات ، منظمًا بواسطة bin. عندما يتم حساب رقم بالفعل ، سترجع FREQUENCY صفرًا. هذه ميزة أساسية في تشغيل هذه الصيغة. النتيجة من FREQUENCY هي مصفوفة مثل هذا:

كيفية الجمع بين الجمل في Excel
 
{30020300200} // output from FREQUENCY

ملاحظة: تقوم FREQUENCY دائمًا بإرجاع مصفوفة تحتوي على عنصر واحد أكثر من سلال_أري .

يمكننا الآن إعادة كتابة الصيغة على النحو التالي:

 
= SUMPRODUCT (--({30020300200}>0))

بعد ذلك ، نتحقق من القيم الأكبر من الصفر (> 0) ، والتي تحول الأرقام إلى TRUE أو FALSE ، ثم استخدم سالب مزدوج (-) لتحويل قيم TRUE و FALSE إلى 1s و 0s. الآن لدينا:

 
= SUMPRODUCT ({10010100100})

أخيرًا ، يقوم SUMPRODUCT ببساطة بإضافة الأرقام وإرجاع الإجمالي ، والذي يكون في هذه الحالة 4.

التعامل مع الخلايا الفارغة

ستؤدي الخلايا الفارغة في النطاق إلى إرجاع الصيغة للخطأ # N / A. للتعامل مع الخلايا الفارغة ، يمكنك استخدام صيغة صفيف أكثر تعقيدًا تستخدم الدالة IF لتصفية القيم الفارغة:

 
{= SUM ( IF ( FREQUENCY ( IF (data'',  MATCH (data,data,0)), ROW (data)- ROW (data.firstcell)+1),1))}

ملاحظة: إضافة IF يجعل هذا في ملف صيغة الصفيف يتطلب دخول التحكم في التحول.

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

من عند مايك جيفين كتاب ممتاز في صيغ الصفيف ، السيطرة ، التحول ، أدخل.

طرق أخرى لحساب القيم الفريدة

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

إلى جدول محوري هي أيضًا طريقة ممتازة لحساب القيم الفريدة.

المؤلف ديف برونز


^