SUMIF-funktionen i Excel og summer efter flere betingelser

Excel er et utroligt funktionelt program. Selv det indbyggede funktionssæt er nok til at fuldføre næsten enhver opgave. Og udover de standard, som mange kender, er der også dem, som de færreste har hørt om. Men samtidig holder de ikke op med at være nyttige. De har en snævrere specialisering, og der er ikke altid behov for dem. Men hvis du kender til dem, så kan de på et kritisk tidspunkt være meget nyttige.

I dag vil vi tale om en af ​​sådanne funktioner − SUMMESLIMN.

Hvis brugeren står over for opgaven med at opsummere flere værdier med fokus på bestemte kriterier, så er det nødvendigt at bruge funktionen SUMMESLIMN. Formlen, der bruger denne funktion, tager disse betingelser som argumenter, summerer derefter de værdier, der opfylder dem, og derefter indtastes den fundne værdi i cellen, hvor den er skrevet. 

SUMIFS Funktion Detaljeret beskrivelse

Før du overvejer funktionen SUMMESLIMN, skal du først forstå, hvad en enklere version af det er – SUMMESLI, da det er på den, den funktion vi overvejer er baseret på. Hver af os er sikkert allerede bekendt med to funktioner, der ofte bruges – SUM (foretager summeringen af ​​værdier) og HVIS (tester en værdi mod en specificeret tilstand).

Hvis du kombinerer dem, får du en anden funktion – SUMMESLI, som kontrollerer dataene mod brugerspecificerede kriterier og summerer kun de tal, der opfylder disse kriterier. Hvis vi taler om den engelske version af Excel, så kaldes denne funktion SUMIF. Med enkle ord er det -sprogede navn en direkte oversættelse af det engelsksprogede. Denne funktion kan bruges til en række forskellige formål. Det kan især bruges som et alternativ VPR, altså skrive ned

Den største forskel mellem funktionen SUMMESLIMN  fra den sædvanlige funktion SUMMESLI er, at der anvendes flere kriterier. Dens syntaks er ret kompleks ved første øjekast, men ved nærmere undersøgelse viser det sig, at logikken i denne funktion er meget enkel. Først skal du vælge det område, hvor dataene skal kontrolleres, og derefter indstille betingelserne for overholdelse, som analysen skal udføres. Og en sådan operation kan udføres for et ret stort antal forhold.

Selve syntaksen er:

SUMMER(sum_område; betingelse_område1; betingelse1; [tilstand_område2; betingelse2], …)

På egnede steder er det nødvendigt at placere arrays af celler, der er egnede i et bestemt tilfælde. 

Lad os se på argumenterne mere detaljeret:

1. Sum_område. Dette argument, såvel som intervallet af betingelse 1 og betingelse 1, er påkrævet. Det er et sæt celler, der skal summeres.
2. Tilstandsområde1. Dette er området, hvor tilstanden vil blive kontrolleret. Det er parret med det næste argument – ​​Betingelse1. Summeringen af ​​værdier, der svarer til kriteriet, udføres inden for de celler, der er angivet i det foregående argument.
3. Tilstand 1. Dette argument specificerer de kriterier, der skal kontrolleres i forhold til. Den kan f.eks. indstilles på denne måde: "> 32".
4. Betingelsesområde 2, Betingelse 2... Her er følgende betingelser indstillet på samme måde. Hvis mere end et par betingelser skal angives, tilføjes argumenterne Betingelsesområde 3 og Betingelse 3. Syntaksen er den samme for følgende argumenter.

Funktionen tillader maksimal behandling af op til 127 par betingelser og områder. 

Du kan bruge det på flere områder på én gang (vi giver kun nogle få, listen er faktisk endnu længere):

1. Regnskab. Det er for eksempel godt at bruge funktionen SUMMESLIMN at oprette oversigtsrapporter efter kvartal for f.eks. udgifter over et bestemt beløb. Eller opret en rapport om et produkt fra en bestemt priskategori.
2. Salgsledelse. Også her kan funktionen være meget nyttig. For eksempel står vi over for opgaven kun at opsummere omkostningerne ved varer, der blev solgt til en bestemt kunde på et bestemt tidspunkt. Og i sådan en situation er funktionen SUMMESLIMN kan være meget nyttigt.
3. Uddannelse. Vi vil give flere praktiske eksempler fra dette område i dag. Især kan du bruge den til at få en oversigt over elevernes karakterer. Du kan vælge for et enkelt fag eller for individuelle karakterer. En person kan straks indstille flere kriterier, som vurderingen vil blive udvalgt efter, hvilket er meget praktisk og kan spare meget tid.

Som du kan se, er anvendelsesområdet for denne funktion meget bredt. Men dette er ikke dens eneste fortjeneste. Lad os tage et kig på nogle flere fordele, som denne funktion har:

1. Evne til at opstille flere kriterier. Hvorfor er dette en fordel? Du kan bruge den normale funktion SUMMESLI! Og alt sammen fordi det er praktisk. Det er ikke nødvendigt at udføre særskilte beregninger for hvert af kriterierne. Alle handlinger kan programmeres på forhånd, selv før. hvordan datatabellen vil blive dannet. Dette er en stor tidsbesparelse.
2. Automatisering. Den moderne tidsalder er automatiseringens tidsalder. Kun den person, der ved, hvordan man korrekt automatiserer sit arbejde, kan tjene meget. Derfor er evnen til at mestre Excel og funktionen SUMMESLIMN i særdeleshed er så vigtig for enhver person, der ønsker at opbygge en karriere. At kende én funktion giver dig mulighed for at udføre flere handlinger på én gang, som én. Og her går vi videre til den næste fordel ved denne funktion.
3. Sparer tid. Bare på grund af det faktum, at én funktion udfører flere opgaver på én gang.
4. Enkelhed. På trods af det faktum, at syntaksen er ret tung ved første øjekast på grund af dens omfang, er logikken i denne funktion faktisk meget enkel. Først vælges en række data, derefter en række værdier, som vil blive kontrolleret for overholdelse af en bestemt betingelse. Og selve tilstanden skal selvfølgelig også specificeres. Og så flere gange. Faktisk er denne funktion kun baseret på én logisk konstruktion, hvilket gør den enklere end den velkendte VPR på trods af, at den kan bruges til samme formål, også under hensyntagen til et større antal kriterier. 

Funktioner ved brug af SUMIFS-funktionen

Der er flere funktioner ved at bruge denne funktion, som du skal være opmærksom på. Først og fremmest ignorerer denne funktion områder med tekststrenge eller nuller, da disse datatyper ikke kan tilføjes sammen i et aritmetisk mønster, kun sammenkædes som strenge. Denne funktion kan ikke gøre dette. Du skal også være opmærksom på følgende forhold:

1. Du kan bruge disse typer værdier som betingelser for valg af celler for yderligere at tilføje de værdier, der er indeholdt i dem: numeriske værdier, booleske udtryk, cellereferencer og så videre. 
2. Hvis tekst, logiske udtryk eller matematiske tegn kontrolleres, er sådanne kriterier angivet i anførselstegn.
3. Kan ikke bruge termer på mere end 255 tegn.
4. Det er muligt at bruge omtrentlige kriterier til at vælge værdier ved hjælp af jokertegn. Spørgsmålstegnet bruges til at erstatte et enkelt tegn, og multiplikationstegnet (stjerne) er nødvendigt for at erstatte flere tegn. 
5. Booleske værdier, der er i summeringsområdet, konverteres automatisk til numeriske værdier i henhold til deres type. Således bliver værdien "TRUE" til én, og "FALSE" - til nul. 
6. Hvis en #VALUE! fejl vises i en celle, betyder det, at antallet af celler i tilstands- og summeringsintervallet er forskelligt. Du skal sikre dig, at størrelserne på disse argumenter er de samme. 

Eksempler på brug af SUMIFS-funktionen

Funktion SUMMESLIMN ikke så kompliceret, som det ser ud ved første øjekast, viser det sig. Men for mere klarhed, lad os se på nogle praktiske eksempler på, hvordan du kan bruge funktionen SUMMESLIMN. Dette vil gøre det meget lettere at fordybe sig i emnet.

Betingelsessummeringsdynamisk område

Så lad os starte med det første eksempel. Lad os sige, at vi har en tabel, der indeholder information om, hvordan eleverne klarer pensum i et bestemt fag. Der er et sæt karakterer, præstation bedømmes på en 10-skala. Opgaven er at finde karakteren til eksamen for de elever, hvis efternavn begynder med bogstavet A, og deres minimumsscore er 5.

Bordet ser sådan ud.

For at vi kan beregne den samlede score baseret på kriterierne beskrevet ovenfor, skal vi anvende følgende formel.

Lad os beskrive argumenterne mere detaljeret:

1. C3:C14 er vores summeringsområde. I vores tilfælde falder det sammen med tilstandsintervallet. Herfra vil der blive udvalgt point, der bruges til at beregne beløbet, men kun dem, der falder ind under vores kriterier.
2. ">5" er vores første betingelse.
3. B3:B14 er det andet summeringsområde, der behandles for at matche det andet kriterium. Vi ser, at der ikke er noget sammenfald med summeringsområdet. Ud fra dette konkluderer vi, at summeringsområdet og tilstandens rækkevidde kan være identiske eller ikke. 
4. "A*" er det andet område, som kun angiver valget af karakterer for de elever, hvis efternavn begynder med A. I vores tilfælde betyder en stjerne et vilkårligt antal tegn. 

Efter beregninger får vi følgende tabel.

Som du kan se, opsummerede formlen værdierne baseret på det dynamiske område og baseret på betingelserne angivet af brugeren.

Selektiv summering efter betingelse i Excel

Antag nu, at vi ønsker at få information om, hvilke varer der blev sendt til hvilke lande i løbet af det sidste kvartal. Find derefter den samlede omsætning fra forsendelser for juli og august.

Selve bordet ser sådan ud. 

For at bestemme det endelige resultat har vi brug for en sådan formel.

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

Som et resultat af beregningerne udført med denne formel får vi følgende resultat.

OBS! Denne formel ser ret stor ud, selvom vi kun brugte to kriterier. Hvis dataområdet er det samme, så kan du reducere længden af ​​formlen betydeligt, som vist nedenfor.

SUMIFS-funktion til at summere værdier på tværs af flere forhold

Lad os nu give et andet eksempel til illustration. I dette tilfælde forbliver tabellen den samme som i det foregående tilfælde. 

Vi bruger følgende formel (men vi skriver den som en matrixformel, det vil sige, vi indtaster den gennem tastekombinationen CTRL + SHIFT + ENTER).

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

Efter funktionen SUMMESLIMN vil summere matrixen af ​​værdier baseret på kriterierne specificeret i formlen (det vil sige landene i Kina og Georgien), den resulterende matrix summeres af den sædvanlige funktion SUM, som er skrevet som en matrixformel.

Hvis betingelserne blev bestået som en matrixkonstant for mere end et par, vil formlen give et forkert resultat.

Lad os nu tage et kig på tabellen, der indeholder totalerne.

Som du kan se, er det lykkedes. Du vil helt sikkert også lykkes. Stor succes på dette område. Dette er en meget simpel funktion, som en person, der lige har sat sine ben på vejen til at lære Excel, kan forstå. Og vi ved allerede, at funktionen SUMMESLIMN giver dig mulighed for at være effektiv inden for ethvert aktivitetsområde, fra regnskab til endda uddannelse. Selvom du bygger en karriere inden for et hvilket som helst andet område, der ikke er beskrevet ovenfor, vil denne funktion stadig hjælpe dig med at tjene penge. Derfor er hun værdifuld.

Vigtigst af alt giver det dig mulighed for at spare tid, hvilket desværre er en begrænset ressource. Det ser ud til, at der er et par sekunder til at anvende to funktioner, men når du skal udføre et stort antal gentagne operationer, så summer disse sekunder op til timer, der kunne bruges på noget andet. Så vi anbefaler, at du øver dig i at bruge denne funktion. Desuden er det utrolig enkelt.