Summa minsta eller lägsta N-värden baserat på kriterier i Excel
I tidigare handledning har vi diskuterat hur man summerar de minsta n värdena i ett dataintervall. I den här artikeln kommer vi att utföra ytterligare en avancerad operation – att summera de lägsta n värdena baserat på ett eller flera kriterier i Excel.
Summa minsta eller lägsta N-värden baserat på kriterier i Excel
Om jag antar att jag har en rad data som visas nedan, vill jag nu summera de 3 lägsta beställningarna av produkten Apple.
I Excel, för att summera de n nedersta värdena i ett intervall med kriterier, kan du skapa en matrisformel genom att använda funktionerna SUMMA, LITEN och OM. Den generiska syntaxen är:
Array formula, should press Ctrl + Shift + Enter keys together.
- range=criteria: Omfånget av celler som matchar de specifika kriterierna;
- values: Listan som innehåller de n nedersta värdena som du vill summera;
- N: Det N:te bottenvärdet.
För att lösa ovanstående problem, använd nedanstående arrayformel i en tom cell:
Och tryck sedan på Ctrl + Skift + Enter tangenterna tillsammans för att få rätt resultat enligt nedanstående skärmbild:
Förklaring av formeln:
=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))
- IF(($A$2:$A$14=D2), $B$2:$B$14): Om produkten i sortimentet A2:A14 är lika med "Apple", kommer det att returnera det relativa numret från beställningslistan (B2:B14); Om produkten inte är "Apple", kommer ett FALSE att visas. Du kommer att få resultatet så här: {800;FALSE;FALSE;FALSE;1000;230;FALSE;FALSE;1600;FALSE;900;FALSE;500}.
- SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Den här SMALL-funktionen ignorerar FALSE-värdena och returnerar de 3 nedersta värdena i arrayen, så resultatet är detta: {230,500,800}.
- SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): Slutligen summerar SUM-funktionen talen i arrayen för att få resultatet: 1530.
Tips: Ta itu med två eller flera villkor:
Om du behöver summera de nedersta n värdena baserat på två eller flera kriterier behöver du bara lägga till andra intervall och kriterier med *-tecken inom IF-funktionen så här:
Array formula, should press Ctrl + Shift + Enter keys together.
- Range1=criteria1: Det första cellintervallet som matchar det första kriteriet;
- Range2=criteria2: Det andra cellintervallet för att matcha det andra kriteriet;
- Range3=criteria3: Det tredje intervallet av celler som matchar det tredje kriteriet;
- values: Listan som innehåller de n nedersta värdena som du vill summera;
- N: Det N:te bottenvärdet.
Till exempel vill jag summera de tre nedersta beställningarna av produkten Apple som säljs av Kerry, använd följande formel:
Och tryck sedan på Ctrl + Skift + Enter nycklar tillsammans för att få resultatet som du behöver:
Relativ funktion som används:
- BELOPP:
- SUM-funktionen lägger till värden. Du kan lägga till individuella värden, cellreferenser eller intervall eller en blandning av alla tre.
- SMÅ:
- Excel SMALL-funktionen returnerar ett numeriskt värde baserat på dess position i en lista när den sorteras efter värde i stigande ordning.
- IF:
- OM-funktionen testar för ett specifikt tillstånd och returnerar motsvarande värde som du anger för TRUE eller FALSE.
Fler artiklar:
- Summa minsta eller nedersta N -värden
- I Excel är det lätt för oss att summera ett cellintervall genom att använda SUM-funktionen. Ibland kan du behöva summera de minsta eller lägsta 3, 5 eller n siffrorna i ett dataintervall enligt skärmbilden nedan. I det här fallet kan SUMPRODUKTEN tillsammans med SMALL-funktionen hjälpa dig att lösa detta problem i Excel.
- Delsumma fakturabelopp efter ålder i Excel
- För att summera fakturabeloppen baserat på ålder som nedan skärmdump kan vara en vanlig uppgift i Excel, denna handledning visar hur man subtotalar fakturabelopp efter ålder med en normal SUMIF-funktion.
- Summa alla antal celler som ignorerar fel
- När man summerar ett intervall av tal som innehåller vissa felvärden kommer den normala SUM-funktionen inte att fungera korrekt. För att bara summera siffror och hoppa över felvärdena kan AGGREGATE-funktionen eller SUMMA tillsammans med IFERROR-funktionerna göra dig en tjänst.
De bästa Office-produktivitetsverktygen
Kutools för Excel - Hjälper dig att sticka ut från mängden
Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...
Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)
- En sekund att växla mellan dussintals öppna dokument!
- Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
- Ökar din produktivitet med 50 % när du visar och redigerar flera dokument.
- Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.
