Splošna formula
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Povzetek
Za štetje edinstvenih besedilnih vrednosti v obsegu lahko uporabite formulo, ki uporablja več funkcij: FREQUENCY, MATCH, ROW in SUMPRODUCT. V prikazanem primeru je formula v F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
ki vrne 4, saj so v B5 4 edinstvena imena: B14.
Opomba: Drug način za štetje unikatnih vrednosti je uporaba funkcije COUNTIF. To je veliko preprostejša formula, vendar lahko počasi deluje na velikih naborih podatkov. Z Excelom 365 lahko uporabite preprostejšo in hitrejšo formulo, ki temelji na UNIQUE.
Pojasnilo
Ta formula je bolj zapletena kot podobna formula, ki uporablja FREQUENCY za štetje enoličnih številskih vrednosti, ker FREQUENCY ne deluje z neštevilskimi vrednostmi. Kot rezultat, velik del formule preprosto pretvori nenumerične podatke v numerične podatke, ki jih FREQUENCY lahko obdeluje.
Funkcija MATCH, ki deluje od znotraj navzven, se uporablja za določanje položaja vsakega elementa, ki se pojavi v podatkih:
MATCH(B5:B14,B5:B14,0)
Rezultat iz MATCH je matrika, kot je ta:
(1;1;1;4;4;6;6;6;9;9)
Ker MATCH vedno vrne položaj prvega ujemanja, vrednosti, ki se v podatkih pojavijo večkrat, vrnejo isti položaj. Na primer, ker se "Jim" na seznamu pojavi trikrat, se v tem polju trikrat prikaže kot številka 1.
Ta matrika je podana v FREQUENCY kot argument data_array . Bins_array argument je izdelana iz tega dela formulo:
ROW(B5:B14)-ROW(B5)+1)
ki gradi zaporedni seznam števil za vsako vrednost v podatkih:
(1;2;3;4;5;6;7;8;9;10)
V tem trenutku je FREQUENCY nastavljen tako:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY vrne niz številk, ki kažejo štetje za vsako število v podatkovnem polju, ki ga organizira bin. Ko je število že prešteto, bo FREQUENCY vrnil ničlo. To je ključna značilnost delovanja te formule. Rezultat FREQUENCY je matrika, kot je ta:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Opomba: FREQUENCY vedno vrne matriko z enim elementom več kot bins_array .
Zdaj lahko formulo prepišemo tako:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Nato preverimo vrednosti, večje od nič (> 0), ki pretvorijo številke v TRUE ali FALSE, nato pa z dvojno negativom (-) pretvorimo TRUE in FALSE vrednosti v 1s in 0s. Zdaj imamo:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Končno SUMPRODUCT preprosto sešteje številke in vrne seštevek, ki je v tem primeru 4.
Ravnanje s praznimi celicami
Prazne celice v obsegu povzročijo, da formula vrne napako # N / A. Za obdelavo praznih celic lahko uporabite bolj zapleteno formulo matrike, ki uporablja funkcijo IF za filtriranje praznih vrednosti:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Opomba: dodajanje IF to pretvori v matrično formulo, ki zahteva control-shift-enter.
Za več informacij glejte to stran.
Drugi načini za štetje edinstvenih vrednosti
Če imate Excel 365, lahko s funkcijo UNIQUE štejete unikatne vrednosti s precej preprostejšo formulo.
Vrtilna tabela je tudi odličen način za štetje edinstvenih vrednosti.
