o Excel tem muitas funções que um usuário precisa especificar um único ou vários critérios para obter o resultado. Por exemplo, se você quiser contar células com base em vários critérios, você pode usar as funções COUNTIF ou COUNTIFS no Excel.
Este tutorial cobre várias maneiras de usar um único ou vários critérios na função COUNTIF e COUNTIFS no Excel.,
enquanto eu estiver focando principalmente em funções de COUNTIF e COUNTIFS neste tutorial, todos esses exemplos também podem ser usados em outras funções do Excel que tomam vários critérios como entradas (tais como SUMIF, SUMIFS, AVERAGEIF, e AVERAGEIF).
Este Tutorial cobre:
Uma Introdução às funções do Excel CONTIF e CONTIFS
vamos primeiro ter um controle sobre o uso de funções do COUNTIF e CONTIFS no Excel.,
A função do Conselho do Excel (tem critérios únicos)
a função do conselho do Excel é mais adequada para situações em que você quer contar células com base num único critério. Se você quiser contar com base em vários critérios, use a função COUNTIFS.
sintaxe
=COUNTIF (intervalo, critérios)
argumentos de entrada
- intervalo – o intervalo de células que deseja contar.critérios
- – os critérios que devem ser avaliados em relação à gama de células para uma célula a ser contada.,
função de contadores do Excel (tem vários critérios)
função de contadores do Excel é mais adequada para situações em que você quer contar células com base em vários critérios.
a Sintaxe
=COUNTIFS(criteria_range1, criteria1, …)
Argumentos de Entrada
- criteria_range1 – O intervalo de células que você deseja avaliar contra criteria1.
- criteria1-os critérios que você quer avaliar para criteria_range1 para determinar quais células a contar.
- – a gama de células para as quais pretende avaliar em função dos critériosa2.,
- – os critérios que você quer avaliar para criteria_range2 para determinar quais células a contar.
Agora vamos dar uma olhada em alguns exemplos de uso de vários critérios em funções do COUNTIF no Excel.
usando critérios de número em funções de contagem de células do Excel
#1 Quando os critérios são iguais a um valor
para obter a contagem de células onde o argumento de critérios é igual a um valor especificado, você pode entrar diretamente nos critérios ou usar a referência de células que contém os critérios.,
abaixo está um exemplo onde contamos as células que contêm o número 9 (o que significa que o argumento de 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 baseia-se na célula D3. Você também pode inserir os critérios diretamente na fórmula., Por exemplo, você pode utilizar também:
=CONT.SE($B$2:$B$11,9)
#2 Contagem de Células quando o Critério for MAIOR QUE um Valor
Para obter a contagem de células com um valor maior do que um valor especificado, usamos o maior operador (“>”). Podemos usá-lo diretamente na fórmula ou usar uma referência de célula que tem os critérios.
sempre que usamos um operador em critérios no Excel, precisamos colocá-lo dentro de aspas duplas., Por exemplo, se o critério for maior que 10, então precisamos entrar “>10” como o critério (veja foto abaixo):
Aqui está a fórmula:
=CONT.SE($B$2:$B$11,”>10″)
Você também pode ter o critério em uma célula e utilizar a referência de célula como os critérios. Neste caso, você NÃO precisa colocar os critérios de aspas:
=CONT.SE($B$2:$B$11,D3)
Há também poderia ser um caso de quando você deseja que o critério para ser em uma célula, mas não se quer 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érios, que é uma combinação de operador e referência de célula (veja foto abaixo):
=CONT.SE($B$2:$B$11,”>”&D3)
OBSERVAÇÃO: Quando você combina um operador e uma referência de célula, o operador é sempre entre aspas duplas. O operador e a referência da célula são unidos por um ampersand (&).,
#3 células contadas quando os critérios são inferiores a um valor
para obter a contagem de células com um valor inferior a um valor especificado, usamos o menor que o operador (“<“). Podemos usá-lo diretamente na fórmula ou usar uma referência de célula que tem os critérios.
sempre que usamos um operador em critérios no Excel, precisamos colocá-lo dentro de aspas duplas., Por exemplo, se o critério é que o número deve ser menor que 5, então precisamos entrar “<5” como os critérios (veja foto abaixo):
=CONT.SE($B$2:$B$11,”<5″)
Você também pode ter o critério em uma célula e utilizar a referência de célula como os critérios., Neste caso, você NÃO precisa colocar os critérios de aspas duplas (veja foto abaixo):
=CONT.SE($B$2:$B$11,D3)
além disso, pode haver casos em que você quer que o critério seja em uma célula, mas não se quer 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 é sempre entre aspas duplas. O operador e a referência da célula são unidos por um ampersand (&).,
#4 Células de contagem com múltiplos critérios – entre dois valores
para obter uma contagem de valores entre dois valores, precisamos usar vários critérios na função COUNTIF.
Aqui estão dois métodos de fazer isso:
método 1: Usando a função de contadores
CONTIFS função pode lidar com vários critérios como argumentos e conta as células apenas quando todos os critérios são verdadeiros., A contagem de células com valores entre dois valores especificados (digamos, 5 e 10), podemos usar o seguinte COUNTIFS função:
=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)
NOTA: A fórmula acima não contagem de células que contêm 5 ou 10. Se você quiser incluir estas células, use maior que igual a (=) e menor que igual a (<=) operadores., Aqui está a fórmula:
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″)
Você também pode ter esses critérios em células e usar a referência de célula como os critérios. Neste caso, não é necessário colocar os critérios Entre Aspas (ver pic abaixo):
Pode também usar uma combinação de referências de células e operadores (onde o operador é indicado directamente na fórmula). Quando você combina um operador e uma referência de célula, o operador está sempre entre aspas., O operador e a referência da célula são unidos por um ampersand (&).
método 2: Usando duas funções contábeis
Se tiver vários critérios, pode usar contábeis ou criar uma combinação de funções contábeis., A fórmula abaixo também fazem a mesma coisa:
=CONT.SE($B$2:$B$11,”>5″)-CONT.SE($B$2:$B$11,”>10″)
Na fórmula acima, primeiro iremos encontrar o número de células com um valor maior do que 5 e subtraímos a contagem de células com um valor maior que 10. Isso nos daria o resultado como 5 (que é o número de células que têm valores mais de 5 e menos do que igual a 10).,3c219a21b4″>
Se você deseja que a fórmula para incluir tanto os 5 e 10, use a seguinte fórmula, em vez de:
=CONT.SE($B$2:$B$11,”>=5″)-CONT.SE($B$2:$B$11,”>10″)
Se você deseja que a fórmula para excluir ‘5’ e ’10’ da contagem, use a fórmula a seguir:
=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 células de referências, ou você pode usar uma combinação de operadores e referências de células.,
usando critérios de texto em funções Excel
#1 células de contagem 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 o critério. Por exemplo, no conjunto de dados (mostrado abaixo na foto), se eu quero contar todas as células com o nome de Joe, eu posso usar a fórmula abaixo:
=CONT.SE($B$2:$B$11,”Joe”)
uma vez que esta é uma seqüência de caracteres de texto, eu preciso colocar os critérios de texto entre aspas duplas.,
Você também pode ter o critério em uma célula e, em seguida, usa essa referência de célula (como mostrado abaixo):
=CONT.SE($B$2:$B$11,E3)
NOTA: Você pode obter resultados incorretos se há inicial/espaços à direita em critérios ou intervalo de critérios. Certifique-se de limpar os dados antes de usar estas fórmulas.
#2 células de contagem quando o critério não é igual 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 isso, precisamos usar o não igual ao operador (<).
Suponha que você queira contar todas as células que não contêm o nome de JOE, aqui está a fórmula que irá fazê-lo:
=CONT.SE($B$2:$B$11,”<>Joe”)
Você também pode ter o critério em uma célula e utilizar a referência de célula como os critérios., Neste caso, você NÃO precisa colocar os critérios de aspas duplas (veja foto abaixo):
=CONT.SE($B$2:$B$11,E3)
Há também poderia ser um caso de quando você deseja que o critério para ser em uma célula, mas não quero ele 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érios, que é uma combinação de operador e referência de célula (veja foto abaixo):
=CONT.SE($B$2:$B$11,”<>”&E3)
Quando você combina um operador e uma referência de célula, o operador é sempre entre aspas duplas. O operador e a referência da célula são unidos por um ampersand (&).
usando critérios de data em funções do Excel COUNTIF e do COUNTIFS
Excel armazenar data e hora como números., Então podemos usá-lo da mesma forma que usamos números.
#1 células contadas 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 igual ao operador (=) juntamente com a data.
para usar a data, eu recomendo usar a função DATA, uma vez que se livra de qualquer possibilidade de erro no valor da data. Assim, por exemplo, se eu quiser usar a data de 1 de setembro de 2015, pode usar a função de DATA, como mostrado abaixo:
=DATA(2015,9,1)
Esta fórmula retornaria com a mesma data, apesar de 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, esta fórmula retornaria sempre 1 de setembro de 2105.
Aqui está 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 Contagem de Células quando o Critério é ANTES ou DEPOIS de uma Data Especificada
A contagem de células que contêm data antes ou depois de uma data especificada, podemos usar o menor que/maior que os operadores.,
Por exemplo, se eu quiser contar todas as células que contêm uma data que é depois de 02 setembro de 2015, e que eu possa usar a fórmula:
=CONT.SE($A$2:$A$11,”>”&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 e ‘igual a’ operador junto com ‘maior/menor que’ operador.
também pode usar uma referência de célula que contém uma data., Neste caso, você precisa combinar o operador (dentro de aspas duplas) com a data usando um ampersand (&).
Veja o exemplo abaixo:
=CONT.SE($A$2:$A$11,”>”&F3)
#3 Contagem de 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 em função CONT.SE.
Podemos fazer isso usando dois métodos-uma única função COUNTIFS ou duas funções COUNTIF.,
método 1: Usando a função COUNTIFS
A função COUNTIFS pode ter vários critérios como os argumentos e conta as células apenas quando todos os critérios são verdadeiros. A contagem de células com valores entre duas datas especificadas (digamos 2 de setembro e 7 de setembro), podemos usar o seguinte COUNTIFS função:
=COUNTIFS($A$2:$A$11,”>”&DATA(2015,9,2),$A$2:$A$11,”<“&DATA(2015,9,7))
A fórmula acima não contagem de células que contêm as datas especificadas., Se você quiser incluir essas datas também, use maior que igual a (=) e menor que igual a (<=) operadores. 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 um celular e usar a referência de célula como os critérios. Neste caso, você não pode ter o operador com a data nas células., Você precisa adicionar manualmente os operadores na Fórmula (entre aspas) e Adicionar Referência de células usando um ampersand (&). Veja a foto abaixo:
=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)
o MÉTODO 2: Usando SOMA de funções
Se você tiver vários critérios, você pode usar um COUNTIFS função ou criar uma combinação de duas SOMA de funções., A fórmula abaixo seria também fazer o truque:
=CONT.SE($A$2:$A$11,”>”&DATA(2015,9,2))-CONT.SE($A$2:$A$11,”>”&DATA(2015,9,7))
Na fórmula acima, temos que em primeiro lugar encontrar a quantidade de células que têm uma data depois do dia 2 de setembro, e subtraímos a contagem de células com datas posteriores a 7 de setembro. Isso nos daria o resultado como 7 (que é o número de células que têm datas depois de 2 de setembro e em ou antes de 7 de setembro).,>DATA(2015,9,2))-CONT.SE($A$2:$A$11,”>”&DATA(2015,9,7))
Se você deseja excluir as datas de contagem, use a seguinte fórmula:
=CONT.SE($A$2:$A$11,”>”&DATA(2015,9,2))-CONT.SE($A$2:$A$11,”>”&DATA(2015,9,7)-CONT.SE($A$2:$A$11,DATA(2015,9,7)))
além disso, você pode ter o critério de datas nas células e usar as células de referências (juntamente com os operadores aspas juntou-se a utilizar ” e ” comercial).,
Usando CARACTERES CURINGA em Critérios de CONT & COUNTIFS Funções
Há três caracteres curinga no Excel:
Você pode usar a função CONT.SE com caracteres curinga para contagem de células quando outros inerentes a função contar falha. Por exemplo, suponha que você tenha um conjunto de dados, como mostrado abaixo:
Agora vamos dar vários exemplos:
#1 Contagem de Células que contêm Texto
A contagem de células com texto, podemos usar o caractere curinga * (asterisco)., Uma vez que o asterisco representa qualquer número de caracteres, ele iria contar todas as células que têm qualquer texto nele. Aqui está a fórmula:
=COUNTIFS($C$2:$C$11,”*”)
Nota: A fórmula acima ignora células que contêm números, células vazias, valores lógicos e, mas gostaria de contagem de células contém um apóstrofo (e, portanto, aparecem em branco) ou células que contêm a cadeia de caracteres vazia (=””) o que pode ter sido retornado como parte de uma fórmula.
aqui está um tutorial detalhado sobre o tratamento de casos onde há uma cadeia vazia ou apóstrofo.,
aqui está um tutorial detalhado sobre o tratamento de casos onde existem strings vazias ou apóstrofos.
abaixo está um vídeo que explica diferentes cenários de contagem de células com texto nele.
#2 Contagem de células não-brancas
Se você está pensando em usar a função conta, pense novamente.Tente e pode falhar. Conta também contará uma célula que contém uma cadeia vazia (muitas vezes retornada por fórmulas como =”” ou quando as pessoas entram apenas um apóstrofo em uma célula). Células que contêm strings vazias parecem em branco, mas não são, e, portanto, contadas pela função conta.,
Couta também contará uma célula que contém uma cadeia vazia (muitas vezes devolvida por fórmulas como =”” ou quando as pessoas entram apenas um apóstrofo em uma célula). Células que contêm strings vazias parecem em branco, mas não são, e, portanto, contadas pela função conta.
por isso, se usar a fórmula =conta(A1:A11), devolve 11, enquanto deve devolver 10.
Aqui está a correção:
= CONTIF ($a$1:$a$11,”?*”) + COUNT ($a$1:$A$11)+SUMPRODUCT (–ISLOGICAL ($a$1:$a$11))
Let’s understand this formula by breaking it down:
- COUNTIF ($n$8:$n$18,”?,* “) – Esta parte da fórmula devolve 5. Isto inclui qualquer célula que tenha um caráter de texto nele. A ? representa um carácter e * representa qualquer número de caracteres. Daí a combinação ?* in the criteria forces excel to count cells that have at least one text character in it.
- ($A$1:$A$11) – isto conta todas as células que contêm números. No exemplo acima, ele retorna 3.
- SUMPRODUCT(–ISLOGICAL($a$1:$A$11) – isto conta todas as células que contêm valores lógicos. No exemplo acima, ele retorna 2.,
#3 Contagem de Células que contêm texto
Vamos dizer que nós queremos contar todas as células onde o representante de vendas nome começa com J. Isto pode ser facilmente conseguido usando um caractere curinga em 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 as células que contêm o alfabeto em qualquer parte do texto, flanqueie-o com um asterisco em ambos os lados., Por exemplo, se você quiser contar células que contêm o alfabeto “a” nele, use *a* como o critério.