SUMIF funkcija programoje Excel ir suma pagal kelias sąlygas

„Excel“ yra neįtikėtinai funkcionali programa. Net ir integruoto funkcijų rinkinio pakanka beveik bet kokiai užduočiai atlikti. Be standartinių, daugeliui pažįstamų, yra ir tokių, apie kuriuos mažai kas girdėjo. Tačiau tuo pat metu jie nenustoja būti naudingi. Jie turi siauresnę specializaciją, ir ne visada jų reikia. Bet jei apie juos žinote, kritiniu momentu jie gali būti labai naudingi.

Šiandien kalbėsime apie vieną iš tokių funkcijų − SUMMESLIMN.

Jei vartotojas susiduria su užduotimi susumuoti kelias reikšmes, sutelkiant dėmesį į tam tikrus kriterijus, tada būtina naudoti funkciją SUMMESLIMN. Formulė, kuri naudoja šią funkciją, šias sąlygas priima kaip argumentus, tada susumuoja jas atitinkančias reikšmes, o rasta reikšmė įvedama į langelį, kuriame ji parašyta. 

SUMIFS funkcijos Išsamus aprašymas

Prieš svarstant funkciją SUMMESLIMN, pirmiausia turite suprasti, kas yra paprastesnė versija – SUMMESLI, nes būtent juo remiasi mūsų svarstoma funkcija. Kiekvienas iš mūsų tikriausiai jau žino dvi dažnai naudojamas funkcijas – SUMA (atlieka reikšmių sumavimą) ir JEI (tikrina vertę pagal nurodytą sąlygą).

Jei juos derinsite, gausite kitą funkciją – SUMMESLI, kuri tikrina duomenis pagal vartotojo nurodytus kriterijus ir sumuoja tik tuos kriterijus atitinkančius skaičius. Jei kalbame apie anglišką „Excel“ versiją, tada ši funkcija vadinama SUMIF. Paprastais žodžiais tariant, pavadinimas - kalba yra tiesioginis anglų kalbos vertimas. Ši funkcija gali būti naudojama įvairiems tikslams. Visų pirma, jis gali būti naudojamas kaip alternatyva VPR, tai yra, užsirašykite

Pagrindinis skirtumas tarp funkcijos SUMMESLIMN  nuo įprastos funkcijos SUMMESLI yra naudojami keli kriterijai. Jos sintaksė iš pirmo žvilgsnio gana sudėtinga, tačiau atidžiau panagrinėjus paaiškėja, kad šios funkcijos logika labai paprasta. Pirmiausia reikia pasirinkti diapazoną, kuriame bus tikrinami duomenys, o tada nustatyti sąlygas, kurių laikantis bus atliekama analizė. Ir tokią operaciją galima atlikti gana daugybei sąlygų.

Pati sintaksė yra tokia:

SUMIFS(sumos_diapazonas, sąlyga_diapazonas1, sąlyga1, [sąlygos_diapazonas2, sąlyga2], …)

Tinkamose vietose būtina sudėti konkrečiam atvejui tinkamų langelių masyvus. 

Pažvelkime į argumentus išsamiau:

1. Sumos_diapazonas. Šis argumentas, taip pat 1 ir 1 sąlygos diapazonas yra būtinas. Tai ląstelių rinkinys, kurį reikia sumuoti.
2. Sąlygos_diapazonas1. Tai yra diapazonas, kuriame būsena bus tikrinama. Jis suporuotas su kitu argumentu – Sąlyga1. Vertės, atitinkančios kriterijų, sumuojamos ankstesniame argumente nurodytose ląstelėse.
3. Sąlyga1. Šis argumentas nurodo kriterijus, pagal kuriuos reikia patikrinti. Jį galima nustatyti, pavyzdžiui, tokiu būdu: “> 32”.
4. Sąlygų diapazonas 2, Sąlyga 2… Čia taip pat nustatomos šios sąlygos. Jei reikia nurodyti daugiau nei kelias sąlygas, pridedami 3 sąlygų diapazono ir 3 sąlygos argumentai. Šių argumentų sintaksė yra tokia pati.

Ši funkcija leidžia maksimaliai apdoroti iki 127 porų sąlygų ir diapazonų. 

Jį galite naudoti keliose srityse vienu metu (pateiksime tik kelias, sąrašas iš tikrųjų dar ilgesnis):

1. Apskaita. Pavyzdžiui, naudinga naudoti funkciją SUMMESLIMN sukurti suvestines ataskaitas pagal ketvirčius, pavyzdžiui, kai išleidžiate tam tikrą sumą. Arba sukurkite ataskaitą apie vieną produktą iš tam tikros kainų kategorijos.
2. Pardavimų vadyba. Čia taip pat gali būti labai naudinga funkcija. Pavyzdžiui, susiduriame su užduotimi susumuoti tik prekių, kurios buvo parduotos tam tikram klientui tam tikru metu, savikainą. Ir tokioje situacijoje funkcija SUMMESLIMN gali būti labai naudinga.
3. Išsilavinimas. Šiandien pateiksime daugiau praktinių pavyzdžių iš šios srities. Visų pirma, galite jį naudoti norėdami gauti mokinių pažymių suvestinę. Galite pasirinkti vieną dalyką arba atskirus pažymius. Žmogus iš karto gali nusistatyti kelis kriterijus, pagal kuriuos bus pasirenkamas vertinimas, o tai labai patogu ir gali sutaupyti daug laiko.

Kaip matote, šios funkcijos pritaikymo spektras yra labai platus. Tačiau tai ne vienintelis jos nuopelnas. Pažvelkime į dar kelis šios funkcijos pranašumus:

1. Galimybė nustatyti kelis kriterijus. Kodėl tai privalumas? Galite naudoti įprastą funkciją SUMMESLI! Ir viskas todėl, kad tai patogu. Nereikia atlikti atskirų kiekvieno kriterijaus skaičiavimų. Visi veiksmai gali būti užprogramuoti iš anksto, net ir anksčiau. kaip bus formuojama duomenų lentelė. Tai puikus laiko taupymas.
2. Automatika. Šiuolaikinis amžius yra automatizacijos amžius. Daug uždirbti gali tik tas, kuris moka tinkamai automatizuoti savo darbą. Štai kodėl gebėjimas įvaldyti Excel ir funkciją SUMMESLIMN ypač svarbu bet kuriam asmeniui, norinčiam kurti karjerą. Žinodami vieną funkciją, galite vienu metu atlikti kelis veiksmus kaip vieną. Ir čia pereiname prie kitos šios funkcijos pranašumo.
3. Taupomas laikas. Jau vien dėl to, kad viena funkcija vienu metu atlieka kelias užduotis.
4. Paprastumas. Nepaisant to, kad iš pirmo žvilgsnio sintaksė yra gana sunki dėl savo didelių gabaritų, iš tikrųjų šios funkcijos logika yra labai paprasta. Pirmiausia pasirenkamas duomenų diapazonas, tada reikšmių diapazonas, kuris bus patikrintas, ar atitinka tam tikrą sąlygą. Ir, žinoma, pati sąlyga taip pat turi būti nurodyta. Ir taip kelis kartus. Tiesą sakant, ši funkcija pagrįsta tik vienu loginiu dariniu, todėl ji yra paprastesnė nei gerai žinoma VPR nepaisant to, kad jis gali būti naudojamas tiems patiems tikslams, taip pat atsižvelgiant į didesnį kriterijų skaičių. 

Funkcijos SUMIFS naudojimo ypatybės

Yra keletas šios funkcijos naudojimo ypatybių, į kurias reikia atkreipti dėmesį. Visų pirma, ši funkcija nepaiso diapazonų su teksto eilutėmis arba nuliais, nes šių duomenų tipų negalima sudėti į aritmetinį šabloną, o tik sujungti kaip eilutes. Ši funkcija negali to padaryti. Taip pat turite atkreipti dėmesį į šias sąlygas:

1. Šių tipų reikšmes galite naudoti kaip langelių pasirinkimo sąlygas, kad toliau pridėtumėte juose esančias reikšmes: skaitines reikšmes, logines išraiškas, langelių nuorodas ir pan. 
2. Jei tikrinamas tekstas, loginės išraiškos ar matematiniai ženklai, tokie kriterijai nurodomi kabutėse.
3. Negalima naudoti terminų, ilgesnių nei 255 simboliai.
4. Galima naudoti apytikslius kriterijus pasirenkant reikšmes naudojant pakaitos simbolius. Klaustukas naudojamas norint pakeisti vieną simbolį, o daugybos ženklas (žvaigždutė) reikalingas norint pakeisti kelis simbolius. 
5. Būlio reikšmės, esančios sumavimo diapazone, automatiškai konvertuojamos į skaitines reikšmes pagal jų tipą. Taigi reikšmė „TRUE“ virsta vienetu, o „FALSE“ – nuliu. 
6. Jei #VERTĖ! langelyje atsiranda klaida, tai reiškia, kad langelių skaičius sąlygos ir sumavimo diapazonuose yra skirtingas. Turite įsitikinti, kad šių argumentų dydžiai yra vienodi. 

Funkcijos SUMIFS naudojimo pavyzdžiai

Funkcija SUMMESLIMN ne taip sudėtinga, kaip atrodo iš pirmo žvilgsnio, pasirodo. Tačiau, kad būtų daugiau aiškumo, pažvelkime į keletą praktinių pavyzdžių, kaip galite naudoti šią funkciją SUMMESLIMN. Taip bus daug lengviau įsigilinti į temą.

Sąlygos sumavimo dinaminis diapazonas

Taigi pradėkime nuo pirmojo pavyzdžio. Tarkime, kad turime lentelę, kurioje pateikiama informacija apie tai, kaip mokiniai susidoroja su konkretaus dalyko mokymo programa. Yra pažymių rinkinys, veikla vertinama 10 balų sistemoje. Užduotis – rasti tų mokinių, kurių pavardė prasideda raide A, o minimalus balas – 5, egzamino įvertinimą.

Lentelė atrodo taip.

Kad galėtume apskaičiuoti bendrą balą pagal aukščiau aprašytus kriterijus, turime taikyti šią formulę.

Apibūdinkime argumentus išsamiau:

1. C3:C14 yra mūsų sumavimo diapazonas. Mūsų atveju tai sutampa su sąlygų diapazonu. Iš jo bus atrinkti taškai, naudojami sumai apskaičiuoti, bet tik tie, kurie atitinka mūsų kriterijus.
2. „>5“ yra pirmoji mūsų sąlyga.
3. B3:B14 yra antrasis sumavimo diapazonas, kuris apdorojamas, kad atitiktų antrąjį kriterijų. Matome, kad nėra sutapimo su sumavimo diapazonu. Iš to darome išvadą, kad sumavimo diapazonas ir sąlygos diapazonas gali būti identiški arba ne. 
4. „A*“ yra antrasis diapazonas, nurodantis pažymių pasirinkimą tik tiems mokiniams, kurių pavardė prasideda raide A. Mūsų atveju žvaigždutė reiškia bet kokį simbolių skaičių. 

Atlikę skaičiavimus, gauname tokią lentelę.

Kaip matote, formulė apibendrino reikšmes pagal dinaminį diapazoną ir pagal vartotojo nurodytas sąlygas.

Atrankinis sumavimas pagal sąlygą programoje Excel

Tarkime, kad norime gauti informacijos apie tai, kokios prekės buvo išsiųstos į kokias šalis per paskutinį ketvirtį. Po to suraskite visas liepos ir rugpjūčio mėnesio pajamas iš siuntų.

Pati lentelė atrodo taip. 

Norint nustatyti galutinį rezultatą, mums reikia tokios formulės.

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

Atlikę skaičiavimus pagal šią formulę, gauname tokį rezultatą.

Dėmesio! Ši formulė atrodo gana didelė, nors naudojome tik du kriterijus. Jei duomenų diapazonas yra toks pat, galite žymiai sumažinti formulės ilgį, kaip parodyta toliau.

Funkcija SUMIFS susumuoti reikšmes keliomis sąlygomis

Dabar pateiksime kitą pavyzdį iliustravimui. Šiuo atveju lentelė išlieka tokia pati kaip ir ankstesniu atveju. 

Naudojame šią formulę (bet ją rašome kaip masyvo formulę, tai yra, įvedame per klavišų kombinaciją CTRL + SHIFT + ENTER).

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

Po funkcijos SUMMESLIMN apibendrins reikšmių masyvą pagal formulėje nurodytus kriterijus (tai yra Kinijos ir Gruzijos šalys), gautas masyvas susumuojamas pagal įprastą funkciją SUMA, kuri parašyta kaip masyvo formulė.

Jei sąlygos buvo pateiktos kaip masyvo konstanta daugiau nei vienai porai, formulė pateiks neteisingą rezultatą.

Dabar pažvelkime į lentelę, kurioje pateikiamos sumos.

Kaip matote, mums pavyko. Jums taip pat tikrai pavyks. Didelė sėkmė šioje srityje. Tai labai paprasta funkcija, kurią gali suprasti žmogus, ką tik įžengęs į Excel mokymosi kelią. Ir mes jau žinome, kad funkcija SUMMESLIMN leidžia būti efektyviam bet kurioje veiklos srityje – nuo ​​buhalterinės apskaitos iki netgi išsilavinimo. Net jei kuriate karjerą kitoje srityje, kuri nebuvo aprašyta aukščiau, ši funkcija vis tiek padės užsidirbti pinigų. Štai kodėl ji yra vertinga.

Svarbiausia, kad tai leidžia sutaupyti laiko, kuris, deja, yra ribotas išteklius. Atrodytų, kad dviem funkcijoms pritaikyti reikia poros sekundžių, tačiau kai tenka atlikti daugybę pasikartojančių operacijų, šios sekundės prideda valandų, kurias būtų galima skirti kažkam kitam. Todėl rekomenduojame pasitreniruoti naudojant šią funkciją. Be to, tai neįtikėtinai paprasta.