Funkcija IF — ligzdotās formulas kļūmju novēršana

Funkcija IF — ligzdotās formulas kļūmju novēršana

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).

Salikts ligzdots IF priekšraksts — formula šūnā E2 ir =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"))))))))))))
  • =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.

  1. Ja pārbaudes darbā iegūts vairāk par 89 punktiem (šūna D2), skolēns saņem atzīmi A.

  2. Ja pārbaudes darbā iegūts vairāk par 79 punktiem, skolēns saņem atzīmi B.

  3. Ja pārbaudes darbā iegūts vairāk par 69 punktiem, skolēns saņem atzīmi C.

  4. Ja pārbaudes darbā iegūts vairāk par 59 punktiem, skolēns saņem atzīmi D.

  5. 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.

Formula šūnā D9 ir IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
  • =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:

Formula šūnā D9 nav pareiza: =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))

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:

Formula šūnā D2 ir =VLOOKUP(C2,C5:D17,2,TRUE)
  • =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.

Formula šūnā C9 ir =VLOOKUP(B9,B2:C6,2,TRUE)
  • =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.

Šeitdaudz detalizētāk ir apskatīts VLOOKUP, taču tas ir pārliecināts, ka ir daudz vienkāršāks par 12 līmeņu, sarežģītāku 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 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 Office 365 abonentiem. Ja esat Office 365 abonents, pārliecinieties, vai lietojat jaunāko Office versiju.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Saistītās tēmas

Video: papildu IF funkcijas
funkcija IFS (Office 365, Excel 2016 un jaunākas versijas)
funkcija COUNTIF saskaita vērtības, kas atbilst vienam kritērijam
funkcija COUNTIFS saskaita vērtības, pamatojoties uz vairākiem kritērijiem
.Funkcija SUMIF summē vērtības, pamatojoties uz vienu kritēriju
, funkcija SUMIFS summē vērtības, pamatojoties uz vairākiem kritērijiem
un funkciju
vai funkciju

Funkcijas VLOOKUPpārskats par formulām programmā Excel
, kā izvairīties no bojātām formulām
,nosaka kļūdas formulās
loģiskās funkcijas
Excel funkcijas (alfabēta secībā)
Excel funkcijas (pēc kategorijas)

Piezīme.:  Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai jūs varat mūs informēt, vai informācija bija noderīga? Šeit ir raksts angļu valodā jūsu atsaucei.​

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×