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

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

Piezīme.: Mēs vēlamies sniegt jums visjaunāko palīdzības saturu jūsu valodā, cik vien ātri to varam. Šī 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 lapas lejasdaļā varat mums pavēstīt, vai informācija jums noderēja? Ērtai atsaucei šeit ir šis raksts angļu valodā.

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.

Īpaši šajā piemērā ir relatīvi droša, tāpēc, ka tas nav iespējams, ka korelācijas starp testa rādītājus un tiks mainīts burtu kategorijas, tā nebūs nepieciešama daudz uzturēšanas. Bet šeit ir doma — kā rīkoties, ja vēlaties segmentā pakāpēm starp A + A un A - (u. tml.)? Tagad četras nosacījums IF priekšrakstu nepieciešams pārrakstīt ir 12 nosacījumiem! Lūk, kas formulai jāizskatās šim:

  • =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)))))

Jūs varat redzēt, kas nav kārtībā? Salīdziniet secības ieņēmumu salīdzināt iepriekšējā piemērā. Kāds paņēmiens ir šo vienu notiek? Tas ir labi, tas notiek no apakšas uz augšu ($5000 līdz $15000), nevis otrādi. Bet kāpēc tas ir tāds īpašs? Tas ir īpašs, jo formulu nevar iziet pirmais novērtējums jebkura vērtība vairāk nekā 5000 $. Pieņemsim, ka esat ieguvis $12500 ieņēmumu — IF priekšrakstu atgriezīs 10 %, jo tas ir lielāks par $5000, un tas tiek apturēts tur. Tas var būt neticami problemātisks, jo situācijām daudzās šāda veida kļūdas pamanīti līdz brīdim, kad tie ir bijis negatīvi ietekmēt. Lai zinot, ka ir dažas nopietni kļūmēm ar sarežģītas ligzdotās IF priekšrakstu, ko var darīt? Lielākajā daļā gadījumu, varat izmantot funkciju VLOOKUP nebūtu jāveido sarežģīta formula ar funkciju IF. Izmantojot funkciju VLOOKUP, vispirms jāizveido atsauces tabulu:

Formula šūnā D2 ir =VLOOKUP(C2,C5:D17,2,TRUE)
  • =VLOOKUP(C2,C5:D17,2,TRUE)

Šī formula saka meklējiet vērtību šūnā C2 C5:C17 diapazonā. Ja vērtība ir atrasta, tiek atgriezts atbilstošo vērtību no tās pašas rindas kolonnā 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.

Funkcija VLOOKUP daudz detalizētāk ir aprakstīta šeit; tā tiešām 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 tie funkcija , kas var aizstāt vairākus ligzdotu funkciju IF lietošana ar vienu funkciju. Tā vietā mūsu piemērā sākotnējo atzīmes, kas ir 4 ligzdotās IF funkcijas:

  • =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.

Izmēģiniet Office 365 vai jaunāko Excel 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
tie funkcija (Office 365, Excel 2016 un jaunākas versijas)
funkciju COUNTIF tiks saskaitītu vērtības, vadoties pēc vienam kritērijam
funkcija COUNTIFS tiks saskaitītu vērtības, vadoties pēc skaitlim, kas dalās bez atlikuma kritēriju
funkciju SUMIF tiks saskaitīt vērtības, pamatojoties uz vienu kritēriju
funkcija SUMIFS tiks saskaitīt vērtības, pamatojoties uz vairākiem kritērijiem
Funkcija AND
Funkcija OR
Funkcija VLOOKUP
pārskats par formulām programmā Excel
kā nepieļaut kļūdainas formulas
noteikt kļūdu labošana formulās
loģiskās funkcijas
Excel funkciju (saraksts pēc alfabēta)
Excel funkcijas (pēc kategorijas)

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 varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×