1. Functia Excel COUNTIF – sintaxa si utilizare

Functia Excel COUNTIF este utilizata pentru numararea celulelor dintr-un set de celule specificat care indeplineste o anumita conditie logica.

Sa luam spre exemplificare tabelul de mai jos, pentru a ne fi mai usor sa urmarim sintaxa:

Exemplul 1.1: Cate produse „Aspen” exista pe coloana Produsul?

=COUNTIF(E2:E11, „Aspen”)

Sintaxa functiei COUNTIF este foarte simpla:

COUNTIF (celulele_unde_numara, criteriu_cautare)

Dupa cum observati, exista doar 2 argumente, ambele fiind necesare:

celulele_unde_numara – defineste una sau mai multe celule de numarat. Puneti intervalul asa cum faceti de obicei in Excel, de ex. E2: E11

criteriu_cautare – defineste conditia logica care stabileste ce anume conteaza la numarare. Poate fi un numar, sir text, referinta sau expresie a unei celule. De pilda, puteti utiliza astfel de criterii: „15”, C2, „> = 15”, „Aspen”

Observatie: Functia CountIF, ca majoritatea functiilor Excel NU este case-sensitive, cu alte cuvinte nu conteaza daca scrieti aspen cu litere mici sau mari. In acest articol arat si cum se pot face numararile tinand cont de tipul literelor.(majuscule/minuscule).

2. Formule COUNTIF pentru texte si numere (cu potrivire exacta)

Exemplul 2.1: De cate ori apare compania Tri Fly pe coloana Compania?

Toti ceilalti operatori de comparatie sunt obligatoriu de specificat

=COUNTIF(D2:D13, „Tri fly”)

                                Sau

=COUNTIF(D2:D13, „=Tri fly”)

Operatorul de comparatie „=” este optional , daca il omiteti Excel intelege ca ati dorit sa-l folositi.

Exemplul 2.2: De cate ori apare compania scrisa intr-o anumita celula?

Putem sa folosim o adresa de celula pentru argumentul de criterii:

=COUNTIF(D2:D13, H6)

                sau

=COUNTIF(D2:D13, „=” &  H6)

Observati ca am concatenat semnul = cu valoarea celulei in care am compania cautata. Pare un exces de zel inutil, insa o sa vedem in cele ce urmeaza utilitatea unei astfel de abordari.

In cazul in care cautam valori exacte, recomand sa folositi liste cu valorile implicite ale acelor valori cautate.

De exemplu, in cazul nostru in celula H6 vreau sa permit introducerea unei companii folosind un Drop Down List (vezi animatia de mai jos):

Pentru a face un Drop Down List, urmariti a doua animatie:

Exemplul 2.3 Cum cautam numere exacte

De cate ori apare facura cu ID 659442 ?

=COUNTIF(B2:B13, 659442)

Observati ca numerele NU se scriu intre ghilimele cand se pun direct in argumentul criteria

Insa daca dorim sa punem explicit operatorul de comparatie (in cazul nostru semnul =), suntem obligati sa punem tot argumentul criteria intre ghilimele:

=COUNTIF(B2:B13, „=659442”)

 —ASA DA

=COUNTIF(B2:B13, =659442)

— GRESIT, deoarece criteriul de la argumentul al doilea se scrie intotdeauna intre ghilimele, atat timp cat acesta contine in el explicit operatorul de comparatie (in acest caz =)

=COUNTIF(B2:B13, „=” & 659442)

 — ASA DA, se foloseste aceasta metoda cand numarul cautat se afla in alta celula sau daca este intors de o alta formula.

3. CountIF cu operatori de comparatie

Putem sa folosim operatorii de comparatie consacrati in Excel: <, <=, >, >=, =, <>

Iata mai jos cateva exemple punctuale realizate pe urmatorul tabel cu date:

4. COUNTIF cu metacaractere / wildcards (potrivire partiala)

Caracterere speciale (wildcards) ne permit sa cautam texte ce au o „potrivire partiala”.

In Excel exista trei caractere speciale, numite si wildcards (sau metacaractere):

* (asterix) – inlocuieste oricate caractere

? (semnul intrebarii) -inlocuieste un singur caracter

~ (tilda) – se mai numeste si caracter de „escape” pentru ca transforma caracterul ce apare dupa el (de regula * sau ?) in caracter obisnuit

Prezint cateva exemple de folosire a functiei CountIF cu potrivire partiala cu cautarile realizate pe datele din tabelul urmator:

Sa se numare toate Produsele ce contin in denumirea lor cuvantul „mere”:

=COUNTIF(A2:A12, „*mere*”)

rezultatul este 3

Cate produse ce incep cu litera „m” exista?

=COUNTIF(A2:A12, „m*”)

rezultatul este 5

La cate produse numele lor se termina in cuvantul „verzi”?

=COUNTIF(A2:A12, „*verzi”)

rezultatul este 2

Cate produse de tipul: COD:XXXXX exista?

=COUNTIF(A2:A12, „COD:?????”)

rezultatul este 3

Cate produse contin in denumire semnul intrebarii „?”

=COUNTIF(A2:A12, „*?*”)

; este gresit, deoarece caracterul „?” de aici nu e litera normala, ci wildcard (adica inlocuieste orice alta litera). Este ca si cum am spune: sa se gaseasca Produsele ce au in denumire…orice litera 😊

=COUNTIF(A2:A12, „*~?*”)

semnul „~” (tilda) neaga wildcardul ce vine imediat dupa el. In acest caz carcaterul „?” devine unul obisnuit, adica se cauta produsele care chiar au acel semn in denumire.

Este recomandat sa nu scriem manual in formule ce anume cautam deoarece va fi greu sa modificam de fiecare data textul cautat.

Putem sa folosim concatenarea (cu semnul &) pentru a referentia textul cautat partial aflat intr-o celula separata:

=COUNTIF(A2:A12, „*” &C4 & „*”)

5. Numararea celulelor necompletate sau goale (populate cu sirul empty, adica cu „”)

Pentru exemplificare se da tabelul de mai jos

Exemplul 5.1: Cum folosesc CountIF ca sa numar doar celulele corespondente textelor BLANK sau EMPTY („”)

=COUNTIF(A2:A10,””)

, rezultatul este 2 (include atat celulele lasate libere, adica BLANK, dar si celulelece contin sirul gol („”) sau care au formule ce intorc „”)

Sau putem sa folosim o alta functie dedicata:

=COUNTBLANK(A2:A10)

Exemplul 5.2: Cum folosesc COUNTIF ca sa numar doar celulele corespondente textelor BLANK (NU si empty „”)

=CountIF(A2:A10,”=”)

, rezultatul este 1, pentru ca avem doar o celula BLANK

Exemplul 5.3: Cum folosesc CountIF ca sa numar doar celulele corespondente textelor Empty („”)

=CountIF(A2:A10,””)- CountIF(A2:A10,”=”)

, rezultatul este 1 (am scazut (BLANK + EMPTY)- BLANK) 😊

Exemplul 5.4: Cum folosesc CountIF ca sa numar celulele fara BLANKS?

=CountIF(A2:A10,”<>”)

 nu ia in considerare BLANK, insa le ia la socoteala pe cele empty

Sau folosim o functie dedicata numararii fara BLANKS:

=COUNTA(A2:A10)

Exemplul 5.5 Cum folosesc CountIF ca sa numar celulele fara BLANKS si fara Empty (adica care au ceva in ele, orice insa nu goale sau blank)?

=ROWS(A2:A10)-COUNTBLANK(A2:A10)

, functia ROWS intoarce numarul total de randuri (indiferent daca sunt sau nu completate) din care scadem nr de celule blank / empty

6. Sfaturi de urmat daca formula dvs. cu CountIF returneaza erori

Intrebare: Formula mea COUNTIF nu functioneaza! Ce am facut gresit?

Raspuns: Daca ati scris o formula care pare a fi corecta, dar care nu functioneaza sau nu produce rezultatul dorit, incepeti sa verificati cele mai evidente lucruri, cum ar fi o celulele selectate, conditiile de indeplinit, utilizarea semnului ampersand pentru concatenare (&) sau a ghilimelelor.

Greseala frecventa # 1: Am pus din gresela un spatiu in criteriul care stabileste textul dupa care se numare

=COUNTIF(A2:A9, „mere verzi „)

Sigur ca am putea sa-l stergem pe acel spatiu ce apare dupa cuvantul verzi, insa ce ne facem daca apar mai multe spatii (mai ales cele din interior sunt greu de gasit si sters)

Greseala frecventa # 2: Am pus din gresela unul sau mai multe spatii in interiorul criteriul care stabileste textul dupa care se numare

In acest caz mai bine apelam la functia TRIM care scoate automat toate spatiile inutile (atat dintre cuvinte cat si cele ce apar eventual la inceputul sau sfarsitul textului).

=Trim(”  Curs       Excel          „)

 intoarce „Curs Excel”

Astfel, formula noastra devine:

=COUNTIF(A2:A9, trim(„mere  verzi”))

Greseala frecventa # 3: Avem spatii inutile in celulele unde cautam:

O solutie rapida si simpla este sa adaugam o coloana calculata separata de cea cu probleme si sa scriem o formula care aduce acolo produsele fara spatii inutile (vezi animatia de mai jos)

Apoi sa inlocuim cautarea cu countif sa se faca pe aceasta noua coloana (care deja nu mai are in ea spatii aditionale)

=COUNTIF(B2:B9, „mere verzi”)

Greseala frecventa # 4: Avem caractere invizibile inutile in celulele unde cautam:

Atentie mare ca in afara spatiilor mai exista si caractere CU ADEVARAT INVIZIBILE, adica cele care nu ocupa niciun loc vizibil pe ecran.

Aceste caractere invizibile dau mare bataie de cap, chiar si expertilor in expert. Insa cum dvs. va aflati in cel mai bun loc de pe Internetul romanesc in materie de Microsoft Excel, permiteti-mi sa fac o scurta introducere in materie de „Curatire a datelor inutile”, cu promisiunea ca am sa scriu in articol separat in care am sa tratez exhaustiv acest aspect.

Daca ati eliminat spatiile inutile (cu functia Trim) si tot nu va numara corect, retineti ca aveti sanse mari ca sa existe caractere „mute” (sau invizibile) in setul de date cu care lucrati. Acest aspect poate fi valabil si daca faceti cautari cu Find and Replace sau cu Vlookup. Asa incat mare atentie si cititi tot ce va explic in continuare.

Exista doua tipuri de caractere invizibile:

• Caractere invizibile cu cod ASCII UNICODE

• Caractere invizibile cu cod in afara ASCII (NON-UNICODE)

Pe scurt, toate caracterele invizibile sau nu au un cod unic numeric. De pilda litera A are codul 65, iar litera B are codul 66

Codul Ascii pentru literele mici este egale cu cel al literelor mari la care se adauga constanta 32

De exemplu, litera c are codul Ascii al literei C (mare) + 32, adica 67+32=99

Daca codul este pana in 127, acel caracter se numeste ASCII UNICODE, daca codul sau este mai mare de 127 se numeste NON-UNICODE

 

Scenariul 1: Cum scapam de caracterele invizibile ASCII UNICODE (cu cod pana in 127)

In acest caz este simplu. Exista o functie Excel care stiu sa scoata automat caracterele invizibile ASCII UNICODE, se numeste CLEAN

Adaugam o coloana calculata in care folosim functia CLEAN (vezi animatia)

Apoi facem cautarea pe aceasta noua coloana cu functia CountIF:

=COUNTIF(C2:C9,”mere verzi”)

Scenariul 2: Cum scapam de caracterele  NON-UNICODE (cu cod mai mare de 127)

Daca, cumva, in coloana unde cautam avem caractere invizibile In afara spectrului ASCII UNICODE (adica caractere NON-UNICODE), atunci problema se complica putin, in sensul ca trebuie aflat ce caracter ne face probleme. Sa presupunem ca am aflat codul caracterului NON-UNICODE care ne face probleme (cum anume, am sa va arat intr-un articol viitor), sa zicem ca este vorba de caracterul cu codul 141.

Folosim functia Substitute pentru a-l inlocui cu un caracter inofensiv sau mai bine cu sirul gol (empty „”)

Facem o noua coloana calculata care copiaza numele produsului insa fara caracterul cu codul 141:

=SUBSTITUTE(B5,CHAR(141),””)

Iar apoi numaram cu CountIF pe acea noua coloana calculata care sigur nu mai are acel caracter. Dar poate avea altele, asa incat procedeul trebuie reluat pentru fiecare caracter special care ne face probleme.

Asta este totul pentru astazi. In articolul urmator, vom explora mai multe moduri de a numara celule in Excel cu mai multe conditii (folosind functiile COUNTIFS).

Va invit sa parcurgeti si articolul:

Numararea conditionata in Excel cu functiile COUNTIFS si COUNTIF (Partea II) – pentru avansati

Sper sa ne revedem saptamana viitoare 😊

Va multumesc ca ma urmariti!