Általános képlet
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Összegzés
A tartomány egyedi szövegértékeinek és feltételekkel történő számlálásához használhatja a FREQUENCY és a MATCH függvényeken alapuló tömbképletet. A bemutatott példában a G6 képlete a következő:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
ami 3-at ad vissza, mivel három különböző ember dolgozott az Omega projekten.
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Magyarázat
Ez egy összetett képlet, amely a FREQUENCY használatával számolja a MATCH függvényből származtatott numerikus értékeket. Belülről kifelé haladva a MATCH funkcióval megkapjuk az adatokban megjelenő értékek helyzetét:
MATCH(B5:B11,B5:B11,0)
A MATCH eredménye egy ilyen tömb:
(1;1;3;1;1;6;7)
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" négyszer jelenik meg a listában, ebben a tömbben négyszer jelenik meg 1-es számként.
A MATCH függvényen kívül az IF függvényt alkalmazzák a kritériumok alkalmazására, amely ebben az esetben magában foglalja annak tesztelését, hogy a projekt "omega"-e (a G5 cellából):
IF(C5:C11=G5 // filter on "omega"
Az IF funkció szűrőként működik, csak akkor engedi át a MATCH értékeit, ha "omega" -hoz vannak társítva. Az eredmény egy ilyen tömb:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
A szűrt tömb közvetlenül a FREQUENCY függvénybe kerül, mint data_array argumentum. Ezután a ROW függvény segítségével sorozatos számlistát készítünk az adatok minden értékéhez:
ROW(B3:B12)-ROW(B3)+1
Ez egy ilyen tömböt hoz létre:
(1;2;3;4;5;6;7;8;9;10)
amely a SZŰRŐ bins_array argumentumává válik . Ezen a ponton:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
A FREQUENCY olyan tömböt ad vissza, amely az adat tömbben lévő értékek számát jelöli, bin szerint rendezve. Amikor egy számot már megszámláltak, a FREQUENCY nulla értéket ad vissza. A FREQUENCY eredménye egy ilyen tömb:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Megjegyzés: A FREQUENCY mindig egy tömböt ad vissza, amelyben egynél több elem van, mint a bins_array .
Ezen a ponton így írhatjuk át a képletet:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Ellenőrizzük, hogy vannak-e nullánál nagyobb értékek, amelyek átalakítják a számokat IGAZ vagy HAMIS:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Ezután kettős negatív segítségével kényszerítjük a logikai értékeket 1-re és 0-ra:
=SUM((1;0;0;0;0;1;1;0))
Végül a SUM függvény 3-at ad eredményül.
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni.
Üres cellák kezelése a tartományban
Ha a tartomány bármely cellája üres, akkor ki kell igazítania a képletet, hogy megakadályozza az üres cellák átadását a MATCH függvényben, ami hibát okoz. Ezt megteheti egy másik beágyazott IF függvény hozzáadásával, hogy ellenőrizze az üres cellákat:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Két kritériummal
Ha két kritériuma van, kibővítheti a képlet logikáját egy másik beágyazott IF hozzáadásával:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Ahol c1 = kritérium1, c2 = kritérium2 és vals = az értéktartomány.
Logikai logikával
A logikai logikával csökkentheti a beágyazott IF-ket:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Ez megkönnyíti a további feltételek hozzáadását és kezelését.
