Üldine valem
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Kokkuvõte
Vahemikus sobitamise kriteeriumide alumise n väärtuse kokkuvõtteks võite kasutada funktsiooni SUM sisse mähitud funktsiooni VÄIKE massiivi valemit. Üldises vormis valemiga (ülal), vahemik1 esindab lahtripiirkond võrreldes kriteeriumid , vahemik2 sisaldab arvväärtused, millest alumine väärtuste laaditakse ja N tähistab "nda".
Näites sisaldab aktiivne lahter järgmist valemit:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Kus värv on nimega vahemik B5: B12 ja väärtus nimega vahemik C5: C12.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selgitus
Kõige lihtsamal kujul tagastab SMALL selle konstruktsiooniga vahemikus "N väikseima" väärtuse:
=SMALL (range,N)
Nii näiteks:
=SMALL (C5:C12,2)
tagastab vahemikus C5: C12 2. väikseima väärtuse, mis on näidatud näites 5.
Kui aga sisestate teiseks argumendiks SMALLile "massiivikonstandi" (nt konstant kujul (1,2,3)), tagastab SMALL tulemuste massiivi ühe tulemuse asemel. Niisiis, valem:
=SMALL (C5:C12, (1,2,3))
tagastab massiivi 1., 2. ja 3. väikseima väärtuse C5: C12 sellises massiivis: (4,5,7).
Niisiis, siin on trikk väärtuste filtreerimine enne VÄIKSET käivitamist. Teeme seda IF-funktsioonil põhineva avaldisega:
IF(color=E5,value)
See loob väärtuste massiivi, mis sisestatakse SMALL-i. Põhimõtteliselt muudavad massiivi ainult punase värviga seotud väärtused. Kui värv võrdub "punasega", sisaldab massiiv numbrit ja kui värv pole punane, sisaldab massiiv valet FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Funktsioon VÄIKE ignoreerib FALSE väärtusi ja tagastab massiivi 3 väikseimat väärtust: (4,8,10). Funktsioon SUM tagastab lõpptulemuse 22.
