Generisk formel
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Sammendrag
For å oppsummere toppverdiene i et område, kan du bruke en formel basert på STOR-funksjonen, pakket inn i SUMPRODUCT-funksjonen. I den generiske formen av formelen (ovenfor) representerer rng et område med celler som inneholder numeriske verdier, og N representerer ideen om Nth-verdi.
I eksemplet inneholder den aktive cellen denne formelen:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Forklaring
I sin enkleste form vil LARGE returnere den "Nth største" verdien i et område. For eksempel formelen:
=LARGE(B4:B13, 2)
vil returnere den nest største verdien i området B4: B13, som i eksemplet ovenfor er tallet 9.
Imidlertid, hvis du leverer en "matrisekonstant" (f.eks. En konstant i formen (1,2,3)) til STOR som det andre argumentet, vil LARGE returnere en rekke resultater i stedet for et enkelt resultat. Så formelen:
=LARGE(B4:B13,(1,2,3))
vil returnere 1., 2. og 3. største verdi i området B4: B13. I eksemplet ovenfor, der B4: B13 inneholder tallene 1-10, blir resultatet fra STOR matrisen (8,9,10). SUMPRODUCT summerer deretter tallene i denne matrisen og returnerer totalt, som er 27.
SUM i stedet for SUMPRODUCT
SUMPRODUCT er en fleksibel funksjon som lar deg bruke cellereferanser for k inne i STOR funksjon.
Imidlertid, hvis du bruker en enkel hardkodet matrisekonstant som (1,2,3), kan du bare bruke SUM-funksjonen:
=SUM(LARGE(B4:B13,(1,2,3)))
Merk at du må angi denne formelen som en matriseformel hvis du bruker cellereferanser og ikke en matrisekonstant for k inne i STOR.
Når N blir stor
Når N blir stor blir det kjedelig å lage matrisekonstanten for hånd - Hvis du vil oppsummere til de 20 beste eller 30 beste verdiene i en stor liste, vil det ta lang tid å skrive ut en matrisekonstant med 20 eller 30 elementer. I dette tilfellet kan du bruke en snarvei til å bygge matrisekonstanten som bruker ROW og INDIRECT-funksjonene.
For eksempel, hvis du vil SUM de 20 beste verdiene i et område kalt "rng", kan du skrive en formel som denne:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variabel N
Med utilstrekkelig data kan en fast N forårsake feil. I dette tilfellet kan du prøve en formel som denne:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Her bruker vi MIN med COUNT for å summere de tre beste verdiene, eller antall verdier, hvis mindre enn 3.
