IF yra viena pirmųjų funkcijų, kurių išmokstama, ir viena dažniausiai sugadinamų. Kol sąlyga viena, viskas paprasta. Kai reikia patikrinti ir sumą, ir miestą, arba priskirti vieną iš keturių kategorijų, formulės ima augti į vienos eilutės pastraipas.
Pagrindinė formulė
=IF(D2>=1000;"Didelis";"Mažas")
IF turi tris dalis: ką tikrinti, ką grąžinti, kai sąlyga teisinga, ir ką grąžinti, kai ne. Tekstas rašomas kabutėse, skaičiai be jų. Vietoje teksto galima grąžinti skaičių ar kitą formulę, pavyzdžiui =IF(D2>=1000;D2*0,03;0) apskaičiuos 3 % nuolaidą tik didesniems užsakymams.
AND: kai visos sąlygos turi išsipildyti
Užsakymas laikomas prioritetiniu, jei suma yra bent 1000 € ir klientas iš Vilniaus:
=IF(AND(D2>=1000;C2="Vilnius");"Prioritetas";"")
AND grąžina TRUE tik tada, kai teisingos visos jame nurodytos sąlygos. Sąlygų gali būti ir daugiau nei dvi.
OR: kai užtenka vienos
Nemokamas pristatymas, jei suma bent 500 € arba klientas pažymėtas kaip nuolatinis:
=IF(OR(D2>=500;E2="Nuolatinis");"Nemokamai";"Mokamai")
AND ir OR galima derinti: =IF(AND(C2="Vilnius";OR(D2>=500;E2="Nuolatinis"));...). Tokias formules verta rašyti atidžiai ir patikrinti keliomis eilutėmis, kurios atsiduria ant ribos.
Daugiau nei du rezultatai: įdėtiniai IF ir IFS
Nuolaida pagal užsakymo sumą: nuo 2000 € 5 %, nuo 500 € 3 %, kitais atvejais 0 %. Su įdėtiniais IF:
=IF(D2>=2000;5%;IF(D2>=500;3%;0))
Tas pats su IFS:
=IFS(D2>=2000;5%;D2>=500;3%;TRUE;0)
IFS tikrina sąlygas iš eilės ir grąžina pirmą tinkantį rezultatą. Paskutinė pora su TRUE atlieka „visais kitais atvejais“ vaidmenį. Jei jos nėra ir nė viena sąlyga neišsipildo, IFS grąžina klaidą #N/A.
Abiem atvejais svarbi tvarka: tikrinkite nuo didžiausios ribos. Jei pirmiausia patikrinsite D2>=500, 2500 € užsakymas gaus 3 %, nes ši sąlyga jam irgi teisinga.
IFS yra Microsoft 365, Excel 2019 ir naujesnėse versijose, taip pat Google Sheets.
SWITCH: kai tikrinate vieną langelį su keliomis reikšmėmis
Kai reikia ne palyginti, o pagal tikslią reikšmę priskirti kitą, SWITCH trumpesnė:
=SWITCH(C2;"Vilnius";"Rytai";"Kaunas";"Rytai";"Klaipėda";"Vakarai";"Kita")
Paskutinė reikšmė be poros grąžinama, kai nė viena neatitiko.
Kada IF geriau pakeisti lentele
Jei ribos ir procentai kartais keičiasi, įrašyti juos į formulę yra rizikinga: pakeitus nuolaidų politiką reikės surasti ir perrašyti visas formules. Patikimiau susikurti mažą lentelę:
| Suma nuo | Nuolaida |
|---|---|
| 0 | 0 % |
| 500 | 3 % |
| 2000 | 5 % |
ir ieškoti joje su XLOOKUP:
=XLOOKUP(D2;Nuolaidos!A:A;Nuolaidos!B:B;0;-1)
Dabar nuolaidą keičiate vienoje vietoje, o formulės lieka tos pačios. Kaip veikia paskutinis argumentas -1, aprašiau straipsnyje apie XLOOKUP funkciją.
IF su tekstu: ar langelyje yra žodis
IF pati nemoka ieškoti dalies teksto, todėl =IF(A2="*skubu*";...) neveiks. Žvaigždutės veikia COUNTIF funkcijoje, todėl ją galima naudoti kaip sąlygą:
=IF(COUNTIF(A2;"*skubu*");"Skubus";"")
Tą patį daro ISNUMBER ir SEARCH: =IF(ISNUMBER(SEARCH("skubu";A2));"Skubus";""). SEARCH neskiria didžiųjų ir mažųjų raidžių, o FIND skiria.
Dažniausios klaidos
- Skaičius kabutėse.
=IF(D2>="1000";...)lygina su tekstu, o ne su skaičiumi, ir rezultatas būna netikėtas. Skaičius rašykite be kabučių. - Tuščias langelis laikomas nuliu. Jei D2 tuščias, sąlyga
D2<500bus teisinga. Kai tai svarbu, pirmiausia patikrinkite, ar langelis netuščias:=IF(D2="";"";IF(D2>=1000;"Didelis";"Mažas")). - > ir >= painiava. Užsakymas lygiai už 500 € su sąlyga
D2>500nuolaidos negaus. Patikrinkite ribines reikšmes. - Skaičiai, išsaugoti kaip tekstas. Jei duomenys iš eksporto, palyginimai su skaičiais gali neveikti. Kaip tai atpažinti, rašiau straipsnyje kodėl Excel nesumuoja skaičių ir neatpažįsta datų.
Kitos dažnai naudojamos funkcijos surinktos Excel funkcijų sąraše su pavyzdžiais.
- IF ten, kur reikia sumos. Jei IF stulpelis reikalingas tik tam, kad vėliau sumuotumėte pagal sąlygą, dažnai užtenka vienos SUMIFS formulės ar COUNTIFS.