I den här artikeln kommer vi att lära oss hur man summerar översta eller nedre N -värden med en rad nummer i Excel. Om du vill summera topp 4, topp 5 eller topp N -värden i ett givet intervall, hjälper den här artikeln till det.
Summan av TOP N -värden
För den här artikeln kommer vi att behöva använda SUMPRODUCT -funktionen. Nu kommer vi att göra en formel av dessa funktioner. Här får vi ett intervall och vi måste toppa 5 värden i intervallet och få summan av värdena.
Generisk formel:
= SUMPRODUCT (STORT (intervall, {1, 2,…., N}})
räckvidd: värdeintervall
Värden: siffror åtskilda med kommatecken som om du vill hitta de tre högsta värdena, använd {1, 2, 3}.
Exempel: Summa de fem bästa värdena för intervallet
Låt oss testa denna formel genom att köra den på exemplet som visas nedan.
Här har vi en rad värden från A1: A23.
Området A1: A23 innehåller värden och det heter som värden.
För det första måste vi hitta de fem bästa värdena med hjälp av funktionen LARGE och sedan utföra summanövrering över dessa 5 värden. Nu kommer vi att använda följande formel för att få summan av de fem bästa värdena.
Använd formeln:
= SUMPRODUCT (STORT (intervall, {1, 2, 3, 4, 5}))
Förklaring:
- LARGE -funktionen returnerar de 5 numeriska värdena och returnerar matrisen till SUMPRODUCT -funktionen.
= SUMPRODUCT ({149, 123, 100, 87, 85}))
- SUMPRODUCT -funktionen får en uppsättning av topp 5 -värden att summera.
Här anges intervallet som det angivna intervallet. Tryck på Enter för att få SUMMEN av de fem bästa siffrorna.
Som du kan se i ögonblicksbilden ovan är den summan 544. Summan av värdena 149 + 123 + 100 + 87 + 85 = 544.
Ovanstående process används för att beräkna summan av några siffror från toppen. Men att räkna ut för n (stort) antal värden inom ett långt intervall.
Använd formeln:
= SUMPRODUCT (STOR (intervall, RAD (INDIRECT ("1:10"))
Här genererar vi summan av de 10 bästa värdena genom att få en array med 1 till 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} med ROW & INDIRECT Excel -funktioner.
Här fick vi summan av de 10 bästa siffrorna {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}) vilket resulterar i 850.
Summan av botten N -värden
Hur löser man problemet?
Generisk formel:
= SUMPRODUCT (SMÅ (intervall, {1, 2,…., N}})
Område: värdeintervall
Värden: siffror åtskilda med kommatecken som om du vill hitta de tre nedersta värdena, använd {1, 2, 3}.
Exempel:
Allt detta kan vara förvirrande att förstå. Så, låt oss testa denna formel genom att köra den på exemplet som visas nedan.
Här har vi en rad värden från A1: A23.
Här är intervallet som ges med det namngivna excel -verktyget.
För det första måste vi hitta de fem nedre värdena med hjälp av SMALL -funktionen och sedan utföra summanövrering över dessa 5 värden. Nu kommer vi att använda följande formel för att få summan
Använd formeln:
= SUMPRODUCT (SMÅ (intervall, {1, 2, 3, 4, 5}))
Förklaring:
- SMALL -funktionen returnerar de 5 nedersta numeriska värdena och returnerar matrisen till SUMPRODUCT -funktionen.
= SUMPRODUCT ({23, 27, 28, 28, 30}))
- SUMPRODUCT -funktionen får en uppsättning av de fem nedre värdena att summera.
Här anges intervallet som det angivna intervallet. Tryck på Enter för att få SUMMEN för de 5 nedre siffrorna.
Som du kan se i ögonblicksbilden ovan är summan 136. Summan av värdena 23 + 27 + 28 + 28 + 30 = 136.
Ovanstående process används för att beräkna summan av några tal från botten. Men att räkna ut för n (stort) antal värden inom ett långt intervall.
Använd formeln:
= SUMPRODUCT (Liten (intervall, RAD (INDIRECT ("1:10"))
Här genererar vi summan av de 10 nedersta värdena genom att få en array med 1 till 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} med ROW & INDIRECT Excel -funktioner.
Här har vi summan av de 10 nedersta talen som kommer att resultera i 307.
Här är några observationsanteckningar som visas nedan.
Anmärkningar:
- Formeln fungerar bara med siffror.
- Formeln fungerar bara när det inte finns några dubbletter på uppslagstabellen
- SUMPRODUCT -funktionen betraktar icke -numeriska värden (som text abc) och felvärden (som #NUM!, #NULL!) Som nollvärden.
- SUMPRODUCT -funktionen anser logiskt värde TRUE som 1 och Falskt som 0.
- Argumentmatrisen måste ha samma längd som funktionen.
Hoppas att den här artikeln om hur du returnerar summan av översta n -värden eller nedre n -värden i Excel är förklarande. Hitta fler artiklar om SUMPRODUCT -funktioner här. Dela din fråga nedan i kommentarsfältet. Vi hjälper dig.
Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar SUMMEN efter multiplikation av värden i flera matriser i excel.
SUMMA om datumet är mellan : Returnerar summan av värden mellan givna datum eller period i excel.
Summa om datum är större än angivet datum: Returnerar summan av värden efter det angivna datumet eller perioden i excel.
2 sätt att summera per månad i Excel: Returnerar summan av värden inom en viss specifik månad i excel.
Hur man summerar flera kolumner med villkor: Returnerar SUMMAN av värden över flera kolumner med villkor i excel
Hur man använder jokertecken i excel : Räkna celler som matchar fraser med jokertecken i excel
Populära artiklar
50 Excel -genväg för att öka din produktivitet
Redigera en rullgardinslista
Absolut referens i Excel
Om med villkorlig formatering
Om med jokertecken
Vlookup efter datum
Konvertera tum till fot och tum i Excel 2016
Gå med för- och efternamn i excel
Räkna celler som matchar antingen A eller B
