I forrige artikkel lærte vi hvordan vi kan oppsummere topp- eller bunn -N -verdier. I denne artikkelen prøver vi å oppsummere topp eller bunn N verdier med et kriterium.
Summen av TOP N -verdier med kriterier
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 et kriterium. Vi må få de fem beste verdiene i området og få summen av verdiene basert på de gitte kriteriene.
Generisk formel:
= SUMPRODUCT (STORT ((liste = kriterier) * (område), {1, 2,…., N}})
liste: kriterieliste
Kriterier: kriterier å matche
område: verdiområde
verdier: tall atskilt med kommaene, for eksempel hvis du vil finne de tre beste verdiene, bruk {1, 2, 3}.
Eksempel:
Her har vi datasettverdiene fra A1: D50.
For det første må vi finne de fem beste verdiene ved hjelp av LARGE -funksjonen som samsvarer med byen "Boston", og deretter utføres sumoperasjon over de fem verdiene. Nå vil vi bruke følgende formel for å få summen
Bruk formelen:
= SUMPRODUCT (LARGE ((City = "Boston") * (antall), {1, 2, 3, 4, 5}))
Forklaring:
- Citys "Boston" matcher City -serien som er nevnt. Dette returnerer en rekke sanne og usanne.
- LARGE -funksjonen returnerer de fem øverste numeriske verdiene fra mengdeområdet og returnerer matrisen til SUMPRODUCT -funksjonen.
= SUMPRODUKT {193, 149, 138, 134, 123}
- SUMPRODUCT -funksjonen får en matrise med topp 5 -verdier, med en matrise med topp 5 -tall som returnerer SUMMEN av disse tallene.
Her er by- og mengdeområdet gitt som det navngitte området. Trykk Enter for å få SUMMEN av de fem beste tallene.
Som du kan se i øyeblikksbildet ovenfor, er den summen 737. Summen av verdiene 193 + 149 + 138 + 134 + 123 = 737.
Du kan kontrollere verdiene ovenfor i datasettet ved hjelp av excel -filteralternativet. Bruk filteret på By & mengdeoverskriften og klikk på pilknappen på byoverskriften som vises. Følg trinnene som vist nedenfor.
Trinn:
- Velg City -overskriftscellen. Påfør filter ved hjelp av snarvei Ctrl + Shift + L
- Klikk på pilen som vises som et filteralternativ.
- Velg alternativet (Velg alle).
- Velg bare byen Boston.
- Velg mengdeoverskriften nå.
- Sorter listen fra største til minste, og du kan se alle de fem beste verdiene som vi beregnet ved hjelp av formelen.
Som du kan se i gifen ovenfor, alle de 5 verdiene som samsvarer med de gitte kriteriene. Dette betyr også at formelen fungerer fint for å få tellingen av disse verdiene
STORE N -tall
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 (LARGE ((City = "Boston") * (antall), RAD (INDIRECT ("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 har vi summen av de 10 beste tallene som resulterer i 1147.
Summen av bunn N -verdier med kriterier
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:
{= SUMME (LITEN (HVIS (City = "Boston", mengde), {1, 2, 3, 4, 5}))}
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: D50.
Her er byen og mengdeområdet gitt ved å bruke det navngitte området excel -verktøyet.
For det første må vi finne de fem nederste verdiene ved hjelp av SMALL -funksjonen som samsvarer med kriterier og deretter utføres sumoperasjon over de 5 verdiene. Nå vil vi bruke følgende formel for å få summen
Bruk formelen:
{= SUMME (LITEN (HVIS (City = "Boston", mengde), {1, 2, 3, 4, 5}))}
IKKE bruk krøllbøyler manuelt. Krøllete seler påført med Ctrl + Shift + Enter i stedet for bare Tast inn.
Forklaring:
- SMALL -funksjon med IF -funksjon returnerer de 5 nederste numeriske verdiene som samsvarer med City "Boston" og returnerer matrisen til SUM -funksjonen.
= SUMMER ({23, 27, 28, 28, 30}))
- SUM -funksjonen får matrisen med bunn 5 -verdier, som har en matrise med bunn 5 -tall, returnerer SUMMEN for tallene som brukes med CTRL + SKIFT + ENTER.
Her er by- og mengdeområdet gitt som det navngitte området. trykk Ctrl + Shift + Enter for å få SUMMEN av de nederste 5 tallene, da dette er en matriseformel.
Som du kan se i øyeblikksbildet ovenfor er summen 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:
{ = SUMME (LITEN (HVIS (By = "Boston", mengde), RAD (INDIRECT ("1:10")))) }
IKKE bruk krøllebrakettene manuelt. Bruk Ctrl + Shift + Enter i stedet for å bruke Enter.
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 har vi summen av de 10 nederste tallene som vil resultere i 155.
Her er noen observasjonsnotater vist nedenfor.
Merknader:
- Formelen fungerer bare med tall.
- Formelen fungerer bare når det ikke er dubletter på 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 som funksjonen.
Håper denne artikkelen om hvordan du returnerer sum av topp 5 -verdier eller bunn 5 -verdier med kriterier i Excel er forklarende. Finn flere artikler om SUMPRODUCT -funksjoner her. Vennligst del spørringen nedenfor i kommentarfeltet. Vi hjelper deg.
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
50 Excel -snarvei for å øke produktiviteten
Rediger en rullegardinliste
Absolutt referanse i Excel
Hvis det er betinget formatering
Hvis det er jokertegn
Vlookup etter dato
Konverter tommer til fot og tommer i Excel 2016
Bli med for- og etternavn i excel
Telle celler som matcher enten A eller B