Curingas no Excel: *, ? e ~ no CONT.SE, PROCV e mais

O Excel tem três caracteres curinga para critérios e buscas: * corresponde a qualquer quantidade de caracteres (inclusive nenhum), ? corresponde a exatamente um caractere, e ~ transforma o * ou o ? seguinte de volta em caractere comum. =COUNTIF(A2:A7,"*apple*") conta as células que contêm apple em qualquer posição. No Excel em português é =CONT.SE(A2:A7;"*apple*") (COUNTIF em inglês), e você também pode digitar as fórmulas assim na tabela, em português e com ponto e vírgula.

FórmulasRestaurar

D2fx

=COUNTIF($A$2:$A$7,C2)

	A	B	C	D
1	Product		Pattern	Count
2	Apple juice		*apple*	4
3	Green apple		apple*	2
4	Pineapple		*juice	2
5	Orange juice		?????	0
6	Pear		*e	4
7	Apples

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =CONT.SE($A$2:$A$7;C2)

• *apple* contém apple: 4 correspondências, porque Pineapple também conta.
• apple* começa com apple: só Apple juice e Apples. A CONT.SE ignora maiúsculas.
• *juice termina com juice.
• ????? tem exatamente cinco caracteres. Nenhum desses produtos tem cinco caracteres, então 0; digite Peach em A6 e a contagem vira 1.
• *e termina com e.

Digite o seu próprio padrão na coluna C, como *an* ou P*, e a contagem se atualiza.

Correspondência parcial no PROCV e na PROCX

O PROCV (VLOOKUP em inglês) aceita curingas no modo de correspondência exata (FALSO como último argumento). Junte o curinga ao valor dentro da fórmula, para que só as primeiras letras fiquem em D2:

FórmulasRestaurar

E2fx

=VLOOKUP(D2&"*",A2:B6,2,FALSE)

	A	B	C	D	E
1	Product	Price		Starts with	Price
2	Apple juice	3.5		Pin	4
3	Green apple	1.2			4
4	Pineapple	4
5	Orange juice	3.2
6	Pear	0.9

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =PROCV(D2&"*";A2:B6;2;FALSO)

As duas fórmulas encontram Pineapple. Mude D2 para juice: o padrão do PROCV, juice*, exige que o texto comece com juice e retorna #N/D (#N/A em inglês; a tabela mostra os nomes de erro em inglês), enquanto o padrão da PROCX, *juice*, encontra o primeiro produto que contém a palavra, Apple juice. Como toda correspondência exata, uma busca com curinga retorna a primeira linha que serve, então faça um padrão específico o bastante.

A PROCX (XLOOKUP em inglês) só trata * e ? como curingas quando o quinto argumento, match_mode, é 2. Sem isso, ela procura o asterisco literal. A CORRESP aceita curingas com tipo de correspondência 0, e a CORRESPX com match_mode 2, igual à PROCX. Veja PROCV para o resto dos argumentos.

FórmulasRestaurar

D2fx

	A	B	C	D
1	Product			Phone products
2	Smartphone
3	Headphones
4	Phone stand
5	Laptop bag
6	Charger

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Sua vez: Em D2, conte os produtos cujo nome contém phone em qualquer posição.

VerificarVer resposta

Somar e tirar a média com um curinga

Toda função que recebe critérios lê os critérios do mesmo jeito, então os mesmos padrões funcionam na SOMASE, SOMASES, MÉDIASE, MÉDIASES, MÁXIMOSES e MÍNIMOSES:

FórmulasRestaurar

E2fx

=SUMIF(A2:A7,D2,B2:B7)

	A	B	C	D	E
1	Region	Sales		Pattern	Total
2	North-East	120		North*	285
3	North-West	95		*West	155
4	South	80		?????	150
5	South-West	60
6	East	110
7	North	70

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SOMASE(A2:A7;D2;B2:B7)

North* soma todas as regiões que começam com North, inclusive a própria North, porque o * também corresponde a nada. ????? soma as regiões com exatamente cinco caracteres: South e North.

FórmulasRestaurar

E2fx

	A	B	C	D	E
1	Region	Sales			West total
2	North-East	120
3	North-West	95
4	South	80
5	South-West	60
6	East	110
7	North	70

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Sua vez: Em E2, some as vendas de todas as regiões cujo nome termina com West.

VerificarVer resposta

Encontrar um asterisco ou um ponto de interrogação de verdade com ~

Para contar texto que contém um * ou um ? de verdade, coloque um til na frente. O próprio til se escreve ~~.

FórmulasRestaurar

C2fx

=COUNTIF(A2:A5,"*~**")

	A	B	C
1	Note		Count
2	Rated 5*		1
3	Why?		1
4	Done		4
5	5 stars

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =CONT.SE(A2:A5;"*~**")

C2 conta as células que contêm um asterisco literal (só A2), C3 as que contêm um ponto de interrogação. C4 mostra o outro lado: "*" sozinho corresponde a qualquer texto, então conta todas as células com texto, 4 aqui. Ele pula números e células vazias, o que faz de CONT.SE(intervalo;"*") o jeito comum de contar células com texto.

Quais funções aceitam curingas

A LOCALIZAR aceita curingas dentro de uma fórmula: =LOCALIZAR("b?d";"a bad day") retorna 3 no Excel (a página PROCURAR e LOCALIZAR tem os detalhes). Na FILTRO, use ÉNÚM(LOCALIZAR(...)) como condição em vez de um padrão.

Erro comum: um curinga depois do =

O operador = nunca lê curingas: =A2="*apple*" pergunta se A2 tem os sete caracteres *apple*. No Excel, com Green apple em A2:

=A2="*apple*"                    FALSE
=IF(A2="*apple*","yes","no")     no

No Excel em português, a segunda é =SE(A2="*apple*";"yes";"no"), e a primeira retorna FALSO. Coloque o teste em uma CONT.SE, que retorna 1 ou 0 para uma célula só, ou use a LOCALIZAR:

FórmulasRestaurar

B2fx

=IF(COUNTIF(A2,"*apple*"),"contains apple","no")

	A	B	C
1	Product	COUNTIF test	SEARCH test
2	Green apple	contains apple	contains apple
3	Pear	no	no

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SE(CONT.SE(A2;"*apple*");"contains apple";"no")

A SE trata o 1 da CONT.SE como VERDADEIRO e o 0 como FALSO. A versão com LOCALIZAR não precisa de curinga nenhum, porque a LOCALIZAR já procura o texto em qualquer lugar da célula.