Kako koristiti funkciju ako u Excelu. Kako u Excelu pomoću ugniježđenih funkcija if () izračunati prodajni bonus

Sposobnost logičkog provjeravanja stanica moćan je alat. Naći ćete beskrajne svrhe za AKO A () u svakodnevnim dokumentima. Problem je u tome što su često potrebne provjere izvan mogućnosti jednostavne funkcije. AKO A () ... To se događa kada je potrebno prije prihvaćanja rezultata provjeriti dva ili više uvjeta.

U takvim slučajevima Excel nudi nekoliko mogućnosti: upotreba AKO A () unutar drugog AKO A (), funkcije I() i IL I()... Dalje ćemo se upoznati s tim metodama.

Koristeći AKO A () unutar druge funkcije AKO A ()

Razmotrimo varijantu koja se temelji na prethodno proučenoj funkciji \u003d IF (A1\u003e 1000; "puno"; "malo"). Što ako trebate ispisati još jedan niz kada je broj u A1, na primjer, veći od 10.000? Drugim riječima, ako je A1\u003e 1000 točno, morat ćete pokrenuti još jedan test i provjeriti je li A1\u003e 10000 točno. Takvu varijantu možete stvoriti primjenom druge funkcije AKO A () unutar prvog kao argument, vrijednost _if_true: \u003d IF (A1\u003e 1000; IF (A1\u003e 10000; "jako puno"; "mnogo"); "malo").

Ako je A1\u003e 1000 tačno, pokreće se druga funkcija AKO A (), koji vraća vrijednost "jako puno" kada je A1\u003e 10000. Ako je, međutim, A1 manji ili jednak 10000, vraća se vrijednost "puno". Ako je tijekom prve provjere broj A1 manji od 1000, prikazat će se vrijednost "malo".

Imajte na umu da možete jednako dobro pokrenuti i drugu provjeru je li prva netočna (to jest, u argumentu value_if_false funkcije ifo). Evo malog primjera koji vraća vrijednost "vrlo mali" kada je broj u A1 manji od 100: \u003d IF (A1\u003e 1000; "many"; IF (A1<100;"очень мало"; "мало")) .

Izračun prodajnog bonusa

Dobar primjer korištenja jedne provjere unutar druge je izračunavanje prodajnog bonusa osoblju. koji radi u Klubu - hotel Heliopark Talasso, Zvenigorod. U ovom slučaju, ako je vrijednost X, želite jedan rezultat, ako je Y drugi, ako je Z
- Treći. Na primjer, u slučaju izračunavanja bonusa za uspješnu prodaju moguće su tri mogućnosti:

  1. Prodavatelj nije dosegao planiranu vrijednost, bonus je 0.
  2. Prodavatelj je premašio planiranu vrijednost za manje od 10%, bonus je 1.000 rubalja.
  3. Prodavatelj je premašio planiranu vrijednost za više od 10%, bonus je jednak 10.000 rubalja.

Evo formule za izračunavanje takvog primjera: \u003d IF (E3\u003e 0; IF (E3\u003e 0,1; 10000; 1000); 0). Ako je vrijednost u E3 negativna, vraća se 0 (bez bonusa). Ako je rezultat pozitivan, provjerava se je li veći od 10%, a ovisno o tome izdaje se 1.000 ili 10.000. 4.17 prikazuje primjer kako formula djeluje.

Funkcija AND ()

Često je potrebno istovremeno provjeriti vjernost dvaju uvjeta. Na primjer, bonus se isplaćuje samo kada je prodaja određenog proizvoda premašila plan, a ujedno i ukupna prodaja premašila je plan. Ako jedan od ovih uvjeta nije zadovoljen (ili oba uvjeta), premija se ne plaća. U logičkoj logici to se naziva logičkim I jer oba uvjeta moraju biti istinita da bi cijeli izraz bio istinit.

U Excelu, logički izrazi I obrađuju se pomoću funkcije I(): AND (boolean_value1; boolean_value2; ...). Svaki je argument logička vrijednost za testiranje. Možete unijeti koliko god želite argumenata.

Još jednom, zabilježimo kako funkcionira funkcija:

  • Ako svi izrazi vrate TRUE (ili bilo koji pozitivan broj), I() vraća TRUE.
  • Ako jedan ili više argumenata vrati FALSE (ili 0), I() vraća FALSE.

Najčešće I() primijenjen unutar funkcije AKO A ()... U ovom slučaju, kada su svi argumenti unutra I() vratit će TRUE, funkcija AKO A () slijedit će vrijednost njegove grane ako je true. Ako jedan ili više izraza u I() vratit će FALSE, funkciju AKO A () slijedit će granu value_if_false.

Evo malog primjera: \u003d IF (AND (C2\u003e 0; B2\u003e 0); 1000; "bez bonusa"). Ako je vrijednost u B2 veća od nule, a vrijednost u C2 veća od nule, formula će vratiti 1000, inače će se prikazati redak "bez bonusa".

Razdvajanje vrijednosti u kategorije

Korisna upotreba funkcije and () je kategorizacija po vrijednosti. Na primjer, imate tablicu s rezultatima ankete ili glasovanja, a sve glasove želite podijeliti u kategorije prema sljedećim dobnim rasponima: 18-34,35-49, 50-64,65 ili više. Pod pretpostavkom da je dob ispitanika u ćeliji B9, sljedeći argumenti funkcije i () izvršavaju logičku provjeru starosti koja pripada rasponu: \u003d AND (B9\u003e \u003d 18; B9


Ako je odgovor osobe u ćeliji C9, sljedeća će formula dati rezultat glasanja osobe ako se pokrene provjera usklađenosti s dobnom skupinom 18-34: \u003d IF (AND (B9\u003e \u003d 18; B9

  • 35-49: \u003d AKO (I (B9\u003e \u003d 35; B9
  • 50-64: \u003d AKO (I (B9\u003e \u003d 50; B9
  • 65+: \u003d IF (B9\u003e \u003d 65; C9; "")

ILI () funkcija

Postoje situacije kada vam je potrebna odluka samo kada je jedan od uvjeta pozitivan (ISTINITO). Na primjer, možda ćete odlučiti platiti bonus zaposlenicima kada ukupna prodaja premaši ciljanu ili kada je prodaja pojedinog predmeta veća od predviđene. U logičkoj logici to se naziva logičkom ILI.

Takvi se uvjeti provjeravaju u programu Excel pomoću funkcije ILI(): ILI (boolean1, boolean2, ...). Svaki je argument logička vrijednost za testiranje. Možete unijeti koliko god želite argumenata. Rezultat rada ILI() ovisi o sljedećim uvjetima:

  • Ako jedan ili više argumenata vrati TRUE (bilo koji pozitivan broj), ILI() vraća TRUE.
  • Ako svi argumenti vrate FALSE (nula), rezultat je ILI() bit će LAŽNO.

Kao i I(), najčešće funkcija ILI() koristi se unutar provjere AKO A ()... U ovom slučaju, kada je jedan od argumenata unutra ILI() vratit će TRUE, funkcija AKO A () slijedit će njegovu granu value_if_true. Ako svi izrazi u ILI() vratit će FALSE, funkciju AKO A () slijedit će granu value_if_false... Evo malog primjera: \u003d IF (ILI (C2\u003e 0; B2\u003e 0); 1000; "bez bonusa").

Ako se u jednoj od ćelija nalazi pozitivan broj (C2 ili B2), funkcija će vratiti 1000. Tek kad su obje vrijednosti negativne (ili jednake nuli), funkcija će vratiti niz "bez bonusa".

Ovaj će se članak usredotočiti na funkciju "Ako" u programu Excel za proračunske tablice. Bavi se logičkim mogućnostima aplikacije. Ova se funkcija može pripisati najčešćim, koji se često koriste tijekom rada.


Ključne značajke

U program Excel formula "Ako" omogućuje razne vrste zadataka koji zahtijevaju usporedbu određenih vrijednosti i dobivanje rezultata. Ovo rješenje omogućuje upotrebu algoritama grananja i izradu stabla odluka.

Primjeri primjene

Funkcija izgleda ovako: \u003d IF (zadatak; true; false). Prvi dio je logički izraz. Može se pojaviti kao fraza ili broj. Na primjer, "10" ili "bez PDV-a". Ovaj parametar mora biti popunjen. True je vrijednost prikazana kao rezultat ako je izraz true. False predstavlja podatke koji će se vratiti kada je zadatak netočan.

Jednakost parametara dviju stanica

Da biste bolje razumjeli mogućnosti funkcije If u Excelu, trebate navesti primjere. Stoga ih nadalje vrijedi početi razmatrati. U ćeliju C1 morate unijeti vrijednost 8. Nakon toga u polje na adresi D1 trebate unijeti formulu oblika: \u003d IF (C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Može se razmotriti još jedan primjer. Nekoliko je učenika i njihove ocjene zarađene za ispit. Podaci su sljedeći: 5, 4, kao i 3 i 2. Prema uvjetima zadatka, predviđeno je za svakog učenika stvoriti tekstualni komentar "prošao" ili "nije prošao". Dakle, ako student dobije ocjenu tri ili više, smatra se da je položio ispit. Ako je njegova ocjena manja od 3, učenik nije uspio dobro. Da biste riješili takav problem, napišite sljedeću formulu: \u003d IF (C1<3; «не справился»; «сдал»).

Program započinje s usporedbom rezultata svakog učenika. Ako je pokazatelj manji od tri, u odgovarajućoj ćeliji pojavit će se natpis "nije uspjelo". Kada je ocjena 3 i više, u traženom stupcu možete vidjeti komentar da student nije položio ispit. Treba napomenuti da se tekstualni komentari uvijek trebaju stavljati u navodnike. Kada koristite ovu funkciju, možete koristiti sljedeće operatore usporedbe:< >, =, >, <, >=, <=.

Primjeri korištenja uvjeta "ILI", "I"

Potrebno je nastaviti razmatrati logičke mogućnosti ove aplikacije. Moguće je kombinirati funkciju If s operatorima usporedbe. To su sljedeći parametri:

"ILI";
"I".

U Excelu je potrebno naznačiti važan uvjet: kada je ocjena učenika jednaka ili manja od 5, ali veća od 3. U tom slučaju treba prikazati komentar: "prolazi" ili "ne". Tako prolaze samo oni studenti koji su zaradili petice i četvorke. Da biste taj zadatak napisali u uređivaču proračunskih tablica, trebali biste primijeniti posebnu formulu. Izgleda ovako: \u003d IF (AND (A1<=5; A1>3); "Prolazi", "ne").

Ako uzmete u obzir složeniji primjer, morat ćete upotrijebiti "ILI" ili "I". Dakle, možete se upoznati s primjenom formule u Excelu ako u poslu postoji više uvjeta. Na primjer: \u003d IF (ILI (A1 \u003d 5; A1 \u003d 10); 100; 0). U ovom slučaju možemo zaključiti da kada je vrijednost u ćeliji A1 5 ili 10, program prikazuje rezultat 100. Inače je 0. Ovim je operaterima moguće pronaći rješenje za složenije probleme.

Na primjer, u bazi podataka morate izračunati dužnike koji trebaju platiti više od 10.000 rubalja. Možete postaviti uvjet da zajam nije vraćen dulje od šest mjeseci, odnosno šest mjeseci. Zahvaljujući funkciji uređivača proračunskih tablica Excel "If", moguće je automatski dobiti oznaku "problem client" pored odgovarajućih imena. Recimo da ćelija A1 sadrži podatke koji označavaju rok duga (mjeseci). Polje B1 sadrži iznos.

U ovom slučaju, formula je predstavljena u sljedećem obliku: \u003d IF (AND (A1\u003e \u003d 6; B1\u003e 10000); "problem client"; ""). Dakle, ako se identificira osoba koja ispunjava navedene uvjete, program prikazuje odgovarajući komentar ispred njenog imena. Za ostale članove popisa, ova ćelija ostat će prazna.

Možete vidjeti i primjer kada je situacija kritična. Mora se unijeti odgovarajući komentar. Kao rezultat, formula će biti sljedeća: \u003d IF (ILI (AL\u003e \u003d 6; B1\u003e 10000); "kritična situacija"; ""). Međutim, ako program otkrije podudaranja barem jednog od parametara, prikazuje se odgovarajuća napomena.

Izazovni zadaci

Excel-ova funkcija If koristi se za izbjegavanje ugrađenih podjela s nula pogrešaka. Osim toga, koristi se i u nekim drugim situacijama. Prvi slučaj označen je kao "DIV / 0". Može se naći prilično često. To se obično događa kada formula "A / B" zahtijeva kopiranje. Istodobno, pokazatelj B u pojedine stanice je 0. Da bi se izbjegla takva situacija, vrijedi koristiti mogućnosti dotičnog operatora. Dakle, potrebna formula je: \u003d IF (B1 \u003d 0; 0; A1 / B1). Dakle, ako je ćelija B1 ispunjena vrijednošću "nula", urednik će prikazati "0". Inače, program će podijeliti pokazatelj A1 s podacima B1 i dati traženi rezultat.

Popust

Kao što pokazuje praksa, često se javljaju situacije o kojima se govori u nastavku. Popust ćete trebati izračunati na temelju ukupnog iznosa utrošenog za kupnju određenog predmeta. Matrica koja se koristi je sljedeća: manje od 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; više od 5001 - 7%. Možete vidjeti situaciju kada Excel ima bazu podataka posjetitelja i podatke o iznosu potrošenom na kupnju. Sljedeći je korak izračun popusta za svakog kupca. Da biste to učinili, morate primijeniti sljedeći izraz: \u003d IF (A1\u003e \u003d 5001; B1 * 0,93; IF (A1\u003e \u003d 3001; B1 * 0,95; ..).

Sustav provjerava ukupan iznos kupnje. Ako premašuje vrijednost od 5001 rubalja, množenje se vrši s 93 posto cijene predmeta. Ako se premaši oznaka od 3001 jedinica, dogodi se slična radnja, ali 95% je već uzeto u obzir.

IF () funkcija , engleska verzija AKO (), koristi se prilikom provjere uvjeta. Na primjer, \u003d IF (A1\u003e 100; "Proračun premašen"; "U redu!")... Ovisno o vrijednosti u ćeliji A1 rezultat formule bit će "Proračun premašen" ili "U redu!"

Funkcija IF () jedna je od najčešće korištenih funkcija.

Sintaksa funkcije

IF (logički_test, value_if_true, [value_if_false])

Log_Expression - bilo koja vrijednost ili izraz koji poprimaju vrijednosti TRUE ili FALSE.
\u003d IF (A1\u003e \u003d 100; "Proračun premašen"; "U redu!")
Oni. ako je u ćeliji A1 sadrži vrijednost veću ili jednaku 100, tada će se formula vratiti U REDU!,a ako ne, onda Proračun premašen.

Formule se mogu koristiti kao argumenti funkcije, na primjer:
\u003d AKO (A1\u003e 100, SUM (B1: B10), SUM (C1: C10))
Oni. ako je u ćeliji A1 sadrži vrijednost\u003e 100, tada se zbrajanje vrši preko stupca B , a ako manje, onda duž kolone IZ .

Ugniježđeni IF

U EXCEL 2007, kao vrijednosti argumenata vrijednost_ako_istina i value_if_false možete koristiti do 64 funkcije IF () ugniježđene jedna u drugu za izgradnju složenijih provjera.
\u003d IF (A1\u003e \u003d 100; "Proračun premašen"; IF (A1\u003e \u003d 90; "Veliki projekt"; IF (A1\u003e \u003d 50; "Srednji projekt"; "Mali projekt")))

POGLED (A1; (0; 50; 90; 100); ("Mali projekt"; "Srednji projekt"; "Veliki projekt"; "Proračun premašen"))

PREGLED (A1; A3: B6; 2)

Za funkciju VLOOKUP () trebate stvoriti u rasponu A3: B6 tablica vrijednosti:

Ako želite prikazati drugačiji tekst ako je u ćeliji A1 negativna vrijednost, pozitivna vrijednost ili 0, tada možete napisati sljedeću formulu:

POGLEDAJ (A1; (- 1E + 307; 0; 1E-307); ("<0";"=0";">0"})

ili, ako želite prikazati formule umjesto tekstualnih vrijednosti, možete koristiti reference na niz ćelija (koji sadrže formule)

POTRAŽI (A24; (- 1E + 307; 0; 1E-307); A27: A29) (vidi primjer datoteke)

Izostavljen treći argument [value_if_false]

Treći argument funkcije nije obavezan, ako ga izostavite, funkcija će vratiti FALSE (ako uvjet nije zadovoljen).
\u003d IF (A1\u003e 100; "Proračun premašen")
Ako je u ćeliji A1 sadrži vrijednost 1, gornja formula vratit će FALSE.

U prvi je argument umjesto TRUE ili FALSE upisan broj

Jer vrijednost FALSE ekvivalentna je 0, a zatim formule
\u003d IF (0; "Proračun premašen"; "U redu!")
ili (ako je u ćeliji A1 sadrži vrijednost 0)
\u003d IF (A1, "Premašen proračun"; "U redu!")
će se vratiti U REDU!

Ako je u ćeliji A1 nađe se bilo koji broj koji nije 0, tada će se formula vratiti Proračun premašen... Ovaj je pristup prikladan za ispitivanje jednakosti vrijednosti nuli.

Povezivanje funkcije IF () s drugim funkcijama pomoću uvjeta

EXCEL također sadrži i druge funkcije koje možete koristiti za analizu podataka pomoću uvjeta. Na primjer, funkcija COUNTIF () koristi se za brojanje broja pojavljivanja brojeva u rasponu ćelija, a funkcija SUMIF () koristi se za dodavanje vrijednosti koje ispunjavaju određene uvjete.

Funkcija IF (), također se može koristiti za brojanje i dodavanje vrijednosti pomoću uvjeta. Ilustrativni primjeri dati su u nastavku.

Neka podaci budu u rasponu A6: A11 (vidi primjer datoteke)


Izračunajmo zbroj vrijednosti veće od 10 pomoću funkcije SUMIF () pisanjem \u003d SUMIF (A6: A11, "\u003e 10")... Sličan rezultat (23) može se dobiti pomoću
\u003d SUM (AKO (A6: A11\u003e 10, A6: A11))
(za unos formule u ćeliju umjesto UNESI treba pritisnuti CTRL + SHIFT + ENTER)

Sada izbrojimo broj pojavljivanja brojeva većih od 10 u rasponu stanica A6: A11 \u003d COUNTIF (A6: A11, "\u003e 10")... Sličan rezultat (2) može se dobiti pomoću
\u003d COUNT (AKO (A6: A11\u003e 10, A6: A11))

Sada kada je princip jasan, druge formule s uvjetima mogu se konstruirati pomoću funkcije IF (). Na primjer, pronalaženje minimalne vrijednosti među brojevima većim od 10:
\u003d MIN (AKO (A6: A11\u003e 10, A6: A11))



Preporučeno za čitanje