SUMIF-funksie in Excel en som deur verskeie voorwaardes

Excel is 'n ongelooflike funksionele program. Selfs die ingeboude kenmerkstel is genoeg om byna enige taak te voltooi. En naas die standaards, bekend aan baie, is daar ook dié waarvan min mense al gehoor het. Maar terselfdertyd hou hulle nie op om nuttig te wees nie. Hulle het 'n nouer spesialisasie, en daar is nie altyd 'n behoefte daaraan nie. Maar as jy van hulle weet, dan kan hulle op 'n kritieke oomblik baie nuttig wees.

Vandag sal ons praat oor een van sulke funksies - SUMMESLIMN.

As die gebruiker gekonfronteer word met die taak om verskeie waardes op te som, met die fokus op sekere kriteria, dan is dit nodig om die funksie te gebruik SUMMESLIMN. Die formule wat hierdie funksie gebruik, neem hierdie voorwaardes as argumente, som dan die waardes op wat daaraan voldoen, en dan word die gevind waarde in die sel ingevoer waarin dit geskryf is. 

SUMIFS-funksie Gedetailleerde beskrywing

Voordat u die funksie oorweeg SUMMESLIMN, moet jy eers verstaan ​​wat 'n eenvoudiger weergawe daarvan is - SUMMESLI, aangesien dit daarop is dat die funksie wat ons oorweeg, gebaseer is. Elkeen van ons is waarskynlik reeds vertroud met twee funksies wat dikwels gebruik word – som (voer die opsomming van waardes uit) en AS (toets 'n waarde teen 'n gespesifiseerde toestand).

As jy hulle kombineer, kry jy nog 'n funksie – SUMMESLI, wat die data teen gebruiker-gespesifiseerde kriteria kontroleer en net daardie getalle optel wat aan daardie kriteria voldoen. As ons praat oor die Engelse weergawe van Excel, dan word hierdie funksie SUMIF genoem. In eenvoudige woorde is die -taalnaam 'n direkte vertaling van die Engelstalige een. Hierdie funksie kan vir 'n verskeidenheid doeleindes gebruik word. Dit kan veral as 'n alternatief gebruik word VPR, dit wil sê, skryf neer

Die belangrikste verskil tussen die funksie SUMMESLIMN  vanaf die gewone funksie SUMMESLI is dat verskeie kriteria gebruik word. Die sintaksis daarvan is met die eerste oogopslag redelik kompleks, maar by nadere ondersoek blyk dit dat die logika van hierdie funksie baie eenvoudig is. Eerstens moet u die reeks kies waar die data nagegaan sal word, en dan die voorwaardes stel vir voldoening waaraan die analise uitgevoer sal word. En so 'n operasie kan uitgevoer word vir 'n redelike groot aantal toestande.

Die sintaksis self is:

SOMME(som_reeks; toestand_reeks1; voorwaarde1; [voorwaarde_reeks2; voorwaarde2], …)

Op geskikte plekke is dit nodig om skikkings selle te plaas wat geskik is in 'n spesifieke geval. 

Kom ons kyk na die argumente in meer besonderhede:

1. Som_reeks. Hierdie argument, sowel as die reeks van voorwaarde 1 en voorwaarde 1, word vereis. Dit is 'n stel selle wat opgetel moet word.
2. Toestand_reeks1. Dit is die reeks waar die toestand nagegaan sal word. Dit word gepaard met die volgende argument – ​​Voorwaarde1. Die opsomming van waardes wat ooreenstem met die maatstaf word uitgevoer binne die selle wat in die vorige argument gespesifiseer is.
3. Toestand 1. Hierdie argument spesifiseer die kriteria waarteen gekontroleer moet word. Dit kan byvoorbeeld op hierdie manier gestel word: "> 32".
4. Toestandreeks 2, Toestand 2... Hier word die volgende voorwaardes op dieselfde manier gestel. As meer as 'n paar voorwaardes gespesifiseer moet word, word die voorwaardereeks 3 en voorwaarde 3 argumente bygevoeg. Die sintaksis is dieselfde vir die volgende argumente.

Die funksie laat maksimum verwerking van tot 127 pare toestande en reekse toe. 

Jy kan dit in verskeie gebiede gelyktydig gebruik (ons sal net 'n paar gee, die lys is eintlik selfs langer):

1. Rekeningkunde. Dit is byvoorbeeld goed om die funksie te gebruik SUMMESLIMN om opsommende verslae te skep, byvoorbeeld per kwartaal vir besteding oor 'n sekere bedrag. Of skep 'n verslag oor een produk uit 'n sekere pryskategorie.
2. Verkope bestuur. Ook hier kan die funksie baie nuttig wees. Ons staan ​​byvoorbeeld voor die taak om slegs die koste van goedere op te som wat op 'n sekere tyd aan 'n sekere klant verkoop is. En in so 'n situasie, die funksie SUMMESLIMN kan baie nuttig wees.
3. Onderwys. Ons sal vandag meer praktiese voorbeelde uit hierdie gebied gee. In die besonder, kan jy dit gebruik om 'n opsomming van studente grade te kry. Jy kan kies vir 'n enkele vak of vir individuele grade. 'n Persoon kan onmiddellik verskeie kriteria stel waarvolgens die assessering gekies sal word, wat baie gerieflik is en baie tyd kan bespaar.

Soos u kan sien, is die reeks toepassings vir hierdie funksie baie wyd. Maar dit is nie sy enigste verdienste nie. Kom ons kyk na nog 'n paar voordele wat hierdie kenmerk het:

1. Vermoë om verskeie kriteria te stel. Hoekom is dit 'n voordeel? Jy kan die normale funksie gebruik SUMMESLI! En dit alles omdat dit gerieflik is. Dit is nie nodig om afsonderlike berekeninge vir elk van die kriteria uit te voer nie. Alle aksies kan vooraf geprogrammeer word, selfs voor. hoe die datatabel gevorm sal word. Dit is 'n wonderlike tydbespaarder.
2. Outomatisering. Die moderne era is die era van outomatisering. Slegs die persoon wat weet hoe om sy werk behoorlik te outomatiseer kan baie verdien. Dit is hoekom die vermoë om Excel en die funksie te bemeester SUMMESLIMN veral, is so belangrik vir enige persoon wat 'n loopbaan wil bou. Om een ​​funksie te ken, laat jou toe om verskeie aksies gelyktydig uit te voer, as een. En hier gaan ons aan na die volgende voordeel van hierdie kenmerk.
3. Spaar tyd. Net as gevolg van die feit dat een funksie verskeie take gelyktydig verrig.
4. Eenvoud. Ten spyte van die feit dat die sintaksis met die eerste oogopslag redelik swaar is as gevolg van sy omvang, is die logika van hierdie funksie eintlik baie eenvoudig. Eerstens word 'n reeks data gekies, dan 'n reeks waardes, wat nagegaan sal word vir voldoening aan 'n sekere voorwaarde. En natuurlik moet die toestand self ook gespesifiseer word. En so verskeie kere. Trouens, hierdie funksie is op slegs een logiese konstruk gebaseer, wat dit eenvoudiger maak as die bekende VPR ten spyte van die feit dat dit vir dieselfde doeleindes gebruik kan word, ook met inagneming van 'n groter aantal kriteria. 

Kenmerke van die gebruik van die SUMIFS-funksie

Daar is verskeie kenmerke van die gebruik van hierdie funksie waaraan jy aandag moet gee. Eerstens ignoreer hierdie funksie reekse met teksstringe of nulpunte, aangesien hierdie datatipes nie in 'n rekenkundige patroon saamgevoeg kan word nie, net soos stringe aaneengeskakel word. Hierdie funksie kan dit nie doen nie. U moet ook aandag gee aan die volgende voorwaardes:

1. U kan hierdie tipe waardes gebruik as voorwaardes vir die seleksie van selle om die waardes wat daarin vervat is verder by te voeg: numeriese waardes, Boolese uitdrukkings, selverwysings, ensovoorts. 
2. As teks, logiese uitdrukkings of wiskundige tekens nagegaan word, word sulke kriteria deur aanhalingstekens gespesifiseer.
3. Kan nie terme langer as 255 karakters gebruik nie.
4. Dit is moontlik om benaderde kriteria te gebruik vir die keuse van waardes met behulp van wildcards. Die vraagteken word gebruik om 'n enkele karakter te vervang, en die vermenigvuldigingsteken (sterretjie) is nodig om veelvuldige karakters te vervang. 
5. Boole-waardes wat in die sombereik is, word outomaties omgeskakel na numeriese waardes volgens hul tipe. Dus, die waarde "WAAR" verander in een, en "ONWAAR" - in nul. 
6. As 'n #WAARDE! fout in 'n sel verskyn, beteken dit dat die aantal selle in die toestand- en sombereik verskil. Jy moet seker maak dat die groottes van hierdie argumente dieselfde is. 

Voorbeelde van die gebruik van die SUMIFS-funksie

funksie SUMMESLIMN nie so ingewikkeld soos dit met die eerste oogopslag lyk nie, blyk dit. Maar vir meer duidelikheid, kom ons kyk na 'n paar praktiese voorbeelde van hoe jy die funksie kan gebruik SUMMESLIMN. Dit sal dit baie makliker maak om in die onderwerp te delf.

Toestand-opsomming dinamiese omvang

Kom ons begin dus met die eerste voorbeeld. Kom ons sê ons het 'n tabel wat inligting bevat oor hoe studente die kurrikulum in 'n spesifieke vak hanteer. Daar is 'n stel grade, prestasie word op 'n 10-punt skaal geassesseer. Die taak is om die graad vir die eksamen te vind van daardie studente wie se van met die letter A begin, en hul minimum telling is 5.

Die tafel lyk so.

Om die totale telling op grond van die kriteria hierbo beskryf te kan bereken, moet ons die volgende formule toepas.

Kom ons beskryf die argumente in meer besonderhede:

1. C3:C14 is ons opsommingsreeks. In ons geval val dit saam met die toestandreeks. Daaruit sal punte gekies word wat gebruik word om die bedrag te bereken, maar slegs dié wat onder ons kriteria val.
2. ">5" is ons eerste voorwaarde.
3. B3:B14 is die tweede sombereik wat verwerk word om by die tweede kriterium te pas. Ons sien dat daar geen toeval is met die sombereik nie. Hieruit lei ons af dat die omvang van opsomming en die omvang van die toestand identies mag wees of nie. 
4. “A*” is die tweede reeks, wat die seleksie van punte net spesifiseer vir daardie studente wie se van met A begin. In ons geval beteken 'n asterisk enige aantal karakters. 

Na berekeninge kry ons die volgende tabel.

Soos u kan sien, het die formule die waardes opgesom op grond van die dinamiese reeks en gebaseer op die voorwaardes wat deur die gebruiker gespesifiseer is.

Selektiewe opsomming volgens toestand in Excel

Gestel nou ons wil inligting kry oor watter goedere gedurende die laaste kwartaal na watter lande verskeep is. Vind daarna die totale inkomste uit verskepings vir Julie en Augustus.

Die tafel self lyk so. 

Om die finale resultaat te bepaal, benodig ons so 'n formule.

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

As gevolg van die berekeninge wat deur hierdie formule uitgevoer word, kry ons die volgende resultaat.

Aandag! Hierdie formule lyk redelik groot al het ons net twee kriteria gebruik. As die datareeks dieselfde is, kan jy die lengte van die formule aansienlik verminder, soos hieronder getoon.

SUMIFS-funksie om waardes oor verskeie toestande op te som

Kom ons gee nou nog 'n voorbeeld om te illustreer. In hierdie geval bly die tabel dieselfde as in die vorige geval. 

Ons gebruik die volgende formule (maar ons skryf dit as 'n skikkingsformule, dit wil sê ons voer dit in deur die sleutelkombinasie CTRL + SHIFT + ENTER).

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

Na die funksie SUMMESLIMN sal die reeks waardes optel op grond van die kriteria wat in die formule gespesifiseer word (dit is die lande van China en Georgië), word die resulterende skikking opgesom deur die gewone funksie SOM, wat as 'n skikkingsformule geskryf word.

As die voorwaardes as 'n skikkingskonstante vir meer as een paar deurgegee is, sal die formule 'n verkeerde resultaat gee.

Kom ons kyk nou na die tabel wat die totale bevat.

Soos jy kan sien, het ons daarin geslaag. Jy sal beslis ook slaag. Groot sukses op hierdie gebied. Dit is 'n baie eenvoudige funksie wat 'n persoon wat pas 'n voet op die pad gesit het om Excel te leer, kan verstaan. En ons weet reeds dat die funksie SUMMESLIMN laat jou toe om effektief te wees in enige veld van aktiwiteit, van rekeningkunde tot selfs onderwys. Selfs as jy 'n loopbaan bou in enige ander area wat nie hierbo beskryf is nie, sal hierdie kenmerk jou steeds help om geld te verdien. Dit is hoekom sy waardevol is.

Die belangrikste is dat dit jou toelaat om tyd te bespaar, wat ongelukkig 'n beperkte hulpbron is. Dit wil voorkom asof daar 'n paar sekondes is om twee funksies toe te pas, maar wanneer jy 'n groot aantal herhalende bewerkings moet uitvoer, dan tel hierdie sekondes ure op wat aan iets anders bestee kan word. Ons beveel dus aan dat jy oefen om hierdie kenmerk te gebruik. Boonop is dit ongelooflik eenvoudig.