IF funkcija Excel. Pavyzdžiai (su keliomis sąlygomis)

Vienas iš pagrindinių Excel skaičiuoklių privalumų yra galimybė užprogramuoti konkretaus dokumento funkcionalumą. Kaip dauguma žmonių žino iš mokyklos informatikos pamokų, vienas iš pagrindinių komponentų, leidžiančių tai pritaikyti praktikoje, yra loginiai operatoriai. Vienas iš jų – IF operatorius, numatantis tam tikrų veiksmų atlikimą, kai įvykdomos tam tikros sąlygos. 

Pavyzdžiui, jei reikšmė atitinka tam tikrą, tada langelyje rodoma viena etiketė. Jei ne, tai kitaip. Pažvelkime į šią veiksmingą priemonę praktikoje išsamiau.

IF funkcija programoje Excel (bendra informacija)

Bet kuri programa, net jei ji yra maža, būtinai turi veiksmų seką, kuri vadinama algoritmu. Tai gali atrodyti taip:

1. Patikrinkite, ar visame A stulpelyje nėra lyginių skaičių.
2. Jei rastas lyginis skaičius, pridėkite tokias ir tokias reikšmes.
3. Jei lyginis skaičius nerastas, parodykite užrašą „nerasta“.
4. Patikrinkite, ar gautas skaičius yra lyginis. 
5. Jei taip, pridėkite jį prie visų 1 dalyje pasirinktų lyginių skaičių.

Ir net jei tai tik hipotetinė situacija, kurios vargu ar prireiks realiame gyvenime, bet kokios užduoties vykdymas būtinai reiškia panašaus algoritmo buvimą. Prieš naudodami funkciją JEI, savo galvoje turite turėti aiškų supratimą, kokį rezultatą norite pasiekti. 

Funkcijos IF sintaksė su viena sąlyga

Bet kuri „Excel“ funkcija atliekama naudojant formulę. Šablonas, pagal kurį duomenys turi būti perduodami funkcijai, vadinamas sintakse. Operatoriaus atveju IF, formulė bus tokio formato.

=JEI (loginė_išraiška, vertė_jei_tiesa, vertė_jei_klaidinga)

Pažvelkime į sintaksę išsamiau:

1. Būlio išraiška. Tai pati sąlyga, kurios atitiktį ar neatitikimą „Excel“ tikrina. Galima patikrinti ir skaitinę, ir tekstinę informaciją.
2. Reikšmė_jei_tiesa. Rezultatas, kuris bus rodomas langelyje, jei tikrinami duomenys atitinka nurodytus kriterijus.
3. value_if_false. Rezultatas, kuris rodomas langelyje, jei tikrinami duomenys neatitinka sąlygos.

Štai pavyzdys aiškumo dėlei.

Čia funkcija lygina langelį A1 su skaičiumi 20. Tai pirmoji sintaksės pastraipa. Jei turinys yra didesnis už šią reikšmę, langelyje, kuriame buvo parašyta formulė, rodoma reikšmė „didesnė nei 20“. Jei situacija neatitinka šios sąlygos – „mažiau arba lygi 20“.

Jei norite langelyje rodyti teksto reikšmę, turite ją įdėti į kabutes.

Čia yra kita situacija. Kad galėtų laikyti egzaminų sesiją, studentai turi išlaikyti testų sesiją. Mokiniams pavyko iškovoti visų dalykų įskaitas, o dabar liko paskutinis, kuris ir tapo lemiamu. Mūsų užduotis – nustatyti, kurie mokiniai yra priimti į egzaminus, o kurie – ne.

Kadangi norime patikrinti tekstą, o ne skaičių, pirmasis argumentas yra B2 = "cons.".

IF funkcijos sintaksė su keliomis sąlygomis

Dažnai vieno kriterijaus nepakanka norint patikrinti vertę. Jei reikia apsvarstyti daugiau nei vieną parinktį, galite sudėti funkcijas IF vienas į kitą. Bus kelios įdėtos funkcijos.

Kad būtų aiškiau, pateikiame sintaksę.

=IF(loginė_išraiška, vertė_jei_tiesa, IF(loginė_išraiška, vertė_jei_tiesa, vertė_jei_klaidinga))

Tokiu atveju funkcija vienu metu patikrins du kriterijus. Jei pirmoji sąlyga yra teisinga, grąžinama reikšmė, gauta atlikus operaciją pirmame argumente. Jei ne, tikrinama, ar laikomasi antrojo kriterijaus.

Štai pavyzdys.

Ir naudodamiesi tokia formule (parodyta žemiau esančioje ekrano kopijoje) galite analizuoti kiekvieno mokinio veiklą.

Kaip matote, čia buvo pridėta dar viena sąlyga, tačiau principas nepasikeitė. Taigi galite patikrinti kelis kriterijus vienu metu.

Kaip išplėsti IF funkcionalumą naudojant AND ir OR operatorius

Kartais pasitaiko situacija, kai reikia nedelsiant patikrinti, ar laikomasi kelių kriterijų, ir nenaudoti loginių įdėtųjų operatorių, kaip ankstesniame pavyzdyje. Norėdami tai padaryti, naudokite bet kurią funkciją И arba funkcija OR priklausomai nuo to, ar reikia atitikti kelis kriterijus iš karto, ar bent vieną iš jų. Pažvelkime į šiuos kriterijus atidžiau.

Funkcija IF su sąlyga IR

Kartais vienu metu reikia patikrinti, ar išraiška atitinka kelias sąlygas. Tam naudojama funkcija IR, parašyta pirmajame funkcijos argumente IF. Tai veikia taip: jei a yra lygus vienetui, o a yra 2, reikšmė bus c.

Funkcija IF su sąlyga „OR“.

Funkcija ARBA veikia panašiai, tačiau šiuo atveju yra tik viena iš sąlygų. Kiek įmanoma, tokiu būdu galima patikrinti iki 30 sąlygų. 

Štai keletas funkcijų naudojimo būdų И и OR kaip funkcijos argumentas IF.

Duomenų palyginimas dviejose lentelėse

Kartkartėmis galima palyginti dvi panašias lenteles. Pavyzdžiui, žmogus dirba buhalteriu ir jam reikia palyginti dvi ataskaitas. Yra ir kitų panašių užduočių, pavyzdžiui, skirtingų partijų prekių savikainos palyginimas, tada skirtingų laikotarpių mokinių vertinimai ir pan.

Norėdami palyginti dvi lenteles, naudokite funkciją COUNTIF. Pažvelkime į tai išsamiau.

Tarkime, kad turime dvi lenteles, kuriose yra dviejų virtuvinių kombainų specifikacijos. Ir mes turime juos palyginti, o skirtumus pabrėžti spalva. Tai galima padaryti naudojant sąlyginį formatavimą ir funkciją COUNTIF. 

Mūsų stalas atrodo taip.

Parenkame asortimentą, atitinkantį pirmojo virtuvinio kombaino technines charakteristikas.

Po to spustelėkite šiuos meniu: Sąlyginis formatavimas – sukurkite taisyklę – naudokite formulę, kad nustatytumėte suformatuotus langelius.

Formatavimo formulės pavidalu įrašome funkciją =COUNTIF (palygintinas diapazonas; pirmasis pirmosios lentelės langelis) = 0. Lentelė su antrojo virtuvinio kombaino savybėmis naudojama kaip palyginimo diapazonas.

Turite įsitikinti, kad adresai yra absoliutūs (su dolerio ženklu prieš eilučių ir stulpelių pavadinimus). Po formulės pridėkite =0, kad „Excel“ ieškotų tikslių reikšmių.

Po to turite nustatyti langelių formatavimą. Norėdami tai padaryti, šalia pavyzdžio turite spustelėti mygtuką „Formatas“. Mūsų atveju naudojame užpildą, nes tai yra patogiausia šiam tikslui. Bet galite pasirinkti bet kokį norimą formatą.

Stulpelio pavadinimą priskyrėme kaip diapazoną. Tai daug patogiau nei rankiniu būdu įvesti diapazoną.

SUMIF funkcija programoje Excel

Dabar pereikime prie funkcijų IF, kuris padės vienu metu pakeisti du algoritmo taškus. Pirmasis yra SUMMESLEY, kuri prideda du skaičius, atitinkančius tam tikrą sąlygą. Pavyzdžiui, mes susiduriame su užduotimi nustatyti, kiek pinigų reikia sumokėti per mėnesį visiems pardavėjams. Tam būtina.

1. Pridėkite eilutę su bendromis visų pardavėjų pajamomis ir įvedę formulę spustelėkite langelį, kuriame bus rezultatas. 
2. Randame mygtuką fx, esantį šalia formulių eilutės. Tada pasirodys langas, kuriame per paiešką galite rasti reikiamą funkciją. Pasirinkę operatorių, turite spustelėti mygtuką „Gerai“. Tačiau rankinis įvedimas visada įmanomas.
   

   

3. Tada pasirodys funkcijos argumentų įvedimo langas. Visas reikšmes galima nurodyti atitinkamuose laukuose, o diapazoną įvesti per šalia jų esantį mygtuką.
   

   

4. Pirmasis argumentas yra diapazonas. Čia įvedate langelius, kuriuose norite patikrinti, ar jie atitinka kriterijus. Jei kalbėtume apie mus, tai yra darbuotojų pareigybės. Įveskite diapazoną D4:D18. Arba tiesiog pasirinkite dominančias ląsteles.
5. Lauke „Kriterijai“ įveskite poziciją. Mūsų atveju – „pardavėjas“. Kaip sumavimo diapazoną nurodome tuos langelius, kuriuose surašyti darbuotojų atlyginimai (tai daroma ir rankiniu būdu, ir pasirenkama pele). Spustelėkite „Gerai“ ir gausime visų darbuotojų, kurie yra pardavėjai, apskaičiuotą atlyginimą.

Sutikite, kad tai labai patogu. Ar ne taip?

SUMIFS funkcija „Excel“.

Ši funkcija leidžia nustatyti reikšmių, atitinkančių kelias sąlygas, sumą. Pavyzdžiui, gavome užduotį nustatyti visų pietiniame įmonės padalinyje dirbančių vadovų bendrą atlyginimą.

Pridėkite eilutę, kurioje bus galutinis rezultatas, ir įterpkite formulę į norimą langelį. Norėdami tai padaryti, spustelėkite funkcijos piktogramą. Atsidarys langas, kuriame turėsite rasti funkciją SUMMESLIMN. Tada pasirinkite jį iš sąrašo ir atsidarys pažįstamas langas su argumentais. Tačiau šių argumentų skaičius dabar skiriasi. Ši formulė leidžia naudoti begalinį kriterijų skaičių, tačiau minimalus argumentų skaičius yra penki. 

Argumentų įvesties dialogo lange galima nurodyti tik penkis. Jei jums reikia daugiau kriterijų, tada jie turės būti įvesti rankiniu būdu pagal tą pačią logiką, kaip ir pirmieji du.

Pažvelkime į pagrindinius argumentus išsamiau:

1. Sumavimo diapazonas. Sumuojamos ląstelės.
2. 1 sąlygų diapazonas – diapazonas, kurio atitiktis tam tikram kriterijui bus tikrinama. 
3. 1 sąlyga yra pati sąlyga.
4. 2 kriterijų diapazonas yra antrasis diapazonas, kuris bus patikrintas pagal kriterijų.
5. 2 sąlyga yra antroji sąlyga.

Tolesnė logika panaši. Dėl to nustatėme visų Pietų filialo vadovų atlyginimus.

COUNTIF funkcija „Excel“.

Jei reikia nustatyti, kiek langelių atitinka tam tikrą kriterijų, naudokite funkciją COUNTIF. Tarkime, kad turime suprasti, kiek pardavėjų dirba šioje organizacijoje:

1. Pirmiausia pridėkite eilutę su pardavėjų skaičiumi. Po to turite spustelėti langelį, kuriame bus rodomas rezultatas.
2. Po to turite spustelėti mygtuką „Įterpti funkciją“, kurį rasite skirtuke „Formulės“. Atsidarys langas su kategorijų sąrašu. Turime pasirinkti elementą „Visas abėcėlinis sąrašas“. Sąraše mus domina formulė COUNTIF. Pasirinkę jį, turime spustelėti mygtuką „Gerai“.
   

   

3. Po to turime šioje organizacijoje dirbančių pardavėjų skaičių. Jis gautas suskaičiavus langelių, kuriose įrašytas žodis „pardavėjas“, skaičių. Viskas paprasta. 

„Excel“ funkcija COUNTSLIM

Panašus į formulę SUMMESLIMN, ši formulė skaičiuoja langelių, atitinkančių kelias sąlygas, skaičių. Sintaksė yra panaši, bet šiek tiek skiriasi nuo formulės SUMMESLIMN:

1. Sąlygų diapazonas 1. Tai diapazonas, kuris bus patikrintas pagal pirmąjį kriterijų.
2. Sąlyga 1. Tiesiogiai pirmasis kriterijus.
3. 2 sąlygų diapazonas. Tai diapazonas, kuris bus patikrintas pagal antrąjį kriterijų. 
4. 2 sąlyga.
5. Diapazono sąlygos 3.

Ir taip toliau.

Taigi funkcija IF Excelyje – ne vienintelė, yra dar kelios jos atmainos, kurios automatiškai atlieka dažniausiai įprastus veiksmus, o tai labai supaprastina žmogaus gyvenimą. 

Daugiausia dėl funkcijos IF „Excel“ skaičiuoklės laikomos programuojamomis. Tai daugiau nei paprastas skaičiuotuvas. Jei pagalvoji, tai funkcija IF yra bet kokio programavimo kertinis akmuo.

Taigi, jei išmoksite dirbti su dideliais duomenų kiekiais programoje „Excel“, išmokti programuoti bus daug lengviau. Loginių operatorių dėka šios sritys tikrai turi daug bendro, nors Excel dažniau naudoja buhalteriai. Tačiau darbo su duomenimis mechanizmas iš esmės yra tas pats. 

Funkcija dešinėse rankose IF ir jo variantai leidžia „Excel“ lapą paversti visaverte programa, galinčia veikti pagal sudėtingus algoritmus. Suprasti, kaip veikia funkcija IF yra pirmasis žingsnis mokantis makrokomandų – kitas žingsnis lankstesniame darbe su skaičiuoklėmis. Bet tai jau yra profesionalesnis lygis.