Funkcija IF – įdėtosios formulės ir kaip išvengti klaidų

Funkcija IF – įdėtosios formulės ir kaip išvengti klaidų

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.

Sudėtinis įdėtasis IF sakinys – formulė E2 yra =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"))))))))))))
  • =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:

  1. Jei testo rezultatas (langelyje D2) yra didesnis už 89, studentas gauna A

  2. Jei testo rezultatas yra didesnis už 79, studentas gauna B

  3. Jei testo rezultatas yra didesnis už 69, studentas gauna C

  4. Jei testo rezultatas yra didesnis už 59, studentas gauna D

  5. 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.

Formulė langelyje D9 yra IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
  • =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:

Formulė D9 netinkama, nes =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))

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ę:

Formulė langelyje D2 yra =VLOOKUP(C2,C5:D17,2,TRUE)
  • =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.

Formulė langelyje C9 yra =VLOOKUP(B9,B2:C6,2,TRUE)
  • =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 daug išsamiau aprašyta čia, tačiau tai tikrai yra daug paprasčiau nei 12 lygmenų 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 „Office 365“ prenumeratą. Jei prenumeruojate „Office 365“, įsitikinkite, kad naudojate naujausią „Office“ versiją.

Išbandykite „Office 365“ arba naujausią „Excel“ versiją

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Susijusios temos

Vaizdo įrašas: išplėstinės IF funkcijos
Funkcija IFS („Office 365“, „Excel 2016“ ir naujesnės versijos)
Funkcija COUNTIF apskaičiuos reikšmes pagal vieną kriterijų
Funkcija COUNTIFS apskaičiuos reikšmes pagal kelis kriterijus
Funkcija SUMIF bus sumuos reikšmes pagal vieną kriterijų
Funkcija SUMIFS sumuos reikšmes pagal kelis kriterijus
Funkcija AND
Funkcija OR
Funkcija VLOOKUP
„Excel“ formulių apžvalga
Kaip išvengti sugadintų formulių
Klaidų tikrinimo naudojimas formulių klaidoms aptikti
Loginės funkcijos
„Excel“ funkcijos (pagal abėcėlę)
„Excel“ funkcijos (pagal kategoriją)

Tobulinkite savo įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×