IF Formula Excel - Declarații If Then

Descărcați Exemplu de registru de lucru

Descărcați exemplul de registru de lucru

Acest tutorial arată cum să utilizați Funcția Excel IF în Excel pentru a crea declarații If Then.

Prezentare generală a funcției IF

Funcția IF Verifică dacă este îndeplinită o condiție. Dacă ADEVĂRAT fac un lucru, dacă FALS fac altul.

Pentru a utiliza funcția Foaie de lucru IF Excel, selectați o celulă și tastați:

(Observați cum apar datele introduse de formulă)

Funcția IF Sintaxă și intrări:

1	= IF (logic_test, value_if_true, value_if_false)

test_logic - Expresie logică. Exemplu: A1> 4.

value_if_true - Valoare sau Calcul pentru a efectua dacă expresia logică este ADEVĂRATĂ.

value_if_false - Valoare sau Calcul pentru a efectua dacă expresia logică este FALSĂ.

IF este o funcție „condițională”. Aceasta înseamnă că definiți un test logic și va returna o valoare dacă testul respectiv este evaluat ca fiind adevărat și o valoare diferită dacă este falsă

Cum se folosește funcția IF

Iată un exemplu foarte simplu, astfel încât să puteți vedea la ce mă refer. Încercați să tastați următoarele în Excel:

1	= IF (2 + 2 = 4, „Este adevărat”, „Este fals!”)

Deoarece 2 + 2 este de fapt egal cu 4, Excel va returna „Este adevărat!”. Dacă am folosit acest lucru:

1	= IF (2 + 2 = 5, „Este adevărat”, „Este fals!”)

Acum Excel va returna „Este fals!”, Deoarece 2 + 2 nu este egal cu 5.

Iată cum puteți utiliza IF într-o foaie de calcul.

1	= IF (C4-D4> 0, C4-D4,0)

Rulați un bar sportiv și setați limite individuale de tab pentru diferiți clienți. Ați configurat această foaie de calcul pentru a verifica dacă fiecare client își depășește limita, caz în care le veți întrerupe până când își vor plăti fila.

Verificați dacă C4-D4 (valoarea curentă a filei minus limita lor) este mai mare de 0. Acesta este testul dvs. logic. Dacă acest lucru este adevărat, IF returnează „Da” - ar trebui să le întrerupeți. Dacă acest lucru este fals, IF returnează „Nu” - îi lăsați să continue să bea.

Ce IF poate reveni

Deasupra am returnat un șir de text, „Da” sau „Nu”. Dar puteți, de asemenea, să returnați numere sau chiar alte formule.

Să presupunem că unii dintre clienții dvs. execută file mari. Pentru a descuraja acest lucru, veți începe să percepeți dobânzi pentru clienții care depășesc limita lor.

Puteți utiliza IF pentru asta:

1	= IF (C4> D4, C4 * 0,03,0)

Dacă fila este mai mare decât limita, returnați fila înmulțită cu 0,03, care returnează 3% din filă. În caz contrar, returnați 0: nu sunt peste fila lor, deci nu veți percepe dobândă.

Folosind IF cu AND

Puteți combina IF cu funcția ȘI Excel <>. Utilizați acest lucru în testul logic, care vă permite să specificați două sau mai multe condiții de testat. Excel va returna TRUE doar dacă TOATE testele sunt adevărate.

Deci, v-ați implementat rata dobânzii. Dar unii obișnuiți se plâng. Întotdeauna și-au plătit filele în trecut, de ce le reprimiți acum? Găsiți o soluție: nu veți percepe dobânzi anumitor clienți de încredere.

Faceți o coloană nouă în foaia de calcul pentru a identifica clienții de încredere și vă actualizați declarația IF cu o funcție ȘI:

1	= IF (ȘI (C4> D4, F4 = "Nu"), C4 * 0,03,0)

Să analizăm partea ȘI separat:

1	ȘI (C4> D4, F4 = "Nu")

Rețineți cele două condiții:

• C4> D4: verificarea dacă depășește limita de file, ca înainte
• F4 = ”Nu”: acesta este noul bit, verificând dacă nu sunt un client de încredere

Deci, acum returnăm rata dobânzii numai dacă clientul este peste fila lor, ȘI avem „Nu” în coloana de încredere pentru clienți. Obișnuiții tăi sunt din nou fericiți.

Citiți mai multe pe pagina principală pentru funcția Excel ȘI <>.

Folosind IF cu OR

SAU este o altă funcție logică Excel. Ca și AND, vă permite să definiți mai multe condiții. Dar, spre deosebire de AND, va reveni ADEVĂRAT dacă oricare dintre testele pe care le definiți sunt adevărate.

Poate că clienții care trec peste fila lor nu este singurul motiv pentru care i-ați întrerupe. Poate le oferiți unor persoane o interdicție temporară din alte motive, jocurile de noroc, probabil, în incintă.

Așadar, adăugați o nouă coloană pentru a identifica clienții excluși și vă actualizați „Cut off?” coloană cu un test SAU:

1	= IF (SAU (C4> D4, E4 = "Da"), "Da", "Nu")

Privind doar partea OR:

1	SAU (C4> D4, E4 = "Da")

Există două condiții:

• C4> D4: verificarea dacă depășește limita de file
• F4 = ”Da”: partea nouă, verificând dacă sunt interzise în prezent

Acest lucru va fi adevărat dacă sunt peste fila lor sau dacă există un „Da” în coloana E. După cum puteți vedea, Harry este întrerupt acum, chiar dacă nu a depășit limita de filă.

Citiți mai multe pe pagina principală pentru funcția Excel SAU <>.

Folosind IF cu XOR

XOR este o altă funcție logică, care returnează „Sau exclusiv”. Acest lucru este puțin mai puțin intuitiv decât cele pe care le-am discutat anterior.

În cazuri simple, definiți două condiții, iar XOR va reveni:

• ADEVĂRAT dacă oricare dintre argumente este adevărat (la fel ca un OR normal)
• FALS dacă ambele argumente sunt adevărate
• FALS dacă ambele argumente sunt false

Un exemplu ar putea face acest lucru mai clar. Imaginați-vă că doriți să începeți să acordați bonusuri lunare personalului dvs.:

• Dacă vând peste 800 USD în alimente sau peste 800 USD în băuturi, le veți acorda o jumătate de bonus
• Dacă vând peste 800 USD în ambele, le veți acorda un bonus complet
• Dacă vând sub 800 USD în ambele, nu primesc niciun bonus.

Știi deja cum să te antrenezi dacă primesc bonusul complet. Ați folosi doar IF cu ȘI, așa cum s-a descris mai sus.

1	= IF (ȘI (C4> 800, D4> 800), „Da”, „Nu”)

Dar cum ați putea afla cine primește jumătate de bonus? Aici intervine XOR:

1	= IF (XOR (C4> = 800, D4> = 800), „Da”, „Nu”)

După cum puteți vedea, vânzările de băuturi ale lui Woody au fost de peste 800 USD, dar nu vânzările de alimente. Așa că primește jumătate de bonus. Reversul este adevărat pentru antrenor. Diane și Carla au vândut mai mult de 800 de dolari pentru ambele, deci nu primesc jumătate de bonus (ambele argumente sunt ADEVĂRATE), iar Rebecca a făcut sub pragul pentru ambele (ambele argumente FALS), astfel încât formula returnează din nou „Nu”.

Citiți mai multe pe pagina principală pentru funcția Excel XOR <>.

Folosind IF cu NOT

NOT este încă o funcție logică Excel, care este foarte frecvent utilizată cu IF.

NU inversează rezultatul unui test logic. Cu alte cuvinte, verifică dacă nu a fost îndeplinită o condiție.

Îl puteți folosi cu IF astfel:

1	= IF (AND (C3> = 1985, NOT (D3 = "Steven Spielberg")), "Watch", "Don't Watch")

Aici avem un tabel cu date despre unele filme din anii 1980. Vrem să identificăm filme lansate după sau după 1985, care nu au fost regizate de Steven Spielberg.

Deoarece NOT este imbricat într-o funcție ȘI, Excel va evalua acest lucru mai întâi. Apoi va folosi rezultatul ca parte a AND.

Citiți mai multe pe pagina principală a funcției Excel NOT <>.

Declarații IF imbricate

De asemenea, puteți returna o declarație IF în cadrul declarației dvs. IF. Acest lucru vă permite să faceți calcule mai complexe.

Să ne întoarcem la masa clienților noștri. Imaginați-vă că doriți să clasificați clienții în funcție de nivelul datoriilor față de dvs.:

• $ 0: Nici unul
• Până la 500 USD: scăzut
• 500 $ la 1000 $: mediu
• Peste 1000 $: mare

Puteți face acest lucru prin „cuibărirea” declarațiilor IF:

1	= IF (C4 = 0, "None", IF (C4 <= 500, "Low", IF (C4 <= 1000, "Medium", IF (C4> 1000, "High"))))

Este mai ușor de înțeles dacă puneți instrucțiunile IF pe linii separate (ALT + ENTER pe Windows, CTRL + COMMAND + ENTER pe Mac):

12345

=IF (C4 = 0, „Nici unul”,IF (C4 <= 500, "Scăzut",IF (C4 <= 1000, „Mediu”,IF (C4> 1000, "Înalt", "Necunoscut"))))

DACĂ C4 este 0, returnăm „None”. În caz contrar, trecem la următoarea declarație IF. DACĂ C4 este egal sau mai mic de 500, returnăm „Low”. În caz contrar, trecem la următoarea declarație IF … și așa mai departe.

Simplificarea instrucțiunilor IF complexe cu coloane Helper

Dacă aveți mai multe instrucțiuni IF imbricate și introduceți și funcții logice, formulele dvs. pot deveni foarte greu de citit, testat și actualizat.

Acest lucru este deosebit de important de reținut dacă alte persoane vor folosi foaia de calcul. Ceea ce are sens în capul tău, s-ar putea să nu fie atât de evident pentru ceilalți.

Coloanele de ajutor sunt o modalitate excelentă de a rezolva această problemă.

Sunteți analist în departamentul financiar al unei mari corporații. Vi s-a cerut să creați o foaie de calcul care să verifice dacă fiecare angajat este eligibil pentru pensia companiei.

Iată criteriile:

Așadar, dacă aveți sub 55 de ani, trebuie să aveți 30 de ani de serviciu sub centură pentru a fi eligibil. Dacă ai 55 - 59 de ani, ai nevoie de 15 ani de serviciu. Și așa mai departe, până la vârsta de 65 de ani, unde sunteți eligibil indiferent de cât timp ați lucrat acolo.

Puteți utiliza o singură instrucțiune IF complexă pentru a rezolva această problemă:

1	= IF (OR (F4> = 65, AND (F4> = 62, G4> = 5), AND (F4> = 60, G4> = 10), AND (F4> = 55, G4> = 15), G4 > 30), „Eligibil”, „Neeligibil”)

Vai! Îmi este greu să te învârți cu capul, nu-i așa?

O abordare mai bună ar putea fi utilizarea coloanelor de ajutor. Aici avem cinci teste logice, corespunzătoare fiecărui rând din tabelul criteriilor. Acest lucru este mai ușor de văzut dacă adăugăm întreruperi de linie la formulă, așa cum am discutat mai devreme:

12345678

= DACĂ (SAU(F4> = 65,ȘI (F4> = 62, G4> = 5),ȘI (F4> = 60, G4> = 10),ȘI (F4> = 55, G4> = 15),G4> 30), „Eligibil”, „Neeligibil”)

Deci, putem împărți aceste cinci teste în coloane separate și apoi verificăm pur și simplu dacă vreunul dintre ele este adevărat:

Fiecare coloană din tabelul de la E la I deține fiecare dintre criteriile noastre separat. Apoi în J4 avem următoarea formulă:

1	= IF (COUNTIF (E4: I4, TRUE), „Eligibil”, „Neeligibil”)

Aici avem o instrucțiune IF, iar testul logic folosește COUNTIF <> pentru a număra numărul de celule din E4: I4 care conțin TRUE.

Dacă COUNTIF nu găsește o valoare ADEVĂRATĂ, va returna 0, pe care IF interpretează ca FALS, astfel încât IF returnează „Nu este eligibil”.

Dacă COUNTIF găsește valori ADEVĂRATE, va returna numărul acestora. IF interpretează orice alt număr decât 0 ca fiind ADEVĂRAT, deci returnează „Eligibil”.

Împărțirea testelor logice în acest fel face ca formula să fie mai ușor de citit și, dacă ceva nu merge bine, este mult mai ușor să observi unde este greșeala.

Utilizarea grupării pentru a ascunde coloanele de ajutor

Coloanele de ajutor facilitează gestionarea formulei, dar odată ce le-ați instalat și știți că funcționează corect, adesea ocupă spațiu pe foaia de calcul fără a adăuga informații utile.

Puteți ascunde coloanele, dar acest lucru poate duce la probleme, deoarece coloanele ascunse sunt greu de detectat, cu excepția cazului în care priviți cu atenție anteturile coloanei.

O opțiune mai bună este gruparea.

Selectați coloanele pe care doriți să le grupați, în cazul nostru E: I. Apoi apăsați ALT + SHIFT + SĂGEATĂ DREAPTA pe Windows sau COMMAND + SHIFT + K pe Mac. De asemenea, puteți accesa fila „Date” de pe panglică și puteți selecta „Group” din secțiunea „Outline”.

Veți vedea grupul afișat deasupra antetelor coloanei, astfel:

Apoi, pur și simplu apăsați butonul „-“ pentru a ascunde coloanele:

Funcția IFS

Instrucțiunile IF imbricate sunt foarte utile atunci când trebuie să efectuați comparații logice mai complexe și trebuie să o faceți într-o singură celulă. Cu toate acestea, se pot complica pe măsură ce devin mai lungi și pot fi greu de citit și actualizat pe ecran.

Din Excel 2022 și Excel 365, Microsoft a introdus o altă funcție, IFS, pentru a face acest lucru un pic mai ușor de gestionat. Exemplul IF imbricat de mai sus ar putea fi realizat cu IFS astfel:

1234567

= IFS (C4 = 0, „Nici unul”,C4 <= 500, "Scăzut",C4 <= 1000, „Mediu”,C4> 1000, "Înalt",ADEVĂRAT, "Necunoscut",)

Puteți citi totul despre aceasta pe pagina principală pentru funcția Excel IFS <>.

Utilizarea IF cu formatarea condiționată

Funcția de formatare condiționată Excel vă permite să formatați o celulă în moduri diferite, în funcție de conținutul acesteia. Deoarece IF returnează valori diferite pe baza testului nostru logic, s-ar putea să dorim să folosim formatarea condiționată pentru a face aceste valori diferite mai ușor de văzut.

Așadar, să ne întoarcem la tabelul de bonusuri al personalului de mai devreme.

Întoarcem „Da” sau „Nu”, în funcție de bonusul pe care dorim să-l oferim. Acest lucru ne spune ceea ce trebuie să știm, dar informațiile nu ne afectează. Să încercăm să rezolvăm problema.

Iată cum ați face acest lucru:

• Selectați intervalul de celule care conține instrucțiunile IF. În cazul nostru, acesta este E4: F8.
• Faceți clic pe „Formatare condiționată” din secțiunea „Stiluri” din fila „Acasă” de pe panglică.
• Faceți clic pe „Evidențiați regulile celulelor” și apoi pe „Egal cu”.
• Tastați „Da” (sau orice valoare de returnare de care aveți nevoie) în prima casetă, apoi alegeți formatarea dorită din a doua casetă. (Voi alege verde pentru asta).
• Repetați pentru toate valorile returnate (voi seta și valorile „Nu” la roșu)

Iată rezultatul:

Utilizarea IF în formule de matrice

O matrice este o gamă de valori, iar în matricile Excel sunt reprezentate ca valori separate prin virgulă închise între paranteze, cum ar fi:

1	{1,2,3,4,5}

Frumusețea matricilor este că vă permit să efectuați un calcul pentru fiecare valoare din interval și apoi să returnați rezultatul. De exemplu, funcția SUMPRODUCT ia două matrice, le înmulțește împreună și rezumă rezultatele.

Deci, această formulă:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… întoarce 32. De ce? Să o rezolvăm:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Putem aduce o declarație IF în această imagine, astfel încât fiecare dintre aceste multiplicări să aibă loc numai dacă un test logic revine adevărat.

De exemplu, luați aceste date:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png „nu”> 1 = SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))

Notă: În Excel 2022 și versiunile anterioare, trebuie să apăsați CTRL + SHIFT + ENTER pentru a transforma acest lucru într-o formulă matrice.

Am termina cu așa ceva:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png "nu"> 1 $ C $ 2: $ C $ 10 = $ G2

În engleză, dacă numele din coloana C este egal cu ceea ce este în G2 („Olivia”), DO înmulțește valorile din coloanele D și E pentru acel rând. În caz contrar, nu le înmulțiți. Apoi, sumați toate rezultatele.

Puteți afla mai multe despre această formulă pe pagina principală pentru SUMPRODUCT IF Formula <>.

DACĂ în Foi de calcul Google

Funcția IF funcționează exact la fel în Foi de calcul Google ca în Excel:

Note Aditionale

Utilizați funcția IF pentru a testa dacă o condiție este ADEVĂRATĂ. Dacă condiția este ADEVĂRATĂ, atunci faceți un lucru. Dacă este FALS, atunci faceți altul. Condiția trebuie să fie o expresie logică (ex: a1> 5), o referință la o celulă care conține TRUE sau FALSE sau o matrice care conține toate valorile logice.

Funcția IF poate testa doar o condiție la un moment dat. Cu toate acestea, puteți „cuibura” alte funcții logice în condiția IF pentru a testa mai multe condiții simultan:

= if (ȘI (a1> 0, a2> 0), ADEVĂRAT, FALS)
= if (OR (a1> 0, a2> 0), TRUE, FALSE)
= if (XOR (a1> 0, a2> 0), TRUE, FALSE)
SAU Test de funcții dacă una sau mai multe condițiile sunt îndeplinite.
ȘI testul funcțiilor dacă toate condițiile sunt îndeplinite.
Test de funcții XOR dacă unul și unul singur condiția sunt îndeplinite.

De asemenea, puteți „cuibări” o funcție IF în cadrul unei funcții IF:

1	= if (a1 <0, if (a2 <0, "Both", "only 1"), "only one")

Acum, pentru câteva exemple concrete despre cum funcționează funcția IF în practică:

1. Porniți o carte de lucru nou.

2. În celula A1 introduceți valoarea 10 (și apăsați Enter)

3. Apoi, în celula B1, introduceți următoarea formulă:

1	= DACĂ (A1> 5, „MAI MARE DE 5”, „MAI MIC DE 5”)

4. Ecranul ar trebui să arate astfel:

5. Dacă ați introdus corect formula, în celula B1 va apărea mesajul „Mai mare decât 5”.

6. Formula introdusă în celula B1 efectuează testul „A1> 5”, adică verifică dacă valoarea din celula A1 este mai mare de 5. În prezent, valoarea din celula A1 este 10 - deci condiția este ADEVĂRATĂ și mesajul Apare „BIGGER THAN 5”

7. Dacă acum schimbăm valoarea din celula A1 la 2:

Atunci mesajul din celula B2 este acum „MAI MIC DE 5”, deoarece condiția este FALSĂ.

8. Puteți continua să modificați valoarea din celula A1, iar mesajul din celula B2 se va regla corespunzător.

9. Desigur, există situații în care condiția ar putea da rezultate răutăcioase:

• Ce se întâmplă dacă introducem valoarea 5 în celula A1?

• Dar dacă lăsăm celula A1 necompletată?

• Dar dacă punem un text în celula A1, de exemplu, expresia DOG

Mai multe despre funcția Excel IF

Acum vom analiza mai detaliat funcția IF. Poate fi folosit pentru a analiza foarte ușor cantități mari de date.

Imaginați-vă că sunteți director de vânzări de zonă și aveți o echipă de vânzări. Puteți înregistra vânzările totale pe care le face fiecare persoană într-o foaie de calcul Excel simplă:

Să presupunem că criteriile pentru un bonus au fost că vânzările făcute de acea persoană au depășit 40.000 GBP. Puteți pur și simplu „face ochi” datele și să stabiliți că numai Anton, Newton și Monique au atins ținta.

Acest lucru este destul de ușor atunci când aveți doar o mână de nume. Cu toate acestea, dacă aveți mai multe, atunci există posibilități de eroare. Din fericire, utilizând funcția IF a Excel, se poate face mult mai rapid și mai sigur.

Configurați un nou registru de lucru și introduceți datele conform celor de mai sus. Apoi în celula D4 tastați următoarea formulă: -

1	= IF (C4> 40000, „BONUS DE PLĂTIT”, „FĂRĂ BONUS”)

astfel încât să aveți:

Rețineți cum Excel arată structura formulei IF - care este un memoriu de ajutor util.

După ce ați introdus formula, apăsați ENTER și o vedeți evaluată pentru primul rând:

Formula a fost evaluată pentru Martin - deoarece a câștigat mai puțin de 40.000 de lire sterline nu are dreptul la niciun bonus.

Și apoi tragem formulele în jos făcând clic pe colțul din dreapta jos și glisând în jos putem stabili dacă fiecare persoană are dreptul la un bonus:

Și vedem că Excel a stabilit care dintre oamenii de vânzări au dreptul la un bonus.

Reveniți la lista tuturor funcțiilor din Excel

Declarații VBA IF

Puteți utiliza, de asemenea, Instrucțiuni If în VBA. Faceți clic pe link pentru a afla mai multe, dar iată un exemplu simplu:

1234567

Sub Test_IF ()Dacă Range ("a1"). Valoare <0 atunciInterval ("b1"). Valoare = "Negativ"End IfEnd If

Acest cod va testa dacă o valoare a celulei este negativă. Dacă da, va scrie „negativ” în celula următoare.