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

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 punctajele, această formulă este un bun exemplu legat de cât de dificilă poate fi menținerea instrucțiunilor IF mari, ce ar trebui să faceți dacă organizația dvs. ar decide să adauge noi niveluri de compensare și, eventual, chiar să modifice valorile monetare sau cele procentuale? Ar trebui multă muncă manuală pentru aceasta!

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 descris mult mai detaliat aici, dar această funcție este mult mai simplă decât o instrucțiune IF imbricată complexă de nivel 12! 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 Office 365. Dacă sunteți abonat la Office 365, asigurați-vă că aveți cea mai recentă versiune de Office.

Încercați Office 365 sau cea mai recentă versiune de Excel

Aveți nevoie de ajutor suplimentar?

Puteți întreba întotdeauna un expert de la Excel Tech Community, puteți obține asistență de la comunitatea Answers sau puteți sugera o caracteristică nouă sau o îmbunătățire pe Excel UserVoice.

Subiecte asociate

Video: Funcții IF complexe
Funcție IFS (Office 365, Excel 2016 și versiuni mai recente)
Funcția COUNTIF va contoriza valori pe baza unui singur criteriu
Funcția COUNTIFS va contoriza 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
Funcția VLOOKUP
Prezentare generală a formulelor din Excel
Cum să evitați formulele eronate
Utilizarea verificării erorilor pentru detectarea erorilor din formule
Funcții logice
Funcții Excel (în ordine alfabetică)
Funcții Excel (după categorie)

Extindeți-vă competențele
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×