Yleinen kaava
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Yhteenveto
Alueen n pienimmän arvon nollaamiseksi voit käyttää PIENI-funktioon ja SUMPRODUCT-funktioon perustuvaa kaavaa. Kaavan (yllä) yleisessä muodossa rng edustaa solualueita, jotka sisältävät numeerisia arvoja, ja n edustaa pienimpien yhteenlaskettavien arvojen lukumäärää. Esitetyssä esimerkissä E5 sisältää tämän kaavan:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
joka palauttaa B5: n kolmen pienimmän arvon summan: B14, 60
Selitys
Yksinkertaisimmassa muodossaan SMALL palauttaa alueen n: nneksi pienimmän arvon. Esimerkiksi:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Jos kuitenkin syötät matriisivakion (esim. Vakion muodossa muodossa (1,2,3)) SMALL: ksi toisena argumenttina, SMALL palauttaa joukon tuloksia yhden tuloksen sijaan. Esimerkiksi:
=SMALL(A1:A10,(1,2,3))
palauttaa 1., 2. ja 3. pienimmät arvot alueella A1: A10.
Työskentelemällä sisältä ulospäin esitetyssä esimerkissä SMALL palauttaa 3 pienintä arvoa alueella B5: B14:
=SMALL(B4:B14,(1,2,3))
Tuloksena on tällainen taulukko:
(10,20,30)
Tämä taulukko palautetaan suoraan SUMPRODUCT-funktioon, joka laskee yhteen numerot ja palauttaa kokonaismäärän:
SUMPRODUCT((10,20,30)) // returns 60
Taulukko kaavan kanssa SUM
On tavallista käyttää SUMPRODUCT kuten yllä, koska se voi käsitellä matriiseja natiivisti ilman, että syötät matriisikaavana. Voit kuitenkin kirjoittaa myös taulukon kaavan SUM-funktiolla seuraavasti:
(=SUM(SMALL(B4:B13,(1,2,3))))
Tämä on matriisikaava, ja se on syötettävä näppäimillä + + Vaihto + Enter, paitsi Excel 365: ssä.
Kun n: stä tulee suuri
Kun n kasvaa suureksi, matriisivakion luominen käsin on tylsää - 20 tai 30 alkion sisältävän matriisivakion kirjoittaminen kestää kauan. Tässä tapauksessa voit käyttää pikakuvaketta taulukon vakion rakentamiseen, joka käyttää RIVI- ja EPÄSUORA-toimintoja. Esimerkiksi, jos summataan "rng" -alueen 20 alinta arvoa, voit kirjoittaa seuraavan kaavan:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Tässä INDIRECT muuntaa merkkijonon "1:20" alueeksi 1:20, joka palautetaan suoraan SMALL-muotoon.
Muuttuja n
Jos haluat määrittää kaavan, jossa n on muuttuja toisessa solussa, voit ketjuttaa INDIRECT-sisällä. Esimerkiksi, jos A1 sisältää N, voit käyttää:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Tämän avulla käyttäjä voi muuttaa n: n arvoa suoraan laskentataulukossa.
