Funkcija SUMIF u Excelu i zbroj po više uvjeta

Excel je nevjerojatno funkcionalan program. Čak je i ugrađeni skup značajki dovoljan za obavljanje gotovo svakog zadatka. A osim standardnih, mnogima poznatih, postoje i oni za koje je malo tko čuo. Ali u isto vrijeme, oni ne prestaju biti korisni. Imaju užu specijalizaciju i za njima nema uvijek potrebe. 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 zbrajanja 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 zapisana. 

Detaljan opis funkcije SUMIFS

Prije razmatranja funkcije SUMMESLIMN, prvo morate shvatiti što je to jednostavnija verzija – SUMMESLI, budući da se na njemu temelji funkcija koju razmatramo. Svatko od nas vjerojatno je već upoznat s dvije funkcije koje se često koriste – IZNOS (provodi zbrajanje vrijednosti) i AKO (testira vrijednost u odnosu na određeni uvjet).

Ako ih spojite, dobit ćete još jednu funkciju – SUMMESLI, koji provjerava podatke prema kriterijima koje je odredio korisnik i zbraja samo one brojeve koji zadovoljavaju te kriterije. Ako govorimo o engleskoj verziji programa Excel, onda se ova funkcija zove SUMIF. Jednostavnim riječima, naziv na -jeziku izravan je prijevod onog na engleskom jeziku. Ova se funkcija 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 kriterija. Sintaksa je na prvi pogled prilično složena, ali nakon detaljnijeg ispitivanja ispada da je logika ove funkcije vrlo jednostavna. Najprije je potrebno odabrati raspon u kojem će se podaci provjeravati, a zatim postaviti uvjete usklađenosti s kojima će se analiza provoditi. I takva se operacija može izvesti za prilično velik broj uvjeta.

Sama sintaksa je:

SUMIFS(raspon_zbroja, raspon_uvjeta1, uvjet1, [raspon_uvjeta2, uvjet2], …)

Na prikladna mjesta potrebno je postaviti nizove ćelija prikladne za određeni slučaj. 

Pogledajmo detaljnije argumente:

1. Raspon_zbroja. Potreban je ovaj argument, kao i raspon uvjeta 1 i uvjeta 1. To je skup ćelija koje je potrebno zbrojiti.
2. Raspon_uvjeta1. Ovo je raspon u kojem će se uvjet provjeriti. Uparen je sa sljedećim argumentom – Condition1. Zbrajanje vrijednosti koje odgovaraju kriteriju provodi se unutar ćelija navedenih u prethodnom argumentu.
3. Uvjet1. Ovaj argument specificira kriterije prema kojima se treba provjeriti. Može se postaviti, na primjer, na ovaj način: “> 32”.
4. Raspon uvjeta 2, uvjet 2... Ovdje su sljedeći uvjeti postavljeni na isti način. Ako je potrebno navesti više od nekoliko uvjeta, tada se dodaju argumenti Raspon uvjeta 3 i Uvjet 3. Sintaksa je ista za sljedeće argumente.

Funkcija omogućuje maksimalnu obradu do 127 parova uvjeta i raspona. 

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

1. Računovodstvo. Na primjer, dobro je koristiti funkciju SUMMESLIMN za izradu sumarnih izvješća, po kvartalima za npr. potrošnju iznad određenog iznosa. Ili izradite izvješće o jednom proizvodu iz određene cjenovne kategorije.
2. Upravljanje prodajom. Ovdje također funkcija može biti vrlo korisna. Na primjer, suočeni smo sa zadatkom zbrajanja samo troškova robe koja je prodana određenom kupcu u određeno vrijeme. I u takvoj situaciji funkcija SUMMESLIMN može biti vrlo korisno.
3. Obrazovanje. Danas ćemo dati više praktičnih primjera iz ove oblasti. Konkretno, možete ga koristiti za dobivanje sažetka ocjena učenika. Možete birati za jedan predmet ili za pojedinačne razrede. Osoba može odmah postaviti nekoliko kriterija prema kojima će se odabrati procjena, što je vrlo zgodno i može uštedjeti puno vremena.

Kao što vidite, raspon primjena ove funkcije je vrlo širok. Ali to nije njegova jedina zasluga. Pogledajmo još nekoliko prednosti koje ova značajka ima:

1. Mogućnost postavljanja više kriterija. Zašto je to prednost? Možete koristiti normalnu funkciju SUMMESLI! I sve zato što je prikladno. Nema potrebe provoditi zasebne izračune za svaki od kriterija. Sve radnje mogu se programirati unaprijed, čak i prije. kako će se tablica podataka formirati. Ovo je velika ušteda vremena.
2. Automatizacija. Moderno doba je doba automatizacije. Samo onaj tko zna pravilno automatizirati svoj rad može puno zaraditi. Zato sposobnost svladavanja Excela i funkcije SUMMESLIMN posebno, toliko je važno za svaku osobu koja želi izgraditi karijeru. Poznavanje jedne funkcije omogućuje vam da izvršite nekoliko radnji odjednom, kao jednu. I ovdje prelazimo na sljedeću prednost ove značajke.
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, zatim raspon vrijednosti za koje će se provjeravati usklađenost s određenim uvjetom. I naravno, mora biti naveden i sam uvjet. I tako nekoliko puta. Zapravo, ova se funkcija temelji na samo jednoj logičkoj konstrukciji, što je čini jednostavnijom od dobro poznate VPR unatoč tome što se može koristiti u iste svrhe, također uzimajući u obzir veći broj kriterija. 

Značajke korištenja funkcije SUMIFS

Postoji nekoliko značajki korištenja ove funkcije na koje morate obratiti pozornost. Prije svega, ova funkcija zanemaruje raspone s tekstualnim nizovima ili nulama, budući da se ti tipovi podataka ne mogu zbrajati zajedno u aritmetičkom uzorku, samo spojeni poput nizova. Ova funkcija to ne može učiniti. Također morate obratiti pozornost na sljedeće uvjete:

1. Ove vrste vrijednosti možete koristiti kao uvjete za odabir ćelija za daljnje dodavanje vrijednosti sadržanih u njima: numeričke vrijednosti, Booleovi izrazi, reference ćelija i tako dalje. 
2. Ako se provjeravaju tekst, logički izrazi ili matematički znakovi, tada se takvi kriteriji navode kroz navodnike.
3. Ne mogu se koristiti pojmovi duži 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) potreban je za zamjenu više znakova. 
5. Booleove vrijednosti koje se nalaze u rasponu zbrajanja automatski se pretvaraju u numeričke vrijednosti u skladu s njihovom vrstom. Dakle, vrijednost "TRUE" se pretvara u jedinicu, a "FALSE" - u nulu. 
6. Ako #VALUE! ako se u ćeliji pojavi pogreška, to znači da je broj ćelija u rasponima uvjeta i zbroja različit. Morate biti sigurni da su veličine ovih argumenata iste. 

Primjeri korištenja funkcije SUMIFS

funkcija SUMMESLIMN nije tako komplicirano kao što se na prvi pogled čini, ispada. Ali radi veće jasnoće, pogledajmo neke praktične primjere kako možete koristiti tu funkciju SUMMESLIMN. To će znatno olakšati udubljivanje u temu.

Dinamički raspon zbrajanja uvjeta

Pa počnimo s prvim primjerom. Recimo da imamo tablicu koja sadrži podatke o tome kako se učenici snalaze u gradivu pojedinog predmeta. Postoji skup ocjena, izvedba se ocjenjuje na ljestvici od 10 bodova. Zadatak je pronaći ocjenu ispita onih studenata čije prezime počinje na slovo A, a minimalna ocjena im je 5.

Tablica izgleda ovako.

Kako bismo izračunali ukupni rezultat na temelju gore opisanih kriterija, moramo primijeniti sljedeću formulu.

Opišimo argumente detaljnije:

1. C3:C14 je naš raspon zbrajanja. U našem slučaju, to se podudara s rasponom uvjeta. Iz njega će biti odabrani bodovi koji se koriste za izračun iznosa, ali samo oni koji potpadaju pod naše kriterije.
2. “>5” je naš prvi uvjet.
3. B3:B14 je drugi raspon zbroja koji se obrađuje da odgovara drugom kriteriju. Vidimo da nema slučajnosti s rasponom zbrajanja. 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 odabir ocjena samo za one studente čije prezime počinje s A. U našem slučaju zvjezdica označava bilo koji broj znakova. 

Nakon izračuna dobivamo sljedeću tablicu.

Kao što vidite, formula je zbrojila vrijednosti na temelju dinamičkog raspona i na temelju uvjeta koje je odredio korisnik.

Selektivno zbrajanje po stanju u Excelu

Sada pretpostavimo da želimo dobiti informacije o tome koja je roba poslana u koje zemlje tijekom posljednjeg kvartala. Nakon toga pronađite ukupni prihod od pošiljaka za srpanj i kolovoz.

Sama tablica 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 izračuna provedenih ovom formulom, dobivamo 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 duljinu formule, kao što je prikazano u nastavku.

Funkcija SUMIFS za zbrajanje vrijednosti u više uvjeta

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

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

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

Nakon funkcije SUMMESLIMN će zbrojiti niz vrijednosti na temelju kriterija navedenih u formuli (to jest, zemlje Kine i Gruzije), rezultirajući niz zbraja se uobičajenom funkcijom IZNOS, koji je napisan kao formula polja.

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

Sada pogledajmo tablicu koja sadrži ukupne iznose.

Kao što vidite, uspjeli smo. I vi ćete 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 funkcija SUMMESLIMN omogućuje vam da budete učinkoviti u bilo kojem području aktivnosti, od računovodstva do obrazovanja. Čak i ako gradite karijeru u bilo kojem drugom području koje nije gore opisano, ova će vam značajka svejedno pomoći da zaradite novac. Zbog toga je vrijedna.

Što je najvažnije, omogućuje vam uštedu vremena, koje je, nažalost, ograničen resurs. Čini se da postoji nekoliko sekundi za primjenu dvije funkcije, ali kada morate izvršiti ogroman broj ponavljajućih operacija, tada te sekunde zbrajaju sate koje biste mogli potrošiti na nešto drugo. Stoga preporučujemo da vježbate korištenje ove značajke. Štoviše, nevjerojatno je jednostavan.