Funkcija SUMIF u Excelu i zbir prema više uslova

Excel je neverovatno funkcionalan program. Čak je i ugrađeni skup funkcija dovoljan da izvrši gotovo svaki zadatak. A osim standardnih, mnogima poznatih, postoje i oni za koje je malo tko čuo. Ali u isto vrijeme, oni ne prestaju biti korisni. Oni imaju užu specijalizaciju i ne postoji uvijek potreba za njima. Ali ako znate za njih, onda u kritičnom trenutku mogu biti vrlo korisni.

Danas ćemo govoriti o jednoj od takvih funkcija − SUMMESLIMN.

Ako je korisnik suočen sa zadatkom da zbroji nekoliko vrijednosti, fokusirajući se na određene kriterije, tada je potrebno koristiti funkciju SUMMESLIMN. Formula koja koristi ovu funkciju uzima ove uvjete kao argumente, zatim zbraja vrijednosti koje ih ispunjavaju, a zatim se pronađena vrijednost unosi u ćeliju u kojoj je upisana. 

Detaljan opis funkcije SUMIFS

Prije razmatranja funkcije SUMMESLIMN, prvo morate shvatiti koja je to jednostavnija verzija – SUMMESLI, budući da je na njemu zasnovana funkcija koju razmatramo. Svako od nas je vjerovatno već upoznat sa dvije funkcije koje se često koriste – SUMA (izvodi sumiranje vrijednosti) i AKO (testira vrijednost u odnosu na specificirani uvjet).

Ako ih kombinujete, dobijate još jednu funkciju – SUMMESLI, koji provjerava podatke prema kriterijima koje je odredio korisnik i zbraja samo one brojeve koji ispunjavaju te kriterije. Ako govorimo o engleskoj verziji Excela, onda se ova funkcija zove SUMIF. Jednostavnim riječima, naziv -language je direktan prijevod onog na engleskom jeziku. Ova funkcija se može koristiti u razne svrhe. Konkretno, može se koristiti kao alternativa VPR, odnosno zapišite

Glavna razlika između funkcije SUMMESLIMN  od uobičajene funkcije SUMMESLI je da se koristi nekoliko kriterijuma. Njena sintaksa je na prvi pogled prilično složena, ali nakon detaljnijeg proučavanja, ispostavlja se da je logika ove funkcije vrlo jednostavna. Prvo morate odabrati opseg u kojem će se podaci provjeravati, a zatim postaviti uslove za usklađenost s kojima će se analiza vršiti. I takva operacija se može izvesti za prilično veliki broj uvjeta.

Sama sintaksa je:

SUMIFS(opseg_zbira, opseg_uslov1, uslov1, [opseg_uslova2, uslov2], …)

Na pogodna mjesta potrebno je postaviti nizove ćelija koje odgovaraju određenom slučaju. 

Pogledajmo argumente detaljnije:

1. Raspon_zbira. Ovaj argument, kao i opseg uslova 1 i uslova 1, je potreban. To je skup ćelija koje treba sabrati.
2. Opseg_uvjeta1. Ovo je raspon u kojem će se stanje provjeriti. Uparen je sa sljedećim argumentom – Condition1. Zbrajanje vrijednosti koje odgovaraju kriteriju vrši se unutar ćelija navedenih u prethodnom argumentu.
3. Stanje1. Ovaj argument specificira kriterije prema kojima se treba provjeriti. Može se postaviti, na primjer, na ovaj način: “> 32”.
4. Opseg uslova 2, uslov 2… Ovde su sledeći uslovi postavljeni na isti način. Ako je potrebno specificirati više od nekoliko uslova, tada se dodaju argumenti Opseg uslova 3 i Uslov 3. Sintaksa je ista za sljedeće argumente.

Funkcija omogućava maksimalnu obradu do 127 parova uslova i opsega. 

Možete ga koristiti u nekoliko područja odjednom (navest ćemo samo neka, spisak je zapravo još duži):

1. Računovodstvo. Na primjer, dobro je koristiti funkciju SUMMESLIMN da kreirate zbirne izvještaje, po kvartalima za potrošnju preko određenog iznosa, na primjer. Ili kreirajte izvještaj o jednom proizvodu iz određene cjenovne kategorije.
2. Upravljanje prodajom. Ovdje također funkcija može biti vrlo korisna. Na primjer, pred nama je zadatak da zbrojimo samo cijenu robe koja je prodata određenom kupcu u određenom trenutku. I u takvoj situaciji, funkcija SUMMESLIMN može biti od velike pomoći.
3. Obrazovanje. Danas ćemo dati više praktičnih primjera iz ove oblasti. Konkretno, možete ga koristiti da dobijete sažetak ocjena učenika. Možete birati za jedan predmet ili za pojedinačne ocjene. Osoba može odmah postaviti nekoliko kriterija po kojima će biti odabrana procjena, što je vrlo zgodno i može uštedjeti mnogo vremena.

Kao što vidite, raspon aplikacija za ovu funkciju je vrlo širok. Ali to nije njegova jedina zasluga. Pogledajmo još nekoliko prednosti koje ova funkcija ima:

1. Mogućnost postavljanja više kriterijuma. Zašto je ovo prednost? Možete koristiti normalnu funkciju SUMMESLI! I sve zato što je zgodno. Nema potrebe za posebnim proračunima za svaki od kriterija. Sve radnje se mogu programirati unaprijed, čak i prije. kako će se formirati tabela podataka. Ovo je odlična ušteda vremena.
2. Automatizacija. Moderno doba je doba automatizacije. Samo onaj ko zna kako da pravilno automatizuje svoj rad može mnogo zaraditi. Zato sposobnost savladavanja Excela i funkcije SUMMESLIMN posebno je toliko važno za svaku osobu koja želi da izgradi karijeru. Poznavanje jedne funkcije omogućava vam da izvršite nekoliko radnji odjednom, kao jednu. I ovdje prelazimo na sljedeću pogodnost ove funkcije.
3. Ušteda vremena. Samo zbog činjenice da jedna funkcija obavlja nekoliko zadataka odjednom.
4. Jednostavnost. Unatoč činjenici da je sintaksa na prvi pogled prilično teška zbog svoje glomaznosti, zapravo je logika ove funkcije vrlo jednostavna. Najprije se odabire raspon podataka, a zatim raspon vrijednosti, koji će se provjeravati u skladu s određenim uvjetom. I naravno, sam uvjet također mora biti specificiran. I tako nekoliko puta. Zapravo, ova funkcija je zasnovana na samo jednoj logičkoj konstrukciji, što je čini jednostavnijom od dobro poznate VPR uprkos činjenici da se može koristiti u iste svrhe, takođe uzimajući u obzir veći broj kriterijuma. 

Značajke korištenja SUMIFS funkcije

Postoji nekoliko karakteristika korištenja ove funkcije na koje morate obratiti pažnju. Prije svega, ova funkcija zanemaruje opsege s tekstualnim nizovima ili nultim vrijednostima, budući da se ovi tipovi podataka ne mogu zbrajati zajedno u aritmetičkom uzorku, već samo spojiti kao nizovi. Ova funkcija to ne može učiniti. Takođe morate obratiti pažnju na sledeće uslove:

1. Ove vrste vrijednosti možete koristiti kao uvjete za odabir ćelija za dalje dodavanje vrijednosti sadržanih u njima: numeričke vrijednosti, logički izrazi, reference ćelija itd. 
2. Ako se provjeravaju tekst, logički izrazi ili matematički znakovi, onda se takvi kriteriji specificiraju kroz navodnike.
3. Ne mogu koristiti termine duže od 255 znakova.
4. Moguće je koristiti približne kriterije za odabir vrijednosti pomoću zamjenskih znakova. Upitnik se koristi za zamjenu jednog znaka, a znak množenja (zvjezdica) je potreban za zamjenu više znakova. 
5. Booleove vrijednosti koje se nalaze u rasponu sumiranja automatski se pretvaraju u numeričke vrijednosti prema njihovom tipu. Tako se vrijednost “TRUE” pretvara u jedan, a “FALSE” – u nulu. 
6. Ako je #VRIJEDNOST! greška se pojavljuje u ćeliji, to znači da je broj ćelija u rasponima uvjeta i zbrajanja različit. Morate biti sigurni da su veličine ovih argumenata iste. 

Primjeri korištenja SUMIFS funkcije

funkcija SUMMESLIMN nije tako komplikovano kao što se čini na prvi pogled, ispostavilo se. Ali radi više jasnoće, pogledajmo nekoliko praktičnih primjera kako možete koristiti funkciju SUMMESLIMN. Ovo će znatno olakšati udubljivanje u temu.

Dinamički opseg zbrajanja uslova

Dakle, počnimo s prvim primjerom. Recimo da imamo tabelu koja sadrži informacije o tome kako se učenici nose sa nastavnim planom i programom iz određenog predmeta. Postoji skup ocjena, učinak se ocjenjuje na skali od 10 bodova. Zadatak je pronaći ocjenu na ispitu onih učenika čije prezime počinje na slovo A, a minimalni rezultat im je 5.

Tabela izgleda ovako.

Da bismo izračunali ukupan rezultat na osnovu gore opisanih kriterijuma, treba da primenimo sledeću formulu.

Opišimo argumente detaljnije:

1. C3:C14 je naš raspon sumiranja. U našem slučaju, to se poklapa sa opsegom uslova. Iz njega će biti odabrani bodovi koji se koriste za izračunavanje iznosa, ali samo oni koji potpadaju pod naše kriterije.
2. “>5” je naš prvi uslov.
3. B3:B14 je drugi raspon zbrajanja koji se obrađuje kako bi odgovarao drugom kriteriju. Vidimo da ne postoji slučajnost sa opsegom sumiranja. Iz ovoga zaključujemo da raspon zbrajanja i raspon uvjeta mogu, ali i ne moraju biti identični. 
4. “A*” je drugi raspon, koji određuje izbor ocjena samo za one učenike čije prezime počinje sa A. U našem slučaju, zvjezdica označava bilo koji broj znakova. 

Nakon proračuna dobijamo sledeću tabelu.

Kao što vidite, formula je sumirala vrijednosti na osnovu dinamičkog raspona i na osnovu uslova koje je odredio korisnik.

Selektivno zbrajanje po uslovu u Excelu

Pretpostavimo sada da želimo da dobijemo informacije o tome koja je roba isporučena u koje zemlje tokom poslednjeg kvartala. Nakon toga pronađite ukupan prihod od pošiljaka za jul i avgust.

Sama tabela izgleda ovako. 

Da bismo odredili konačni rezultat, potrebna nam je takva formula.

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

Kao rezultat proračuna izvedenih po ovoj formuli, dobijamo sljedeći rezultat.

Pažnja! Ova formula izgleda prilično velika iako smo koristili samo dva kriterija. Ako je raspon podataka isti, tada možete značajno smanjiti dužinu formule, kao što je prikazano u nastavku.

SUMIFS funkcija za zbrajanje vrijednosti u više uvjeta

Sada dajmo još jedan primjer za ilustraciju. U ovom slučaju tabela ostaje ista kao u prethodnom slučaju. 

Koristimo sljedeću formulu (ali je pišemo kao formulu niza, odnosno unosimo je kombinacijom tipki CTRL + SHIFT + ENTER).

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

Nakon funkcije SUMMESLIMN će zbrojiti niz vrijednosti na osnovu kriterija navedenih u formuli (to jest, zemlje Kine i Gruzije), rezultirajući niz se zbraja uobičajenom funkcijom SUMA, koja je zapisana kao formula niza.

Ako su uvjeti proslijeđeni kao konstanta niza za više od jednog para, tada će formula dati pogrešan rezultat.

Sada pogledajmo tabelu koja sadrži ukupne vrijednosti.

Kao što vidite, uspjeli smo. I ti ćeš sigurno uspjeti. Veliki uspjeh na ovom polju. Ovo je vrlo jednostavna funkcija koju može razumjeti osoba koja je tek kročila na put učenja Excela. A već znamo da je funkcija SUMMESLIMN omogućava vam da budete efikasni u bilo kojoj oblasti aktivnosti, od računovodstva do obrazovanja. Čak i ako gradite karijeru u bilo kojoj drugoj oblasti koja nije gore opisana, ova funkcija će vam i dalje pomoći da zaradite novac. Zbog toga je ona vredna.

Ono što je najvažnije, omogućava vam da uštedite vrijeme, koje je, nažalost, ograničen resurs. Čini se da postoji nekoliko sekundi za primjenu dvije funkcije, ali kada morate obaviti ogroman broj operacija koje se ponavljaju, onda se te sekunde zbrajaju u sate koje možete potrošiti na nešto drugo. Stoga preporučujemo da vježbate korištenje ove funkcije. Štaviše, nevjerovatno je jednostavan.