SUMIF-funktionen i Excel och summera med flera villkor

Excel är ett otroligt funktionellt program. Även den inbyggda funktionsuppsättningen räcker för att utföra nästan alla uppgifter. Och förutom de vanliga, bekanta för många, finns det också de som få människor har hört talas om. Men samtidigt slutar de inte att vara användbara. De har en snävare specialisering, och det finns inte alltid ett behov av dem. Men om du känner till dem, kan de i ett kritiskt ögonblick vara mycket användbara.

Idag kommer vi att prata om en av sådana funktioner − SUMMESLIMN.

Om användaren står inför uppgiften att summera flera värden, med fokus på vissa kriterier, är det nödvändigt att använda funktionen SUMMESLIMN. Formeln som använder den här funktionen tar dessa villkor som argument, summerar sedan värdena som uppfyller dem, och sedan matas det hittade värdet in i cellen där det är skrivet. 

SUMIFS Funktion Detaljerad beskrivning

Innan du överväger funktionen SUMMESLIMN, måste du först förstå vad en enklare version av det är – SUMMESLI, eftersom det är på den som funktionen vi överväger är baserad. Var och en av oss är förmodligen redan bekanta med två funktioner som ofta används – SUMMA (utför summeringen av värden) och OM (testar ett värde mot ett specificerat villkor).

Om du kombinerar dem får du en annan funktion – SUMMESLI, som kontrollerar data mot användarspecificerade kriterier och summerar endast de siffror som uppfyller dessa kriterier. Om vi ​​talar om den engelska versionen av Excel, så kallas denna funktion SUMIF. Med enkla ord är -språknamnet en direkt översättning av det engelskspråkiga. Denna funktion kan användas för en mängd olika ändamål. I synnerhet kan den användas som ett alternativ VPR, det vill säga skriv ner

Den största skillnaden mellan funktionen SUMMESLIMN  från den vanliga funktionen SUMMESLI är att flera kriterier används. Dess syntax är ganska komplex vid första anblicken, men vid närmare granskning visar det sig att logiken i denna funktion är mycket enkel. Först måste du välja intervallet där data ska kontrolleras och sedan ställa in villkoren för överensstämmelse med vilka analysen kommer att utföras. Och en sådan operation kan utföras för ett ganska stort antal tillstånd.

Själva syntaxen är:

SUMIFS(summa_intervall, villkorsintervall1, villkor1, [villkorsintervall2, villkor2], …)

På lämpliga platser är det nödvändigt att placera uppsättningar av celler som är lämpliga i ett särskilt fall. 

Låt oss titta på argumenten mer detaljerat:

1. Sum_range. Detta argument, liksom intervallet för villkor 1 och villkor 1, krävs. Det är en uppsättning celler som behöver summeras.
2. Condition_range1. Detta är intervallet där villkoret kommer att kontrolleras. Det är ihopkopplat med nästa argument – ​​Condition1. Summeringen av värden som motsvarar kriteriet utförs inom de celler som anges i föregående argument.
3. Skick1. Detta argument specificerar kriterierna som ska kontrolleras mot. Den kan till exempel ställas in på detta sätt: "> 32".
4. Villkorsområde 2, Villkor 2... Här ställs följande villkor in på samma sätt. Om fler än ett fåtal villkor behöver specificeras, läggs argumenten Villkorsområde 3 och Villkor 3 till. Syntaxen är densamma för följande argument.

Funktionen tillåter maximal bearbetning av upp till 127 par av villkor och intervall. 

Du kan använda den på flera områden samtidigt (vi kommer bara att ge några, listan är faktiskt ännu längre):

1. Bokföring. Det är till exempel bra att använda funktionen SUMMESLIMN att skapa sammanfattande rapporter per kvartal för till exempel utgifter över en viss summa. Eller skapa en rapport om en produkt från en viss priskategori.
2. Försäljningsledning. Även här kan funktionen vara mycket användbar. Vi står till exempel inför uppgiften att endast summera kostnaden för varor som sålts till en viss kund vid en viss tidpunkt. Och i en sådan situation, funktionen SUMMESLIMN kan vara till stor hjälp.
3. Utbildning. Vi kommer att ge fler praktiska exempel från detta område idag. I synnerhet kan du använda den för att få en sammanfattning av elevbetyg. Du kan välja för ett enskilt ämne eller för individuella betyg. En person kan omedelbart ställa in flera kriterier enligt vilka bedömningen kommer att väljas, vilket är mycket bekvämt och kan spara mycket tid.

Som du kan se är utbudet av applikationer för denna funktion mycket brett. Men detta är inte dess enda förtjänst. Låt oss ta en titt på några fler fördelar som den här funktionen har:

1. Möjlighet att sätta flera kriterier. Varför är detta en fördel? Du kan använda den normala funktionen SUMMESLI! Och allt för att det är bekvämt. Det finns inget behov av att göra separata beräkningar för vart och ett av kriterierna. Alla åtgärder kan programmeras i förväg, även innan. hur datatabellen kommer att bildas. Detta är en stor tidsbesparing.
2. Automatisering. Den moderna tiden är automationstiden. Endast den som vet hur man korrekt automatiserar sitt arbete kan tjäna mycket. Det är därför förmågan att behärska Excel och funktionen SUMMESLIMN i synnerhet är så viktigt för alla som vill bygga en karriär. Genom att känna till en funktion kan du utföra flera åtgärder samtidigt, som en. Och här går vi vidare till nästa fördel med denna funktion.
3. Sparar tid. Bara på grund av att en funktion utför flera uppgifter samtidigt.
4. Enkelhet. Trots det faktum att syntaxen är ganska tung vid första anblicken på grund av dess skrymmande, är faktiskt logiken i denna funktion väldigt enkel. Först väljs ett dataintervall, sedan ett värdeintervall, som kommer att kontrolleras för överensstämmelse med ett visst villkor. Och självklart måste även själva tillståndet specificeras. Och så flera gånger. Faktum är att denna funktion är baserad på endast en logisk konstruktion, vilket gör den enklare än den välkända VPR trots att den kan användas för samma ändamål, även med hänsyn till ett större antal kriterier. 

Funktioner för att använda SUMIFS-funktionen

Det finns flera funktioner för att använda denna funktion som du måste vara uppmärksam på. Först och främst ignorerar den här funktionen intervall med textsträngar eller nollor, eftersom dessa datatyper inte kan läggas ihop i ett aritmetiskt mönster, bara sammanlänkas som strängar. Denna funktion kan inte göra detta. Du måste också vara uppmärksam på följande villkor:

1. Du kan använda dessa typer av värden som villkor för att välja celler för att ytterligare lägga till värdena som finns i dem: numeriska värden, booleska uttryck, cellreferenser och så vidare. 
2. Om text, logiska uttryck eller matematiska tecken kontrolleras, specificeras sådana kriterier med citattecken.
3. Kan inte använda termer som är längre än 255 tecken.
4. Det är möjligt att använda ungefärliga kriterier för att välja värden med hjälp av jokertecken. Frågetecknet används för att ersätta ett enstaka tecken, och multiplikationstecknet (asterisk) behövs för att ersätta flera tecken. 
5. Booleska värden som ligger i summeringsintervallet konverteras automatiskt till numeriska värden enligt deras typ. Således förvandlas värdet "TRUE" till ett och "FALSE" - till noll. 
6. Om ett #VALUE! fel visas i en cell betyder det att antalet celler i villkors- och summeringsintervallen är olika. Du måste se till att storleken på dessa argument är densamma. 

Exempel på användning av SUMIFS-funktionen

Funktion SUMMESLIMN inte så komplicerat som det verkar vid första anblicken, visar det sig. Men för mer klarhet, låt oss titta på några praktiska exempel på hur du kan använda funktionen SUMMESLIMN. Detta kommer att göra det mycket lättare att fördjupa sig i ämnet.

Dynamiskt intervall för tillståndssummering

Så låt oss börja med det första exemplet. Låt oss säga att vi har en tabell som innehåller information om hur eleverna klarar av läroplanen i ett visst ämne. Det finns en uppsättning betyg, prestation bedöms på en 10-gradig skala. Uppgiften är att hitta betyget på provet för de elever vars efternamn börjar med bokstaven A och deras lägsta poäng är 5.

Tabellen ser ut så här.

För att vi ska kunna beräkna totalpoängen baserat på kriterierna som beskrivs ovan måste vi tillämpa följande formel.

Låt oss beskriva argumenten mer detaljerat:

1. C3:C14 är vårt summeringsintervall. I vårt fall sammanfaller det med tillståndsintervallet. Från det kommer att väljas poäng som används för att beräkna beloppet, men endast de som faller under våra kriterier.
2. ">5" är vårt första villkor.
3. B3:B14 är det andra summeringsintervallet som bearbetas för att matcha det andra kriteriet. Vi ser att det inte finns någon sammanträffande med summeringsintervallet. Av detta drar vi slutsatsen att summeringsintervallet och tillståndets intervall kan vara identiska eller inte. 
4. "A*" är det andra intervallet, som anger urvalet av betyg endast för de elever vars efternamn börjar med A. I vårt fall betyder en asterisk valfritt antal tecken. 

Efter beräkningar får vi följande tabell.

Som du kan se sammanfattade formeln värdena baserat på det dynamiska intervallet och baserat på de villkor som anges av användaren.

Selektiv summering efter villkor i Excel

Anta nu att vi vill få information om vilka varor som skickades till vilka länder under det senaste kvartalet. Därefter hittar du de totala intäkterna från försändelser för juli och augusti.

Själva bordet ser ut så här. 

För att bestämma det slutliga resultatet behöver vi en sådan formel.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Som ett resultat av de beräkningar som utförs med denna formel får vi följande resultat.

Observera! Denna formel ser ganska stor ut även om vi bara använde två kriterier. Om dataintervallet är detsamma kan du avsevärt minska längden på formeln, som visas nedan.

SUMIFS-funktion för att summera värden över flera villkor

Låt oss nu ge ett annat exempel för att illustrera. I det här fallet förblir tabellen densamma som i föregående fall. 

Vi använder följande formel (men vi skriver den som en matrisformel, det vill säga vi anger den genom tangentkombinationen CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Efter funktionen SUMMESLIMN kommer att summera matrisen av värden baserat på kriterierna som anges i formeln (det vill säga länderna i Kina och Georgien), den resulterande matrisen summeras av den vanliga funktionen BELOPP, som är skriven som en matrisformel.

Om villkoren passerades som en matriskonstant för mer än ett par, kommer formeln att ge ett felaktigt resultat.

Låt oss nu ta en titt på tabellen som innehåller summorna.

Som ni ser har vi lyckats. Du kommer definitivt att lyckas också. Stor framgång på detta område. Detta är en mycket enkel funktion som en person som precis har satt sin fot på vägen att lära sig Excel kan förstå. Och vi vet redan att funktionen SUMMESLIMN låter dig vara effektiv inom alla verksamhetsområden, från redovisning till även utbildning. Även om du bygger en karriär inom något annat område som inte har beskrivits ovan, kommer den här funktionen fortfarande hjälpa dig att tjäna pengar. Det är därför hon är värdefull.

Viktigast av allt, det låter dig spara tid, vilket tyvärr är en begränsad resurs. Det verkar som att det finns ett par sekunder för att tillämpa två funktioner, men när du måste utföra ett stort antal repetitiva operationer, så summerar dessa sekunder till timmar som kan spenderas på något annat. Så vi rekommenderar att du övar på att använda den här funktionen. Dessutom är det otroligt enkelt.