IF Formula Excel - Izjave If Then

Preuzmite Primjer radne knjige

Preuzmite primjer radne knjige

Ovaj vodič pokazuje kako se koristi Excel IF funkcija u Excelu za stvaranje If If izjava.

Pregled funkcije IF

IF funkcija provjerava je li uvjet ispunjen. Ako TRUE učini jedno, ako FALSE učini drugo.

Da biste koristili funkciju radnog lista IF Excel, odaberite ćeliju i upišite:

(Obratite pažnju na to kako se pojavljuju unosi formule)

IF funkcija Sintaksa i ulazi:

1	= IF (logički_test, vrijednost_ako je istinita, vrijednost_ako je lažna)

logički_test - Logički izraz. Primjer: A1> 4.

vrijednost_ako_istina - Vrijednost ili izračun za izvođenje ako je logički izraz TRUE.

value_if_false - Vrijednost ili izračun za izvođenje ako je logički izraz FALSE.

IF je "uvjetna" funkcija. To znači da definirate logički test i on će vratiti jednu vrijednost ako se taj test ocijeni kao istinita, a drugu vrijednost ako je netočna

Kako koristiti funkciju IF

Evo vrlo osnovnog primjera pa možete vidjeti na što mislim. Pokušajte unijeti sljedeće u Excel:

1	= IF (2 + 2 = 4, "Istina je", "Lažno je!")

Budući da je 2 + 2 zapravo jednako 4, Excel će vratiti "Istina je!". Ako smo koristili ovo:

1	= IF (2 + 2 = 5, "Istina je", "Lažno je!")

Sada će Excel vratiti "To je lažno!", Jer 2 + 2 nije jednako 5.

Evo kako biste mogli koristiti IF u proračunskoj tablici.

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

Vodite sportski bar i postavljate pojedinačna ograničenja za različite korisnike. Ovu ste proračunsku tablicu postavili za provjeru je li svaki kupac prekoračio ograničenje, u tom ćete ga slučaju odrezati dok ne plate svoju karticu.

Provjeravate je li C4-D4 (njihov trenutni iznos kartice minus granica) veći od 0. Ovo je vaš logički test. Ako je to istina, IF vrati "Da" - trebali biste ih odrezati. Ako je ovo netočno, IF vrati "Ne" - dopustite im da nastave piti.

Što IF može vratiti

Iznad smo vratili tekstualni niz, "Da" ili "Ne". Ali možete vratiti i brojeve, pa čak i druge formule.

Recimo da neki od vaših klijenata pokreću velike kartice. Kako biste to obeshrabrili, počet ćete naplaćivati ​​kamate klijentima koji pređu svoje granice.

Za to možete koristiti IF:

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

Ako je kartica veća od ograničenja, vratite karticu pomnoženu s 0,03, što vraća 3% kartice. U suprotnom vratite 0: nisu preko svoje kartice pa nećete zaračunati kamate.

Korištenje IF s AND

IF možete kombinirati s Excelovom funkcijom AND <>. To koristite u logičkom testu koji vam omogućuje da navedete dva ili više uvjeta za testiranje. Excel će vratiti TRUE samo ako su svi testovi točni.

Dakle, implementirali ste svoju kamatnu stopu. No, neki vaši redoviti ljudi žale se. Uvijek su plaćali svoje kartice u prošlosti, zašto ih sada suzbijate? Došli ste do rješenja: određenim klijentima od povjerenja nećete naplaćivati ​​kamate.

Napravite novi stupac u svojoj proračunskoj tablici kako biste identificirali pouzdane klijente i ažurirate svoju izjavu IF funkcijom AND:

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

Pogledajmo dio AND odvojeno:

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

Imajte na umu dva uvjeta:

• C4> D4: provjera jesu li premašili ograničenje kartica, kao i prije
• F4 = "Ne": ovo je novi bit, provjerava se da li je pouzdan kupac

Dakle, sada vraćamo kamatnu stopu samo ako je kupac preko svoje kartice, a mi imamo "Ne" u stupcu pouzdanog klijenta. Vaši redovnici ponovno su sretni.

Pročitajte više na glavnoj stranici za Excel i funkciju <>.

Korištenje IF s OR

ILI je još jedna od Excelovih logičkih funkcija. Kao i AND, omogućuje vam definiranje više uvjeta. No, za razliku od AND, vratit će se TRUE ako je BILO KOJI od testova koje definirate istinit.

Možda korisnici koji su preko svojih kartica nisu jedini razlog zašto biste ih prekinuli. Možda nekim ljudima date privremenu zabranu iz drugih razloga, možda kockajući u prostorijama.

Dodali ste novi stupac za identifikaciju zabranjenih kupaca i ažurirali svoj "Odsečeno?" stupac s OR testom:

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

Gledajući samo dio OR:

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

Postoje dva uvjeta:

• C4> D4: provjerava jesu li prekoračili ograničenje broja kartica
• F4 = "Da": novi dio, provjeravajući jesu li trenutno zabranjeni

Ovo će se ocijeniti istinitim ako su preko svoje kartice ili ako u stupcu E. postoji "Da". Kao što vidite, Harry je sada odsječen, iako nije prekoračio ograničenje broja kartica.

Pročitajte više na glavnoj stranici za funkciju Excel ILI <>.

Korištenje IF s XOR -om

XOR je još jedna logička funkcija koja vraća “Exclusive Or”. Ovo je malo manje intuitivno od prethodnih o kojima smo govorili.

U jednostavnim slučajevima definirate dva uvjeta i XOR će se vratiti:

• TRUE ako je bilo koji argument istinit (isto kao i uobičajeno ILI)
• NETAČNO ako su oba argumenta točna
• NETAČNO ako su oba argumenta netočna

Primjer bi ovo mogao učiniti jasnijim. Zamislite da želite početi davati mjesečne bonuse svom osoblju:

• Ako prodaju više od 800 USD u hrani ili više od 800 USD u pićima, dat ćete im pola bonusa
• Ako u oba slučaja prodaju više od 800 USD, dat ćete im pun bonus
• Ako u oba slučaja prodaju ispod 800 USD, neće dobiti bonus.

Već znate kako vježbati ako dobiju puni bonus. Samo biste koristili IF s AND, kako je ranije opisano.

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

Ali kako biste shvatili tko dobiva pola bonusa? Tu dolazi XOR:

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

Kao što vidite, Woody’s je prodao piće preko 800 USD, ali ne i prodaju hrane. Tako dobiva polu bonus. Obrnuto vrijedi za trenera. Diane i Carla prodale su više od 800 USD za oboje, pa ne dobivaju pola bonusa (oba su argumenta ISTINA), a Rebecca je napravila ispod praga za oba (oba argumenta NETAČNO), pa formula opet vraća "Ne".

Pročitajte više na glavnoj stranici funkcije Excel XOR <>.

Korištenje IF s NOT

NOT je još jedna od Excelovih logičkih funkcija, koja se vrlo često koristi s IF -om.

NE poništava ishod logičkog testa. Drugim riječima, provjerava nije li uvjet ispunjen.

Možete ga koristiti s IF -om ovako:

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

Ovdje imamo tablicu s podacima o nekim filmovima iz 1980 -ih. Želimo identificirati filmove objavljene 1985. godine ili nakon njih, a koje nije režirao Steven Spielberg.

Budući da se NOT nalazi unutar funkcije AND, Excel će to prvo procijeniti. Tada će rezultat koristiti kao dio AND -a.

Pročitajte više na glavnoj stranici programa Excel NOT Funkcija <>.

Ugniježđeni IF izrazi

Također možete vratiti IF iskaz unutar svog IF izvoda. To vam omogućuje da napravite složenije izračune.

Vratimo se na stol naših kupaca. Zamislite da želite klasificirati klijente na temelju njihove razine duga prema vama:

• 0 USD: Nema
• Do 500 USD: Nisko
• 500 do 1000 USD: Srednje
• Više od 1000 USD: Visoko

To možete učiniti "ugniježđenjem" IF naredbi:

1	= IF (C4 = 0, "Ništa", IF (C4 <= 500, "Nisko", IF (C4 <= 1000, "Srednje", IF (C4> 1000, "Visoko")))

Lakše je razumjeti ako stavke IF stavite u zasebne retke (ALT + ENTER u sustavu Windows, CTRL + COMMAND + ENTER u računalima Mac):

12345

=IF (C4 = 0, "Ništa",IF (C4 <= 500, "Nisko",IF (C4 <= 1000, "Srednji",IF (C4> 1000, "Visoko", "Nepoznato"))))

AKO je C4 0, vraćamo "Ništa". U protivnom prelazimo na sljedeću IF naredbu. AKO je C4 jednak ili manji od 500, vraćamo se "Nisko". U protivnom prelazimo na sljedeću IF naredbu … i tako dalje.

Pojednostavljivanje složenih IF naredbi s pomoćnim stupcima

Ako imate više ugniježđenih IF izraza, a ubacujete i logičke funkcije, vaše formule mogu postati vrlo teške za čitanje, testiranje i ažuriranje.

To je osobito važno imati na umu ako će drugi ljudi koristiti proračunsku tablicu. Ono što ima smisla u vašoj glavi, drugima možda nije tako očito.

Stupci pomoćnici sjajan su način zaobilaženja ovog problema.

Analitičar ste u financijskom odjelu velike korporacije. Od vas je zatraženo da napravite proračunsku tablicu koja provjerava ispunjava li svaki zaposlenik pravo na mirovinu tvrtke.

Evo kriterija:

Dakle, ako ste mlađi od 55 godina, morate imati 30 godina staža da biste ispunili uvjete. Ako imate 55 do 59 godina, trebate 15 godina staža. I tako dalje, do 65. godine, gdje imate pravo bez obzira koliko dugo tamo radili.

Za rješavanje ovog problema mogli biste koristiti jednu, složenu IF naredbu:

1	= IF (OR (F4> = 65, AND (F4> = 62, G4> = 5), AND (F4> = 60, G4> = 10), AND (F4> = 55, G4> = 15), G4 > 30), "Ispunjava uvjete", "Ne ispunjava uvjete")

Vau! Teško je to shvatiti, zar ne?

Bolji pristup bi mogao biti korištenje pomoćnih stupaca. Ovdje imamo pet logičkih testova koji odgovaraju svakom retku u tablici kriterija. To je lakše vidjeti dodamo li prijelome retka formuli, kao što smo ranije raspravljali:

12345678

= IF (ILI(F4> = 65,AND (F4> = 62, G4> = 5),AND (F4> = 60, G4> = 10),AND (F4> = 55, G4> = 15),G4> 30), "Ispunjava uvjete", "Ne ispunjava uvjete")

Dakle, možemo podijeliti ovih pet testova u zasebne stupce, a zatim jednostavno provjeriti je li neki od njih istinit:

Svaki stupac u tablici od E do I drži svaki naš kriterij zasebno. Tada u J4 imamo sljedeću formulu:

1	= IF (COUNTIF (E4: I4, TRUE), "Podobno", "Ne ispunjava uvjete")

Ovdje imamo izraz IF, a logički test koristi COUNTIF <> za brojanje broja ćelija unutar E4: I4 koje sadrže TRUE.

Ako COUNTIF ne pronađe TRUE vrijednost, vratit će 0, što IF tumači kao FALSE, pa IF vraća "Ne ispunjava uvjete".

Ako COUNTIF pronađe bilo koju vrijednost TRUE, vratit će se njihov broj. IF tumači bilo koji broj osim 0 kao TRUE, pa vraća "Prihvatljivo".

Razdvajanje logičkih testova na ovaj način čini formulu lakšom za čitanje, a ako s njom nešto pođe po zlu, puno je lakše uočiti gdje je greška.

Korištenje grupiranja za skrivanje kolona pomoćnika

Pomoćni stupci olakšavaju upravljanje formulom, ali kad ih postavite i znate da rade ispravno, često samo zauzmu mjesto u vašoj proračunskoj tablici bez dodavanja korisnih informacija.

Mogli biste sakriti stupce, ali to može dovesti do problema jer je skrivene stupce teško otkriti, osim ako pomno pogledate zaglavlja stupaca.

Bolja opcija je grupiranje.

Odaberite stupce koje želite grupirati, u našem slučaju E: I. Zatim pritisnite ALT + SHIFT + STRELICA DESNO u sustavu Windows ili COMMAND + SHIFT + K na Macu. Također možete otići na karticu "Podaci" na vrpci i odabrati "Grupa" u odjeljku "Nacrt".

Vidjet ćete grupu prikazanu iznad zaglavlja stupaca, ovako:

Zatim jednostavno pritisnite gumb “-” da biste sakrili stupce:

IFS funkcija

Ugniježđeni IF izrazi vrlo su korisni kada trebate izvesti složenije logičke usporedbe, a to morate učiniti u jednoj ćeliji. Međutim, oni se mogu zakomplicirati kako postaju dulji, pa ih može biti teško čitati i ažurirati na zaslonu.

Iz programa Excel 2022 i Excel 365, Microsoft je uveo drugu funkciju, IFS, kako bi se time olakšalo upravljanje. Gore navedeni ugniježđeni primjer IF -a mogao bi se postići s IFS -om ovako:

1234567

= IFS (C4 = 0, "Ništa",C4 <= 500, "nisko",C4 <= 1000, "srednji",C4> 1000, "Visoko",TRUE, "Nepoznato",)

Sve o tome možete pročitati na glavnoj stranici za Excel IFS funkciju <>.

Korištenje IF s uvjetnim oblikovanjem

Excelova značajka uvjetnog oblikovanja omogućuje oblikovanje ćelije na različite načine, ovisno o njenom sadržaju. Budući da IF vraća različite vrijednosti na temelju našeg logičkog testa, možda bismo htjeli upotrijebiti uvjetno oblikovanje kako bismo te različite vrijednosti lakše vidjeli.

Vratimo se na naš bonus stolu bonusa od ranije.

Vraćamo "Da" ili "Ne" ovisno o bonusu koji želimo dati. To nam govori ono što moramo znati, ali nam informacije ne iskaču. Pokušajmo to popraviti.

Evo kako biste to učinili:

• Odaberite raspon ćelija koji sadrži vaše IF izraze. U našem slučaju to je E4: F8.
• Kliknite "Uvjetno oblikovanje" u odjeljku "Stilovi" na kartici "Početna" na vrpci.
• Kliknite "Označi pravila ćelija", a zatim "Jednako".
• U prvi okvir upišite "Da" (ili bilo koju povratnu vrijednost koja vam je potrebna), a zatim iz drugog okvira odaberite željeno oblikovanje. (Za ovo ću izabrati zelenu boju).
• Ponovite za sve povratne vrijednosti (također ću postaviti vrijednosti "Ne" na crveno)

Evo rezultata:

Korištenje IF u formulama niza

Niz je raspon vrijednosti, a u Excelu nizovi su predstavljeni kao vrijednosti odvojene zarezima zatvorene u zagradama, kao što su:

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

Ljepota nizova je u tome što vam omogućuju da izvršite izračun svake vrijednosti u rasponu, a zatim vratite rezultat. Na primjer, funkcija SUMPRODUCT uzima dva niza, množi ih zajedno i zbraja rezultate.

Dakle ova formula:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… Vraća 32. Zašto? Idemo to riješiti:

12345

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

Na ovu sliku možemo unijeti izraz IF, tako da se svako od ovih množenja događa samo ako logički test vrati točno.

Na primjer, uzmite ove podatke:

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

Napomena: U Excelu 2022 i starijim morate pritisnuti CTRL + SHIFT + ENTER da biste to pretvorili u formulu niza.

Završili bismo s nečim ovakvim:

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

Na engleskom, ako je naziv u stupcu C jednak onome što je u G2 ("Olivia"), DO pomnožite vrijednosti u stupcima D i E za taj redak. U suprotnom, nemojte ih umnožavati. Zatim zbrojite sve rezultate.

Više o ovoj formuli možete saznati na glavnoj stranici formule SUMPRODUCT IF <>.

AKO u Google tablicama

IF funkcija radi potpuno isto u Google tablicama kao i u Excelu:

dodatne napomene

Pomoću IF funkcije provjerite je li uvjet TRUE. Ako je uvjet TRUE, učinite jednu stvar. Ako je TOČNO, učinite drugo. Uvjet mora biti logički izraz (primjer: a1> 5), referenca na ćeliju koja sadrži TRUE ili FALSE ili niz koji sadrži sve logičke vrijednosti.

IF funkcija može testirati samo jedan uvjet odjednom. Međutim, možete "ugnijezditi" druge logičke funkcije unutar uvjeta IF da biste testirali više uvjeta odjednom:

= if (AND (a1> 0, a2> 0), TRUE, FALSE)
= if (OR (a1> 0, a2> 0), TRUE, FALSE)
= ako (XOR (a1> 0, a2> 0), TRUE, FALSE)
ILI Testirajte funkcije ako jedan ili više uslovi su ispunjeni.
AND Funkcije testiraju ako svi uslovi su ispunjeni.
XOR funkcije provjerite ako jedan i jedini uvjet je ispunjen.

IF funkciju također možete "ugnijezditi" u IF funkciju:

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

Sada za neke konkretne primjere kako funkcija IF funkcionira u praksi:

1. Pokrenite novu radnu knjigu.

2. U ćeliju A1 unesite vrijednost 10 (i pritisnite Enter)

3. Zatim u ćeliju B1 unesite sljedeću formulu:

1	= AKO (A1> 5, "VEĆE OD 5", "MANJE OD 5")

4. Zaslon bi trebao izgledati ovako:

5. Ako ste ispravno unijeli formulu, vidjet ćete poruku "Veće od 5" u ćeliji B1.

6. Formula koju ste unijeli u ćeliju B1 izvodi test "A1> 5", tj. Provjerava je li vrijednost u ćeliji A1 veća od 5. Trenutno je vrijednost u ćeliji A1 10 - dakle, uvjet je TRUE i poruka Pojavljuje se "VEĆE OD 5"

7. Ako sada promijenimo vrijednost u ćeliji A1 u 2:

Tada je poruka u ćeliji B2 sada "MANJA OD 5" jer je uvjet LAŽ.

8. Možete nastaviti mijenjati vrijednost u ćeliji A1 i poruka u ćeliji B2 će se prilagoditi u skladu s tim.

9. Naravno da postoje situacije u kojima bi stanje moglo dati nestašne rezultate:

• Što se događa ako unesemo vrijednost 5 u ćeliju A1?

• Što ako ostavimo ćeliju A1 praznu?

• Što ako stavimo neki tekst u ćeliju A1, npr. Izraz DOG

Više o funkciji Excel IF

Sada ćemo detaljnije pogledati IF funkciju. Može se koristiti za vrlo jednostavnu analizu velikih količina podataka.

Zamislite da ste voditelj prodaje na području i da imate prodajni tim. Možete zabilježiti ukupnu prodaju koju svaka osoba ostvari u jednostavnoj Excel proračunskoj tablici:

Pretpostavimo da je kriterij za bonus bio da je prodaja od te osobe premašila 40.000 funti. Mogli ste samo "pogledati" podatke i utvrditi da su samo Anton, Newton i Monique ispunili cilj.

To je prilično jednostavno ako imate samo nekoliko imena. Međutim, ako ih imate nekoliko, postoji mogućnost pogreške. Srećom, pomoću Excel -ove IF funkcije to se može učiniti mnogo brže i sigurnije.

Postavite novu radnu knjigu i upišite podatke prema gore navedenom. Zatim u ćeliju D4 upišite sljedeću formulu:-

1	= AKO (C4> 40000, "BONUS PLAĆEN", "BEZ BONUSA")

tako da imate:

Zapazite kako Excel prikazuje strukturu formule IF - što je korisna pomoćna bilješka.

Nakon što ste unijeli formulu, pritisnite ENTER i vidjet ćete kako se procjenjuje za prvi red:

Formula je procijenjena za Martina - budući da je zaradio manje od 40.000 funti, nema pravo na bonus.

Zatim povlačimo formule prema dolje klikom na donji desni kut i povlačenjem prema dolje možemo odrediti ima li svaka osoba pravo na bonus:

Vidimo da je Excel odredio tko od prodavača ima pravo na bonus.

Povratak na popis svih funkcija u Excelu

VBA IF Izjave

Također možete koristiti If Statements u VBA. Kliknite vezu da biste saznali više, ali evo jednostavnog primjera:

1234567

Podispit_IF ()Ako je raspon ("a1"). Vrijednost <0 tadaRaspon ("b1"). Vrijednost = "Negativno"Završi akoZavrši ako

Ovaj kôd će testirati je li vrijednost ćelije negativna. Ako je tako, u sljedećoj ćeliji će napisati "negativno".