Általános képlet
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Összegzés
Egy egyedi szövegértékek megszámolásához egy tartományban használhat egy képletet, amely több funkciót használ: FREKVENCIA, MATCH, ROW és SUMPRODUCT. A bemutatott példában az F5 képlete a következő:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
amely 4-et ad vissza, mivel a B5-ben 4 egyedi név található: B14.
Megjegyzés: Az egyedi értékek számlálásának másik módja a COUNTIF függvény használata. Ez egy sokkal egyszerűbb képlet, de nagy adathalmazokon lassan futtatható. Az Excel 365 segítségével egyszerűbb és gyorsabb, egyedülálló alapú képletet használhat.
Magyarázat
Ez a képlet bonyolultabb, mint egy hasonló képlet, amely a FREQUENCY használatával számolja az egyedi numerikus értékeket, mivel a FREQUENCY nem működik nem numerikus értékekkel. Ennek eredményeként a képlet nagy része egyszerűen átalakítja a nem numerikus adatokat olyan numerikus adatokká, amelyeket a FREQUENCY képes kezelni.
Kívülről kifelé haladva a MATCH funkcióval megkapjuk az adatokban megjelenő minden elem helyzetét:
MATCH(B5:B14,B5:B14,0)
A MATCH eredménye egy ilyen tömb:
(1;1;1;4;4;6;6;6;9;9)
Mivel a MATCH mindig az első egyezés pozícióját adja vissza, az adatokban többször megjelenő értékek ugyanazt a pozíciót adják vissza. Például, mivel a "Jim" háromszor jelenik meg a listán, ebben a tömbben háromszor jelenik meg 1-es számként.
Ezt a tömböt a FREQUENCY adathordozó argumentumként adjuk meg . A bins_array argumentum a képlet ezen részéből épül fel:
ROW(B5:B14)-ROW(B5)+1)
amely szekvenciális listát állít össze az adatok minden értékéhez:
(1;2;3;4;5;6;7;8;9;10)
Ezen a ponton a FREQUENCY a következőképpen van konfigurálva:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
A FREQUENCY olyan tömböt ad vissza, amely binárisan rendezve jelzi az adattömb minden egyes számának számát. Amikor egy számot már megszámláltak, a FREQUENCY nulla értéket ad vissza. Ez a képlet működésének egyik legfontosabb jellemzője. A FREQUENCY eredménye egy ilyen tömb:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Megjegyzés: A FREQUENCY mindig egy tömböt ad vissza, amelyben egynél több elem van, mint a bins_array .
Most így írhatjuk át a képletet:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Ezután ellenőrizzük a nullánál nagyobb értékeket (> 0), amelyek átalakítják a számokat IGAZ vagy HAMIS értékre, majd kettős negatív (-) értékkel konvertáljuk az IGAZ és HAMIS értékeket 1-re és 0-ra. Most van:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Végül a SUMPRODUCT egyszerűen összeadja a számokat, és visszaadja az összeget, ami ebben az esetben 4.
Üres cellák kezelése
A tartomány üres cellái a képlet # N / A hibát adnak vissza. Az üres cellák kezeléséhez bonyolultabb tömbképletet használhat, amely az IF függvényt használja az üres értékek kiszűrésére:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Megjegyzés: az IF hozzáadásával ez egy tömbképletté válik, amely megköveteli a control-shift-enter billentyűt.
További információkért lásd ezt az oldalt.
Az egyedi értékek számlálásának egyéb módjai
Ha rendelkezik Excel 365-tel, akkor az UNIQUE függvény segítségével sokkal egyszerűbb képlettel számolhatja az egyedi értékeket.
A pivot tábla az egyedi értékek számlálásának kiváló módja is.
