I denne artikel lærer vi, hvordan man opsummerer de øverste eller nederste N -værdier i en række tal i Excel. Hvis du vil opsummere top 4, top 5 eller top N værdier i et givet område, hjælper denne artikel med at gøre det.
Summen af TOP N værdier
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 toppe 5 værdier inden for rækkevidde og få summen af værdierne.
Generisk formel:
= SUMPRODUCT (STORT (område, {1, 2,…., N}})
rækkevidde: værdiområde
Værdier: tal adskilt ved hjælp af kommaer, f.eks. Hvis du vil finde de tre øverste værdier, skal du bruge {1, 2, 3}.
Eksempel: Sum Top 5 værdier af område
Lad os teste denne formel ved at køre den på eksemplet nedenfor.
Her har vi en række værdier fra A1: A23.
Området A1: A23 indeholder værdier, og det er navngivet som værdier.
For det første skal vi finde de fem bedste værdier ved hjælp af LARGE -funktionen og derefter udføres sumoperation over disse 5 værdier. Nu vil vi bruge følgende formel til at få summen af top 5 værdier.
Brug formlen:
= SUMPRODUCT (STORT (område, {1, 2, 3, 4, 5}))
Forklaring:
- LARGE -funktionen returnerer de 5 øverste numeriske værdier og returnerer matrixen til SUMPRODUCT -funktionen.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- SUMPRODUCT -funktionen opsummerer en række top 5 -værdier.
Her angives intervallet 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 544. Summen af værdierne 149 + 123 + 100 + 87 + 85 = 544.
Ovenstående proces bruges til at beregne summen af et par tal fra toppen. Men at beregne for n (stort) antal værdier i en lang rækkevidde.
Brug formlen:
= SUMPRODUCT (STORT (rækkevidde, RÆKE (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 fik vi summen af de 10 bedste tal {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}), hvilket resulterer i 850.
Summen af bunden N -værdier
Hvordan løser man problemet?
Generisk formel:
= SUMPRODUCT (SMÅ (rækkevidde, {1, 2,…., N}})
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: A23.
Her er det område, der er givet 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 og derefter udføres sumoperation over disse 5 værdier. Nu vil vi bruge følgende formel for at få summen
Brug formlen:
= SUMPRODUCT (SMÅ (rækkevidde, {1, 2, 3, 4, 5}))
Forklaring:
- SMALL funktion returnerer de 5 nederste numeriske værdier og returnerer arrayet til SUMPRODUCT funktionen.
= SUMPRODUCT ({23, 27, 28, 28, 30}))
- SUMPRODUCT -funktionen får en række bundværdier at opsummere.
Her angives intervallet som det navngivne område. Tryk på Enter for at få SUMMEN af de nederste 5 tal.
Som du kan se i ovenstående snapshot er denne sum 136. Summen af værdierne 23 + 27 + 28 + 28 + 30 = 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:
= SUMPRODUCT (SMÅ (rækkevidde, RÆKE (INDIREKTE ("1:10"))
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 307.
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 n -værdier eller nederste n -værdier i Excel, er forklarende. Find flere artikler om SUMPRODUCT -funktioner her. Del venligst din forespørgsel nedenfor i kommentarfeltet. Vi hjælper dig.
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
