COUNTIF numără greșit valorile care conțin * sau ? (caractere wildcard)
În COUNTIF, SUMIF și VLOOKUP, * înseamnă „orice text” și ? „un caracter”. Criteriul „10*20” se potrivește deci și cu „10x20” sau „10 pe 20”. Ca să cauți chiar caracterul *, pune ~ în fața lui: =COUNTIF(A2:A6,"10~*20") sau =COUNTIF(A2:A6,SUBSTITUTE(E1,"*","~*")).
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
Funcțiile cu criterii (COUNTIF, SUMIF, AVERAGEIF și variantele cu S, plus VLOOKUP, MATCH și XLOOKUP cu wildcard) tratează unele caractere ca wildcard: * = orice șir de caractere, ? = exact un caracter. E util pentru „începe cu” ("Cluj*"), dar devine o problemă când datele chiar conțin aceste caractere: dimensiuni (10*20), coduri (A?B), întrebări din formulare.
Criteriul „10*20” înseamnă „începe cu 10 și se termină cu 20”, așa că numără și „10x20”, „10 pe 20” sau „1020”. Rezultatul e prea mare, fără nicio eroare.
Tilda ~ anulează semnificația specială a caracterului următor: ~* caută un asterisc, ~? un semn de întrebare, iar ~~ o tildă. Când criteriul vine dintr-o celulă, adaugă tilda cu SUBSTITUTE.
De ce apare: cauzele frecvente
1Asteriscul din date e luat drept wildcard
=COUNTIF(A2:A6,"10*20") → 4=COUNTIF(A2:A6,"10~*20") → 2~* înseamnă caracterul * propriu-zis.
2Criteriul vine dintr-o celulă
=COUNTIF(A2:A6,E1) (E1 = 10*20)=COUNTIF(A2:A6,SUBSTITUTE(E1,"*","~*"))SUBSTITUTE pune tilda în fața fiecărui asterisc din E1.
3Semnul întrebării în coduri
=VLOOKUP("A?3",A2:B9,2,FALSE) (găsește și AB3)=VLOOKUP("A~?3",A2:B9,2,FALSE)? se potrivește cu orice caracter; ~? caută exact semnul întrebării.
Repară eroarea, direct în pagină
În F1, =COUNTIF(A2:A6,E1) trebuie să numere cutiile cu dimensiunea scrisă exact ca în E1 (10*20), dar afișează 4, pentru că numără și 10x20 și 10 pe 20. Repară formula din F1 ca să numere doar valorile identice cu E1, păstrând E1 ca sursă a criteriului.
Indiciu
Asteriscul e caracter special în criteriile COUNTIF; există un caracter care îi anulează semnificația.
Reparația
Pune tilda în fața asteriscului din criteriu:
=COUNTIF(A2:A6,SUBSTITUTE(E1,"*","~*"))F1 afișează 2.
De ce merge
În COUNTIF, criteriul 10*20 înseamnă „începe cu 10 și se termină cu 20”, deci se potriveau și 10x20 și 10 pe 20. SUBSTITUTE transformă criteriul în 10~*20, iar tilda îi spune lui COUNTIF că asteriscul trebuie căutat ca atare. Rămân doar cele două celule scrise exact 10*20.
Alte forme ale mesajului
COUNTIF asteriskCOUNTIF wildcard tildeCOUNTIF counts wrong with *VLOOKUP asterisk in dataCOUNTIF cu asterisc
Cum o eviți pe viitor
- Evită * și ? în codurile și cheile pe care le cauți; folosește x sau - (10x20).
- Când criteriul vine din date sau de la utilizator, protejează-l cu
SUBSTITUTE(SUBSTITUTE(E1,"*","~*"),"?","~?"). - Pentru numărare exactă, fără wildcard,
SUMPRODUCT(--(A2:A6=E1))compară textul literal.
Întrebări frecvente
Și comparația cu = folosește wildcard?
Nu. =A2="10*20" compară textul exact. De aceea SUMPRODUCT(--(A2:A6=E1)) e o alternativă sigură pentru numărarea exactă.
XLOOKUP folosește wildcard?
Doar dacă match_mode este 2. Cu potrivirea implicită, XLOOKUP caută textul exact, inclusiv *.
Erori înrudite
SUMIF returnează 0 la căutarea unei părți din text (lipsește wildcard-ul *) Fără caractere wildcard, criteriul "Caiet" se potrivește doar cu celulele care conțin exact „Caiet”, nu și cu „Caiet A4”. Pentru „începe cu” pune * la final, pentru „conține” pune * de ambele părți: =SUMIF(A2:A7,"Caiet*",B2:B7). COUNTIF returnează 0: referința din criteriu e între ghilimele ("<E1") În =COUNTIF(B2:B7,"<E1") Excel caută textul E1, nu valoarea din celula E1, așa că rezultatul e 0. Lasă doar operatorul între ghilimele și lipește referința cu &: =COUNTIF(B2:B7,"<"&E1). La fel la SUMIF, AVERAGEIF și COUNTIFS. COUNTIFS returnează 0 cu două valori pe aceeași coloană (condiție SAU, nu ȘI) COUNTIFS și SUMIFS leagă condițiile cu ȘI: =COUNTIFS(B2:B9,"Livrat",B2:B9,"În curs") caută rânduri care sunt simultan și Livrat și În curs, deci dă 0. Pentru SAU adună două numărări: =COUNTIF(B2:B9,"Livrat")+COUNTIF(B2:B9,"În curs"). Învață Excel de la zero, cu exerciții la fiecare lecție
Curs Excel: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 20 lecții sunt gratuite.
