Defecțiuni 

Cum se scrie o condiție în Excel. Cum să utilizați funcția if în excel - instrucțiuni pas cu pas (2018). Funcții ȘI, SAU, NU

Funcția IF(). , versiunea în limba engleză a IF(), folosit la verificarea conditiilor. De exemplu, =IF(A1>100;"Buget depășit";"OK!"). În funcție de valoarea din celulă A1 Rezultatul formulei va fi fie „Buget depășit”, fie „OK!”.

Funcția IF() este una dintre cele mai frecvent utilizate funcții.

Sintaxa funcției

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

Expresie logică- orice valoare sau expresie care se evaluează la TRUE sau FALSE.
=IF(A1>=100;"Buget depășit";"OK!")
Aceste. dacă într-o celulă A1 conține o valoare mai mare sau egală cu 100, atunci formula va reveni BINE!, iar dacă nu, atunci Buget depășit.

Formulele pot fi folosite ca argumente de funcție, de exemplu:
=DACĂ(A1>100,SUMA(B1:B10),SUMA(C1:C10))
Aceste. dacă într-o celulă A1 conține o valoare >100, apoi însumarea se efectuează peste coloană B , iar dacă mai puțin, atunci pe coloană CU .

IF imbricate

În EXCEL 2007 ca valori de argument valoare_dacă_adevăratăŞi valoare_dacă_falsă Puteți utiliza până la 64 de funcții IF() imbricate pentru a construi verificări mai complexe.
=IF(A1>=100;"Buget depășit";IF(A1>=90,"Proiect mare";IF(A1>=50,"Proiect mediu","Proiect mic")))

VIEW(A1,(0,50,90,100);("Proiect mic","Proiect mediu","Proiect mare","Buget depășit"))

CĂUTARE V(A1;A3:B6;2)

Pentru funcția VLOOKUP() trebuie să creați în interval A3:B6 tabel de valori:

Dacă trebuie să te retragi text diferit dacă este prezent în celulă A1 valoare negativă, valoare pozitivă sau 0, atunci puteți scrie următoarea formulă:

VEZI(A1,(-1E+307,0,1E-307);("<0";"=0";">0"})

sau, în schimb, dacă este necesar valorile textului afișați formule, puteți utiliza link-uri către o serie de celule (care conțin formule)

VEZI (A24,(-1E+307,0,1E-307),A27:A29)(vezi exemplu de fișier)

Al treilea argument a fost omis [value_if_false]

Al treilea argument al funcției este opțional dacă este omis, funcția va returna FALSE (dacă condiția nu este îndeplinită).
=IF(A1>100,"Buget depășit")
Dacă într-o celulă A1 conține valoarea 1, atunci formula de mai sus va returna FALSE.

În loc de TRUE sau FALSE, se introduce un număr în primul argument

Deoarece valoarea FALSE este echivalentă cu 0, apoi formulele
=IF(0;"Buget depășit";"OK!")
sau (dacă se află în celulă A1 conține valoarea 0)
=IF(A1,"Buget depășit","OK!")
va fi returnat BINE!

Dacă într-o celulă A1 este orice alt număr decât 0, atunci formula va reveni Buget depășit. Această abordare este convenabilă atunci când se verifică dacă o valoare este egală cu zero.

Conectarea funcției IF() cu alte funcții folosind condiții

EXCEL conține și alte funcții pe care le puteți utiliza pentru a analiza datele folosind condiții. De exemplu, pentru a număra numărul de apariții ale numerelor dintr-un interval de celule, utilizați funcția COUNTIF() și pentru a adăuga valori care îndeplinesc anumite condiții, utilizați funcția SUMIF().

IF() funcția ca varianta alternativa, poate fi folosit și pentru a număra și adăuga valori folosind condiții. Mai jos sunt exemple ilustrative.

Lasă datele să fie într-un interval A6:A11 (vezi exemplu de fișier)

Funcția logică DACĂîn Excel - una dintre cele mai populare. Returnează un rezultat (o valoare sau altă formulă) în funcție de condiție.

Funcția IF în Excel

Funcția are următoarea sintaxă.

DACĂ( expresie_log; valoare_dacă_adevărată; [value_if_false])

expresie_log este o condiție care trebuie verificată. De exemplu, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

valoare_dacă_adevărată – o valoare sau o formulă care este returnată atunci când are loc evenimentul specificat în primul parametru.

valoare_dacă_falsă este o valoare sau o formulă alternativă care este returnată dacă o condiție nu este îndeplinită. Acest câmp nu este obligatoriu să fie completat. În acest caz, când are loc un eveniment alternativ, funcția va returna FALSE.

Un exemplu foarte simplu. Trebuie să verificați dacă vânzările de produse individuale depășesc 30 de unități. sau nu. Dacă depășesc, atunci formula ar trebui să returneze „Ok”, în caz contrar – „Șterge”. Mai jos este calculul cu rezultatul.

Vânzările primului produs sunt 75, adică. este îndeplinită condiția ca acesta să fie mai mare de 30. Prin urmare, funcția returnează ceea ce este specificat în câmpul următor - „Ok”. Vânzările celui de-al doilea produs sunt mai mici de 30, deci condiția (>30) nu este îndeplinită și se returnează valoarea alternativă specificată în al treilea câmp. Acesta este punctul central al funcției IF. Prin extinderea calculului în jos, obținem rezultatul pentru fiecare produs.

Totuși, acesta a fost un demo. Mai des, formula Excel IF este utilizată pentru verificări mai complexe. Să presupunem că există vânzări săptămânale medii de bunuri și soldurile lor curente. Cumpărătorul trebuie să facă o prognoză a soldurilor în 2 săptămâni. Pentru a face acest lucru, trebuie să scazi de două ori vânzările săptămânale medii din stocurile curente.

Până acum totul este logic, dar dezavantajele sunt confuze. Există solduri negative? Desigur că nu. Stocurile nu pot fi sub zero. Pentru ca prognoza să fie corectă, valorile negative trebuie înlocuite cu zerouri. Formula IF este de mare ajutor aici. Va verifica valoarea prezisă și dacă este mai mică de zero, va da cu forță răspunsul 0, altfel va da rezultatul calculului, adică. un număr pozitiv. În general, aceeași logică, dar în loc de valori folosim o formulă ca condiție.

Nu mai există valori negative în prognoza de inventar, ceea ce este în general foarte bun.

Formulele Excel IF sunt, de asemenea, utilizate pe scară largă în formulele matrice. Nu vom merge prea adânc aici. Pentru cei interesați, recomand să citească articolul despre. Adevărat, calculul din acel articol nu mai este relevant, pentru că Excel 2016 a introdus funcțiile MINESLI și MAXESLI. Dar este foarte util să ne uităm la exemplu – va fi util într-o altă situație.

Formula IF în Excel - exemple de mai multe condiții

Destul de des, numărul de condiții posibile nu este 2 (testabil și alternativ), ci 3, 4 sau mai mult. În acest caz, puteți folosi și funcția IF, dar acum va trebui să o cuibărați unul în celălalt, specificând pe rând toate condițiile. Luați în considerare următorul exemplu.

Mai mulți manageri de vânzări trebuie să li se acorde un bonus în funcție de implementarea planului de vânzări. Sistemul de motivare este următorul. Dacă planul este îndeplinit cu mai puțin de 90%, atunci nu se datorează niciun bonus, dacă de la 90% la 95% - un bonus de 10%, de la 95% la 100% - un bonus de 20%, iar dacă planul este depășit, atunci 30%. După cum puteți vedea aici, există 4 opțiuni. Pentru a le indica într-o singură formulă, este necesară următoarea structură logică. Dacă prima condiție este îndeplinită, atunci apare prima opțiune, în caz contrar, dacă a doua condiție este îndeplinită, atunci apare a doua opțiune, în caz contrar dacă... etc. Numărul de condiții poate fi destul de mare. La sfârșitul formulei, este indicată ultima alternativă pentru care nu este îndeplinită niciuna dintre condițiile enumerate anterior (ca al treilea câmp dintr-o formulă IF obișnuită). Ca rezultat, formula arată astfel:

Combinația de funcții IF funcționează în așa fel încât atunci când este îndeplinită orice condiție specificată, următoarele nu mai sunt verificate. Prin urmare, este important să le indicați în ordinea corectă. Dacă am început să verificăm cu B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

Este ușor să fii confuz când scrii o formulă, așa că este o idee bună să te uiți la sfatul instrument.

La sfârșit, trebuie să închideți toate parantezele, altfel Excel va arunca o eroare.

Funcția IF din Excel își face, în general, treaba bine. Dar opțiunea când trebuie să scrieți un lanț lung de condiții nu este foarte plăcută, deoarece, în primul rând, nu este întotdeauna posibil să o scrieți prima dată (fie condiția este indicată incorect, fie paranteza nu poate fi închisă); în al doilea rând, dacă este necesar, înțelegerea unei astfel de formule poate fi dificilă, mai ales când există multe condiții și calculele în sine sunt complexe.

MS Excel 2016 a adăugat o funcție IFS, de dragul căruia a fost scris întreg acest articol. Acesta este același IF, doar ascuțit special pentru a testa multe condiții. Acum nu mai trebuie să scrieți IF de o sută de ori și să numărați parantezele deschise. Este suficient să enumerați condițiile și să închideți un parantez la sfârșit.

Funcționează după cum urmează. Să luăm exemplul de mai sus și să folosim noua formulă IFS a Excel.

După cum puteți vedea, scrierea formulei pare mult mai simplă și mai clară.

Merită să fiți atenți la următoarele. Încă listăm termenii în ordinea corectă pentru a evita suprapunerea inutilă a intervalului. Ultima condiție alternativă, spre deosebire de IF obișnuită, trebuie de asemenea specificată. IF specifică doar o valoare alternativă care apare dacă niciuna dintre condițiile enumerate nu este îndeplinită. Aici trebuie să indicați condiția în sine, care în cazul nostru ar fi B2>=1. Totuși, acest lucru poate fi evitat prin scrierea TRUE în câmpul de condiție, indicând că dacă condițiile enumerate anterior nu sunt îndeplinite, apare TRUE și se returnează ultima valoare alternativă.

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

Pentru a face acest lucru, trebuie să faceți următoarele:

  1. Faceți clic pe pictograma de inserare a formulei (Fx).
  2. În meniul care apare, selectați „IF”.
  1. Ca rezultat, veți vedea o fereastră în care trebuie să specificați argumentele funcției:
    • expresie booleană;
    • valoarea dacă este adevărată;
    • valoare dacă este falsă.
  2. După introducerea valorilor în aceste câmpuri, în dreapta acestora va fi afișat un rezultat preliminar.
  3. Pentru a introduce, faceți clic pe butonul „OK”.

Exemple

Să ne uităm la câteva expresii booleene diferite pentru a vă ajuta să înțelegeți cum să utilizați această funcție.

  1. Faceți activă celula „A1”.
  2. Repetați pașii descriși mai sus pentru a deschide fereastra de inserare „Argumente”.
  3. Introduceți următorul cod în câmpul de stare.
C1=””

Sensul acestei expresii este următorul: dacă celula C1 conține un gol. Adică nu este nimic în ea.

  1. În câmpul pentru adevăr, introduceți următoarele.
„Celula C1 este goală”

Acesta este textul care va fi afișat dacă această condiție este îndeplinită.

  1. În câmpul „dacă fals”, introduceți următoarele.
„Celula C1 nu este goală”

Vom vedea acest text dacă condiția este falsă.

  1. Pentru a introduce formula noastră, faceți clic pe butonul „OK”.
  1. Ca urmare a acestui fapt, vom vedea următoarele (deoarece celula este goală, mesajul este adecvat).
  1. Introdu orice text în celula C.
  1. După ce faceți clic pe butonul Enter, veți vedea următorul rezultat.

După cum puteți vedea, editorul Excel a produs mesajul pe care l-am indicat în cazul unui rezultat fals al afecțiunii.

Operatori de comparație

Mai jos vedeți o listă de operatori de comparație:

Operatori aritmetici

Și acesta este un tabel de operatori aritmetici:

Puteți citi mai multe despre operatorii aritmetici și prioritatea lor în Excel în ajutorul online de pe site-ul Microsoft.

Condiții multiple

Dacă doriți, puteți utiliza un număr mare de expresii booleene. Totul funcționează exact la fel. Să ne uităm la un exemplu de condiție mai complexă.

Vom crea formula folosind bara de instrumente. Acest lucru este mult mai convenabil decât a scrie totul pe o singură linie, deoarece este foarte ușor să fii confuz. Mai ales la început, când tocmai înveți.

Pentru a face acest lucru, trebuie să efectuați următorii pași.

  1. Accesați fila „Formule”. Faceți clic pe butonul „Inserare funcție”.
  1. Ca rezultat, întregul conținut al celulei va fi înlocuit automat în „constructor”.
  1. Ștergeți totul din câmpul „Value_if_false”.
  1. Acum o nouă expresie logică va fi scrisă în această linie. Adică vom avea o nouă condiție imbricată. Mai întâi, introduceți condiția „Dacă valoarea celulei C1 este mai mare de 1000”. Pentru a face acest lucru, introduceți următorul cod. Asigurați-vă că includeți un punct și virgulă la sfârșit.
IF(C1>1000;

Acordați o atenție deosebită aspectului actual al tastaturii. Mulți oameni greșesc și introduc litera rusă C în loc de C engleză. Din punct de vedere vizual, nu veți vedea diferența, dar pentru editor acest lucru este foarte important. În acest caz, nimic nu va funcționa.

  1. Acum să adăugăm un mesaj care va fi afișat dacă numărul din celula C1 este mai mare de 1000.

Toate valorile textului trebuie introduse între ghilimele.

  1. Acum, exact în același mod, introducem valoarea dacă această condiție nu este îndeplinită.
  1. În cele din urmă, faceți clic pe butonul „OK”.
  1. Ca urmare a acestui fapt, vedem un mesaj care spune că numărul introdus este mai mare de 1000.
  1. Ștergeți conținutul celulei C. Rezultatul este următorul.

Am testat toate cele trei rezultate posibile. Totul funcționează grozav.

Copierea unei funcții în tabele

Uneori se întâmplă ca expresia logică introdusă să fie duplicată pe mai multe rânduri. În unele cazuri, trebuie să duplicați mult. Această automatizare este mult mai convenabilă decât verificarea manuală.

Să ne uităm la un exemplu de copiere pe un tabel de bonusuri pentru angajați în perioada sărbătorilor. Pentru a face acest lucru, trebuie să faceți următorii pași.

  1. Creați tabelul corespunzător.
  1. Faceți clic pe o celulă goală din primul rând și selectați „Fx” în câmpul de introducere.
  1. În fereastra care apare, selectați funcția „IF” și faceți clic pe butonul „OK”.
  1. În primul câmp, introduceți următoarea condiție.
C6=”M”

Astfel verificăm dacă angajatul este bărbat.

  1. Dacă este adevărat, introduceți un număr. De exemplu, 3000. Dacă condiția este falsă, introduceți 0. Aceasta înseamnă că fetelor nu trebuie să li se acorde un bonus. Pentru a introduce, faceți clic pe butonul „OK”.
  1. Apoi, plasați cursorul peste colțul din dreapta jos al celulei. După ce cursorul își schimbă aspectul în „plus negru”, fără a elibera degetul, trageți-l până la ultima linie.
  1. Rezultatul va fi următorul.
  1. Acum copiați complet întregul tabel (folosind comanda rapidă de la tastatură Ctrl + C și Ctrl + V). Schimbați titlul în 8 martie.
  1. Du-te la prima celulă. Faceți clic pe câmpul de introducere a formulei.
  1. Schimbați litera „M” în „F”.
  1. Acum duplicați funcția până în partea de jos.
  1. Rezultatul va fi următorul.

Aici vedem că s-a întâmplat total invers. Aceasta înseamnă că totul funcționează corect.

Utilizarea operatorilor suplimentari

Pe lângă expresiile și comparațiile aritmetice, puteți utiliza și operatorii AND și OR. Să le aruncăm o privire mai atentă, deoarece datorită lor, capacitățile funcției „IF” sunt extinse semnificativ.

În primul rând, creați un tabel care va avea mai multe câmpuri prin care puteți compara rânduri. În cazul nostru, folosind câmpul „Statut de angajat”, vom verifica cine trebuie să plătească bani și cine nu.

Operator AND

Vom folosi vechea funcție în care am verificat dacă angajatul este bărbat. Acum trebuie să faceți următoarele modificări.

  1. Adăugați o paranteză la condiție și litera „I” de lângă ea.
  2. Vechea condiție va fi prima, iar a doua va fi după punct și virgulă.
=IF(AND(C35="M";D35="Personal principal");3000;0)
  1. Duplicați această formulă până la capăt.
  1. Drept urmare, vedem că acei angajați care nu fac parte din personalul de bază vor fi lipsiți de bonusuri pentru sărbători. Chiar dacă sunt bărbați.

Operator Or

Același efect poate fi obținut folosind operatorul SAU.

Pentru a face acest lucru, faceți următoarele modificări formulei:

  1. Schimbați litera „F” în „M”.
  2. Schimbați statutul de angajat în „Lucrător cu normă parțială”.
  3. Pune 0 în câmpul de adevăr și 3000 pentru un eveniment fals.
  1. Să duplicăm formula până la ultima linie.
  1. Rezultatul a fost exact același. Cert este că operatorii „ȘI” și „SAU” sunt exact opuși unul față de celălalt. Prin urmare, este foarte important să indicați corect valorile în câmpurile adevărat și fals. Nu te înșela.
  1. Pentru a verifica, puteți schimba statutul unui angajat în „Personal principal”.
  2. Imediat după aceasta, veți vedea că în dreptul numelui său de familie va apărea numărul 3000.

Funcția SUMIF

Cu siguranță mulți dintre voi ați făcut calcule în Excel. Dar există momente când trebuie să numărați nu toate rândurile dintr-un tabel, ci doar unele care îndeplinesc o anumită condiție.

De exemplu, să luăm un tabel care listează diverse produse, costul, cantitatea și starea acestora. Calcularea sumei totale este ușor. Dar cum poți afla numărul de produse vândute?


Informații generale despre IF

Funcția IF este una dintre cele mai populare funcții din Excel. În limba engleză Excel, precum și în Google Sheets, LibreOffice, OpenOffice, această funcție se numește IF. IF (IF) se referă la funcții logice.

Nivel de dificultate pe scara BRP ADVICE - 2 din 7. Fiecare IF imbricat dublează complexitatea formulei.

IF (IF) vă permite să construiți un arbore de decizie, adică dacă o condiție este îndeplinită, efectuați o acțiune, iar dacă nu este îndeplinită, alta.În acest caz, condiția trebuie să fie o întrebare cu opțiuni de răspuns „da/nu” sau „adevărat/fals” (în ceea ce privește Excel, Google Sheets, LibreOffice, OpenOffice acesta este „adevărat/fals”).

Pentru a înțelege funcția IF, mai întâi trebuie să înțelegeți ce sunt funcțiile logice.

Care sunt funcțiile logice

În Excel, Google Sheets, LibreOffice, OpenOffice și alte foi de calcul, funcționarea funcțiilor logice se bazează pe existența unor parametri logici. Există doi parametri logici: primul este TRUE, al doilea este FALS.

Pe baza utilizării acestor parametri logici, se poate construi un arbore de decizie. Cea mai simplă versiune a acestui arbore va pune o întrebare la care se poate răspunde ADEVĂRAT sau FALS și va oferi instrucțiuni despre ce trebuie făcut în fiecare dintre aceste două cazuri. Un astfel de arbore de decizie este prezentat schematic în figura de mai jos.

Desen. Cel mai simplu arbore de decizie

Funcțiile logice vă permit fie să construiți un astfel de arbore de decizie, fie să puneți o întrebare și să primiți un parametru logic. Primele includ, de exemplu, IF (IF), IFERROR. Cele doua sunt ISNUMBER, AND (AND), SAU (OR).

Excel, Google Sheets, LibreOffice, OpenOffice și majoritatea altor programe vă permit să utilizați parametrii logici TRUE și FALSE atunci când efectuați operații matematice. Cel mai adesea, TRUE ia valoarea 1, FALSE ia valoarea 0. Deși uneori TRUE și FALSE iau alte valori, de exemplu, când se programează în VBA, TRUE este -1 și nu 1.

Apropo, parametrii logici sunt numiți și parametri booleeni în onoarea matematicianului și logicianului englez George Boole.

Funcția IF

Deci, funcția IF vă permite să construiți un arbore de decizie. Acest arbore de decizie are o întrebare de intrare și două opțiuni. Întrebarea are în mod necesar două răspunsuri posibile: da/nu, adevărat/fals, sau în ceea ce privește parametrii logici TRUE/FALSE.

O întrebare și două opțiuni de acțiune sunt cele trei argumente ale funcției IF.

Primul argument al funcției IF este o întrebare logică. În Excel se numește „expresie_log”. Excel, Google Sheets, LibreOffice, OpenOffice găsesc automat răspunsul la această întrebare, iar acest răspuns trebuie să fie ADEVĂRAT / FALS. Ce poate oferi un astfel de răspuns? Cele mai simple opțiuni sunt egalitățile și inegalitățile clasice. De exemplu, expresia 12=12 va returna parametrul logic TRUE, iar inegalitatea 12>40 va returna parametrul logic FALSE.

Într-o întrebare logică, puteți utiliza egalități (partea stângă și dreaptă sunt comparate folosind semnul „="), inegalități (mai mult - „>", mai puțin - „<», больше или равно - «>=", mai mic sau egal cu "<=»), а также просто не равно - «<>».

Întrebări logice mai complexe pot fi adresate folosind funcții imbricate. Ca rezultat al calculării unor astfel de funcții imbricate, ar trebui să se obțină același parametru logic TRUE sau FALSE. Astfel de funcții includ, de exemplu, ISNUMBER, ISTEXT, ISNA, AND, OR, în cazuri complexe - un alt IF.

Al doilea și al treilea argument sunt funcția IF pentru a determina când răspunsul la întrebare este ADEVĂRAT și când este FALS. Funcția IF evaluează fie doar al doilea argument (dacă este TRUE), fie doar al treilea argument (dacă FALSE).

Să ne uităm la exemple de utilizare a funcției IF cu una sau mai multe condiții.

Folosind IF cu o condiție

Puteți descărca fișierul exemplu nr. 1.

Să presupunem că compania are un plan de vânzări: fiecare manager trebuie să vândă cel puțin 1 milion de ruble pe lună. Salariul unui manager de vânzări este de 20 de mii de ruble. Când planul este îndeplinit, managerul primește un salariu și un bonus de 5% din volumul real de vânzări. Dacă planul de vânzări nu este îndeplinit - doar salariu.

La sfârșitul fiecărei luni este generat un tabel care conține informații despre vânzările fiecărui manager. Acest tabel ar putea arăta, de exemplu, ca în figura de mai jos.

Desen. Vânzări de către managerul de vânzări pentru luna de raportare

Folosind funcția IF, acest tabel poate fi transformat rapid dintr-un simplu set de date de vânzări pentru luna într-un raport care va arăta cine a îndeplinit planul, cine nu și care va fi salariul fiecărui manager. Un astfel de raport poate arăta ca figura de mai jos.

Desen. Raport asupra performanței managerilor de vânzări

Pentru a completa automat coloana „Implementarea planului” și „Salariu pentru lună, frecați”. (coloanele E și respectiv F), puteți utiliza funcția IF.

Exemplul 1.1 - înlocuirea textului folosind IF

.

În coloana „Plan de execuție” din celula E4, folosim următoarea formulă:

IF(D4>=1000000;"Bravo!";"Planul nu a fost îndeplinit:(")

IF(D4>=1000000;"Bravo!";"Planul nu a fost îndeplinit:(") .

Apropo, în unele versiuni Excel, în loc de ";" "," trebuie folosit.

După aceasta, celula poate fi copiată până la sfârșitul coloanei, iar programul va scrie pe fiecare rând cine a făcut bine și cine nu a îndeplinit planul.

Ce înseamnă toate argumentele IF?

1. Expresie logică: D4>=1000000. În exemplul nostru, întrebarea logică este o comparație între rezultatul real și planul de vânzări. D4 este o legătură către celula cu vânzările efective ale acestui manager. Excel, Google Sheets, LibreOffice, OpenOffice înlocuiesc valoarea din această celulă cu D4 și verifică dacă inegalitatea specificată este adevărată. Ca urmare a verificării formulei, se obține un rezultat intermediar care este utilizat pentru a selecta ramura dorită în arborele de decizie.

2. Valoare_dacă_adevărat. În diagramele noastre, aceasta este ramura din stânga a arborelui de decizie. În exemplul curent, valoarea argumentului este „Bine făcut!”. Acest argument indică ce ar trebui să facă funcția IF atunci când primul argument se evaluează la TRUE. În exemplul actual, trebuie doar să scrieți textul „Bine făcut!”

Apropo, „Bravo!” îl avem scris între ghilimele, deoarece orice text din interiorul formulei trebuie scris între ghilimele. Singurele excepții sunt numele funcțiilor și intervalele denumite. În caz contrar, pune întotdeauna textul între ghilimele.

3. Valoare_dacă_fals. În diagramele noastre, aceasta este ramura dreaptă a arborelui de decizie. În exemplul curent, valoarea argumentului este „Planul eșuat:(”. Acest argument indică ce ar trebui să facă funcția IF atunci când primul argument este evaluat la FALS. În exemplul curent, scrieți pur și simplu textul „Planul a eșuat :( ".

De asemenea, am pus textul între ghilimele aici, deoarece dacă nu puneți ghilimele în jurul textului într-o formulă, veți obține un #NUME? (#NUME?). Singurele excepții sunt numele funcțiilor și intervalele denumite.

În primul rând, funcția IF răspunde la o întrebare logică (evaluează primul argument). În al doilea rând, merge la ramura corespunzătoare a arborelui de decizie. Potrivit lui Alexandrov P.F. se dovedește așa:

1. D4>=1000000, prin urmare verificăm 1000329>

2. Accesați argumentul Value_if_true. Trebuie doar să inserați textul „Bine făcut!” Specificați textul din celulă. Sfârșitul calculelor.

Desen. Cum funcționează funcția IF când o expresie booleană returnează TRUE?

1. D5>=1000000, prin urmare verificăm 848880>

2. Accesați argumentul Value_if_false. Trebuie doar să introduceți textul „Planul nefinalizat:(”. Indicăm textul în celulă. Sfârșitul calculelor.

Schematic, calculele arată ca în figura de mai jos.

Desen. Cum funcționează funcția IF când o expresie booleană returnează FALS?

Exemplul 1.2 - calcularea diferitelor formule folosind IF

Puteți descărca fișierul exemplu nr. 1.

DACA(D4>=1000000;20000+D4*5/100;20000)

sau pentru engleză Excel, Google Sheets, LibreOffice, OpenOffice:

DACA(D4>=1000000;20000+D4*5/100;20000) .

După aceasta, celula poate fi copiată până la sfârșitul coloanei, iar programul va scrie salariul fiecărui manager în fiecare rând.

Ce face exact funcția IF în acest exemplu?

Funcția IF răspunde la întrebarea logică (evaluează primul argument) și trece la ramura corespunzătoare a arborelui de decizie. Potrivit lui Alexandrov P.F. se dovedește așa:

1. D4>=1000000, prin urmare verificăm 1000329>=1000000, expresia este adevărată, ceea ce înseamnă că parametrul logic este TRUE.

2. Accesați argumentul Value_if_true. Trebuie să calculați 20000+D4*5/100 (adică salariul este de 20 de mii și același bonus este de 5% din vânzări). Obținem 70016, indică această valoare în celulă. Sfârșitul calculelor.

Argumentul Value_if_false este ignorat în acest caz de funcția IF.

Potrivit lui Ilyin M.A. se dovedește așa:

1. D5>=1000000, prin urmare verificăm 848880>=1000000, expresia nu este adevărată, ceea ce înseamnă că parametrul logic este FALS.

2. Accesați argumentul Value_if_false. Trebuie doar să puneți 20000. Indicăm numărul din celulă. Sfârșitul calculelor.

Argumentul Value_if_true în acest caz este ignorat de funcția IF.

Ca întotdeauna, exercițiile noastre funcționează în Excel 2007-2013, iar funcționalitatea avansată poate fi utilizată în Excel 2010 și 2013. Cu ajutorul acestuia, puteți începe orice exercițiu de la început cu doar un singur buton „Începe de la capăt” din fila SFATURI BRP care apare în Excel când deschideți exercițiile noastre. Doar nu uitați să activați macrocomenzi.

Utilizarea IF cu mai multe condiții

Exemplul 2 - condiții diferite într-o expresie logică

Puteți descărca fișierul exemplu nr. 2.

În exemplul anterior, atât managerii, cât și managerii seniori au avut același plan de vânzări pentru luna. Să complicăm sarcina: vom stabili un plan sporit pentru managerii superiori - 1 milion 200 de mii pe lună. Raportul va arăta apoi ca în figura de mai jos.

În acest caz, în coloana „Plan de execuție” din celula E4, folosim următoarea formulă:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);"Bravo!";"Planul nu a fost îndeplinit:(")

sau pentru engleză Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);"Bravo!";"Planul nu a fost îndeplinit:(") .

Ce face exact funcția IF în acest exemplu?

Potrivit lui Alexandrov P.F. se dovedește așa:

2. Funcția IF imbricată verifică expresia logică: funcția managerului este senior manager sau nu. Alexandrov P.F. - acesta nu este un senior manager. Prin urmare, expresia logică din IF imbricat (IF) returnează FALSE.

3. Funcția IF imbricată merge la argumentul Value_if_false și compară vânzările reale cu planul managerului de vânzări. 1.000.329 este mai mare decât 1.000.000, astfel încât IF imbricat returnează parametrul boolean TRUE.

Potrivit lui Ilyin M.A. se dovedește așa:

1. Funcția IF își începe calculul cu o expresie logică și vede o funcție IF imbricată acolo. Excel, Google Sheets, LibreOffice, OpenOffice calculează mai întâi funcția imbricată.

2. Funcția IF imbricată verifică expresia logică: funcția managerului este senior manager sau nu. Ilyin M.A. - acesta nu este un senior manager. Prin urmare, expresia logică din IF imbricat (IF) returnează FALSE.

3. Funcția IF imbricată merge la argumentul Value_if_false și compară vânzările reale cu planul managerului de vânzări. 848.880 este mai mic de 1.000.000, astfel încât IF imbricat returnează parametrul boolean FALSE.

Vedeți arborele de decizie construit în diagrama de mai jos.

Desen. Arborele de decizie pentru o funcție IF cu mai multe condiții

Potrivit lui Nezenetsev A.A. se dovedește așa:

1. Funcția IF își începe calculul cu o expresie logică și vede o funcție IF imbricată acolo. Excel, Google Sheets, LibreOffice, OpenOffice calculează mai întâi funcția imbricată.

2. Funcția IF imbricată verifică expresia logică: funcția managerului este senior manager sau nu. Nezenetsev A.A. - Acesta este un manager superior. Prin urmare, expresia logică din IF imbricat (IF) returnează TRUE.

3. Funcția IF imbricată merge la Value_if_true și compară vânzările reale cu planul managerului senior de vânzări. 1.204.346 este mai mare decât 1.200.000, astfel încât IF imbricat returnează parametrul boolean TRUE.

4. Rezultatul calculului funcției IF imbricate este transmis funcției principale. Funcția principală IF vede parametrul logic TRUE și merge la argumentul său (nu imbricat) Value_if_true. Acest argument este pur și simplu textul „Bine făcut!”

O formulă cu mai multe condiții, adică cu funcții IF imbricate, returnează textul „Bine făcut!”.

Vedeți arborele de decizie construit în diagrama de mai jos.

Desen. Arborele de decizie pentru o funcție IF cu mai multe condiții

Potrivit lui Sokolova N.I. se dovedește așa:

1. Funcția IF își începe calculul cu o expresie logică și vede o funcție IF imbricată acolo. Excel, Google Sheets, LibreOffice, OpenOffice calculează mai întâi funcția imbricată.

2. Funcția IF imbricată verifică expresia logică: funcția managerului este senior manager sau nu. Sokolova N.I. - Acesta este un manager superior. Prin urmare, expresia logică din IF imbricat (IF) returnează TRUE.

3. Funcția IF imbricată merge la Value_if_true și compară vânzările reale cu planul managerului senior de vânzări. 1.046.625 este mai mic de 1.200.000, astfel încât IF imbricat returnează parametrul boolean FALSE.

4. Rezultatul calculului funcției IF imbricate este transmis funcției principale. Funcția principală IF vede parametrul boolean FALSE și merge la argumentul său (nu imbricat) Value_if_false. Acest argument este pur și simplu textul „Planul eșuat:(”.

O formulă cu mai multe condiții, adică cu funcții IF imbricate, returnează textul „Planul eșuat:(” în celulă.

Vedeți arborele de decizie construit în diagrama de mai jos.

Desen. Arborele de decizie pentru o funcție IF cu mai multe condiții

Formula de calcul a salariilor din exemplul 3

În coloana „Salariu pe lună, frecați”. în celula F4 folosim această formulă:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);20000+D4*5/100;20000)

sau pentru engleză Excel, Google Sheets, LibreOffice, OpenOffice:

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);20000+D4*5/100;20000) .

Nu uitați, în unele versiuni de Excel, în loc de „;” "," trebuie folosit.

În acest caz, funcția IF funcționează exact la fel ca în celula E4.

Exemplul 4 - condiții diferite atât în ​​expresia logică, cât și în ramurile arborelui de decizie

Puteți descărca fișierul exemplu nr. 3.

Deci avem manageri, avem manageri seniori. Managerii superiori au un plan mai înalt decât managerii obișnuiți. Pentru ca acest model să funcționeze, sunt adesea necesare stimulente suplimentare pentru managerii superiori. De exemplu, bonusul senior manager crește la 6%. Adică avem mai multe condiții deodată:

1. Bonusul se plătește numai dacă planul este îndeplinit.

2. Dacă postul este senior manager, planul este de 1 milion 200 de mii, în caz contrar - 1 milion.

3. Dacă postul este senior manager, bonusul este de 6%, în caz contrar - 5%.

Rezultatul este un raport ca cel de mai jos.

Desen. Raport privind performanța managerilor și a managerilor superiori

Cum se rezolvă o astfel de problemă folosind funcția IF?

În celula F4 puteți scrie următoarea formulă:

IF(C4="Senior Manager";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Senior Manager";6;5)/100;

sau pentru engleză Excel, Google Sheets, LibreOffice, OpenOffice:

IF(C4="Senior Manager";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Senior Manager";6;5)/100;

Nu uitați, în unele versiuni de Excel, în loc de „;” "," trebuie folosit.

Figura de mai jos arată schematic arborele de decizie construit.

Desen. Exemplu de arbore de decizie cu mai multe condiții atât în ​​expresia logică, cât și în alte argumente ale funcției IF

Greșeli frecvente când lucrați cu funcția IF

1. Pentru funcția IF, primul argument trebuie întotdeauna specificat - o expresie logică și al doilea argument - valoarea dacă este adevărată. Al treilea argument este opțional. Utilizatorii uită adesea să specifice al treilea argument, mai ales atunci când lucrează cu formule complexe, din această cauză, în unele cazuri, în loc de rezultatul dorit, în celulă apare un parametru logic FALSE.

2. Complexitatea formulei crește foarte repede atunci când se utilizează IF imbricate. Din această cauză, de foarte multe ori utilizatorii uită să închidă parantezele calculelor imbricate și nu setează separatorul de argumente (“;” sau “,”). În funcție de eroare, formula fie nu poate fi scrisă în celulă, fie este calculată incorect.

3. În formulele complexe cu IF (IF), este foarte dificil să urmăriți corectitudinea calculelor: fiecare funcție imbricată IF (IF) adaugă o întrebare și cel puțin două ramuri arborelui de decizie. În medie, o persoană ține până la 7 obiecte în minte, se dovedește că, cu trei FI imbricate (IF), trebuie să păstreze în minte 3 întrebări și 6 ramuri ale arborelui de decizie. Controlabilitatea și fiabilitatea formulei sunt în scădere rapidă.

Cum să evitați aceste greșeli atunci când lucrați cu funcția IF? Minimizați utilizarea IF-urilor cu alte funcții și mai ales cu IF imbricate. Este mai bine să faceți calcule intermediare în celulele adiacente.

Sfat: Lucrul cu formule complexe

Adesea trebuie să lucrăm cu formule complexe, formule în care altele sunt imbricate într-o funcție. Cum să nu greșești atunci când creezi o astfel de formulă? Urmați următorul algoritm:

1. Stabilește scopul final al calculelor tale: ce rezultat ar trebui să obții în final.

2. Definiți o funcție care vă permite să faceți acest lucru.

3. Începeți să creați o formulă cu această funcție, specificați-o și treceți la lucrul cu argumente.

5. Dacă trebuie să faceți calcule intermediare, atunci determinați scopul final al acestor calcule, funcția și așa mai departe. De obicei, sarcina calculelor intermediare este de a obține un argument pentru funcția principală. Țineți cont de acest lucru, deoarece uneori trebuie să obțineți un argument de un anumit tip (text, număr, parametru logic sau altceva).

6. Urmăriți întotdeauna parantezele: după ce ați terminat de descris funcția, închideți parantezele.

Și amintiți-vă, dacă formula este prea complexă, este mai bine să faceți un calcul intermediar în celula următoare.

Cum să completați și să înlocuiți funcția IF

În loc de constante, puteți utiliza intervale denumite într-o formulă.

Rezolvarea unei probleme cu mai multe condiții poate fi simplificată foarte mult prin utilizarea funcțiilor imbricate AND (ȘI), SAU (SAU).

Funcția DACĂ poate fi înlocuită uneori cu o funcție CĂUTARE V, CĂUTARE, CĂUTARE, DACĂ EROARE, SUMIF sau CONTĂRARE.

Fișierul de exemplu nr. 1 „Utilizarea funcției IF cu o singură condiție” pe care îl puteți descărca .

Dosar exemplu nr. 2 „Folosirea funcției IF cu mai multe condiții”.

Mai ai întrebări? Scrie-ne în formular feedbackși înscrieți-vă pentru Curs intensiv Excel sau funcții Excel.

Suită de birou aplicațiile vă ajută să efectuați multe sarcini în diferite domenii. Deci, MS Excel, care este procesor de masă, este capabil să rezolve o varietate de probleme. Este suficient să introduceți funcția și iată - rezultatul. De regulă, această aplicație este folosită în contabilitate, dar nu numai. Și în toată diversitatea, funcția „IF” ocupă departe de ultimul loc.

Informații generale despre foaia de calcul MS Excel

Procesorul de foi de calcul este conceput pentru a organiza calcule, a construi grafice și diagrame fără a scrie un software special.

Un document Excel se numește registru de lucru, care constă din foi. Numărul lor poate fi cât se dorește. Apropo, tabelele din această aplicație nu au capăt și nici margine, în sensul literal. Deși dezvoltatorii susțin că tabelul este format din coloane denumite în alfabet latin (A-IV) și rânduri numerotate cu cifre arabe (1-65536).

ÎN registrul de lucru Există un tabel care constă din rânduri și coloane, iar intersecția lor se numește celulă. Munca se va organiza direct cu ea. Utilizatorii pot lucra și cu un grup de celule, numit și bloc sau interval.

Inițial (vorbim acum despre MS Excel 2003) în zona de lucru Există 2 bare de instrumente: „Standard” și „Formatare”. Desigur, utilizatorul poate adăuga și alte comenzi pe care le folosește cel mai des. Deci, puteți organiza un panou din care funcția „IF” va fi apelată folosind o casetă de dialog.

Adresa celulei este formată dintr-o literă din alfabetul latin și o cifră arabă: A10, D15, T1523. Puteți introduce atât date, cât și formule într-o celulă. Dacă are loc un calcul într-o celulă, totalul va fi afișat în ea, iar formula specificată în linia corespunzătoare din partea de sus.

Tipuri de funcții

Există aproximativ 400 de funcții în aplicația pachet Excel. Acestea au fost împărțite în categorii adecvate (sunt oferite câteva exemple pentru referință):

  • financiar: PROSPAYT, POO, PS, RATE, NPV etc.;
  • date și ore - ORA, ZIUA SĂPTĂMNICĂ, AN, etc.;
  • matematic - ABS, ATAN, LOG, LN, SIN etc.;
  • statistic - DISP, HYPERGEOMET, QUARTIL etc.;
  • legături și matrice - ADRESĂ, SELECTARE, ZONA, CĂUTARE, LINIE etc.;
  • baze de date - BDDISP, BSCHOTA, BDSUMM, DSRZNACH etc.;
  • text - ÎNLOCUIRE, GĂSIRE, REPETARE, ÎNLOCUIRE, FIX, etc.;
  • logic - DACA, ADEVĂRAT, NU, FALS etc.;
  • verificarea proprietăților și valorilor - EROARE, INFORM, ISNUMBER, CELL etc.

După cum puteți vedea, funcția „IF” este inclusă în categoria funcțiilor logice. Un studiu mai detaliat al acestora va avea loc puțin mai târziu.

Aflați mai multe despre funcțiile logice

Formulele din această categorie sunt necesare pentru compararea oricăror tipuri de date, și anume matematice, text, logice. Orice înregistrare de condiție trebuie să conțină cel puțin un semn de comparație: =, >,<, >=, <=, <>. Aceste simboluri sunt cele care definesc relația dintre elemente.

Rezultatul unei ecuații logice date poate fi ADEVĂRAT sau FALS. Primul parametru este exprimat ca (1), al doilea - ca (0).

Cel mai adesea, funcția „IF” din Excel funcționează împreună cu formule precum „ȘI” și „SAU”. Primul este folosit pentru a verifica mai multe condiții și a le executa simultan. Dacă cel puțin unul dintre parametri este fals, atunci întreaga condiție AND va lua valoarea (0). „SAU” se evaluează la adevărat atunci când cel puțin una dintre condiții este adevărată.

„Și”, „SAU” poate avea până la 30 de condiții în formula lor. Cu toate acestea, este necesar să rețineți: formulele lungi și grele sunt foarte greu de perceput. Prin urmare, dacă este posibil, este de dorit să le ușurăm oarecum prin adăugarea de coloane sau rânduri.

Formula „DAC”. Subtilități ale introducerii mai multor condiții

Funcția IF este exprimată prin următoarea formulă, care poate fi specificată în două moduri.

Prima metodă: funcția „IF” (formula poate fi introdusă cu litere mici sau majuscule) este scrisă manual de utilizator, paranteza este deschisă, iar procesorul de foi de calcul afișează un indiciu care poate fi folosit pentru a naviga.

A doua modalitate: apelați funcția prin meniu sau bara de instrumente. În meniu, trebuie să mergeți la „Inserare” - „Funcții”. Deja aici în categoria „Logic” se află funcția „IF” în Excel, cu mai multe condiții sau un singur parametru. Se deschide o casetă de dialog în care introduceți indicatorii necesari pentru a obține rezultatul.

Dacă condiția este adevărată, atunci prima expresie specificată este executată; dacă este fals, atunci rezultatul va fi al doilea parametru.

Utilizatorii începători trebuie să rețină că procesorul de tabelă va ajuta dacă există o eroare. Va apărea o casetă de dialog care oferă opțiuni pentru defecte. Utilizatorul poate corecta eroarea independent sau poate folosi ajutorul, care oferă exemple comune de scriere a unor astfel de formule. Orice lucru mic poate face ca o problemă să fie rezolvată. Virgula este în locul greșit, a fost plasat punct și virgulă în loc de două puncte, paranteza nu a fost închisă, a fost adăugat un semn minus suplimentar - aceasta nu este o listă completă a deficiențelor întâlnite în lucrare.

Greșeli tipice la introducerea formulelor

Când introduceți diferite formule într-o celulă (funcția „IF” din Excel nu face excepție), pot apărea diverse erori.

  • #NUME? - adresa celulei la care se referă funcția este introdusă incorect sau există o eroare în numele formulei.
  • #DIV/0! - toată lumea la matematică știe că nu poți împărți cu „0” (zero). Această eroare ne amintește de acest punct important.
  • #NUMĂR! - există o serie de restricții în diverse funcții matematice, de ex. condiţii (număr negativ sub semnul rădăcinii sau logaritmului natural). Este necesar să revizuiți funcția dată și să corectați defectul.
  • #VALOARE! - este posibil ca utilizatorul să fi introdus parametri incorect. De exemplu, în loc de un interval de celule, celulele sunt specificate folosind metoda de enumerare.
  • #LEGĂTURĂ! - funcția nu poate face referire la celulă deoarece adresa acesteia este incorectă.
  • ########## - aceasta indică faptul că rezultatul calculului nu se încadrează în lățimea specificată a coloanei. Este suficient să-l extinzi, iar apoi răspunsul va fi vizibil.

Câteva exemple

Exemplul de funcție IF poate conține una sau mai multe condiții. Aceasta poate include formule suplimentare: „ȘI”, „SAU”, „DAC”, etc.

Sarcină

Se oferă o listă cu angajații care au credite de consum și credite pentru locuințe. Tabelul arată acumulările și deducerile salariale.

  • Reține 20% pentru acei angajați care au două tipuri de împrumuturi.

  • Reține 10% pentru rambursarea datoriilor acelor angajați care au cel puțin un împrumut.

  • Reține 20% de la acei angajați care au două împrumuturi, în caz contrar dacă există cel puțin o datorie, atunci reține 10% din salarii; dacă nu există datorii, afișați rândul „Fără credit”.

Această funcție poate conține mai multe formule imbricate. Principalul lucru este să nu faceți greșeli în compilarea parametrilor.