Funcția IF în Excel. Exemple (cu mai multe condiții)

Unul dintre principalele avantaje ale foilor de calcul Excel este capacitatea de a programa funcționalitatea unui anumit document. După cum știu majoritatea oamenilor de la lecțiile de informatică de la școală, una dintre componentele principale care vă permite să puneți acest lucru în practică sunt operatorii logici. Unul dintre ele este operatorul IF, care prevede executarea anumitor acțiuni atunci când sunt îndeplinite anumite condiții. 

De exemplu, dacă valoarea se potrivește cu o anumită, atunci o etichetă este afișată în celulă. Dacă nu, e diferit. Să ne uităm la acest instrument eficient mai detaliat în practică.

Funcția IF în Excel (informații generale)

Orice program, chiar dacă este mic, conține în mod necesar o secvență de acțiuni, care se numește algoritm. Poate arăta astfel:

1. Verificați întreaga coloană A pentru numere pare.
2. Dacă se găsește un număr par, adăugați astfel de valori.
3. Dacă nu este găsit un număr par, atunci afișați inscripția „negăsit”.
4. Verificați dacă numărul rezultat este par. 
5. Dacă da, adăugați-l la toate numerele pare selectate în paragraful 1.

Și chiar dacă aceasta este doar o situație ipotetică, care este puțin probabil să fie necesară în viața reală, executarea oricărei sarcini implică în mod necesar prezența unui algoritm similar. Înainte de a utiliza funcția DACĂ, trebuie să ai o idee clară în cap despre ce rezultat vrei să obții. 

Sintaxa funcției IF cu o singură condiție

Orice funcție din Excel este efectuată folosind o formulă. Modelul prin care datele trebuie transmise unei funcții se numește sintaxă. În cazul operatorului IF, formula va fi în acest format.

=IF (expresie_logică, valoare_dacă_adevărată, valoare_dacă_falsă)

Să ne uităm la sintaxa mai detaliat:

1. Expresie booleană. Aceasta este condiția în sine, a cărei conformitate sau nerespectare verifică Excel. Pot fi verificate atât informațiile numerice, cât și textuale.
2. Valoare_dacă_adevărat. Rezultatul care va fi afișat în celulă dacă datele verificate îndeplinesc criteriile specificate.
3. valoare_dacă_falsă. Rezultatul care este afișat în celulă dacă datele verificate nu se potrivesc cu condiția.

Iată un exemplu pentru claritate.

Aici funcția compară celula A1 cu numărul 20. Acesta este primul paragraf al sintaxei. Dacă conținutul este mai mare decât această valoare, valoarea „mai mare decât 20” este afișată în celula în care a fost scrisă formula. Dacă situația nu corespunde acestei condiții – „mai puțin sau egal cu 20”.

Dacă doriți să afișați o valoare de text într-o celulă, trebuie să o încadrați între ghilimele.

Iată o altă situație. Pentru a fi eligibili pentru a susține o sesiune de examene, studenții trebuie să promoveze o sesiune de teste. Elevii au reușit să câștige credite la toate disciplinele, iar acum rămâne ultima, care s-a dovedit a fi decisivă. Sarcina noastră este să stabilim care dintre studenți sunt admiși la examene și care nu.

Deoarece vrem să verificăm text și nu un număr, primul argument este B2=”cons.”.

Sintaxa funcției IF cu condiții multiple

Adesea, un criteriu nu este suficient pentru a verifica valoarea. Dacă trebuie să luați în considerare mai multe opțiuni, puteți imbrica funcții IF unul în celălalt. Vor exista mai multe funcții imbricate.

Pentru a fi mai clar, iată sintaxa.

=IF(expresie_logică, valoare_dacă_adevărată, IF(expresie_logică, valoare_dacă_adevărată, valoare_dacă_fals))

În acest caz, funcția va verifica două criterii simultan. Dacă prima condiție este adevărată, se returnează valoarea obținută ca urmare a operației din primul argument. Dacă nu, al doilea criteriu este verificat pentru conformitate.

Iată un exemplu.

Și cu ajutorul unei astfel de formule (prezentată în captura de ecran de mai jos), puteți analiza performanța fiecărui elev.

După cum puteți vedea, aici a fost adăugată încă o condiție, dar principiul nu s-a schimbat. Deci, puteți verifica mai multe criterii simultan.

Cum se extinde funcționalitatea IF folosind operatori AND și OR

Din când în când există situația de a verifica imediat respectarea mai multor criterii și de a nu folosi operatori imbricați logici, ca în exemplul anterior. Pentru a face acest lucru, utilizați fie funcția И sau funcție OR în funcție de dacă trebuie să îndepliniți mai multe criterii deodată sau cel puțin unul dintre ele. Să aruncăm o privire mai atentă asupra acestor criterii.

Funcția IF cu condiția AND

Uneori trebuie să verificați o expresie pentru mai multe condiții simultan. Pentru aceasta se folosește funcția AND, scrisă în primul argument al funcției IF. Funcționează astfel: dacă a este egal cu unu și a este egal cu 2, valoarea va fi c.

Funcția IF cu condiția „SAU”.

Funcția SAU funcționează într-un mod similar, dar în acest caz, doar una dintre condiții este adevărată. Pe cât posibil, până la 30 de condiții pot fi verificate în acest fel. 

Iată câteva modalități de utilizare a funcțiilor И и OR ca argument al funcției IF.

Compararea datelor din două tabele

Din când în când, este posibil să comparați două tabele similare. De exemplu, o persoană lucrează ca contabil și trebuie să compare două rapoarte. Există și alte sarcini similare, cum ar fi compararea costului mărfurilor din diferite loturi, apoi, evaluările studenților pentru diferite perioade și așa mai departe.

Pentru a compara două tabele, utilizați funcția COUNTIF. Să ne uităm la asta mai detaliat.

Să presupunem că avem două tabele care conțin specificațiile a două procesoare de bucătărie. Și trebuie să le comparăm și să evidențiem diferențele cu culoarea. Acest lucru se poate face folosind formatarea condiționată și funcția COUNTIF. 

Masa noastră arată așa.

Selectam gama corespunzatoare caracteristicilor tehnice ale primului robot de bucatarie.

După aceea, faceți clic pe următoarele meniuri: Formatare condiționată – creați o regulă – utilizați o formulă pentru a determina celulele formatate.

Sub forma unei formule de formatare, scriem funcția =COUNTIF (interval de comparat; prima celulă a primului tabel)=0. Tabelul cu caracteristicile celui de-al doilea robot de bucătărie este folosit ca interval de comparație.

Trebuie să vă asigurați că adresele sunt absolute (cu semnul dolarului în fața numelor rândurilor și coloanelor). Adăugați =0 după formulă, astfel încât Excel să caute valori exacte.

După aceea, trebuie să setați formatarea celulelor. Pentru a face acest lucru, lângă eșantion, trebuie să faceți clic pe butonul „Format”. În cazul nostru, folosim umplutura, deoarece este cea mai convenabilă în acest scop. Dar puteți alege orice format doriți.

Am atribuit un nume de coloană ca interval. Acest lucru este mult mai convenabil decât introducerea manuală în interval.

Funcția SUMIF în Excel

Acum să trecem la funcții IF, care va ajuta la înlocuirea a două puncte ale algoritmului simultan. Primul este SUMMESLEY, care adună două numere care îndeplinesc o anumită condiție. De exemplu, ne confruntăm cu sarcina de a determina câți bani ar trebui plătiți pe lună tuturor vânzătorilor. Pentru aceasta este necesar.

1. Adăugați un rând cu veniturile totale ale tuturor vânzătorilor și faceți clic pe celula care va conține rezultatul după introducerea formulei. 
2. Găsim butonul fx, care se află lângă linia pentru formule. În continuare, va apărea o fereastră în care puteți găsi funcția necesară prin căutare. După selectarea operatorului, trebuie să faceți clic pe butonul „OK”. Dar introducerea manuală este întotdeauna posibilă.
   

   

3. În continuare, va apărea o fereastră pentru introducerea argumentelor funcției. Toate valorile pot fi specificate în câmpurile corespunzătoare, iar intervalul poate fi introdus prin butonul de lângă ele.
   

   

4. Primul argument este un interval. Aici introduceți celulele pe care doriți să le verificați pentru conformitatea cu criteriile. Dacă vorbim despre noi, acestea sunt posturile angajaților. Introduceți intervalul D4:D18. Sau doar selectați celulele de interes.
5. În câmpul „Criterii”, introduceți poziția. În cazul nostru – „vânzător”. Ca interval de însumare, indicăm acele celule în care sunt listate salariile angajaților (acest lucru se face atât manual, cât și le selectăm cu mouse-ul). Faceți clic pe „OK” și obținem salariile calculate finite ale tuturor angajaților care sunt vânzători.

De acord că este foarte convenabil. Nu-i asa?

Funcția SUMIFS în Excel

Această funcție vă permite să determinați suma valorilor care îndeplinesc mai multe condiții. De exemplu, ni s-a dat sarcina de a stabili salariul total al tuturor managerilor care lucrează în filiala de sud a companiei.

Adăugați un rând unde va fi rezultatul final și introduceți formula în celula dorită. Pentru a face acest lucru, faceți clic pe pictograma funcției. Va apărea o fereastră în care trebuie să găsiți funcția SUMMESLIMN. Apoi, selectați-l din listă și se deschide fereastra familiară cu argumente. Dar numărul acestor argumente este acum diferit. Această formulă face posibilă utilizarea unui număr infinit de criterii, dar numărul minim de argumente este de cinci. 

Doar cinci pot fi specificate prin dialogul de introducere a argumentelor. Dacă aveți nevoie de mai multe criterii, atunci acestea vor trebui introduse manual după aceeași logică ca primele două.

Să ne uităm la argumentele principale mai detaliat:

1. Interval de însumare. Celulele de însumat.
2. Interval de condiție 1 – intervalul care va fi verificat pentru conformitatea cu un anumit criteriu. 
3. Condiția 1 este condiția în sine.
4. Intervalul de criterii 2 este al doilea interval care va fi verificat în raport cu criteriul.
5. Condiția 2 este a doua condiție.

Logica suplimentară este similară. Ca urmare, am stabilit salariile tuturor managerilor Filialei de Sud.

Funcția COUNTIF în Excel

Dacă trebuie să determinați câte celule se încadrează sub un anumit criteriu, utilizați funcția COUNTIF. Să presupunem că trebuie să înțelegem câți agenți de vânzări lucrează în această organizație:

1. Mai întâi, adăugați o linie care conține numărul de vânzători. După aceea, trebuie să faceți clic pe celula în care va fi afișat rezultatul.
2. După aceea, trebuie să faceți clic pe butonul „Inserare funcție”, care poate fi găsit în fila „Formule”. Va apărea o fereastră cu o listă de categorii. Trebuie să selectăm elementul „Lista alfabetică completă”. În listă, ne interesează formula COUNTIF. După ce îl selectăm, trebuie să facem clic pe butonul „OK”.
   

   

3. După aceea, avem numărul de vânzători angajați în această organizație. A fost obținut prin numărarea numărului de celule în care este scris cuvântul „vânzător”. Totul este simplu. 

Funcția COUNTSLIM în Excel

Similar cu formula SUMMESLIMN, această formulă numără numărul de celule care se potrivesc cu mai multe condiții. Sintaxa este similară, dar ușor diferită de formulă SUMMESLIMN:

1. Interval de condiție 1. Acesta este intervalul care va fi testat în funcție de primul criteriu.
2. Condiția 1. Direct primul criteriu.
3. Interval de condiție 2. Acesta este intervalul care va fi testat în funcție de al doilea criteriu. 
4. Condiția 2.
5. Condiții de gamă 3.

Și așa mai departe.

Deci funcția IF în Excel – nu singurul, există mai multe dintre soiurile sale care efectuează automat cele mai comune acțiuni, ceea ce simplifică foarte mult viața unei persoane. 

În mare parte datorită funcției IF Foile de calcul Excel sunt considerate programabile. Este mai mult decât un simplu calculator. Dacă te gândești la asta, atunci funcția IF este o piatră de temelie în orice fel de programare.

Deci, dacă înveți cum să lucrezi cu cantități mari de date în Excel, va fi mult mai ușor să înveți programarea. Datorită operatorilor logici, aceste zone chiar au multe în comun, deși Excel este folosit mai des de contabili. Dar mecanismul de lucru cu date este în mare parte același. 

Funcție în mâinile drepte IF iar variațiile sale vă permit să transformați o foaie Excel într-un program cu drepturi depline care poate acționa pe algoritmi complecși. Înțelegerea modului în care funcționează funcția IF este primul pas către învățarea macrocomenzilor – următorul pas în munca mai flexibilă cu foi de calcul. Dar acesta este deja un nivel mai profesional.