Generisk formel
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Sammanfattning
För att summera de översta värdena i ett intervall kan du använda en formel baserad på den STORA funktionen, insvept i SUMPRODUCT-funktionen. I den generiska formen av formeln (ovan) representerar rng ett antal celler som innehåller numeriska värden och N representerar idén om Nth-värdet.
I exemplet innehåller den aktiva cellen följande formel:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Förklaring
I sin enklaste form kommer LARGE att returnera det "Nth största" värdet i ett intervall. Till exempel formeln:
=LARGE(B4:B13, 2)
returnerar det näst största värdet i intervallet B4: B13 som i exemplet ovan är siffran 9.
Men om du anger en "arraykonstant" (t.ex. en konstant i formen (1,2,3)) till LARGE som det andra argumentet, kommer LARGE att returnera en array med resultat istället för ett enda resultat. Så formeln:
=LARGE(B4:B13,(1,2,3))
returnerar det 1: a, 2: a och 3: e största värdet i intervallet B4: B13. I exemplet ovan, där B4: B13 innehåller siffrorna 1-10, blir resultatet från STOR matrisen (8,9,10). SUMPRODUCT summerar sedan siffrorna i denna matris och returnerar en summa, vilket är 27.
SUM istället för SUMPRODUCT
SUMPRODUCT är en flexibel funktion som låter dig använda cellreferenser för k inuti LARGE-funktionen.
Men om du använder en enkel hårdkodad arraykonstant som (1,2,3) kan du bara använda SUM-funktionen:
=SUM(LARGE(B4:B13,(1,2,3)))
Observera att du måste ange denna formel som en matrisformel om du använder cellreferenser och inte en arraykonstant för k inuti LARGE.
När N blir stor
När N blir stor blir det tråkigt att skapa arraykonstanten för hand - Om du vill summera till de översta 20 eller 30 värdena i en stor lista tar det lång tid att skriva ut en arraykonstant med 20 eller 30 objekt. I det här fallet kan du använda en genväg för att bygga arraykonstanten som använder ROW- och INDIRECT-funktionerna.
Om du till exempel vill SUMMA de 20 bästa värdena i ett intervall som heter "rng" kan du skriva en formel så här:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variabel N
Med otillräcklig data kan ett fast N orsaka fel. I det här fallet kan du prova en sådan formel:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Här använder vi MIN med COUNT för att summera de tre bästa värdena, eller antalet värden, om det är mindre än 3.
