Üldine valem
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Kokkuvõte
Vahemiku madalaimate n väärtuse summeerimiseks võite kasutada funktsiooni VÄIKE ja funktsiooni SUMPRODUCT põhinevat valemit. Valemi (ülaltoodud) üldises vormis tähistab rng arvulisi väärtusi sisaldavat lahtrivahemikku ja n tähistab summeeritavate väikseimate väärtuste arvu. Näidatud näites sisaldab E5 järgmist valemit:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
mis tagastab B5 kolme väikseima väärtuse summa: B14, 60
Selgitus
Lihtsamas vormis tagastab SMALL vahemiku n-nda väikseima väärtuse. Näiteks:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Kui aga sisestate massiivi konstandi (nt konstant kujul (1,2,3)) teiseks argumendiks SMALL, tagastab SMALL tulemuste massiivi ühe tulemuse asemel. Näiteks:
=SMALL(A1:A10,(1,2,3))
tagastab 1., 2. ja 3. väikseima väärtuse vahemikus A1: A10.
Näidatud näites töötades seestpoolt, tagastab SMALL vahemikus B5: B14 3 väikseimat väärtust:
=SMALL(B4:B14,(1,2,3))
Tulemuseks on selline massiiv:
(10,20,30)
See massiiv tagastatakse otse funktsioonile SUMPRODUCT, mis summeerib numbrid ja tagastab koguarvu:
SUMPRODUCT((10,20,30)) // returns 60
Massiivi valem koos summaga
SUMPRODUCT on tavaline, nagu ülalpool, kuna see saab massiive käsitleda loomulikult, massiivivalemina sisestamata. Kuid massiivi valemit saate kirjutada ka funktsiooniga SUM nii:
(=SUM(SMALL(B4:B13,(1,2,3))))
See on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Kui n muutub suureks
Kui n muutub suureks, muutub massiivikonstandi käsitsi loomine tüütuks - 20 või 30 üksusega massiivikonstandi väljatrükkimine võtab kaua aega. Sellisel juhul saate kasutada otseteed massiivi konstandi loomiseks, mis kasutab funktsioone ROW ja INDIRECT. Näiteks vahemiku "rng" 20 alumise väärtuse summeerimiseks võite kirjutada järgmise valemi:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Siin teisendab INDIRECT stringi "1:20" vahemikuks 1:20, mis tagastatakse otse VÄIKSEKS.
Muutuja n
Valemi seadistamiseks, kus n on muutuja teises lahtris, saate ühendada INDIRECTi sees. Näiteks kui A1 sisaldab N, võite kasutada järgmist.
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
See võimaldab kasutajal muuta n väärtust otse töölehel.
