Cum să faci mai puțin sau egal cu în Excel. Excel funcţionează dacă (dacă) şi dacă (dacă) pentru mai multe condiţii

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 testată. 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 altfel- „Ștergeți”. 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ă vă familiarizați cu exemplul - vă 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ă veți avea nevoie de următoarele structura logica. 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. Ultima este indicată la sfârșitul formulei varianta alternativa, 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 atunci 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.

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.

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

1. În câmpul „dacă fals”, introduceți următoarele.

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ă apăsarea butonului 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.

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ă. 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.

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ă expresii și comparații 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ă.

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șeli.

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?

Există multe funcții diferite în Excel care funcționează prin verificarea condițiilor logice. De exemplu, acestea sunt funcțiile IF, COUNTIF, SUMIF etc. Condițiile logice pot fi specificate și în formule obișnuite dacă trebuie să obțineți un răspuns afirmativ: Da sau Nu. De exemplu, punând condiții logice simple, puteți răspunde la următoarele întrebări:

• 5 este mai mult de 8?
• Este conținutul celulei A5 mai mic de 8?
• Sau poate egal cu 8?

Operatori de comparație în Excel

Excel are o serie de operatori standard care sunt utilizați pentru a seta condiții booleene simple. Toți cei șase operatori de comparație posibili sunt prezentați în tabelul de mai jos:

Cum să setați o condiție în Excel

Operatorii de comparație vă permit să specificați condiții care returnează valorile booleene TRUE sau FALSE. Exemple de utilizare a condițiilor logice sunt prezentate mai jos:

=A1=B1– Această condiție va returna TRUE dacă valorile din celulele A1 și B1 sunt egale, sau FALSE în caz contrar. Prin setarea acestei condiții, puteți compara șirurile de text fără a ține seama de majuscule și minuscule. De exemplu, comparând „IANUARIE” și „Ianuarie”, formula va returna TRUE.

=A1>B1– Următoarea formulă va returna TRUE dacă valoarea din celula A1 este mai mare decât cea din B1. În caz contrar, formula va returna FALSE. Astfel de comparații pot fi setate și atunci când lucrați cu text.

De exemplu, dacă celula A1 conține valoarea „Portocaliu” și B1 conține „Pepene verde”, formula va returna FALS deoarece „Pepene verde” este mai mic în ordine alfabetică decât „Portocaliu”. Cu cât mai jos, cu atât mai mult.

=A1<=B1 – Formula va returna TRUE dacă valoarea din celula A1 este mai mică sau egală cu valoarea din celula B1. În caz contrar, rezultatul va fi FALS.

=A1<>B1– Formula va returna TRUE dacă valorile celulelor A1 și B1 nu sunt egale. Altfel - FALS.

Există funcții booleene în Excel ADEVĂRAT()Şi MINCIUNĂ(), care nu au argumente. Aceste caracteristici există în primul rând pentru a oferi compatibilitate cu alte foi de calcul. Puteți introduce valori TRUE și FALSE direct în celule sau formule fără a utiliza formularul de notare a funcției, Excel va înțelege totul bine.

Dacă ești sigur că ai stăpânit deja suficient de bine acest subiect, te poți referi la articol Utilizarea funcțiilor booleene din Excel pentru a seta condiții complexe pentru a învăța cum să setați condiții folosind diverse funcții logice, de ex. ŞI() sau SAU().