Generisk formel
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Sammendrag
For å oppsummere de laveste n-verdiene i et område, kan du bruke en formel basert på SMALL-funksjonen og SUMPRODUCT-funksjonen. I den generiske formen av formelen (ovenfor) representerer rng et område med celler som inneholder numeriske verdier, og n representerer antallet laveste verdier som skal summeres. I eksemplet som vises inneholder E5 denne formelen:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
som returnerer summen av de tre minste verdiene i B5: B14, 60
Forklaring
I sin enkleste form vil SMALL returnere den "nth minste" verdien i et område. For eksempel:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Imidlertid, hvis du leverer en matrisekonstant (f.eks. En konstant i formen (1,2,3)) til SMALL som det andre argumentet, vil SMALL returnere en matrise med resultater i stedet for et enkelt resultat. For eksempel:
=SMALL(A1:A10,(1,2,3))
vil returnere de første, andre og tredje minste verdiene i området A1: A10.
Arbeider fra innsiden og ut i eksemplet som vises, returnerer SMALL de 3 minste verdiene i området B5: B14:
=SMALL(B4:B14,(1,2,3))
Resultatet er en matrise som dette:
(10,20,30)
Denne matrisen returneres direkte til SUMPRODUCT-funksjonen, som summerer tallene og returnerer totalen:
SUMPRODUCT((10,20,30)) // returns 60
Formelformel med SUM
Det er vanlig å bruke SUMPRODUCT som ovenfor, fordi den kan håndtere matriser naturlig uten å gå inn som en matriseformel. Du kan imidlertid også skrive en matriseformel med SUM-funksjonen slik:
(=SUM(SMALL(B4:B13,(1,2,3))))
Dette er en matriseformel og må angis med kontroll + skift + enter, unntatt i Excel 365.
Når n blir stor
Når n blir stor blir det kjedelig å lage matrisekonstanten for hånd - å skrive ut en matrisekonstant med 20 eller 30 elementer vil ta lang tid. I dette tilfellet kan du bruke en snarvei til å bygge matrisekonstanten som bruker ROW og INDIRECT-funksjonene. For å SUMME de nederste 20 verdiene i et område kalt "rng" kan du skrive en formel som denne:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Her konverterer INDIRECT strengen "1:20" til området 1:20, som returneres direkte til SMALL.
Variabel n
For å sette opp en formel der n er en variabel i en annen celle, kan du sammenkoble i INDIRECT. For eksempel, hvis A1 inneholder N, kan du bruke:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Dette lar en bruker endre verdien av n direkte på regnearket.
