I föregående artikel lärde vi oss hur man summerar översta eller nedre N -värden. I den här artikeln försöker vi summera översta eller nedre N -värden med ett kriterium.
Summan av TOP N -värden med kriterier
Hur löser man problemet?
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 en rad och ett kriterium. Vi måste få de fem bästa värdena i intervallet och få summan av värdena baserat på de givna kriterierna.
Generisk formel:
= SUMPRODUCT (STOR ((lista = kriterier) * (intervall), {1, 2,…., N}})
lista: kriterielista
Kriterier: kriterier att matcha
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:
Här har vi datauppsättningsvärdena från A1: D50.
Först måste vi hitta de fem bästa värdena med hjälp av funktionen STOR som matchar staden "Boston" och sedan utföra summanöverföring över dessa fem värden. Nu kommer vi att använda följande formel för att få summan
Använd formeln:
= SUMPRODUCT (LARGE ((Stad = "Boston") * (kvantitet), {1, 2, 3, 4, 5}))
Förklaring:
- Citys "Boston" matchar City -serien som nämns. Detta returnerar en uppsättning sant och falskt.
- LARGE -funktionen returnerar de fem översta numeriska värdena från kvantitetsintervallet och returnerar matrisen till SUMPRODUCT -funktionen.
= SUMPRODUKT {193, 149, 138, 134, 123}
- SUMPRODUCT -funktionen får en array med topp 5 -värden, med en array med topp 5 -nummer som returnerar SUMMEN för dessa nummer.
Här anges stads- och kvantitetsintervallet 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 737. Summan av värdena 193 + 149 + 138 + 134 + 123 = 737.
Du kan kontrollera ovanstående värden i dataset med hjälp av excel -filteralternativet. Applicera filtret på rubriken Stad och antal och klicka på pilknappen på stadsrubriken som visas. Följ stegen enligt nedan.
Steg:
- Välj cellen City header. Applicera filter med genväg Ctrl + Skift + L
- Klicka på pilen som visas som ett filteralternativ.
- Välj alternativet (Välj alla).
- Välj bara staden Boston.
- Välj kvantitetsrubrik nu.
- Sortera listan från största till minsta och du kan se alla de fem bästa värdena som vi beräknade med hjälp av formeln.
Som du kan se i ovanstående gif alla fem värden som matchar de givna kriterierna. Detta betyder också att formeln fungerar bra för att få räkningen av dessa värden
STORA N -nummer
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 (LARGE ((City = "Boston") * (kvantitet), 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 har vi summan av de 10 bästa siffrorna som resulterar i 1147.
Summan av Bottom N -värden med kriterier
Hur löser man problemet?
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 bottna 5 värden i intervallet och få summan av värdena.
Generisk formel:
{= SUMMA (LILLA (OM (Stad = "Boston", kvantitet), {1, 2, 3, 4, 5}))}
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 ett värdeintervall från A1: D50.
Här anges stads- och kvantitetsintervallet med hjälp av det angivna excelverktyget.
För det första måste vi hitta de fem nedre värdena med hjälp av SMALL -funktionen som matchar kriterier och sedan summeras operationen över dessa 5 värden. Nu kommer vi att använda följande formel för att få summan
Använd formeln:
{= SUMMA (LILLA (OM (Stad = "Boston", kvantitet), {1, 2, 3, 4, 5}))}
ANVÄND INTE lockiga hängslen manuellt. Lockiga hängslen applicerade med Ctrl + Skift + Enter i stället för just Stiga på.
Förklaring:
- SMALL -funktionen med IF -funktion returnerar de 5 nedersta numeriska värdena som matchar City "Boston" och returnerar matrisen till SUM -funktionen.
= SUMMA ({23, 27, 28, 28, 30}))
- SUM -funktionen får matrisen med de 5 nedre värdena, som har en matris med botten 5 -siffrorna returnerar SUMMEN för de siffror som används med CTRL + SKIFT + ENTER.
Här anges stads- och kvantitetsintervallet som det angivna intervallet. Tryck Ctrl + Skift + Enter för att få SUMMEN för de 5 nedre siffrorna eftersom detta är en matrisformel.
Som du kan se i ögonblicksbilden ovan är summan 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:
{ = SUMMA (LILLA (OM (Stad = "Boston", kvantitet), RAD (INDIRECT ("1:10")))) }
ANVÄND INTE de lockiga fästena manuellt. Använd Ctrl + Skift + Enter istället för att använda Enter.
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 155.
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 topp 5 -värden eller nedre 5 -värden med kriterier 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.
Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen
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
