Splošna formula
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Povzetek
Za štetje edinstvenih besedilnih vrednosti v obsegu z merili lahko uporabite matrično formulo, ki temelji na funkcijah FREQUENCY in MATCH. V prikazanem primeru je formula v G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
ki vrne 3, saj so pri projektu Omega delali trije različni ljudje.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
To je zapletena formula, ki uporablja FREQUENCY za štetje številskih vrednosti, ki so izpeljane s funkcijo MATCH. Funkcija MATCH deluje od znotraj navzven, da dobi položaj vsake vrednosti, ki se pojavi v podatkih:
MATCH(B5:B11,B5:B11,0)
Rezultat iz MATCH je matrika, kot je ta:
(1;1;3;1;1;6;7)
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 4-krat, se v tem polju 4-krat prikaže kot številka 1.
Zunaj funkcije MATCH se funkcija IF uporablja za uporabo meril, kar v tem primeru vključuje testiranje, ali je projekt "omega" (iz celice G5):
IF(C5:C11=G5 // filter on "omega"
Funkcija IF deluje kot filter in omogoča, da vrednosti iz MATCH prehajajo skozi le, če so povezane z "omega". Rezultat je matrika, kot je ta:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtrirano polje se dostavi neposredno v funkcijo FREQUENCY kot argument data_array . Nato se s funkcijo ROW ustvari zaporedni seznam števil za vsako vrednost v podatkih:
ROW(B3:B12)-ROW(B3)+1
To ustvari matriko, kot je ta:
(1;2;3;4;5;6;7;8;9;10)
ki postane argument bins_array v FILTER. Na tej točki imamo:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY vrne niz številk, ki kažejo štetje za vsako vrednost v podatkovni matriki, ki jo organizira bin. Ko je število že prešteto, bo FREQUENCY vrnil ničlo. Rezultat FREQUENCY je matrika, kot je ta:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Opomba: FREQUENCY vedno vrne matriko z enim elementom več kot bins_array .
V tem trenutku lahko formulo napišemo takole:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Preverimo, ali so vrednosti večje od nič, kar pretvori številke v TRUE ali FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Nato z dvojnim negativom prisilimo logične vrednosti na 1s in 0s:
=SUM((1;0;0;0;0;1;1;0))
Na koncu funkcija SUM vrne 3 kot končni rezultat.
Opomba: to je formula matrike in jo je treba vnesti s kombinacijo Control + Shift + Enter.
Ravnanje s praznimi celicami v območju
Če so katere koli celice v obsegu prazne, boste morali prilagoditi formulo, da preprečite, da se prazne celice prenesejo v funkcijo MATCH, kar bo povzročilo napako. To lahko storite z dodajanjem druge ugnezdene funkcije IF za preverjanje praznih celic:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Z dvema kriterijema
Če imate dva kriterija, lahko logiko formule razširite z dodajanjem drugega ugnezdenega IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Kjer je c1 = kriterij1, c2 = kriterij2 in vals = obseg vrednosti.
Z logično logiko
Z logično logiko lahko zmanjšate ugnezdene IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Tako je lažje dodajati in upravljati dodatna merila.
