Üldine valem
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Kokkuvõte
Kriteeriumidega vahemikus olevate ainulaadsete tekstiväärtuste lugemiseks võite kasutada massiivivalemit, mis põhineb funktsioonidel Sagedus ja MATCH. Näidatud näites on valem G6-s järgmine:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
mis tagastab 3, kuna projekti Omega kallal töötasid kolm erinevat inimest.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selgitus
See on keeruline valem, mis kasutab funktsiooni MATCH tuletatud arvväärtuste loendamiseks FREQUENCY. Seestpoolt töötades kasutatakse funktsiooni MATCH iga andmetes kuvatud väärtuse positsiooni saamiseks:
MATCH(B5:B11,B5:B11,0)
MATCHi tulemus on selline massiiv:
(1;1;3;1;1;6;7)
Kuna MATCH tagastab alati esimese vaste positsiooni, tagastavad andmetes rohkem kui üks kord kuvatud väärtused sama positsiooni. Näiteks, kuna "Jim" ilmub loendis 4 korda, ilmub ta selles massiivis neli korda numbrina 1.
Väljaspool funktsiooni MATCH kasutatakse kriteeriumide rakendamiseks funktsiooni IF, mis sel juhul hõlmab testimist, kas projekt on "omega" (lahtrist G5):
IF(C5:C11=G5 // filter on "omega"
Funktsioon IF toimib nagu filter, võimaldades MATCH-i väärtustel läbida ainult siis, kui need on seotud oomega. Tulemuseks on selline massiiv:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtreeritud massiiv tarnitakse argumentidena data_array otse funktsioonile FREQUENCY . Järgmisena kasutatakse funktsiooni ROW järjestikuse arvude loendi loomiseks andmete iga väärtuse jaoks:
ROW(B3:B12)-ROW(B3)+1
See loob sellise massiivi:
(1;2;3;4;5;6;7;8;9;10)
millest saab FILTERis bin_array- argument. Sel hetkel on meil:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY tagastab arvude massiivi, mis näitab andmemassiivi iga väärtuse arvu, korraldatuna prügikasti järgi. Kui number on juba loendatud, tagastab FREQUENCY nulli. FREQUENCY tulemuseks on selline massiiv:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Märkus. FREQUENCY tagastab alati massiivi, millel on üks üksus rohkem kui bin-array .
Siinkohal võime valemi ümber kirjutada järgmiselt:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Kontrollime, kas väärtused on suuremad kui null, mis teisendab arvud tõeks või vääraks:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Seejärel kasutame topeltnegatiivi loogiliste väärtuste sundimiseks 1s ja 0s:
=SUM((1;0;0;0;0;1;1;0))
Lõpuks tagastab funktsioon SUM lõpptulemuseks 3.
Märkus: see on massiivivalem ja see tuleb sisestada, kasutades klahve Control + Tõst + Enter.
Vahemikus olevate tühjade lahtrite käitlemine
Kui mõni vahemik lahtrit on tühi, peate valemit kohandama, et vältida tühjade lahtrite edastamist funktsiooni MATCH, mis põhjustab vea. Seda saate teha, lisades tühjade lahtrite kontrollimiseks veel ühe pesastatud IF-funktsiooni:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Kahe kriteeriumiga
Kui teil on kaks kriteeriumi, saate valemi loogikat laiendada, lisades teise pesastatud IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Kus c1 = kriteerium1, c2 = kriteerium2 ja vals = väärtuste vahemik.
Boolean loogikaga
Boolean loogika abil saate sisestatud IF-sid vähendada:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
See hõlbustab lisakriteeriumide lisamist ja haldamist.
