Kuidas luua tingimuslikku analüüsi IF-funktsiooniga Excelis: täielik juhend

Tingimusfunktsioonide valdamine Excelis võib olla määrava tähtsusega andmete analüüsimisel, korduvate ülesannete automatiseerimisel ja teadlike otsuste tegemisel. Kõigist Exceli pakutavatest tööriistadest paistavad IF-funktsioon ja selle variandid silma võtmeelementidena neile, kes soovivad oma arvutustabelite võimalusi nii tööl kui ka isiklikult maksimeerida.

Kui olete kunagi mõelnud Kuidas Excelis loogilisi tingimusi rakendada nii, et arvutuste tulemused sõltuksid teatud kriteeriumidest, see artikkel on just sulle. Siit leiate kõige täielikuma ja ajakohasema juhendi Tingimusanalüüs IF-funktsiooniga Excelis. Me jagame põhitõed edasijõudnuteks, lisades näpunäiteid, praktilisi näiteid ja soovitusi, et iga kasutaja – algajatest kuni kõige kogenenumateni – saaks neist tööriistadest maksimumi võtta.

Mis on tingimusfunktsioon Excelis?

The tingimusfunktsioonid Excelis võimaldab teil teatud toiminguid teha ainult siis, kui on täidetud teatud tingimused. Teisisõnu, hinda loogikatesti (mis võib olla nii lihtne kui kontrollida, kas väärtus ületab teatud arvu, või nii keeruline kui mitme tingimuse kombineerimine) ja olenevalt tulemusest tagastab Excel ühe või teise väärtuse. See võimaldab andmeid automaatselt klassifitseerida ja teha täpsemaid arvutusi ilma käsitsi sekkumiseta.

Mõned märkimisväärsed näited tingimusfunktsioonidest Excelis on:

• SIKõige mitmekülgsem ja kasutatavam. Hindab tingimust ja tagastab ühe tulemuse, kui see on tõene, või teise, kui see on väär.
• COUNT IF: Loendab, mitu lahtrit vastab teatud kriteeriumile.
• LISA KUI: Summeerib antud tingimusele vastavate lahtrite väärtused.
• KESKMINE.IFArvutab ainult nende väärtuste keskmise, mis vastavad kindlale kriteeriumile.

Nende funktsioonide paindlikkus ja võimsus muutes need asendamatuteks elementideks, olgu selleks siis varude kontroll, finantsanalüüs, personalijuhtimine või mis tahes muu andmetöötlusega seotud valdkond.

Milleks Excelis tingimusanalüüse kasutatakse?

IF-funktsiooni ja tingimuslausete tegelik potentsiaal Excelis seisneb nende võimes automatiseerida igapäevaseid ülesandeid ja parandada andmeanalüüsi täpsust. Mõned kõige levinumad kasutusviisid on:

• Automatiseeri klassifikatsioonidNäiteks õpilaste automaatne märgistamine kas „Arvestatud“ või „Netohinne mittearvestatud“ miinimumhinde alusel.
• Filtreeri asjakohast teavet: Ekstraheeri ja analüüsi ainult andmeid, mis vastavad teatud kriteeriumidele, näiteks teatud summat ületavate müükide tuvastamine.
• Tõsta visuaalselt esile olulised andmedTingimusfunktsioonide kombineerimine tingimusvorminguga, et märkida näiteks eelseisvaid aegumiskuupäevi või keskmisest kõrgemaid väärtusi.
• Abi otsuste tegemiselErinevate tulemuste pakkumine (näiteks allahindluste rakendamine või teadete käivitamine) eeltingimuste kombinatsiooni põhjal.

Need funktsioonid on olulised nii professionaalidele kui ka kodukasutajatele, kes soovivad aega kokku hoida ja oma arvutustabelite usaldusväärsust parandada.

Kuidas IF-funktsioon Excelis töötab: süntaks ja struktuur

IF-funktsioon on Exceli tingimusliku analüüsi kuninganna. Selle põhistruktuur on väga lihtne, kuid väga kasulik.:

=IF(loogiline_test;väärtus_kui_tõene;väärtus_kui_vale)

Selle kolm peamist komponenti on:

• loogiline tõestus: See on tingimus, mida soovite kontrollida (näiteks kas lahtri väärtus on suurem kui 60).
• Väärtus, kui tõene: Tulemus, mis peaks ilmuma, kui tingimus on täidetud.
• Väärtus, kui väärMis peaks juhtuma, kui tingimus ei ole täidetud (võib olla tekst, arv, lahter, valem jne).

Lihtne näideKui soovite teada, kas hinne on läbitav (eeldades, et läbimise hinne on 60) ja teil on B-veerus hinded:

=IF(B2>=60; "Läbitud"; "Nurjus")

Pea meeles et saate kasutada nii numbreid kui ka teksti ning et Excel peaks neid teksti jutumärkide abil eristama.

Operaatorid ja tingimused IF-funktsioonis

Aastal loogiline tõestus IF-funktsiooniga saate väärtuste või lahtriviidete võrdlemiseks kasutada mitmesuguseid operaatoreid:

• = (võrdne)
• <> (välja arvatud)
• < (vähem kui)
• > (suurem kui)
• <= (väiksem või võrdne)
• >= (suurem või võrdne)

See võimaldab IF-funktsiooni kasutada äärmiselt mitmekülgne ja kohandub igat tüüpi vajadustega.

IF-funktsiooni praktilised näited Excelis

1. Tulemuste klassifitseerimine

Oletame, et teil on õpilaste nimekiri koos nende hinnetega ja soovite iga tulemuse kategoriseerida järgmiselt: "Kinnitatud" o "Ebaõnnestus":

=IF(B2>=60; "Läbitud"; "Nurjus")

2. Tekstipõhine hindamine

Samuti on lahtrites oleva teksti kontrollimiseks võimalik kasutada funktsiooni IF. Kujutage ette, et soovite kontrollida, kas töötaja on esitanud aruande („Jah” või „Ei”):

=IF(C2="Jah"; "Kohale toimetatud"; "Ootel")

3. Tühjade lahtrite kontrollimine

Kui teil on vaja teada, kas lahter on tühi või mitte, võite kasutada järgmist:

=IF(ISBLANK(D2); "Vajab lõpetamist"; "Valmis")

4. Matemaatilised tehted vastavalt tingimusele

IF-funktsioon ei tagasta ainult teksti. Samuti saate arvutusi teha tingimuse täitmise põhjal. Näiteks kahe väärtuse vahe tagastamine ainult siis, kui eelarve on ületatud:

=SI(E2>F2; E2-F2; 0)

5. Kuva tühjad lahtrid, kui tingimus pole täidetud

Kui eelistate, et tingimuse väära korral kuvatakse tühi lahter:

=IF(G2>100; "Läbitud"; "")

Soovitused IF-funktsiooniga töötamiseks

IF-funktsiooni kasutamisel peaksite arvestama mõne olulise näpunäitega Vigade vältimiseks ja valemite selguse parandamiseks toimige järgmiselt.

• Kontrollige süntaksit hoolikalt vigade vältimiseks #JULGUS! põhjustatud sulgudest või ebaõigetest viidetest.
• Kasutage tekstides jutumärke ja mitte numbrite ega lahtriviidete jaoks.
• Kontrolli, et loogikatest tagastaks alati väärtuse TRUE või FALSE.
• Veenduge, et argumendid on õiged ja vältige tühjade argumentide jätmist, kui on vaja tagastada konkreetseid andmeid.
• Tuginege veakontrolli funktsioonidele näiteks IFERROR või ISERROR, et hallata valemites esinevaid võimalikke probleeme.

Pesastatud IF-funktsioon: mitme tingimuse hindamine

Üks IF-funktsiooni suuri tugevusi on selle võimalus pesastada mitu funktsiooni endasse. See on eriti kasulik, kui peate järjest hindama mitut tingimust:

=IF(B2>=90; "Suurepärane"; KUI(B2>=60; "Pärast"; "Keelatud"))

Selles näites kontrollib Excel kõigepealt, kas hinne on võrdne või suurem kui 90 („Suurepärane“). Kui mitte, siis kontrollige, kas see on vähemalt 60 ("Passed"). Kui ka see kriteerium ei ole täidetud, tagastatakse "Failed".

Kuni 64 IF-funktsiooni saab pesastada ühes valemis, kuigi praktikas pole nii paljude käsitsemine loetavuse ja hooldatavuse tõttu tavaliselt soovitatav.

Täpsemate võrdluste tegemiseks kombineeri IF-funktsiooni loogikafunktsioonidega AND, OR ja NOT

Kui teil on vaja loogikatestis kontrollida mitut tingimust samaaegselt – st kui soovite, et toiming täidetaks ainult siis, kui kõik või mõned nõuded on täidetud – Saate IF-funktsiooni kombineerida loogikafunktsioonidega AND, OR ja NOT.

• YTagastab väärtuse TRUE ainult siis, kui kõik selles sisalduvad tingimused on tõesed.
• OTagastab väärtuse TRUE, kui cualquiera tingimustest on tõene.
• EI: Pöörab loogilise tingimuse tulemuse ümber, tagastades TÕENE väärtuse, kui tingimus on väär, ja vastupidi.

Näide Y-ga:

=IF(JA(A2>50;B2<100); "Õige"; "Vale")

Näide O-ga:

=IF(VÕI(C2="Jah";D2="Ootel");"Toiming vajalik";"Toimingut pole")

Näide NO-ga:

=IF(NOT(E2>100); "Piirete piires"; "Üle")

Need kombinatsioonid võimaldavad teil luua keerukaid tingimusanalüüse, väljendades reegleid, mis jäljendavad teie äriloogika keerukust ühes valemis.

IF.SET: IF-funktsiooni evolutsioon

Exceli uuemates versioonides (Office 2019, Office 2021 ja Microsoft 365) IF-funktsioon hõlbustab mitme tingimuse samaaegset hindamist. ilma et oleks vaja pesastada kümneid IF-funktsioone. Selle süntaks on:

=IF(tingimus1; väärtus1; tingimus2; väärtus2; ...)

Selle abil defineerite tingimuste ja tulemuste paare: Excel hindab iga tingimust järjekorras ja kui leiab esimese tõese tingimuse, tagastab vastava väärtuse.

Peamine eelis: lihtsustab valemeid, mis muidu oleksid keerulised ja raskesti hallatavad.

IF-funktsiooni kombineerimine teiste Exceli funktsioonidega

Lisaks loogilistele funktsioonidele JAH, saab kombineerida teiste funktsioonidega veelgi võimsamate valemite loomiseks:

• KOKKU: Tingimusest sõltuvate tekstide ühendamiseks (näiteks aktsia tõusu või languse protsendi näitamiseks).
• OTSING, INDEKS: Tingimuse tulemuste viitamiseks teistele tabelitele või arvutustabeli väärtustele.

Võimalused on sama laiad kui teie kujutlusvõime ja analüüsi vajadused.

Muud tingimusfunktsioonid: COUNTIF, SUMIF ja AVERAGEIF

Lisaks traditsioonilisele IF-funktsioonile Excel sisaldab spetsiaalseid tingimusfunktsioone andmete loendamiseks, summeerimiseks või keskmistamiseks kriteeriumide alusel. Need on väga praktilised tööriistad suurte andmemahtude analüüsimiseks ilma abiveergude loomise vajaduseta.

COUNT IF

Seda kasutatakse selleks, et loendada, mitu rakku vastab kindlale kriteeriumile.. Selle süntaks on:

=COUNTIF(vahemik; kriteeriumid)

Näide: Loendage, mitu korda perekonnanimi "Martínez" loendis esineb:

=COUNTIF(A2:A7; "Martinez")

LISA KUI

Summeerib kriteeriumile vastavate lahtrite väärtused. Süntaks on:

=SUMIF(vahemik; kriteeriumid; )

Näide: Lisage ainult "Martínezi" müügid:

=SUM.IF(A2:A7; "Martínez"; B2:B7)

KESKMINE.IF

Arvutab tingimusele vastavate väärtuste keskmise. Selle süntaks on:

=KESKMINE.KUI(vahemik; kriteerium; )

Näide: Leidke töötajate keskmine palk, kelle perekonnanimi on "Perez":

=KESKMINEIF(A2:A7; "Pérez"; B2:B7)

Tingimusvormingud Excelis ja nende seos IF-funktsioonidega

El Tingimusvormingud võimaldavad teil lahtreid kriteeriumide alusel automaatselt esile tõsta mida saab defineerida valemite abil, sh need, mis kasutavad funktsiooni IF, aga ka funktsioone AND, OR ja NOT. See tõstab olulised andmed ühe pilguga esile, hõlbustades visuaalset analüüsi. Selle kasutamise kohta lisateabe saamiseks võite konsulteerida ka .

Näiteks kõigi teatud piirist kõrgemate lahtrite roheliseks värvimiseks:

• Valige lahtrite vahemik.
• Klõpsake valikutel „Tingimuslik vormindamine” > „Uus reegel” > „Kasuta valemit…”.
• Sisesta valem (näiteks =A2>100).
• Valige esiletõstmise vorming ja kinnitage.

See süsteem on eriti kasulik aruannetes, armatuurlaudadel või andmekvaliteedi kontrollimisel.

Olulised näpunäited tingimusfunktsioonide maksimaalseks ärakasutamiseks

• Harjuta päris näidetegaTöötage isiklike või tööandmetega, et mõista loogikat ja kohandada seda oma igapäevaste vajadustega.
• Kasutage suhtelisi ja absoluutseid viiteid (kasutades $). See on oluline valemite kopeerimisel erinevatesse asukohtadesse või suurte andmemahtudega töötamisel.
• Kombineeri valemeid keeruliste probleemide lahendamiseks: Integreeri IF funktsiooni AND, OR, NOT ja teiste funktsioonidega, et luua keerukamaid analüüse.
• Korraldage ja dokumenteerige oma valemeidKasutage vahemiku nimesid ja kommentaare, et muuta arvutustabeleid lihtsamini hallatavaks ja arusaadavaks.
• Testige oma valemeid väikese andmekogumi peal enne nende rakendamist kogu komplektile, minimeerides seeläbi vigu ja üllatusi.
• Kasutage funktsioonide täiustatud versiooneVajadusel kasutage mitme tingimuse analüüsi käsitlemiseks funktsioone IFSET, COUNTIFS, SUMIFS või AVERAGEIFS.

Levinud vead ja nende lahendamise viisid

Kuigi tingimusfunktsioonid on kontseptuaalselt lihtsad, võivad tekkida tavalised vead. Kõige tüüpilisemate hulka kuuluvad:

• Ootamatud nullid: Esineb siis, kui atribuudile „value_if_true” või „value_if_false” pole väärtust määratletud.
• #NAME?Levinud viga, kui valem on valesti kirjutatud või funktsioon on mõnes muus keeles.
• #VÄÄRTUS!: Tavaliselt ilmub see süntaksivigade, valede argumentide või vigaste lahtrite viidete tõttu.
• # DIV / 0!: Ilmub siis, kui ühe hinnatud tingimuse korral püütakse jagada nulliga.

Nende vigade vältimiseks ja parandamiseks:

• Kontrolli alati oma valemite süntaksit.
• Kasutage IFERROR ja ISERROR funktsioone soovimatute tulemuste kontrollimiseks ja sõbralikumate väärtuste tagastamiseks.
• Kontrollige lahtriviiteid veendumaks, et need on õigesti kirjutatud ja ei viita olematutele vahemikele.

Korduma kippuvad küsimused tingimusliku analüüsi kohta Excelis

Kas IF-funktsioonis saab kasutada mitut tingimust?

Jahsaate pesastada mitu IF-funktsiooni või kombineerida IF-funktsioonidega AND, OR ja NOT, et hinnata samas valemis rohkem kui ühte kriteeriumi.

Mitu pesastatud IF-i taset on lubatud?

Exceli tänapäevastes versioonides saab pesastada kuni 64 IF-funktsiooni, kuigi soovitatav on neid võimaluse korral lihtsustada, kasutades funktsiooni IFSET või viitetabeleid.

Mis saab siis, kui tahan liita või lugeda rohkem kui ühe kriteeriumi järgi?

Sel eesmärgil on olemas sellised funktsioonid nagu LOENDA, KUI SET, SUMMA.IF.SET y KESKMINE, KUI LIIGNE, mis võimaldavad teil korraga mitut tingimust selgemal ja tõhusamal viisil hallata.

Kas ma saan IF-funktsiooni abil lahtri tühjaks jätta, kui tingimus pole täidetud?

Jah, sa pead lihtsalt argumendi "value_if_false" tühjaks jätma või kirjutama sinna funktsiooni sees "" (tühjad jutumärgid).

Lõplikud näpunäited edasijõudnutele kasutajatele

Spetsialistidele, kes soovivad Exceli järgmisele tasemele viia, on siin mõned edasijõudnutele mõeldud soovitused:

• Kasutage maatriksvalemeid et hinnata samaaegselt kõiki tingimuste loendeid.
• Loo korduvkasutatavaid malle tingimusfunktsioonide kombineerimine pöördtabelite, diagrammide ja täiustatud tingimusliku vormindamisega.
• Automatiseerige protsesse suuremahulistes arvutustabelites absoluutsete viidete kasutamine ja kombineerimine tingimusfunktsioonid makrodega või VBA, kui on vaja täielikku kohandamist.

Exceli tingimusfunktsioonid, eriti IF-funktsioon ja selle variandid, moodustavad arvutustabelite loogilise analüüsi selgroo. Olenemata sellest, kas sorteerite, filtreerite, summeerite, loendate või lihtsalt tõstate esile olulisi andmeid, võimaldab nende tööriistade põhjalik tundmine teil töötada tõhusamalt, teha paremaid otsuseid ja säästa aega igat tüüpi projektide puhul. Jaga juhendit ja rohkem kasutajaid õpib seda kasutama