1. Functia IF cu conditii logice AND/OR/NOT multiple

Dupa cum stim din Partea I, sintaxa generala a functiei IF este urmatoarea:

IF(test logic, [valoarea daca testul este ADEVARAT], [valoarea daca testul este FALS])

Pot exista 3 tipuri de baza de conditii logice multiple- folosind functiile logice (booleene) AND, OR si NOT.

Daca testul dvs. logic contine functia AND, atunci Excel returneaza TRUE daca sunt indeplinite toate conditiile SIMULTAN; altfel returneaza FALSE.

In cazul in care utilizati functia OR in testul logic, Excel returneaza TRUE daca sunt indeplinite cel putin una dintre conditii (oricare), altfel returneaza FALSE.

Pentru a ilustra mai bine acest aspect, sa aruncam o privire pe cateva exemple IF cu conditii multiple.

Exemplul 1.1 Utilizarea functiei IF impreuna cu functia AND

Sa presupunem ca aveti un tabel ca cel de mai jos:

Dorim sa indexam salariile cu 10% DOAR pentru inginerii din Iasi. Cu alte cuvinte trebuiesc indeplinite doua conditii logice simultan pentru indexare: o linie anume sa contina Profesia=”inginer” si in acelasi timp Orasul=”Iasi”. Daca oricare dintre cele doua conditii logice nu este indeplinita, atunci NU se va realiza indexarea

Predicatul logic este format din doua conditii logice legate cu functia AND:

AND(C5=”Iasi”, D5=”inginer”)=TRUE

=IF(AND(C5=”Iasi”, D5=”inginer”)=TRUE, E5*1.1, E5)

Daca conditiile logice din IF sunt evaluate cu valoarea TRUE, atunci nu mai este nevoie a scrie explicit acest lucru. Putem rescrie formula de mai sus mai simplu, astfel:

=IF(AND(C5=”Iasi”, D5=”inginer”), E5*1.1, E5)

Observatie: Excel verifica toate conditiile din functia AND, chiar daca una dintre conditiile testate deja a fost evaluata la FALSE. Un astfel de comportament este un pic atipic, deoarece in majoritatea limbajelor de programare, conditiile ulterioare nu mai sunt testate daca vreunul dintre testele anterioare a returnat FALSE.

Astfel, o formula aparent corecta IF cu AND poate duce la o eroare din cauza acestei fapt.

De pilda, pentru tabelul din poza de mai jos, formula

=IF(AND(C5>100, C5/D5>5), C5/D5, „”)

va returna eroarea „Impartire la zero” (#DIV/0!)

Pentru a evita acest lucru, ar trebui sa utilizati o functie IF imbricata (nested IF):

=IF(C5>100, IF(C5/D5>5, C5/D5,””), „”)

In acest caz, expresia C5/D5 se executa numai daca se indeplineste conditia C5>100

Exemplul 1.2. Utilizarea functiei IF impreuna cu functia OR

Utilizati combinatia de functii IF cu OR intr-un mod similar. Diferenta fata de formula IF cu AND, discutata mai sus, este aceea ca Excel returneaza TRUE daca sunt indeplinite cel putin una dintre conditiile specificate (oricare).

Fie tabelul cu angajati prezentat mai jos:

Doriti sa mariti salariile cu 10% doar pentru angajatii din Iasi sau Bacau, pentru aceasta veti scrie formula:

=IF(OR(C5=”Iasi”, C5=”Bacau”), D5*1.1, D5)

Exemplul 1.3 Utilizarea functiei IF cu NOT

Functia NOT neaga toata expresia logica ce apare imediat dupa ea:

In poza de mai jos dorim sa indexam salariile cu 10% doar celor CARE au salariul Stabilit (nu si celor care au la salariu lasat BLANK sau sirul empty „”)

Gasim mai intai celulele BLANK impreuna cu cele empty, iar apoi NEGAM cu NOT intregul predicat logic (pentru ca pe noi ne intereseaza cei care au Salariu nu cei care n-au)

Celulele cu BLANK si empty:

OR(ISBLANK(D5), D5=””)

, iar acum negam intregul predicat:

Negatia lui BLANK cu Empty este

NOT(OR(ISBLANK(D5), D5=””))

, astfel asa i-am gasit pe cei care au salariul completat.

Mai ramane doar sa impaturim totul intr-un IF care se permita indexarea propriu-zisa a salariului:

=IF(NOT(OR(ISBLANK(D5), D5=””)), D5*1.1,””)

Conform regulilor matematicii booleene (vezi nota de mai jos), care spun ca NOT transforma AND in OR si viceversa, putea sa descompunem formula de mai sus astfel (rezultatul final fiind acelasi):

=IF(AND(NOT(ISBLANK(D5)), D5<>””), D5*1.1,””)

Nota: NOT(P OR Q) se descompune in  NOT P  AND NOT Q (unde P este in cazul nostru ISBLANK(D5), iar Q este D5=””)

Trebuie sa fiti de acord cu mine ca varianta cu NOT care neaga intregul predicat OR (prima varianta) este mult mai eleganta si mai logica.

Exemplul 1.4: Folosirea tuturor celor trei functii logice AND, OR NOT , toate in tandem cu IF:

Doriti sa mariti salariilor tuturor angajatilor mai putin a celor din Bucuresti sau Timisoara care au deja salariile peste 5000.

Stiu ca este un predicat logic mai greu de urmarit, insa eu va recomand sa scrieti mai intai predicatul pentru ce pe care doriti sa-I excludeti, apoi sa negati totul cu NOT.

In consecinta, pe cine dorim sa excludem de la marirea salariului:

Pe cei din Bucuresti sau Timisoara care au deja salariile peste 5000

(P OR Q) AND T

adica (Orasul Bucuresti SAU Orasul Timisoara) SI Salariul peste 5000

Negam intregul predicat de mai sus cu NOT pentru a-i gasi pe cei la care indexam

NOT (P OR Q) AND T

, adica NU( (Orasul Bucuresti SAU Orasul Timisoara) SI Salariul peste 5000)

Traducem totul intr-o sintaxa intermediara Excel:

NOT( (Celula_Oras= „Bucuresti” OR Celula_Oras=”Timisoara”) AND Celula_Salariul > 5000)

Apoi transformam predicatul boolean in formula cu functii Excel:

=IF(NOT( AND(OR(C5= „Bucuresti”,  C5=”Timisoara”), D5 > 5000)),D5*1.1,””)

Desigur, nu va limitati la utilizarea a trei functii AND / OR  / NOT in formulele cu IF. Puteti utiliza cat de  multe functii logice cat solicita domeniul de afaceri, cu conditia ca:

-In Excel 2016, 2013, 2010 si 2007, formula dvs. sa nu contina functii cu mai mult de 255 de argumente, iar lungimea totala a formulei sa nu depaseasca 8.192 de caractere.

-In Excel 2003 si versiunile inferioare, puteti utiliza pana la 30 de argumente, iar lungimea totala a formulei dvs. nu trebuie sa depaseasca 1.024 de caractere.

2. Functii IF imbricate (nested Ifs)

Daca aveti nevoie pentru a crea teste logice mai elaborate pentru datele dvs., puteti include instructiuni IF suplimentare in argumentele:

[valoarea daca testul este ADEVARAT], [valoarea daca testul este FALS]

Aceste functii multiple IF se numesc functii IF imbricate (nested) si se pot dovedi deosebit de puternice daca doriti ca formula dvs. sa dea 3 sau mai multe rezultate diferite.

Iata un exemplu tipic: sa presupunem ca nu doriti pur si simplu sa calificati rezultatele studentilor cu LUAT / PICAT, ci vreti sa definiti scorul total cu litere: F, D, C, B, A. De exemplu:

F daca score este sub 64

D daca score este sub 73

C daca score este sub 85

B daca score este sub 95

A daca score este peste sau egal cu 95

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

Puteti sa scrieti mai lizibil formula astfel:

                          =IF(C5<64,”F”,

                                                 IF(C5<73,”D”,

                                                                        IF(C5<85,”C”,

                                                                                             IF(C5<95,”B”,”A”))))

Va recomand sa va scrieti IF-urile nest in bara de formule folosind urmatoare caractere pentru indentari:

-Tastele ALT + Enter apasate simultan pentru a trece pe rand nou

-Tasta Spatiu apasata de mai multe ori pentru indentarea la dreapta

3. Cum se foloseste functia IF in formulele vectoriale (array formula)

Ca si alte functii Excel, IF poate fi utilizata in formule de tip vectorial. Este posibil sa aveti nevoie de o astfel de formula daca doriti sa evaluati fiecare element al tabloului atunci cand este efectuata instructiunea IF.

Va rog sa urmariti poza de mai jos:

Urmatorul exemplu de formula SUM / IF demonstreaza modul in care puteti insuma celule in intervalul specificat bazat pe o anumita conditie, fara a folosi o coloana ajutatoare:

{=SUM(IF(C5:C11<64, 1,0))}

Nota: Formula de mai sus se introduce cu combinatia de taste CTRL + SHIFT + Enter pentru ca este o formula vectoriala

Formula aloca un anumit numar de „puncte” fiecarei valori din coloana C – daca Punctajul este mai mic de 64, ea aloca TRUE (adica 1), daca nu atunci ramane FALSE (adica 0). Si apoi, functia SUM aduna valorile 1 si 0 in rezultatul final, asa cum se arata in imaginea de mai jos.

Practic conditia logica C5:C11<64 este evaluata la TRUE sau FALSE pentru fiecare celula din blocul C5:C11, iar IF va inlocui fiecare True cu 1, respectiv False cu 0

Selectati cu mouse-ul conditia logica din IF (adica C5:C11<64) si apasati tasta F9 (apoi, pentru a renunta, apasati tasta Escape)

=SUM(IF({FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}, 1,0))

Rezultand SUM({0;0;1;1;0;1;0}), ca apoi functia SUM sa adune toate aceste elemente ale vectorului

Sigur ca in acest exemplu simplu puteam sa folosim si o functie agregata conditionata de numarare, de pilda: CountIF, fara sa ne mai chinuim cu vectorii

Formula cu CountIF este:

=CountIF(C5:C11,”<64″)

Folosim calculele vectoriale cand nu avem de ales sau cand dorim sa facem „dintr-un foc” ce altfel s-ar face in mai multi pasi (fara sa apelam la coloane calculate sau la panouri de control).

De ce sa folositi formule vectoriale in Excel?

Formulele vectoriale Excel sunt cel mai usor instrument pentru a efectua calcule sofisticate si pentru a face sarcini complexe. O formula vectoriale unica poate inlocui literalmente sute de formule obisnuite. Formulele Array sunt foarte bune pentru sarcini precum:

-Insumare daca se indeplinesc anumite conditii, de exemplu, insumarea a N valori aflate intr-un interval dat

-Insumare a fiecarei a N-a valoare de pe o coloana data(din n in n randuri).

-Numarare de cate ori apare pe o coloana un anumit text.

Aceste formule vectoriale reprezinta un subiect complex, asa incat am sa-l explic in articole separate.

O ultima observatie, pentru ca trebuie tastata combinatia CTRL +SHIFT + Enter la finalul scrierii unei formule vectoriale, acestea se mai numesc si formule CSE

4. Folosirea functiei IF impreuna cu alte functii Excel

Mai devreme in acest articol, am discutat cateva exemple de formule cu IF care demonstreaza modul de utilizare a functiei IF cu functii logice AND , OR, NOT. Acum, sa vedem ce alte functii Excel mai pot fi utilizate cu IF si ce beneficii va ofera aceasta tehnica.

4.1. Functia IF impreuna cu functiile agregate: SUM, Average, MIN, MAX

Priviti poza de mai jos:

Dorim sa fixam un calificativ final pentru SUMA notelor, la fiecare cursant in parte:

-Daca suma notelor este pana in 150, primeste calificativ PROST

-Daca suma notelor este pana in 250, primeste calificativ BUN

-Daca suma notelor este peste sau egala cu 250, primeste calificativ EXCELENT

Formula pentru primul cursant (Ion) este:

=IF(SUM(C5:E5)<150, „PROST”, IF(SUM(C5:E5)<250, „BUN”, „EXCELENT”))

Exemplul de mai jos calculeaza calificativul din perspectiva mediei:

=IF(AVERAGE(C5:E5)<50, „PROST”, IF(AVERAGE (C5:E5)<75, „BUN”, „EXCELENT”))

Sau poate dorim sa afisam in dreptul celui cu scorul general cel mai mare calificativul „Cel mai tare”, respectiv in dreptul celui cu scorul general cel mai mic „Cel mai slab”. Vedeti poza:

=IF(F5=MAX($F$5:$F$11), „Cel mai tare”, IF(F5=MIN($F$5:$F$11), „Cel mai slab”,””))

 4.2. Functia IF cu functiile de testare ISNUMBER si ISTEXT

Deja stim sa folosim functia ISBLANK(celula) care returneaza TRUE daca celula este necompletata

Vorbim acum de functiile ISNUMBER(celula) si ISTEXT(celula) care evalueaza continutul celulei trimise ca parametru si returneaza TRUE daca ea contine un numar, respectiv un text.

In tabelul aratat mai jos dorim sa afisam in dreptul fiecarui rand cum a interpretat Excel valoarea din stanga. Observati ca datele calendaristice sunt de fapt numere

4.3. Folosirea functiei IF in interiorul altor functii

Putem sa folosim functia IF pe post de argument pentru orice functie care suporta calcule intermediare in interiorul sau.

De pilda, functia Concatenate folosita in exemplul urmator, lipeste un text variabila la un text fix in functie de o conditie logica gestionata de un IF

=CONCATENATE(„Te descurci „, IF(C4>80, „extraordinar”, „bine”))

5. Cum lucreaza functia IF cu functiile de tratarea a erorilor: IFERROR and IFNA

5.1. Functia IF cu IFERROR

In exemplul de mai jos, nu putem face direct impartirea pretului Facturii la cantitate, deoarece exista cantitati necompletate (si riscam sa primim eroarea de impartire la 0)

Folosind functia IFError tratam exceptia de impartire la 0, pentru a da un raspuns „prietenos” utilizatorului:

=IFERROR(C5/D5, „aici nu pot calcula”)

Problema cu aceasta abordare este ca functia IFERROR va afisa acelasi mesaj de eroare indiferent ce exceptie apare (poate alta decat „impartirea la 0”)

Daca insa dorim sa prindem fiecare exceptii in parte, de pilda pentru a da un mesaj personalizat in functie de tipul exceptiei:

=IF(D5=0, „nu se poate impartii la zero”, C5/D5)

Sau si mai bine, convertim rezultatul la 0 daca apare impartirea imposibila, iar daca nu apare eroarea returnam rezultatul impartirii:

=IF(ISERROR(C5/D5),0, C5/D5)

5.2 Functia IFNA cu Vlookup

Functia Vlookup poate cauta o valoarea exacta de pe o coloana a unui tabel intr-o coloana din alt tabel.

In cazul in care valoarea cautata nu exista, Vlookup intoarce exceptia #N/A.

Cu ajutorul functiei IFNA putem sa inlocuim #N/A cu un mesaj prietenos, de pilda cu: „nu exista”

Sintaxa:

IFNA(expresie de evaluat si de returnat daca ea NU este #N/A,  ce sa returneze in cazul in care expresia de la argumentul 1 este #N/A)

In exemplul clasic de mai jos, dorim sa cautam procentul de indexare in nomenclatorul de sus si sa-l punem in tabelul cu date de mai jos:

In coloana din tabelul de jos scriem formula de cautare cu functia Vlookup in tabelul de sus:

=VLOOKUP(B9,$A$2:$B$6,2,0)

Problema apare atunci cand nu exista profesia (de pilda „hr”) in nomenclatorul de sus, de aceea VLOOKUP intoarce in acel caz eroarea #N/A

Tratam valoarea #N/A cu functia ISNA.

Aceasta va returna un mesaj text personalizat in locul valorii #N/A

=IFNA(VLOOKUP(B9,$A$2:$B$6,2,0), „nu exista”)

6. Alternative la functia IF (Vlookup, IFS, CHOOSE, Switch si Concatenate)

Functia IF poate fi inlocuita cu succes in anumite tipuri de luari de decizii.

6.1 Inlocuirea functiei IF cu VLOOKUP daca trebuie sa luam decizii multiple pe baza unor valori exacte

Cum ar arata formula folosind functia IF care inlocuieste Vlookup-ul din exemplul de mai sus (5.2)?

Fiind de luat 4 decizii diferite in functie de Profesie, trebuie sa folosim 3 if-uri imbricate:

=IF(B8=”inginer”,10%,IF(B8=”asistent”,5%,IF(B8=”manager”,14%,20%)))

Daca aveam 10 tipuri diferite de Profesii, ar fi trebuit sa scriem 9 IF-uri imbricate, ceea ce ar fi fost de neacceptat

Concluzia: Cand avem de cautat intr-o lista de valori si de luat decizii punctuale pe baza acelor valori din lista, NU FOLOSIM IF CI VLOOKUP (cu un panou de control separat care „traduce” fiecare valoare cautata). Este si mai usor de gestionat in cazul in care se schimba des valorile din panoul de control. Cu IF-urile imbricate ar fi trebuit sa le modific de fiecare data in formula.

6.2 Inlocuirea functiei IF cu VLOOKUP daca trebuie sa luam decizii multiple pe baza unor valori aflate in intervale multiple

Va supun atentiei urmatorul exemplu de luare a deciziilor pe baza unor Scoring-uri

Am prezentat deja acest exemplu in articolul curent in capitolul 2. Functii IF imbricate (nested Ifs)

Alternativa la acele IF-uri imbricate este crearea unui panou de control separat cu literele de Scoring, apoi folosirea functiei Vlookup in scenariul de cautari aproximative (TRUE la al patrulea argument, in loc de False cu care sunteti obisnuiti)

Nu uitati sa SORTATI tabelul ajutator dupa coloana Punctaj (cea pe care cautam cu VLOOKUP).

Atentie: VLOOKUP trebuie sa aibe la sfarsit atributul TRUE (daca nu gaseste fix valoarea cautata se opreste la cea de deasupra, iar cum tabelul ajutator este sortat inseamna ca acea valoare reprezinta scoring-ul sau cel mai apropiat)

Cum functioneaza VLOOKUP cu true la final?

Sa luam cautarea pentru Mirela (are scoring-ul 84).

Cauta pe coloana Punctaj din tabelul din dreapta cea mai mare valoare care este mai MICA decat cea cautata.

In cazul Mirelei: 73 este cea mai mare dintre valorile mai mici de 84

Adica scoring-ul C

6.3 Inlocuirea functiei IF cu IFS in cazul in care dorim sa testam conditii multiple

In Excel 2016 si versiunile ulterioare, Microsoft a introdus o functie speciala pentru a evalua mai multe conditii in cadrul aceleiasi formule – functia IFS.

O formula IFS poate gestiona pana la 127 de perechi test_logic / valoare_adevarat, iar primul test logic care evalueaza TRUE „castiga”:

Sintaxa:

IFS (test_logic 1/ valoare_adevarat1, test_logic2 / valoare_adevarat2, …)

In conformitate cu sintaxa de mai sus, formula noastra IF imbricata de la 2. Functii IF imbricate (nested Ifs) poate fi reconstruita astfel:

Pornind de la:

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

Ajungem la:

= IFS (C5<64, „F”, C5<73, „D”, C5<85, „C”, C5<95, „B”, TRUE, „A”)

Conditiile logice din IFS sunt evaluate pe rand de la stanga la dreapta, prima care este indeplinita duce si la returnarea pe loc a valorii sale.

Observati ultima pereche: True, „A” care este evaluata ultima daca niciuna din conditiile logice de dinainte nu au fost indeplinite.

Va rugam sa retineti ca functia IFS returneaza eroarea #N/A daca nu este indeplinita niciuna dintre conditiile specificate. Pentru a evita acest lucru, puteti adauga inca o pereche test_logic / valoare_true la sfarsitul formulei dvs. , care va returna 0 sau sir gol („”) sau orice valoare doriti daca niciunul dintre testele logice anterioare nu este TRUE:

= IFS (C5<64, „F”, C5<73, „D”, C5<85, „C”, C5<95, „B”, C5>=95,”A”,TRUE, „”)

Ca rezultat, formula noastra va returna „”” in loc de eroarea #N/A daca o celula corespunzatoare din coloana C este goala sau contine text.

6.4 Inlocuirea functiilor IF imbricate cu (o singura) functie CHOOSE

Un alt mod de a testa mai multe conditii intr-o singura formula Excel este folosirea functiei CHOOSE, care este proiectata pentru a returna o valoare din lista pe baza unei pozitii a acelei valori.

Sintaxa:

= CHOOSE(index, valoare1, valoare2, …)

Pe baza primului parametru index, returneaza o valoare din lista de valori

De ex.: Choose (2, „Luni”, „Marti”, „Miercuri”, „Joi”, „Vineri”) , returneaza „Marti”

Aplicata setului nostru de date, formula are urmatoarea forma:

= CHOOSE ((C5>0) + (C5>64) + (C5>73) + (C5>85) + (C5>95), „F”,”D”, „C”, „B”, „A”)

Sa presupunem ca C5 contine scoring-ul 88

(C5>0) + (C5>64) + (C5>73) +  (C5>85) + (C5>95)

devine:

True + True + True + True + False, adica: 1+ 1+ 1+ 1+ 0= 4, iar al patrulea element din lista de valori specificata de CHOOSE este „B”

Daca niciunul dintre testele logice nu este TRUE, atunci index este egal cu 0, iar formula returneaza eroarea #VALUE!. O solutie usoara ar fi tratarea acestei erori folosind functia IFERROR, astfel:

     = IFERROR (

          CHOOSE ((C5>0) + (C5>64) + (C5>73) + (C5>85) + (C5>95), „F”,”D”, „C”, „B”, „A”),

                „nu pot calcula”)

Daca C5 contine: sirul gol („”) sau un numar negativ, formula de mai sus intoarce textul „nu pot calcula”)

In poza de mai jos aveti tabelul cu Scoring-ul in care apar toate cele 4 variante de calcul (cu IF-uri imbricate, cu Vlookup de cautare aproximativa, cu IFS si conditii multiple neimbricate, respectiv cu Choose)

6.5 Inlocuirea functiilor IF imbricate cu (o singura) functie SWITCH

Functia SWITCH este disponibila incepand cu versiunea Excel 2016.

In cazul in care avem de evaluat o singur expresie cu o lista de valori predefinite, putem sa folosim functia Switch (in locul IF-urilor imbricate sau a Vlookup cu cautare exacta)

Sintaxa:

=Switch(expresie de evaluat, val1, val2, …)

De pilda, formula cu IF-urile imbricate care calculeaza procentul de indexare pe baza profesiei:

=IF(B8=”inginer”,10%,IF(B8=”asistent”,5%,IF(B8=”manager”,14%,20%)))

Se poate rescrie cu functia Switch, astfel:

=SWITCH(B8, „inginer”, 10%, „asistent”, 5%, „manager”, 14%, „avocat”, 20%)

Expresia de evaluat (B8) nu o mai scriem de fiecare data din moment ce numai pe ea o evaluam.

Switch poate inlocui functiile IF imbricate si chiar solutia VLOOKUP cu cautare exacta (daca din diferite motive nu putem realiza panoul de control)

In poza de mai jos gasiti cele trei variante de luat decizii pe baza listelor de valori predefinite (IF-uri nest, Vlookup cu cautare exacta, respectiv Switch)

Spuneam mai sus ca functia switch este disponibila incepand cu versiunea Excel 2016.

Pentru utilizatorii care folosesc versiuni mai vechi de Excel, va propun mai jos o varianta care simuleaza facilitatile functiei Switch, folosind concatenarea:

=(IF(B8=”inginer”,10%,””) & IF(B8=”asistent”,5%,””) & IF(B8=”manager”,14%,””) & IF(B8=”avocat”,20%,””))*1

Dupa cum ati observat, inmultim rezultatul la final cu 1. Facem asta pentru a converti un sir returnat de concatenare intr-un numar, altfel procentele returnate sunt automat texte (de ex „10%”).

Interesanta abordare, nu? 😊

Deocamdata atat am avut de spus despre utilizarea functiei IF din Excel.

Va multumesc si sper sa va revad pe blog cat mai curand.