Generička formula
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Sažetak
Za brojanje jedinstvenih tekstualnih vrijednosti u rasponu s kriterijima možete koristiti formulu niza koja se temelji na funkcijama FREQUENCY i MATCH. U prikazanom primjeru formula u G6 je:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
koji vraća 3, budući da su tri različite osobe radile na projektu Omega.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
Ovo je složena formula koja koristi FREQUENCY za brojanje numeričkih vrijednosti izvedenih s funkcijom MATCH. Funkcionirajući iznutra prema van, funkcija MATCH koristi se za dobivanje položaja svake vrijednosti koja se pojavljuje u podacima:
MATCH(B5:B11,B5:B11,0)
Rezultat iz MATCH-a je niz poput ovog:
(1;1;3;1;1;6;7)
Budući da MATCH uvijek vraća položaj prvog podudaranja, vrijednosti koje se pojavljuju više puta u podacima vraćaju isto mjesto. Na primjer, jer se "Jim" na popisu pojavljuje 4 puta, u ovom se polju 4 puta pojavljuje kao broj 1.
Izvan funkcije MATCH, funkcija IF koristi se za primjenu kriterija, što u ovom slučaju uključuje ispitivanje je li projekt "omega" (iz ćelije G5):
IF(C5:C11=G5 // filter on "omega"
IF funkcija djeluje poput filtra, dopuštajući da vrijednosti iz MATCH prođu samo ako su povezane s "omega". Rezultat je niz poput ovog:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtrirani niz isporučuje se izravno funkciji FREQUENCY kao argument data_array . Dalje, funkcija RED koristi se za izgradnju sekvencijalnog popisa brojeva za svaku vrijednost u podacima:
ROW(B3:B12)-ROW(B3)+1
Ovo stvara niz poput ovog:
(1;2;3;4;5;6;7;8;9;10)
koji postaje argument bins_array u FILTERU. U ovom trenutku imamo:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY vraća niz brojeva koji označavaju broj za svaku vrijednost u podatkovnom polju, organiziran od strane bin. Kada je broj već izbrojan, FREQUENCY će vratiti nulu. Rezultat iz FREQUENCY je niz poput ovog:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Napomena: FREQUENCY uvijek vraća niz s još jednom stavkom od polja bins_array .
U ovom trenutku formulu možemo prepisati ovako:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Provjeravamo ima li vrijednosti veće od nule, što brojeve pretvara u TRUE ili FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Zatim koristimo dvostruki negativ da prisilimo logičke vrijednosti na 1s i 0s:
=SUM((1;0;0;0;0;1;1;0))
Konačno, funkcija SUM vraća 3 kao konačni rezultat.
Napomena: ovo je formula niza i mora se unijeti pomoću Control + Shift + Enter.
Rukovanje praznim stanicama u rasponu
Ako su bilo koje stanice u rasponu prazne, morat ćete prilagoditi formulu kako biste spriječili da prazne stanice prelaze u funkciju MATCH, što će dovesti do pogreške. To možete učiniti dodavanjem druge ugniježđene IF funkcije za provjeru praznih ćelija:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
S dva kriterija
Ako imate dva kriterija, logiku formule možete proširiti dodavanjem drugog ugniježđenog IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Gdje su c1 = kriteriji1, c2 = kriteriji2 i vals = raspon vrijednosti.
S logičkom logikom
Pomoću logičke logike možete smanjiti ugniježđene IF-ove:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
To olakšava dodavanje i upravljanje dodatnim kriterijima.
