Általános képlet
=SUMPRODUCT(1/COUNTIF(data,data))
Összegzés
A cellatartományban lévő egyedi értékek számának megszámításához használjon a COUNTIF és a SUMPRODUCT függvényeken alapuló képletet. A példabemutatóban az F6 képlete a következő:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Magyarázat
Belülről kifelé haladva a COUNTIF a B5: B14 tartomány értékeire van konfigurálva, mindezen értékek felhasználásával mint feltétel:
COUNTIF(B5:B14,B5:B14)
Mivel 10 kritériumértéket adunk meg, egy 10 tömböt kapunk vissza, így:
(3;3;3;2;2;3;3;3;2;2)
Minden szám egy számot jelent - a "Jim" háromszor jelenik meg, a "Sue" kétszer jelenik meg stb.
Ez a tömb osztóként van beállítva, 1-gyel a számláló. Osztás után kapunk egy másik tömböt:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
A tartományban csak egyszer előforduló értékek 1-ként jelennek meg, de a többször előforduló értékek a többszörösnek megfelelő töredékértékekként jelennek meg. (azaz egy adat, amely négyszer jelenik meg az adatokban, 4 értéket eredményez = 0,25).
Végül a SUMPRODUCT függvény összesíti a tömb összes értékét és visszaadja az eredményt.
Üres cellák kezelése
Az üres vagy üres cellák kezelésének egyik módja a képlet beállítása az alábbiak szerint:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Egy üres karaktersorozat ("") összefűzésével megakadályozzuk, hogy a nullák a COUNTIF által létrehozott tömbbe kerüljenek, ha üres cellák vannak az adatokban. Ez azért fontos, mert az osztó nullája miatt a képlet # DIV / 0 hibát dob. Ez azért működik, mert ha egy üres karakterláncot ("") használ a kritériumokhoz, az üres cellákat fog számolni.
Bár a képlet ezen verziója nem dob # DIV / 0 hibát üres cellák esetén, üres cellákat is tartalmaz a számlálásban. Ha ki szeretné zárni az üres cellákat a számlálásból, használja:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Ennek eredményeként az üres cellák száma törlődik azzal, hogy a számláló nullává teszi a társított számlálást.
Lassú teljesítmény?
Ez egy klassz és elegáns képlet, de sokkal lassabban számol, mint azok a képletek, amelyek a FREQUENCY-t használják az egyedi értékek számlálásához. Nagyobb adatsorok esetén érdemes a FREQUENCY függvényen alapuló képletre váltani. Itt van egy képlet a numerikus értékekre és egy a szöveges értékekre.
