Generisk formel
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Resumé
For at opsummere topværdierne i et interval kan du bruge en formel baseret på den store funktion, pakket ind i funktionen SUMPRODUCT. I den generiske form med formlen (ovenfor) repræsenterer rng et område af celler, der indeholder numeriske værdier, og N repræsenterer ideen om Nth-værdi.
I eksemplet indeholder den aktive celle denne formel:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Forklaring
I sin enkleste form vil LARGE returnere den "Nth største" værdi i et interval. For eksempel formlen:
=LARGE(B4:B13, 2)
returnerer den næststørste værdi i området B4: B13, som i eksemplet ovenfor er tallet 9.
Men hvis du leverer en "arraykonstant" (f.eks. En konstant i formen (1,2,3)) til LARGE som det andet argument, returnerer LARGE et array med resultater i stedet for et enkelt resultat. Så formlen:
=LARGE(B4:B13,(1,2,3))
returnerer den 1., 2. og 3. største værdi i området B4: B13. I eksemplet ovenfor, hvor B4: B13 indeholder tallene 1-10, bliver resultatet fra STOR arrayet (8,9,10). SUMPRODUCT summerer derefter tallene i denne matrix og returnerer et totalt, hvilket er 27.
SUM i stedet for SUMPRODUCT
SUMPRODUCT er en fleksibel funktion, der giver dig mulighed for at bruge cellereferencer til k inde i LARGE-funktionen.
Men hvis du bruger en simpel hårdkodet arraykonstant som (1,2,3), kan du bare bruge SUM-funktionen:
=SUM(LARGE(B4:B13,(1,2,3)))
Bemærk, at du skal indtaste denne formel som en matrixformel, hvis du bruger cellereferencer og ikke en matrixkonstant for k inde i LARGE.
Når N bliver stor
Når N bliver stor, bliver det kedeligt at oprette arraykonstanten manuelt - Hvis du vil opsummere de øverste 20 eller 30 værdier i en stor liste, vil det tage lang tid at indtaste en arraykonstant med 20 eller 30 elementer. I dette tilfælde kan du bruge en genvej til at opbygge den arraykonstant, der bruger ROW- og INDIRECT-funktionerne.
For eksempel, hvis du vil SUM de øverste 20 værdier i et interval kaldet "rng", kan du skrive en formel som denne:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variabel N
Med utilstrækkelige data kan et fast N forårsage fejl. I dette tilfælde kan du prøve en formel som denne:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Her bruger vi MIN med COUNT til at opsummere de øverste 3 værdier eller antallet af værdier, hvis mindre end 3.
