19. اكسل كيفية حساب عدد القيم الفريدة

---

الحل الأول

لحساب عدد القيم الفريدة في نطاق من الخلايا A3:A17 على سبيل المثال ،

1 - إذا كان النطاق لا يحتوي على خلايا فارغة ، استخدم الصيغة :

=SUMPRODUCT(1/COUNTIF(A3:A17,A3:A17))

2 - إذا كان النطاق يحتوي على خلايا فارغة ، استخدم الصيغة :

=SUMPRODUCT((A3:A17<>"")/COUNTIF(A3:A17,A3:A17&""))

شرح الصيغ

سنشرح هذه الصيغ تدريجيًا وهي مناسبة للتذكير ببعض دوال أكسل.

لنعتبر المقتطف التالي:

الصيغة في الخلية B3 هي:

=COUNTIF($A$3:$A$17,A3)

تحسب هذه الصيغة عدد تكرارات القيمة في A3 في النطاق A3:A17. تمت إضافة علامات "$" لأجل التعبئة التلقائية.

في C3 ، معكوس القيمة في B3 ، أي الصيغة

=1/B3

وفي الخلية C18 ، مجموع القيم الموجودة في العمود . :

=SUM(C3:C17)

القيمة التي تم الحصول عليها في الخلية C18 هي بالفعل عدد القيم الفريدة في العمود A ، لأنه يتم حساب كل قيمة فريدة بمقدار 1.

خذ على سبيل المثال قيمة موجودة 3 مرات ، سيكون لدينا في العمود C لكل تكرار :

0,3333333

بإضافة هذه القيمة 3 مرات ، سيكون لدينا 1.

إذن بهذه الطريقة ، قمنا بحساب عدد القيم الفريدة ، ولكن يمكننا القيام بذلك دون المرور بعمود وسيط. الصيغة كما هو موضح أعلاه:

=SUMPRODUCT(1/COUNTIF(A3:A17,A3:A17))

هنا أخذت الدالة COUNTIF نطاق من الخلايا كوسيطة ثانية. في هذه الحالة ، يتم إجراء المقارنة مع قيمة كل خلية في النطاق وإرجاع مصفوفة (أي جدول من القيم) عدد عناصرها يساوي عدد الخلايا في النطاق.

تعطينا الدالة SUMPRODUCT مع وسيطة واحدة مجموع عناصر المصفوفة المعطاة كوسيطة. و هي نفس القيم الموجودة في العمود C من المقتطف السابق.

من أجل مساعدتك على فهم أفضل ، أعرض عليك عناصر المصفوفة التي تم إرجاعها بواسطة الدالة COUNTIF ، بالإضافة إلى العناصر العكسية ، و ذلك باستخدام الدالة INDEX.

في الخلية C3 ، الصيغة هي :

=INDEX(COUNTIF($A$3:$A$17,$A$3:$A$17),B3)

في الخلية D3 ، الصيغة هي :

=INDEX(1/COUNTIF($A$3:$A$17,$A$3:$A$17),B3)

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

إذا أضفنا خلية فارغة إلى النطاق A3:A17 ، فحينئذٍ الصيغة

COUNTIF(A3:A17,A3:A17)

ترجع مصفوفة تحتوي على عنصر يساوي 0. و الصيغة

1/COUNTIF(A3:A17,A3:A17)

ترجع مصفوفة تحتوي على عنصر يساوي الخطأ #DIV/0! وسيكون المجموع الذي يتم إرجاعه بواسطة SUMPRODUCT أيضًا هو الخطأ #DIV/0!.

السبب هو أن المقارنة مع خلية فارغة في دالة COUNTIF تعطي FALSE. ولكن ، يمكننا حل هذه المشكلة عن طريق ضم القيمة المراد مقارنتها بالسلسلة الفارغة "" ، لأن مقارنة الخلية الفارغة بالسلسلة الفارغة تعطي القيمة TRUE.

لذلك فإن الصيغة التي يجب استخدامها هي: :

=SUMPRODUCT(1/COUNTIF(A3:A17,A3:A17&""))

بهذه الطريقة نتجنب الخطأ #DIV/0! ولكن ، سيتم أيضًا حساب الخلايا الفارغة بمقدار 1.

إذا لكي لا يتم احتساب الخلايا الفارغة ، فعلينا استخدام الصيغة :

=SUMPRODUCT((A3:A17<>"")/COUNTIF(A3:A17,A3:A17&""))

الحل الثاني

لحساب عدد القيم الرقمية الفريدة في نطاق A3: A17 ، استخدم الصيغة :

=SUM(IF(FREQUENCY(A3:A17,A3:A17)>0.1,1,0))

لحساب عدد القيم الفريدة نصوص أو قيم رقمية في النطاق A3:A17 بشرط ألا يحتوي هذا النطاق على أي خلايا فارغة ، استخدم الصيغة :

=SUM(IF(FREQUENCY(MATCH(A3:A17,A3:A17,0),MATCH(A3:A17,A3:A17,0))>0.1,1,0))

لحساب عدد القيم الفريدة نصوص أو قيم رقمية في النطاق A3:A17 ، استخدم الصيغة :

=SUM(IF(FREQUENCY(IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""),IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""))>0.1,1,0))

شرح الصيغ

تذكير بخصوص الدالة FREQUENCY :

تأخذ الدالة FREQUENCY وسيطتين وتقوم بإرجاع صفيف من العناصر. نأخذ المثال التالي:

الوسيطة الثانية تستخدم لتحديد مجالات. فيما يتعلق بالمثال الموضح أعلاه حيث الوسيطة الثانية هي النطاق B3:B6 ، فإن المجالات هي:

• القيم أقل من أو تساوي 1
• القيم الأكبر من 1 وأقل من أو تساوي 3
• القيم الأكبر من 3 وأقل من أو تساوي 5
• القيم الأكبر من 5 وأقل من أو تساوي 7
• قيم أكبر من 7

لكل واحد من هذه المجالات نجد عنصر من الصفيف الذي يتم إرجاعه بواسطة الدالة FREQUENCY وقيمته هي عدد قيم الوسيطة الأولى A2:A16 المحصورة في المجال المقابل.

على سبيل المثال ، العنصر الأول من المصفوفة التي تم إرجاعها هو عدد القيم الموجودة في النطاق A2:A16 التي تكون أقل من أو تساوي 1.

العنصر الأخير من المصفوفة التي تم إرجاعها هو عدد القيم الموجودة في النطاق A2:A16 والتي تكون أكبر من 7 ، أي 0.

في المثال السابق ، تم ترتيب القيم الواردة في الوسيطة الثانية. ماذا يحدث إذا قدمنا ​​نفس القيم بترتيب مختلف:

لاحظ أننا نحصل على نفس القيم ولكن بترتيب مختلف. أولاً ، عدد القيم بين 3 و 5 ، ثم عدد القيم بين 1 و 3.

سؤال آخر ، إذا كانت هناك قيمة مكررة في الوسيطة الثانية؟ في هذه الحالة ، تكون القيمة المقابلة للتكرار هي 0 :

أخيرًا ، إذا أعطيت الوسيطة الأولى والثانية نفس نطاق الخلايا B3:B17

• أمام القيمة 5 ، لدينا عدد تكرارات هذه القيمة.
• أمام القيمة 3 ، لدينا عدد تكرارات هذه القيمة.
• أمام القيمة 4 ، لدينا عدد تكرارات هذه القيمة.
• أمام تكرار القيمة 5 في السطر 6 ، نحصل على 0. وهكذا.

والنتيجة هي أن لدينا رقمًا مختلف عن 0 أمام التواجد الأول لكل رقم.

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

=SUM(IF(FREQUENCY(B3:B17,B3:B17)>=1,1,0))

كيفية حساب عدد القيم الرقمية الفريدة وكذلك النصوص

كما هو مذكور أعلاه ، فالصيغة هي :

=SUM(IF(FREQUENCY(MATCH(B3:B17,B3:B17,0),MATCH(B3:B17,B3:B17,0))>=1,1,0))

مقارنة بالصيغة السابقة ، لقد استبدلت فقط عنوان النطاق B3:B17 بـ :

MATCH(B3:B17,B3:B17,0)

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

الصيغة:

MATCH(B3:B17,B3:B17,0)

تُرجع مصفوفة تمثل عناصرها مواضع التكرارات الأولى لكل قيمة. يوضح المقتطف التالي عناصر هذه المصفوفة :

إذن قامت الدالة MATCH بإرجاع أرقام ستتم معالجتها بواسطة الدالة FREQUENCY كما هو موضح أعلاه.

إذا كان هناك قيمة فارغة

ترجع الدالة MATCH الخطأ #N/A إذا أخذت كوسيطة أولى خلية فارغة.

لذلك إذا كان النطاق يحتوي على خلايا فارغة ، فسنحتاج إلى استبدال في الصيغة:

MATCH(B3:B17,B3:B17,0)

ب

IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),"")

الصيغة النهائية لحساب القيم الرقمية والنصية الفريدة هي :

=SUM(IF(FREQUENCY(IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""),IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""))>0.1,1,0))

---