Funkcija IF leidžia atlikti loginį reikšmės ir to, ko tikitės, palyginimą patikrindama sąlygą ir grąžindama rezultatą, jei True (teisinga) arba False (neteisinga).
-
=IF (jei kas nors yra teisinga, atlikite kokius nors veiksmus, kitu atveju atlikite kitus veiksmus)
Taigi, sakinys IF gali turėti du rezultatus. Pirmasis rezultatas gaunamas, jei jūsų palyginimas yra teisingas, antrasis – jei palyginimas yra klaidingas.
IF sakiniai yra itin patikimi ir sudaro daugelio skaičiuoklių modelių pagrindą, tačiau jie sukelia ir daugelį skaičiuoklių problemų. Geriausia, kai IF sakinys taikomas naudojant minimalias sąlygas, pvz., vyras/moteris, taip/ne/galbūt, tačiau kartais gali reikėti įvertinti sudėtingesnius scenarijus, kuriems reikia kartu įdėti* daugiau nei 3 IF funkcijas.
* Įdėjimas reiškia praktiką, kai kelios funkcijos sujungiamos į vieną formulę.
Naudokite funkciją IF , vieną iš loginių funkcijų, kad vienokia reikšmė būtų pateikiama, jei sąlyga teisinga, o kitokia – jei sąlyga klaidinga.
Sintaksė
IF(loginis_testas, reikšmė_jei_teisinga, [reikšmė_jei_klaidinga])
Pavyzdžiui:
-
=IF(A2>B2,"Biudžete numatytų išlaidų viršijimas","GERAI")
-
=IF(A2=B2,B4-A4,"")
|
Argumento pavadinimas |
Aprašas |
|
logical_test (būtina) |
Sąlyga, kurią norite patikrinti. |
|
value_if_true (būtina) |
Reikšmė, kuri turi būti grąžinta, jei logical_test yra rezultatas TRUE. |
|
value_if_false (pasirinktinai) |
Reikšmė, kuri turi būti grąžinta, jei loginis_testas rezultatas yra FALSE. |
Pastabos
Programoje „Excel“ galima įdėti iki 64 skirtingų funkcijų IF, tačiau to daryti nepatariama. Kodėl?
-
Norint tinkamai sukurti kelis funkcijos IF sakinius, reikia gerai juos apgalvoti ir įsitikinti, kad juos galima logiškai teisingai apskaičiuoti atsižvelgiant į kiekvieną sąlygą. Jei savo formulės neįdėsite 100 % tikslumu, ji gali veikti 75 % atvejų, tačiau pateikti netikėtų rezultatų 25 % atvejų. Deja, šansai patekti į 25 % nedideli.
-
Kelis IF sakinius gali būti labai sunku tvarkyti, ypač tada, kai po kurio laiko prie jų grįžtate ir bandote suprasti, ką jūs ar dar blogiau – kitas asmuo nori padaryti.
Jei susidūrėte su IF sakiniu, kuris be perstojo auga, laikas padėti pelę į šalį ir iš naujo apgalvoti savo strategiją.
Pažiūrėkime, kaip tinkamai sukurti sudėtinį įdėtąjį IF sakinį naudojant kelias IF ir kada geriau verta naudoti kitą įrankį iš „Excel“ arsenalo.
Pavyzdžiai
Toliau pateikiamas gana standartinio IF sakinio, norint konvertuoti testo balus į jų raidės įvertinimo atitikmenį, pavyzdys.
-
=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))
Ši sudėtinis įdėtasis IF sakinys yra pagrįstas paprasta logika:
-
Jei testo rezultatas (langelyje D2) yra didesnis už 89, studentas gauna A
-
Jei testo rezultatas yra didesnis už 79, studentas gauna B
-
Jei testo rezultatas yra didesnis už 69, studentas gauna C
-
Jei testo rezultatas yra didesnis už 59, studentas gauna D
-
Kitu atveju studentas gauna F
Šis konkretus pavyzdys yra sąlyginai saugus, nes tikėtina, kad santykis tarp testo rezultatų ir įvertinimų raidėmis nesikeis, taigi jo nereikės daug tvarkyti. Tačiau ką daryti, jei įvertinimus reikia segmentuoti į A+, A, A- ir t. t.? Dabar jūsų keturių sąlygų IF sakinį reikia perrašyti, kad jis apimtų 12 sąlygų! Štai kaip jūsų formulė atrodys dabar:
-
=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
Ji vis dar funkciškai tiksli ir veiks, kaip tikimasi, tačiau ilgai užtruksite norėdami ją užrašyti ir patikrinti, kad įsitikintumėte, jog ji daro tai, ko norite. Kitas aktualus klausimas, kad turėjote rankiniu būdu įvesti rezultatus ir atitinkamus įvertinimus raidėmis. Kokia tikimybė, kad netyčia padarysite rašybos klaidą? Dabar įsivaizduokite, kad bandote tai padaryti 64 kartus su dar sudėtingesnėmis sąlygomis! Žinoma, tai įmanoma, bet ar tikrai esate pasiryžę įdėti tiek pastangų ir galbūt vis tiek padaryti klaidų, kurias bus labai sunku pastebėti?
Patarimas: Kiekvienai funkcijai programoje „Excel“ reikalingas atidaromasis ir uždaromasis skliaustelis (). „Excel“ bandys jums padėti suprasti, kur kas turi būti, nuspalvindama skirtingas jūsų formulės dalis, kai ją redaguosite. Pavyzdžiui, jei redaguotumėte aukščiau pateiktą formulę, kai perkeltumėte žymiklį už kiekvienų iš pabaigos skliaustelių „)“, jiems priklausantys atidaromieji skliausteliai nusidažytų ta pačia spalva. Tai gali būti ypač naudinga naudojant sudėtingas įdėtąsias formules, kai bandote suprasti, ar turite pakankamai atitinkančių skliaustelių.
Papildomi pavyzdžiai
Toliau pateikiamas dažnai pasitaikantis pavyzdys, kai reikia apskaičiuoti pardavimo komisinius pagal gautas pajamas.
-
=IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
Ši formulė nurodo, kad jei IF(C9 yra daugiau už 15 000, reikia pateikti 20 %, jei IF(C9 daugiau už 12 500, reiki pateikti 17,5 % ir t. t...
Nors šis pavyzdys labai panašus į ankstesnį pavyzdį su pažymiais, ši formulė yra puikus pavyzdys, kaip sunku gali būti tvarkyti didelius IF sakinius – ką reikėtų daryti, jei jūsų organizacija nuspręstų įtraukti naujų kompensavimo lygių ar net pakeisti esamas dolerio arba procentines reikšmes? Jūs turėtumėte labai daug darbo!
Patarimas: Galite į formulės juostą įterpti eilučių lūžių, kad būtų lengviau skaityti ilgas formules. Tiesiog prieš tekstą, kurį norite kelti į naują eilutę, paspauskite ALT + ENTER.
Toliau pateikiamas komisinių scenarijaus pavyzdys, kurio logika sugadinta:
Ar matote, kas negerai? Palyginkite pajamų palyginimų tvarką su ankstesniu pavyzdžiu. Kokia tvarka išdėstyta? Visiškai teisingai, išdėstyta nuo apačios į viršų (nuo 5 000 USD iki 15 000 USD), o ne atvirkščiai. Tačiau kodėl tai turėtų būti svarbu? Tai svarbu, nes formulė negali atlikti įvertinimo, jei vertė yra didesnė nei 5 000 USD. Tarkime, kad gavote 12 500 USD pajamų – sakinys IF pateiks 10 %, nes tai daugiau už 5 000 USD sumą ir čia sustos. Tai gali sukelti daug problemų, nes dažnai tokio tipo klaidos lieka nepastebėtos tol, kol nepridaro žalos. Taigi žinodami, kad sudėtingi įdėtieji IF sakiniai turi rimtų trūkumų, ką galite padaryti? Daugeliu atveju galite naudoti funkciją VLOOKUP vietoj to, kad kurtumėte sudėtingą formulę su funkcija IF. Naudojant VLOOKUP pirmiausia reikia sukurti nuorodų lentelę:
-
=VLOOKUP(C2,C5:D17,2,TRUE)
Ši formulė nurodo ieškoti reikšmės C5:C17 diapazono langelyje C2. Radus reikšmę, reikia grąžinti atitinkamą reikšmę iš stulpelio D toje pačioje eilutėje.
-
=VLOOKUP(B9,B2:C6,2,TRUE)
Taip pat ši formulė ieško reikšmės diapazono B2:B22 langelyje B9. Radus reikšmę, reikia grąžinti atitinkamą reikšmę iš stulpelio C toje pačioje eilutėje.
Pastaba: Abi šios VLOOKUP formulių gale naudoja argumentą TRUE, tai reiškia, kad jos turi ieškoti apytikslės atitikties. Kitaip tariant, ji ieškos tikslių peržvalgos lentelės reikšmių, taip pat bet kokių reikšmių, kurios yra tarp jų. Šiuo atveju peržvalgos lentelių turi būti surikiuotos didėjimo tvarka, nuo mažiausios iki didžiausios.
VLOOKUP čia pateikiama daug išsamiau, tačiau tai tikrai yra daug paprasčiau nei 12 lygių sudėtinis įdėtasis IF sakinys! Bet yra ir kitų mažiau akivaizdžių pranašumų:
-
VLOOKUP nuorodų lenteles lengva stebėti.
-
Lentelės reikšmes galima lengvai atnaujinti ir jums nereikia nieko daryti su formule, jei pasikeičia jūsų sąlygos.
-
Jei nenorite, kad žmonėms matytų arba ką nors darytų su jūsų nuorodų lentele, tiesiog perkelkite ją į kitą darbalapį.
Ar žinojote?
Dabar yra funkcija IFS, galinti viena funkcija pakeisti kelis įdėtuosius IF sakinius. Taigi, vietoj mūsų pradinio įvertinimų pavyzdžio, kuris apima 4 įdėtąsias IF funkcijas:
-
=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))
Viską galima atlikti daug paprasčiau, naudojant vieną IFS funkciją:
-
=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")
Funkcija IFS patogi todėl, kad jums nereikia sukti galvos dėl visų šių IF sakinių ir skliaustų.
Pastaba: Ši funkcija pasiekiama tik jei turite „Microsoft 365“ prenumeratą. Jei prenumeruojate „„Microsoft 365““, įsitikinkite, kad naudojate naujausią „Office“ versiją.
Įsigykite arba išbandykite „Microsoft 365“
Reikia daugiau pagalbos?
Visada galite paklausti „Excel“ technologijų bendruomenės specialisto arba gauti palaikymo bendruomenėse.
Susijusios temos
Vaizdo įrašas: išplėstinės IF funkcijos
Funkcija IFS ("Microsoft 365", Excel 2016 ir naujesnės versijos)
Funkcija COUNTIF suskaičiuos reikšmes pagal vieną kriterijų
Funkcija COUNTIFS suskaičiuos reikšmes pagal kelis kriterijus
Funkcija SUMIF susumuos reikšmes pagal vieną kriterijų
Funkcija SUMIFS susumuos reikšmes pagal kelis kriterijus
ir funkcija
OR funkcija
VLOOKUP
"Excel"
formulių apžvalgaKaip išvengti sugadintų formulių
Klaidų aptikimas formulėse
Loginės funkcijos
"Excel" funkcijos (pagal abėcėlę)
"Excel" funkcijos (pagal kategoriją)
