SUMIF-funksjon i Excel og summer etter flere forhold

Excel er et utrolig funksjonelt program. Selv det innebygde funksjonssettet er nok til å fullføre nesten enhver oppgave. Og foruten de vanlige, kjent for mange, er det også de som få mennesker har hørt om. Men samtidig slutter de ikke å være nyttige. De har en snevrere spesialisering, og det er ikke alltid behov for dem. Men hvis du vet om dem, kan de i et kritisk øyeblikk være veldig nyttige.

I dag skal vi snakke om en av slike funksjoner − SUMMESLIMN.

Hvis brukeren står overfor oppgaven med å summere opp flere verdier, med fokus på visse kriterier, er det nødvendig å bruke funksjonen SUMMESLIMN. Formelen som bruker denne funksjonen tar disse betingelsene som argumenter, summerer deretter verdiene som oppfyller dem, og deretter legges den funnet verdien inn i cellen der den er skrevet. 

SUMIFS-funksjon Detaljert beskrivelse

Før du vurderer funksjonen SUMMESLIMN, må du først forstå hva en enklere versjon av det er – SUMMESLI, siden det er på den funksjonen vi vurderer er basert. Hver av oss er sannsynligvis allerede kjent med to funksjoner som ofte brukes – SUM (utfører summeringen av verdier) og HVIS (tester en verdi mot en spesifisert tilstand).

Hvis du kombinerer dem, får du en annen funksjon – SUMMESLI, som sjekker dataene mot brukerspesifiserte kriterier og summerer kun de tallene som oppfyller disse kriteriene. Hvis vi snakker om den engelske versjonen av Excel, kalles denne funksjonen SUMIF. Med enkle ord er -språknavnet en direkte oversettelse av det engelskspråklige. Denne funksjonen kan brukes til en rekke formål. Spesielt kan den brukes som et alternativ VPR, altså skriv ned

Hovedforskjellen mellom funksjonen SUMMESLIMN  fra den vanlige funksjonen SUMMESLI er at flere kriterier brukes. Syntaksen er ganske kompleks ved første øyekast, men ved nærmere undersøkelse viser det seg at logikken til denne funksjonen er veldig enkel. Først må du velge området der dataene skal kontrolleres, og deretter angi betingelsene for samsvar som analysen skal utføres. Og en slik operasjon kan utføres for et ganske stort antall forhold.

Selve syntaksen er:

SUMMER(sum_område; tilstandsområde1; tilstand1; [tilstandsområde2; tilstand2], …)

På egnede steder er det nødvendig å plassere arrays av celler som er egnet i et bestemt tilfelle. 

La oss se på argumentene mer detaljert:

1. Sum_range. Dette argumentet, så vel som området for betingelse 1 og betingelse 1, er påkrevd. Det er et sett med celler som må summeres.
2. Tilstandsområde1. Dette er området hvor tilstanden vil bli sjekket. Det er sammenkoblet med det neste argumentet – Betingelse1. Summen av verdier som tilsvarer kriteriet utføres i cellene spesifisert i forrige argument.
3. Tilstand 1. Dette argumentet spesifiserer kriteriene som skal kontrolleres mot. Den kan for eksempel stilles inn på denne måten: “> 32”.
4. Tilstandsområde 2, Tilstand 2... Her er følgende betingelser satt på samme måte. Hvis mer enn noen få betingelser må spesifiseres, legges betingelsesområde 3 og betingelse 3 til. Syntaksen er den samme for følgende argumenter.

Funksjonen tillater maksimal behandling av opptil 127 par med forhold og områder. 

Du kan bruke den på flere områder samtidig (vi gir bare noen få, listen er faktisk enda lengre):

1. Regnskap. For eksempel er det greit å bruke funksjonen SUMMESLIMN å lage oppsummeringsrapporter, for eksempel kvartalsvis for utgifter over et visst beløp. Eller lag en rapport om ett produkt fra en bestemt priskategori.
2. Salgsledelse. Også her kan funksjonen være svært nyttig. For eksempel står vi overfor oppgaven med å summere kun kostnadene for varer som ble solgt til en bestemt kunde på et bestemt tidspunkt. Og i en slik situasjon, funksjonen SUMMESLIMN kan være svært nyttig.
3. Utdanning. Vi vil gi flere praktiske eksempler fra dette området i dag. Spesielt kan du bruke den til å få et sammendrag av elevkarakterer. Du kan velge for enkeltfag eller for individuelle karakterer. En person kan umiddelbart sette flere kriterier som vurderingen vil bli valgt etter, noe som er veldig praktisk og kan spare mye tid.

Som du kan se, er utvalget av applikasjoner for denne funksjonen veldig bredt. Men dette er ikke den eneste fordelen. La oss ta en titt på noen flere fordeler som denne funksjonen har:

1. Evne til å sette flere kriterier. Hvorfor er dette en fordel? Du kan bruke den vanlige funksjonen SUMMESLI! Og alt fordi det er praktisk. Det er ikke nødvendig å utføre separate beregninger for hvert av kriteriene. Alle handlinger kan programmeres på forhånd, også før. hvordan datatabellen vil bli dannet. Dette er en stor tidsbesparelse.
2. Automasjon. Den moderne tidsalder er automatiseringens tidsalder. Bare personen som vet hvordan han skal automatisere arbeidet sitt, kan tjene mye. Det er derfor muligheten til å mestre Excel og funksjonen SUMMESLIMN spesielt, er så viktig for enhver person som ønsker å bygge en karriere. Å kjenne én funksjon lar deg utføre flere handlinger samtidig, som én. Og her går vi videre til den neste fordelen med denne funksjonen.
3. Sparer tid. Bare på grunn av det faktum at en funksjon utfører flere oppgaver samtidig.
4. Enkelhet. Til tross for at syntaksen er ganske tung ved første øyekast på grunn av dens omfang, er logikken til denne funksjonen veldig enkel. Først velges et dataområde, deretter et verdiområde, som vil bli sjekket for samsvar med en bestemt betingelse. Og selvfølgelig må selve tilstanden også spesifiseres. Og så flere ganger. Faktisk er denne funksjonen basert på kun én logisk konstruksjon, noe som gjør den enklere enn den velkjente VPR til tross for at den kan brukes til samme formål, også under hensyntagen til et større antall kriterier. 

Funksjoner ved bruk av SUMIFS-funksjonen

Det er flere funksjoner ved bruk av denne funksjonen du må være oppmerksom på. Først av alt ignorerer denne funksjonen områder med tekststrenger eller nullverdier, siden disse datatypene ikke kan legges sammen i et aritmetisk mønster, bare sammenkobles som strenger. Denne funksjonen kan ikke gjøre dette. Du må også være oppmerksom på følgende forhold:

1. Du kan bruke disse typene verdier som betingelser for å velge celler for ytterligere å legge til verdiene som finnes i dem: numeriske verdier, boolske uttrykk, cellereferanser og så videre. 
2. Hvis tekst, logiske uttrykk eller matematiske tegn kontrolleres, spesifiseres slike kriterier gjennom anførselstegn.
3. Kan ikke bruke termer på mer enn 255 tegn.
4. Det er mulig å bruke omtrentlige kriterier for å velge verdier ved å bruke jokertegn. Spørsmålstegnet brukes til å erstatte et enkelt tegn, og multiplikasjonstegnet (stjerne) er nødvendig for å erstatte flere tegn. 
5. Boolske verdier som er i summeringsområdet konverteres automatisk til numeriske verdier i henhold til deres type. Dermed blir verdien "TRUE" til én, og "FALSE" - til null. 
6. Hvis en #VERDI! feil vises i en celle, betyr det at antall celler i tilstands- og summeringsområdet er forskjellig. Du må sørge for at størrelsen på disse argumentene er de samme. 

Eksempler på bruk av SUMIFS-funksjonen

Funksjon SUMMESLIMN ikke så komplisert som det ser ut ved første øyekast, viser det seg. Men for mer klarhet, la oss se på noen praktiske eksempler på hvordan du kan bruke funksjonen SUMMESLIMN. Dette vil gjøre det mye lettere å fordype seg i temaet.

Dynamisk område for tilstandssummering

Så la oss starte med det første eksemplet. La oss si at vi har en tabell som inneholder informasjon om hvordan elevene takler læreplanen i et bestemt fag. Det er et sett med karakterer, prestasjon vurderes på en 10-punkts skala. Oppgaven er å finne karakteren for eksamen til de studentene hvis etternavn begynner med bokstaven A, og deres minimumspoeng er 5.

Tabellen ser slik ut.

For at vi skal kunne beregne den totale poengsummen basert på kriteriene beskrevet ovenfor, må vi bruke følgende formel.

La oss beskrive argumentene mer detaljert:

1. C3:C14 er summeringsområdet vårt. I vårt tilfelle faller det sammen med tilstandsområdet. Fra den vil det bli valgt poeng som brukes til å beregne beløpet, men kun de som faller inn under våre kriterier.
2. ">5" er vår første betingelse.
3. B3:B14 er det andre summeringsområdet som behandles for å matche det andre kriteriet. Vi ser at det ikke er sammenfall med summeringsområdet. Fra dette konkluderer vi med at summeringsområdet og tilstandens rekkevidde kan være identiske eller ikke. 
4. "A*" er det andre området, som spesifiserer utvalget av karakterer kun for de elevene hvis etternavn begynner med A. I vårt tilfelle betyr en stjerne et hvilket som helst antall tegn. 

Etter beregninger får vi følgende tabell.

Som du kan se, oppsummerte formelen verdiene basert på det dynamiske området og basert på betingelsene spesifisert av brukeren.

Selektiv summering etter tilstand i Excel

Anta nå at vi ønsker å få informasjon om hvilke varer som ble sendt til hvilke land i løpet av siste kvartal. Deretter finner du den totale inntekten fra forsendelser for juli og august.

Selve bordet ser slik ut. 

For å bestemme det endelige resultatet, trenger vi en slik formel.

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

Som et resultat av beregningene utført av denne formelen, får vi følgende resultat.

Oppmerksomhet! Denne formelen ser ganske stor ut selv om vi bare brukte to kriterier. Hvis dataområdet er det samme, kan du redusere lengden på formelen betydelig, som vist nedenfor.

SUMIFS-funksjon for å summere verdier over flere forhold

La oss nå gi et annet eksempel for å illustrere. I dette tilfellet forblir tabellen den samme som i forrige tilfelle. 

Vi bruker følgende formel (men vi skriver den som en matriseformel, det vil si at vi skriver den inn gjennom tastekombinasjonen CTRL + SHIFT + ENTER).

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

Etter funksjonen SUMMESLIMN vil summere matrisen med verdier basert på kriteriene spesifisert i formelen (det vil si landene i Kina og Georgia), den resulterende matrisen summeres av den vanlige funksjonen SUM, som er skrevet som en matriseformel.

Hvis betingelsene ble passert som en matrisekonstant for mer enn ett par, vil formelen gi et feil resultat.

La oss nå ta en titt på tabellen som inneholder totalsummene.

Som du ser har vi lyktes. Du vil garantert lykkes også. Stor suksess på dette feltet. Dette er en veldig enkel funksjon som en person som nettopp har satt sin fot på veien for å lære Excel kan forstå. Og vi vet allerede at funksjonen SUMMESLIMN lar deg være effektiv innen ethvert aktivitetsfelt, fra regnskap til til og med utdanning. Selv om du bygger en karriere innen et annet område som ikke er beskrevet ovenfor, vil denne funksjonen fortsatt hjelpe deg å tjene penger. Det er derfor hun er verdifull.

Viktigst av alt, det lar deg spare tid, som dessverre er en begrenset ressurs. Det ser ut til at det er et par sekunder å bruke to funksjoner, men når du må utføre et stort antall repeterende operasjoner, summerer disse sekundene seg til timer som kan brukes på noe annet. Så vi anbefaler at du øver deg på å bruke denne funksjonen. Dessuten er det utrolig enkelt.