#006 Брой на уникалните стойности в зона

В някой примери се налага да се преброят уникалните стойности в група клетки.  Формулата която се използва в случая е:

=SumProduct(1/CountIf(Зона,Зона)). Първия път когато срещнах тази формула ми се видя доста чудата и за това ще се опитам накратко да анализирам как действа с реален пример.

Пример: Да се намери броя на уникалните стойности в зоната A1:A8.

Зона клетки

Отговор: =SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)). Ако въведете тази формула ще видите верния отговор 5.  Както се вижда формулата работи и за текстови и за числови данни. Зоната не е задължително да се състои от една колона.

1. COUNTIF(A1:A8,A1:A8) - До сега най-вероятно сте използвали CountIF с единична стойност за втория параметър (условието за броенето). Оказва се, че и условието може да бъде зона. Всъщност в този случай функцията се "развива" според втория параметър  (само по втория!) и се получават 8(!) отделни функции...

• COUNTIF(A1:A8,A1) = COUNTIF(A1:A8,"aaa")=3 ("aaa" го има три пъти!)
• COUNTIF(A1:A8,A2) = COUNTIF(A1:A8,"111")=2 ("111" го има два пъти!)
• COUNTIF(A1:A8,A3) = COUNTIF(A1:A8,"bbb")=1 ("bbb" го има само един път!)
• и т.н.

2. В резултат на изпълнението на функцията се получава масив от осем елемента:
{3,2,1,3,1,2,1,3}
3. Върху този масив се прилага операцията 1/{}. Което дава следния резултатен масив:
{1/3, 1/2, 1, 1/3, 1, 1/2, 1, 1/3}. Всъщност това е вид "нормализиране" на стойностите които ги има повече от един път.
4. Върху приложения масив се използва функцията Sumproduct (поради дискутирания "дефект" на Sum при работа с масиви) за намиране сумата на елементите която е 5 (броя на уникалните стойности).

Ето така действа тази "магическа" функция:)