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