Generisk formel
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Resumé
For at opsummere de laveste n-værdier i et interval kan du bruge en formel baseret på SMALL-funktionen og SUMPRODUCT-funktionen. I den generiske form med formlen (ovenfor) repræsenterer rng et område af celler, der indeholder numeriske værdier, og n repræsenterer antallet af laveste værdier, der skal summeres. I det viste eksempel indeholder E5 denne formel:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
som returnerer summen af de tre mindste værdier i B5: B14, 60
Forklaring
I sin enkleste form returnerer SMALL den "nth mindste" værdi i et interval. For eksempel:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Men hvis du leverer en arraykonstant (f.eks. En konstant i formen (1,2,3)) til SMALL som det andet argument, returnerer SMALL et array med resultater i stedet for et enkelt resultat. For eksempel:
=SMALL(A1:A10,(1,2,3))
returnerer 1., 2. og 3. mindste værdi i området A1: A10.
Arbejder indefra og ud i det viste eksempel, returnerer SMALL de 3 mindste værdier i området B5: B14:
=SMALL(B4:B14,(1,2,3))
Resultatet er en matrix som denne:
(10,20,30)
Denne matrix returneres direkte til SUMPRODUCT-funktionen, som summerer tallene og returnerer det samlede:
SUMPRODUCT((10,20,30)) // returns 60
Arrayformel med SUM
Det er almindeligt at bruge SUMPRODUCT som ovenfor, fordi det kan håndtere arrays indbygget uden at indtaste som en matrixformel. Du kan dog også skrive en matrixformel med SUM-funktionen som denne:
(=SUM(SMALL(B4:B13,(1,2,3))))
Dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Når n bliver stor
Når n bliver stor, bliver det kedeligt at oprette arraykonstanten manuelt - at indtaste en arraykonstant med 20 eller 30 elementer vil tage lang tid. I dette tilfælde kan du bruge en genvej til at opbygge den arraykonstant, der bruger ROW- og INDIRECT-funktionerne. For eksempel, for at SUMME de nederste 20 værdier i et interval kaldet "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 at indstille en formel, hvor n er en variabel i en anden celle, kan du sammenkæde inde i INDIRECT. For eksempel, hvis A1 indeholder N, kan du bruge:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Dette giver en bruger mulighed for at ændre værdien af n direkte på regnearket.
