Funkcija IF ļauj loģiski salīdzināt faktisko un paredzēto vērtību, pārbaudot nosacījumu un atgriežot rezultātu, ja tas ir patiess vai aplams.
-
=IF(ja kāda vērtība ir patiesa, veikt noteiktu darbību, pretējā gadījumā darīt kaut ko citu)
Tātad IF priekšrakstam iespējami divi rezultāti. Pirmais rezultāts parādās, ja salīdzinājums ir patiess, bet otrais rezultāts parādās, ja salīdzinājums ir aplams.
IF priekšraksti ir neticami jaudīgi, un tie ir pamatā daudziem izklājlapu modeļiem, bet tie arī ir dažādu izklājlapu problēmu cēlonis. Ideālā gadījumā IF priekšraksts būtu jālieto minimāliem nosacījumiem, piemēram, Vīrietis/sieviete, Jā/nē/iespējams, bet dažreiz ir nepieciešams novērtēt sarežģītākus scenārijus, kur nepieciešams kopā ligzdot* vairāk nekā 3 funkcijas IF.
* “Ligzdot” nozīmē apvienot vairākas funkcijas vienā formulā.
Izmantojiet IF funkciju, vienu no loģiskajām funkcijām, lai atgrieztu vienu vērtību, ja nosacījums ir patiess, un citu vērtību, ja tas ir aplams.
Sintakse
IF(loģiskais_tests, [vērtība_ja_true], [vērtība_ja_false])
Piemērs.
-
=IF(A2>B2;"Neiekļaujas budžetā";"Labi")
-
=IF(A2=B2,B4-A4,"")
|
Argumenta nosaukums |
Apraksts |
|
loģiskais_tests (obligāts) |
Nosacījums, ko vēlaties pārbaudīt. |
|
vērtība_ja_patiess (obligāts) |
Vērtība, kas ir jāatgriež, ja loģiskā_testa rezultāts ir TRUE. |
|
vērtība_ja_aplams (neobligāts) |
Vērtība, kas ir jāatgriež, ja loģiskā_testa rezultāts ir FALSE. |
Piezīmes
Lai gan Excel atļauj ligzdot līdz pat 64 dažādām funkcijām IF, tā rīkoties nav ieteicams. Kāpēc?
-
Ir rūpīgi jāapsver, kā pareizi izveidot vairākus IF priekšrakstus un nodrošināt, ka to loģika veic pareizus aprēķinus katram nosacījumam. Ja neligzdojat formulu 100% precīzi, tā var darboties 75% gadījumu, taču 25% gadījumu atgriezt neparedzētus rezultātus. Diemžēl iespēja, ka iekļūsit šajos 25%, ir gana liela.
-
Uzturēt vairākus IF priekšrakstus var būt ļoti sarežģīti, īpaši, ja atgriežaties pie formulas vēlāk un mēģināt saprast, ko jūs vai kāds cits ir mēģinājis ar to paveikt.
Ja jums ir IF priekšraksts, kurš kļūst aizvien garāks un kura beigas nav paredzamas, ir laiks atlaist peli un pārdomāt savu stratēģiju.
Noskaidrosim, kā pareizi izveidot sarežģītu ligzdotu IF priekšrakstu, izmantojot vairākas funkcijas IF, un kā saprast, ka tomēr jāizmanto kāda cita Excel iespēja.
Piemēri
Tālāk ir sniegts salīdzinoši standartizēta IF priekšraksta piemērs; tas pārvērš skolēnu pārbaudes darbā iegūtos punktus par atzīmēm (izsakot tās ar burtiem).
-
=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))
Šajā sarežģītajā ligzdotajā IF priekšrakstā ir ievērota vienkārša loģika.
-
Ja pārbaudes darbā iegūts vairāk par 89 punktiem (šūna D2), skolēns saņem atzīmi A.
-
Ja pārbaudes darbā iegūts vairāk par 79 punktiem, skolēns saņem atzīmi B.
-
Ja pārbaudes darbā iegūts vairāk par 69 punktiem, skolēns saņem atzīmi C.
-
Ja pārbaudes darbā iegūts vairāk par 59 punktiem, skolēns saņem atzīmi D.
-
Pārējos gadījumos skolēns saņem atzīmi F.
Šajā piemērā parādīta diezgan droša formula, jo korelācija starp pārbaudes darbā iegūtajiem punktiem un atzīmēm, visticamāk, nemainīsies, tāpēc nebūs jāveic uzturēšana. Bet — ko darīt, ja atzīmes jāprecizē kā A+, A un A- (utt.)? Tad IF priekšraksts, kurā bija četri nosacījumi, ir jāpārraksta tā, lai tajā būtu 12 nosacījumi! Tagad formula izskatītos šādi:
-
=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
Tā joprojām ir funkcionāli precīza un darbojas, kā paredzēts, bet ir nepieciešams daudz laika, lai to uzrakstītu, un vēl vairāk laika, lai pārbaudītu, vai tā darbojas pareizi. Vēl viena problēma ir tā, ka punkti un atbilstošās atzīmes bija jāieraksta pašrocīgi. Cik liela ir iespēja pieļaut pareizrakstības kļūdu? Tagad iedomājieties, ka tas jāizdara 64 reizes, veidojot vēl sarežģītākus nosacījumus! Protams, to var paveikt, bet vai tiešām ir vērts pielikt tādas pūles, ņemot vērā, ka varat nejauši pieļaut grūti atrodamas kļūdas?
Padoms.: Katrai Excel funkcijai ir nepieciešamas atverošās un aizverošās iekavas (). Excel palīdz saprast, kas kur jāraksta, formulas rediģēšanas laikā iekrāsojot dažādās tās daļas. Piemēram, ja jums jārediģē iepriekšējā formula, tad, pārvietojot kursoru pāri katrai aizverošajai iekavai “)”, tai atbilstošā atverošā iekava tiek iekrāsota tādā pašā krāsā. Tas var būt īpaši noderīgi, strādājot ar sarežģītām ligzdotām formulām, kad mēģināt saprast, vai esat ierakstījis pietiekami daudz iekavu pāru.
Papildu piemēri
Tālāk piedāvājam bieži izmantotas formulas piemēru: komisijas maksas aprēķinās, balstoties uz gūtajiem ieņēmumiem.
-
=IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
Šī formula nosaka: IF(ja C9 vērtība ir lielāka par 15 000, atgriezt 20%, IF(ja C9 vērība ir lielāka par 12 500, atgriezt 17,5% u.t.t.
Kaut arī šis piemērs ir diezgan līdzīgs iepriekšējam piemēram par atzīmēm, tā lieliski parāda, cik sarežģīti var būt uzturēt lielus IF priekšrakstus — kas būtu jāveic, ja organizācija nolemtu pievienot jaunus kompensācijas līmeņus vai pat izmainīt esošās naudas vienību vai procentu vērtības? Jums būtu ļoti daudz darba!
Padoms.: Formulu joslā var ievietot rindiņu pārtraukumus, lai formulas būtu ērtāk lasāmas. Vienkārši nospiediet taustiņus ALT+ENTER pirms teksta, kuru vēlaties pārnest jaunā rindā.
Tālāk piedāvājam komisijas maksas scenārija piemēru, kurā loģika nav pareiza:
Vai redzat, kas nav pareizi? Salīdziniet ieņēmumu salīdzinājumu secību ar iepriekšējo piemēru. Kādā secībā tie ir sarindoti? Tieši tā, no mazākā uz lielāko (no 5000 EUR līdz 15 000 EUR), nevis pretēji. Bet kāpēc tas tik svarīgi? Tas ir svarīgi tāpēc, ka formula netiek tālāk par pirmo novērtējumu, ja vērtība pārsniedz 5000 EUR. Pieņemsim, ka ieņēmumi ir 12 500 EUR, bet IF priekšraksts atgriezīs 10%, jo šī vērtība ir lielāka par 5000, un šeit salīdzināšana tiek pārtraukta. Tas var sagādāt daudz problēmu, jo daudzās situācijās šādas kļūdas paliek nepamanītas līdz brīdim, kad tām jau ir negatīvas sekas. Zinot, ka ar sarežģītiem ligzdotiem IF priekšrakstiem var būt dažādas nopietnas problēmas, kā jums rīkoties? Vairākumā gadījumā sarežģītas formulas ar funkciju IF vietā varat izmantot funkciju VLOOKUP. Izmantojot funkciju VLOOKUP, vispirms ir jāizveido atsauces tabula:
-
=VLOOKUP(C2,C5:D17,2,TRUE)
Šī formula nosaka, ka diapazonā C5:C17 ir jāmeklē šūnas C2 vērtība. Ja vērtība tiek atrasta, tiek atgriezta atbilstoša vērtība no tās pašas rindas kolonnas D.
-
=VLOOKUP(B9,B2:C6,2,TRUE)
Savukārt šī formula meklē šūnas B9 vērtību diapazonā B2:B22. Ja vērtība tiek atrasta, tiek atgriezta atbilstoša vērtība no tās pašas rindas kolonnas C.
Piezīme.: Abas šīs funkcijas VLOOKUP formulas beigās izmanto argumentu TRUE; tas nozīmē, ka ir jāmeklē precīza atbilstība. Citiem vārdiem sakot, tā uzmeklēšanas tabulā meklē precīzas vērtības, kā arī vērtības starp tām. Šajā gadījumā uzmeklēšanas tabulai ir jābūt sakārtotai augošā secībā — no mazākās vērtības līdz lielākajai.
Funkcija VLOOKUP šeit ir daudz detalizētāka , taču tā ir daudz vienkāršāka par sarežģītu 12 līmeņu ligzdotu IF priekšrakstu! Tai ir arī citas priekšrocības, kas nav tik acīmredzamas.
-
VLOOKUP atsauces tabulas ir skaidri un viegli pārskatāmas.
-
Tabulas vērtības var viegli atjaunināt, un, mainoties nosacījumiem, nav nepieciešams mainīt pašu formulu.
-
Ja nevēlaties, lai citi redzētu atsauces tabulu un nevarētu tajā veikt izmaiņas, ievietojiet to citā darblapā.
Vai zinājāt?
Tagad ir pieejama funkcija IFS, kas var aizstāt vairākus ligzdotus IF priekšrakstus ar vienu funkciju. Tātad, sākotnējais piemērs par atzīmēm, kurā ir 4 ligzdotas funkcijas IF:
-
=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))
Tagad to var padarīt daudz vienkāršāku, izmantojot funkciju IFS:
-
=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")
Funkcija IFS ir lieliska, jo nav jāuztraucas par visiem IF priekšrakstiem un iekavām.
Piezīme.: Šis līdzeklis ir pieejams tikai tad, ja jums ir Microsoft 365 abonements. Ja esat Microsoft 365 abonents, pārliecinieties, vai jums ir jaunākā Office versija.Iegādājieties vai izmēģiniet Microsoft 365
Vai nepieciešama papildu palīdzība?
Vienmēr varat pajautāt speciālistam Excel tech kopienā vai saņemt atbalstu kopienās.
Saistītās tēmas
Video: Papildu IF funkcijas IFS (Microsoft 365, Excel 2016 un jaunākas versijas)Funkcija COUNTIF saskaitīt vērtības, pamatojoties uz vienu kritēriju. Funkcija COUNTIFS saskaitīt vērtības, kas atbilst vairākiem kritērijiem FunkcijaSUMIF summē vērtības, pamatojoties uz vienu kritēriju. Funkcija SUMIFS summē vērtības, pamatojoties uz vairākiem kritērijiem un funkcijuORfunkcijuVLOOKUP Pārskats par formulām programmā Excel Kā nepieļautkļūdatīvas formulasKļūdu noteikšanaformulās Loģiskās funkcijas Excel funkcijas (alfabētasecībā)Excel funkcijas (pēc kategorijas)
