Funkcija IF v Excelu. Primeri (z več pogoji)

Ena glavnih prednosti Excelovih preglednic je možnost programiranja funkcionalnosti določenega dokumenta. Kot večina ljudi ve iz šolskega pouka računalništva, so ena glavnih komponent, ki vam omogočajo, da to uporabite v praksi, logični operatorji. Eden od njih je operater IF, ki omogoča izvedbo določenih dejanj, ko so izpolnjeni določeni pogoji. 

Na primer, če se vrednost ujema z določeno, se v celici prikaže ena oznaka. Če ne, je drugače. Oglejmo si to učinkovito orodje podrobneje v praksi.

Funkcija IF v Excelu (splošne informacije)

Vsak program, tudi če je majhen, nujno vsebuje zaporedje dejanj, ki se imenuje algoritem. Morda je videti takole:

1. Preverite celoten stolpec A za soda števila.
2. Če najdemo sodo število, seštejemo takšne in drugačne vrednosti.
3. Če sodo število ni najdeno, se prikaže napis "ni najdeno".
4. Preverite, ali je dobljeno število sodo. 
5. Če da, ga dodajte vsem sodim številom, izbranim v odstavku 1.

In tudi če je to le hipotetična situacija, ki v resničnem življenju verjetno ne bo potrebna, izvedba katere koli naloge nujno pomeni prisotnost podobnega algoritma. Pred uporabo funkcije ČE, v glavi morate imeti jasno predstavo o tem, kakšen rezultat želite doseči. 

Sintaksa funkcije IF z enim pogojem

Vsaka funkcija v Excelu se izvaja s formulo. Vzorec, po katerem morajo biti podatki posredovani funkciji, se imenuje sintaksa. V primeru operaterja IF, bo formula v tej obliki.

=IF (logični_izraz, vrednost_če_true, vrednost_če_napačna)

Oglejmo si sintakso podrobneje:

1. Boolov izraz. To je sam pogoj, katerega skladnost ali neizpolnjevanje Excel preverja. Preveriti je mogoče tako številčne kot besedilne podatke.
2. Vrednost_če_true. Rezultat, ki bo prikazan v celici, če podatki, ki se preverjajo, izpolnjujejo navedena merila.
3. vrednost_če_napačna. Rezultat, ki se prikaže v celici, če se podatki, ki se preverjajo, ne ujemajo s pogojem.

Tukaj je primer za jasnost.

Tukaj funkcija primerja celico A1 s številko 20. To je prvi odstavek sintakse. Če je vsebina večja od te vrednosti, se v celici, kjer je zapisana formula, prikaže vrednost »več kot 20«. Če situacija ne ustreza temu pogoju - "manj kot ali enako 20".

Če želite v celici prikazati besedilno vrednost, jo morate dati v narekovaje.

Tukaj je še ena situacija. Da lahko študentje opravijo izpit, morajo opraviti izpit. Dijaki so uspeli osvojiti kreditne točke pri vseh predmetih, zdaj pa je ostal še zadnji, ki se je izkazal za odločilnega. Naša naloga je ugotoviti, kateri od študentov so sprejeti na izpite in kateri ne.

Ker želimo preveriti besedilo in ne številko, je prvi argument B2=”cons”.

Sintaksa funkcije IF z več pogoji

Pogosto eno merilo ni dovolj za preverjanje vrednosti. Če morate upoštevati več kot eno možnost, lahko ugnezdite funkcije IF enega v drugega. Na voljo bo več ugnezdenih funkcij.

Da bo bolj jasno, je tukaj sintaksa.

=IF(logični_izraz, vrednost_če_true, IF(logični_izraz, vrednost_če_true, vrednost_če_neresnično))

V tem primeru bo funkcija preverila dva kriterija hkrati. Če je prvi pogoj resničen, se vrne vrednost, dobljena kot rezultat operacije v prvem argumentu. Če ni, se preveri skladnost drugega merila.

Tukaj je primer.

In s pomočjo takšne formule (prikazano na spodnjem posnetku zaslona) lahko analizirate uspešnost vsakega učenca.

Kot lahko vidite, je bil tukaj dodan še en pogoj, vendar se princip ni spremenil. Tako lahko preverite več meril hkrati.

Kako razširiti funkcionalnost IF z uporabo operatorjev IN in ALI

Občasno se zgodi, da takoj preverite skladnost z več merili in ne uporabite logičnih ugnezdenih operatorjev, kot v prejšnjem primeru. Če želite to narediti, uporabite bodisi funkcijo И ali funkcijo OR glede na to, ali morate izpolnjevati več kriterijev hkrati ali vsaj enega od njih. Oglejmo si ta merila podrobneje.

Funkcija IF s pogojem IN

Včasih morate preveriti izraz za več pogojev hkrati. Za to se uporablja funkcija IN, zapisana v prvem argumentu funkcije IF. Deluje takole: če je a enako ena in a enako 2, bo vrednost c.

Funkcija IF s pogojem »ALI«.

Funkcija ALI deluje na podoben način, vendar je v tem primeru resničen samo eden od pogojev. Kolikor je mogoče, je na ta način mogoče preveriti do 30 pogojev. 

Tukaj je nekaj načinov uporabe funkcij И и OR kot argument funkcije IF.

Primerjava podatkov v dveh tabelah

Od časa do časa je mogoče primerjati dve podobni tabeli. Na primer, oseba dela kot računovodja in mora primerjati dve poročili. Obstajajo še druge podobne naloge, kot je primerjava stroškov blaga različnih serij, nato ocene študentov za različna obdobja itd.

Če želite primerjati dve tabeli, uporabite funkcijo COUNTIF. Oglejmo si ga podrobneje.

Recimo, da imamo dve tabeli s specifikacijami dveh kuhinjskih robotov. In primerjati jih moramo, razlike pa poudariti z barvo. To lahko storite s pogojnim oblikovanjem in funkcijo COUNTIF. 

Naša miza izgleda takole.

Izberemo obseg, ki ustreza tehničnim značilnostim prvega kuhinjskega robota.

Nato kliknite naslednje menije: Pogojno oblikovanje – ustvarite pravilo – uporabite formulo za določitev oblikovanih celic.

V obliki formule za oblikovanje zapišemo funkcijo =COUNTIF (obseg za primerjavo; prva celica prve tabele)=0. Tabela s funkcijami drugega kuhinjskega robota je uporabljena kot primerjalno območje.

Prepričati se morate, da so naslovi absolutni (z znakom za dolar pred imeni vrstic in stolpcev). Za formulo dodajte =0, da Excel poišče točne vrednosti.

Po tem morate nastaviti oblikovanje celic. Če želite to narediti, morate poleg vzorca klikniti gumb »Oblika«. V našem primeru uporabimo polnilo, ker je za ta namen najbolj priročno. Lahko pa izberete poljubno oblikovanje.

Ime stolpca smo dodelili kot obseg. To je veliko bolj priročno kot ročno vnašanje obsega.

Funkcija SUMIF v Excelu

Zdaj pa preidimo na funkcije IF, ki bo pomagal zamenjati dve točki algoritma hkrati. Prvi je SUMMESLEY, ki sešteje dve števili, ki izpolnjujeta določen pogoj. Na primer, soočeni smo z nalogo določiti, koliko denarja je treba plačati na mesec vsem prodajalcem. Za to je potrebno.

1. Dodajte vrstico s skupnim prihodkom vseh prodajalcev in kliknite na celico, ki bo vsebovala rezultat po vnosu formule. 
2. Najdemo gumb fx, ki se nahaja poleg vrstice za formule. Nato se prikaže okno, kjer lahko z iskanjem poiščete potrebno funkcijo. Ko izberete operaterja, morate klikniti gumb »V redu«. Toda ročni vnos je vedno možen.
   

   

3. Nato se prikaže okno za vnos argumentov funkcije. Vse vrednosti je mogoče določiti v ustreznih poljih, obseg pa vnesti prek gumba poleg njih.
   

   

4. Prvi argument je obseg. Tukaj vnesete celice, za katere želite preveriti skladnost s kriteriji. Če govorimo o nas, so to delovna mesta zaposlenih. Vnesite obseg D4:D18. Ali pa preprosto izberite celice, ki vas zanimajo.
5. V polje "Kriterij" vnesite položaj. V našem primeru – »prodajalec«. Kot obseg seštevanja označimo tiste celice, kjer so navedene plače zaposlenih (to naredimo tako ročno kot jih izberemo z miško). Kliknite »V redu« in dobimo končne obračunane plače vseh zaposlenih, ki so prodajalci.

Strinjam se, da je zelo priročno. Ali ni?

Funkcija SUMIFS v Excelu

Ta funkcija vam omogoča, da določite vsoto vrednosti, ki izpolnjujejo več pogojev. Na primer, dobili smo nalogo, da ugotovimo skupno plačo vseh vodij, ki delajo v južni podružnici podjetja.

Dodajte vrstico, kjer bo končni rezultat, in vstavite formulo v želeno celico. Če želite to narediti, kliknite ikono funkcije. Prikaže se okno, v katerem morate najti funkcijo SUMMESLIMN. Nato ga izberite s seznama in odpre se znano okno z argumenti. Toda število teh argumentov je zdaj drugačno. Ta formula omogoča uporabo neskončnega števila kriterijev, vendar je najmanjše število argumentov pet. 

Samo pet jih je mogoče določiti v pogovornem oknu za vnos argumentov. Če potrebujete več kriterijev, jih boste morali vnesti ročno po enaki logiki kot prva dva.

Oglejmo si glavne argumente podrobneje:

1. Razpon seštevanja. Celice, ki jih je treba sešteti.
2. Razpon pogoja 1 – obseg, ki bo preverjen glede skladnosti z določenim kriterijem. 
3. Pogoj 1 je sam pogoj.
4. Obseg kriterijev 2 je drugi obseg, ki bo preverjen glede na kriterij.
5. Pogoj 2 je drugi pogoj.

Nadaljnja logika je podobna. Posledično smo določili plače vseh vodilnih delavcev južne podružnice.

Funkcija COUNTIF v Excelu

Če morate določiti, koliko celic spada pod določeno merilo, uporabite funkcijo COUNTIF. Recimo, da moramo razumeti, koliko prodajalcev dela v tej organizaciji:

1. Najprej dodajte vrstico s številom prodajalcev. Po tem morate klikniti celico, kjer bo prikazan rezultat.
2. Po tem morate klikniti gumb »Vstavi funkcijo«, ki ga najdete na zavihku »Formule«. Odpre se okno s seznamom kategorij. Izbrati moramo postavko »Celoten abecedni seznam«. Na seznamu nas zanima formula COUNTIF. Ko ga izberemo, moramo klikniti gumb »V redu«.
   

   

3. Po tem imamo število prodajalcev, zaposlenih v tej organizaciji. Dobili smo ga s štetjem števila celic, v katerih je zapisana beseda »prodajalec«. Vse je preprosto. 

Funkcija COUNTSLIM v Excelu

Podobno formuli SUMMESLIMN, ta formula šteje število celic, ki ustrezajo več pogojem. Sintaksa je podobna, vendar nekoliko drugačna od formule SUMMESLIMN:

1. Razpon pogojev 1. To je obseg, ki bo preizkušen glede na prvi kriterij.
2. Pogoj 1. Neposredno prvi kriterij.
3. Razpon pogoja 2. To je obseg, ki bo preizkušen glede na drugo merilo. 
4. Pogoj 2.
5. Pogoji območja 3.

In tako naprej.

Torej funkcija IF v Excelu - ne edini, obstaja več njegovih vrst, ki samodejno izvajajo najpogostejša dejanja, kar močno poenostavi življenje osebe. 

V veliki meri zaradi funkcije IF Excelove preglednice veljajo za programabilne. Je več kot le preprost kalkulator. Če pomislite na to, potem funkcija IF je temeljni kamen v kateri koli vrsti programiranja.

Če se torej naučite delati z velikimi količinami podatkov v Excelu, se boste veliko lažje naučili programiranja. Zahvaljujoč logičnim operatorjem imata ti področji res veliko skupnega, čeprav računovodje pogosteje uporabljajo Excel. Toda mehanizem za delo s podatki je večinoma enak. 

Funkcija v pravih rokah IF in njegove različice vam omogočajo, da Excelov list spremenite v popoln program, ki lahko deluje na zapletene algoritme. Razumevanje delovanja funkcije IF je prvi korak k učenju makrov – naslednji korak k bolj prilagodljivemu delu s preglednicami. Ampak to je že bolj profesionalna raven.