IF Formula Excel - izjave Če potem

Prenesite primer delovnega zvezka

Prenesite primer delovnega zvezka

Ta vadnica prikazuje, kako uporabljati Excel IF funkcija v Excelu za ustvarjanje izjav If then.

Pregled funkcije IF

Funkcija IF preveri, ali je pogoj izpolnjen. Če TRUE naredi eno, če FALSE naredi drugo.

Če želite uporabiti funkcijo delovnega lista Excel Excel, izberite celico in vnesite:

(Upoštevajte, kako so prikazani vnosi formule)

Funkcija IF Sintaksa in vhodi:

1	= IF (logični_test, vrednost_da_istina, vrednost_ko_napaka)

logični_test - Logični izraz. Primer: A1> 4.

value_if_true - Vrednost ali izračun, če je logični izraz TRUE.

value_if_false - Vrednost ali izračun, če je logični izraz FALSE.

IF je "pogojna" funkcija. To pomeni, da določite logični preskus in bo vrnil eno vrednost, če je ta ocenjena kot resnična, in drugo vrednost, če je napačna

Kako uporabljati funkcijo IF

Tu je zelo preprost primer, da vidite, kaj mislim. Poskusite v Excel vnesti naslednje:

1	= IF (2 + 2 = 4, "Res je", "Napačno!")

Ker je 2 + 2 dejansko enako 4, bo Excel vrnil »Res je!«. Če smo uporabili tole:

1	= IF (2 + 2 = 5, "Res je", "Napačno!")

Zdaj bo Excel vrnil »To je napačno!«, Ker 2 + 2 ni enako 5.

Evo, kako lahko uporabite IF v preglednici.

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

Vodite športno palico in določite posamezne omejitve zavihkov za različne stranke. To preglednico ste nastavili za preverjanje, ali vsaka stranka presega svojo mejo, v tem primeru jo boste odrezali, dokler ne plača zavihka.

Preverite, ali je C4-D4 (njihov trenutni znesek zavihka minus njihova meja) večji od 0. To je vaš logični test. Če je to res, IF vrne "Da" - jih morate odrezati. Če je to napačno, IF vrne "Ne" - jim dovolite, da nadaljujejo s pitjem.

Kaj lahko IF vrne

Zgoraj smo vrnili besedilni niz "Da" ali "Ne". Lahko pa vrnete tudi številke ali celo druge formule.

Recimo, da nekatere vaše stranke izvajajo velike zavihke. Da bi to odvrnili, boste začeli zaračunavati obresti strankam, ki presežejo svojo mejo.

Za to lahko uporabite IF:

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

Če je zavihek višji od omejitve, vrnite zavihek pomnožen z 0,03, kar vrne 3% zavihka. V nasprotnem primeru vrnite 0: niso na zavihku, zato obresti ne boste zaračunali.

Uporaba IF z AND

IF lahko kombinirate z Excelovo funkcijo AND <>. To uporabite pri logičnem testu, ki vam omogoča, da določite dva ali več pogojev za testiranje. Excel bo vrnil TRUE le, če so vsi testi resnični.

Torej ste uvedli svojo obrestno mero. Toda nekateri vaši redni obiskovalci se pritožujejo. V preteklosti so vedno plačevali svoje zavihke, zakaj jih zdaj zatirate? Prišli ste do rešitve: nekaterim zaupanja vrednim strankam ne boste zaračunali obresti.

V preglednici naredite nov stolpec za prepoznavanje zaupanja vrednih strank in posodobite svoj stavek IF s funkcijo AND:

1	= IF (AND (C4> D4, F4 = "ne"), C4*0,03,0)

Poglejmo ločeno del AND:

1	IN (C4> D4, F4 = "Ne")

Upoštevajte dva pogoja:

• C4> D4: preverjanje, ali presegajo omejitev zavihkov, kot prej
• F4 = "Ne": to je nov bit, ki preverja, ali niso zaupanja vredna stranka

Tako zdaj obrestno mero vračamo le, če je stranka čez zavihek, IN imamo v stolpcu zaupanja vrednih strank »Ne«. Vaši redni ljudje so spet srečni.

Več o funkciji Excel IN <> preberite na glavni strani.

Uporaba IF z OR

ALI je še ena izmed Excelovih logičnih funkcij. Tako kot AND vam omogoča, da definirate več pogojev. Toda za razliko od AND se bo vrnilo TRUE, če je kateri koli test, ki ga definirate, resničen.

Morda stranke, ki so na svojem zavihku, niso edini razlog, da bi jih odrezali. Morda boste nekaterim ljudem začasno prepovedali iz drugih razlogov, morda igralništvo v prostorih.

Zato dodate nov stolpec za identifikacijo prepovedanih strank in posodobite »Odsekano?« stolpec s preskusom OR:

1	= IF (OR (C4> D4, E4 = "Da"), "Da", "Ne")

Če pogledamo samo del OR:

1	ALI (C4> D4, E4 = "Da")

Obstajata dva pogoja:

• C4> D4: preverjanje, če presegajo omejitev zavihkov
• F4 = "Da": novi del, ki preverja, ali so trenutno prepovedani

To bo ocenilo resnico, če so čez zavihek ali če je v stolpcu E. "Da". Kot lahko vidite, je Harry zdaj odrezan, čeprav ne presega omejitve zavihkov.

Več o funkciji Excel ALI preberite na glavni strani <>.

Uporaba IF z XOR

XOR je še ena logična funkcija, ki vrne »izključno ali«. To je nekoliko manj intuitivno od prejšnjih, o katerih smo razpravljali.

V preprostih primerih določite dva pogoja in XOR se vrne:

• TRUE, če je kateri od argumentov res (enako kot običajni OR)
• FALSE, če sta oba argumenta res
• FALSE, če sta oba argumenta napačna

Primer bi lahko bil bolj jasen. Predstavljajte si, da želite svojemu osebju začeti dajati mesečne bonuse:

• Če prodajo več kot 800 USD v hrani ali več kot 800 USD v pijači, jim boste dali polovico bonusa
• Če v obeh prodajo več kot 800 USD, jim boste dali polni bonus
• Če v obeh prodajo pod 800 USD, ne dobijo nobenega bonusa.

Že veste, kako se lotiti, če dobijo polni bonus. Uporabili bi samo IF z AND, kot je opisano prej.

1	= IF (AND (C4> 800, D4> 800), "da", "ne")

Kako pa bi ugotovili, kdo dobi polovični bonus? Tu pride XOR:

1	= IF (XOR (C4> = 800, D4> = 800), "Da", "Ne")

Kot lahko vidite, je bila prodaja pijače Woody's več kot 800 dolarjev, ne pa tudi hrane. Tako dobi polovični bonus. Za trenerja velja obratno. Diane in Carla sta za oba prodala več kot 800 USD, tako da ne dobita polovičnega bonusa (oba argumenta sta TRUE), Rebecca pa je za oba dosegla prag (oba argumenta FALSE), zato formula spet vrne "Ne".

Več o funkciji Excel XOR preberite na glavni strani <>.

Uporaba IF z NOT

NOT je še ena izmed Excelovih logičnih funkcij, ki se zelo pogosto uporablja z IF.

NE obrne izida logičnega testa. Z drugimi besedami, preverja, ali pogoj ni izpolnjen.

Uporabite ga lahko z IF na naslednji način:

1	= IF (AND (C3> = 1985, NOT (D3 = "Steven Spielberg")), "Watch", "Don't Watch")

Tukaj imamo tabelo s podatki o nekaterih filmih iz osemdesetih let. Želimo identificirati filme, ki so bili izdani leta 1985 ali pozneje, ki jih ni režiral Steven Spielberg.

Ker NOT ni ugnezden v funkciji AND, bo Excel to najprej ocenil. Nato bo rezultat uporabil kot del AND.

Preberite več na glavni strani funkcije Excel NOT <>.

Ugnezdeni stavki IF

Izjavo IF lahko vrnete tudi znotraj izjave IF. To vam omogoča, da naredite bolj zapletene izračune.

Vrnimo se k mizi naših strank. Predstavljajte si, da želite stranke razvrstiti glede na raven dolga do vas:

• $ 0: Brez
• Do 500 USD: Nizko
• 500 do 1000 USD: Srednje
• Več kot 1000 USD: Visoko

To lahko storite tako, da "ugnezdite" stavke IF:

1	= IF (C4 = 0, "Brez", IF (C4 <= 500, "Nizko", IF (C4 <= 1000, "Srednje", IF (C4> 1000, "Visoko")))

Lažje je razumeti, če stavke IF postavite v ločene vrstice (ALT + ENTER v sistemu Windows, CTRL + COMMAND + ENTER v računalnikih Mac):

12345

=IF (C4 = 0, "Brez",IF (C4 <= 500, "Nizko",IF (C4 <= 1000, "Srednje",IF (C4> 1000, "Visoko", "Neznano"))))

ČE je C4 0, vrnemo »Brez«. V nasprotnem primeru se premaknemo na naslednji stavek IF. ČE je C4 enak ali manjši od 500, vrnemo »Nizko«. V nasprotnem primeru preidemo na naslednjo izjavo IF … itd.

Poenostavitev kompleksnih izjav IF s pomožnimi stolpci

Če imate več ugnezdenih stavkov IF in vnašate tudi logične funkcije, lahko postanejo vaše formule zelo težke za branje, testiranje in posodabljanje.

To je še posebej pomembno upoštevati, če bodo drugi ljudje uporabljali preglednico. Kar je v vaši glavi smiselno, drugim morda ni tako očitno.

Pomočni stolpci so odličen način za reševanje tega vprašanja.

Ste analitik v finančnem oddelku velike korporacije. Od vas se je zahtevalo, da ustvarite preglednico, ki preveri, ali je vsak zaposleni upravičen do pokojnine podjetja.

Tu so merila:

Torej, če ste mlajši od 55 let, morate imeti 30 let delovne dobe, da ste upravičeni. Če ste stari od 55 do 59 let, potrebujete 15 let delovne dobe. In tako naprej, do 65. leta, ko ste upravičeni ne glede na to, koliko časa ste tam delali.

Za rešitev tega problema bi lahko uporabili eno samo zapleteno izjavo IF:

1	= IF (OR (F4> = 65, AND (F4> = 62, G4> = 5), AND (F4> = 60, G4> = 10), AND (F4> = 55, G4> = 15), G4 > 30), "Primerno", "Ni primerno")

Vau! Težko se je iti na glavo, kajne?

Boljši pristop je lahko uporaba pomožnih stolpcev. Tukaj imamo pet logičnih testov, ki ustrezajo vsaki vrstici v tabeli meril. To je lažje videti, če formuli dodamo prelome vrstic, kot smo že razpravljali:

12345678

= IF (ALI (F4> = 65,AND (F4> = 62, G4> = 5),AND (F4> = 60, G4> = 10),AND (F4> = 55, G4> = 15),G4> 30), "Primerno", "Ni primerno")

Tako lahko teh pet testov razdelimo v ločene stolpce in nato preprosto preverimo, ali kateri od njih drži:

Vsak stolpec v tabeli od E do I vsebuje vsako naše merilo posebej. Potem imamo v J4 naslednjo formulo:

1	= ČE (COUNTIF (E4: I4, TRUE), "Primerno", "Ni primerno")

Tukaj imamo stavek IF in logični test uporablja COUNTIF <> za štetje števila celic znotraj E4: I4, ki vsebujejo TRUE.

Če COUNTIF ne najde vrednosti TRUE, bo vrnil 0, kar IF interpretira kot FALSE, zato IF vrne „Not Eligible“.

Če COUNTIF najde vrednosti TRUE, bo vrnil njihovo število. IF interpretira katero koli število razen 0 kot TRUE, zato vrne »Primerno«.

Ločitev logičnih testov na ta način olajša branje formule, in če je z njo kaj narobe, je veliko lažje opaziti, kje je napaka.

Uporaba razvrščanja v skupine za skrivanje stolpcev pomočnikov

Pomožni stolpci olajšajo upravljanje formule, a ko jih namestite in veste, da delujejo pravilno, pogosto zavzamejo prostor v preglednici, ne da bi dodali uporabne informacije.

Lahko bi skrili stolpce, vendar lahko to povzroči težave, ker skrite stolpce težko zaznate, razen če natančno pogledate glave stolpcev.

Boljša možnost je združevanje v skupine.

Izberite stolpce, ki jih želite združiti, v našem primeru E: I. Nato pritisnite ALT + SHIFT + PUŠČICA DESNO v sistemu Windows ali COMMAND + SHIFT + K v sistemu Mac. Prav tako lahko odprete zavihek »Podatki« na traku in v razdelku »Oris« izberete »Skupina«.

Skupina bo prikazana nad glavo stolpcev, na primer:

Nato preprosto pritisnite gumb »-«, da skrijete stolpce:

Funkcija IFS

Ugnezdeni stavki IF so zelo uporabni, ko morate izvesti bolj zapletene logične primerjave in morate to narediti v eni celici. Ko pa se podaljšajo, se lahko zapletejo, zato jih je težko brati in posodabljati na zaslonu.

Microsoft je iz Excelov 2022 in Excel 365 predstavil še eno funkcijo, IFS, ki je olajšala upravljanje tega. Zgornji ugnezdeni primer IF bi lahko dosegli z IFS, kot je ta:

1234567

= IFS (C4 = 0, "Brez",C4 <= 500, "Nizko",C4 <= 1000, "srednji",C4> 1000, "visoko",TRUE, "neznano",)

Vse o tem lahko preberete na glavni strani funkcije Excel IFS <>.

Uporaba IF s pogojnim oblikovanjem

Excelova funkcija pogojnega oblikovanja omogoča oblikovanje celice na različne načine, odvisno od njene vsebine. Ker IF vrne različne vrednosti na podlagi našega logičnega testa, bomo morda želeli uporabiti pogojno oblikovanje, da bomo te različne vrednosti lažje videli.

Vrnimo se torej k naši tabeli bonusov za zaposlene od prej.

Vrnemo "Da" ali "Ne", odvisno od tega, kakšen bonus želimo podariti. To nam pove, kaj moramo vedeti, vendar nam informacije ne skočijo. Poskusimo to popraviti.

Takole bi to storili:

• Izberite obseg celic, ki vsebuje vaše stavke IF. V našem primeru je to E4: F8.
• Kliknite »Pogojno oblikovanje« v razdelku »Slogi« na zavihku »Domov« na traku.
• Kliknite »Označi pravila o celicah« in nato »Enako«.
• V prvo polje vnesite »Da« (ali katero koli vrnjeno vrednost, ki jo potrebujete), nato pa v drugem polju izberite želeno oblikovanje. (Za to bom izbral zeleno).
• Ponovite za vse vaše vrnjene vrednosti (vrednosti "Ne" bom nastavil tudi na rdečo)

Tukaj je rezultat:

Uporaba IF v matričnih formulah

Niz je obseg vrednosti in v Excelu so matrike predstavljene kot vrednosti, ločene z vejicami, zaprte v oklepajih, na primer:

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

Lepota nizov je, da vam omogočajo izračun za vsako vrednost v obsegu in nato vrnejo rezultat. Na primer, funkcija SUMPRODUCT vzame dva niza, jih pomnoži skupaj in povzame rezultate.

Torej ta formula:

1	= PODROČJE ({1,2,3}, {4,5,6})

… Vrača 32. Zakaj? Delajmo skozi:

12345

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

Na to sliko lahko vnesemo stavek IF, tako da se vsako od teh množenja zgodi le, če logični test vrne res.

Vzemite na primer te podatke:

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

Opomba: V Excelu 2022 in starejših morate pritisniti CTRL + SHIFT + ENTER, da to spremenite v formulo matrike.

Na koncu bi dobili nekaj takega:

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

Če je ime v stolpcu C enako tistemu v G2 ("Olivia"), DO pomnožite vrednosti v stolpcih D in E za to vrstico. V nasprotnem primeru jih ne množite. Nato povzemite vse rezultate.

Več o tej formuli lahko izveste na glavni strani formule SUMPRODUCT IF <>.

ČE v Google Preglednicah

Funkcija IF v Google Preglednicah deluje popolnoma enako kot v Excelu:

Dodatne opombe

S funkcijo IF preverite, ali je pogoj TRUE. Če je pogoj TRUE, naredite eno stvar. Če je napačno, naredite drugo. Pogoj mora biti logični izraz (npr. A1> 5), sklic na celico, ki vsebuje TRUE ali FALSE, ali matrika, ki vsebuje vse logične vrednosti.

Funkcija IF lahko preizkusi samo en pogoj hkrati. Vendar lahko v pogoju IF "ugnezdite" druge logične funkcije, da preizkusite več pogojev hkrati:

= če (AND (a1> 0, a2> 0), TRUE, FALSE)
= če (ALI (a1> 0, a2> 0), TRUE, FALSE)
= če (XOR (a1> 0, a2> 0), TRUE, FALSE)
ALI Preverite delovanje funkcij, če enega ali več pogoji so izpolnjeni.
IN Funkcije preskusite, če vse pogoji so izpolnjeni.
Preskus funkcij XOR, če en in edini pogoj je izpolnjen.

Funkcijo IF lahko tudi "ugnezdite" v funkcijo IF:

1	= if (a1 <0, if (a2 <0, "Oba", "samo 1"), "samo ena")

Zdaj pa nekaj konkretnih primerov, kako funkcija IF deluje v praksi:

1. Začnite novo delovno knjigo.

2. V celico A1 vnesite vrednost 10 (in pritisnite Enter)

3. Nato v celico B1 vnesite naslednjo formulo:

1	= ČE (A1> 5, "VEČJE OD 5", "MANJE OD 5")

4. Zaslon bi moral izgledati takole:

5. Če ste formulo vnesli pravilno, se bo v celici B1 pojavilo sporočilo "Večje od 5".

6. Formula, ki ste jo vnesli v celico B1, izvaja preskus "A1> 5", tj. Preverja, ali je vrednost v celici A1 večja od 5. Trenutno je vrednost v celici A1 10 - torej je pogoj TRUE in sporočilo Prikaže se »VEČJE OD 5«

7. Če zdaj vrednost v celici A1 spremenimo v 2:

Sporočilo v celici B2 je zdaj "LESS THAN 5", saj je pogoj FALSE.

8. V celici A1 lahko še naprej spreminjate vrednost in sporočilo v celici B2 se bo temu ustrezno prilagodilo.

9. Seveda obstajajo situacije, ko bi lahko stanje povzročilo hude posledice:

• Kaj se zgodi, če v celico A1 vnesemo vrednost 5?

• Kaj pa, če pustimo celico A1 prazno?

• Kaj pa, če v celico A1 vstavimo nekaj besedila, na primer besedno zvezo DOG

Več o funkciji Excel IF

Zdaj bomo podrobneje pogledali funkcijo IF. Z njim je mogoče zelo enostavno analizirati velike količine podatkov.

Predstavljajte si, da ste vodja prodaje na območju in imate prodajno skupino. Skupno prodajo, ki jo opravi vsaka oseba, lahko zabeležite v preprosto Excelovo preglednico:

Recimo, da je merilo za bonus to, da je prodaja te osebe presegla 40.000 funtov. Podatke bi lahko le "prestregli" in ugotovili, da so cilj dosegli le Anton, Newton in Monique.

To je precej enostavno, če imaš le nekaj imen. Če pa jih imate več, obstaja možnost za napako. Na srečo z uporabo Excelove funkcije IF je to mogoče narediti veliko hitreje in varneje.

Nastavite nov delovni zvezek in vnesite podatke, kot je opisano zgoraj. Nato v celico D4 vnesite naslednjo formulo:-

1	= ČE (C4> 40000, "BONUS PLAČLJIV", "NI BONUSA")

tako da imaš:

Upoštevajte, kako Excel prikazuje strukturo formule IF - ki je koristen spomin.

Ko vnesete formulo, pritisnite ENTER in videli boste, da se oceni za prvo vrstico:

Formula je bila ocenjena za Martina - saj je zaslužil manj kot 40.000 funtov, ni upravičen do nobenega bonusa.

Nato povlečemo formule navzdol tako, da kliknemo spodnji desni kot in povlečemo navzdol, da ugotovimo, ali je vsaka oseba upravičena do bonusa:

Vidimo, da je Excel določil, kdo od prodajalcev je upravičen do bonusa.

Vrnite se na seznam vseh funkcij v Excelu

Izjave VBA IF

Uporabite lahko tudi izjave If v VBA. Če želite izvedeti več, kliknite povezavo, vendar je tukaj preprost primer:

1234567

Sub Test_IF ()Če je obseg ("a1"). Vrednost <0 potemRazpon ("b1"). Vrednost = "Negativno"Konec ČeKonec Če

Ta koda bo preverila, ali je vrednost celice negativna. Če je tako, bo v naslednjo celico zapisano »negativno«.