IF valem Excel - kui siis avaldused

Laadige alla töövihiku näide

Laadige alla töövihiku näide

See õpetus näitab, kuidas Excel IF funktsioon Excelis, et luua avaldusi, kui on siis.

IF -funktsioonide ülevaade

Funktsioon IF kontrollib, kas mõni tingimus on täidetud. Kui TÕESTI tehke üht, kui VÄÄR, siis tehke teist.

IF Exceli töölehe funktsiooni kasutamiseks valige lahter ja tippige:

(Pange tähele, kuidas valemisisendid ilmuvad)

IF -funktsiooni süntaks ja sisendid:

1	= IF (loogiline_test, väärtus_tõde, väärtus_kasuta_väär)

loogiline_test - loogiline väljend. Näide: A1> 4.

value_if_tõde - Väärtus või arvutus, mida teha, kui loogiline avaldis on TÕENE.

value_if_false - Väärtus või arvutus, mida teha, kui loogiline avaldis on VÄÄR.

IF on „tingimuslik” funktsioon. See tähendab, et määratlete loogilise testi ja see tagastab ühe väärtuse, kui see hindab tõeseks, ja teise väärtuse, kui see on vale

Funktsiooni IF kasutamine

Siin on väga lihtne näide, et saaksite aru, mida ma mõtlen. Proovige Excelisse sisestada järgmine tekst:

1	= KUI (2 + 2 = 4, "See on tõsi", "See on vale!")

Kuna 2 + 2 on tegelikult võrdne 4 -ga, tagastab Excel "See on tõsi!". Kui me seda kasutaksime:

1	= KUI (2 + 2 = 5, "See on tõsi", "See on vale!")

Nüüd tagastab Excel "See on vale!", Sest 2 + 2 ei võrdu 5 -ga.

Siit saate teada, kuidas IF -d arvutustabelis kasutada.

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

Te juhite spordibaari ja määrate erinevatele klientidele individuaalsed vahekaartide piirangud. Olete seadistanud selle arvutustabeli, et kontrollida, kas iga klient on ületanud oma limiiti, sellisel juhul katkestate ta, kuni ta maksab vahekaardi.

Kontrollite, kas C4-D4 (nende vahekaardi summa miinus nende limiit) on suurem kui 0. See on teie loogiline test. Kui see on tõsi, tagastab IF „Jah” - peaksite need katkestama. Kui see on vale, tagastab IF „Ei” - lasete neil joomist jätkata.

Mida IF saab tagasi

Eespool tagasime tekstistringi „Jah” või „Ei”. Kuid võite tagastada ka numbreid või isegi muid valemeid.

Oletame, et mõned teie kliendid kasutavad suuri vahekaarte. Selle peletamiseks hakkate intressi küsima klientidelt, kes ületavad oma piirmäära.

Selleks saate kasutada IF -d:

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

Kui sakk on piirist kõrgem, tagastage vaheleht korrutatuna 0,03 -ga, mis tagastab 3% vahekaardist. Vastasel korral tagastage 0: nad pole vahekaardil, nii et te ei võta intressi.

IF kasutamine koos AND -ga

IF -d saate kombineerida Exceli AND -funktsiooniga <>. Kasutate seda loogilises testis, mis võimaldab määrata testimiseks kaks või enam tingimust. Excel tagastab tõe ainult siis, kui kõik testid on tõesed.

Niisiis, rakendasite oma intressimäära. Kuid mõned teie püsikliendid kurdavad. Varem on nad alati oma vahekaardid maksnud, miks te nüüd nende vastu vaeva näete? Pakute välja lahenduse: te ei võta teatud usaldusväärsetelt klientidelt intressi.

Teete oma arvutustabelisse uue veeru, et tuvastada usaldusväärsed kliendid, ja värskendate IF -avaldust JA -funktsiooniga.

1	= IF (JA (C4> D4, F4 = "ei"), C4*0,03,0)

Vaatame JA osa eraldi:

1	JA (C4> D4, F4 = "Ei")

Pange tähele kahte tingimust:

• C4> D4: kontrollimine, kas nad ületavad vahelehtede piiri, nagu varem
• F4 = ”Ei”: see on uus bitt, mis kontrollib, kas tegemist pole usaldusväärse kliendiga

Nüüd tagastame intressimäära ainult siis, kui klient on üle oma vahekaardi, JA meil on usaldusväärse kliendi veerus „Ei”. Teie püsikliendid on jälle õnnelikud.

Lisateavet leiate Exceli JA funktsiooni <> avalehelt.

IF kasutamine koos OR -ga

VÕI on teine ​​Exceli loogiline funktsioon. Nagu AND, võimaldab see määratleda rohkem kui ühe tingimuse. Kuid erinevalt AND -st tagastab see tõe, kui mõni teie määratletud test on tõene.

Võib -olla pole klientide vahekaardil viibimine ainus põhjus, miks te neid katkestasite. Võib -olla annate mõnele inimesele ajutise keelu muudel põhjustel, näiteks hasartmängude mängimiseks.

Nii et lisate uue veeru keelatud klientide tuvastamiseks ja värskendate oma jaotist „Katkestas?” veerg OR -testiga:

1	= KUI (VÕI (C4> D4, E4 = "Jah"), "Jah", "Ei")

Vaadates ainult VÕI osa:

1	VÕI (C4> D4, E4 = "Jah")

On kaks tingimust:

• C4> D4: kontrollimine, kas nad ületavad vahekaardilimiidi
• F4 = “Jah”: uus osa, kontrollides, kas need on praegu keelatud

See on tõene, kui nad on vahekaardi kohal või kui veerus E on „Jah”. Nagu näete, on Harry nüüd katkestatud, kuigi ta ei ületa vahekaartide limiiti.

Lisateavet leiate Exceli või funktsiooni <> avalehelt.

IF kasutamine koos XOR -iga

XOR on veel üks loogiline funktsioon, mis tagastab "Exclusive Or". See on natuke vähem intuitiivne kui eelmised, mida me arutasime.

Lihtsatel juhtudel määratlete kaks tingimust ja XOR tagastab:

• TRUE, kui kumbki argument on tõene (sama mis tavaline VÕI)
• VÄÄR, kui mõlemad argumendid on tõesed
• VÄÄR, kui mõlemad argumendid on valed

Näide võib selle selgemaks teha. Kujutage ette, et soovite hakata oma töötajatele igakuiseid boonuseid andma:

• Kui nad müüvad toitu üle 800 dollari või jooke üle 800 dollari, annate neile poole boonuse
• Kui nad müüvad mõlemas üle 800 dollari, annate neile täieliku boonuse
• Kui nad müüvad mõlemas alla 800 dollari, ei saa nad boonust.

Te juba teate, kuidas treenida, kui nad saavad täieliku boonuse. Kasutage lihtsalt IF -d JA -ga, nagu varem kirjeldatud.

1	= IF (JA (C4> 800, D4> 800), "Jah", "Ei")

Aga kuidas saaksite välja mõelda, kes saab pool boonust? Siit tuleb XOR:

1	= IF (XOR (C4> = 800, D4> = 800), "Jah", "Ei")

Nagu näete, oli Woody jookide müük üle 800 dollari, kuid mitte toidumüük. Nii saab ta pool boonust. Coachi puhul on vastupidi. Diane ja Carla müüsid mõlema eest üle 800 dollari, seega ei saa nad pool boonust (mõlemad argumendid on TÕESED) ja Rebecca tegi mõlema künnise alla (mõlemad argumendid VÄÄR), nii et valem tagastab uuesti "Ei".

Lugege lisateavet Exceli funktsiooni XOR avalehelt <>.

IF kasutamine koos NOT -ga

NOT on järjekordne Exceli loogiline funktsioon, mida IF -ga väga sageli kasutatakse.

EI pööra loogilise testi tulemust ümber. Teisisõnu kontrollib see, kas mõni tingimus ei ole täidetud.

Saate seda IF -ga kasutada järgmiselt:

1	= IF (JA (C3> = 1985, EI (D3 = "Steven Spielberg"))), "Vaata", "Ära vaata")

Siin on meil tabel andmetega mõne 1980ndate filmi kohta. Tahame tuvastada 1985. aastal või pärast seda ilmunud filmid, mille režissöör pole Steven Spielberg.

Kuna NOT on pesastatud funktsiooni AND, hindab Excel seda kõigepealt. Seejärel kasutab see tulemust JA osana.

Lugege lisateavet Exceli NOT Function <> avalehelt.

Pesastatud IF -avaldused

IF -avalduse saate tagastada ka IF -avalduses. See võimaldab teil teha keerukamaid arvutusi.

Läheme tagasi oma klientide laua juurde. Kujutage ette, et soovite klassifitseerida kliente vastavalt teie võlatasemele:

• $ 0: pole
• Kuni 500 dollarit: madal
• 500–1000 dollarit: keskmine
• Üle 1000 dollari: kõrge

Seda saate teha IF -lausete pesastamisega:

1	= IF (C4 = 0, "puudub", IF (C4 <= 500, "madal", IF (C4 <= 1000, "keskmine", IF (C4> 1000, "kõrge")))))

Seda on lihtsam mõista, kui panete IF -laused eraldi ridadele (ALT + ENTER Windowsis, CTRL + COMMAND + ENTER Macis):

12345

=IF (C4 = 0, "puudub",IF (C4 <= 500, "madal",IF (C4 <= 1000, "keskmine",IF (C4> 1000, "kõrge", "teadmata"))))

Kui C4 on 0, tagastame "Puudub". Vastasel juhul liigume järgmise IF -lause juurde. Kui C4 on võrdne või väiksem kui 500, tagastame "Madal". Vastasel juhul liigume järgmise IF -avalduse juurde … ja nii edasi.

Komplekssete IF -lausete lihtsustamine abiveergudega

Kui teil on mitu pesastatud IF -lauset ja kasutate ka loogikafunktsioone, võivad teie valemid muutuda väga raskeks lugeda, testida ja värskendada.

Seda on eriti oluline meeles pidada, kui arvutustabelit kasutavad teised inimesed. See, mis on teie peas mõistlik, ei pruugi teistele nii selge olla.

Abistajate veerud on suurepärane viis selle probleemi lahendamiseks.

Olete suure ettevõtte rahandusosakonna analüütik. Teil paluti luua arvutustabel, mis kontrollib, kas igal töötajal on õigus saada ettevõtte pensioni.

Siin on kriteeriumid:

Nii et kui olete alla 55 -aastane, peab teil olema vöö all 30 -aastane teenistus. Kui olete 55–59 -aastane, vajate teenust 15 aastat. Ja nii edasi, kuni 65 -aastaseks saamiseni, kui olete abikõlblik olenemata sellest, kui kaua olete seal töötanud.

Selle probleemi lahendamiseks võite kasutada ühte keerulist IF -lauset:

1	= IF (VÕI (F4> = 65, JA (F4> = 62, G4> = 5), JA (F4> = 60, G4> = 10), JA (F4> = 55, G4> = 15), G4) > 30), "Abikõlblik", "Mitte kõlblik")

Vau! Natuke raske oma pead ümber pöörata, kas pole?

Parem lähenemine võib olla abistajate veergude kasutamine. Meil on siin viis loogikatesti, mis vastavad kriteeriumitabeli igale reale. Seda on lihtsam näha, kui lisame valemile reavahe, nagu me varem arutasime:

12345678

= IF (VÕI (F4> = 65,JA (F4> = 62, G4> = 5),JA (F4> = 60, G4> = 10),JA (F4> = 55, G4> = 15),G4> 30), "Sobilik", "Ei kõlba")

Seega võime need viis testi jagada eraldi veergudeks ja seejärel lihtsalt kontrollida, kas mõni neist on tõene:

Tabeli iga veerg E kuni I sisaldab kõiki meie kriteeriume eraldi. Siis on meil J4 -s järgmine valem:

1	= IF (COUNTIF (E4: I4, TRUE), "Sobilik", "Pole sobilik")

Siin on meil IF -lause ja loogiline test kasutab COUNTIF <>, et loendada E4: I4 lahtrite arv, mis sisaldavad tõde.

Kui COUNTIF ei leia tõelist väärtust, tagastab see 0, mida IF tõlgendab kui FALSE, seega tagastab IF väärtuse „Pole sobilik”.

Kui COUNTIF leiab tõeseid väärtusi, tagastab see nende arvu. IF tõlgendab kõiki numbreid peale 0 tõena, nii et tagastab väärtuse „Sobiv”.

Loogiliste testide sel viisil jagamine muudab valemi hõlpsamini loetavaks ja kui midagi läheb valesti, on viga märgata palju lihtsam.

Rühmitamise kasutamine abiveergude peitmiseks

Abistajate veerud hõlbustavad valemi haldamist, kuid kui olete need paika loonud ja teate, et need töötavad õigesti, võtavad need sageli lihtsalt teie arvutustabelis ruumi, lisamata kasulikku teavet.

Võite veerud peita, kuid see võib põhjustada probleeme, kuna peidetud veerge on raske tuvastada, kui te veerupäiseid tähelepanelikult ei vaata.

Parem variant on rühmitamine.

Valige veerud, mida soovite rühmitada, meie puhul E: I. Seejärel vajutage Windowsis klahve ALT + SHIFT + paremnool või Macis COMMAND + SHIFT + K. Võite minna ka lindil vahekaardile „Andmed” ja valida jaotises „Kontuur” „Grupp”.

Näete veerupäiste kohal kuvatavat rühma järgmiselt:

Seejärel vajutage lihtsalt veergude peitmiseks nuppu „-“:

Funktsioon IFS

Pesastatud IF -laused on väga kasulikud, kui peate tegema keerukamaid loogilisi võrdlusi ja peate seda tegema ühes lahtris. Pikemaks muutudes võivad need aga keeruliseks muutuda ning neid on ekraanil raske lugeda ja värskendada.

Microsoft tutvustas rakendustes Excel 2022 ja Excel 365 veel üht funktsiooni IFS, mis hõlbustab selle haldamist. Ülaltoodud pesastatud IF -näite võib saavutada IFS -iga järgmiselt:

1234567

= IFS (C4 = 0, "puudub",C4 <= 500, "madal",C4 <= 1000, "keskmine",C4> 1000, "kõrge",TRUE, "Tundmatu",)

Selle kohta saate lugeda Exceli IFS -funktsiooni <> avalehelt.

IF kasutamine tingimusvorminguga

Exceli tingimusvormingu funktsioon võimaldab teil lahtrit erinevatel viisidel sõltuvalt selle sisust vormindada. Kuna IF tagastab meie loogilise testi põhjal erinevad väärtused, võiksime soovida kasutada tingimusvormingut, et muuta need erinevad väärtused paremini nähtavaks.

Nii et tuleme tagasi meie töötajate boonustabeli juurde varasemast ajast.

Vastame "jah" või "ei" sõltuvalt sellest, millist boonust me soovime anda. See ütleb meile, mida me peame teadma, kuid see teave ei tule meile silma. Proovime seda parandada.

Siin on, kuidas seda teha.

• Valige IF -avaldusi sisaldav lahtrivahemik. Meie puhul on see E4: F8.
• Klõpsake lindi vahekaardi „Avaleht” jaotises „Stiilid” valikul „Tingimuslik vormindamine”.
• Klõpsake „Esiletõstetud lahtrite reeglid” ja seejärel „Võrdne”.
• Tippige esimesse kasti „Jah” (või mis tahes tagastamisväärtus) ja valige teisest kastist soovitud vorming. (Ma valin selle jaoks rohelise).
• Korrake kõigi tagastamisväärtuste puhul (määran ka „Ei” väärtused punaseks)

Siin on tulemus:

IF kasutamine massiivivalemites

Massiiv on väärtuste vahemik ja Excelis kuvatakse massiivid komaga eraldatud väärtustena, mis on suletud sulgudes, näiteks:

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

Massiivide ilu seisneb selles, et need võimaldavad teil arvutada vahemiku iga väärtuse ja seejärel tulemuse tagastada. Näiteks funktsioon SUMPRODUCT võtab kaks massiivi, korrutab need kokku ja summeerib tulemused.

Nii et see valem:

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

… Tagastab 32. Miks? Töötame selle läbi:

12345

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

Me võime sellele pildile tuua IF -lause, nii et iga kordamine toimub ainult siis, kui loogiline test tagastab tõese.

Näiteks võtke need andmed:

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

Märkus. Excelis 2022 ja varasemates versioonides peate massiivivalemiks muutmiseks vajutama klahvikombinatsiooni CTRL + SHIFT + ENTER.

Lõpuks saame midagi sellist:

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

Kui inglise keeles on C -veerus olev nimi võrdne G2 -ga („Olivia”), siis DO korrutage selle rea veergudes D ja E olevad väärtused. Vastasel juhul ärge neid korrutage. Seejärel summeerige kõik tulemused.

Selle valemi kohta saate lisateavet SUMPRODUCT IF valemi <> avalehelt.

KUI Google'i arvutustabelites

Funktsioon IF töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:

lisamärkmed

Kasutage funktsiooni IF, et testida, kas tingimus on tõene. Kui tingimus on ÕIGE, siis tehke ühte asja. Kui see on vale, tehke teine. Tingimus peab olema loogiline avaldis (nt: a1> 5), viide lahtrile, mis sisaldab tõde või vale, või massiiv, mis sisaldab kõiki loogilisi väärtusi.

Funktsioon IF saab korraga testida ainult ühte tingimust. Siiski saate IF tingimuses teisi loogilisi funktsioone "pesasse panna", et korraga mitut tingimust testida.

= kui (JA (a1> 0, a2> 0), TRUE, FALSE)
= kui (OR (a1> 0, a2> 0), TRUE, FALSE)
= kui (XOR (a1> 0, a2> 0), TRUE, FALSE)
VÕI Funktsioonide test, kui üks või mitu tingimused on täidetud.
JA funktsioonide test, kui kõik tingimused on täidetud.
XOR -funktsioonide test, kui üks ja ainus tingimus on täidetud.

IF -funktsiooni saab IF -funktsiooni sees ka „pesastada“:

1	= kui (a1 <0, kui (a2 <0, "mõlemad", "ainult 1"), "ainult üks")

Nüüd mõned konkreetsed näited selle kohta, kuidas IF -funktsioon praktikas toimib:

1. Alustage uue tööraamatuga.

2. Lahtrisse A1 sisestage väärtus 10 (ja vajutage sisestusklahvi)

3. Seejärel sisestage lahtrisse B1 järgmine valem:

1	= KUI (A1> 5, "SUUREM KUI 5", "VÄHEM KUI 5")

4. Ekraan peaks nüüd välja nägema selline:

5. Kui olete valemi õigesti sisestanud, näete lahtris B1 teadet „Suurem kui 5”.

6. Lahtrisse B1 sisestatud valem täidab testi „A1> 5”, st kontrollib, kas lahtri A1 väärtus on suurem kui 5. Praegu on lahtri A1 väärtus 10 - seega on tingimus TÕENE ja teade Ilmub “SUUREM KUI 5”

7. Kui muudame nüüd lahtri A1 väärtuseks 2:

Lahtris B2 olev teade on nüüd “VÄHEM KUI 5”, kuna tingimus on VÄÄR.

8. Saate väärtust lahtris A1 pidevalt muuta ja sõnum lahtris B2 kohandub vastavalt.

9. Loomulikult on olukordi, kus see tingimus võib anda vallatuid tulemusi:

• Mis juhtub, kui sisestame lahtrisse A1 väärtuse 5?

• Mis siis, kui jätame lahtri A1 tühjaks?

• Mis siis, kui paneme lahtrisse A1 teksti, nt fraasi KOER

Lisateavet funktsiooni Excel IF kohta

Nüüd vaatame IF -funktsiooni üksikasjalikumalt. Seda saab kasutada suurte andmemahtude väga lihtsaks analüüsimiseks.

Kujutage ette, et olete piirkonna müügijuht ja teil on müügimeeskond. Saate registreerida iga inimese kogumüügi lihtsas Exceli arvutustabelis:

Oletame, et boonuse kriteeriumiks oli see, et selle isiku müük ületas 40 000 naela. Võiksite lihtsalt andmeid “silmale visata” ja teha kindlaks, et ainult Anton, Newton ja Monique täitsid eesmärgi.

See on üsna lihtne, kui teil on vaid käputäis nimesid. Kui teil on aga mitu, on vigade võimalus olemas. Õnneks saab Exceli IF -funktsiooni kasutades seda teha palju kiiremini ja ohutumalt.

Seadistage uus töövihik ja sisestage andmed ülaltoodud viisil. Seejärel tippige lahtrisse D4 järgmine valem:-

1	= IF (C4> 40000, "BONUS MAKSETAV", "NO BONUS")

nii et teil on:

Pange tähele, kuidas Excel näitab IF -valemi struktuuri - see on kasulik abimälestus.

Kui olete valemi sisestanud, vajutage sisestusklahvi (ENTER) ja näete selle esimese rea hindamist:

Valemit on Martinile hinnatud - kuna ta teenis vähem kui 40 000 naela, pole tal õigust boonusele.

Ja siis lohistame valemid alla, klõpsates paremas alanurgas ja lohistades allapoole, saame kindlaks teha, kas igal inimesel on õigus boonusele või mitte:

Ja me näeme, et Excel on määranud, kellel müügiinimestest on õigus boonusele.

Tagasi kõigi Exceli funktsioonide loendisse

VBA IF avaldused

VBA -s saate kasutada ka If -avaldusi. Lisateabe saamiseks klõpsake linki, kuid siin on lihtne näide:

1234567

Alamkatse_IF ()Kui vahemik ("a1"). Väärtus <0 siisVahemik ("b1"). Väärtus = "Negatiivne"Lõpp KuiLõpp Kui

See kood kontrollib, kas lahtri väärtus on negatiivne. Kui jah, kirjutab see järgmisesse lahtrisse negatiivse.