IF funktsioon Excelis. Näited (mitme tingimusega)

Exceli arvutustabelite üks peamisi eeliseid on võimalus programmeerida konkreetse dokumendi funktsionaalsust. Nagu enamik inimesi kooli informaatika tundidest teab, on üks peamisi komponente, mis võimaldab seda praktikas rakendada, loogilised operaatorid. Üks neist on IF-operaator, mis näeb ette teatud toimingute teostamise, kui teatud tingimused on täidetud. 

Näiteks kui väärtus ühtib teatud väärtusega, kuvatakse lahtris üks silt. Kui ei, siis on teisiti. Vaatame seda tõhusat tööriista praktikas üksikasjalikumalt.

IF-funktsioon Excelis (üldteave)

Iga programm, isegi kui see on väike, sisaldab tingimata toimingute jada, mida nimetatakse algoritmiks. See võib välja näha selline:

1. Kontrollige kogu veerust A paarisarvude osas.
2. Kui leitakse paarisarv, lisa sellised ja sellised väärtused.
3. Kui paarisarvu ei leitud, kuvage kiri "ei leitud".
4. Kontrollige, kas saadud arv on paaris. 
5. Kui jah, siis lisage see kõikidele lõikes 1 valitud paarisarvudele.

Ja isegi kui see on ainult hüpoteetiline olukord, mida reaalses elus tõenäoliselt ei vajata, eeldab mis tahes ülesande täitmine tingimata sarnase algoritmi olemasolu. Enne funktsiooni kasutamist KUI, sul peab peas olema selge ettekujutus, millist tulemust soovid saavutada. 

Funktsiooni IF süntaks ühe tingimusega

Kõik Exceli funktsioonid täidetakse valemi abil. Mustrit, mille järgi andmed tuleb funktsioonile edastada, nimetatakse süntaksiks. Operaatori puhul IF, on valem selles vormingus.

=IF (loogiline_avaldis, väärtus_kui_tõene, väärtus_kui_vale)

Vaatame süntaksit üksikasjalikumalt:

1. Boole'i ​​väljend. See on tingimus ise, mille vastavust või mittevastavust Excel kontrollib. Kontrollida saab nii numbrilist kui ka tekstilist teavet.
2. Väärtus_kui_tõene. Tulemus, mis kuvatakse lahtris, kui kontrollitavad andmed vastavad määratud kriteeriumidele.
3. väärtus_kui_vale. Tulemus, mis kuvatakse lahtris, kui kontrollitavad andmed ei vasta tingimusele.

Siin on selguse huvides näide.

Siin võrdleb funktsioon lahtrit A1 arvuga 20. See on süntaksi esimene lõik. Kui sisu on sellest väärtusest suurem, kuvatakse lahtris, kuhu valem kirjutati, väärtus "suurem kui 20". Kui olukord sellele tingimusele ei vasta – "vähem kui 20 või sellega võrdne".

Kui soovite kuvada tekstiväärtust lahtris, peate selle jutumärkidesse panema.

Siin on teine ​​olukord. Eksamisessioonile pääsemiseks peavad õpilased sooritama testisessiooni. Õpilastel õnnestus kõigis ainetes ainepunkte võita ja nüüd on jäänud viimane, mis osutus määravaks. Meie ülesanne on teha kindlaks, kes õpilastest pääseb eksamitele ja kes mitte.

Kuna tahame kontrollida teksti, mitte numbrit, on esimene argument B2=”cons.”.

IF-funktsiooni süntaks mitme tingimusega

Sageli ei piisa väärtuse kontrollimiseks ühest kriteeriumist. Kui peate kaaluma rohkem kui ühte võimalust, saate funktsioone pesastada IF üks teise sisse. Seal on mitu pesastatud funktsiooni.

Selgemaks muutmiseks on siin süntaks.

=IF(loogiline_avaldis, väärtus_kui_tõene, IF(loogiline_avaldis, väärtus_kui_tõene, väärtus_kui_vale))

Sel juhul kontrollib funktsioon korraga kahte kriteeriumi. Kui esimene tingimus on tõene, tagastatakse esimeses argumendis tehtud toimingu tulemusena saadud väärtus. Kui ei, kontrollitakse teise kriteeriumi vastavust.

Siin on näide.

Ja sellise valemi (näidatud alloleval ekraanipildil) abil saate analüüsida iga õpilase sooritust.

Nagu näha, lisati siia veel üks tingimus, kuid põhimõte pole muutunud. Nii saate korraga kontrollida mitut kriteeriumi.

Kuidas laiendada IF-funktsiooni AND- ja OR-operaatorite abil

Aeg-ajalt tuleb ette olukordi, kus tuleb kohe kontrollida mitme kriteeriumi vastavust ja mitte kasutada loogilisi pesastatud operaatoreid, nagu eelmises näites. Selleks kasutage kas funktsiooni И või funktsioon OR sõltuvalt sellest, kas peate vastama mitmele kriteeriumile korraga või vähemalt ühele neist. Vaatame neid kriteeriume lähemalt.

IF funktsioon tingimusega JA

Mõnikord peate kontrollima avaldist korraga mitme tingimuse jaoks. Selleks kasutatakse funktsiooni JA, mis on kirjutatud funktsiooni esimesse argumendisse IF. See toimib järgmiselt: kui a on võrdne ühega ja a on 2, on väärtuseks c.

IF funktsioon tingimusega "OR".

Funktsioon VÕI töötab sarnaselt, kuid sel juhul on tõene ainult üks tingimus. Nii palju kui võimalik, saab sel viisil kontrollida kuni 30 tingimust. 

Siin on mõned funktsioonide kasutamise viisid И и OR funktsiooni argumendina IF.

Andmete võrdlemine kahes tabelis

Aeg-ajalt on võimalik võrrelda kahte sarnast tabelit. Näiteks töötab inimene raamatupidajana ja tal on vaja võrrelda kahte aruannet. Sarnaseid ülesandeid on teisigi, näiteks erinevate partiide kaupade maksumuse võrdlemine, seejärel õpilaste hinnangud erinevatele perioodidele jne.

Kahe tabeli võrdlemiseks kasutage funktsiooni COUNTIF. Vaatame seda üksikasjalikumalt.

Oletame, et meil on kaks tabelit, mis sisaldavad kahe köögikombaini tehnilisi andmeid. Ja me peame neid võrdlema ja värviga erinevused esile tooma. Seda saab teha tingimusvormingu ja funktsiooni abil COUNTIF. 

Meie tabel näeb välja selline.

Valime esimese köögikombaini tehnilistele omadustele vastava vahemiku.

Seejärel klõpsake järgmistel menüüdel: Tingimuslik vormindamine – loo reegel – vormindatud lahtrite määramiseks kasutage valemit.

Vormindamise valemi kujul kirjutame funktsiooni =COUNTIF (võrdlusvahemik; esimese tabeli esimene lahter)=0. Võrdlusvahemikuna on kasutatud tabelit teise köögikombaini omadustega.

Peate veenduma, et aadressid on absoluutsed (ridade ja veergude nimede ees on dollarimärk). Lisage valemi järele =0, et Excel otsiks täpseid väärtusi.

Pärast seda peate määrama lahtrite vormingu. Selleks peate näidise kõrval klõpsama nuppu "Vorminda". Meie puhul kasutame täidist, sest see on selleks otstarbeks kõige mugavam. Kuid saate valida mis tahes soovitud vormingu.

Oleme määranud veeru nime vahemikuna. See on palju mugavam kui vahemiku käsitsi sisestamine.

SUMIF funktsioon Excelis

Liigume nüüd funktsioonide juurde IF, mis aitab korraga asendada kaks algoritmi punkti. Esimene on SUMMESLEY, mis liidab kaks teatud tingimusele vastavat arvu. Näiteks seisame silmitsi ülesandega kindlaks teha, kui palju raha tuleks kuus kõigile müüjatele maksta. Selleks on see vajalik.

1. Lisage rida kõigi müüjate kogutuludega ja klõpsake pärast valemi sisestamist lahtrit, mis sisaldab tulemust. 
2. Leiame nupu fx, mis asub valemite rea kõrval. Järgmisena ilmub aken, kust leiate otsingu kaudu vajaliku funktsiooni. Pärast operaatori valimist peate klõpsama nuppu "OK". Kuid käsitsi sisestamine on alati võimalik.
   

   

3. Järgmisena ilmub funktsiooni argumentide sisestamise aken. Kõik väärtused saab määrata vastavatel väljadel ja vahemikku saab sisestada nende kõrval oleva nupu kaudu.
   

   

4. Esimene argument on vahemik. Siin sisestate lahtrid, mille vastavust kriteeriumidele soovite kontrollida. Kui meist rääkida, siis need on töötajate ametikohad. Sisestage vahemik D4:D18. Või lihtsalt valige huvipakkuvad lahtrid.
5. Sisestage positsioon väljale „Kriteeriumid“. Meie puhul – “müüja”. Summeerimisvahemikuna märgime need lahtrid, kus on kirjas töötajate palgad (seda tehakse nii käsitsi kui ka valitakse need hiirega). Klõpsake nuppu "OK" ja saame kõigi müüjatest töötajate arvutatud töötasu.

Nõus, et see on väga mugav. Pole see?

SUMIFS funktsioon Excelis

See funktsioon võimaldab teil määrata mitmele tingimusele vastavate väärtuste summa. Näiteks saime ülesandeks määrata kõigi ettevõtte lõunapoolses filiaalis töötavate juhtide kogupalk.

Lisage rida, kus on lõpptulemus, ja sisestage valem soovitud lahtrisse. Selleks klõpsake funktsiooniikoonil. Ilmub aken, kus peate funktsiooni leidma SUMMESLIMN. Järgmisena vali see loendist ja avaneb tuttav argumentidega aken. Kuid nende argumentide arv on nüüd erinev. See valem võimaldab kasutada lõpmatu arvu kriteeriume, kuid argumentide minimaalne arv on viis. 

Argumendi sisestamise dialoogi kaudu saab määrata ainult viis. Kui vajate rohkem kriteeriume, tuleb need sisestada käsitsi sama loogika järgi nagu kaks esimest.

Vaatame peamisi argumente üksikasjalikumalt:

1. Summeerimisvahemik. Summeeritavad lahtrid.
2. Tingimuste vahemik 1 – vahemik, mille vastavust teatud kriteeriumile kontrollitakse. 
3. Tingimus 1 on tingimus ise.
4. Kriteeriumide vahemik 2 on teine ​​vahemik, mida kriteeriumi suhtes kontrollitakse.
5. Tingimus 2 on teine ​​tingimus.

Edasine loogika on sarnane. Sellest tulenevalt määrasime kõikidele Lõuna filiaali juhtide palgad.

COUNTIF funktsioon Excelis

Kui teil on vaja määrata, kui palju lahtreid teatud kriteeriumi alla kuuluvad, kasutage funktsiooni COUNTIF. Oletame, et peame mõistma, kui palju müüjaid selles organisatsioonis töötab:

1. Esmalt lisage rida, mis sisaldab müüjate arvu. Pärast seda peate klõpsama lahtril, kus tulemus kuvatakse.
2. Pärast seda peate klõpsama nuppu "Sisesta funktsioon", mille leiate vahekaardilt "Valemid". Ilmub aken kategooriate loendiga. Peame valima üksuse "Täielik tähestikuline loend". Loendis oleme huvitatud valemist COUNTIF. Pärast selle valimist peame klõpsama nuppu "OK".
   

   

3. Pärast seda on meil selles organisatsioonis töötavate müüjate arv. See saadi, loendades lahtrite arvu, milles on kirjutatud sõna "müüja". Kõik on lihtne. 

COUNTSLIM funktsioon Excelis

Sarnane valemiga SUMMESLIMN, loendab see valem mitmele tingimusele vastavate lahtrite arvu. Süntaks on valemist sarnane, kuid veidi erinev SUMMESLIMN:

1. Tingimuste vahemik 1. See on vahemik, mida testitakse esimese kriteeriumi alusel.
2. Tingimus 1. Otseselt esimene kriteerium.
3. Tingimuste vahemik 2. See on vahemik, mida testitakse teise kriteeriumi alusel. 
4. Tingimus 2.
5. Vahemiku tingimused 3.

Ja nii edasi.

Seega funktsioon IF Excelis – mitte ainuke, selle sorte on veel mitu, mis sooritavad automaatselt kõige tavalisemaid toiminguid, mis lihtsustab oluliselt inimese elu. 

Suuresti tänu funktsioonile IF Exceli tabeleid peetakse programmeeritavateks. See on midagi enamat kui lihtne kalkulaator. Kui järele mõelda, siis funktsioon IF on igasuguse programmeerimise nurgakivi.

Nii et kui õpite Excelis töötama suurte andmemahtudega, on programmeerimist palju lihtsam õppida. Tänu loogilistele operaatoritele on neil aladel tõesti palju ühist, kuigi raamatupidajad kasutavad Excelit sagedamini. Kuid andmetega töötamise mehhanism on suures osas sama. 

Funktsioon paremates kätes IF ja selle variatsioonid võimaldavad teil muuta Exceli lehe täisväärtuslikuks programmiks, mis suudab tegutseda keeruliste algoritmide alusel. Funktsiooni toimimise mõistmine IF on esimene samm makrode õppimise suunas – järgmine samm paindlikumas töös tabelitega. Aga see on juba professionaalsem tase.