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

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

Svarīgi! :  Šis raksts ir mašīntulkots, skatiet atrunu. Lūdzu, skatiet šī raksta versiju angļu valodā šeit jūsu informācijai.

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)

Lai IF priekšrakstu var būt divi rezultātus. Pirmais rezultāts ir, ja jūsu salīdzinājums ir True, otrā, ja jūsu salīdzinājums ir False.

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

Kamēr Excel ļaus ligzdot līdz 64 citā IF funkcijas, to nemaz nav ieteicams to darīt. kāpēc?

  • Vairāku IF lietošana nepieciešama liela doma veidot pareizi un pārliecinieties, vai to loģika var aprēķināt pareizi caur katram nosacījumam, līdz beigām. Ja jums nav precīzi ligzdot formulas 100 %, tad tas varētu strādāt 75 % no laika, bet atgriezt neparedzētus rezultātus 25 % no laika. Diemžēl jūsu nozvejojot 25 % likmes ir slaids.

  • Vairāku IF lietošana var kļūt neticami grūti saglabāt, īpaši tad, kad atgriezties pēc kāda laika un mēģiniet skaitlis, ko jūs vai vissliktākais kādam citam, mēģināja darīt.

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

Tas ir joprojām funkcionāli precīzu un darbotos, kā paredzēts, bet tas aizņem daudz laika, lai rakstītu un ilgāk, lai pārliecinātos, vai tas, ko vēlaties pārbaudīt. Citu būtisko problēma ir, ka jūs esat bija ievadīt rādītāji un ekvivalentu burtu kategorijas ar roku. Kas ir likmes, ka jums būs nejauši pārrakstīšanās kļūdu? Tagad iedomājieties, mēģinot veikt šo 64 reizes ar sarežģītākiem nosacījumi! Protams, tas ir iespējams, bet vai tiešām vēlaties tēma sevi šāda veida piepūli un iespējamais kļūdas, kas būs patiešām grūti vietas?

Padoms : Katru funkciju programmā Excel ir nepieciešams sākuma un beigu iekavu (). Excel mēģinās, lai palīdzētu jums saprast, kas notiek, ja pēc krāsojumu dažādas daļas savu formulu, kad rediģējat to. Piemēram, ja vēlaties iepriekš formulas rediģēšana, kā jūs kursors tiek pārvietots garām katras beigu iekavas ")", tās atbilstošo atverošās iekavas kļūs viena krāsa. Tas var būt īpaši noderīgi ligzdotas sarežģītas formulas, kad mēģināt noskaidrot, ja jums ir pietiekami daudz atbilstošu iekavas.

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.

Kamēr tas ir ļoti līdzīga agrāk atzīmes. piemēram, šī formula ir lielisks piemērs, kā sarežģīta var saglabāt lielas IF lietošana — būtu norādītas darbības, kas jāveic, ja vēlaties pievienot jaunus kompensācija līmeņus, un, iespējams, pat mainīt jūsu organizācijā esošu dolāru vai procentuālās vērtības? Vai ir daudz darba, jūsu rokās!

Padoms : Formulu joslā, lai būtu vieglāk lasīt garš formulas, varat ievietot rindiņu pārtraukumi. Pirms tekstu, ko vēlaties aplauzt jaunā rindiņā, vienkārši, nospiediet taustiņu kombināciju ALT + ENTER.

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)

Līdzīgi šī formula meklē vērtību šūnā B9 B2:B22 diapazonā. Ja vērtība ir atrasta, tiek atgriezts atbilstošo vērtību no tās pašas rindas kolonnā C.

Piezīme : Abas šīs VLOOKUPs izmantojiet arguments TRUE formulas, kas nozīmē, ka mēs vēlamies, lai tās izskatītos approxiate match beigās. Citiem vārdiem sakot, tas atbilstu precīzas vērtības uzmeklēšanas tabulas, kā arī vērtības, kas atrodas starp tām. Šajā gadījumā uzmeklēšanas tabulas jākārto augošā secībā no mazākā līdz lielākajam.

VLOOKUP ir iekļauts daudz detalizēti šeit, bet pārliecināts, ka tas ir daudz vienkāršāk nekā 12 līmeņa un sarežģīts ligzdotās IF priekšrakstu! Pastāv arī citas mazāk redzama priekšrocības:

  • 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
kļūdu pārbaudes izmantošana, lai noteiktu kļūdas formulās
loģiskās funkcijas
Excel funkciju (saraksts pēc alfabēta)
Excel funkcijas (pēc kategorijas)

Piezīme : Mašīntulkošanas atruna. Šo rakstu ir tulkojusi datorsistēma bez cilvēka iejaukšanās. Microsoft piedāvā šos mašīntulkojumus, lai palīdzētu angliski nerunājošajiem lietotājiem izmanot saturu par Microsoft produktiem, pakalpojumiem un tehnoloģijām. Tā kā šis raksts ir mašīntulkots, tajā var būt leksikas, sintakses un gramatikas kļūdas.

Paplašiniet savas 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.

×