Este tutorial demonstra como usar oExcel Função COUNTIF e COUNTIFSções no Excel para contar dados que atendam a determinados critérios.
Visão geral da função COUNTIF
Você pode usar a função CONT.SE no Excel para contar células que contêm um valor específico, contar células que são maiores ou iguais a um valor, etc.
(Observe como as entradas da fórmula aparecem)
Sintaxe e argumentos da função COUNTIF:
= CONT.SE (intervalo, critérios)
faixa - O intervalo de células a serem contadas.
critério - Os critérios que controlam quais células devem ser contadas.
Qual é a função CONT.SE?
A função CONT.SE é uma das funções mais antigas usadas em planilhas. Em termos simples, é ótimo para escanear um intervalo e dizer quantas das células atendem a essa condição. Veremos como a função funciona com texto, números e datas; bem como algumas das outras situações que podem surgir.
Exemplo básico
Vamos começar examinando esta lista de itens aleatórios. Temos alguns números, células em branco e algumas sequências de texto.
Se quiser saber quantos itens correspondem exatamente aos critérios, você pode especificar o que deseja procurar como o segundo argumento. Um exemplo desta fórmula pode ser parecido com
= CONT.SE (A2: A9, "Apple")
Essa fórmula retornaria o número 3, pois há 3 células em nosso intervalo que atendem a esse critério. Como alternativa, podemos usar uma referência de célula em vez de codificar um valor. Se escrevêssemos "Apple" na célula G2, poderíamos mudar a fórmula para
= CONT.SE (A2: A9, G2)
Ao lidar com números, é importante distinguir entre números e números que foram armazenados como texto. Geralmente, você não coloca aspas em torno dos números ao escrever fórmulas. Então, para escrever uma fórmula que verifica o número 5, você escreveria
= CONT.SE (A2: A9, 5)
Finalmente, também podemos verificar se há células em branco usando uma string de comprimento zero. Escreveríamos essa fórmula como
= CONT.SE (A2: A9, "")
Observação: Esta fórmula contará as células que estão realmente vazias, bem como as que estão em branco como resultado de uma fórmula, como uma função IF.
Partidas parciais
A função CONT.SE suporta o uso de curingas, “*” ou “?”, Nos critérios. Vejamos esta lista de saborosos produtos de padaria:
Para encontrar todos os itens que começam com Apple, poderíamos escrever “Apple *”. Então, para obter uma resposta de 3, nossa fórmula em D2 é
= CONT.SE (A2: A5, "Apple *")
Observação: A função CONT.SE não faz distinção entre maiúsculas e minúsculas, então você também pode escrever “maçã *” se quiser.
De volta aos nossos produtos de padaria, também podemos querer descobrir quantas tortas temos em nossa lista. Podemos descobrir isso colocando o caractere curinga no início do nosso termo de pesquisa e escrever
= CONT.SE (A2: A5, "* torta")
Esta fórmula dá o resultado de 2.
Também podemos usar curingas para verificar as células com texto. Vamos voltar para nossa lista original de dados.
Para contar o número de células que possuem pelo menos algum texto, sem contar números ou células em branco, podemos escrever
= CONT.SE (A2: A9, "*")
Você pode ver que nossa fórmula retorna corretamente um resultado de 4.
Operadores de comparação em COUNTIF
Ao escrever os critérios até agora, sugerimos que nosso operador de comparação é “=”. Na verdade, poderíamos ter escrito isso:
= CONT.SE (A2: A9, "= Apple")
É um caractere extra para escrever, por isso geralmente é omitido. No entanto, isso significa que você pode usar os outros operadores como maior que, menor que ou diferente de. Vejamos esta lista de idades registradas:
Se quisermos saber quantas crianças têm pelo menos 5 anos de idade, podemos escrever uma comparação "maior ou igual a" como esta:
= CONT.SE (A2: A8, "> = 5")
Observação: O operador de comparação é sempre fornecido como uma string de texto e, portanto, deve estar entre aspas.
Da mesma forma, você também pode verificar se há itens menores que um determinado valor. Se precisarmos descobrir quantos são menos de 8, podemos escrever
= CONT.SE (A2: A8, "<8")
Isso nos dá o resultado desejado de 5. Agora, vamos imaginar que todas as crianças de 6 anos estão saindo para um passeio. Quantas crianças vão ficar? Podemos descobrir isso usando uma comparação "diferente de" como esta:
= CONT.SE (A2: A8, "6")
Agora podemos ver rapidamente que temos 6 filhos que não têm 6 anos.
Nestes exemplos de comparação até agora, codificamos os valores que queríamos. Você também pode usar uma referência de célula. O truque é que você precisa concatenar o operador de comparação com a referência da célula. Digamos que colocamos o número 7 na célula C2 e queremos que nossa fórmula em D2 mostre quantas crianças têm menos de 7 anos.
Nossa fórmula em D2 precisa ser assim:
= CONT.SE (A2: A8, "<" & C2)
Observação: Preste atenção especial ao escrever essas fórmulas, para saber se você precisa colocar um item entre aspas ou fora dela. Os operadores estão sempre dentro das citações, as referências das células estão sempre fora das citações. Os números estão fora se você estiver fazendo uma correspondência exata, mas dentro se estiver fazendo um operador de comparação.
Trabalhando com datas
Vimos como você pode fornecer um texto ou número como critério, mas e quando precisarmos trabalhar com datas? Aqui está um exemplo rápido de lista com a qual podemos trabalhar:
Para contar quantas datas são após 4 de maio, precisamos tomar alguns cuidados. Os computadores armazenam datas como números, portanto, precisamos ter certeza de que o computador usa o número correto. Se escrevêssemos esta fórmula, obteríamos o resultado correto?
= CONT.SE (A2: A9, "
A resposta é “possivelmente”. Como omitimos o ano de nossos critérios, o computador assumirá que estamos falando do ano atual. Se todas as datas com as quais estamos trabalhando forem do ano em curso, obteremos a resposta correta. No entanto, se houver algumas datas no futuro, receberemos a resposta errada. Além disso, assim que o próximo ano começar, essa fórmula retornará um resultado diferente. Como tal, esta sintaxe provavelmente deve ser evitada.
Como pode ser difícil escrever datas corretamente em uma fórmula, é uma prática recomendada escrever a data que você deseja usar em uma célula e, em seguida, você pode usar essa referência de célula em sua fórmula CONT.SE. Então, vamos escrever a data de 7 de maio de 2020 na célula C2, e então podemos colocar nossa fórmula em C4.
A fórmula em C4 é
= CONT.SE (A2: A9, "<" & C2)
Agora sabemos que o resultado de 7 está correto e a resposta não mudará inesperadamente se abrirmos esta planilha em algum momento no futuro.
Antes de sairmos desta seção, é comum usar a função TODAY ao trabalhar com datas. Podemos usar isso da mesma forma que usaríamos uma referência de célula. Por exemplo, poderíamos alterar a fórmula anterior para ser esta:
= CONT.SE (A2: A9, "<" & HOJE ())
Agora, nossa fórmula ainda será atualizada conforme o progresso em tempo real, e teremos uma contagem de itens que são menores do que hoje.
Vários critérios e COUNTIFS
A função original do COUNTIF foi aprimorada em 2007, quando o COUNTIFS foi lançado. A sintaxe entre os dois é muito semelhante, com o último permitindo que você forneça intervalos e critérios adicionais. Você pode usar o COUNTIFS facilmente em qualquer situação em que o COUNTIF exista. É uma boa ideia saber que ambas as funções existem.
Vejamos esta tabela de dados:
Para descobrir quantas pessoas estão nos níveis salariais 1 a 2, você pode escrever um somatório das funções CONT.SE como este:
= CONT.SE (B2: B7, "> = 1") - CONT.SE (B2: B7, "> 2")
Essa fórmula funcionará, pois você encontra tudo que está acima de 1, mas então subtrai o número de registros que estão além do seu ponto de corte. Como alternativa, você pode usar COUNTIFS como este:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
O último é mais intuitivo de ler, então você pode querer usar essa rota. Além disso, COUNTIFS é mais poderoso quando você precisa considerar várias colunas. Digamos que queremos saber quantas pessoas estão na gestão e no nível de remuneração 1. Você não pode fazer isso com apenas um CONT.SE; você precisa escrever
= COUNTIFS (A2: A7, "Gestão", B2: B7, 1)
Esta fórmula forneceria o resultado correto de 2. Antes de sairmos desta seção, vamos considerar uma lógica do tipo Or. E se quiséssemos saber quantas pessoas estão na Gestão ou? Você precisaria adicionar alguns COUNTIFS, mas existem duas maneiras de fazer isso. A maneira mais simples é escrever assim:
= CONT.SE (A2: A7, "HR") + CONT.SE (A2: A7, "Gestão")
Você também pode usar uma matriz e escrever esta fórmula de matriz:
= SOMA (CONT.SE (A2: A7, {"RH", "Gestão"}))
Observação: As fórmulas de matriz devem ser confirmadas usando `Ctrl + Shift + Enter` e não apenas` Enter`.
Como essa fórmula funcionará, verá que você forneceu uma matriz como entrada. Assim, ele calculará o resultado para duas funções CONT.SE diferentes e as armazenará em um array. A função SUM irá então adicionar todos os resultados em nosso array para fazer uma única saída. Assim, nossa fórmula será avaliada da seguinte forma:
= SOMA (CONT.SE (A2: A7, {"RH", "Gestão"})) = SOMA ({2, 3}) = 5
Conte valores únicos
Agora que vimos como usar uma matriz com a função CONT.SE, podemos dar um passo adiante para nos ajudar a contar quantos valores únicos existem em um intervalo. Primeiro, vamos olhar novamente para nossa lista de departamentos.
= SOMA (1 / CONT.SE (A2: A7, A2: A7))
Podemos ver que existem 6 células de dados, mas existem apenas 3 itens diferentes. Para fazer a matemática funcionar, precisaríamos que cada item valesse 1 / N, onde N é o número de vezes que um item é repetido. Por exemplo, se cada RH valesse apenas 1/2, então, quando você os somava, obteria uma contagem de 1, para 1 valor único.
De volta ao nosso COUNTIF, que foi projetado para descobrir quantas vezes um item aparece em um intervalo. Em D2, vamos escrever a fórmula de matriz
= SOMA (1 / CONT.SE (A2: A7, A2: A7))
Como essa fórmula vai funcionar, é para cada célula no intervalo de A2: A7, vai verificar quantas vezes ela aparece. Com nossa amostra, isso vai produzir uma série de
{2, 2, 3, 3, 3, 1}
Em seguida, transformamos todos esses números em frações, fazendo alguma divisão. Agora nosso array se parece com
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Quando somamos tudo isso, obtemos o resultado desejado de 3.
Countif com duas ou múltiplas condições - a função Countifs
Até agora, trabalhamos apenas com a função CONT.SE. A função CONT.SE só pode lidar com um critério de cada vez. Para CONTAR.SE com vários critérios, você precisa usar a função CONT.SE. COUNTIFS se comporta exatamente como COUNTIF. Você acabou de adicionar critérios extras. Vamos dar uma olhada no exemplo abaixo.
= COUNTIFS (B2: B7, "= 130")
COUNTIF e COUNTIFS no Planilhas Google
A função CONT.SE e CONT.SE funciona exatamente da mesma forma no Planilhas Google e no Excel:
