Cum de a Funcții IF Nivele multiple în Excel

01 din 06

Cum funcționează funcțiile IF născute

Utilitatea funcției IF poate fi extinsă prin introducerea sau inserarea mai multor funcții IF în interiorul celuilalt.

Funcțiile IF născute cresc numărul de condiții posibile și pot crește numărul de acțiuni care pot fi luate pentru a face față acestor rezultate.

Cele mai recente versiuni ale Excel permit 64 de funcții IF să fie imbricate unul în altul, în timp ce Excel 2003 și anterioare au permis doar șapte.

Funcționarea Tutorial IF Funcții

Așa cum se arată în imaginea de mai sus, acest tutorial utilizează doar două funcții IF pentru a crea următoarea formulă care calculează o sumă anuală de deducere pentru angajați pe baza salariului lor anual.

Formula folosită în exemplu este prezentată mai jos. Funcția IF imbricată acționează ca argumentul value_if_false pentru prima funcție IF.

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

Diferitele părți ale formulei sunt separate prin virgule și îndeplinesc următoarele sarcini:

1. Prima parte, D7, verifică dacă salariul unui angajat este mai mic de 30.000 de dolari
2. Dacă este, partea de mijloc, $ D $ 3 * D7 , înmulțește salariul cu rata de deducere de 6%
3. Dacă nu este, a doua funcție IF: dacă (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) testează două condiții suplimentare:
   • D7> = 50000 , verifică dacă salariul unui salariat este mai mare sau egal cu 50.000 $
   • Dacă este, $ D $ 5 * D7 multiplică salariul cu rata de deducere de 10%
   • Dacă nu este, $ D $ 4 * D7 înmulțește salariul cu rata de deducere de 8%

Introducerea datelor Tutorial

Introduceți datele în celulele C1 - E6 ale foii de lucru Excel așa cum se vede în imaginea de mai sus.

Singurele date care nu au fost introduse în acest moment sunt funcțiile IF aflate în celula E7.

Pentru cei care nu doresc să scrie, datele și instrucțiunile pentru copierea în Excel sunt disponibile la acest link.

Notă: instrucțiunile pentru copierea datelor nu includ pașii de formatare pentru foaia de lucru.

Acest lucru nu va interfera cu finalizarea tutorialului. Foaia dvs. de lucru poate arăta diferită de exemplul afișat, dar funcția IF vă va oferi aceleași rezultate.

02 din 06

Pornirea funcției IF născute

Deși este posibil să introduceți doar formula completă

= IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))

în celula E7 din foaia de lucru și o să funcționeze, este adesea mai ușor să utilizați caseta de dialog a funcției pentru a introduce argumentele necesare.

Utilizarea casetei de dialog este un pic mai complicată atunci când intrăm în funcții imbricate , deoarece funcția imbricată trebuie să fie introdusă. O a doua casetă de dialog nu poate fi deschisă pentru a intra în al doilea set de argumente.

Pentru acest exemplu, funcția IF imbricată va fi introdusă în a treia linie a casetei de dialog ca argumentul Value_if_false .

• Deoarece foaia de calcul calculează deducerea anuală pentru mai mulți angajați, formula va fi introdusă mai întâi într-o singură celulă E7 folosind referințe absolute pentru celulele ratelor de deducere și apoi copiată în celulele E8: E11.

Tutoriale Pași

1. Faceți clic pe celula E7 pentru a deveni celula activă. - locația pentru formula IF nested.
2. Faceți clic pe fila Formule a panglicii
3. Faceți clic pe pictograma Logical deschideți lista verticală de funcții.
4. Faceți clic pe IF din listă pentru a afișa caseta de dialog a funcției.

Datele introduse în liniile goale din caseta de dialog formează argumentele funcției IF.

Aceste argumente indică funcția condiția testată și ce acțiuni trebuie luate dacă condiția este adevărată sau falsă.

Opțiunea de scurtături pentru tutoriale

Pentru a continua cu acest exemplu, puteți

• introduceți argumentele în caseta de dialog, așa cum se arată în imaginea de mai sus, apoi treceți la pasul final care acoperă copierea formulei în rândurile de la 7 la 10;
• sau urmați următoarele cinci pagini care oferă instrucțiuni detaliate și explicații pentru introducerea celor trei argumente.

03 din 06

Introducerea argumentului Logical_test

Argumentul Logical_test este întotdeauna o comparație între două elemente de date. Aceste date pot fi numere, referințe de celule , rezultate ale formulelor sau chiar date de text.

Pentru a compara două valori, Logical_test utilizează un operator de comparare între valori.

În acest exemplu, există trei niveluri salariale care determină deducerea anuală a unui angajat.

• mai puțin de 30.000 de dolari
• între $ 30.000 și $ 49.999
• 50.000 $ sau mai mult

O singură funcție IF poate compara două niveluri, dar al treilea nivel al salariului necesită utilizarea celei de-a doua funcții IF imbricate.

Prima comparație va fi între salariul anual al angajatului, situat în celula D, cu pragul salariului de 30.000 $.

Deoarece obiectivul este acela de a determina dacă D7 este mai mic de 30.000 $, operatorul Mai mic decât "<" este folosit între valori.

Tutoriale Pași

1. Faceți clic pe linia Logical_test din caseta de dialog
2. Faceți clic pe celula D7 pentru a adăuga această referință de celule la linia Logical_test
3. Apăsați mai puțin de tasta "<" de pe tastatură
4. Tipul 30000 după mai puțin decât simbolul
5. Testul logic complet trebuie citit: D7 <30000

Notă: nu introduceți semnul dolar ($) sau un separator de virgulă (,) cu modelul 30000.

Un mesaj de eroare nevalid va apărea la sfârșitul liniei Logical_test dacă unul dintre aceste simboluri este introdus împreună cu datele.

04 din 06

Introducerea argumentului Value_if_true

Argumentul Value_if_true indică funcția IF ce să facă atunci când testul Logical_test este adevărat.

Argumentul Value_if_true poate fi o formulă, un bloc de text, o valoare , o referință de celule sau celula poate fi lăsată necompletată.

În acest exemplu, când datele din celula D7 sunt mai mici de 30.000 $. Excel multiplică salariul anual al angajatului în celula D7 cu rata de deducere de 6% situată în celula D3.

Relative față de referințele absolute ale celulelor

În mod normal, atunci când o formulă este copiată în alte celule, referințele celulare relative din formula se modifică pentru a reflecta noua locație a formulei. Acest lucru facilitează utilizarea aceleiași formulări în mai multe locații.

Cu toate acestea, ocazional, schimbarea referințelor de celule atunci când se copiază o funcție va duce la erori.

Pentru a preveni aceste erori, referințele de celule pot fi făcute Absolute care le împiedică să se schimbe atunci când sunt copiate.

Recomandările de celule absolute sunt create prin adăugarea de semne de dolar în jurul unei referințe de celule obișnuite, cum ar fi $ D $ 3 .

Adăugarea semnelor în dolari se face cu ușurință apăsând tasta F4 de pe tastatură după ce referința celulei a fost introdusă în caseta de dialog.

În exemplu, rata de deducere localizată în celula D3 este introdusă ca referință de celule absolute în linia Value_if_true a casetei de dialog.

Tutoriale Pași

1. Faceți clic pe linia Value_if_true din caseta de dialog
2. Faceți clic pe celula D3 din foaia de lucru pentru a adăuga această referință de celule la linia Value_if_true
3. Apăsați tasta F4 de pe tastatură pentru a face D3 o referință de celule absolute ($ D $ 3)
4. Apăsați tasta asterisc ( * ) de pe tastatură - asteriscul este simbolul de multiplicare din Excel
5. Faceți clic pe celula D7 pentru a adăuga această referință de celule la linia Value_if_true
6. Linia Value_if_true completă trebuie citită: $ D $ 3 * D7

Notă : D7 nu este introdus ca referință de celule absolute, deoarece trebuie să se schimbe atunci când formula este copiată în celulele E8: E11 pentru a obține suma corectă de deducere pentru fiecare angajat.

05 din 06

Introducerea funcției IF Nestate ca argument Value_if_false

În mod normal, argumentul Value_if_false indică funcției IF ce să facă atunci când testul Logical_test este fals, dar în acest caz, funcția IF imbricată este introdusă ca argument.

Procedând astfel, apar următoarele rezultate:

• Argumentul Logical_test din funcția IF imbricată ( D7> = 50000 ) testează toate salariile care nu sunt mai mici de 30.000 $.
• Pentru salariile mai mari sau egale cu 50.000 dolari, argumentul Value_if_true îi multiplică prin rata de deducere de 10% situată în celula D5.
• Pentru salariile rămase - cele care depășesc 30.000 $, dar mai puțin de 50.000 $ - argumentul Value_if_false îi multiplică prin rata de deducere de 8% situată în celula D4.

Tutoriale Pași

După cum sa menționat la începutul tutorialului, nu se poate deschide oa doua casetă de dialog pentru a intra în funcția imbricată, așa că trebuie introdusă în linia Value_if_false.

Notă: Funcțiile imbricate nu pornesc cu un semn egal - ci mai degrabă cu numele funcției.

1. Faceți clic pe linia Value_if_false din caseta de dialog
2. Introduceți următoarea funcție IF
   IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7)
3. Faceți clic pe OK pentru a finaliza funcția IF și a închide caseta de dialog
4. Valoarea de 3.678,96 dolari ar trebui să apară în celula E7 *
5. Când faceți clic pe celula E7, funcția completă
   = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7))
   apare în bara de formula de deasupra foii de lucru

* Deoarece R. Holt câștigă mai mult de 30.000 de dolari, dar mai puțin de 50.000 de dolari pe an, formula de 45.987 $ * 8% este folosită pentru a calcula deducerea anuală.

Dacă ați urmat toți pașii, exemplul dvs. ar trebui să corespundă în prezent primei imagini din acest articol.

Ultimul pas constă în copierea formulei IF la celulele E8 până la E11 utilizând mânerul de umplere pentru a completa foaia de lucru.

06 din 06

Copierea funcțiilor IF încorporate utilizând mânerul de umplere

Pentru a finaliza foaia de lucru, formula care conține funcția IF imbricată trebuie să fie copiată în celulele E8 până la E11.

Pe măsură ce funcția este copiată, Excel va actualiza referințele de celule relative pentru a reflecta noua locație a funcției, păstrând totodată aceeași referință de celule absolute.

O modalitate ușoară de a copia formulele în Excel este cu mânerul de umplere.

Tutoriale Pași

1. Faceți clic pe celula E7 pentru a deveni celula activă .
2. Așezați indicatorul mouse-ului peste pătratul negru din colțul din dreapta jos al celulei active. Pointerul se va schimba la semnul plus "+".
3. Faceți clic pe butonul stâng al mouse-ului și trageți mânerul de umplere în jos în celula E11.
4. Eliberați butonul mouse-ului. Celulele E8 la E11 vor fi umplute cu rezultatele formulei așa cum se arată în imaginea de mai sus.