Cum să utilizați mai multe instrucțiuni IF în Microsoft Excel

O instrucțiune IF în Excel rulează un test logic care returnează o valoare dacă o condiție este îndeplinită și o altă valoare dacă nu este îndeplinită. Utilizarea unei singure instrucțiuni Excel IF poate găzdui doar operațiuni de bază, dar dacă imbricați mai multe instrucțiuni IF, puteți efectua sarcini complicate. Singurul dezavantaj este că trebuie să fii atent când construiești formula pentru a evita greșelile. Funcțiile alternative Excel pot face aceeași treabă cu mai puțin risc de eroare. Aflați elementele de bază ale instrucțiunilor IF imbricate în Excel și alternativele pentru a găsi cea mai bună abordare pentru nevoile dvs.

Funcția IF din Excel spune în esență „dacă acest lucru este adevărat, returnați valoarea x, dar dacă nu este, returnați valoarea y”. De exemplu, dacă ați verificat pentru a vedea dacă diferite departamente din afacerea dvs au fost sub sau peste buget, ai putea compara cheltuielile cu bugetele lor folosind funcția și ai putea returna „Depășire buget” dacă departamentul a cheltuit prea mult sau „În buget” dacă nu a făcut-o. În general, utilizați operatori de comparație pentru a vă forma declarația, cum ar fi:

• = (egal cu)

• (mai mare ca)

• = (mai mare sau egal cu)

• < (mai mic decât)
• <= (mai mic sau egal cu)
• <> (nu este egal cu)

Înțelegerea sintaxei instrucțiunii Excel IF este un pas crucial către învățarea să o folosească corect. Formatul funcției este IF(test_logic, valoare_dacă_adevărată, [valoare_dacă_fals]) unde parantezele pătrate indică faptul că acest ultim argument este opțional. Acestea sunt ușor de interpretat: The test_logic înseamnă afirmația pe care doriți să o testați, valoare_dacă_adevărată este locul pentru ceea ce doriți să arate Excel dacă testul este adevărat și [value_if_false] este locul opțional pentru ceea ce doriți să afișeze Excel dacă testul este fals.

Acest lucru este suficient pentru a utiliza formula Excel pentru declarațiile dacă-atunci, care poate fi demonstrată folosind exemplul „Depășire buget/în limitele bugetului” de mai devreme. Imaginați-vă că aveți suma cheltuită de diverse departamente în coloana B și suma bugetată în coloana C, cu valorile începând cu al doilea rând. În celula D2, ați tasta =IF(B2>C2, „Peste buget”, „În buget”) pentru a returna rezultatul pe care îl căutați (reținând că trebuie să utilizați ghilimele pentru ca acesta să returneze textul în interior). Prima parte spune „dacă valoarea din B2 este mai mare decât valoarea din C2” (dacă cheltuielile sunt mai mari decât bugetul), a doua parte spune „atunci returnează textul Peste buget iar partea a treia spune „dacă nu, atunci întoarce-te In buget."

A profita la maximum de instrumentul de instrucțiuni Excel IF înseamnă a putea combina mai multe instrucțiuni IF atunci când aveți o sarcină potrivită pentru aceasta. Puteți include o a doua instrucțiune IF în instrucțiunea IF originală și puteți include o altă instrucțiune IF în aceasta și așa mai departe. Acest lucru este util în special dacă doriți să clasificați ceva într-unul din mai multe grupuri folosind o singură formulă. De exemplu, puteți utiliza instrucțiuni IF imbricate pentru a converti scorurile obținute de elevi la un test într-o notă.

Pentru a utiliza mai multe instrucțiuni IF corect, gândiți-vă la logica a ceea ce cereți Excel să facă. În esență, după condiția dvs. (primul argument din funcție), puteți adăuga o altă instrucțiune IF în valoare_dacă_adevărată sau valoare_dacă_falsă spații, pentru a spune Excel ce trebuie să facă în continuare. Gândiți-vă la acest lucru ca a face ca Excel să verifice celula cu un al doilea criteriu dacă celula în cauză nu face acest lucru îndepliniți primul criteriu (sau într-adevăr, dacă îl îndeplinește pe primul), și puteți continua acest proces pentru o lungă perioadă de timp timp. Cu alte cuvinte, transformi „dacă asta, atunci aia” în „dacă asta și asta, atunci aia” sau „dacă nu aceasta, ci aceasta, atunci aceea”.

Imaginați-vă că le-ați oferit studenților un test în care nota de 85% sau mai mare este A, între 70 și 85% este un B, între 55 și 70 la sută este un C, între 40 la sută și 55 la sută este un D și orice este mai mic este un Eșuează. Scorurile sunt în coloana B, de la rândul 2 până la rândul 11. Puteți utiliza o funcție Excel IF construită cu atenție cu mai multe condiții pentru a clasifica scorurile studenților.

Prima parte este ușoară (pentru un scor în B2, în acest exemplu): =IF(B2>=85, „A”) spune Excel să returneze un A dacă studentul a obținut 85 sau mai mult. Imbricarea vă permite să adăugați alte note, folosind al treilea argument (ce trebuie să faceți dacă condiția este falsă) pentru a adăuga condiții suplimentare: =IF(B2>=85, „A”, DACA(B2>=70, „B”)), care îi spune Excel să returneze un A pentru scoruri de 85 sau mai mari, dar dacă nu, returnează un B dacă scorul este peste 70. Continuați în acest fel pentru a completa formula, folosind al treilea argument pentru o nouă instrucțiune IF de fiecare dată: =IF(B2>=85, „A”, IF(B2>=70, „B”,IF(B2>=55, „C”, IF(B2>=40, „D”, „Eșuează”)) ))

Acest lucru pare complicat, așa că defalcarea ar trebui să cimenteze ideea. Primele două argumente spun „dacă scorul în B2 este de 85 sau mai mult, atunci studentul primește un A”, iar al treilea spune, „în caz contrar, verificați următoarea condiție”. The al doilea IF conduce la această afirmație, „(pentru studenții care au un scor mai mic de 85) dacă scorul în B2 este de 70 sau mai mare, atunci studentul primește un B și, dacă nu, verificați următoarea condiție." Al treilea IF spune "(pentru studenții care au un scor mai mic de 70) dacă scorul în B2 este de 55 sau mai mare, studentul care a obținut un punctaj primește un C și, dacă nu, bifați următoarea condiție” iar a patra spune „(pentru studenții care au un punctaj mai mic de 55) dacă scorul în B2 este mai mare de 40, studentul primește un D, ​​dacă nu, studentul eșuează.”

Cu condiția să introduceți corect formula pentru declarația dvs. IF, aceasta va returna una dintre opțiunile pe care le-ați oferit pentru Excel. Cu toate acestea, dacă faceți o greșeală, formula poate returna a 0 sau #NUME? în celula care conține formula dvs.

A 0 eroare înseamnă că fie valoare_dacă_adevărată sau valoare_dacă_falsă argumentul este gol. Remediați această problemă asigurându-vă că o valoare este introdusă în ambele argumente, sau cel puțin, în valoare_dacă_adevărată argument. The valoare_dacă_falsă argumentul poate fi eliminat complet și va returna FALSE dacă nu puneți o a doua virgulă după valoare_dacă_adevărată camp.

A #NUME? eroarea vă spune că probabil ați scris greșit sau ați introdus greșit ceva în formulă. Acest lucru ar putea fi puțin mai greu de rezolvat, deoarece ar putea fi orice parte a formulei, dar cel mai bun sfat este să verificați totul sau să scrieți din nou formula.

Exemplul anterior de formulă a funcției IF imbricate a devenit destul de lung, ceea ce este unul dintre dezavantajele majore ale utilizării mai multor instrucțiuni IF în Excel. Ele pot deveni complicate și este ușor să greșești, deoarece trebuie să urmezi logica cu atenție pentru a te asigura că totul funcționează așa cum ți-ai propus. Microsoft subliniază că o formulă imbricată incorect poate chiar returna rezultatul corect de cele mai multe ori, dar rezultate incorecte în rare ocazii. Acestea sunt greu de observat.

A nu pune condițiile în ordinea corectă este o greșeală comună. În exemplul testelor gradate, dacă ați început cu scorul mai mic =IF(B2>=40, „D”, …” și a trecut la listarea celorlalți în ordine crescătoare, chiar și un scor de 100 ar returna un D. Acest lucru se datorează faptului că prima condiție este verificată mai întâi, așa că dacă ați început cu „dacă celula B2 este de 40 sau mai mare, returnați un D" ar clasifica orice scor de 40 sau peste drept D fără a verifica altceva, deoarece îndeplinește primul condiție. Acesta este un exemplu perfect de ce trebuie să urmezi logica formulei tale.

De asemenea, trebuie să vă asigurați că adăugați numărul corect de paranteze la sfârșitul mai multor instrucțiuni IF pentru a închide corect formula. Excel vă ajută prin coordonarea culorilor și evidențierea parantezelor în timp ce lucrați.

Din punct de vedere tehnic, puteți imbrica până la 64 de funcții IF într-o singură formulă, dar Microsoft nu recomandă utilizarea acestei funcții din motivele de mai sus. Lucrurile se complică rapid și șansele sunt că, dacă încercați să încadrați mai mult de 10 instrucțiuni IF, probabil că există deja un instrument mai bun pentru munca inclusă în Excel.

Cea mai convenabilă alternativă la funcția IF este funcția IFS, care este o versiune a funcție care îndeplinește aceeași sarcină ca imbricarea într-o singură funcție și poate gestiona până la 127 conditii. Funcția IFS are o sintaxă mai simplă: =IFS(test_logic1, valoare_dacă_adevărat1, [test_logic2, valoare_dacă_adevărat2], [test_logic3, valoare_dacă_adevărat3]...) si asa mai departe. În esență, aceasta funcționează ca instrucțiuni IF imbricate, cu excepția instrucțiunilor IF ulterioare ocupă spațiul de după „valoarea dacă este adevărată” fără a fi nevoie să afișeze din nou funcția.

Formula din exemplul de calificare a testului poate fi scrisă mult mai simplu cu funcția IFS. In loc de:

=IF(B2>=85, „A”, IF(B2>=70, „B”,IF(B2>=55, „C”, IF(B2>=40, „D”, „Eșuează”)) ))

pur și simplu scrie

=IFS(B2>=85, „A”, B2>=70, „B”, B2>=55, „C”, B2>=40, „D”, TRUE, „Eșuat”)

The ADEVĂRAT în locul dinaintea ultimului spune „dacă nici una dintre celelalte valori nu este îndeplinită, atunci returnează următoarea valoare”.

Această caracteristică este disponibilă numai în Excel 2019 sau Excel pentru Office 365.

Funcția CĂUTARE V din Excel este adesea o alternativă mai simplă la mai multe instrucțiuni IF și este disponibilă pe orice versiune de Excel începând cu 2007. Nu este ideal pentru toate situațiile, deoarece fie returnează rezultate pentru o potrivire exactă, fie pentru cel mai apropiat număr, deci nu a putut fi folosit pentru note precum exemplul anterior. Cu toate acestea, în multe situații, vă poate economisi multă muncă scrierea unei formule, deși trebuie să construiți un tabel cu valori posibile într-unul. coloana, cum ar fi produsele pe care le vinde magazinul dvs. și ceea ce doriți să returneze funcția, cum ar fi prețul produsului, într-o altă coloană, pentru exemplu.

Sintaxa pentru VLOOKUP este =CĂUTAREV(valoare_căutare, matrice_tabelă, număr_index_col, [căutare_gamă]) iar învățarea fiecăreia dintre componente vă arată cum să o utilizați. The valoare_căutare argumentul este valoarea pe care doriți să o verificați, care trebuie să fie o celulă în aceeași coloană cu începutul tabelului cu care doriți să o verificați, fie deasupra, fie dedesubtul acesteia. The table_array este gama de celule care conțin tabelul dvs. de informații, cum ar fi produsele și prețurile lor corespunzătoare și col_index_num este coloana în care doriți să caute Excel pentru a găsi valoarea cu care să returnați 1 indicând coloana din stânga, 2 indicându-l pe următorul și așa mai departe. In cele din urma, [cautare_gamă] este opțional, dar introduceți „TRUE” dacă doriți ca Excel să bazeze răspunsul pe cea mai apropiată valoare sau „FALSE” dacă doriți doar o potrivire exactă.