Kuidas kasutada Microsoft Excelis mitut IF-lauset

IF-lause Excelis käivitab loogilise testi, mis tagastab ühe väärtuse, kui tingimus on täidetud, ja teise väärtuse, kui see pole täidetud. Ühe Exceli IF-lause kasutamine mahutab ainult põhitoimingud, kuid kui pesastate mitu IF-lauset, saate teha keerulisi toiminguid. Ainus negatiivne külg on see, et vigade vältimiseks peate valemi koostamisel olema ettevaatlik. Alternatiivsed Exceli funktsioonid saavad teha sama tööd väiksema veariskiga. Õppige Excelis pesastatud IF-lausete põhitõdesid ja alternatiive, et leida oma vajadustele parim lähenemisviis.

Funktsioon IF Excelis ütleb sisuliselt "kui see on tõene, tagastage väärtus x, aga kui see pole nii, tagastage y väärtus." Näiteks kui kontrolliksite, kas teie ettevõttes on mitu osakonda olid eelarvest alla või üle, saate funktsiooni abil võrrelda nende kulutusi nende eelarvetega ja lasta sellel tagastada "Üle eelarve", kui osakond kulutas liiga palju või "Eelarve piires", kui ei teinud. Üldiselt kasutate oma avalduse koostamiseks võrdlusoperaatoreid, näiteks:

• = (võrdne)

• (suurem kui)

• = (suurem või võrdne)

• < (väiksem kui)
• <= (väiksem või võrdne)
• <> (ei ole võrdne)

Exceli IF-lause süntaksi mõistmine on oluline samm selle õige kasutamise õppimisel. Funktsiooni vorming on IF(loogiline_test, väärtus_kui_tõene, [väärtus_kui_vale]) kus nurksulud näitavad, et viimane argument on valikuline. Neid on lihtne tõlgendada: loogiline_test tähendab väidet, mida soovite testida, väärtus_kui_tõene on koht, mida soovite, et Excel näitaks, kas test on tõene, ja [väärtus_kui_vale] on valikuline koht selle jaoks, mida soovite Excelis kuvada, kui test on vale.

Sellest piisab, et kasutada Exceli valemit kui-siis-lausete jaoks, mida saab demonstreerida varasema näitega "Üle eelarve/eelarve piires". Kujutage ette, et teil on veerus B erinevate osakondade kulutatud summa ja veerus C eelarvestatud summa, kusjuures väärtused algavad teisest reast. Lahtrisse D2 peaksite tippima =IF(B2>C2, "Üle eelarve", "Eelarve piires") et tagastada otsitav tulemus (arvestades, et sees oleva teksti tagastamiseks peate kasutama jutumärke). Esimene osa ütleb "kui väärtus B2-s on suurem kui väärtus C2-s" (kui kulutused on suuremad kui eelarve), teises osas öeldakse "siis tagastage tekst Üle eelarve ja kolmas osa ütleb: "Kui ei, siis pöörduge tagasi Eelarve piires."

Exceli IF-lause tööriista maksimaalselt ärakasutamine tähendab võimalust kombineerida mitut IF-lauset, kui teil on selleks sobiv ülesanne. Saate lisada oma algsesse IF-lausesse teise IF-lause ja sellesse teise IF-lause ja nii edasi. See on eriti kasulik, kui soovite kategoriseerida midagi ühte mitmest rühmast, kasutades ühte valemit. Näiteks saate kasutada pesastatud IF-lauseid, et teisendada õpilaste testis saavutatud tulemused hindeks.

Mitme IF-lause õigeks kasutamiseks mõelge selle loogikale, mida te Excelil palute. Põhimõtteliselt saate pärast teie tingimust (funktsiooni esimene argument) lisada IF-lause teise IF-lause väärtus_kui_tõene või väärtus_kui_vale tühikud, et öelda Excelile, mida edasi teha. Mõelge sellele nii, et Excel kontrollib lahtrit teise kriteeriumi järgi, kui kõnealune lahter seda ei tee vastama esimesele kriteeriumile (või tõepoolest, kui see vastab esimesele) ja saate seda protsessi jätkata kaua aega. Teisisõnu muudate "kui see, siis see" sõnaks "kui see ja see, siis see" või "kui mitte see, vaid see, siis see".

Kujutage ette, et olete andnud õpilastele testi, kus 85 protsenti või rohkem tulemus on A, 70–85 protsenti on B, 55–70 protsenti on C, 40–55 protsenti on D ja kõik, mis on madalam, on Ebaõnnestumine. Hinded on veerus B, reast 2 kuni reani 11. Õpilaste hinnete kategoriseerimiseks saate kasutada mitme tingimusega hoolikalt koostatud Exceli IF-funktsiooni.

Esimene osa on lihtne (selle näite puhul B2 skoori jaoks): =IF(B2>=85, "A") käsib Excelil tagastada A, kui õpilane saavutas 85 või rohkem. Pesastamine võimaldab teil lisada muid hindeid, kasutades lisatingimuste lisamiseks kolmandat argumenti (mida teha, kui tingimus on vale): =IF(B2>=85, "A", IF(B2>=70, "B")), mis käsib Excelil tagastada 85-punktise või kõrgema tulemuse korral A, kuid kui mitte, tagastada B, kui tulemus on üle 70. Jätkake valemi lõpetamiseks järgmiselt, kasutades iga kord uue IF-lause jaoks kolmandat argumenti: =IF(B2>=85, "A", IF(B2> = 70, "B", IF(B2> = 55, "C", IF(B2> = 40, "D", "Ebaõnnestunud"))) ))

See tundub keeruline, nii et selle tükeldamine peaks ideed kinnistama. Esimesed kaks argumenti ütlevad: "Kui B2 skoor on 85 või rohkem, saab õpilane A" ja kolmas: "Muidu kontrollige järgmist tingimust." The teine ​​IF viib selle väiteni: "(alla 85 punkti saanud õpilaste puhul), kui B2 skoor on 70 või kõrgem, saab õpilane B ja kui mitte, kontrollige järgmine tingimus." Kolmas IF ütleb: "(alla 70 punkti saanud õpilaste puhul), kui B2 skoor on 55 või kõrgem, saab õpilane hinde C ja kui mitte, siis kontrollige järgmine tingimus" ja neljas ütleb "(alla 55 punkti saanud õpilaste puhul) kui B2 skoor on kõrgem kui 40, saab õpilane D, kui mitte, siis õpilane ebaõnnestub."

Kui sisestate IF-lause valemi õigesti, tagastab see ühe Excelile antud valikutest. Kui aga teete vea, võib valem tagastada a 0 või #NAME? teie valemit sisaldavas lahtris.

A 0 viga tähendab, et kas väärtus_kui_tõene või väärtus_kui_vale argument on tühi. Lahendage see probleem, tagades, et väärtus sisestatakse mõlemasse argumendisse või vähemalt argumenti väärtus_kui_tõene argument. The väärtus_kui_vale argumendi saab täielikult eemaldada ja see tagastab väärtuse FALSE, kui te ei pane pärast tähist teist koma väärtus_kui_tõene valdkonnas.

A #NAME? viga annab teada, et olete tõenäoliselt midagi valemis valesti kirjutanud või valesti kirjutanud. Seda võib olla veidi raskem parandada, kuna see võib olla valemi mis tahes osa, kuid parim nõuanne on kõike veelkord kontrollida või valem uuesti välja kirjutada.

Eelmise näite pesastatud IF-funktsiooni valem sai üsna pikk, mis on Excelis mitme IF-lause kasutamise üks peamisi varjukülgi. Need võivad muutuda keeruliseks ja vigu on lihtne teha, kuna peate hoolikalt järgima loogikat, veendumaks, et kõik toimib nii, nagu soovite. Microsoft juhib tähelepanu sellele, et valesti pesastatud valem võib enamasti isegi anda õige tulemuse, kuid harvadel juhtudel võib see anda valed tulemused. Neid on raske märgata.

Tingimuste mitte õigesse järjekorda seadmine on tavaline viga. Hindeliste testide näites, kui alustasite madalama tulemusega =IF(B2>=40, "D", …" ja jätkas teiste loendamisega kasvavas järjekorras, isegi 100 skoor annaks D. Selle põhjuseks on asjaolu, et kõigepealt kontrollitakse esimest tingimust, nii et kui alustasite lausega "kui lahter B2 on 40 või suurem, tagastage D" liigitaks kõik punktid 40 või rohkem kui D, ilma midagi muud kontrollimata, sest see vastab esimesele tingimus. See on suurepärane näide sellest, miks peate oma valemi loogikat järgima.

Samuti peate valemi õigeks sulgemiseks veenduma, et lisate mitme IF-lause lõppu õige arvu sulgusid. Excel aitab seda teha, koordineerides töötamise ajal värve ja tõstes esile sulud.

Tehniliselt saate ühte valemisse paigutada kuni 64 IF-funktsiooni, kuid Microsoft ei soovita ülaltoodud põhjustel sellist funktsiooni kasutada. Asjad lähevad kiiresti keeruliseks ja on tõenäoline, et kui proovite pesastada rohkem kui 10 IF-i avaldust, on Excelis tõenäoliselt juba töö jaoks parem tööriist.

Funktsiooni IF kõige mugavam alternatiiv on IFS-funktsioon, mis on selle versioon funktsioon, mis täidab sama ülesannet kui pesastamine ühes funktsioonis ja saab hakkama kuni 127-ga tingimused. Funktsioonil IFS on lihtsam süntaks: =IFS(loogiline_test1, väärtus_kui_tõene1, [loogiline_test2, väärtus_kui_tõene2], [loogiline_test3, väärtus_kui_tõene3]…) ja nii edasi. Põhimõtteliselt toimib see nagu pesastatud IF-laused, välja arvatud juhul, kui järgnevad IF-laused hõivavad tühiku "väärtus, kui tõene" järel, ilma et oleks vaja funktsiooni uuesti avada.

Testi hinde näite valemit saab palju lihtsamalt kirjutada funktsiooniga IFS. Selle asemel:

=IF(B2>=85, "A", IF(B2> = 70, "B", IF(B2> = 55, "C", IF(B2> = 40, "D", "Ebaõnnestunud"))) ))

lihtsalt kirjuta

=IFS(B2>=85, "A", B2> = 70, "B", B2> = 55, "C", B2> = 40, "D", TÕENE, "Ebaõnnestunud")

The TÕSI kohas enne viimast ütleb "kui ükski muu väärtus ei ole täidetud, tagastage järgmine väärtus."

See funktsioon on saadaval ainult Excel 2019-s või Excel for Office 365-s.

Funktsioon VLOOKUP Excelis on sageli lihtsam alternatiiv mitmele IF-lausele ja on alates 2007. aastast saadaval kõigis Exceli versioonides. See ei ole ideaalne kõikides olukordades, kuna see tagastab kas täpse vaste või lähim number, seega ei saanud seda kasutada hinnete jaoks nagu eelmine näide. Kuid paljudes olukordades võib see säästa palju tööd valemi kirjutamisel, kuigi peate koostama tabeli võimalike väärtustega ühes veerus, näiteks tooted, mida teie pood müüb, ja mida soovite, et funktsioon tagastaks, näiteks toote hind, teises veerus näide.

VLOOKUPi süntaks on =VLOOKUP(otsingu_väärtus, tabeli_massiiv, veergude_indeksi_arv, [vahemiku_otsing]) ja iga komponendi õppimine näitab teile, kuidas seda kasutada. The otsingu_väärtus argument on väärtus, mida soovite kontrollida ja mis peab olema lahter samas veerus kui tabeli algus, mille suhtes soovite seda kontrollida, kas selle kohal või all. The tabeli_massiiv on lahtrite vahemik, mis sisaldab teie teabetabelit, nagu tooted ja nende vastavad hinnad ning veergu_indeksi_arv on veerg, mida Excel peaks otsima, et leida tagastatav väärtus 1 mis näitab kõige vasakpoolsemat veergu, 2 märkides järgmist ja nii edasi. Lõpuks [vahemiku_otsing] on valikuline, kuid sisestage "TRUE", kui soovite, et Excel võtaks vastuse aluseks lähima väärtuse, või "FALSE", kui soovite ainult täpset vastet.