Excel

Sum top n værdier

Sum Top N Values

Excel -formel: Sum de øverste n -værdierGenerisk formel | _+_ | Resumé

For at opsummere de øverste værdier i et område kan du bruge en formel baseret på funktionen LARGE, pakket ind i SUMPRODUCT -funktionen. I den generiske form af formlen (ovenfor), rng repræsenterer en række celler, der indeholder numeriske værdier og N repræsenterer ideen om Nth -værdi.





I eksemplet indeholder den aktive celle denne formel:

hvad gør mod-funktionen
= SUMPRODUCT ( LARGE (rng,{1,2, N }))
Forklaring

I sin enkleste form returnerer LARGE den 'Nth største' værdi i et område. For eksempel formlen:





 
= SUMPRODUCT ( LARGE (B4:B13,{1,2,3}))

vil returnere den 2. største værdi i området B4: B13, som i eksemplet ovenfor er tallet 9.

Men hvis du angiver en 'matrixkonstant' (f.eks. En konstant i formen {1,2,3}) til LARGE som det andet argument, returnerer LARGE en række resultater i stedet for et enkelt resultat. Så formlen:



 
= LARGE (B4:B13, 2)

vil returnere den 1., 2. og 3. største værdi i området B4: B13. I eksemplet ovenfor, hvor B4: B13 indeholder tallene 1-10, er resultatet fra LARGE arrayet {8,9,10}. SUMPRODUCT summerer derefter tallene i denne matrix og returnerer en total, som er 27.

SUM i stedet for SUMPRODUCT

SUMPRODUCT er en fleksibel funktion, der giver dig mulighed for at bruge cellereferencer for k inde i LARGE -funktionen.

Men hvis du bruger en simpel hårdkodet array konstant ligesom {1,2,3} kan du bare bruge SUM -funktionen:

 
= LARGE (B4:B13,{1,2,3})

Bemærk du skal indtast 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 matrixkonstanten i hånden - Hvis du vil summere til de øverste 20 eller 30 værdier i en stor liste, vil det tage lang tid at skrive en matrixkonstant med 20 eller 30 emner ud. I dette tilfælde kan du bruge en genvej til at opbygge den matrixkonstant, der bruger funktionerne RÆKKE og INDIREKTE.

For eksempel, hvis du vil SUMME de 20 bedste værdier i et område kaldet 'rng', kan du skrive en formel som denne:

 
= SUM ( LARGE (B4:B13,{1,2,3}))

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:

hvordan man viser dataanalyse i Excel
 
= SUMPRODUCT ( LARGE (rng, ROW ( INDIRECT ('1:20'))))

Her bruger vi MIN med COUNT til at opsummere de 3 øverste værdier, eller antallet af værdier, hvis det er mindre end 3.

Forfatter Dave Bruns


^