Excel funktsioneerib if (if) ja ifs (if) mitme tingimuse jaoks. Funktsiooni „Kui” kasutamine Excelis Sisestage tingimusega valem
Tingimuste õigsuse testimine ja väljendite loogilised võrdlused, mis on paljudele probleemidele ühised. Tingimuslike valemite loomiseks võite kasutada " Ja", "," ja " Kui ".
Näiteks funktsioon Kui kasutab järgmisi argumente.
Valem, kasutades funktsiooni if
Loogiline avaldis: testitav tingimus.
Value_if_true: tagastab väärtuse, kui tingimus on tõene.
Value_if_false: tagastab väärtuse, kui tingimus on väär.
Lisateavet valemite loomise kohta leiate teemast Valemite loomine ja kustutamine.
Selles artiklis
Tingimusvalemi loomine, mille tulemuseks on Boole'i väärtus (tõene või väär)
Selle ülesande täitmiseks kasutage funktsioone ja ning operaatoreid. või Ja Mitte
Näide
Näite kopeerimine
Tähtis:
Valemid rühmas Valemi sõltuvused klõpsake nuppu Kuva valemid.
|
|
Lisateavet nende funktsioonide kasutamise kohta leiate jaotisest Funktsioonid ja funktsioonid, mitte funktsioonid.
Tingimusvalemi loomine, mille tulemuseks on erinev arvutus või muud väärtused kui tõene või väär
Selle ülesande täitmiseks kasutage funktsioone ja operaatoreid Kui, ja ja või, nagu on näidatud järgmises näites.
Näide
Selle näite lihtsamaks mõistmiseks kopeerige see tühjale paberilehele.
Näite kopeerimine
Tõstke esile selles artiklis toodud näide.
Tähtis:Ärge tõstke esile ridade või veergude pealkirju.
Abis näite esiletõstmine
Vajutage CTRL+C.
Looge Excelis tühi töövihik või leht.
Valige lehel lahter A1 ja vajutage klahvikombinatsiooni CTRL+V.
Tähtis: Et näide õigesti töötaks, tuleb see sisestada lahtrisse A1.
Tulemuste vaatamise ja neid tulemusi tagastavate valemite vaatamise vahel vahetamiseks vajutage klahvikombinatsiooni CTRL+` (aktsentmärk) või vahekaardil Valemid rühmas Valemi sõltuvused klõpsake nuppu Kuva valemid.
Kopeerides näite tühjale lehele, saate seda oma vajadustele vastavaks kohandada.
|
|
Lisateavet nende funktsioonide kasutamise kohta leiate artiklitest Funktsioon ja Funktsioon.
Excelis on palju erinevaid funktsioone, mis töötavad loogilisi tingimusi kontrollides. Näiteks on need funktsioonid IF, COUNTIF, SUMIF jne. Loogilisi tingimusi saab määrata ka tavalistes valemites, kui on vaja saada jaatav vastus: Jah või Ei. Näiteks küsides lihtsaid loogilisi tingimusi, saate vastata järgmistele küsimustele:
- 5 on rohkem kui 8?
- Kas lahtri A5 sisu on väiksem kui 8?
- Või äkki võrdne 8-ga?
Võrdlusoperaatorid Excelis
Excelis on mitmeid standardseid operaatoreid, mida kasutatakse lihtsate Boole'i tingimuste seadmiseks. Kõik kuus võimalikku võrdlusoperaatorit on näidatud allolevas tabelis:
Kuidas Excelis tingimust seada
Võrdlusoperaatorid võimaldavad teil määrata tingimusi, mis tagastavad Boole'i väärtused TRUE või FALSE. Loogiliste tingimuste kasutamise näited on toodud allpool:
=A1=B1– See tingimus tagastab väärtuse TRUE, kui lahtrites A1 ja B1 olevad väärtused on võrdsed või VÄÄR. Selle tingimuse seadmisega saate võrrelda tekstistringe tõstutundlikul viisil. Näiteks kui võrrelda “JAANUAR” ja “Jaanuar”, siis tagastab valem väärtuse TRUE.
=A1>B1– Järgmine valem tagastab TRUE, kui väärtus lahtris A1 on suurem kui lahtris B1. Vastasel juhul tagastab valem FALSE. Selliseid võrdlusi saab määrata ka tekstiga töötamisel.
Näiteks kui lahter A1 sisaldab väärtust "Orange" ja B1 sisaldab väärtust "Arbuus", tagastab valem VÄÄR, kuna "Arbuus" on tähestikulises järjekorras madalam kui "Apelsin". Mida madalam, seda rohkem.
=A1<=B1 – Valem tagastab TRUE, kui väärtus lahtris A1 on väiksem või võrdne lahtri B1 väärtusega. Vastasel juhul on tulemus VALE.
=A1<>B1– Valem tagastab väärtuse TRUE, kui lahtrite A1 ja B1 väärtused ei ole võrdsed. Muidu - VALE.
Excelis on loogilised funktsioonid TÕSI() Ja LIE(), millel pole argumente. Need funktsioonid on mõeldud eelkõige ühilduvuse tagamiseks teiste arvutustabelitega. Väärtused TRUE ja FALSE saate sisestada otse lahtritesse või valemitesse ilma funktsiooni tähistusvormi kasutamata, Excel saab kõigest hästi aru.
Kui olete kindel, et olete seda teemat juba piisavalt hästi valdanud, võite tutvuda artikliga Exceli loogiliste funktsioonide kasutamine keeruliste tingimuste seadmiseks, et õppida, kuidas seada tingimusi näiteks erinevate loogiliste funktsioonide abil. JA() või VÕI().
Loogiline funktsioon KUI Excelis - üks populaarsemaid. See tagastab olenevalt tingimusest tulemuse (väärtuse või muu valemi).
IF funktsioon Excelis
Funktsioonil on järgmine süntaks.
IF( logi_avaldis; väärtus_kui_tõene; [väärtus_kui_vale])
logi_avaldis on tingimus, mida tuleb kontrollida. Näiteks A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.
väärtus_kui_tõene – väärtus või valem, mis tagastatakse esimeses parameetris määratud sündmuse toimumisel.
väärtus_kui_vale on alternatiivne väärtus või valem, mis tagastatakse, kui tingimus ei ole täidetud. Seda välja ei pea täitma. Sel juhul tagastab funktsioon alternatiivse sündmuse korral FALSE.
Väga lihtne näide. Peate kontrollima, kas üksikute toodete müük ületab 30 ühikut. või mitte. Kui need ületavad, peaks valem tagastama "OK", vastasel juhul - "Kustuta". Allpool on arvutus koos tulemusega.
Esimese toote müük on 75, s.o. tingimus, et see on suurem kui 30, on täidetud. Seetõttu tagastab funktsioon selle, mis on määratud järgmisel väljal - "Ok". Teise toote müük on alla 30, seega tingimus (>30) ei ole täidetud ja tagastatakse kolmandal väljal määratud alternatiivne väärtus. See on kogu funktsiooni IF mõte. Venitades arvutust allapoole, saame tulemuse iga toote kohta.
See oli siiski demo. Sagedamini kasutatakse keerukamate kontrollide jaoks Exceli IF valemit. Oletame, et on nädala keskmine kaupade müük ja nende jooksvad saldod. Ostjal on vaja teha saldoprognoos 2 nädala jooksul. Selleks tuleb praegustest laovarudest lahutada kahekordne keskmine nädalamüük.
Seni on kõik loogiline, aga miinused ajavad segadusse. Kas saldod on negatiivsed? Muidugi mitte. Varud ei tohi olla alla nulli. Et prognoos oleks õige, tuleb negatiivsed väärtused asendada nullidega. IF-valem on siin suureks abiks. See kontrollib prognoosist saadud väärtust ja kui see on väiksem kui null, annab jõuga vastuseks 0, vastasel juhul annab arvutuse tulemuse, s.t. mingi positiivne number. Üldiselt sama loogika, kuid väärtuste asemel kasutame tingimusena valemit.
Varude prognoosis ei ole enam negatiivseid väärtusi, mis on üldiselt väga hea.
Exceli IF valemeid kasutatakse laialdaselt ka massiivivalemites. Me ei lähe siin liiga sügavale. Huvilistel soovitan lugeda artiklit teemal. Tõsi, selle artikli arvutus pole enam asjakohane, sest Excel 2016 tutvustas MINESLI ja MAXESLI funktsioone. Kuid on väga kasulik vaadata näidet – see tuleb kasuks mõnes teises olukorras.
IF-valem Excelis – mitme tingimuse näited
Üsna sageli ei ole võimalike tingimuste arv 2 (testitav ja alternatiivne), vaid 3, 4 või rohkem. Sel juhul saate kasutada ka funktsiooni IF, kuid nüüd peate selle üksteise sisse pesastama, määrates kõik tingimused kordamööda. Mõelge järgmisele näitele.
Mitmele müügijuhile tuleb sõltuvalt müügiplaani täitmisest määrata lisatasu. Motivatsioonisüsteem on järgmine. Kui plaan on täidetud vähem kui 90%, siis boonust ei maksta, kui 90% kuni 95% - 10% boonus, 95% kuni 100% - 20% boonus ja kui plaan on ületatud, siis 30%. Nagu näete siit, on 4 võimalust. Nende ühes valemis märkimiseks on vaja järgmist loogilist struktuuri. Kui esimene tingimus on täidetud, siis tekib esimene variant, vastasel juhul, kui on täidetud teine tingimus, siis tekib teine variant, muidu kui... jne. Tingimuste arv võib olla üsna suur. Valemi lõpus on märgitud viimane alternatiiv, mille puhul ei ole täidetud ükski eelnevalt loetletud tingimustest (nagu kolmas väli tavalises IF-valemis). Selle tulemusena näeb valem välja selline:
IF-funktsioonide kombinatsioon töötab nii, et kui mõni määratud tingimus on täidetud, siis järgmisi enam ei kontrollita. Seetõttu on oluline need õiges järjekorras märkida. Kui me alustaksime kontrollimist B2-ga<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.
Valemi kirjutamisel on lihtne segadusse sattuda, seega on hea vaadata kohtspikri.
Lõpus peate sulgema kõik sulud, vastasel juhul viskab Excel veateate.
Exceli IF-funktsioon teeb üldiselt oma tööd hästi. Kuid valik, kui peate pika tingimuste ahela üles kirjutama, pole eriti meeldiv, sest esiteks pole alati võimalik seda esimest korda kirjutada (kas tingimus on valesti näidatud või sulgu ei saa sulgeda); teiseks, vajaduse korral võib sellise valemi mõistmine olla keeruline, eriti kui tingimusi on palju ja arvutused ise on keerulised.
MS Excel 2016 on lisanud funktsiooni IFS, mille nimel kogu see artikkel kirjutati. See on sama IF, ainult teritatud spetsiaalselt paljude tingimuste testimiseks. Nüüd ei pea te sada korda IF-i kirjutama ja avatud sulgusid kokku lugema. Piisab, kui loetleda tingimused ja sulgeda lõpus üks sulg.
See toimib järgmiselt. Võtame ülaltoodud näite ja kasutame Exceli uut IFS-i valemit.
Nagu näete, tundub valemi kirjutamine palju lihtsam ja selgem.
Tähelepanu tasub pöörata järgnevale. Loetleme terminid siiski õiges järjekorras, et vältida vahemike asjatut kattumist. Erinevalt tavalisest IF-st tuleb täpsustada ka viimane alternatiivtingimus. IF määrab ainult alternatiivse väärtuse, mis ilmneb juhul, kui ükski loetletud tingimustest ei ole täidetud. Siin tuleb märkida tingimus ise, mis meie puhul oleks B2>=1. Seda saab aga vältida, kirjutades tingimuse väljale TÕENE, mis näitab, et kui eelnevalt loetletud tingimused ei ole täidetud, ilmneb TRUE ja tagastatakse viimane alternatiivne väärtus.
See artikkel keskendub Exceli tabeliredaktori funktsioonile "Kui". See on seotud rakenduse loogiliste võimalustega. Seda funktsiooni võib pidada kõige levinumaks, mida sageli kasutatakse töö ajal.
Põhijooned
Excelis võimaldab valem "If" teha erinevat tüüpi ülesandeid, mis nõuavad teatud väärtuste võrdlemist ja tulemuse saamist. See lahendus võimaldab kasutada hargnemisalgoritme ja luua otsustuspuu.
Rakenduse näited
Funktsioon näeb välja selline: =IF (ülesanne; tõene; väär). Esimene komponent on loogiline avaldis. See võib ilmuda fraasina või numbrina. Näiteks "10" või "ilma käibemaksuta". See parameeter tuleb täita. Tõene on väärtus, mis kuvatakse tulemusena, kui avaldis on tõene. Väär tähistab andmeid, mis tagastatakse, kui ülesanne on vale.
Kahe lahtri parameetrite võrdsus
Et paremini mõista If-funktsiooni võimalusi Excelis, on vaja tuua näiteid. Seetõttu tasub hakata nendega lähemalt tegelema. Lahtrisse C1 peate sisestama väärtuse 8. Pärast seda peate aadressi D1 väljale sisestama järgmise valemi: =IF(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.
Võime kaaluda veel ühte näidet. Eksamil on mitu õpilast ja nende hindeid. Andmed on järgmised: 5, 4, samuti 3 ja 2. Vastavalt ülesande tingimustele on see ette nähtud iga õpilase kohta tekstikommentaari “sooritatud” või “mittearvestatud” loomiseks. Seega, kui õpilane saab hindeks kolm või rohkem, loetakse ta eksami sooritanuks. Kui tema punktisumma on alla 3, kukkus õpilane testi läbi. Sellise ülesande lahendamiseks peaksite kirjutama järgmise valemi: =IF(C1<3; «не справился»; «сдал»).
Programm hakkab võrdlema iga õpilase tulemusi. Kui indikaator on alla kolme, ilmub vastavasse lahtrisse teade „ebaõnnestus”. Kui hinne on 3 või kõrgem, näete nõutavas veerus kommentaari, et õpilane ei sooritanud eksamit. Tasub teada, et tekstikommentaarid tuleks alati kirjutada jutumärkidesse. Selle funktsiooni kasutamisel saab kasutada järgmisi võrdlusoperaatoreid:< >, =, >, <, >=, <=.
Näited tingimuste „VÕI”, „JA” kasutamise kohta
Selle rakenduse loogilisi võimalusi on vaja täiendavalt kaaluda. Funktsiooni If on võimalik kombineerida võrdlusoperaatoritega. Need on järgmised parameetrid:
"VÕI";
"JA".
Excelis on vaja märkida oluline tingimus: kui õpilase hinne on võrdne või väiksem kui 5, kuid suurem kui 3. Sel juhul tuleb kuvada kommentaar: "sobib" või "ei õnnestunud". Seega ainult need õpilased, kes said A- ja B-pileti. Selle ülesande kirjutamiseks tabeliredaktoris peate kasutama spetsiaalset valemit. Sellel on järgmine vorm: =IF(AND(A1<=5; A1>3); "läbib", "ei").
Kui võtame arvesse keerukamat näidet, peame kasutama "OR" või "AND". Nii saate end kurssi viia valemi kasutamisega Excelis, kui ülesandes on mitu tingimust. Näiteks: =IF(VÕI(A1=5; A1=10); 100; 0). Sel juhul võime järeldada, et kui väärtus lahtris A1 on 5 või 10, kuvab programm tulemuseks 100. Vastasel juhul on see 0. Neid operaatoreid on võimalik rakendada keerukamatele probleemidele lahenduste leidmiseks.
Näiteks peate andmebaasis arvutama võlgnikud, kes peavad maksma rohkem kui 10 000 rubla. Saate seada tingimuseks, et laenu ei ole tagasi makstud rohkem kui kuus kuud ehk kuus kuud. Tänu Exceli tabeliredaktori funktsioonile "If" saate vastavate nimede juurde automaatselt märgi "probleemne klient". Oletame, et lahter A1 sisaldab andmeid, mis näitavad võla kestust (kuudes). Väli B1 sisaldab summat.
Sel juhul esitatakse valem järgmisel kujul: =IF(AND(A1>=6; B1>10000); “probleemne klient”; “”). Seega, kui tuvastatakse määratud tingimustele vastav isik, kuvab programm tema nime kõrval vastava kommentaari. Teiste loendi liikmete jaoks jääb see lahter tühjaks.
Näete ka näidet, kui olukord on kriitiline. Tuleb sisestada vastav kommentaar. Selle tulemusena on valem järgmine: =IF(OR(A1>=6; B1>10000); "kriitiline olukord"; ""). Kui aga programm tuvastab vaste vähemalt ühe parameetri põhjal, kuvatakse vastav märge.
Keerulised ülesanded
Exceli funktsiooni If kasutatakse sisseehitatud vigade vältimiseks nulliga jagamisel. Lisaks kasutatakse seda mõnes muus olukorras. Esimene juhtum on tähistatud kui "DIV/0". Seda võib leida üsna sageli. Tavaliselt juhtub see siis, kui A/B valem nõuab kopeerimist. Samal ajal on B-indikaator üksikutes lahtrites 0. Sellise olukorra vältimiseks tasub kasutada kõnealuse operaatori võimalusi. Seega on nõutav valem järgmine: =IF(B1=0; 0; A1/B1). Seega, kui lahter B1 on täidetud väärtusega "null", kuvab redaktor "0". Vastasel juhul jagab programm A1 indikaatori B1 andmetega ja annab vajaliku tulemuse.
Allahindlus
Nagu praktika näitab, tekib sageli olukordi, mida arutatakse allpool. Peate arvutama allahindlused, võttes arvesse konkreetse toote ostmiseks kulutatud raha kogusummat. Kasutatav maatriks on järgmine: alla 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; rohkem kui 5001 - 7%. Tutvuda saab olukorraga, kui Excelis on külastajate andmebaas ja info ostule kulunud summa kohta. Järgmisena peaksite iga kliendi jaoks arvutama allahindluse. Selleks peate rakendama järgmist avaldist: =IF(A1>=5001; B1*0,93; IF(A1>=3001; B1*0,95;...).
Süsteem kontrollib ostude kogusummat. Kui see ületab 5001 rubla väärtust, korrutatakse toote hind 93 protsendiga. 3001 ühiku märgi ületamisel toimub sarnane toiming, kuid 95% on juba arvesse võetud.
Üks Exceli rakenduse populaarsemaid funktsioone on KUI. See on loogiline võrdlus tähendus ja saadud tulemus. Lihtsamalt öeldes võib funktsioon kõlada järgmiselt:
KUI tingimus on tõene, siis teeme seda, muidu teeme midagi muud
Süntaks See funktsioon on üsna lihtne:
IF (loogiline_tingimus; väärtus juhtumis_TRUE; väärtus juhtumis_FALSE)
Vaatame lähemalt:
- Loogiline_tingimus– väärtus, mida me kontrollime
- Väärtus_juhus_TRUE– tegutsemine, kui nõue on täidetud
- Väärtus käändes_FALSE– toiming, kui loogiline_tingimus ei ole täidetud
Kuidas funktsiooni kasutada
Näide nr 1
Allolevad ekraanipildid näitavad lihtsaimat kasutusjuhtu. Meie esiteks kontrollimine antud tingimus A1>25. KUI see on nõue sooritatud, seejärel väljastage lahtrisse " rohkem kui 25", muidu" väiksem või võrdne 25-ga». 
Näide nr 2
Nüüd raskema ülesande juurde. Allpool vaatleme näidet ettevõtte töötajate ümbersertifitseerimisest. Esialgu näeb tabel välja selline: 
Meie vajalik veerus C kuvage uuesti sertifitseerimise tulemused, mis võivad võtta kahendväärtuse: LÄBI LÄBI või EI LÄBI LÄBI. Meie kriteeriumid on järgmised: kes valis rohkem kui 45 punkti, peetakse teda kes läbis eksam, aga kõik teised seda ei tee.
Ülesande täitmiseks vajalik:
Kasutame mitmeid tingimusi
Vajadusel funktsioon KUI Saab investeeridaüksteise sisse. Seega laiendame lahendusvariante.
Võtame näiteks töötajate ümbersertifitseerimise, millest me varem rääkisime. Muudame tulemuse kriteeriume ja Anname kõigile hinnangu: Halb, hea ja suurepärane. Suurepärane paneme millal punktid ületab 60. Hindamine Saate selle hästi kätte, kui sisestate kohast 45-60 punkti. Noh, muudel juhtudel me määrame Halvasti.
Nagu näitest näha, saate funktsiooni teise ja kolmanda väärtuse asemel asendada tingimuse. Nii lisame vajaliku arvu manuseid. Kuid see on märkimist väärt et pärast 3-5 manuse lisamist muutub valemiga töötamine peaaegu võimatuks, sest see saab olema väga mahukas.
Funktsiooni muud kasutusalad
Veergude või lahtrite summa leidmine
Näiteks on vaja summeerida veeru iga teine lahter. Sel juhul aitab operaatori kasutamine SUMIF. See võimaldab teil töötada ainult nende veergudega, mis vastavad meie nõuetele. Vaatleme juhtumit, kui on vaja paaritute ridade elemendid summeerida. 
Probleemi lahendamiseks vajame sisestada lisaveerg ja nummerdage read 1 ja 0. Just neid andmeid kasutame oma valemis. Näide näitab, millist valemit me kasutasime.
Süntaks: SUMF(lahtri_vahemik; kasutatud_tingimus; [summa_vahemik])
- Vahemik– nõutav argument. Siin saate määrata vahemiku, mille tingimusele vastavust hinnatakse.
- Seisukord_kasutatud– siin näitame, millised lahtrid tuleb summeerida.
- Summa_vahemik– märkige, millist vahemikku liitmiseks kasutada.
Elementide korduste arv lehel
Sel juhul saame lugeda, mitu korda antud element lehel ilmub. Selleks kasutame funktsiooni COUNTIF. See loendab rakkude arvu, mis vaste etteantud väärtusega. 
Selles näites loendame, kui palju kliente on igas linnas. Valemis määrame kõigepealt vahemiku ja teiseks otsitavad väärtused.
Andmete arvutamisel kasutame COUNTIF ja SUMIF
Näites proovime kindlaks teha keskmine sissetulek klientidelt igas linnas. Selleks jagame linna kogutulu klientide arvuga. 
Loendist elemendi mitme esinemise leidmine
Kui meil on pidevalt uuendatav andmete loend, mille kasvades duplikaadid, siis võib selles loendis kirjete leidmine olla keeruline. Lihtsaim viis selle probleemi lahendamiseks on funktsiooni kasutamine VLOOKUP Ja COUNTIF.
Alustama, lisame täiendav veerg, mis lisab kliendi nimele esinemisjärjekorranumbri. Selle tulemusena tekib elemendi esmamainimisel nimi1, järgmine Nimi2 jne. 
Me kasutame otsing klient Christina Aguilera. Kasutades valemit VLOOKUP("Christina Aguilera3", search_range, 2, FALSE), saame selle kliendi kolmanda numbri. Valemis määrame viimaseks väärtuseks FALSE, kuna loendit ei sorteerita ja vajame elementide täpset vastet.
Kuidas vähendada investeeringute arvu IF
Toetatud versioonid kuni EXCEL 2003 (kaasa arvatud). kuni 7 taset pesa IF. Alates Excel 2007-st on see piirang eemaldatud. Kuid väärib märkimist, et vähesed inimesed lähevad alla 3-4 pesitsustaseme.
IF-pesastuste kasutamise vähendamiseks saate kasutada funktsiooni VALIK. See töötab väärtuste või toimingutega antud loendist indeksinumbri järgi.
Süntaks: SELECT (indeksi_number; indeksi_väärtus1; indeksi_väärtus2; [indeksi_väärtus3];…)
Näiteks funktsioon VALIK (1; "Kolmas"; "Teine"; "Esimene"), tagastab meile sõna " Kolmandaks"Kui lisame selle lahtrisse.
Väärib märkimist, et on mõned piirangud. Eelkõige võib indeks olla ainult numbrid.
