Määra Exceli tingimus. Kuidas excelis kasutada pesastatud funktsioone, kui () arvutada müügiboonus

IF () funktsioon , IF () ingliskeelne versioon, kasutatakse tingimuste kontrollimisel. Näiteks, \u003d IF (A1\u003e 100; "Eelarve on ületatud"; "OK!")... Sõltuvalt lahtris olevast väärtusest A1 valemi tulemuseks on kas "Eelarve on ületatud" või "OK!"

Funktsioon IF () on üks sagedamini kasutatavaid funktsioone.

Funktsioonide süntaks

IF (boolean_test, väärtus_if_tõde, [väärtus_if_false])

Logi_väljend - mis tahes väärtus või väljend, mis võib olla TÕENE või VALE.
\u003d KUI (A1\u003e \u003d 100; "Eelarve on ületatud"; "OK!")
Need. kui kambris A1 sisaldab väärtust, mis on suurem või võrdne 100, siis valem naaseb OKEI!,ja kui ei, siis Eelarve on ületatud.

Valemeid saab kasutada funktsiooni argumentidena, näiteks:
\u003d IF (A1\u003e 100, SUM (B1: B10), SUM (C1: C10))
Need. kui kambris A1 sisaldab väärtust\u003e 100, siis summeeritakse veeru kohal B ja kui vähem, siis veergude kaupa KOOS .

Pesastatud IF

EXCEL 2007 argumendiväärtustena väärtus_tõene ja väärtus_if_false keerukamate kontrollide loomiseks võite kasutada kuni 64 sisestatud IF () funktsiooni.
\u003d IF (A1\u003e \u003d 100; "Eelarve ületatud"; IF (A1\u003e \u003d 90; "Suur projekt"; IF (A1\u003e \u003d 50; "Keskmine projekt"; "Väike projekt"))

VAATA (A1; (0; 50; 90; 100); ("Väike projekt"; "Keskmine projekt"; "Suur projekt"; "Eelarve ületatud"))

VAATAMINE (A1; A3: B6; 2)

Funktsiooni VLOOKUP () jaoks peate looma vahemikus A3: B6 väärtuste tabel:

Kui soovite kuvada erinevat teksti, kui see on lahtris A1 negatiivne väärtus, positiivne väärtus või 0, siis võite kirjutada järgmise valemi:

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

või kui soovite tekstiväärtuste asemel kuvada valemeid, võite kasutada viiteid lahtrivahemikule (mis sisaldab valemeid)

VAATAMINE (A24; (- 1E + 307; 0; 1E-307); A27: A29) (vt näidisfaili)

Välja jäetud kolmas argument [väärtus_if_false]

Funktsiooni kolmas argument on valikuline; selle väljajätmisel tagastab funktsioon FALSE (kui tingimus ei ole täidetud).
\u003d IF (A1\u003e 100; "Eelarve on ületatud")
Kui kambris A1 sisaldab väärtust 1, tagastab ülaltoodud valem vale.

Esimeses argumendis sisestatakse TRUE või FALSE asemel number

Sest väärtus FALSE on võrdne 0-ga, siis valemid
\u003d IF (0, "Eelarve on ületatud"; "OK!")
või (kui lahtris A1 sisaldab väärtust 0)
\u003d KUI (A1, "Eelarve ületatud"; "OK!")
naaseb OKEI!

Kui kambris A1 on mis tahes muu arv kui 0, siis valem naaseb Eelarve on ületatud... See lähenemine on mugav, kui testitakse väärtuse võrdsust nulliga.

Funktsiooni IF () linkimine tingimuste abil teiste funktsioonidega

EXCEL sisaldab ka muid funktsioone, mida saate tingimuste abil andmete analüüsimiseks kasutada. Näiteks funktsiooni COUNTIF () kasutatakse arvude esinemiste arvu loendamiseks lahtrivahemikus ja funktsiooni SUMIF () lisatakse teatud tingimustele vastavad väärtused.

Funktsiooni IF () võib alternatiivina kasutada ka tingimuste abil väärtuste lugemiseks ja lisamiseks. Allpool on toodud illustreerivad näited.

Andmed olgu vahemikus A6: A11 (vt näidisfaili)


Arvutame funktsiooni SUMIF () abil kirjutamise teel väärtuste summa, mis on suurem kui 10 \u003d SUMIF (A6: A11, "\u003e 10")... Sarnase tulemuse (23) võib saada kasutades
\u003d SUMMA (IF (A6: A11\u003e 10, A6: A11))
(valemi sisestamiseks lahtrisse SISENEMA vaja vajutada CTRL + SHIFT + ENTER)

Nüüd loeme lahtrite vahemikus arvude, mis on suuremad kui 10, esinemiste arvu A6: A11 \u003d COUNTIF (A6: A11, "\u003e 10")... Sarnase tulemuse (2) võib saada kasutades
\u003d ARV (kui (A6: A11\u003e 10, A6: A11))

Nüüd, kui põhimõte on selge, saab funktsiooni IF () abil koostada muid tingimustega valemeid. Näiteks leitakse minimaalse väärtuse leidmine arvude vahel, mis on suuremad kui 10:
\u003d MIN (kui (A6: A11\u003e 10, A6: A11))

Võime rakke loogiliselt kontrollida on võimas tööriist. Leiate lõputult kasutamiseks KUI () oma igapäevastes dokumentides. Probleem on selles, et sageli ületavad vajalikud kontrollid lihtsa funktsiooni võimalusi. KUI () ... See juhtub siis, kui enne tulemuse aktsepteerimist on vaja kontrollida kahte või enamat tingimust.

Selliste juhtumite jaoks pakub Excel mitmeid võimalusi: kasuta KUI () teise sees KUI (), funktsioonid JA () ja IL JA ()... Vaatleme neid meetodeid allpool.

Kasutamine KUI () teise funktsiooni sees KUI ()

Vaatleme varianti, mis põhineb varem uuritud funktsioonil \u003d IF (A1\u003e 1000; "palju"; "vähe"). Mis siis, kui peate printima teise stringi, kui number A1-s on näiteks suurem kui 10 000? Teisisõnu, kui A1\u003e 1000 on tõene, soovite käivitada veel ühe kontrolli ja vaadata, kas A1\u003e 10000 vastab tõele. Sellise variandi saate luua teise funktsiooni rakendamisega KUI () esimese argumendina väärtus _if_true: \u003d IF (A1\u003e 1000; IF (A1\u003e 10000; "väga palju"; "palju"); "vähe").

Kui A1\u003e 1000 on tõene, algab teine \u200b\u200bfunktsioon KUI (), mis tagastab väärtuse "väga palju", kui A1\u003e 10000. Kui A1 on siiski väiksem kui 10 000 või sellega võrdne, tagastatakse väärtus "palju". Kui esimese kontrollimise ajal on A1 number väiksem kui 1000, kuvatakse väärtus "väike".

Pange tähele, et sama hästi saate käivitada ka teise kontrolli, kui esimene on vale (see tähendab ifo funktsiooni argumendis value_if_false). Siin on väike näide, mis tagastab väärtuse "väga väike", kui arv A1-s on väiksem kui 100: \u003d IF (A1\u003e 1000; "palju"; IF (A1<100;"очень мало"; "мало")) .

Müügiboonuse arvutamine

Hea näide ühe tšeki kasutamisest teise sees on töötajate müügipreemia arvutamine. kes töötab klubis - hotell Heliopark Thalasso, Zvenigorod. Sel juhul, kui väärtus on X, soovite ühte tulemust, kui Y on teine, kui Z
- kolmas. Näiteks edukaks müümiseks boonuse arvutamisel on võimalik kolm võimalust:

  1. Müüja ei ole jõudnud kavandatud väärtuseni, boonus on 0.
  2. Müüja ületas kavandatud väärtust vähem kui 10%, preemia on 1000 rubla.
  3. Müüja ületas kavandatud väärtust rohkem kui 10%, boonus võrdub 10 000 rubla.

Siin on valem sellise näite arvutamiseks: \u003d IF (E3\u003e 0; IF (E3\u003e 0,1; 10000; 1000); 0). Kui E3 väärtus on negatiivne, tagastatakse 0 (boonust pole). Kui tulemus on positiivne, kontrollitakse, kas see on suurem kui 10%, ja sõltuvalt sellest väljastatakse 1000 või 10 000. 4.17 näitab valemi toimimise näidet.

AND () funktsioon

Sageli tuleb kontrollida ainult kahe tingimuse truudust üheaegselt. Näiteks makstakse boonust alles siis, kui teatud toote müük on plaani ületanud ja ka kogumüük on plaani ületanud. Kui üks neist tingimustest pole täidetud (või mõlemad tingimused), ei maksta preemiat. Boole'i \u200b\u200bloogikas nimetatakse seda loogiliseks JA, kuna mõlemad avaldised peavad olema tõesed, et kogu avaldis oleks tõene.

Excelis boolean väljendid JA töödeldakse funktsiooni abil JA (): AND (boolean_value1; boolean_value2; ...). Iga argument on tõeväärtus, mida tuleb testida. Võite sisestada nii palju argumente kui soovite.

Pange tähele, kuidas funktsioon töötab:

  • Kui kõik avaldised tagastavad TÕENE (või mis tahes positiivse arvu), JA () tagastab TÕENE.
  • Kui üks või mitu argumenti tagastab FALSE (või 0), JA () tagastab FALSE.

Tihedamini JA () rakendatakse funktsiooni sees KUI ()... Sel juhul, kui kõik argumendid on sees JA () tagastab funktsiooni TRUE KUI () järgib selle haru väärtust, kui see on tõsi. Kui üks või mitu avaldist JA () tagastab funktsiooni FALSE KUI () järgib haru väärtus_if_false.

Siin on väike näide: \u003d IF (AND (C2\u003e 0; B2\u003e 0); 1000; "boonust pole"). Kui B2 väärtus on suurem kui null ja C2 väärtus on suurem kui , tagastab valem 1000, vastasel juhul kuvatakse rida "boonust pole".

Väärtuste eraldamine kategooriatesse

Funktsiooni ja () kasulikuks otstarbeks on kategoriseerimine väärtuse järgi. Näiteks on teil küsitluse või hääletuse tulemustega tabel ja soovite jagada kõik hääled kategooriatesse järgmiste vanusevahemike järgi: 18-34,35-49, 50-64,65 või rohkem. Eeldades, et vastaja vanus on lahtris B9, teostavad järgmised funktsiooni argumendid ja () teostavad vahemikku kuuluva vanuse loogilise kontrolli: \u003d AND (B9\u003e \u003d 18; B9


Kui isiku vastus on lahtris C9, väljastab järgmine valem inimese hääletustulemuse, kui kontroll käivitatakse vanuserühma 18–34 täitmiseks: \u003d KUI (JA (B9\u003e \u003d 18; B9)

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

VÕI () funktsioon

On olukordi, kus otsust vajate ainult siis, kui üks tingimustest on positiivne (TÕENE). Näiteks võite otsustada maksta töötajatele lisatasu, kui kogumüük ületab eesmärki või kui üksiku toote müük on prognoositust kõrgem. Loogika loogikas nimetatakse seda booleaniks VÕI.

Selliseid tingimusi kontrollitakse Excelis funktsiooni abil VÕI (): VÕI (boolean1, boolean2, ...). Iga argument on tõeväärtus, mida tuleb testida. Võite sisestada nii palju argumente kui soovite. Töö tulemus VÕI () sõltub järgmistest tingimustest:

  • Kui üks või mitu argumenti tagastab TÕENE (mis tahes positiivne arv), VÕI () tagastab TÕENE.
  • Kui kõik argumendid tagastavad FALSE (null), on tulemus VÕI () saab FALSE.

Sama hästi kui JA (), kõige sagedamini funktsioon VÕI () kasutatud sisekontrollis KUI ()... Sel juhul, kui üks argumentidest sees VÕI () tagastab funktsiooni TRUE KUI () järgib oma haru väärtus_tõene_tõsi. Kui kõik väljendid on VÕI () tagastab funktsiooni FALSE KUI () järgib haru väärtus_if_false... Siin on väike näide: \u003d IF (OR (C2\u003e 0; B2\u003e 0); 1000; "boonust pole").

Kui ühes lahtris on positiivne arv (C2 või B2), tagastab funktsioon 1000. Ainult siis, kui mõlemad väärtused on negatiivsed (või võrdsed nulliga), tagastab funktsioon stringi "no bonus".

See artikkel keskendub Exceli arvutustabeli redaktori funktsioonile "Kui". See käsitleb rakenduse loogilisi võimalusi. Selle funktsiooni võib omistada kõige tavalisemale, mida kasutatakse sageli töö ajal.


Põhijooned

IN exceli programm valem "Kui" võimaldab mitmesuguseid ülesandeid, mis nõuavad teatud väärtuste võrdlemist ja tulemuse saamiseks. See lahendus võimaldab kasutada hargnemisalgoritme, luua otsustuspuu.

Rakenduse näited

Funktsioon näeb välja: \u003d IF (ülesanne; tõene; vale). Esimene osa on tõeväärtus. See võib ilmuda fraasi või numbrina. Näiteks "10" või "ilma käibemaksuta". See parameeter tuleb täita. Tõene on tulemusena kuvatav väärtus, kui avaldis on tõene. Väär tähistab andmeid, mis tagastatakse, kui ülesanne on vale.

Kahe lahtri parameetrite võrdsus

Exceli funktsiooni If võimaluste paremaks mõistmiseks peate esitama näited. Seetõttu tasub edasi neid kaaluma hakata. Lahtrisse C1 peate sisestama väärtuse 8. Pärast seda peate aadressi D1 väljale sisestama järgmise valemi: \u003d IF (C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Võib kaaluda veel ühte näidet. Eksamiks teenitakse mitu õpilast ja nende hinnet. Andmed on järgmised: 5, 4, samuti 3 ja 2. Vastavalt ülesande tingimustele on kavas luua igale õpilasele tekstikommentaar "läbinud" või "mitte läbitud". Seega, kui õpilane saab hindeks kolm või kõrgem, loetakse ta eksami sooritanuks. Kui tema hinne on alla 3, pole õpilasel hästi läinud. Sellise probleemi lahendamiseks kirjutage järgmine valem: \u003d IF (C1<3; «не справился»; «сдал»).

Programm alustab iga õpilase tulemuste võrdlemist. Kui indikaator on väiksem kui kolm, ilmub vastavasse lahtrisse kiri "nurjus". Kui punkt on 3 või kõrgem, näete nõutavas veerus kommentaari, et õpilane ei sooritanud eksamit. Väärib märkimist, et tekstikommentaarid peaksid alati olema jutumärkides. Selle funktsiooni kasutamisel saate kasutada järgmisi võrdlusoperaatoreid:< >, =, >, <, >=, <=.

Näited tingimuste "OR", "AND" kasutamisest

On vaja täiendavalt kaaluda selle rakenduse loogilisi võimalusi. If-funktsiooni on võimalik kombineerida võrdlusoperaatoritega. Need on järgmised parameetrid:

"OR";
"JA".

Excelis on vaja märkida oluline tingimus: kui õpilase hinne on võrdne või alla 5, kuid üle 3. Sel juhul peaks kuvama kommentaari: "läbib" või "ei". Seega läbivad ainult need õpilased, kes on teeninud viis ja neli. Selle ülesande kirjutamiseks arvutustabeli redaktorisse peaksite rakendama spetsiaalset valemit. See näeb välja selline: \u003d IF (AND (A1<=5; A1>3); "Läbib", "ei").

Kui kaalute keerulisemat näidet, peate kasutama "OR" või "AND". Seega saate Excelis valemi rakendamisega tutvuda, kui tööl on mitu tingimust. Näiteks: \u003d IF (OR (A1 \u003d 5; A1 \u003d 10); 100; 0). Sel juhul võime järeldada, et kui lahtris A1 väärtus on 5 või 10, kuvab programm tulemuse 100. Vastasel juhul on see 0. Nende operaatorite abil on võimalik leida lahendus keerukamatele probleemidele.

Näiteks peate andmebaasis arvutama võlgnikud, kes peavad maksma rohkem kui 10 000 rubla. Võite seada tingimuse, et laenu pole tagasi makstud kauem kui kuus kuud, see tähendab kuus kuud. Tänu Exceli arvutustabeliredaktori funktsioonile "Kui" on võimalik automaatselt saada vastavate nimede juurde märk "probleemiklient". Oletame, et lahter A1 sisaldab andmeid, mis näitavad võla tähtaega (kuud). Väljal B1 on summa.

Sellisel juhul esitatakse valem järgmisel kujul: \u003d IF (AND (A1\u003e \u003d 6; B1\u003e 10000); "probleemne klient"; ""). Seega, kui tuvastatakse isik, kes vastab kindlaksmääratud tingimustele, kuvab programm tema nime vastas vastava kommentaari. Teiste loendi liikmete jaoks jääb see lahter tühjaks.

Näete ka näidet, kui olukord on kriitiline. Tuleb sisestada asjakohane kommentaar. Selle tulemusel on valem järgmine: \u003d IF (OR (A1\u003e \u003d 6; B1\u003e 10000); "kriitiline olukord"; ""). Kui programm tuvastab aga vähemalt ühe parameetri vaste, kuvatakse vastav märge.

Väljakutseid esitavad ülesanded

Exceli funktsiooni "Kui" kasutatakse sisseehitatud nullvigade jagamise vältimiseks. Lisaks kasutatakse seda veel mitmes olukorras. Esimene juhtum tähistatakse kui "DIV / 0". Seda võib leida üsna tihti. See juhtub tavaliselt siis, kui valem "A / B" nõuab kopeerimist. Samal ajal on indikaator B sisse üksikud rakud on 0. Sellise olukorra vältimiseks tasub kasutada kõnealuse operaatori võimalusi. Seega on nõutav valem: \u003d IF (B1 \u003d 0; 0; A1 / B1). Niisiis, kui lahter B1 on täidetud väärtusega "null", kuvab redaktor "0". Vastasel juhul jagab programm A1 indikaatori B1 andmetega ja annab vajaliku tulemuse.

Allahindlus

Nagu näitab praktika, tekivad sageli olukorrad, mida arutatakse allpool. Peate arvutama allahindlusi konkreetse eseme ostmiseks kulutatud kogusumma põhjal. Kasutatav maatriks on järgmine: vähem kui 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; rohkem kui 5001 - 7%. Näete olukorda, kui Excelis on külastajate andmebaas ja teave ostuks kulutatud summa kohta. Järgmine samm on arvutada allahindlus igale kliendile. Selleks peate rakendama järgmise avaldise: \u003d IF (A1\u003e \u003d 5001; B1 * 0,93; IF (A1\u003e \u003d 3001; B1 * 0,95; ..).

Süsteem kontrollib kogu ostusummat. Kui see ületab 5001 rubla väärtuse, korrutatakse toode 93 protsendiga eseme hinnast. 3001 ühiku piiri ületamise korral toimub sarnane tegevus, kuid arvesse võetakse juba 95%.



Soovitame lugeda