I denne artikkelen lærer vi hvordan du summerer de N største verdiene i et område i Excel.
Scenario:
I enkle ord, mens vi jobber med datatall, noen ganger gitt en betingelse, dvs. når vi trenger å få summen av tallene til de fem øverste tallene i matrisen eller bunnen av matrisen. Du kan enkelt utføre løsningen på dette problemet ved å bruke Excel -funksjonene som forklart nedenfor.
Summen av TOP N -verdier
Hvordan løse problemet?
For denne artikkelen må vi bruke SUMPRODUCT -funksjonen. Nå skal vi lage en formel av disse funksjonene. Her får vi et område, og vi må topp 5 verdier i området og få summen av verdiene.
Generisk formel:
| = SUMPRODUCT (STORT (område, {1, 2,…., N}}) |
område: verdiområde
Verdier: tall atskilt med kommaene, for eksempel hvis du vil finne de tre beste verdiene, bruk {1, 2, 3}.
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor.
Her har vi en rekke verdier fra A1: A23.
Her er området gitt som ved bruk av det navngitte området excel -verktøyet.
For det første må vi finne de fem beste verdiene ved å bruke LARGE -funksjonen og deretter utføre sumoperasjon over disse 5 verdiene. Nå vil vi bruke følgende formel for å få summen
Bruk formelen:
| = SUMPRODUCT (STORT (område, {1, 2, 3, 4, 5})) |
Forklaring:
- LARGE -funksjonen returnerer de fem numeriske verdiene og returnerer matrisen til SUMPRODUCT -funksjonen.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- SUMPRODUCT -funksjonen får matrisen med topp 5 -verdier, som har en matrise med topp 5 -tall, returnerer SUMMEN for disse tallene.
Her er området gitt som det navngitte området. Trykk Enter for å få SUMMEN av de 5 beste tallene.
Som du kan se i øyeblikksbildet ovenfor er denne summen 544. Summen av verdiene 149 + 123 + 100 + 87 + 85 = 544.
Prosessen ovenfor brukes til å beregne summen av noen få tall fra toppen. Men å beregne for n (stort) antall verdier i et langt område.
Bruk formelen:
| = SUMPRODUCT (STORT (område, RAD (INDIREKT ("1:10")) |
Her genererer vi summen av de 10 beste verdiene ved å få en matrise på 1 til 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} ved hjelp av RAD & INDIREKTE Excel -funksjoner.
Her fikk vi summen av de 10 beste tallene {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}) som resulterer i 850.
Summen av bunn N -verdier
Hvordan løse problemet?
For denne artikkelen må vi bruke SUMPRODUCT -funksjonen. Nå skal vi lage en formel av disse funksjonene. Her får vi et område, og vi må bunn 5 verdier i området og få summen av verdiene.
Generisk formel:
| = SUMPRODUCT (LITT (område, {1, 2,…., N}}) |
område: verdiområde
Verdier: tall atskilt med kommaene, for eksempel hvis du vil finne de tre nederste verdiene, bruker du {1, 2, 3}.
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor.
Her har vi en rekke verdier fra A1: A23.
Her er området gitt som ved bruk av det navngitte området excel -verktøyet.
For det første må vi finne de fem nederste verdiene ved å bruke SMALL -funksjonen, og deretter utføre sumoperasjon over disse 5 verdiene. Nå vil vi bruke følgende formel for å få summen
Bruk formelen:
| = SUMPRODUCT (LITT (område, {1, 2, 3, 4, 5})) |
Forklaring:
- SMALL -funksjonen returnerer de 5 nederste numeriske verdiene og returnerer matrisen til SUMPRODUCT -funksjonen.
= SUMPRODUCT ({23, 27, 28, 28, 30}))
- SUMPRODUCT -funksjonen får matrisen med bunn 5 -verdier, som har en matrise med bunn 5 -tall, returnerer SUMMEN for disse tallene.
Her er området gitt som det navngitte området. Trykk Enter for å få SUMMEN av de nederste 5 tallene.
Som du kan se i øyeblikksbildet ovenfor er denne summen 136. Summen av verdiene 23 + 27 + 28 + 28 + 30 = 136.
Prosessen ovenfor brukes til å beregne summen av noen få tall fra bunnen. Men å beregne for n (stort) antall verdier i et langt område.
Bruk formelen:
| = SUMPRODUCT (LITT (område, RAD (INDIRECT ("1:10")) |
Her genererer vi summen av de nederste 10 verdiene ved å få en matrise på 1 til 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} ved hjelp av RAD & INDIREKTE Excel -funksjoner.
Her fikk vi summen av de 10 nederste tallene som resulterer i 307.
Her er noen observasjonsnotater vist nedenfor.
Merknader:
- Formelen fungerer bare med tall.
- Formelen fungerer bare når det ikke er noen dubletter i oppslagstabellen
- SUMPRODUCT -funksjonen anser ikke -numeriske verdier (som tekst abc) og feilverdier (som #NUM!, #NULL!) Som nullverdier.
- SUMPRODUCT -funksjonen anser logisk verdi TRUE som 1 og Falsk som 0.
- Argumentmatrisen må ha samme lengde, ellers funksjonen.
Håper denne artikkelen om hvordan du summerer de N største verdiene i et område i Excel er forklarende. Finn flere artikler om beregning av verdier og relaterte Excel -formler her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post.
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.
SUM hvis datoen er mellom : Returnerer SUMMEN av verdier mellom gitte datoer eller periode i excel.
Sum hvis datoen er større enn gitt dato: Returnerer SUMMEN av verdier etter gitt dato eller periode i excel.
2 måter å summere etter måned i Excel: Returnerer SUMMEN av verdier i en gitt spesifikk måned i excel.
Slik summerer du flere kolonner med betingelse: Returnerer SUM av verdier på tvers av flere kolonner som har betingelse i excel
Hvordan bruke jokertegn i excel : Tell celler som matcher setninger ved hjelp av jokertegnene i excel
Populære artikler:
Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.
Slik bruker du VLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.