SUMIF-i funktsioon Excelis ja summa mitme tingimusega

Excel on uskumatult funktsionaalne programm. Isegi sisseehitatud funktsioonide komplekt on piisav peaaegu iga ülesande täitmiseks. Ja peale paljudele tuttavate tavapäraste on ka neid, millest vähesed on kuulnud. Kuid samal ajal ei lakka nad olemast kasulikud. Neil on kitsam spetsialiseerumine ja nende järele pole alati vajadust. Aga kui sa neist tead, siis võivad need kriitilisel hetkel väga kasulikud olla.

Täna räägime ühest sellisest funktsioonist − SUMMESLIMN.

Kui kasutaja seisab silmitsi ülesandega summeerida mitu väärtust, keskendudes teatud kriteeriumidele, on vaja kasutada funktsiooni SUMMESLIMN. Seda funktsiooni kasutav valem võtab need tingimused argumentidena, seejärel summeerib neile vastavad väärtused ja seejärel sisestatakse leitud väärtus lahtrisse, kuhu see on kirjutatud. 

SUMIFS-i funktsiooni üksikasjalik kirjeldus

Enne funktsiooni kaalumist SUMMESLIMN, peate esmalt aru saama, mis on selle lihtsam versioon – SUMMESLI, kuna sellel põhineb vaadeldav funktsioon. Igaüks meist on ilmselt juba tuttav kahe sageli kasutatava funktsiooniga – SUM (viib läbi väärtuste liitmise) ja IF (testib väärtust määratud tingimusega).

Kui ühendate need, saate teise funktsiooni - SUMMESLI, mis kontrollib andmeid kasutaja määratud kriteeriumide alusel ja summeerib ainult need arvud, mis vastavad neile kriteeriumidele. Kui me räägime Exceli ingliskeelsest versioonist, siis seda funktsiooni nimetatakse SUMIF. Lihtsamalt öeldes on -keelne nimi ingliskeelse nime otsetõlge. Seda funktsiooni saab kasutada erinevatel eesmärkidel. Eelkõige saab seda kasutada alternatiivina VPRst kirjutage üles

Peamine erinevus funktsioonide vahel SUMMESLIMN  tavalisest funktsioonist SUMMESLI on see, et kasutatakse mitmeid kriteeriume. Selle süntaks on esmapilgul üsna keeruline, kuid lähemal uurimisel selgub, et selle funktsiooni loogika on väga lihtne. Kõigepealt peate valima vahemiku, kus andmeid kontrollitakse, ja seejärel määrama analüüsi tegemise tingimused. Ja sellist toimingut saab teha üsna paljudel tingimustel.

Süntaks ise on järgmine:

SUMIFS(summa_vahemik, tingimuse_vahemik1, tingimus1, [tingimuse_vahemik2, tingimus2], …)

Sobivatesse kohtadesse on vaja panna konkreetsele juhtumile sobivad lahtrite massiivid. 

Vaatame argumente üksikasjalikumalt:

1. Summa_vahemik. See argument, samuti tingimuse 1 ja tingimuse 1 vahemik on nõutavad. See on lahtrite kogum, mis tuleb summeerida.
2. Tingimuse_vahemik1. See on vahemik, kus seisundit kontrollitakse. See on seotud järgmise argumendiga - tingimus1. Kriteeriumile vastavate väärtuste liitmine toimub eelmises argumendis määratud lahtrites.
3. Seisukord1. See argument määrab kriteeriumid, mille alusel kontrollida. Seda saab määrata näiteks järgmiselt: “> 32”.
4. Tingimuste vahemik 2, tingimus 2… Siin on järgmised tingimused määratud samal viisil. Kui on vaja määrata rohkem kui paar tingimust, lisatakse argumendid Tingimuste vahemik 3 ja Tingimus 3. Järgmiste argumentide süntaks on sama.

Funktsioon võimaldab maksimaalselt töödelda kuni 127 paari tingimusi ja vahemikke. 

Saate seda kasutada korraga mitmes valdkonnas (toome vaid mõned, nimekiri on tegelikult veelgi pikem):

1. Raamatupidamine. Näiteks on hea kasutada funktsiooni SUMMESLIMN koondaruannete loomiseks kvartalite kaupa näiteks teatud summat ületavate kulutuste kohta. Või koostage aruanne ühe kindla hinnakategooria toote kohta.
2. Müügijuhtimine. Ka siin võib funktsioon olla väga kasulik. Näiteks seisame silmitsi ülesandega summeerida ainult nende kaupade maksumus, mis müüdi teatud kliendile teatud ajahetkel. Ja sellises olukorras funktsioon SUMMESLIMN võib olla väga kasulik.
3. Haridus. Toome täna sellest vallast praktilisemaid näiteid. Eelkõige saate seda kasutada õpilaste hinnete kokkuvõtte saamiseks. Saate valida ühe aine või üksikute hinnete jaoks. Inimene saab koheselt seada mitu kriteeriumi, mille järgi hinnang valitakse, mis on väga mugav ja võib säästa palju aega.

Nagu näete, on selle funktsiooni rakenduste valik väga lai. Kuid see pole tema ainus eelis. Vaatame veel mõnda selle funktsiooni eelist.

1. Võimalus seada mitu kriteeriumi. Miks see eelis on? Võite kasutada tavalist funktsiooni SUMMESLI! Ja kõik sellepärast, et see on mugav. Iga kriteeriumi jaoks ei ole vaja eraldi arvutusi teha. Kõiki toiminguid saab ette programmeerida, isegi enne. kuidas andmetabel moodustatakse. See on suurepärane aja kokkuhoid.
2. Automatiseerimine. Kaasaegne ajastu on automatiseerimise ajastu. Ainult see, kes teab, kuidas oma tööd õigesti automatiseerida, saab palju teenida. Seetõttu on Exceli ja funktsiooni valdamise võimalus SUMMESLIMN on eriti oluline iga inimese jaoks, kes soovib karjääri teha. Ühe funktsiooni tundmine võimaldab teha mitu toimingut korraga, ühena. Ja siit liigume edasi selle funktsiooni järgmise eelise juurde.
3. Aja kokkuhoid. Just tänu sellele, et üks funktsioon täidab mitut ülesannet korraga.
4. Lihtsus. Hoolimata asjaolust, et süntaks on oma mahukuse tõttu esmapilgul üsna raske, on selle funktsiooni loogika tegelikult väga lihtne. Esiteks valitakse andmete vahemik, seejärel väärtuste vahemik, mille vastavust teatud tingimusele kontrollitakse. Ja muidugi tuleb täpsustada ka tingimus ise. Ja nii mitu korda. Tegelikult põhineb see funktsioon ainult ühel loogilisel konstruktsioonil, mis muudab selle lihtsamaks kui tuntud VPR vaatamata sellele, et seda saab kasutada samadel eesmärkidel, võttes arvesse ka suuremat hulka kriteeriume. 

Funktsiooni SUMIFS kasutamise omadused

Selle funktsiooni kasutamisel on mitmeid funktsioone, millele peate tähelepanu pöörama. Esiteks ignoreerib see funktsioon tekstistringide või nullväärtustega vahemikke, kuna neid andmetüüpe ei saa aritmeetilises mustris kokku liita, vaid ainult stringidena aheldatuna. See funktsioon ei saa seda teha. Samuti peate tähelepanu pöörama järgmistele tingimustele:

1. Seda tüüpi väärtusi saate kasutada lahtrite valimise tingimustena, et lisada neis sisalduvaid väärtusi: arvväärtused, tõeväärtusavaldised, lahtriviited jne. 
2. Kui kontrollitakse teksti, loogilisi avaldisi või matemaatilisi märke, siis sellised kriteeriumid täpsustatakse jutumärkidega.
3. Ei tohi kasutada termineid, mis on pikemad kui 255 tähemärki.
4. Metamärkide abil väärtuste valimiseks on võimalik kasutada ligikaudseid kriteeriume. Küsimärki kasutatakse ühe märgi asendamiseks ja korrutusmärki (tärni) on vaja mitme märgi asendamiseks. 
5. Summeerimisvahemikus olevad tõeväärtused teisendatakse vastavalt nende tüübile automaatselt arvväärtusteks. Seega muutub väärtus “TRUE” üheks ja “FALSE” nulliks. 
6. Kui #VÄÄRTUS! lahtrisse ilmub viga, tähendab see, et lahtrite arv tingimus- ja summeerimisvahemikus on erinev. Peate veenduma, et nende argumentide suurused on samad. 

Näited funktsiooni SUMIFS kasutamisest

funktsioon SUMMESLIMN mitte nii keeruline, kui esmapilgul tundub, selgub. Kuid suurema selguse huvides vaatame mõningaid praktilisi näiteid selle funktsiooni kasutamise kohta SUMMESLIMN. See muudab teemasse süvenemise palju lihtsamaks.

Tingimuste summeerimise dünaamiline vahemik

Nii et alustame esimese näitega. Oletame, et meil on tabel, mis sisaldab teavet selle kohta, kuidas õpilased konkreetse aine õppekavaga toime tulevad. On hinnete komplekt, sooritust hinnatakse 10-pallisel skaalal. Ülesandeks on leida nende õpilaste eksami hinne, kelle perekonnanimi algab A-tähega ja mille minimaalne punktisumma on 5.

Tabel näeb välja selline.

Selleks, et saaksime ülalkirjeldatud kriteeriumide alusel koguhinde arvutada, peame rakendama järgmist valemit.

Kirjeldame argumente üksikasjalikumalt:

1. C3:C14 on meie summeerimisvahemik. Meie puhul langeb see kokku tingimuste vahemikuga. Sellest valitakse välja punktid, mida kasutatakse summa arvutamiseks, kuid ainult need, mis vastavad meie kriteeriumidele.
2. ">5" on meie esimene tingimus.
3. B3:B14 on teine ​​summeerimisvahemik, mida töödeldakse, et see vastaks teisele kriteeriumile. Näeme, et summeerimisvahemikuga kokkulangevust ei ole. Sellest järeldame, et summeerimise vahemik ja tingimuse vahemik võivad olla identsed, kuid ei pruugi olla identsed. 
4. “A*” on teine ​​vahemik, mis määrab hinnete valiku ainult nende õpilaste jaoks, kelle perekonnanimi algab A-ga. Meie puhul tähendab tärn suvalist arvu märke. 

Pärast arvutusi saame järgmise tabeli.

Nagu näete, võttis valem väärtused kokku dünaamilise vahemiku ja kasutaja määratud tingimuste alusel.

Valikuline summeerimine tingimuse järgi Excelis

Oletame nüüd, et tahame saada teavet selle kohta, milliseid kaupu mis riikidesse viimase kvartali jooksul saadeti. Pärast seda leidke juuli ja augusti saadetiste kogutulu.

Tabel ise näeb välja selline. 

Lõpptulemuse määramiseks vajame sellist valemit.

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

Selle valemiga tehtud arvutuste tulemusena saame järgmise tulemuse.

Tähelepanu! See valem tundub üsna suur, kuigi kasutasime ainult kahte kriteeriumi. Kui andmevahemik on sama, saate valemi pikkust oluliselt vähendada, nagu allpool näidatud.

Funktsioon SUMIFS väärtuste liitmiseks mitme tingimuse lõikes

Toome nüüd illustreerimiseks veel ühe näite. Sel juhul jääb tabel samaks, mis eelmisel juhul. 

Kasutame järgmist valemit (kuid kirjutame selle massiivivalemina, st sisestame selle klahvikombinatsiooni CTRL + SHIFT + ENTER kaudu).

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

Pärast funktsiooni SUMMESLIMN liidab väärtuste massiivi valemis määratud kriteeriumide alusel (st Hiina ja Gruusia riigid), saadud massiiv liidetakse tavalise funktsiooniga SUM, mis on kirjutatud massiivivalemina.

Kui tingimused edastati massiivikonstantina rohkem kui ühe paari jaoks, annab valem vale tulemuse.

Nüüd vaatame kogusummasid sisaldavat tabelit.

Nagu näete, on see meil õnnestunud. Kindlasti õnnestub teil ka. Suur edu selles vallas. See on väga lihtne funktsioon, millest saab aru ka inimene, kes on just sammu Exceli õppimise teele astunud. Ja me juba teame seda funktsiooni SUMMESLIMN võimaldab olla efektiivne igal tegevusalal alates raamatupidamisest ja lõpetades isegi haridusega. Isegi kui teete karjääri mõnes muus valdkonnas, mida pole ülalpool kirjeldatud, aitab see funktsioon teil ikkagi raha teenida. Sellepärast on ta väärtuslik.

Kõige tähtsam on see, et see võimaldab säästa aega, mis on kahjuks piiratud ressurss. Näib, et kahe funktsiooni rakendamiseks on paar sekundit, kuid kui peate tegema tohutul hulgal korduvaid toiminguid, siis need sekundid annavad kokku tunnid, mis võiks kuluda millelegi muule. Seetõttu soovitame teil selle funktsiooni kasutamist harjutada. Pealegi on see uskumatult lihtne.