Funcția IF - formulele imbricate și evitarea erorilor de pe parcurs

Funcția IF vă permite să efectuați o comparație logică între o valoare și lucrul la care vă așteptați atunci când testați pentru o condiție și returnați un rezultat dacă este True sau False.

  • =IF(ceva este True, faceți ceva; altfel, faceți altceva)

Așadar, o instrucțiune IF poate avea două rezultate. Primul rezultat apare când comparația este True, al doilea când este False.

Instrucțiunile IF sunt incredibil de robuste și stau la baza multor modele de foi de calcul, dar mai sunt și principala cauză a multor probleme din foile de calcul. În mod ideal, o instrucțiune IF trebuie să se aplice la condiții minime, cum ar fi Masculin/Feminin, Da/Nu/Poate, ca să menționăm doar câteva, însă, uneori, poate fi necesar să evaluați scenarii mai complexe, care necesită imbricarea* a mai mult de 3 funcții IF.

* „Imbricarea” înseamnă practica de unire a mai multor funcții într-o singură formulă.

Utilizați funcția IF, una dintre funcțiile logice, pentru a returna o valoare dacă o condiție este adevărată și o altă valoare dacă este falsă.

Sintaxă

IF(test_logic, valoare_dacă_adevărat, [valoare_dacă_fals])

De exemplu:

  • =IF(A2>B2;"Peste buget";"OK")

  • =IF(A2=B2,B4-A4,"")

Nume argument

Descriere

test_logic   

(obligatoriu)

Condiția pe care doriți să o testați.

valoare_dacă_adevarat   

(obligatoriu)

Valoarea care doriți să fie returnată dacă rezultatul pentru test_logic este TRUE.

valoare_dacă_fals   

(opțional)

Valoarea care doriți să fie returnată dacă rezultatul pentru test_logic este FALSE.

Observații

Deși Excel vă va permite să imbricați până la 64 de funcții IF diferite, nu se recomandă deloc să faceți acest lucru. De ce?

  • Instrucțiunile IF multiple necesită foarte multe eforturi de gândire pentru a fi construite corect, deci asigurați-vă că logica acestora poate să calculeze corect fiecare condiție până la sfârșit. Dacă nu imbricați formula 100% corect, atunci ar putea funcționa 75% din timp, dar ar putea să returneze rezultate neașteptate în restul de 25% din timp. Din păcate, posibilitatea de a avea parte de acest 25% este redusă.

  • Instrucțiunile IF multiple pot deveni incredibil de dificil de întreținut, mai ales atunci când reveniți mai târziu și încercați să vă dați seama ce doreați să faceți dvs. sau o altă persoană.

Dacă vă confruntați cu o instrucțiune IF care pare să crească permanent, este timpul să puneți jos mouse-ul și să regândiți strategia.

Să vedem cum se creează corect o instrucțiune IF imbricată complexă utilizând mai multe IF-uri și când să recunoașteți că este timpul să utilizați un alt instrument din arsenalul dvs. Excel.

Exemple

Iată un exemplu de instrucțiune IF imbricată relativ standard pentru a efectua conversia punctajelor de testare a elevilor în calificativul cu litere echivalent.

Instrucțiune IF imbricată complexă - formula din E2 este =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"))))

    Această instrucțiune IF imbricată complexă se bazează pe o logică simplă:

  1. Dacă Punctajul testului (din celula D2) este mai mare decât 89, atunci elevul primește A

  2. Dacă Punctajul testului este mai mare decât 79, atunci elevul primește B

  3. Dacă Punctajul testului este mai mare decât 69, atunci elevul primește C

  4. Dacă Punctajul testului este mai mare decât 59, atunci elevul primește D

  5. Altfel, elevul primește un F

Acest exemplu particular este relativ sigur, deoarece este improbabil ca această corelare între punctajele de test și notele cu litere să se modifice, deci nu va necesita multă întreținere. Dar iată o altă idee: ce-ar fi dacă ați avea nevoie să segmentați punctajele între +A, A și -A (și așa mai departe)? Acum, instrucțiunea IF cu patru condiții trebuie să fie rescrisă pentru a avea 12 condiții! Iată cum ar arăta acum formula:

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

Aceasta este încă funcțional corectă și va funcționa așa cum vă așteptați, dar necesită mult timp pentru a fi scrisă și mai mult timp pentru a fi testată, ca să vă asigurați că face ceea ce doriți. O altă problemă evidentă este că a trebuit să introduceți manual punctajele și calificativul literă echivalent. Care este posibilitatea să aveți accidental o greșeală de ortografie? Acum imaginați-vă că încercați să faceți acest lucru de 64 de ori cu condiții mai complexe! Desigur, este posibil, dar chiar doriți să vă supuneți unui astfel de efort și să vă expuneți unor erori posibile care vor fi chiar greu de observat?

Sfat: Fiecare funcție din Excel necesită o paranteză de deschidere și închidere (). Excel va încerca să vă ajute să vă dați seama unde se încadrează fiecare element, prin colorarea unor părți diferite ale formulei, atunci când o editați. De exemplu, dacă ați edita formula de mai sus, când mutați cursorul peste fiecare dintre parantezele de final „)”, paranteza de deschidere corespunzătoare va căpăta aceeași culoare. Acest lucru poate fi util mai ales în formule imbricate complexe, atunci când încercați să vă dați seama dacă aveți suficiente paranteze potrivite.

Exemple suplimentare

Iată un exemplu foarte comun de calcul al unui comision de vânzări pe baza nivelurilor de venituri realizate.

Formula din celula D9 este 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)))))

Această formulă afirmă că IF(C9 este mai mare decât 15.000, atunci returnează 20%, IF(C9 este mai mare decât 12.500, atunci returnează 17,5% și așa mai departe...

Deși acest exemplu este remarcabil de asemănător cu exemplul anterior cu notele, această formulă este un bun exemplu despre cât de dificilă poate fi menținerea declarațiilor IF mari, ce ar trebui să faceți dacă organizația dvs. ar decide să adauge noi niveluri de compensații și, eventual, chiar să modifice valorile procentuale sau dolarul existente? Aveți multă muncă de făcut!

Sfat: Puteți să inserați sfârșituri de linie în bara de formule pentru a face formulele lungi mai ușor de citit. Apăsați pur și simplu ALT+ENTER înaintea textului pe care doriți să îl încadrați într-o linie nouă.

Iată un exemplu al scenariului de comision cu logică incorectă:

Formula din D9 este nefuncțională, deoarece =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))

Puteți să vedeți ce este greșit? Comparați ordinea comparațiilor între venituri cu exemplul anterior. În ce sens merge aceasta? Corect, merge din jos în sus (de la 5.000 lei la 15.000 lei), nu invers. Dar de ce este atât de important acest lucru? Este important, deoarece formula nu poate trece prima evaluare pentru nicio valoare de peste 5.000 lei. Să presupunem că aveți 12.500 de lei în venituri - instrucțiunea IF va returna 10%, deoarece este mai mare decât 5.000 lei și se va opri acolo. Acest lucru poate fi incredibil de problematic, deoarece, în multe situații, aceste tipuri de erori trec neobservate înainte să aibă un impact negativ. Așadar, acum că știți că există unele probleme serioase cu instrucțiunile IF imbricate complexe, ce este de făcut? În majoritatea cazurilor, puteți utiliza funcția VLOOKUP în loc să construiți o formulă complexă cu funcția IF. Utilizând VLOOKUP, trebuie mai întâi să creați un tabel de referințe:

Formula din celula D2 este =VLOOKUP(C2,C5:D17,2,TRUE)
  • =VLOOKUP(C2,C5:D17,2,TRUE)

Această formulă sugerează căutarea valorii din C2 în zona C5:C17. Dacă valoarea este găsită, atunci se returnează valoarea corespunzătoare din același rând din coloana D.

Formula din celula C9 este =VLOOKUP(B9,B2:C6,2,TRUE)
  • =VLOOKUP(B9,B2:C6,2,TRUE)

În mod similar, această formulă caută valoarea din celula B9 în zona B2:B22. Dacă valoarea este găsită, atunci se returnează valoarea corespunzătoare din același rând din coloana C.

Notă: Ambele funcții VLOOKUP utilizează argumentul TRUE la sfârșitul formulelor, ceea ce înseamnă că dorim ca ele să caute o potrivire aproximativă. Cu alte cuvinte, va potrivi valorile exacte din tabelul de căutare, precum și orice valori care se încadrează între ele. În acest caz, tabelele de căutare trebuie să fie sortate în ordine ascendentă, de la cele mai mici la cele mai mari.

VLOOKUP este mult mai detaliat aici ,dar această soluție este mult mai simplă decât o inselare IF imbricată complexă de 12 niveluri! Există și alte avantaje mai puțin evidente, precum:

  • Tabelele de referințe VLOOKUP sunt accesibile și simplu de văzut.

  • Valorile de tabel pot fi actualizate cu ușurință și nu trebuie niciodată să ajustați formula dacă se schimbă condițiile.

  • Dacă nu doriți ca alte persoane să vadă sau să modifice tabelul dvs. de referință, amplasați-l pur și simplu în altă foaie de lucru.

Știați?

Acum există o funcție IFS care poate înlocui mai multe instrucțiuni IF imbricate cu o singură funcție. Așadar, în locul exemplului nostru inițial cu punctajele, care are 4 funcții IF imbricate:

  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

Se poate face mult mai simplă, cu o singură funcție IFS:

  • =IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

Funcția IFS este excelentă, deoarece nu trebuie să vă faceți griji în legătură cu toate aceste instrucțiuni IF și paranteze.

Notă: Această caracteristică este disponibilă doar dacă aveți un abonament Microsoft 365. Dacă sunteți abonat Microsoft 365, asigurați-vă că aveți cea mai recentă versiune de Office.

Cumpărați sau încercați Microsoft 365

Aveți nevoie de ajutor suplimentar?

Puteți întreba oricând un expert de la Excel Tech Community sau puteți obține asistență de la comunitatea Answers.

Subiecte asociate

Video: Funcții IF complexe
Funcția IFS (Microsoft 365, Excel 2016 și mai recente)
Funcția COUNTIF va contora valori pe baza unui singur criteriu
Funcția COUNTIFS va contora valori pe baza mai multor criterii
Funcția SUMIF va însuma valori pe baza unui singur criteriu
Funcția SUMIFS va însuma valori pe baza mai multor criterii
Funcția AND
Funcția OR
VLOOKUP (funcția VLOOKUP)
Prezentare generală a formulelor din Excel
Cum să evitați formulele întrerupte
Detectarea erorilor din formule
Funcții logice
Excel (în ordine alfabetică)
Excel funcții (după categorie)

Aveți nevoie de ajutor suplimentar?

Extindeți-vă competențele

Explorați instruirea >

Fiți primul care obține noile caracteristici

Alăturați-vă la Microsoft Insider >

Au fost utile aceste informații?

Cât de mulțumit sunteți de calitatea limbajului?
Ce v-a afectat experiența?

Vă mulțumim pentru feedback!

×