Excela

Cum se utilizează criteriile de formulă (50 de exemple)

How Use Formula Criteria

Una dintre cele mai importante abilități de a construi formule utile este crearea criterii - partea unei formule care decide ce să includă sau să excludă într-un calcul. Cu toate acestea, poate fi surprinzător de dificil să construim criterii eficiente, deoarece necesită o bună înțelegere a modului în care Excel tratează datele. Dacă ați petrecut vreodată o după-amiază depanând o formulă care pare că ar trebui să „funcționeze”, știți la ce mă refer :)





Acest ghid își propune să vă ajute să construiți formule care să funcționeze primul timp.

Notă: specialiștii în limbă vor sublinia că „criteriul” este singular și „criteriul” este plural, dar voi folosi „criteriile” în ambele cazuri pentru a menține lucrurile simple.





Numele funcțiilor de pe fundalurile întunecate de mai jos sunt linkuri către mai multe informații.

Ce fac criteriile?

Printre altele, criteriile:

  • Fluxul logic direct cu logica IF / THEN
  • Limitați procesarea numai la valorile potrivite
  • Creați sume și numărări condiționate
  • Filtrează datele pentru a exclude informațiile irelevante
  • Declanșați reguli de formatare condiționată

Pentru a ajuta la stabilirea scenei, să analizăm trei exemple de criterii în acțiune.



Exemplul nr. 1

În ecranul de mai jos, F3 conține această formulă:

 
= IF (E3>30,'Yes','No')

Exemplu de criterii de formulă # 1

Traducere: dacă valoarea în E3 este mai mare de 30, returnați „Da”, altfel returnați „Nu”.

Aici, E3> 30 sunt criteriile, utilizate în IF pentru a determina dacă formula ar trebui să returneze „Da” sau „Nu” pentru fiecare factură.

Exemplul nr. 2

În exemplul următor, D3 conține această formulă:

 
= IF ( OR (B3='red',B3='green'),C3*1.1,C3)

Exemplu de criterii de formulă # 2 - creșteți prețul dacă este roșu sau verde

cum să introduceți notația științifică în excel

Traducere: dacă B3 este „roșu” sau „verde”, creșteți prețul cu 10%. În caz contrar, returnați prețul inițial.

Exemplul nr. 3

În acest exemplu, funcția SUMIFS este utilizată pentru a însuma totalul numai atunci când culoarea este „roșie”:

 
= SUMIFS (E3:E7,B3:B7,'red')

Exemplu de criterii de formulă # 2 - SUMIF când culoarea este

Traducere: suma valorilor în E3: E7 când valoarea în B3: B7 este „roșie”.

Criterii de bază

Această secțiune acoperă elementele de bază ale criteriilor de formulă și câteva modalități simple de a verifica dacă criteriile sunt performante conform așteptărilor.

Care sunt criteriile?

Criteriile sunt expresii logice care returnează TRUE sau FALSE sau echivalenții lor numerici, 1 sau 0.

Asta e.

Trucul constă în a construi criterii într-un mod astfel încât acestea să returneze TRUE doar atunci când testul îndeplinește criteriile dvs. exacte. În toate celelalte cazuri, criteriile ar trebui să returneze FALS sau zero. Dacă puteți stăpâni această idee, aveți fundamentul pentru a construi și a înțelege multe formule avansate.

Operatori logici

Criteriile folosesc adesea operatorii logici enumerați în tabelul de mai jos.

Operator Sens Exemplu
= Egal cu = A1 = 10
Nu este egal cu = A110
> Mai mare ca = A1> 100
< Mai puțin decât = A1<100
> = Mai mare sau egal cu = A1> = 75
<= Mai mic sau egal cu = A1<=0

Operatorii logici pot fi combinați în diferite moduri, așa cum se vede în exemplele de mai jos.

Funcții logice

Excel are mai multe așa-numite „funcții logice” care pot fi utilizate pentru a construi și utiliza condiții. Tabelul de mai jos prezintă funcțiile logice cheie.

Funcţie Scop
DACĂ Testați un flux logic direct cu o condiție
IFS Testați condițiile multiple fluxul logic direct
NU Inversați criteriile sau rezultatele
ȘI Testează mai multe condiții, returnează TRUE dacă toate sunt TRUE
SAU Testează mai multe condiții, returnează TRUE dacă cel puțin una este TRUE
XOR SAU exclusiv - returnează TRUE dacă unul sau altul, nu ambele
IFERROR Captați erorile și întoarceți rezultate alternative

Criterii multiple

Bineînțeles, există multe cazuri în care veți dori să utilizați mai multe criterii. În situații simple, puteți utiliza funcțiile ȘI, SAU și NU. Iată câteva exemple:

 
= AND (A1>0,A1<10) // greater than 0 and less than 10 = OR (A1='red',A1='blue') // red or blue = NOT ( OR (A1='red',A1='blue')) // not red or blue = AND ( ISNUMBER (A1),A1>100) // number greater than 100

Wildcards

Excel oferă trei „metacaractere” pentru potrivirea textului în formule:

Caracter Nume Scop
* Asterisc Potriviți zero sau mai multe caractere
? Semnul întrebării Potrivește orice personaj
~ Marca de accent Potriviți comodinul literal

Jokerurile pot fi folosite singure sau combinate pentru a obține o varietate de comportamente de potrivire:

Utilizare Comportament Se va potrivi
? Orice personaj „A”, „B”, „c”, „z” etc.
?? Orice două personaje „AA”, „AZ”, „zz” etc.
??? Orice trei personaje „Jet”, „AAA”, „ccc” etc.
* Orice personaje „măr”, „MERE”, „A100” etc.
* a Se termină în „th” „baie”, „a patra” etc.
c * Începe cu „c” „Pisică”, „CAB”, „cindy”, „bomboane” etc.
? * Cel puțin un personaj „a”, „b”, „ab”, „ABCD” etc.
??? - ?? 5 caractere cu hypen „ABC-99”, „100-ZT” etc.
* ~? Se încheie cu semnul întrebării „Bună?”, „Cineva acasă?” Etc.
* xyz * Conține „xyz” „codul este XYZ”, „100-XYZ”, „XyZ90” etc.

Iată câteva exemple de utilizare a comodinelor pentru criterii în funcția COUNTIFS.

 
= COUNTIFS (A1:A100,'*red*') // count cells that contain 'red' = COUNTIFS (A1:A100, 'www*') // count cells starting with 'www' = COUNTIFS (A1:A100,'?????') // count cells with 5 characters

Nu toate funcțiile permit metacaractere. Iată o listă de funcții comune care fac:

Observați că funcția IF este nu pe această listă. Pentru a obține un comportament wildcard cu IF, puteți combina funcțiile SEARCH și ISNUMBER, așa cum este descris mai jos.

Criterii de testare

Modul clasic de a testa criteriile este de a le înfășura în funcția IF. De exemplu, pentru a verifica „roșu” sau „albastru”, putem înfășura funcția SAU în interiorul IF astfel:

 
= IF ( OR (B3='red',B3='blue'),'OK', '')

Criterii de formulă - testare cu funcția IF

Traducere: dacă culoarea este „roșie” sau „albastră”, reveniți la „OK”. Altfel nu returnează nimic.

Cu toate acestea, puteți testa criteriile direct pe foaia de lucru ca o formulă. Să presupunem că doriți să procesați valori de 80 sau mai mari. În ecranul de mai jos, C3 conține această formulă, copiată.

 
=B3>=80

Criterii de formulă - testarea directă pe foaia de lucru

Traducere: valoarea în B3 este mai mare sau egală cu 80.

Fără IF sau altă funcție, obținem doar un rezultat al TRUE sau FALSE, dar este suficient să verificăm că criteriile funcționează conform așteptărilor.

Nu fi aruncat de semnul egal (=) atunci când testezi criteriile ca formulă. Toate formulele Excel trebuie să înceapă cu un semn egal, deci trebuie incluse. Eliminați semnul egal atunci când mutați criteriile într-o altă formulă.

O altă modalitate de testare a criteriilor este utilizarea F9 pentru a evalua criteriile existente. Doar selectați cu atenție o expresie logică și apăsați F9. Excel va evalua imediat expresia și va afișa rezultatul.

Video: Cum se folosește F9 pentru a depana o formulă .

Adăugarea de criterii la formule

Desigur, în majoritatea cazurilor, nu doriți să returnați TRUE sau FALSE într-o celulă, doriți să returnați o altă valoare pe baza criteriilor care returnează TRUE sau FALSE. Pentru a face acest lucru, trebuie doar să eliminați semnul egal și să adăugați criteriile acolo unde este necesar în formulă.

În exemplul de mai jos, formula C3 conține această formulă, care utilizează criteriile de mai sus ca test logic în interiorul IF:

 
= IF (B3>=80,'Pass','Fail')

Adăugarea criteriilor la o formulă

Traducere: dacă valoarea în B3 este mai mare sau egală cu 80, returnați „Treceți”. În caz contrar, returnați „Fail”.

Vezi și: 23 de sfaturi pentru formule ( video | articol )

Exemple de criterii

Această secțiune prezintă exemple de cum să construiți criterii pentru a îndeplini o varietate de sarcini pentru diferite tipuri de conținut.

Gol sau nu gol

Există mai multe moduri în care puteți verifica celulele necompletate sau necompletate. Pentru a returna TRUE dacă A1 este necompletat, puteți utiliza fie:

 
= ISBLANK (A1) =A1=''

Pentru a inversa logica și a verifica celulele care nu sunt goale, puteți utiliza:

 
= NOT ( ISBLANK (A1)) =A1''

O altă modalitate de a testa o celulă goală este de a verifica numărul de caractere:

 
= LEN (A1)=0

Dacă numărul este zero, celula este „goală”. Această formulă este utilă atunci când se testează celule care pot conține formule care returnează șiruri goale (''). ISBLANK (A1) va returna FALSE dacă o formulă returnează un șir gol în A1, dar LEN (A1) = 0 va reveni la TRUE.

Criterii pentru text

Pentru a returna TRUE dacă o celulă conține „roșu”, puteți utiliza:

 
=A1='red' 

Pentru a inversa logica, puteți utiliza funcția NOT sau not egal cu operator () astfel:

 
= NOT (A1='red') =A1'red'

Observați în fiecare caz textul ESTE cuprins între ghilimele duble (de exemplu, „roșu”). Dacă nu utilizați ghilimele, Excel va crede că încercați să faceți referire la un interval numit sau la o funcție și va returna eroarea #NAME.

Criterii pentru numere

Pentru a testa dacă un A1 este egal cu 5, puteți utiliza criterii de genul acesta:

 
=A1=5 // TRUE if A1 equals 5

Iată câteva alte exemple de criterii pentru testarea valorilor numerice:

 
=A1<100 // less than 100 =A1>=1 // greater than or equal to 0 =A10 // not equal to zero = AND (A1>0,A1<5) // greater than zero, less than 5 = MOD (A1,3)=0 // value is a multiple of 3

Numerele de notificare NU sunt cuprinse între ghilimele duble. Dacă includeți un număr între ghilimele, îi spuneți lui Excel să trateze numărul ca text, ceea ce va face ca criteriile să fie inutile. De asemenea, nu uitați asta formatarea numerelor în Excel afectează numai afișajul și nu modifică datele numerice în niciun fel. Nu includeți semne de dolar ($), semne de procent (%) sau alte informații de formatare atunci când creați criterii pentru testarea numerelor.

Criterii pentru date

Datele din Excel sunt doar numere, ceea ce înseamnă că sunteți liber să utilizați operațiuni matematice obișnuite la date, dacă doriți. Cu datele comenzii în coloana A și datele de livrare în coloana B, această formulă din coloana C va marca termenele de livrare mai mari de 3 zile ca „întârziate”:

 
= IF ((B2-A2)>3,'Late','')

Excel oferă, de asemenea, un număr mare de funcții specifice pentru lucrul cu date. De exemplu, pentru a verifica dacă o dată este „în viitor”, puteți utiliza funcția TODAY astfel:

 
=A1> TODAY ()

Exemplu de dată a criteriilor de formulă - mai mare decât astăzi

Pentru a verifica dacă apare o dată în următoarele 30 de zile, formula poate fi extinsă la:

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

Traducere: DACĂ A2 este mai mare decât astăzi ȘI mai mic sau egal astăzi + 30 de zile, returnează TRUE.

cum să alegeți o celulă aleatorie în Excel

Iată câteva alte exemple de criterii pentru date, presupunând că A1 conține o dată validă:

 
= DAY (A1)>15 // greater than 15th = MONTH (A1)=6 // month is June = YEAR (A1) = 2019 // year is 2019 = WEEKDAY (A1)=2 // date is a Monday

Cea mai sigură modalitate de a insera o dată validă în criterii este utilizarea funcției DATE, care acceptă anul, luna și ziua ca argumente separate. Iată câteva exemple:

 
=A1> DATE (2019,1,1) // after Jan. 1, 2019 = AND (A1>= DATE (2018,6,1),B4<= DATE (2018,8,31)) // Jun-Aug 2018

Criterii pentru vremuri

Timpurile sunt numere fracționate în Excel, deci puteți utiliza matematica simplă pentru timp, în unele cazuri. De exemplu, pentru a verifica dacă o oră în A1 este după 12:00 PM (mai mult de 12 ore), puteți utiliza:

 
=A1>.5

Acest lucru funcționează deoarece 1 zi = 24 de ore, deci o jumătate de zi = 12 ore.

Pentru lucrări mai granulare, Excel are funcții speciale pentru a extrage timpul pe componente. De exemplu, cu ora 8:45 AM în celula A1:

 
= HOUR (A1) // returns 8 = MINUTE (A1) // returns 45 = SECOND (A1) // returns 0

Cea mai sigură modalitate de a insera o oră în criterii este utilizarea funcției TIME. Aici sunt cateva exemple:

 
=A1> TIME (9,15,0) // after 9:15 AM = AND (A1>= TIME (9,0,0),A1<= TIME (17,0,0)) // 9 AM to 5 PM

Criterii pentru SUME, COUNTIFE etc.

Criteriile pentru SUMIFS, COUNTIFS, AVERAGEIFS și funcții similare bazate pe interval urmează reguli ușor diferite. Acest lucru se datorează faptului că criteriile sunt împărțite în două părți (gama criteriilor și criteriile), iar acest lucru are impact asupra sintaxei atunci când criteriile includ operatori.

Criteriile simple bazate pe egalitate nu necesită o manipulare specială. Operatorul egal (=) este implicit, deci nu este necesar să îl includeți în criterii:

 
= COUNTIFS (A1:A100,10) // count cells equal to 10 = COUNTIFS (A1:A100,'red') // count cells that equal 'red'

Cu toate acestea, lucrurile se schimbă atunci când adăugăm operatori:

 
= COUNTIFS (A1:A100,'>10') // count cells greater than 10 = COUNTIFS (A1:A100,'<0') // count cells less than zero

Observați ghilimelele ('') în jurul criteriilor? Acestea sunt necesare atunci când criteriile includ un operator în aceste funcții.

Criterii pentru tipurile de date

Excel permite trei tipuri principale de date: text, numere și elemente logice. Datele, orele, procentele și fracțiile sunt doar numere cu formatarea numerelor aplicate pentru a schimba modul în care sunt afișate. În mod implicit, numerele sunt aliniate la dreapta, textul este aliniat la stânga, iar valorile logice sunt centrate. Dar un utilizator poate suprascrie alinierea manual, deci nu este un test bun de tip.

Excel oferă trei funcții pe care le puteți utiliza pentru a verifica tipurile de date: ISTEXT, ISNUMBER și ISLOGICAL. Aceste funcții returnează ADEVĂRAT sau FALS. În ecranul de mai jos, celulele D3, F3 și H3 conțin aceste formule, copiate:

 
= ISTEXT (B3) = ISNUMBER (B3) = ISLOGICAL (B3)

Criterii de formulă - utilizarea funcțiilor pentru testarea tipurilor de date

Pentru a utiliza aceste funcții ca criterii, trebuie doar să plasați apoi în locația corectă a unei formule. De exemplu, pentru a verifica dacă A1 conține un număr, puteți utiliza ISNUMBER ca test logic în interiorul IF astfel:

 
= IF ( ISNUMBER (B3),'OK','Invalid')

Notă: Formulele nu sunt un tip de date, dar puteți verifica formulele cu Funcția ISFORMULA :

 
= ISFORMULA (A1) // TRUE if A1 contains formula

Devenind fantezie

Exemplele de mai sus arată fundamentele utilizării criteriilor în formule, există multe modalități de a face criteriile mai sofisticate. Această secțiune explorează câteva tehnici.

Crearea variabilă a criteriilor

Este adesea util să faci criterii variabile, făcând referire la o celulă din foaia de lucru. De exemplu, în foaia de lucru de mai jos, scorul de trecere se află în celula E3, iar formula pentru a determina trecerea sau eșuarea arată astfel:

 
= IF (B3>=$E,'Pass','Fail')

Crearea variabilă a criteriilor - exemplu de scor

Plasarea scorului de trecere în celula E3 facilitează schimbarea în orice moment fără a edita formule. Rețineți că referința la $ E $ 3 este absolut pentru a preveni modificările pe măsură ce formula este copiată.

Crearea variabilă a criteriilor în COUNTIFS, SUMIFS etc.

Ca și până acum, dacă criteriile testează egalitatea, nu este necesară o manipulare specială:

 
= COUNTIF (range,A1) // count cells equal to A1

Cu toate acestea, dacă criteriile includ operatori, va trebui să utilizați concatenare . De exemplu, pentru a număra celulele mai mare ca A1, va trebui să vă alăturați „>” la „A1” astfel:

 
= COUNTIF (range,'>'&A1)

Concatenarea rulează mai întâi. Dacă A1 conține numărul 10, aceasta este formula după concatenare:

 
= COUNTIF (range,'>10')

Observați că modelul este același cu cel explicat anterior - dacă criteriile includ operatori, acesta trebuie să apară între ghilimele (').

Iată mai multe exemple de utilizare a concatenării în criterii:

 
= COUNTIF (range,'<'&B1) // count less than value in B1 = COUNTIF (range,''&'') // count not blank cells = COUNTIF (range,'*'&B1&'*') // count contains text in B1 = COUNTIF (range,'>'& TODAY ()) // count dates in future = COUNTIF (range,'<'& TODAY ()+7) // count up to 7 days from today

Conține text specific

O situație dificilă este când doriți să testați dacă o celulă conține text specific. Pentru funcții care acceptă metacaractere (cum ar fi COUNTIFS, SUMIFS etc.), puteți utiliza metacaracterele pentru a face acest lucru. De exemplu, pentru a număra celulele care conțin „roșu” oriunde într-o celulă cu COUNTIFS, puteți utiliza un asterisc ca acesta:

 
= COUNTIFS (A1:A100,'*red*')

Cu toate acestea, multe alte funcții (cum ar fi funcția IF) nu acceptă metacaracterele. În acest caz, puteți combina ISNUMBER și SEARCH pentru a crea criterii care verifică o celulă pentru o potrivire parțială. În ecranul de mai jos, D3 conține această formulă:

 
= ISNUMBER ( SEARCH (C3,B3))

Criterii de formulă - celula conține text specific

Puteți utiliza această expresie ca criterii în interiorul IF astfel

 
= IF ( ISNUMBER ( SEARCH ('red',A1)),'red', '')

Traducere: dacă „roșu” se găsește oriunde în A1, întoarceți „roșu”.

Acest lucru funcționează deoarece SEARCH returnează o poziție numerică dacă se găsește „roșu”, iar ISNUMBER returnează TRUE. Dacă nu, SEARCH returnează o eroare, iar ISNUMBER returnează FALSE. Pentru mai multe detalii, a se vedea această pagină .

IF-uri imbricate

Formulele IF imbricate sunt adesea folosite pentru a verifica mai multe criterii și a returna mai multe rezultate. În general, provocarea este de a construi IF imbricate, astfel încât critieria să ruleze în secvența corectă. De exemplu, aici este o formulă IF imbricată care atribuie o notă de literă pe baza unui scor numeric:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Exemplu IF imbricat pentru atribuirea notelor

Observați că testăm mai întâi scoruri mici, apoi scoruri progresiv mai mari.

Mai mult: 19 sfaturi pentru IF-uri imbricate (cu alternative)

Constante de matrice în criterii

Constantele de matrice sunt matrici codificate cu valori fixe ca aceasta: {'A', 'B', 'C'}. Uneori pot fi folosite ca criterii pentru a crea criterii logice simple SAU. De exemplu, în ecranul de mai jos, celula F4 conține această formulă:

 
= SUM ( SUMIFS (C3:C7,B3:B7,{'red','gold'}))

Criterii de formulă cu constante matrice

Traducere: SUMĂ vânzări în care culoarea este „roșu” SAU „auriu”.

Deoarece oferim SUMIFS două valori pentru criterii, acesta returnează două rezultate. Funcția SUM returnează apoi suma celor două rezultate.

Criterii simple pentru matrice

Formulele matrice sunt un subiect complicat, dar criteriile pentru formulele matrice simple pot fi destul de simple. Un exemplu clasic este utilizarea funcției IF pentru a „filtra” valorile care ar trebui excluse, procesând rezultatul cu o altă funcție.

În ecranul de mai jos, formula din G4 este:

 
{= MAX ( IF (regions=F4,totals))}

unde 'regiuni' este gama denumită B3: B8 și „totaluri” reprezintă intervalul numit D3: D8.

Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.

Rezultatul este valoarea maximă pentru fiecare regiune.

Criterii de formulă pentru formula de matrice simplă

Pentru criterii, folosim expresia:

 
regions=F4

Aceasta compară toate valorile regiunii cu „Vest” din F4 și returnează următorul rezultat al matricei în testul logic pentru IF:

 
{TRUEFALSETRUEFALSETRUEFALSE}

Matricea finală returnată de IF arată astfel:

 
{10500FALSE12500FALSE11800FALSE}

Numai valorile asociate cu regiunea „Vest” intră în matrice. Valorile asociate regiunii „Est” sunt FALSE.

Funcția MAX returnează apoi cea mai mare valoare din matrice, ignorând toate valorile FALSE.

Criterii avansate de formulă

Mai jos sunt linkuri către exemple de criterii de formulă mai avansate. Fiecare link are o captură de ecran și o explicație completă.

Mai multe resurse de formulă

Următoarele linkuri conțin informații mai detaliate despre formulele Excel:

Autor Dave Bruns


^