Preskoči na glavno vsebino

Kako uporabiti funkcijo IF z AND, OR in NOT v Excelu?

Excelova funkcija IF je dokaz moči in vsestranskosti logičnih operacij pri obdelavi podatkov. Bistvo funkcije IF je njena zmožnost ovrednotenja pogojev in vrnitve specifičnih rezultatov na podlagi teh ocen. Deluje po osnovni logiki:

=IF(condition, value_if_true, value_if_false)

V kombinaciji z logičnimi operatorji, kot so IN, ALI in NE, se zmogljivosti funkcije IF znatno razširijo. Moč kombinacije je v njihovi zmožnosti obdelave več pogojev hkrati, kar zagotavlja rezultate, ki se lahko prilagodijo različnim in zapletenim scenarijem. V tej vadnici bomo raziskali, kako učinkovito izkoristiti te zmogljive funkcije v Excelu, da odklenete nove razsežnosti analize podatkov in izboljšate svoj postopek odločanja. Poglobimo se in odkrijmo praktične uporabe teh izjemnih Excelovih funkcij!

Ugnezdena formula ČE IN ALI NE

ČE IN formula

Za oceno več pogojev in doseganje določenega rezultata ko so izpolnjeni vsi pogoji (TRUE)in drugačen rezultat, ko kateri koli pogoj ni izpolnjen (FALSE), lahko funkcijo IN vključite v logični preizkus stavka IF. Struktura za to je:

=IF(AND(condition1, condition2, …), value_if_all_true, value_if_any_false)

Na primer, predstavljajte si, da ste učitelj, ki analizira ocene učencev. Želite ugotoviti, ali je študent uspešen na podlagi dveh meril: rezultat nad 70 IN udeležba nad 80%.

  1. Začnite s pregledom podatkov prvega študenta, z njegovim rezultatom v celici B2 in prisotnostjo v celici C2. Za tega študenta uporabite spodnjo formulo v D2: 
    =IF(AND(B2>70, C2>80%), "Pass", "Fail")
    Nasvet: Ta formula preveri, ali je rezultat v B2 nad 70 in ali je prisotnost v C2 več kot 80 %. Če sta izpolnjena oba pogoja, vrne "Pass"; sicer vrne "Neuspeh".
  2. Povlecite formulo navzdol skozi stolpec, da ocenite rezultat in prisotnost vsakega študenta.

    ČE IN formula

Formula ČE ALI

Za ovrednotenje več pogojev in vrnitev določenega rezultata ko je katerikoli od pogojev izpolnjen (TRUE)in drugačen rezultat, ko nobeden od pogojev ni izpolnjen (FALSE), je mogoče uporabiti funkcijo ALI znotraj logičnega preizkusa stavka IF. Formula je strukturirana na naslednji način:

=IF(OR(condition1, condition2, …), value_if_any_true, value_if_all_false)

Na primer, v izobraževalnem kontekstu razmislite o bolj prilagodljivem merilu za uspešnost študentov. Tukaj se šteje, da je študent opravil, če je bodisi rezultat nad 90 OR imeti stopnjo prisotnosti nad 95 %.

  1. Začnite z ocenjevanjem uspešnosti prvega študenta z njegovim rezultatom v celici B2 in prisotnostjo v celici C2. Uporabite formulo v sosednji celici, kot je D2, da ocenite: 
    =IF(OR(B2>90, C2>95%), "Pass", "Fail")
    Nasvet: Ta formula ocenjuje, ali študent doseže več kot 90 točk v B2 ali ima stopnjo prisotnosti nad 95 % v C2. Če je kateri koli pogoj izpolnjen, vrne "Pass"; če ne, "Neuspeh".
  2. Kopirajte to formulo navzdol po stolpcu, da jo uporabite za vsakega študenta na vašem seznamu in tako omogočite hitro oceno primernosti vsakega študenta za opravljanje na podlagi teh meril.

    formula ČE ALI

ČE NI Formula

Za ovrednotenje stanja in vrnitev določenega rezultata če pogoj NI izpolnjen (FALSE), in drugačen rezultat, če je pogoj izpolnjen (TRUE), je funkcija NOT znotraj stavka IF vaša rešitev. Struktura te formule je:

=IF(NOT(condition), value_if_false, value_if_true)

Za praktičen primer razmislite o scenariju na delovnem mestu, kjer se bonusi zaposlenih določijo na podlagi njihove evidence prisotnosti. Zaposleni so upravičeni do dodatka, če NIso bili odsotni več kot 3 dni.

  1. Če želite to oceniti za prvega zaposlenega, katerega dnevi odsotnosti so v celici B2, uporabite formulo: 
    =IF(NOT(B2>3), "Eligible", "Not Eligible")
    Nasvet: Ta formula preveri število dni odsotnosti v B2. Če NI več kot 3, vrne "Primerno"; sicer "Ni primerno".
  2. Kopirajte to formulo navzdol po stolpcu, da jo uporabite za vsakega zaposlenega.

    ČE NI formula

Napredni scenariji z IF in logičnimi funkcijami

V tem razdelku bomo raziskali zapleteno uporabo Excelove funkcije IF z logičnimi operatorji, kot so IN, ALI in NE. Ta razdelek pokriva vse od ocenjevanj, ki razlikujejo med velikimi in malimi črkami, do ugnezdenih stavkov IF, ki prikazujejo Excelovo vsestranskost pri kompleksni analizi podatkov.

Če je vaš pogoj izpolnjen, potem izračunajte

Poleg zagotavljanja vnaprej določenih rezultatov lahko funkcija Excel IF v kombinaciji z logičnimi operatorji, kot so IN, ALI in NE, izvede različne izračune glede na to, ali so nastavljeni pogoji resnični ali napačni. Tukaj bomo kot primer uporabili kombinacijo ČE IN za predstavitev te funkcije.

Predstavljajte si, da vodite prodajno ekipo in želite izračunati bonuse. Vi se odločite, da zaposleni prejme 10% bonus na njihovo prodajo če oni presegajo 100 $ v prodaji IN so delali več kot 30 ur na teden.

  1. Za prvo oceno si oglejte Alicine podatke z njeno prodajo v celici B2 in opravljenimi urami v celici C2. Uporabite to formulo v D2: 
    =IF(AND(B2>100, C2>30), B2*0.1, 0)
    Nasvet: Ta formula izračuna 10-odstotni bonus na Alicino prodajo, če njena prodaja preseže 100 USD in ima več kot 30 delovnih ur. Če sta izpolnjena oba pogoja, izračuna bonus; sicer vrne 0.
  2. Razširite to formulo na preostanek svoje ekipe, tako da jo kopirate v stolpec. Ta pristop zagotavlja, da se bonus vsakega zaposlenega izračuna na podlagi istih meril.

    ČE Nato izračunaj

Opomba: V tem razdelku se osredotočamo na uporabo funkcije IF z IN za izračune na podlagi posebnih pogojev. Ta koncept je mogoče razširiti tudi tako, da vključuje ALI in NE ter ugnezdene logične funkcije, kar omogoča različne pogojne izračune v Excelu.

Stavki IN, ALI in NE razlikujejo med velikimi in malimi črkami

Medtem ko v Excelu logične funkcije, kot so IN, ALI in NE, običajno ne razlikujejo med velikimi in malimi črkami, obstajajo scenariji, kjer je občutljivost med velikimi in malimi črkami v besedilnih podatkih ključna. Z integracijo TOČNO funkcija s temi logičnimi operatorji lahko učinkovito obravnavate takšne pogoje, ki razlikujejo med velikimi in malimi črkami. V tem razdelku prikazujemo uporabo funkcij IF in OR s pristopom, ki razlikuje med velikimi in malimi črkami, kot primer.

Predstavljajte si maloprodajni scenarij, kjer je izdelek primeren za promocijo, če tudi preseže 100 dolarjev prodaje OR njegova koda se natančno ujema z "ABC" pri preverjanju, ki razlikuje med velikimi in malimi črkami.

  1. Za prvi izdelek, naveden v vrstici 2, s prodajo v celici B2 in kodo izdelka v celici C2, uporabite to formulo v D2: 
    =IF(OR(B2>100, EXACT(C2,"ABC")), "Promotion Eligible", "Not Eligible")
    Nasvet: Ta formula oceni, ali prodajna številka v B2 presega 100 USD ali je koda izdelka v C2 natančno "ABC". Izpolnjevanje enega od teh pogojev pomeni, da je izdelek upravičen do promocije; če oboje ne uspe, je neupravičeno.
  2. Ponovite to formulo v stolpcu za vse izdelke, da enotno ocenite njihovo upravičenost do promocije na podlagi prodaje in meril kode izdelka, ki razlikujejo med velikimi in malimi črkami.

    Formula IF ALI, ki razlikuje med velikimi in malimi črkami

Opomba: V tem razdelku smo ponazorili uporabo funkcij IF in OR s funkcijo EXACT za ocene, ki razlikujejo med velikimi in malimi črkami. Podobno lahko uporabite funkcijo EXACT v svojih formulah IF v kombinaciji z IN, ALI, NE ali ugnezdenimi logičnimi funkcijami, da izpolnite različne zahteve glede velikih in malih črk v Excelu.

Integracija IF z ugnezdenimi stavki IN, ALI, NE

Excelova funkcija IF, ko je ugnezdena z IN, ALI in NE, ponuja poenostavljen pristop za obravnavo večplastnih pogojev. V tem razdelku je primer, ki prikazuje uporabo teh ugnezdenih funkcij v maloprodajnem okolju.

Recimo, da nadzirate ekipo, ki je odgovorna za različne kategorije izdelkov, in želite določiti njihovo upravičenost do bonusa. Zaposleni je upravičen do bonusa, če: doseči prodajo nad 100 USD, IN bodisi delati več kot 30 ur na teden OR NISO na oddelku za elektroniko.

  1. Najprej ocenite Annino uspešnost z njeno prodajo v celici B2, opravljenimi urami v celici C2 in oddelkom v celici D2. Formula v E2 bi bila: 
    =IF(AND(B2>100, OR(C2>30, NOT(D2="Electronics"))), "Eligible", "Not Eligible")
    Nasvet: Ta formula preveri, ali prodaja Anne presega 100 USD in ali dela več kot 30 ur ali ne sodeluje z Electronics. Če izpolnjuje ta merila, se šteje za "primerno"; če ne, "Ni primerno".
  2. Prepirajte to formulo navzdol po stolpcu za vsakega zaposlenega, da enotno ocenite upravičenost do bonusa glede na njihovo prodajo, opravljene ure in oddelek.

    Ugnezdena formula ČE IN ALI NE

Ugnezdene funkcije IF z IN, ALI, NE

Ko vaša analiza podatkov vključuje več pogojnih preverjanj, ponujajo ugnezdene funkcije IF v Excelu zmogljivo rešitev. Ta metoda vključuje izdelavo ločenih stavkov IF za različne pogoje, vključno z logiko IN, ALI in NE, in njihovo nato integracijo v eno poenostavljeno formulo.

Razmislite o delovnem mestu, kjer uspešnost zaposlenih je ocenjena kot "odlično", "dobro" ali "pošteno" temelji na prodaja, opravljene ure in upoštevanje politike:

  • "Odlično" za prodajo nad 150 USD IN več kot 35 ur dela.
  • V nasprotnem primeru "dobro" za prodajo nad 100 USD ALI kršitev pravilnika NE več kot 1.
  • "Pošteno", če nobeden od teh pogojev ni izpolnjen.

Če želite oceniti uspešnost vsakega zaposlenega glede na zgornje pogoje, naredite naslednje:

  1. Začnite z oceno Anne, katere prodaja je v celici B2, opravljene ure v celici C2 in kršitve pravilnika v celici D2. Ugnezdena formula IF v E2 je: 
    =IF(AND(B2>150, C2>35), "Excellent", IF(OR(B2>100, NOT(D2>1)), "Good", "Fair"))
    Nasvet: Ta formula najprej preveri, ali Anneina prodaja in delovni čas izpolnjujeta merila za »Odlično«. Če ne, oceni, ali izpolnjuje pogoje za "dobro". Če noben pogoj ni izpolnjen, je kategorizirana kot "poštena".
  2. Razširite to ugnezdeno formulo IF na vsakega zaposlenega, da dosledno ocenite njihovo uspešnost po več merilih.

    Ugnezdene funkcije IF

Uporaba ČE z IN ALI NE: pogosta vprašanja

Namen tega razdelka je obravnavati pogosto zastavljena vprašanja o uporabi ČE z IN, ALI in NE v Microsoft Excelu.

Koliko pogojev lahko podpirajo funkcije IN, ALI in NE?
  • Funkciji IN in ALI lahko podpirata do 255 posameznih pogojev. Vendar je priporočljivo, da jih uporabite le nekaj, da se izognete preveč zapletenim formulam, ki jih je težko vzdrževati.
  • Funkcija NOT upošteva samo en pogoj.
Ali lahko v teh funkcijah uporabim operatorje, kot so , =?

Zagotovo lahko v Excelovih funkcijah IN, ALI in NE uporabite operatorje, kot so manj kot (), enako (=), večje ali enako (>=) in več, da določite pogoje.

Zakaj se v teh funkcijah pojavi napaka #VALUE?

Napaka #VREDNOST v Excelovih funkcijah IN, ALI in NE se pogosto pojavi, če formula ne izpolnjuje nobenega podanega pogoja ali če obstaja težava s tem, kako je formula strukturirana. Označuje, da Excel ne more pravilno interpretirati vnosa ali pogojev v formuli.

Zgoraj je vsa ustrezna vsebina, povezana z uporabo IF s funkcijami IN, ALI in NE v Excelu. Upam, da vam bo vadnica v pomoč. Če želite raziskati več nasvetov in trikov za Excel, kliknite tukaj za dostop do naše obsežne zbirke več kot tisoč vadnic.