Excel

19 padomi ligzdotām IF formulām

19 Tips Nested If Formulas

The IF funkcija ir viena no visbiežāk izmantotajām funkcijām programmā Excel. IF ir vienkārša funkcija, un cilvēkiem patīk IF, jo tas dod viņiem iespēju izveidot Excel atbildēt kā informācija tiek ievadīta izklājlapā. Izmantojot IF, jūs varat atdzīvināt savu izklājlapu.



Bet viens IF bieži noved pie cita, un, apvienojot vairāk nekā pāris IF, jūsu formulas var sākt izskatīties kā mazi frankenšteini :)

Vai ligzdotie IF ir ļauni? Vai tie dažreiz ir nepieciešami? Kādas ir alternatīvas?





Lasiet tālāk, lai uzzinātu atbildes uz šiem un citiem jautājumiem ...

1. Pamata IF

Pirms mēs runājam par ligzdotu IF, ātri pārskatīsim IF pamatstruktūru:



 
= IF (test,[true],[false])

Funkcija IF veic testu un veic dažādas darbības atkarībā no tā, vai rezultāts ir patiess vai nepatiess.

Ņemiet vērā kvadrātiekavas ... tie nozīmē, ka argumenti nav obligāti. Tomēr jums ir jāpiegādā arī vērtība patiesai vai vērtība nepatiesai.

Lai ilustrētu, šeit mēs izmantojam IF, lai pārbaudītu punktus un aprēķinātu “Pass” vismaz 65 punktiem:

IF pamatfunkcija - atgriešanās

Piemēra šūnā D3 ir šāda formula:

 
= IF (C3>=65,'Pass')

To var nolasīt šādi: ja rādītājs C3 ir vismaz 65, atgrieziet “Pass”.

Tomēr ņemiet vērā, ka, ja rezultāts ir mazāk nekā 65, IF atgriež FALSE, jo mēs nepiegādājām vērtību, ja tā ir nepatiesa. Lai parādītu “Fail” nepārvaramiem rezultātiem, mēs varam pievienot “Fail” kā nepatiesu argumentu šādi:

 
= IF (C3>=65,'Pass','Fail')

IF pamatfunkcija - ar pievienotu vērtību false

Video: Kā izveidot loģiskas formulas .

2. Ko nozīmē ligzdošana

Ligzdošana vienkārši nozīmē formulas apvienot viena otrā, lai viena formula apstrādātu citas rezultātu. Piemēram, šeit ir formula, kurā funkcija TODAY ir ievietota MONTH funkcijā:

 
= MONTH ( TODAY ())

Funkcija TODAY atgriež pašreizējo datumu MONTH funkcijā. Funkcija MONTH ņem šo datumu un atgriež pašreizējo mēnesi. Pat vidēji sarežģītas formulas bieži izmanto ligzdošanu, tāpēc sarežģītākās formulās ligzdošanu redzēsit visur.

3. Vienkāršs ligzdots IF

Ligzdots IF ir tikai vēl divi IF paziņojumi formulā, kur viens IF priekšraksts parādās otrā.

Lai ilustrētu, zemāk es paplašināju iepriekšējo sākotnējās caurlaides/neveikšanas formulu, lai tā apstrādātu “nepabeigtus” rezultātus, pievienojot IF funkciju un ievietojot vienu IF otrā:

Pamata ligzdots IF

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

Vispirms darbojas ārējais IF un pārbauda, ​​vai C3 ir tukšs. Ja tā, ārējais IF atgriež “Nepabeigts”, un iekšējais IF nekad nedarbojas.

Ja rezultāts ir nav tukšs , ārējais IF atgriež FALSE un tiek palaista sākotnējā IF funkcija.

Uzziniet ligzdotos IF ar skaidrs, kodolīgs video treniņš .

4. Ligzdots IF svariem

Jūs bieži redzēsiet ligzdotus IF, kas ir iestatīti, lai apstrādātu “svarus” ... piemēram, lai piešķirtu pakāpes, piegādes izmaksas, nodokļu likmes vai citas vērtības, kas atšķiras no skalas ar ciparu ievadi. Kamēr skalā nav pārāk daudz līmeņu, ligzdotie IF šeit darbojas labi, taču formula ir jāsaglabā sakārtota, pretējā gadījumā to būs grūti lasīt.

Triks ir izlemt virzienu (no augsta līdz zemam vai no zemas līdz augstam), pēc tam atbilstoši strukturējiet nosacījumus. Piemēram, lai piešķirtu atzīmes secībā no “zems līdz augsts”, nākamajā tabulā varam attēlot vajadzīgo risinājumu. Ņemiet vērā, ka nav nosacījuma “A”, jo, tiklīdz esam pārvarējuši visus citus nosacījumus, mēs zinām, ka rādītājam jābūt lielākam par 95 un līdz ar to “A”.

Rezultāts Novērtējums Stāvoklis
0-63 F <64
64-72 D <73
73-84 C <85
85-94 B <95
95-100 TO

Skaidri saprotot nosacījumus, mēs varam ievadīt pirmo IF paziņojumu:

kas ir spēles funkcija excel
 
= IF (C5<64,'F')

Tas rūpējas par “F”. Tagad, lai apstrādātu “D”, mums jāpievieno vēl viens nosacījums:

 
= IF (C5<64,'F', IF (C5<73,'D'))

Ņemiet vērā, ka es vienkārši ievietoju citu IF pirmajā IF, lai iegūtu “nepatiesu” rezultātu. Lai paplašinātu formulu, lai apstrādātu “C”, mēs atkārtojam procesu:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

Mēs turpinām šo ceļu, līdz sasniedzam pēdējo klasi. Pēc tam tā vietā, lai pievienotu citu IF, vienkārši pievienojiet galīgo atzīmi par nepatiesu.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Šeit ir pēdējā ligzdotā IF formula darbībā:

Pabeigts ligzdotais IF piemērs atzīmju aprēķināšanai

Video: Kā izveidot ligzdotu IF, lai piešķirtu atzīmes

5. Ligzdotajiem IF ir loģiska plūsma

Daudzas formulas tiek atrisinātas no iekšpuses, jo vispirms ir jāatrisina “iekšējās” funkcijas vai izteiksmes, lai turpinātu pārējo formulu.

Ligzdotajiem IF ir sava loģiskā plūsma, jo “ārējie” IF darbojas kā vārti uz “iekšējiem” IF. Tas nozīmē, ka ārējo IF rezultāti nosaka, vai iekšējie IF pat darbojas. Zemāk redzamā diagramma vizualizēja iepriekšminētās pakāpes formulas loģisko plūsmu.

Ligzdota IF loģiskā plūsma

6. Izmantojiet Novērtēt, lai skatītos loģisko plūsmu

Operētājsistēmā Windows varat izmantot Novērtējiet funkciju skatīties, kā Excel soli pa solim atrisina jūsu formulas. Tas ir lielisks veids, kā “redzēt” sarežģītāku formulu loģisko plūsmu un novērst problēmas, ja viss nedarbojas, kā gaidījāt. Tālāk redzamajā ekrānā redzams, ka vērtēšanas logs ir atvērts un gatavs darbam. Katru reizi, noklikšķinot uz pogas Novērtēt, formulā tiek atrisināts nākamais solis. Novērtēšanu varat atrast lentes cilnē Formulas (Alt M, V).

Izmantojot funkciju Novērtēt, lai izietu caur ligzdotu IF, kas piešķir atzīmes

Diemžēl Excel Mac versija nesatur funkciju Novērtēt, taču jūs joprojām varat izmantot tālāk norādīto triku F9.

7. Lai pārbaudītu pārbaudes rezultātus, izmantojiet F9

Atlasot izteiksmi formulas joslā un nospiežot taustiņu F9, programma Excel atrisina tikai atlasīto daļu. Tas ir spēcīgs veids, kā apstiprināt, ko formula patiešām dara. Tālāk redzamajā ekrānā es izmantoju ekrāna padomu logus, lai atlasītu dažādas formulas daļas, pēc tam noklikšķinot uz F9, lai redzētu, ka šī daļa ir atrisināta:

Izmantojot F9, pārbaudiet ligzdotu IF, kas piešķir atzīmes

Lai atsauktu F9, Mac datorā izmantojiet Control + Z (Command + Z). Varat arī nospiest taustiņu Esc, lai izietu no formulas redaktora bez izmaiņām.

Video: Kā atkļūdot formulu ar F9

8. Ziniet savas robežas

Programmai Excel ir ierobežojumi, cik dziļi varat ligzdot IF funkcijas. Līdz Excel 2007 programmā Excel tika atļauti līdz 7 ligzdotu IF līmeņi. Programmā Excel 2007+ var izmantot līdz 64 līmeņiem.

Tomēr tikai tāpēc, ka tu var ligzdojiet daudz IF, tas nenozīmē, ka jūs vajadzētu . Katrs pievienotais papildu līmenis padara formulu grūtāk saprotamu un novērš problēmu. Ja atklājat, ka strādājat ar ligzdotu IF, kas ir dziļāks par dažiem līmeņiem, iespējams, jums vajadzētu izmantot citu pieeju - skatiet tālāk norādītās alternatīvas.

9. Saskaņojiet iekavas kā profesionālis

Viens no izaicinājumiem, kas saistīti ar ligzdotajiem IF, ir iekavu saskaņošana vai “līdzsvarošana”. Ja iekavas nav pareizi saskaņotas, formula ir bojāta. Par laimi, E xcel piedāvā pāris rīkus, kas palīdz pārliecināties, ka formulas rediģēšanas laikā iekavas ir “līdzsvarotas”.

Pirmkārt, ja jums ir vairāk nekā viena iekavu kopa, iekavas tiek iekrāsotas ar krāsu, lai sākuma iekavas atbilstu noslēguma iekavām. Šīs krāsas ir diezgan grūti saskatāmas, taču tās ir tur, ja paskatās cieši:

Formulu iekavas ir saskaņotas ar krāsām, bet grūti pamanāmas

Otrkārt (un labāk), aizverot iekavas, programma Excel uz īsu brīdi treknrakstīs atbilstošo pāri. Varat arī noklikšķināt uz formulas un izmantot bulttaustiņu, lai pārvietotos pa iekavām, un, ja ir atbilstošs pāris, programma Excel uz īsu brīdi iekrāsos abas iekavas. Ja nav atbilstības, jūs neredzēsiet treknrakstu.

Diemžēl treknrakstā ir tikai Windows funkcija. Ja Mac datorā izmantojat Excel, lai rediģētu sarežģītas formulas, dažreiz ir jēga kopēt un ielīmēt formulu labā teksta redaktorā ( Teksta ķengātājs ir bezmaksas un lielisks), lai iegūtu labākus iekavas atbilstošos rīkus. Teksta Wrangler mirgos, kad iekavas tiks saskaņotas, un jūs varat izmantot Command + B, lai atlasītu visu iekavās esošo tekstu. Pēc formulas izlīdzināšanas varat ielīmēt formulu atpakaļ programmā Excel.

10. Izmantojiet ekrāna padomu logu, lai pārvietotos un atlasītu

Runājot par ligzdoto IF navigāciju un rediģēšanu, funkciju ekrāna padoms ir jūsu labākais draugs. Ar to jūs varat pārvietoties un precīzi atlasīt visus argumentus ligzdotā IF:

Pārvietojieties un atlasiet formulas argumentus, izmantojot ekrāna padomu

Šajā videoklipā varat redzēt, kā es daudz izmantoju ekrāna padomu logu: Kā izveidot ligzdotu IF .

11. Uzmanieties ar tekstu un cipariem

Tāpat kā ātrs atgādinājums, strādājot ar IF funkciju, uzmanieties, lai cipari un teksts būtu pareizi saskaņoti. Es bieži redzu šādas formulas IF:

 
= IF (A1='100','Pass','Fail')

Vai testa rezultāts ir A1 tiešām teksts, nevis cipars? Nē? Tad nelietojiet pēdiņas ap skaitli. Pretējā gadījumā loģiskais tests atgriezīs FALSE pat tad, ja vērtība būs pozitīvs rezultāts, jo '100' nav tas pats, kas 100. Ja testa rezultāts ir skaitlisks, izmantojiet šo:

 
= IF (A1=100,'Pass','Fail')

12. Pievienojot rindiņu pārtraukumus, ligzdotos IF ir viegli lasīt

Strādājot ar formulu, kas satur daudzus ligzdotu IF līmeņus, var būt sarežģīti visu uzturēt taisni. Tā kā programmai Excel vienalga “atstarpe” formulās (t.i., papildu atstarpes vai rindu pārtraukumi), varat ievērojami uzlabot ligzdoto ifs lasāmību, pievienojot rindiņu pārtraukumus.

Piemēram, zemāk esošajā ekrānā ir redzams ligzdots IF, kas aprēķina komisijas maksu, pamatojoties uz pārdošanas numuru. Šeit jūs varat redzēt tipisko ligzdoto IF struktūru, kuru ir grūti atšifrēt:

Ligzdotus IF bez rindu pārtraukumiem ir grūti nolasīt

Tomēr, ja es pievienoju rindu pārtraukumus pirms katras “vērtības, ja tā ir nepatiesa”, formulas loģika skaidri izlec. Turklāt formulu ir vieglāk rediģēt:

Līniju pārtraukumi atvieglo ligzdoto IF lasīšanu

Jūs varat pievienot rindiņu pārtraukumus operētājsistēmā Windows, izmantojot taustiņu kombināciju Alt + Enter, Mac datorā izmantojiet Control + Option + Return.

Video: Kā padarīt ligzdotu IF vieglāk lasāmu .

13. Ierobežot IF ar AND un OR

Ligzdotie IF ir spēcīgi, taču, pievienojot vairāk līmeņu, tie ātri kļūst sarežģīti. Viens veids, kā izvairīties no vairākiem līmeņiem, ir IF izmantošana kombinācijā ar funkcijām AND un OR. Šīs funkcijas atgriež vienkāršu TRUE/FALSE rezultātu, kas lieliski darbojas IF iekšienē, tāpēc varat tās izmantot, lai paplašinātu viena IF loģiku.

Piemēram, tālāk norādītajā uzdevumā mēs vēlamies D slejā ievietot “x”, lai atzīmētu rindas, kurās krāsa ir “sarkana” un izmērs ir “mazs”.

IF ar funkciju AND ir vienkāršāka nekā divi ligzdoti IF

Mēs varētu uzrakstīt formulu ar diviem šādiem ligzdotiem IF:

 
= IF (B6='red', IF (C6='small','x',''),'')

Tomēr, aizstājot testu ar funkciju AND, mēs varam vienkāršot formulu:

 
= IF ( AND (B6='red',C6='small'),'x','')

Tādā pašā veidā mēs varam viegli paplašināt šo formulu ar funkciju OR, lai pārbaudītu, vai nav sarkana VAI zila UN maza:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

Viss šis varētu to var izdarīt ar ligzdotiem IF, bet formula strauji kļūtu sarežģītāka.

Video: JA šis vai tas

14. Nomainiet ligzdotos IF ar VLOOKUP

Ja ligzdotais IF vienkārši piešķir vērtības, pamatojoties uz vienu ievadi, to var viegli aizstāt ar Funkcija VLOOKUP . Piemēram, šis ligzdotais IF piešķir numurus piecām dažādām krāsām:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Mēs to varam viegli nomainīt ar šo (daudz vienkāršāku) VLOOKUP:

 
= VLOOKUP (E3,B3:C7,2,0)

Ielikts IF vs VLOOKUP

Kā bonuss VLOOKUP saglabā vērtības darblapā (kur tās var viegli mainīt), nevis iegulst formulā.

Lai gan iepriekšminētā formula izmanto precīzu atbilstību, to var viegli izmantot VLOOKUP atzīmēm arī.

Skatīt arī: 23 lietas, kas jāzina par VLOOKUP

Video: Kā lietot VLOOKUP

Video: Kāpēc VLOOKUP ir labāks par ligzdotajiem IF

15. Izvēlieties IZVĒLĒTIES

Funkcija CHOOSE var nodrošināt elegantu risinājumu, ja nepieciešams kartēt vienkāršus, secīgus skaitļus (1,2,3 utt.) Līdz patvaļīgām vērtībām.

Zemāk redzamajā piemērā CHOOSE tiek izmantots, lai izveidotu pielāgotus nedēļas dienu saīsinājumus:

Funkcija IF vs CHOOSE

Protams, tu varētu izmantojiet garu un sarežģītu ligzdotu IF, lai darītu to pašu, bet, lūdzu, nedariet to :)

16. Ligzdoto IF vietā izmantojiet IFS

Ja izmantojat programmu Excel 2016, izmantojot Office 365, ir jauna funkcija, ko varat izmantot ligzdoto IF vietā: funkcija IFS. Funkcija IFS nodrošina īpašu struktūru vairāku apstākļu novērtēšanai bez ligzdošana:

IFS funkcija - vairāki nosacījumi bez ligzdošanas

Iepriekš izmantotā formula izskatās šādi:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

Ņemiet vērā, ka mums ir tikai viens iekavu pāris!

Kas notiek, atverot izklājlapu, kas izmanto IFS funkciju vecākā Excel versijā? Programmās Excel 2013 un 2010 (un es uzskatu, ka Excel 2007, bet nevaru pārbaudīt) redzēsit “_xlfn”. pievienots IFS šūnā. Iepriekš aprēķinātā vērtība joprojām būs, taču, ja kaut kas liek formulai pārrēķināties, tiks parādīta kļūda #NAME. Microsoft ir vairāk informācijas šeit .

17. Maksimālais ārā

Dažreiz jūs varat izmantot MAX vai MIN ļoti gudrā veidā, lai izvairītos no IF paziņojuma. Piemēram, pieņemsim, ka jums ir aprēķins, kura rezultāts ir pozitīvs skaitlis vai nulle. Citiem vārdiem sakot, ja aprēķins atgriež negatīvu skaitli, jūs vienkārši vēlaties parādīt nulli.

Funkcija MAX sniedz jums gudru veidu, kā to izdarīt, ja IF nekur nav redzama:

 
= MAX (calculation,0)

Šī metode atgriež aprēķina rezultātu, ja tas ir pozitīvs, un citādi nulli.

Man patīk šī konstrukcija, jo tā ir tik vienkārši . Pilnu rakstīšanu skatiet šajā rakstā .

18. Slazdu kļūdas ar IFERROR

Klasisks IF lietojums ir kļūdu uztveršana un cita rezultāta sniegšana, kad tiek izlaista kļūda, piemēram:

 
= IF ( ISERROR (formula),error_result,formula)

Tas ir neglīti un lieki, jo viena formula tiek ievadīta divreiz, un Excel ir divreiz jāaprēķina tas pats rezultāts, ja nav kļūdas.

Programmā Excel 2007 tika ieviesta funkcija IFERROR, kas ļauj daudz elegantāk uztvert kļūdas:

 
= IFERROR (formula,error_result)

Tagad, kad formula rada kļūdu, IFERROR vienkārši atgriež jūsu norādīto vērtību.

19. Izmantojiet Būla loģiku

Dažreiz varat arī izvairīties no ligzdotiem IF, izmantojot tā dēvēto “Būla loģiku”. Vārds Būla apzīmē patiesas/nepatiesas vērtības. Lai gan programmā Excel šūnās tiek parādīti vārdi TRUE un FALSE, iekšēji Excel uzskata TRUE par 1 un FALSE kā nulli. Jūs varat izmantot šo faktu, lai uzrakstītu gudras un ļoti ātras formulas. Piemēram, iepriekš minētajā VLOOKUP piemērā mums ir ligzdota IF formula, kas izskatās šādi:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

Izmantojot Būla loģiku, jūs varat pārrakstīt formulu šādi:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

Katra izteiksme veic pārbaudi un pēc tam reizina testa rezultātu ar “vērtību, ja tā ir patiesa”. Tā kā testi atgriež patiesu vai nepatiesu (1 vai 0), FALSE rezultāti faktiski atceļ formulu.

Skaitliskiem rezultātiem Būla loģika ir vienkārša un ārkārtīgi ātra, jo nav sazarojuma. No otras puses, Būla loģika var mulsināt cilvēkus, kuri nav pieraduši to redzēt. Tomēr tā ir lieliska tehnika, kas jāzina.

Video: Kā izmantot Būla loģiku Excel formulās

Kad jums ir nepieciešams ligzdots IF?

Izmantojot visas šīs iespējas, lai izvairītos no ligzdotiem IF, jūs varētu brīnīties, kad ir jēga izmantot ligzdotu IF?

Es domāju, ka ligzdotajiem IF ir jēga, kad jums ir nepieciešams novērtēt vairākas dažādas ieejas pieņemt lēmumu.

Piemēram, pieņemsim, ka vēlaties aprēķināt rēķina statusu “apmaksāts”, “atvērts”, “nokavēts” utt. Lai to izdarītu, ir jāaplūko rēķina vecums. un nesamaksātais atlikums:

Rēķina statusa aprēķināšana ar ligzdotu IF

Šajā gadījumā ligzdots IF ir pilnīgi labs risinājums.

Jūsu domas?

Kā ar tevi? Vai esat IF-ster? Vai jūs izvairāties no ligzdotiem IF? Vai ligzdotie IF ir ļauni? Kopīgojiet savas domas zemāk.

Ātri apgūstiet Excel formulas, izmantojot kodolīga video apmācība . Autors Deivs Bruns


^