Funkcija SUMIF v Excelu in seštevek po več pogojih

Excel je neverjetno funkcionalen program. Tudi vgrajeni nabor funkcij zadostuje za dokončanje skoraj vseh nalog. In poleg standardnih, ki jih mnogi poznajo, obstajajo tudi tisti, za katere je le malo ljudi slišalo. Toda hkrati ne prenehajo biti uporabni. Imajo ožjo specializacijo in po njih ni vedno potrebe. Če pa jih poznate, so lahko v kritičnem trenutku zelo koristni.

Danes bomo govorili o eni izmed takih funkcij − SUMMESLIMN.

Če se uporabnik sooča z nalogo seštevanja več vrednosti, pri čemer se osredotoča na določena merila, potem je treba uporabiti funkcijo SUMMESLIMN. Formula, ki uporablja to funkcijo, vzame te pogoje kot argumente, nato sešteje vrednosti, ki jim ustrezajo, nato pa se najdena vrednost vnese v celico, v kateri je zapisana. 

Podroben opis funkcije SUMIFS

Preden razmislite o funkciji SUMMESLIMN, morate najprej razumeti, kaj je preprostejša različica tega – SUMMESLI, saj na njem temelji funkcija, ki jo obravnavamo. Vsak od nas verjetno že pozna dve funkciji, ki ju pogosto uporabljamo – SUM (izvaja seštevanje vrednosti) in ČE (preizkusi vrednost glede na podani pogoj).

Če jih združite, dobite še eno funkcijo – SUMMESLI, ki preveri podatke glede na merila, ki jih določi uporabnik, in sešteje le tista števila, ki ustrezajo tem kriterijem. Če govorimo o angleški različici Excela, se ta funkcija imenuje SUMIF. Preprosto povedano, ime -jezik je neposreden prevod angleškega jezika. To funkcijo lahko uporabite za različne namene. Zlasti se lahko uporablja kot alternativa VPR, torej zapišite

Glavna razlika med funkcijo SUMMESLIMN  od običajne funkcije SUMMESLI je, da se uporablja več meril. Njena sintaksa je na prvi pogled precej zapletena, a ob natančnejšem pregledu se izkaže, da je logika te funkcije zelo preprosta. Najprej morate izbrati obseg, kjer bodo podatki preverjeni, nato pa določiti pogoje za skladnost, s katerimi bo izvedena analiza. In takšno operacijo je mogoče izvesti za precej veliko število pogojev.

Sama sintaksa je:

SUMIFS(obseg_vsote, obseg_pogoja1, pogoj1, [obseg_pogoja2, pogoj2], …)

Na primernih mestih je treba postaviti nize celic, primerne za določen primer. 

Oglejmo si argumente podrobneje:

1. obseg_vsote. Zahtevan je ta argument, pa tudi obseg pogoja 1 in pogoja 1. Je niz celic, ki jih je treba sešteti.
2. obseg_pogoja1. To je obseg, kjer bo pogoj preverjen. Seznanjen je z naslednjim argumentom – Pogoj1. Seštevek vrednosti, ki ustrezajo kriteriju, se izvede v celicah, navedenih v prejšnjem argumentu.
3. Pogoj1. Ta argument podaja kriterije, po katerih se preverja. Nastavite ga lahko na primer na ta način: “> 32”.
4. Razpon pogoja 2, pogoj 2… Tukaj so naslednji pogoji nastavljeni na enak način. Če je treba podati več kot nekaj pogojev, se dodata argumenta Obseg pogoja 3 in Pogoj 3. Sintaksa je enaka za naslednje argumente.

Funkcija omogoča maksimalno obdelavo do 127 parov pogojev in obsegov. 

Uporabite ga lahko na več področjih hkrati (navedli jih bomo le nekaj, seznam je pravzaprav še daljši):

1. Računovodstvo. Na primer, dobro je uporabiti funkcijo SUMMESLIMN ustvariti sumarna poročila, po četrtletjih za porabo nad določenim zneskom, npr. Ali ustvarite poročilo o enem izdelku iz določene cenovne kategorije.
2. Vodenje prodaje. Tudi tukaj je funkcija lahko zelo uporabna. Na primer, soočeni smo z nalogo, da seštejemo le stroške blaga, ki je bilo v določenem času prodano določenemu kupcu. In v takšni situaciji funkcija SUMMESLIMN lahko zelo koristno.
3. izobraževanje. Danes bomo podali več praktičnih primerov s tega področja. Zlasti ga lahko uporabite za pridobitev povzetka ocen učencev. Izbirate lahko za posamezen predmet ali za posamezne ocene. Oseba lahko takoj nastavi več meril, po katerih bo izbrana ocena, kar je zelo priročno in lahko prihrani veliko časa.

Kot lahko vidite, je obseg uporabe te funkcije zelo širok. A to ni njegova edina zasluga. Oglejmo si še nekaj prednosti, ki jih ima ta funkcija:

1. Možnost nastavitve več kriterijev. Zakaj je to prednost? Uporabite lahko običajno funkcijo SUMMESLI! In vse zato, ker je priročno. Za vsako merilo ni treba izvajati ločenih izračunov. Vsa dejanja je mogoče programirati vnaprej, celo prej. kako bo podatkovna tabela oblikovana. To je odličen prihranek časa.
2. Avtomatizacija. Moderna doba je doba avtomatizacije. Samo tisti, ki zna pravilno avtomatizirati svoje delo, lahko veliko zasluži. Zato sposobnost obvladovanja Excela in funkcije SUMMESLIMN še posebej je tako pomembno za vsako osebo, ki želi zgraditi kariero. Poznavanje ene funkcije vam omogoča, da izvedete več dejanj hkrati, kot eno. In tukaj prehajamo na naslednjo prednost te funkcije.
3. Prihranek časa. Samo zaradi dejstva, da ena funkcija opravlja več nalog hkrati.
4. Preprostost. Kljub temu, da je sintaksa na prvi pogled zaradi svoje obsežnosti precej težka, je pravzaprav logika te funkcije zelo preprosta. Najprej se izbere obseg podatkov, nato obseg vrednosti, za katere se preveri skladnost z določenim pogojem. In seveda mora biti naveden tudi sam pogoj. In tako večkrat. Pravzaprav ta funkcija temelji le na enem logičnem konstruktu, zaradi česar je enostavnejša od dobro znanih VPR kljub temu, da se lahko uporablja za iste namene, tudi ob upoštevanju večjega števila kriterijev. 

Značilnosti uporabe funkcije SUMIFS

Obstaja več funkcij uporabe te funkcije, na katere morate biti pozorni. Prvič, ta funkcija ignorira obsege z besedilnimi nizi ali ničelnimi vrednostmi, saj teh podatkovnih tipov ni mogoče sešteti skupaj v aritmetičnem vzorcu, temveč le združiti kot nize. Ta funkcija tega ne more narediti. Prav tako morate biti pozorni na naslednje pogoje:

1. Te vrste vrednosti lahko uporabite kot pogoje za izbiro celic za nadaljnje dodajanje vrednosti, ki jih vsebujejo: številske vrednosti, logični izrazi, sklice na celice itd. 
2. Če se preverja besedilo, logični izrazi ali matematični znaki, so takšni kriteriji navedeni v narekovajih.
3. Ne morete uporabiti izrazov, daljših od 255 znakov.
4. Možno je uporabiti približne kriterije za izbiro vrednosti z uporabo nadomestnih znakov. Vprašaj se uporablja za zamenjavo enega znaka, znak za množenje (zvezdica) pa je potreben za zamenjavo več znakov. 
5. Logične vrednosti, ki so v območju seštevka, se samodejno pretvorijo v številske vrednosti glede na njihov tip. Tako se vrednost "TRUE" spremeni v eno, "FALSE" pa v nič. 
6. Če #VALUE! se v celici pojavi napaka, to pomeni, da je število celic v obsegu pogoja in seštevanja različno. Zagotoviti morate, da so velikosti teh argumentov enake. 

Primeri uporabe funkcije SUMIFS

funkcija SUMMESLIMN ni tako zapleteno, kot se zdi na prvi pogled, se izkaže. Za večjo jasnost pa si poglejmo nekaj praktičnih primerov, kako lahko uporabite to funkcijo SUMMESLIMN. Tako se bo veliko lažje poglobiti v temo.

Dinamično območje seštevanja pogojev

Pa začnimo s prvim primerom. Recimo, da imamo tabelo, ki vsebuje podatke o tem, kako se učenci spopadajo z učnim načrtom pri določenem predmetu. Obstaja niz ocen, uspešnost se ocenjuje na 10-točkovni lestvici. Naloga je poiskati oceno na izpitu tistih študentov, katerih priimek se začne na črko A in imajo minimalno oceno 5.

Tabela izgleda takole.

Da bi lahko izračunali skupno oceno na podlagi zgoraj opisanih meril, moramo uporabiti naslednjo formulo.

Opišemo argumente podrobneje:

1. C3:C14 je naše območje seštevka. V našem primeru sovpada z obsegom pogojev. Iz nje bodo izbrane točke za izračun zneska, vendar le tiste, ki spadajo pod naše kriterije.
2. “>5” je naš prvi pogoj.
3. B3:B14 je drugi obseg seštevka, ki je obdelan tako, da ustreza drugemu kriteriju. Vidimo, da ni naključja z obsegom seštevka. Iz tega sklepamo, da sta lahko obseg seštevanja in obseg pogoja enaka ali pa tudi ne. 
4. “A*” je drugi obseg, ki določa izbor ocen samo za tiste študente, katerih priimek se začne na A. V našem primeru zvezdica pomeni poljubno število znakov. 

Po izračunih dobimo naslednjo tabelo.

Kot lahko vidite, je formula seštela vrednosti na podlagi dinamičnega razpona in na podlagi pogojev, ki jih je določil uporabnik.

Selektivno seštevanje po stanju v Excelu

Predpostavimo, da želimo pridobiti informacije o tem, katero blago je bilo poslano v katere države v zadnjem četrtletju. Nato poiščite skupni prihodek od pošiljk za julij in avgust.

Sama tabela izgleda takole. 

Za določitev končnega rezultata potrebujemo takšno formulo.

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

Kot rezultat izračunov, izvedenih po tej formuli, dobimo naslednji rezultat.

Pozor! Ta formula je videti precej velika, čeprav smo uporabili le dva merila. Če je obseg podatkov enak, lahko znatno skrajšate dolžino formule, kot je prikazano spodaj.

Funkcija SUMIFS za seštevanje vrednosti v več pogojih

Zdaj pa navedimo še en primer za ponazoritev. V tem primeru tabela ostane enaka kot v prejšnjem primeru. 

Uporabimo naslednjo formulo (vendar jo zapišemo kot matrično formulo, to pomeni, da jo vnesemo s kombinacijo tipk CTRL + SHIFT + ENTER).

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

Po funkciji SUMMESLIMN bo seštel matriko vrednosti na podlagi meril, določenih v formuli (to je državi Kitajska in Gruzija), dobljeno matriko se sešteje z običajno funkcijo SUM, ki je zapisan kot matrična formula.

Če so bili pogoji posredovani kot konstanta polja za več kot en par, potem bo formula dala napačen rezultat.

Zdaj pa si poglejmo tabelo s skupnimi vrednostmi.

Kot vidite, nam je uspelo. Zagotovo vam bo tudi uspelo. Velik uspeh na tem področju. To je zelo preprosta funkcija, ki jo razume oseba, ki je šele stopila na pot učenja Excela. In to funkcijo že vemo SUMMESLIMN vam omogoča, da ste učinkoviti na katerem koli področju dejavnosti, od računovodstva do celo izobraževanja. Tudi če gradite kariero na katerem koli drugem področju, ki ni bilo opisano zgoraj, vam bo ta funkcija še vedno pomagala zaslužiti denar. Zato je dragocena.

Najpomembneje pa je, da vam omogoča prihranek časa, ki pa je na žalost omejen vir. Zdi se, da je za uporabo dveh funkcij na voljo nekaj sekund, toda ko morate izvesti ogromno ponavljajočih se operacij, se te sekunde seštejejo v ure, ki bi jih lahko porabili za kaj drugega. Zato priporočamo, da vadite uporabo te funkcije. Poleg tega je neverjetno preprost.