I den forrige artikel lærte vi, hvordan man opsummerer øverste eller nederste N -værdier. I denne artikel forsøger vi at opsummere top eller bund N værdier med et kriterium.
Summen af TOP N -værdier med kriterier
Hvordan løser man problemet?
I denne artikel skal vi bruge SUMPRODUCT -funktionen. Nu vil vi lave en formel ud af disse funktioner. Her får vi en rækkevidde og et kriterium. Vi skal få de 5 bedste værdier i området og få summen af værdierne baseret på de givne kriterier.
Generisk formel:
= SUMPRODUCT (STORT ((liste = kriterier) * (område), {1, 2,…., N}})
liste: kriterieliste
Kriterier: kriterier, der matcher
rækkevidde: værdiområde
værdier: tal adskilt ved hjælp af kommaerne, f.eks. hvis du ønsker at finde de 3 øverste værdier, skal du bruge {1, 2, 3}.
Eksempel:
Her har vi datasættets værdier fra A1: D50.
For det første skal vi finde de fem bedste værdier ved hjælp af LARGE -funktionen, der matcher byen "Boston", og derefter udføres sumoperationen over disse 5 værdier. Nu vil vi bruge følgende formel for at få summen
Brug formlen:
= SUMPRODUCT (LARGE ((By = "Boston") * (mængde), {1, 2, 3, 4, 5}))
Forklaring:
- Citys "Boston" matcher det nævnte City -område. Dette returnerer en række sand og falsk.
- LARGE -funktionen returnerer de 5 øverste numeriske værdier fra mængdeområdet og returnerer matrixen til SUMPRODUCT -funktionen.
= SUMPRODUCT {193, 149, 138, 134, 123}
- SUMPRODUCT -funktionen får en matrix med top 5 -værdier, hvor en matrix med top 5 -numre returnerer SUM af disse tal.
Her er området By & mængde angivet som det navngivne område. Tryk på Enter for at få SUMMEN af de 5 bedste tal.
Som du kan se i ovenstående snapshot, er denne sum 737. Summen af værdierne 193 + 149 + 138 + 134 + 123 = 737.
Du kan kontrollere ovenstående værdier i datasættet ved hjælp af excel -filterindstillingen. Anvend filteret på By & mængdeoverskriften, og klik på pileknappen på byoverskriften, der vises. Følg trinene som vist herunder.
Trin:
- Vælg cellen City header. Anvend filter ved hjælp af genvej Ctrl + Shift + L
- Klik på pilen, der vises som en filterindstilling.
- Vælg indstillingen (Vælg alle).
- Vælg kun byen Boston.
- Vælg mængdeoverskrift nu.
- Sorter listen fra største til mindste, og du kan se alle de 5 bedste værdier, som vi har beregnet ved hjælp af formlen.
Som du kan se i ovenstående gif alle de 5 værdier, der matcher de givne kriterier. Dette betyder også, at formlen fungerer fint for at få tællingen af disse værdier
STORE N -numre
Ovenstående proces bruges til at beregne summen af et par tal fra toppen. Men at regne for n (stort) antal værdier i en lang rækkevidde.
Brug formlen:
= SUMPRODUCT (LARGE ((By = "Boston") * (mængde), RÆK (INDIREKT ("1:10"))
Her genererer vi summen af top 10 -værdier ved at få en matrix på 1 til 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} ved hjælp af ROW & INDIRECT Excel -funktionerne.
Her har vi summen af de 10 bedste tal, hvilket resulterer i 1147.
Summen af bunden N -værdier med kriterier
Hvordan løser man problemet?
I denne artikel skal vi bruge SUMPRODUCT -funktionen. Nu vil vi lave en formel ud af disse funktioner. Her får vi et område, og vi skal bundne 5 værdier i området og få summen af værdierne.
Generisk formel:
{= SUM (SMÅ (HVIS (By = "Boston", mængde), {1, 2, 3, 4, 5}))}
Område: værdiområde
Værdier: tal adskilt ved hjælp af kommaer, f.eks. Hvis du vil finde de nederste 3 værdier, skal du bruge {1, 2, 3}.
Eksempel:
Alt dette kan være forvirrende at forstå. Så lad os teste denne formel ved at køre den på eksemplet nedenfor.
Her har vi en række værdier fra A1: D50.
Her er området By & mængde angivet som ved hjælp af det navngivne område excel -værktøj.
For det første skal vi finde de fem nederste værdier ved hjælp af SMALL -funktionen, der matcher kriterier og derefter udføres sumoperation over disse 5 værdier. Nu vil vi bruge følgende formel for at få summen
Brug formlen:
{= SUM (SMÅ (HVIS (By = "Boston", mængde), {1, 2, 3, 4, 5}))}
IKKE brug krøllet seler manuelt. Krøllet seler påført ved hjælp af Ctrl + Shift + Enter i stedet for bare Gå ind.
Forklaring:
- SMALL -funktion med IF -funktion returnerer de nederste 5 numeriske værdier, der matcher City "Boston" og returnerer arrayet til SUM -funktionen.
= SUM ({23, 27, 28, 28, 30}))
- SUM -funktionen får arrayet med de nederste 5 -værdier, som har en matrix med de nederste 5 -tal, returnerer SUMMEN for de tal, der bruges med CTRL + SKIFT + ENTER.
Her er området By & mængde angivet som det navngivne område. Trykke Ctrl + Shift + Enter for at få SUMMEN af de nederste 5 tal, da dette er en matrixformel.
Som du kan se i ovenstående snapshot er denne sum 136.
Ovenstående proces bruges til at beregne summen af et par tal fra bunden. Men at regne for n (stort) antal værdier i en lang rækkevidde.
Brug formlen:
{ = SUM (LILLE (HVIS (By = "Boston", mængde), RÆK (INDIREKT ("1:10")))) }
IKKE brug krøllet beslag manuelt. Brug Ctrl + Shift + Enter i stedet for at bruge Enter.
Her genererer vi summen af de nederste 10 værdier ved at få en matrix på 1 til 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} ved hjælp af ROW & INDIRECT Excel -funktionerne.
Her har vi summen af de nederste 10 tal, som vil resultere i 155.
Her er nogle observationsnoter vist nedenfor.
Bemærkninger:
- Formlen fungerer kun med tal.
- Formlen fungerer kun, når der ikke er nogen dubletter på opslagstabellen
- SUMPRODUCT -funktionen betragter ikke -numeriske værdier (som tekst abc) og fejlværdier (som #NUM!, #NULL!) Som nulværdier.
- SUMPRODUCT -funktionen betragter logisk værdi TRUE som 1 og Falsk som 0.
- Argumentmatrixen skal have samme længde som funktionen.
Håber denne artikel om, hvordan du returnerer summen af top 5 -værdier eller nederste 5 -værdier med kriterier i Excel, er forklarende. Find flere artikler om SUMPRODUCT -funktioner her. Del venligst din forespørgsel nedenfor i kommentarfeltet. Vi hjælper dig.
Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet
Sådan bruges funktionen SUMPRODUCT i Excel: Returnerer SUMMEN efter multiplikation af værdier i flere arrays i excel.
SUM, hvis datoen er mellem : Returnerer SUM af værdier mellem givne datoer eller periode i excel.
Sum, hvis datoen er større end den givne dato: Returnerer SUM af værdier efter den givne dato eller periode i excel.
2 måder at summe efter måned i Excel: Returnerer SUM af værdier inden for en given specifik måned i excel.
Sådan summeres flere kolonner med betingelse: Returnerer SUM af værdier på tværs af flere kolonner med betingelse i excel
Sådan bruges jokertegn i excel : Tæl celler, der matcher sætninger ved hjælp af jokertegnene i excel
Populære artikler
50 Excel -genvej til at øge din produktivitet
Rediger en rulleliste
Absolut reference i Excel
Hvis med betinget formatering
Hvis det er med jokertegn
Vlookup efter dato
Konverter tommer til fod og tommer i Excel 2016
Deltag i for- og efternavn i excel
Tæl celler, der matcher enten A eller B
