Kako koristiti if funkciju. Korištenje if funkcije

Ostavite komentar 6,950

Booleovi izrazi

Booleovi izrazi u Excelu koriste se za pisanje uvjeta koji uspoređuju brojeve, funkcije, formule, tekst ili Booleove vrijednosti. Svaki logički izraz mora sadržavati najmanje jedan operator usporedbe, koji definira odnos između elemenata logičkog izraza.

Ispod je popis operatora za usporedbu programa Excel:

Rezultat logičkog izraza je logička vrijednost TRUE (1) ili logička vrijednost FALSE (0).

IF funkcija

Funkcija AKO je funkcija koja vam omogućuje prikaz jedne vrijednosti u ćeliji ako je ispunjen uvjet koji je odredio korisnik i druge vrijednosti ako uvjet nije ispunjen.

Sintaksa:

IF(log_izraz; vrijednost_ako_istina; vrijednost_ako_netočno)

Pomaknite kursor na ćeliju D2 . Koristite čarobnjaka za funkcije za odabir iz kategorije mozgalica funkcija AKO(Sl. 27), a zatim kliknite na gumb OK.

Dijaloški prozor Argumenti funkcije(Slika 28) sadrži tri polja za unos . U polju Dnevnik_ izraz potrebno je unijeti uvjet koji određuje da li je količina prodane robe veća od 5 kom., stoga upisujemo u ovo polje C2>5. U polju Vrijednost_ako_točno morate unijeti formulu koja izračunava trošak proizvoda uzimajući u obzir popust, a zatim unesite u ovo polje B2*C2-B2*C2*0,1. U polju Vrijednost_ako_netočno potrebno je unijeti formulu koja izračunava trošak proizvoda bez uzimanja u obzir popusta (uvjet C2>5- False), zatim unesite u ovo polje B2*C2(vidi sliku 29). Sada kliknimo na gumb U REDU.

Kopirajte dobivenu formulu u susjedne ćelije. Rezultati izračuna formule prikazani su na sl. trideset .

Funkcije AKO mogu biti ugniježđeni jedan u drugi kao argumenti value_if_true i value_if_false. Korištenje ugniježđenih funkcija poput ove Ako Moguće je konstruirati složenije provjere. Pogledajmo primjere kako koristiti ugniježđenu funkciju AKO.

Na sl. 31 prikazuje tablicu s podacima. Potrebno:

Odredite količinu prodane robe za svaku vrstu.

Odredite iznos za koji je roba svake vrste prodana, uzimajući u obzir popust (iznos s popustom = iznos - iznos * popust). Popust se izračunava prema sljedećem principu: ako se roba prodaje u iznosu većem od 2500 UAH, tada će popust biti 5%, ako se roba prodaje u iznosu manjem od 1100 UAH, tada će popust biti 0% , u ostalim slučajevima popust će biti 2%.

Da biste ispunili prvu točku zadatka, morate ući u ćeliju D2 unesite formulu =C2*B2.

Kako bismo izračunali iznos popusta koristit ćemo ugniježđenu funkciju AKO, budući da postoje tri mogućnosti obračuna popusta.

Pomaknite kursor na ćeliju E2 i pomoću čarobnjaka za funkcije unesite sljedeću formulu (sl. 33 – 34). Rezultati rješenja prikazani su na sl. 35.

Kliknite ovdje

Riža. 35

Funkcije OR, AND

Funkcije I i funkcija ILI iz kategorije mozgalica može značajno poboljšati vidljivost i razumljivost složenih logičkih provjera. Funkcija ILI i funkcija I omogućuju vam da navedete nekoliko uvjeta u formuli u isto vrijeme, tj. omogućuju stvaranje složenih logičkih izraza. Ove funkcije rade u kombinaciji s jednostavnim operatorima usporedbe. Funkcije I I ILI može imati do 30 booleovih argumenata i imati sintaksu:

ILI(booleova_vrijednost1, booleova_vrijednost2, ...)

AND(booleova_vrijednost1; booleova_vrijednost2; ...)

Argumenti funkcije I, ILI mogu biti Booleovi izrazi, nizovi ili reference ćelija koje sadrže Booleove vrijednosti.

Funkcija ILI PRAVI, ako je barem jedan od logičkih izraza istinit, i funkcija I vraća Booleovu vrijednost PRAVI, samo ako su svi Booleovi izrazi istiniti.

Pretpostavimo da želite prikazati poruku "Semafor" ako je sadržaj ćelije B4 "crven", "zelen" ili "žut". Ako sadrži bilo kakve druge podatke, mora se prikazati poruka “Ovo nije semafor!!!”.

IF(ILI(B4="zeleno";B4="Crvena";B4="žuta boja");"Semafor"; "Ovo nije semafor!!!"

Pretpostavimo da želite prikazati sadržaj ćelije B4, ako sadrži broj striktno između 1 i 100 (1<=B4<=100), и сообщение "Значение вне интервала" в противном случае.

IF(AND(B4>=1; B4<=100); B4; «Значение вне интервала»)

Postoji popis datuma, potrebno je utvrditi je li uneseni datum praznik. Rješenja problema na sl. 36

Formula u ćeliji U 2 može se unijeti s tipkovnice (morate zapamtiti sintaksu korištenih ugniježđenih funkcija) ili pomoću čarobnjaka za funkcije. Pogledajmo kako unijeti formulu pomoću čarobnjaka za funkcije:

Pomaknite kursor na ćeliju NA 2, Pritisnite gumb za umetanje funkcije i odaberite funkciju u logičkoj kategoriji Ako.

Kliknimo u polje Dnevnik_ izraz funkcije Ako, a zatim kliknite na strelicu prema dolje u traci formule i odaberite Ostale funkcije(vidi sliku 37). Sada u kategoriji mozgalica možete odabrati funkciju ILI.

Kliknimo u polje Booleov_izraz_1 funkcije ILI, a zatim kliknite na strelicu prema dolje u traci formule i odaberite Ostale funkcije. U kategoriji Datum i vrijeme odaberite funkciju RADNI DAN i na terenu Datum_u_formatu_broja predstavimo se A2, i na terenu Tip predstavimo se 2 .

ILI ILI, kako bismo završili unos uvjeta u prvo polje ove funkcije (tip = 6).

Kliknimo u polje Logički_izraz_2 funkcije ILI i ponovite korake 3 i 4 za uvođenje drugog uvjeta WEEKDAY(A2,2)=7 .

Na traci formule kliknite unutar riječi AKO i tako se možemo vratiti u dijaloški okvir funkcije AKO, kako biste završili unos formule (u polju Vrijednost_ako_točno Unesi Slobodan dan, u polju Vrijednost_ako_netočno unesite razmak).

Podaci o rezultatima prve kontrole modula i prisutnosti studenata na nastavi prikazani su u tablici, vidi sl. 38. Moraju biti istaknuti sljedeći podaci: Ukoliko je prosjek ocjena studenta manji ili jednak 3,5 i izostajao je više od 49 sati nastave iz neopravdanog razloga, tada je potrebno pozvati roditelje u dekanat; Ako je učenikov prosjek uspjeha veći od ili 4,5 i nije izostao više od 10 sati nastave iz neopravdanog razloga, tada mora poslati pismo zahvalnosti svojim roditeljima. Rješenje problema prikazano je na sl. 39.

Funkcije COUNTIF i SUMMIF

Ove funkcije ne treba tražiti u kategoriji mozgalica, i u kategorijama Statistički i matematički, odnosno (ili u potpunom abecednom popisu).

COUNTIF- broji broj ćelija u rasponu koje zadovoljavaju zadani uvjet, i SUMIF- zbraja vrijednosti ćelija koje zadovoljavaju zadani uvjet. Funkcija SUMIF koristi se u slučajevima kada je potrebno zbrojiti ne cijeli raspon, već samo ćelije koje zadovoljavaju određene uvjete (kriterije).

Sintaksa:

COUNTIF (raspon; kriterij)

domet- raspon u kojem ćelije treba brojati.

kriterij- kriterij (uvjet) u obliku broja, izraza ili teksta koji određuje koje ćelije treba prebrojati.

Funkcija COUNTIF radi na sljedeći način: izračunava broj ćelija u rasponu čije vrijednosti zadovoljavaju uvjet (kriterij).

U zadatku razmatranom u primjeru 10 potrebno je utvrditi broj učenika čiji je prosjek >=4,5.

Zatim u ćeliju C15 trebate unijeti formulu: =COUNTIF(B11:B13;">=4.5"). Rezultat je prikazan na sl. 40

Sintaksa:

SUMIF (raspon, kriterij, raspon_zbroja)

raspon - raspon ćelija provjerenih za kriterij (uvjet).

kriterij - kriterij (uvjet) u obliku broja, izraza ili teksta koji definira ćelije koje se zbrajaju.

raspon_zbroja - stvarne ćelije koje treba zbrojiti.

Funkcija SUMIF radi na sljedeći način: ćelije iz " raspon_zbroja" zbrajaju se samo ako odgovarajuće ćelije u argumentu " domet»zadovoljiti uvjet (kriterij). U slučajevima kada su raspon ćelija koje se izračunavaju (gdje se uvjet testira) i raspon stvarnih ćelija koje se zbrajaju isti, argument raspon_zbroja može se izostaviti.

U tablici na Sl. 41 prikazuje količine pošiljaka koje trgovina prima. Potrebno je zbrojiti samo volumene onih serija čije vrijednosti prelaze 20. Rješenje na Sl. 42

IF(logički_izraz, vrijednost_ako_istina, vrijednost_ako_netočno)

Da biste to učinili, trebate učiniti sljedeće:

Pritisnite ikonu za umetanje formule (Fx).
U izborniku koji se pojavi odaberite “IF”.

Kao rezultat toga, vidjet ćete prozor u kojem trebate navesti argumente funkcije:
- Booleov izraz;
- vrijednost ako je istina;
- vrijednost ako je lažna.
Nakon unosa vrijednosti u ta polja, desno od njih prikazat će se preliminarni rezultat.
Za umetanje kliknite na gumb "OK".

Primjeri

Pogledajmo nekoliko različitih Booleovih izraza kako bismo vam pomogli razumjeti kako koristiti ovu funkciju.

Učinite ćeliju "A1" aktivnom.
Ponovite gore opisane korake za otvaranje prozora za umetanje "Argumenata".
Unesite sljedeći kod u polje uvjeta.

C1=””

Značenje ovog izraza je sljedeće: ako ćelija C1 sadrži prazninu. Odnosno, u njemu nema ničega.

U polje za istinitost upišite sljedeće.

"Ćelija C1 je prazna"

Ovo je tekst koji će se prikazati ako je ovaj uvjet ispunjen.

U polje "if false" unesite sljedeće.

"Ćelija C1 nije prazna"

Vidjet ćemo ovaj tekst ako je uvjet lažan.

Da biste umetnuli našu formulu, kliknite na gumb "OK".

Kao rezultat toga, vidjet ćemo sljedeće (budući da je ćelija prazna, poruka je odgovarajuća).

Unesite bilo koji tekst u ćeliju C.

Nakon što kliknete na gumb Enter, vidjet ćete sljedeći rezultat.

Kao što vidite, Excel editor je proizveo poruku koju smo naznačili u slučaju lažnog rezultata uvjeta.

Operatori usporedbe

Ispod vidite popis operatora za usporedbu:

Aritmetički operatori

A ovo je tablica aritmetičkih operatora:

Više o aritmetičkim operatorima i njihovom prioritetu u Excelu možete pročitati u mrežnoj pomoći na Microsoftovoj web stranici.

Višestruki uvjeti

Ako želite, možete koristiti veliki broj Booleovih izraza. Sve radi potpuno isto. Pogledajmo primjer složenijeg stanja.

Formulu ćemo izraditi pomoću alatne trake. Ovo je mnogo prikladnije nego pisati sve u jednom retku, jer se vrlo lako zbuniti. Pogotovo na samom početku, kada tek učite.

Da biste to učinili, morate izvršiti sljedeće korake.

Idite na karticu "Formule". Kliknite na gumb "Umetni funkciju".

Kao rezultat toga, cijeli sadržaj ćelije automatski će biti zamijenjen u "konstruktoru".

Izbrišite sve u polju "Value_if_false".

Sada će novi logički izraz biti napisan u ovom redu. To jest, imat ćemo novi ugniježđeni uvjet. Prvo unesite uvjet "Ako je vrijednost ćelije C1 veća od 1000." Da biste to učinili, unesite sljedeći kod. Obavezno stavite točku i zarez na kraju.

IF(C1>1000;

Obratite pozornost na trenutni raspored tipkovnice. Mnogi ljudi griješe i unose rusko slovo C umjesto engleskog C. Vizualno nećete vidjeti razliku, ali za urednika je to vrlo važno. U ovom slučaju ništa neće raditi.

Dodajmo sada poruku koja će se prikazati ako je broj u ćeliji C1 veći od 1000.

Sve tekstualne vrijednosti moraju biti unesene u navodnike.

Sada na potpuno isti način unosimo vrijednost ako ovaj uvjet nije ispunjen.

Na kraju kliknite na gumb "OK".

Kao rezultat toga, vidimo poruku da je uneseni broj veći od 1000.

Obrišite sadržaj ćelije C. Rezultat je sljedeći.

Testirali smo sva tri moguća ishoda. Sve super radi.

Kopiranje funkcije u tablice

Ponekad se dogodi da uneseni logički izraz treba duplicirati u nekoliko redaka. U nekim slučajevima morate puno duplicirati. Ova automatizacija je mnogo praktičnija od ručne provjere.

Pogledajmo primjer prepisivanja na tablici bonusa za zaposlenike tijekom praznika. Da biste to učinili, morate poduzeti sljedeće korake.

Napravite odgovarajuću tablicu.

Kliknite na praznu ćeliju u prvom redu i odaberite "Fx" u polju za unos.

U prozoru koji se pojavi odaberite funkciju “IF” i kliknite na gumb “OK”.

U prvo polje unesite sljedeći uvjet.

C6=”M”

Na taj način provjeravamo da li je zaposleni muškarac.

Ako je točno, unesite neki broj. Na primjer, 3000. Ako je uvjet lažan, unesite 0. To znači da djevojkama ne treba dati bonus. Za umetanje kliknite na gumb "OK".

Zatim zadržite pokazivač iznad donjeg desnog kuta ćelije. Nakon što kursor promijeni svoj izgled u "crni plus", bez puštanja prsta, povucite ga do posljednjeg retka.

Rezultat će biti sljedeći.

Sada potpuno kopirajte cijelu tablicu (pomoću tipkovničkog prečaca Ctrl + C i Ctrl + V). Promijenite naslov u 8. mart.

Idite do prve ćelije. Pritisnite polje za unos formule.

Promijenite slovo "M" u "F".

Sada duplicirajte funkciju na samo dno.

Rezultat će biti sljedeći.

Ovdje vidimo da se dogodilo potpuno suprotno. To znači da sve radi ispravno.

Korištenje dodatnih operatora

Osim aritmetičkih izraza i usporedbi, možete koristiti i operatore AND i OR. Pogledajmo ih pobliže jer su zahvaljujući njima mogućnosti funkcije "IF" značajno proširene.

Prije svega napravite tablicu koja će imati nekoliko polja pomoću kojih možete uspoređivati retke. U našem slučaju, pomoću polja “Status zaposlenika” provjerit ćemo tko treba uplatiti novac, a tko ne.

Operator AND

Koristit ćemo staru funkciju u kojoj smo provjeravali je li zaposlenik muškarac. Sada trebate napraviti sljedeće promjene.

Uvjetu dodajte zagradu i slovo "I" pokraj njega.
Stari uvjet bit će prvi, a drugi će biti iza točke i zareza.

=IF(AND(C35="M";D35="Glavno osoblje");3000;0)

Umnožite ovu formulu sve do dna.

Kao rezultat toga, vidimo da će oni zaposlenici koji nisu dio užeg osoblja biti uskraćeni za bonuse za godišnji odmor. Iako su muškarci.

Operater Or

Isti se učinak može postići uporabom operatora OR.

Da biste to učinili, napravite sljedeće promjene u formuli:

Promijenite slovo "F" u "M".
Promijenite status zaposlenika u “Radnik na nepuno radno vrijeme”.
U polje istine stavite 0, a za lažni događaj 3000. Zamijenimo ih.

Duplicirajmo formulu do posljednjeg retka.

Rezultat je bio potpuno isti. Činjenica je da su operatori "I" i "ILI" sušta suprotnost jedan drugome. Stoga je vrlo važno ispravno naznačiti vrijednosti u poljima true i false. Nemoj pogriješiti.

Za provjeru možete promijeniti status jednog zaposlenika u “Primarni kadar”.
Odmah nakon toga vidjet ćete da će se ispred njegovog prezimena pojaviti broj 3000.

funkcija SUMIF

Sigurno su mnogi od vas radili izračune u Excelu. Ali postoje trenuci kada ne morate brojati sve retke u tablici, već samo neke koji ispunjavaju određeni uvjet.

Na primjer, uzmimo tablicu koja navodi razne proizvode, njihovu cijenu, količinu i status. Izračunavanje ukupnog iznosa je jednostavno. Ali kako možete saznati broj prodanih proizvoda?

Danas ćemo pogledati funkciju AKO.

Funkcija IF često se koristi u Excelu za rješavanje mnogih problema. Vrlo ju je korisno poznavati. U ovom članku pokušat ćemo vam reći o njegovom radu koristeći jednostavne primjere; samo trebate jednom razumjeti konstrukciju funkcije IF i moći ćete je koristiti u najsloženijim slučajevima.

IF funkcija provjerava je li uvjet istinit i vraća jednu vrijednost ako je istinit i drugu vrijednost ako nije.

Sintaksa funkcije IF jako jednostavno:

IF(log_izraz ; [ vrijednost_ako_točno]; [vrijednost_ako_netočno])

log_izraz je bilo koja vrijednost ili izraz koji, kada se procjenjuje, daje vrijednost TRUE ili FALSE.

Što to znači? Izraz ima vrijednost TRUE ako je izraz istinit.

U ovom dijelu morate provjeriti dosljednost izraza.

Na primjer:

IF(A1=10; [value_if_true]; [value_if_false]) - ako je A1 jednako 10, onda će izraz A1=10 dati vrijednost TRUE, a ako nije jednako 10, onda će FALSE

Još jedan primjer

IF(A1>30; [value_if_true]; [value_if_false]) - ako je broj u ćelijama A1 veći od 30, tada će A1>30 vratiti TRUE, a ako je manji, tada će FALSE

Još jedan primjer

IF(C1=”Da” ; [vrijednost_ako_istina]; [vrijednost_ako_netočno]) - ako ćelije C1 sadrže riječ “Da”, onda će izraz vratiti vrijednost TRUE, a ako ne, tada će C1=”Da” vratiti FALSE

IF(log_izraz ; [ vrijednost_ako_točno]; [vrijednost_ako_netočno])

vrijednost_ako_točno, vrijednost_ako_netočno– kao što im ime govori, ovo je ono što treba učiniti ovisno o tome što je dnevnik izraza vratio: TRUE i FALSE

Primjer korištenja IF funkcije u Excelu

Pogledajmo korištenje funkcije IF na praktičnom primjeru. Imamo tablicu narudžbi koju smo koristili prilikom pregleda posla. Trebamo popuniti kolonu za Bucket naloge (na slici krivo piše “Table Orders”), odnosno odabrati samo naloge sa Bucketima. To se može učiniti na razne načine, ali mi ćemo koristiti funkciju IF da na primjeru pokažemo kako radi. (vidi sliku)

Da bismo riješili problem, napisat ćemo formulu pomoću funkcije IF

IF(A3="kanta";D3,"-")

Kao što ste možda primijetili, argumenti funkcije IF odvojeni su točkom-zarezom.

Dakle, prvi argument (log izraz) A3="Bucket" provjerava sadrži li ćelija A3 riječ "Bucket", ako sadrži, tada se izvršava drugi argument funkcije IF ( vrijednost_ako_točno), u našem slučaju to je D3 (tj. trošak narudžbe), ako ćelija A3 nije jednaka riječi "Bucket", tada se izvršava treći argument funkcije IF ( vrijednost_ako_netočno), u našem slučaju to je "-" (tj. bit će napisana crtica).

Tako će se u ćelijama E3 pojaviti vrijednost D3, odnosno broj 240.

Dobar dan.

Nekada davno sam napisati formulu u Excelu za mene je bilo nešto nevjerojatno. Čak i unatoč činjenici da sam često morao raditi u ovom programu, nisam upisao ništa osim teksta...

Kako se pokazalo, većina formula nije komplicirana i s njima može lako raditi čak i početni korisnik računala. U ovom članku želim otkriti najpotrebnije formule s kojima najčešće morate raditi...

Dakle, počnimo…

1. Osnovne operacije i osnove. Obuka o osnovama programa Excel.

Sve radnje u članku bit će prikazane u Excel verziji 2007.

Nakon pokretanja programa Excel pojavljuje se prozor s mnogo ćelija - naša tablica. glavna značajka Program je u tome što može izračunati (kao kalkulator) vaše formule koje napišete. Usput, možete dodati formulu u svaku ćeliju!

Formula mora započeti znakom "=". Ovo je obavezno. Zatim napišite što trebate izračunati: na primjer, "=2+3" (bez navodnika) i pritisnite tipku Enter - kao rezultat toga, vidjet ćete da se rezultat "5" pojavljuje u ćeliji. Pogledajte snimak zaslona u nastavku.

Važno! Unatoč činjenici da je broj "5" napisan u ćeliji A1, izračunava se pomoću formule ("=2+3"). Ako jednostavno napišete "5" u sljedeću ćeliju, tada kada postavite kursor iznad ove ćelije, u uređivaču formule (linija na vrhu, Fx) - vidjet ćete prosti broj “5”.

Sada zamislite da u ćeliju možete upisati ne samo vrijednost 2+3, već i brojeve ćelija čije vrijednosti treba zbrojiti. Recimo "=B2+C2".

Naravno, u B2 i C2 moraju postojati neki brojevi, inače će nam Excel pokazati rezultat jednak 0 u ćeliji A1.

I još jedna bitna napomena...

Kada kopirate ćeliju koja u sebi ima formulu, na primjer A1 - i zalijepite je u drugu ćeliju - ne kopira se vrijednost "5", već sama formula!

Štoviše, formula će se promijeniti u izravnom razmjeru: tj. ako se A1 kopira u A2, tada će formula u ćeliji A2 biti "=B3+C3". Excel automatski mijenja vašu formulu: ako je A1=B2+C2, onda je logično da je A2=B3+C3 (svi brojevi uvećani za 1).

Rezultat je, inače, u A2=0, jer Ćelije B3 i C3 nisu navedene, što znači da su jednake 0.

Na ovaj način možete napisati formulu jednom, a zatim je kopirati u sve ćelije željenog stupca - a Excel će sam izvršiti izračun u svakom retku vaše tablice!

Ako ne želite da se B2 i C2 mijenjaju prilikom kopiranja i uvijek su bili povezani s tim ćelijama, tada im jednostavno dodajte znak "$". Primjer ispod.

Na ovaj način, bez obzira gdje kopirate ćeliju A1, ona će se uvijek odnositi na povezane ćelije.

2. Dodavanje vrijednosti u retke (formula SUM i SUMIFS)

Možete, naravno, dodati svaku ćeliju, čineći formulu A1+A2+A3, itd. Ali kako biste izbjegli ovu gnjavažu, u Excelu postoji posebna formula koja će zbrojiti sve vrijednosti u ćelijama koje odaberete!

Uzmimo jednostavan primjer. Na skladištu imamo više artikala robe, a znamo koliko svakog proizvoda zasebno u kg. Na lageru. Pokušajmo izračunati koliko je u kg. teret u skladištu.

Da biste to učinili, idite u ćeliju u kojoj će biti prikazan rezultat i napišite formulu: “=SUM(C2:C5)”. Pogledajte snimak zaslona u nastavku.

Kao rezultat toga, sve ćelije u odabranom rasponu bit će zbrojene i vidjet ćete rezultat.

2.1. Dodatak s uvjetom (s uvjetima)

Sada zamislimo da imamo određene uvjete, tj. Ne morate zbrajati sve vrijednosti u ćelijama (Kg, na zalihama), već samo određene, recimo s cijenom (1 kg) manjom od 100.

Za to postoji prekrasna formula" SUMIFS". Odmah primjer, a potom i objašnjenje svakog simbola u formuli.

=SUMIFS(C2:C5,B2:B5,“<100» ) , Gdje:

C2:C5 - stupac (one ćelije) koji će se zbrajati;

B2:B5- stupac prema kojem će se provjeravati uvjet (tj. cijena, na primjer, manja od 100);

«<100» - sam uvjet, imajte na umu da je uvjet napisan pod navodnicima.

U ovoj formuli nema ništa komplicirano, glavna stvar je održati proporcionalnost: C2: C5; B2: B5 - točno; C2:C6;B2:B5 - netočno. Oni. Raspon zbroja i raspon uvjeta moraju biti proporcionalni, inače će formula vratiti pogrešku.

Važno! Za iznos može postojati mnogo uvjeta, tj. Možete provjeriti ne po 1. stupcu, već po 10 odjednom, postavljajući mnoge uvjete.

3. Prebrojavanje redaka koji zadovoljavaju uvjete (formula COUNTIFS)

Prilično uobičajen zadatak: ne brojati zbroj vrijednosti u ćelijama, već broj takvih ćelija koje zadovoljavaju određene uvjete. Ponekad postoji puno uvjeta.

Dakle, počnimo.

Za brojanje robe u traženoj ćeliji napišite sljedeću formulu (vidi gore):

=COUNTIFS(B2:B5,“>90”), Gdje:

B2:B5- raspon kojim će provjeravati, prema uvjetu koji smo postavili;

">90"- sam uvjet nalazi se pod navodnicima.

Pokušajmo sada malo zakomplicirati naš primjer i dodati fakturu za još jedan uvjet: s cijenom većom od 90 + količina na skladištu manja od 20 kg.

Formula ima oblik:

=COUNTIFS(B2:B6,”>90″,C2:C6 ;“<20» )

Ovdje sve ostaje isto, osim još jednog uvjeta ( C2:C6;"<20″ ). Usput, takvih uvjeta može biti puno!

Jasno je da nitko neće napisati takve formule za tako malu tablicu, ali za tablicu od nekoliko stotina redaka to je sasvim druga stvar. Primjera radi, ova tablica je više nego jasna.

4. Pretraživanje i zamjena vrijednosti iz jedne tablice u drugu (VLOOKUP formula)

Zamislimo da smo dobili novu tablicu s novim cijenama za robu. Dobro je ako ima 10-20 artikala, možete ih sve ručno "dopuniti". Što ako takvih imena ima na stotine? Bilo bi puno brže kad bi Excel samostalno pronalazio podudarna imena iz jedne tablice u drugu, a zatim kopirao nove oznake cijena u našu staru tablicu.

Za takav problem koristi se formula VLOOKUP. Jedno vrijeme sam se igrao s logičkim formulama "AKO" dok nisam naišao na ovu divnu stvar!

Dakle, počnimo…

Evo našeg primjera + nova tablica s oznakama cijena. Sada moramo automatski zamijeniti nove cjenike iz nove tablice u staru (nove cjenike su crvene).

Postavite kursor u ćeliju B2 - tj. u prvu ćeliju u kojoj trebamo automatski promijeniti oznaku cijene. Zatim pišemo formulu kao na snimci zaslona u nastavku (nakon snimke zaslona bit će njeno detaljno objašnjenje).

=VLOOKUP(A2 ;$D$2:$E$5 ;2 ), Gdje

A2- vrijednost koju ćemo tražiti kako bismo uzeli novu cijenu. U našem slučaju, tražimo riječ "jabuke" u novoj tablici.

$D$2:$E$5- kompletno označiti našu novu tablicu (D2:E5, izbor ide od gornjeg lijevog kuta do donjeg desnog dijagonalno), tj. gdje će se izvršiti pretraga. Znak "$" u ovoj formuli neophodan je tako da se prilikom kopiranja ove formule u druge ćelije - D2: E5 ne mijenja!

Važno! Riječ "jabuke" tražit će se samo u prvom stupcu odabrane tablice; u ovom primjeru "jabuke" će se tražiti u stupcu D.

2 - Kada se pronađe riječ “jabuke”, funkcija mora znati iz kojeg stupca odabrane tablice (D2:E5) kopirati željenu vrijednost. U našem primjeru kopirajte iz stupca 2 (E), jer u prvom stupcu (D) izvršili smo pretragu. Ako se vaša odabrana tablica za pretraživanje sastoji od 10 stupaca, tada se pretražuje prvi stupac, a od stupaca 2 do 10 možete odabrati broj za kopiranje.

Do formula =VLOOKUP(A2,$D$2:$E$5,2) zamijenio nove vrijednosti za druge nazive proizvoda - samo ga kopirajte u druge ćelije stupca s oznakama cijena proizvoda (u našem primjeru kopirajte u ćelije B3:B5). Formula će automatski pretražiti i kopirati vrijednost iz stupca koji vam je potreban u novoj tablici.

5. Zaključak

U ovom smo članku pogledali osnove rada s Excelom, počevši od toga kako početi pisati formule. Naveli su primjere najčešćih formula s kojima većina ljudi koji rade u Excelu često mora raditi.

Nadam se da će analizirani primjeri nekome biti od koristi i da će ubrzati njihov rad. Sretno eksperimentiranje!

Koje formule koristite? Je li moguće nekako pojednostaviti formule dane u članku? Na primjer, na slabim računalima, kada se mijenjaju neke vrijednosti u velikim tablicama gdje se izračuni rade automatski, računalo se zamrzne na nekoliko sekundi, ponovno izračunava i prikazuje nove rezultate...

Booleovi izrazi koriste se za pisanje uvjeta koji uspoređuju brojeve, funkcije, formule, tekst ili Booleove vrijednosti. Svaki logički izraz mora sadržavati najmanje jedan operator usporedbe, koji definira odnos između elemenata logičkog izraza. Ispod je popis operatora za usporedbu programa Excel

>= Veće ili jednako

Rezultat logičkog izraza je logička vrijednost TRUE (1) ili logička vrijednost FALSE (0).

IF funkcija

Funkcija IF ima sljedeću sintaksu:

=IF(logički_izraz, vrijednost_ako_točno, vrijednost_ako_netočno)

Sljedeća formula vraća 10 ako je vrijednost u ćeliji A1 veća od 3, odnosno 20 u suprotnom:

IF(A1>3,10,20)

Možete koristiti druge funkcije kao argumente za funkciju IF. Funkcija IF može koristiti tekstualne argumente. Na primjer:

IF(A1>=4;"Položio test","Pao na testu")

Možete koristiti tekstualne argumente u funkciji IF tako da će, ako uvjet nije ispunjen, vratiti prazan niz umjesto 0.

Na primjer:

IF(SUM(A1:A3)=30,A10,"")

Argument boolean_expression funkcije IF može sadržavati tekstualnu vrijednost. Na primjer:

IF(A1="Dinamo";10;290)

Ova formula vraća 10 ako ćelija A1 sadrži niz "Dynamo" i 290 ako sadrži bilo koju drugu vrijednost. Podudaranje između tekstualnih vrijednosti koje se uspoređuju mora biti točno, ali ne razlikuje velika i mala slova.

Funkcije I, ILI, NE

Funkcije AND (AND), OR (OR), NOT (NOT) - omogućuju vam stvaranje složenih logičkih izraza. Ove funkcije rade u kombinaciji s jednostavnim operatorima usporedbe. Funkcije AND i OR mogu imati do 30 Booleovih argumenata i imaju sljedeću sintaksu:

=I(booleova_vrijednost1,booleova_vrijednost2...)
=ILI(booleova_vrijednost1, booleova_vrijednost2...)

Funkcija NOT ima samo jedan argument i sljedeću sintaksu:

=NOT(boolean_vrijednost)

Argumenti za funkcije AND, OR i NOT ne mogu biti Booleovi izrazi, nizovi ili reference na ćelije koje sadrže Booleove vrijednosti.

Navedimo primjer. Neka Excel vrati tekst "Položio" ako učenik ima GPA veći od 4 (ćelija A2) i stopu izostanaka s nastave manju od 3 (ćelija A3). Formula će izgledati ovako:

=IF(AND(A2>4,A3

Iako funkcija OR ima iste argumente kao funkcija AND, rezultati su potpuno drugačiji. Dakle, ako u prethodnoj formuli funkciju I zamijenimo s ILI, tada će učenik položiti ako je zadovoljen barem jedan od uvjeta (prosječna ocjena veća od 4 ili izostanak manji od 3). Dakle, funkcija OR vraća logičku vrijednost TRUE ako je barem jedan od logičkih izraza istinit, a funkcija AND vraća logičku vrijednost TRUE samo ako su svi logički izrazi istiniti.

Funkcija NE preokreće vrijednost svog argumenta na suprotnu Booleovu vrijednost i obično se koristi u kombinaciji s drugim funkcijama. Ova funkcija vraća logičku vrijednost TRUE ako je argument FALSE i logičku vrijednost FALSE ako je argument TRUE.

Ugniježđene IF funkcije

Ponekad može biti vrlo teško riješiti logički problem koristeći samo operatore za usporedbu i funkcije AND, OR, NOT. U tim slučajevima možete koristiti ugniježđene IF funkcije. Na primjer, sljedeća formula koristi tri IF funkcije:

=IF(A1=100,"Uvijek";IF(AND(A1>=80;A1 =60;A1)

Ako je vrijednost u ćeliji A1 cijeli broj, formula glasi: "Ako je vrijednost u ćeliji A1 100, vratite niz "Uvijek". U suprotnom, ako je vrijednost u ćeliji A1 između 80 i 100, vratite "Uobičajeno". inače, ako je vrijednost u ćeliji A1 između 60 i 80, vratite redak "Ponekad." I, ako niti jedan od ovih uvjeta nije istinit, vratite redak "Nikada." Ukupno je dopušteno 7 razina ugniježđivanja IF funkcija .