IF Formula Excel - Jei tada teiginiai

Atsisiųskite pavyzdinę darbo knygą

Atsisiųskite darbaknygės pavyzdį

Ši pamoka parodo, kaip naudotis „Excel IF“ funkcija „Excel“, kad sukurtumėte teiginius „Jei tada“.

IF funkcijų apžvalga

Funkcija IF tikrina, ar įvykdyta sąlyga. Jei TIKRA, daryk vieną, jei NETIESA, daryk kitą.

Jei norite naudoti „IF Excel“ darbalapio funkciją, pasirinkite langelį ir įveskite:

(Atkreipkite dėmesį, kaip atrodo formulės įvestys)

IF funkcijos sintaksė ir įvestys:

1	= IF (loginis_testas, vertė_jei_tiesa, vertė_jei_klaidinga)

loginis_testas - Loginė išraiška. Pavyzdys: A1> 4.

value_if_tie - Reikšmė arba skaičiavimas, jei loginė išraiška yra TIESA.

value_if_false - Reikšmė arba skaičiavimas, kurį reikia atlikti, jei loginė išraiška yra FALSE.

IF yra „sąlyginė“ funkcija. Tai reiškia, kad jūs apibrėžiate loginį testą ir jis grąžins vieną vertę, jei bandymas bus vertinamas kaip tiesa, ir kitą vertę, jei ji bus klaidinga

Kaip naudotis IF funkcija

Čia yra labai paprastas pavyzdys, kad galėtumėte suprasti, ką turiu omenyje. Pabandykite įvesti „Excel“:

1	= JEI (2 + 2 = 4, „Tai tiesa“, „Tai klaidinga!“)

Kadangi 2 + 2 iš tikrųjų yra lygus 4, „Excel“ grąžins „Tai tiesa!“. Jei mes tai panaudojome:

1	= JEI (2 + 2 = 5, „Tai tiesa“, „Tai klaidinga!“)

Dabar „Excel“ grąžins „Tai klaidinga!“, Nes 2 + 2 nėra lygus 5.

Štai kaip galite naudoti IF skaičiuoklėje.

1	= IF (C4-D4> 0, C4-D4,0)

Jūs valdote sporto barą ir nustatote individualius skirtukų apribojimus skirtingiems klientams. Nustatėte šią skaičiuoklę, kad patikrintumėte, ar kiekvienas klientas neviršija leistinos ribos. Tokiu atveju jį nutraukiate, kol nesumokės skirtuko.

Patikrinate, ar C4-D4 (dabartinė skirtuko suma, atėmus jų ribą) yra didesnė nei 0. Tai jūsų loginis testas. Jei tai tiesa, IF grąžina „Taip“ - turėtumėte juos nutraukti. Jei tai klaidinga, IF grąžina „Ne“ - jūs leidžiate jiems toliau gerti.

Ką IF gali grąžinti

Aukščiau mes grąžinome teksto eilutę „Taip“ arba „Ne“. Bet taip pat galite grąžinti skaičius ar net kitas formules.

Tarkime, kai kurie jūsų klientai naudoja didelius skirtukus. Norėdami to atgrasyti, pradėsite skaičiuoti palūkanas už klientus, kurie viršija savo limitą.

Tam galite naudoti IF:

1	= IF (C4> D4, C4*0,03,0)

Jei skirtukas yra didesnis nei riba, grąžinkite skirtuką, padaugintą iš 0,03, o tai grąžina 3% skirtuko. Priešingu atveju grąžinkite 0: jie neviršija skirtuko, todėl jūs nemokėsite palūkanų.

IF naudojimas su AND

Galite sujungti IF su „Excel“ funkcija „AND“ <>. Jūs tai naudojate loginiame bandyme, kuris leidžia nurodyti dvi ar daugiau testavimo sąlygų. „Excel“ pateiks tik TRUE, jei VISI testai bus teisingi.

Taigi, jūs įgyvendinote savo palūkanų normą. Tačiau kai kurie jūsų nuolatiniai žmonės skundžiasi. Anksčiau jie visada mokėdavo už skirtukus, kodėl dabar jų griebiatės? Jūs sugalvojate sprendimą: tam tikriems patikimiems klientams nemokėsite palūkanų.

Skaičiuoklėje sukuriate naują stulpelį, kad identifikuotumėte patikimus klientus, ir atnaujinate IF pareiškimą naudodami funkciją AND:

1	= IF (IR (C4> D4, F4 = "Ne"), C4*0,03,0)

Pažvelkime į AND dalį atskirai:

1	IR (C4> D4, F4 = "Ne")

Atkreipkite dėmesį į dvi sąlygas:

• C4> D4: patikrinkite, ar jie neviršija skirtuko limito, kaip ir anksčiau
• F4 = „Ne“: tai naujas bitas, kuris tikrina, ar jie nėra patikimi klientai

Taigi dabar mes grąžiname palūkanų normą tik tuo atveju, jei klientas yra virš jo skirtuko, IR mes turime „Ne“ stulpelyje „Patikimas klientas“. Jūsų nuolatiniai žmonės vėl laimingi.

Skaitykite daugiau pagrindiniame „Excel“ ir funkcijų puslapyje <>.

IF naudojimas kartu su OR

ARBA yra dar viena „Excel“ loginė funkcija. Kaip ir AND, tai leidžia apibrėžti daugiau nei vieną sąlygą. Tačiau, skirtingai nei IR, ji grąžins TIESA, jei BET KOKIAS jūsų apibrėžtas testas yra teisingas.

Galbūt klientai, esantys skirtuke, nėra vienintelė priežastis, dėl kurios juos nutraukėte. Galbūt kai kuriems žmonėms laikinai uždraudžiate dėl kitų priežasčių, galbūt lošimą patalpose.

Taigi pridedate naują stulpelį, kad identifikuotumėte uždraustus klientus, ir atnaujinsite „Nutraukti?“ stulpelis su ARBA bandymu:

1	= IF (ARBA (C4> D4, E4 = "Taip"), "Taip", "Ne")

Žvelgiant tik į ARBA dalį:

1	ARBA (C4> D4, E4 = "Taip")

Yra dvi sąlygos:

• C4> D4: patikrinimas, ar jie neviršija skirtuko limito
• F4 = „Taip“: nauja dalis, tikrinama, ar jie šiuo metu uždrausti

Tai bus teisinga, jei jie yra virš jų skirtuko arba jei stulpelyje „Taip“ yra „Taip“. Kaip matote, Haris dabar yra nutrauktas, nors jis neviršija skirtuko ribos.

Skaitykite daugiau pagrindiniame „Excel“ ARBA funkcijos <> puslapyje.

IF naudojimas su XOR

XOR yra dar viena loginė funkcija, kuri grąžina „Išskirtinį arba“. Tai šiek tiek mažiau intuityvu nei ankstesni, kuriuos aptarėme.

Paprastais atvejais apibrėžiate dvi sąlygas ir XOR grąžins:

• TIESA, jei kuris nors iš argumentų yra teisingas (toks pat kaip įprastas ARBA)
• NETIESA, jei abu argumentai teisingi
• NETIESA, jei abu argumentai klaidingi

Pavyzdys gali tai padaryti aiškiau. Įsivaizduokite, kad norite pradėti teikti mėnesines premijas savo darbuotojams:

• Jei jie parduoda daugiau nei 800 USD maisto ar daugiau nei 800 USD gėrimų, jiems suteiksite pusę premijos
• Jei abu parduoda daugiau nei 800 USD, suteiksite jiems visą premiją
• Jei abu parduoda mažiau nei 800 USD, jie negauna jokios premijos.

Jūs jau žinote, kaip pasportuoti, jei jie gaus visą premiją. Jūs tiesiog naudosite IF su AND, kaip aprašyta anksčiau.

1	= IF (IR (C4> 800, D4> 800), „Taip“, „Ne“)

Bet kaip išsiaiškinti, kas gauna pusę premijos? Štai kur ateina XOR:

1	= IF (XOR (C4> = 800, D4> = 800), „Taip“, „Ne“)

Kaip matote, Woody gėrimų pardavimas viršijo 800 USD, bet ne maisto produktų pardavimas. Taigi jis gauna pusę premijos. Treneriui yra atvirkščiai. Diane ir Carla už abu pardavė daugiau nei 800 USD, todėl negauna pusės premijos (abu argumentai yra TIKRI), o Rebecca padarė žemiau abiejų slenksčio (abu argumentai FALSE), todėl formulė vėl grąžina „Ne“.

Skaitykite daugiau pagrindiniame „Excel XOR“ funkcijos puslapyje <>.

IF naudojimas su NOT

NOT yra dar viena „Excel“ loginė funkcija, kuri labai dažnai naudojama su IF.

NE atšaukia loginio testo rezultatus. Kitaip tariant, ji tikrina, ar nebuvo įvykdyta sąlyga.

Jį galite naudoti su IF taip:

1	= IF (IR (C3> = 1985, NE (D3 = "Stevenas Spielbergas"))), "Žiūrėti", "Nežiūrėk")

Čia mes turime lentelę su kai kurių devintojo dešimtmečio filmų duomenimis. Norime nustatyti filmus, išleistus 1985 m. Arba vėliau, kurių režisierius nebuvo Stevenas Spielbergas.

Kadangi NOT yra įdėtas į funkciją AND, „Excel“ pirmiausia tai įvertins. Tada jis naudos rezultatą kaip dalį IR.

Skaitykite daugiau pagrindiniame „Excel NOT Function“ puslapyje <>.

Įdėti IF pareiškimai

IF ataskaitoje taip pat galite grąžinti IF pareiškimą. Tai leidžia atlikti sudėtingesnius skaičiavimus.

Grįžkime prie mūsų klientų stalo. Įsivaizduokite, kad norite klasifikuoti klientus pagal jų skolos lygį jums:

• 0 USD: nėra
• Iki 500 USD: mažai
• 500–1000 USD: vidutinė
• Daugiau nei 1000 USD: aukšta

Tai galite padaryti „įdėdami“ IF teiginius:

1	= IF (C4 = 0, "Nėra", IF (C4 <= 500, "Žemas", IF (C4 <= 1000, "Vidutinis", IF (C4> 1000, "Didelis"))))

Tai lengviau suprasti, jei IF teiginius dedate į atskiras eilutes („Windows“ ALT + ENTER, „Mac“ - CTRL + COMMAND + ENTER):

12345

=IF (C4 = 0, „Nėra“,IF (C4 <= 500, "mažas",IF (C4 <= 1000, "vidutinė",IF (C4> 1000, „didelis“, „nežinomas“))))

Jei C4 yra 0, grąžiname „Nėra“. Priešingu atveju pereiname prie kito IF teiginio. Jei C4 yra lygus arba mažesnis nei 500, mes grąžiname „Žemas“. Priešingu atveju pereiname prie kito IF teiginio … ir pan.

Sudėtingų IF teiginių supaprastinimas naudojant pagalbinius stulpelius

Jei turite kelis įdėtus IF teiginius ir taip pat naudojate logines funkcijas, jūsų formules gali būti labai sunku skaityti, išbandyti ir atnaujinti.

Tai ypač svarbu nepamiršti, jei skaičiuoklę naudos kiti žmonės. Tai, kas yra prasminga jūsų galvoje, kitiems gali būti ne taip akivaizdu.

Pagalbiniai stulpeliai yra puikus būdas išspręsti šią problemą.

Esate didelės korporacijos finansų skyriaus analitikas. Jūsų buvo paprašyta sukurti skaičiuoklę, kuri patikrintų, ar kiekvienas darbuotojas turi teisę gauti įmonės pensiją.

Štai kriterijai:

Taigi, jei esate jaunesnis nei 55 metų, turite turėti 30 metų tarnybą pagal diržą, kad būtumėte tinkamas. Jei esate 55–59 metų amžiaus, jums reikia 15 metų tarnybos. Ir taip toliau, iki 65 metų amžiaus, kur esate tinkamas, nesvarbu, kiek laiko ten dirbote.

Norėdami išspręsti šią problemą, galite naudoti vieną sudėtingą IF teiginį:

1	= IF (ARBA (F4> = 65, IR (F4> = 62, G4> = 5), IR (F4> = 60, G4> = 10), IR (F4> = 55, G4> = 15), G4) > 30), „Tinkamas“, „Netinkamas“)

Vau! Sunku sukti galvą, ar ne?

Geresnis būdas būtų naudoti pagalbines stulpelius. Čia turime penkis loginius testus, atitinkančius kiekvieną kriterijų lentelės eilutę. Tai lengviau matyti, jei prie formulės pridėsime eilučių pertraukas, kaip jau aptarėme anksčiau:

12345678

= IF (ARBA (F4> = 65,IR (F4> = 62, G4> = 5),IR (F4> = 60, G4> = 10),IR (F4> = 55, G4> = 15),G4> 30), „Tinkamas“, „Netinkamas“)

Taigi, mes galime padalyti šiuos penkis testus į atskirus stulpelius ir tada tiesiog patikrinti, ar kuris nors iš jų yra teisingas:

Kiekviename lentelės stulpelyje nuo E iki I pateikiamas kiekvienas mūsų kriterijus atskirai. Tada J4 turime tokią formulę:

1	= IF (COUNTIF (E4: I4, TRUE), „Tinkamas“, „Netinkamas“)

Čia mes turime IF teiginį, o loginis testas naudoja COUNTIF <>, kad suskaičiuotų ląstelių skaičių E4: I4, kuriose yra TRUE.

Jei COUNTIF neranda tikros vertės, ji grąžins 0, kurią IF interpretuoja kaip FALSE, taigi IF grąžins „Netinkama“.

Jei COUNTIF randa TRUE reikšmių, jis grąžina jų skaičių. IF interpretuoja bet kokį skaičių, išskyrus 0, kaip TIESA, todėl grąžina „Tinkamas“.

Tokiu būdu padalijus loginius testus, formulę lengviau perskaityti, o jei su ja kažkas negerai, daug lengviau pastebėti klaidos vietą.

Grupavimo naudojimas pagalbiniams stulpeliams slėpti

Pagalbiniai stulpeliai palengvina formulės valdymą, tačiau, kai juos įdedate ir žinote, kad jie veikia tinkamai, jie dažnai tiesiog užima vietą jūsų skaičiuoklėje, nepridėdami jokios naudingos informacijos.

Galite paslėpti stulpelius, tačiau tai gali sukelti problemų, nes paslėptus stulpelius sunku aptikti, nebent atidžiai žiūrite į stulpelių antraštes.

Geresnis pasirinkimas yra grupavimas.

Pasirinkite stulpelius, kuriuos norite grupuoti, mūsų atveju E: I. Tada paspauskite ALT + SHIFT + RODYKLĘ DEŠINĖJE „Windows“ arba COMMAND + SHIFT + K „Mac“. Taip pat galite eiti į juostelės skirtuką „Duomenys“ ir skiltyje „Kontūras“ pasirinkti „Grupė“.

Pamatysite grupę, rodomą virš stulpelių antraščių, taip:

Tada tiesiog paspauskite mygtuką „-“, kad paslėptumėte stulpelius:

IFS funkcija

Įterpti IF teiginiai yra labai naudingi, kai reikia atlikti sudėtingesnius loginius palyginimus ir tai padaryti viename lange. Tačiau jie ilgėjant gali būti sudėtingi, juos gali būti sunku skaityti ir atnaujinti ekrane.

Iš „Excel 2022“ ir „Excel 365“ „Microsoft“ pristatė kitą funkciją - IFS, kuri palengvins jos valdymą. Aukščiau pateiktą įdėtą IF pavyzdį galima pasiekti naudojant tokį IFS:

1234567

= IFS (C4 = 0, „Nėra“,C4 <= 500, "mažas",C4 <= 1000, "vidutinis",C4> 1000, „didelis“,TIKRA, „Nežinoma“,)

Visa tai galite perskaityti pagrindiniame „Excel IFS“ funkcijos <> puslapyje.

IF naudojimas su sąlyginiu formatavimu

„Excel“ sąlyginio formatavimo funkcija leidžia formuoti langelį įvairiais būdais, atsižvelgiant į jo turinį. Kadangi IF grąžina skirtingas vertes pagal mūsų loginį testą, galbūt norėtume naudoti sąlyginį formatavimą, kad šias skirtingas vertes būtų lengviau matyti.

Taigi grįžkime prie mūsų darbuotojų premijų lentelės iš anksčiau.

Mes grąžiname „Taip“ arba „Ne“, priklausomai nuo to, kokią premiją norime suteikti. Tai mums pasako, ką turime žinoti, tačiau informacija mums neatsiranda. Pabandykime tai ištaisyti.

Štai kaip tai padarytumėte:

• Pasirinkite langelių diapazoną, kuriame yra jūsų IF teiginiai. Mūsų atveju tai yra E4: F8.
• Juostelės skirtuko „Pagrindinis“ skiltyje „Stiliai“ spustelėkite „Sąlyginis formatavimas“.
• Spustelėkite „Paryškinti ląstelių taisykles“, tada - „Lygus“.
• Į pirmąjį laukelį įveskite „Taip“ (arba bet kokią jums reikalingą grąžinimo vertę), tada iš antrojo langelio pasirinkite norimą formatą. (Aš tam pasirinksiu žalią).
• Pakartokite visas grąžinimo vertes (aš taip pat nustatysiu „Ne“ reikšmes į raudoną)

Štai rezultatas:

IF naudojimas masyvo formulėse

Masyvas yra reikšmių diapazonas, o „Excel“ masyvai vaizduojami kaip kableliais atskirtos vertės, pateiktos skliausteliuose, pavyzdžiui:

1	{1,2,3,4,5}

Masyvų grožis yra tai, kad jie leidžia apskaičiuoti kiekvieną diapazono vertę ir grąžinti rezultatą. Pavyzdžiui, funkcija SUMPRODUCT paima du masyvus, daugina juos ir sumuoja rezultatus.

Taigi ši formulė:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… Grąžina 32. Kodėl? Išspręskime tai:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Į šį paveikslėlį galime įtraukti IF teiginį, kad kiekvienas iš šių daugybių įvyktų tik tuo atveju, jei loginis testas pateikia teisingą.

Pavyzdžiui, paimkite šiuos duomenis:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png "ne"> 1 = SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))

Pastaba: „Excel 2022“ ir ankstesnėse versijose turite paspausti CTRL + SHIFT + ENTER, kad tai paverstų masyvo formule.

Mes baigtume kažką tokio:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png "ne"> 1 $ 2 $: $ 10 $ = $ G2

Anglų kalba, jei pavadinimas C stulpelyje yra lygus G2 („Olivia“), D padauginkite tos eilutės D ir E stulpelių reikšmes. Priešingu atveju jų nepadauginkite. Tada susumuokite visus rezultatus.

Daugiau apie šią formulę galite sužinoti pagrindiniame SUMPRODUCT IF formulės puslapyje <>.

JEI „Google“ skaičiuoklėse

„IF“ funkcija „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:

papildomi užrašai

Naudokite IF funkciją, kad patikrintumėte, ar sąlyga yra TIKRA. Jei sąlyga yra TIKRA, darykite vieną dalyką. Jei tai netiesa, darykite kitą. Sąlyga turi būti loginė išraiška (pvz., A1> 5), nuoroda į langelį, kuriame yra TRUE arba FALSE, arba masyvas, kuriame yra visos loginės vertės.

IF funkcija vienu metu gali išbandyti tik vieną sąlygą. Tačiau IF sąlygoje galite „įdėti“ kitas logines funkcijas, kad iš karto išbandytumėte kelias sąlygas:

= jei (IR (a1> 0, a2> 0), TRUE, FALSE)
= jei (ARBA (a1> 0, a2> 0), TRUE, FALSE)
= jei (XOR (a1> 0, a2> 0), TRUE, FALSE)
ARBA Funkcijų testas, jei vienas ar daugiau sąlygos įvykdytos.
IR Funkcijų testas, jei visi sąlygos įvykdytos.
XOR funkcijų testas, jei vienas ir vienintelis sąlyga yra įvykdyta.

Taip pat galite „įdėti“ IF funkciją į IF funkciją:

1	= jei (a1 <0, jei (a2 <0, "Abu", "tik 1"), "tik vienas")

Dabar keletas konkrečių pavyzdžių, kaip IF funkcija veikia praktiškai:

1. Pradėkite naują darbo knygą.

2. Langelyje A1 įveskite 10 reikšmę (ir paspauskite „Enter“)

3. Tada langelyje B1 įveskite šią formulę:

1	= JEI (A1> 5, „DIDESNIS KAI 5“, „MAŽESNIS KAI 5“)

4. Dabar ekranas turėtų atrodyti taip:

5. Jei teisingai įvedėte formulę, B1 langelyje pamatysite pranešimą „Didesnis nei 5“.

6. B1 langelyje įvesta formulė atlieka testą „A1> 5“, ty patikrina, ar A1 langelio reikšmė yra didesnė nei 5. Šiuo metu A1 langelio reikšmė yra 10, taigi sąlyga yra TRUE ir pranešimas Pasirodo „BIGGER THAN 5“

7. Jei dabar pakeisime A1 langelio reikšmę į 2:

Dabar B2 langelyje esantis pranešimas yra „LESS THAN 5“, nes sąlyga yra FALSE.

8. Galite toliau keisti reikšmę langelyje A1 ir B2 langelyje esantis pranešimas bus atitinkamai pakoreguotas.

9. Žinoma, yra situacijų, kai ši sąlyga gali duoti piktų rezultatų:

• Kas atsitiks, jei A1 langelyje įvesime 5 reikšmę?

• Ką daryti, jei langelį A1 paliksime tuščią?

• Ką daryti, jei į langelį A1 įdėsime šiek tiek teksto, pvz., Frazę DOG

Daugiau apie „Excel IF“ funkciją

Dabar pažvelgsime į IF funkciją išsamiau. Jis gali būti naudojamas labai lengvai analizuoti didelius duomenų kiekius.

Įsivaizduokite, kad esate pardavimų vadybininkas ir turite pardavimo komandą. Galite įrašyti visus kiekvieno asmens pardavimus paprastoje „Excel“ skaičiuoklėje:

Tarkime, kad premijos kriterijus buvo tas, kad to asmens pardavimai viršijo 40 000 svarų. Galite tiesiog „pakelti akis“ duomenis ir nustatyti, kad tikslą pasiekė tik Antonas, Niutonas ir Monique.

Tai gana paprasta, kai turite tik keletą vardų. Tačiau jei turite kelis, yra klaidų. Laimei, naudojant „Excel“ IF funkciją, tai galima padaryti daug greičiau ir saugiau.

Nustatykite naują darbaknygę ir įveskite duomenis, kaip nurodyta aukščiau. Tada langelyje D4 įveskite šią formulę:-

1	= IF (C4> 40000, „BONUS MOKAMAS“, „NO BONUS“)

kad turėtum:

Atkreipkite dėmesį, kaip „Excel“ parodo IF formulės struktūrą, kuri yra naudinga pagalbinė atmintinė.

Įvedę formulę, paspauskite ENTER ir pamatysite, kaip ji vertinama pirmoje eilutėje:

Formulė buvo įvertinta Martinui - kadangi jis uždirbo mažiau nei 40 000 svarų sterlingų, jis neturi teisės į jokią premiją.

Tada vilkite formules žemyn, spustelėdami apatinį dešinįjį kampą ir vilkdami žemyn, kad nustatytume, ar kiekvienas asmuo turi teisę į premiją:

Ir matome, kad „Excel“ nustatė, kuris iš pardavimo žmonių turi teisę į premiją.

Grįžkite į visų „Excel“ funkcijų sąrašą

VBA IF pareiškimai

Taip pat galite naudoti „If Statements“ VBA. Norėdami sužinoti daugiau, spustelėkite nuorodą, bet čia yra paprastas pavyzdys:

1234567

Antrasis testas_IF ()Jei diapazonas ("a1"). Reikšmė <0 tadaDiapazonas („b1“). Vertė = „Neigiamas“Pabaiga JeiPabaiga Jei

Šis kodas patikrins, ar langelio vertė yra neigiama. Jei taip, kitame langelyje bus parašyta „neigiama“.