O Excel tem muitas funções em que o usuário precisa especificar um único ou vários critérios para obter o resultado. Por exemplo, se você deseja contar células com base em vários critérios, pode usar as funções CONT.SE ou CONT.SE no Excel.
Este tutorial cobre várias maneiras de usar um único ou vários critérios na função CONT.SE e CONT.SE no Excel.
Embora eu esteja focando principalmente nas funções CONT.SE e CONT.SE neste tutorial, todos esses exemplos também podem ser usados em outras funções do Excel que usam vários critérios como entradas (como SOMASE, SOMASE, MÉDIA e MÉDIA).
Uma introdução às funções COUNTIF e COUNTIFS do Excel
Vamos primeiro aprender a usar as funções CONT.SE e CONT.SE no Excel.
Função CONT.SE do Excel (leva critérios únicos)
A função CONT.SE do Excel é mais adequada para situações em que você deseja contar células com base em um único critério. Se você quiser contar com base em vários critérios, use a função COUNTIFS.
Sintaxe
= CONT.SE (intervalo, critérios)
Argumentos de entrada
- alcance - o intervalo de células que você deseja contar.
- critério - os critérios que devem ser avaliados em relação ao intervalo de células para que uma célula seja contada.
Função Excel COUNTIFS (aceita vários critérios)
A função CONT.SE do Excel é mais adequada para situações em que você deseja contar células com base em vários critérios.
Sintaxe
= COUNTIFS (intervalo_critérios1, critérios1, [intervalo_critérios2, critérios2]…)
Argumentos de entrada
- intervalo_de_critérios1 - O intervalo de células para o qual você deseja avaliar em relação aos critérios1.
- criterio1 - os critérios que você deseja avaliar para intervalo_de_criterios1 para determinar quais células contar.
- [intervalo_de_critérios2] - O intervalo de células para o qual você deseja avaliar em relação aos critérios2.
- [critérios2] - os critérios que você deseja avaliar para intervalo_de_criterios2 para determinar quais células contar.
Agora, vamos dar uma olhada em alguns exemplos do uso de vários critérios nas funções CONT.SE no Excel.
Usando NUMBER Critérios em Funções Excel COUNTIF
Nº 1 Contagem de células quando os critérios são iguais a um valor
Para obter a contagem de células em que o argumento dos critérios é igual a um valor especificado, você pode inserir diretamente os critérios ou usar a referência de célula que contém os critérios.
Abaixo está um exemplo em que contamos as células que contêm o número 9 (o que significa que o argumento dos critérios é igual a 9). Aqui está a fórmula:
= CONT.SE ($ B $ 2: $ B $ 11, D3)
No exemplo acima (na foto), o critério está na célula D3. Você também pode inserir os critérios diretamente na fórmula. Por exemplo, você também pode usar:
= CONT.SE ($ B $ 2: $ B $ 11,9)
# 2 Contar células quando os critérios forem superiores a um valor
Para obter a contagem de células com um valor maior que um valor especificado, usamos o operador maior que (“>”). Podemos usá-lo diretamente na fórmula ou usar uma referência de célula que tenha os critérios.
Sempre que usamos um operador em critérios no Excel, precisamos colocá-lo entre aspas duplas. Por exemplo, se o critério for maior que 10, precisamos inserir “> 10” como critério (veja a foto abaixo):
Aqui está a fórmula:
= CONT.SE ($ B $ 2: $ B $ 11, ”> 10 ″)
Você também pode ter os critérios em uma célula e usar a referência da célula como critério. Nesse caso, você NÃO precisa colocar os critérios entre aspas duplas:
= CONT.SE ($ B $ 2: $ B $ 11, D3)
Também pode haver um caso em que você deseja que os critérios estejam em uma célula, mas não deseja que esteja com o operador. Por exemplo, você pode querer que a célula D3 tenha o número 10 e não> 10.
Nesse caso, você precisa criar um argumento de critério que é uma combinação de operador e referência de célula (ver foto abaixo):
= CONT.SE ($ B $ 2: $ B $ 11, ”>” & D3)
NOTA: Quando você combina um operador e uma referência de célula, o operador está sempre entre aspas duplas. O operador e a referência de célula são unidos por um E comercial (&).
# 3 Contar células quando os critérios forem MENOS QUE um valor
Para obter a contagem de células com um valor menor que um valor especificado, usamos o operador menor que (“<“). Podemos usá-lo diretamente na fórmula ou usar uma referência de célula que tenha os critérios.
Sempre que usamos um operador em critérios no Excel, precisamos colocá-lo entre aspas duplas. Por exemplo, se o critério é que o número deve ser menor que 5, precisamos inserir “<5” como critério (ver foto abaixo):
= CONT.SE ($ B $ 2: $ B $ 11, ”<5 ″)
Você também pode ter os critérios em uma célula e usar a referência da célula como critério. Neste caso, você NÃO precisa colocar os critérios entre aspas duplas (ver foto abaixo):
= CONT.SE ($ B $ 2: $ B $ 11, D3)
Além disso, pode haver um caso em que você deseja que os critérios estejam em uma célula, mas não deseja que esteja com o operador. Por exemplo, você pode querer que a célula D3 tenha o número 5 e não <5.
Nesse caso, você precisa criar um argumento de critérios que é uma combinação de operador e referência de célula:
= CONT.SE ($ B $ 2: $ B $ 11, ”<“ & D3)
NOTA: Quando você combina um operador e uma referência de célula, o operador está sempre entre aspas duplas. O operador e a referência de célula são unidos por um E comercial (&).
# 4 Contagem de células com vários critérios - entre dois valores
Para obter uma contagem de valores entre dois valores, precisamos usar vários critérios na função CONT.SE.
Aqui estão dois métodos de fazer isso:
MÉTODO 1: Usando a função COUNTIFS
A função COUNTIFS pode tratar vários critérios como argumentos e conta as células apenas quando todos os critérios são TRUE. Para contar células com valores entre dois valores especificados (digamos 5 e 10), podemos usar a seguinte função COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,” <10 ″)
NOTA: A fórmula acima não conta células que contenham 5 ou 10. Se você quiser incluir essas células, use os operadores maior que igual a (> =) e menor que igual a (<=). Aqui está a fórmula:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Você também pode ter esses critérios nas células e usar a referência da célula como critério. Neste caso, você NÃO precisa colocar os critérios entre aspas duplas (ver foto abaixo):
Você também pode usar uma combinação de referências de células e operadores (onde o operador é inserido diretamente na fórmula). Quando você combina um operador e uma referência de célula, o operador está sempre entre aspas duplas. O operador e a referência de célula são unidos por um E comercial (&).
MÉTODO 2: usando duas funções CONT.SE
Se você tiver vários critérios, poderá usar CONT.SE ou criar uma combinação de funções CONT.SE. A fórmula abaixo também faria a mesma coisa:
= CONT.SE ($ B $ 2: $ B $ 11, ”> 5 ″) - CONT.SE ($ B $ 2: $ B $ 11,”> 10 ″)
Na fórmula acima, primeiro encontramos o número de células que têm um valor maior que 5 e subtraímos a contagem de células com um valor maior que 10. Isso nos daria o resultado de 5 (que é o número de células que têm valores maiores que 5 e menores que iguais a 10).
Se você quiser que a fórmula inclua 5 e 10, use a seguinte fórmula:
= CONT.SE ($ B $ 2: $ B $ 11, ”> = 5 ″) - CONT.SE ($ B $ 2: $ B $ 11,”> 10 ″)
Se você quiser que a fórmula exclua '5' e '10' da contagem, use a seguinte fórmula:
= CONT.SE ($ B $ 2: $ B $ 11, ”> = 5 ″) - CONT.SE ($ B $ 2: $ B $ 11,”> 10 ″) - CONT.SE ($ B $ 2: $ B $ 11,10)
Você pode ter esses critérios em células e usar as referências de células ou pode usar uma combinação de operadores e referências de células.
Usando critérios TEXT em funções do Excel
Nº 1 Contagem de células quando os critérios são iguais a um texto especificado
Para contar células que contêm uma correspondência exata do texto especificado, podemos simplesmente usar esse texto como critério. Por exemplo, no conjunto de dados (mostrado abaixo na foto), se eu quiser contar todas as células com o nome Joe, posso usar a fórmula abaixo:
= CONT.SE ($ B $ 2: $ B $ 11, ”Joe”)
Como esta é uma string de texto, preciso colocar os critérios de texto entre aspas duplas.
Você também pode ter os critérios em uma célula e, em seguida, usar essa referência de célula (conforme mostrado abaixo):
= CONT.SE ($ B $ 2: $ B $ 11, E3)
NOTA: Você pode obter resultados errados se houver espaços iniciais / finais nos critérios ou intervalo de critérios. Certifique-se de limpar os dados antes de usar essas fórmulas.
# 2 Contar células quando os critérios NÃO forem iguais a um texto especificado
Semelhante ao que vimos no exemplo acima, você também pode contar células que não contêm um texto especificado. Para fazer isso, precisamos usar o operador diferente de ().
Suponha que você queira contar todas as células que não contêm o nome JOE, aqui está a fórmula que o fará:
= CONT.SE ($ B $ 2: $ B $ 11, ”Joe”)
Você também pode ter os critérios em uma célula e usar a referência da célula como critério. Neste caso, você NÃO precisa colocar os critérios entre aspas duplas (ver foto abaixo):
= CONT.SE ($ B $ 2: $ B $ 11, E3)
Também pode haver um caso em que você deseja que os critérios estejam em uma célula, mas não deseja que esteja com o operador. Por exemplo, você pode querer que a célula D3 tenha o nome Joe e não Joe.
Nesse caso, você precisa criar um argumento de critério que é uma combinação de operador e referência de célula (ver foto abaixo):
= CONT.SE ($ B $ 2: $ B $ 11, ”” & E3)
Quando você combina um operador e uma referência de célula, o operador está sempre entre aspas duplas. O operador e a referência de célula são unidos por um E comercial (&).
Usando os critérios DATE em funções Excel COUNTIF e COUNTIFS
O Excel armazena a data e a hora como números. Portanto, podemos usá-lo da mesma forma que usamos os números.
Nº 1 Contagem de células quando os critérios são iguais a uma data especificada
Para obter a contagem de células que contêm a data especificada, usaríamos o operador igual a (=) junto com a data.
Para usar a data, recomendo usar a função DATE, pois elimina qualquer possibilidade de erro no valor da data. Então, por exemplo, se eu quiser usar a data 1 de setembro de 2015, posso usar a função DATA conforme mostrado abaixo:
= DATA (2015,9,1)
Essa fórmula retornaria a mesma data, apesar das diferenças regionais. Por exemplo, 01-09-2015 seria 1º de setembro de 2015 de acordo com a sintaxe de data dos EUA e 09 de janeiro de 2015 de acordo com a sintaxe de data do Reino Unido. No entanto, essa fórmula sempre retornaria 1º de setembro de 2105.
Esta é a fórmula para contar o número de células que contêm a data 02-09-2015:
= CONT.SE ($ A $ 2: $ A $ 11, DATA (2015,9,2))
# 2 Contar células quando os critérios forem ANTES ou DEPOIS de uma data especificada
Para contar células que contêm data anterior ou posterior a uma data especificada, podemos usar os operadores menor que / maior que.
Por exemplo, se eu quiser contar todas as células que contêm uma data posterior a 2 de setembro de 2015, posso usar a fórmula:
= CONT.SE ($ A $ 2: $ A $ 11, ”>” e DATA (2015,9,2))
Da mesma forma, você também pode contar o número de células antes de uma data especificada. Se você quiser incluir uma data na contagem, use um operador "igual a" junto com o operador "maior que / menor que".
Você também pode usar uma referência de célula que contenha uma data. Nesse caso, você precisa combinar o operador (entre aspas duplas) com a data usando um E comercial (&).
Veja o exemplo abaixo:
= CONT.SE ($ A $ 2: $ A $ 11, ”>” & F3)
# 3 Contar células com vários critérios - entre duas datas
Para obter uma contagem de valores entre dois valores, precisamos usar vários critérios na função CONT.SE.
Podemos fazer isso usando dois métodos - uma única função CONT.SE ou duas funções CONT.SE.
MÉTODO 1: usando a função COUNTIFS
A função COUNTIFS pode aceitar vários critérios como argumentos e conta as células apenas quando todos os critérios são TRUE. Para contar células com valores entre duas datas especificadas (digamos 2 de setembro e 7 de setembro), podemos usar a seguinte função COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & DATA (2015,9,2), $ A $ 2: $ A $ 11, ”<“ e DATA (2015,9,7))
A fórmula acima não conta células que contêm as datas especificadas. Se você quiser incluir essas datas também, use os operadores maior que igual a (> =) e menor que igual a (<=). Aqui está a fórmula:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATA (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATA (2015,9,7))
Você também pode ter as datas em uma célula e usar a referência da célula como critério. Nesse caso, você não pode ter a operadora com a data nas células. Você precisa adicionar operadores manualmente na fórmula (entre aspas duplas) e adicionar referência de célula usando um E comercial (&). Veja a foto abaixo:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
MÉTODO 2: Usando funções CONT.SE
Se você tiver vários critérios, poderá usar uma função CONT.SE ou criar uma combinação de duas funções CONT.SE. A fórmula abaixo também resolveria o problema:
= CONT.SE ($ A $ 2: $ A $ 11, ”>” & DATA (2015,9,2)) - CONT.SE ($ A $ 2: $ A $ 11, ”>” e DATA (2015,9,7))
Na fórmula acima, primeiro encontramos o número de células com data posterior a 2 de setembro e subtraímos a contagem de células com datas posteriores a 7 de setembro. Isso nos daria o resultado de 7 (que é o número de células com datas após 2 de setembro e em ou antes de 7 de setembro).
Se você não quiser que a fórmula conte 2 de setembro e 7 de setembro, use a seguinte fórmula:
= CONT.SE ($ A $ 2: $ A $ 11, ”> =” & DATA (2015,9,2)) - CONT.SE ($ A $ 2: $ A $ 11, ”>” e DATA (2015,9,7))
Se você deseja excluir ambas as datas da contagem, use a seguinte fórmula:
= CONT.SE ($ A $ 2: $ A $ 11, ”>” & DATA (2015,9,2)) - CONT.SE ($ A $ 2: $ A $ 11, ”>” e DATA (2015,9,7) -CONTA ($ A $ 2: $ A $ 11, DATA (2015,9,7)))
Além disso, você pode ter as datas dos critérios nas células e usar as referências das células (junto com os operadores entre aspas duplas unidos usando "e" comercial).
Usando WILDCARD CHARACTERS em Critérios nas funções COUNTIF e COUNTIFS
Existem três caracteres curinga no Excel:
- * (asterisco) - Representa qualquer número de caracteres. Por exemplo, ex * pode significar excel, excels, example, expert, etc.
- ? (ponto de interrogação) - Representa um único personagem. Por exemplo, Tr? Mp pode significar Trump ou Tramp.
- ~ (til) - É usado para identificar um caractere curinga (~, *,?) No texto.
Você pode usar a função CONT.SE com caracteres curinga para contar células quando outra função de contagem embutida falhar. Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo:
Agora vamos dar vários exemplos:
# 1 Contagem de células que contêm texto
Para contar células com texto, podemos usar o caractere curinga * (asterisco). Como o asterisco representa qualquer número de caracteres, ele contaria todas as células que contêm algum texto. Aqui está a fórmula:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Observação: a fórmula acima ignora células que contêm números, células em branco e valores lógicos, mas contaria se as células contêm um apóstrofo (e, portanto, aparecem em branco) ou células que contêm uma string vazia (= ””) que pode ter sido retornada como um parte de uma fórmula.
Aqui está um tutorial detalhado sobre como lidar com casos em que há uma string vazia ou apóstrofo.
Aqui está um tutorial detalhado sobre como lidar com casos em que há strings vazias ou apóstrofos.
Abaixo está um vídeo que explica diferentes cenários de contagem de células com texto.
# 2 Contagem de células não vazias
Se você está pensando em usar a função CONT.valores, pense novamente.
Experimente e pode falhar. CONT.valores também contará uma célula que contém uma string vazia (geralmente retornada por fórmulas como = ”” ou quando as pessoas inserem apenas um apóstrofo em uma célula). As células que contêm cadeias de caracteres vazias parecem em branco, mas não estão e, portanto, são contadas pela função CONT.valores.
CONT.valores também contará uma célula que contém uma string vazia (geralmente retornada por fórmulas como = ”” ou quando as pessoas inserem apenas um apóstrofo em uma célula). As células que contêm cadeias de caracteres vazias parecem em branco, mas não estão e, portanto, são contadas pela função CONT.valores.
Portanto, se você usar a fórmula = CONT.valores (A1: A11), ela retornará 11, mas deveria retornar 10.
Aqui está a solução:
= CONTAR.SE ($ A $ 1: $ A $ 11, ”? *”) + CONTAGEM ($ A $ 1: $ A $ 11) + SUMPRODUTO (-ISLÓGICO ($ A $ 1: $ A $ 11))
Vamos entender essa fórmula dividindo-a:
- CONT.SE ($ N $ 8: $ N $ 18, ”? *”) - Esta parte da fórmula retorna 5. Isso inclui qualquer célula que contenha um caractere de texto. UMA ? representa um caractere e * representa qualquer número de caracteres. Portanto, a combinação? * Nos critérios força o Excel a contar células que contêm pelo menos um caractere de texto.
- COUNT ($ A $ 1: $ A $ 11) - Conta todas as células que contêm números. No exemplo acima, ele retorna 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - Conta todas as células que contêm valores lógicos. No exemplo acima, retorna 2.
# 3 Contar células que contêm texto específico
Digamos que queremos contar todas as células em que o nome do representante de vendas começa com J. Isso pode ser feito facilmente usando um caractere curinga na função CONT.SE. Aqui está a fórmula:
= COUNTIFS ($ C $ 2: $ C $ 11, ”J *”)
O critério J * especifica que o texto em uma célula deve começar com J e pode conter qualquer número de caracteres.
Se quiser contar células que contenham o alfabeto em qualquer parte do texto, coloque um asterisco em ambos os lados. Por exemplo, se você quiser contar células que contenham o alfabeto “a”, use * a * como critério.
Este artigo é excepcionalmente longo em comparação com meus outros artigos. Espero que você tenha gostado. Deixe-me saber sua opinião, deixando um comentário.
Você também pode achar úteis os seguintes tutoriais do Excel:
- Conte o número de palavras no Excel.
- Contar células com base na cor de fundo no Excel.
- Como somar uma coluna no Excel (5 maneiras realmente fáceis)
