Usar a função COUNTIFS do Excel 2007 para analisar dados

Microsoft Office Excel 2007: Data Analysis and Business Modeling
por Wayne L. Winston

Wayne L. Winston é professor de Ciências da Decisão na Kelley School of Business da Universidade de Indiana e conquistou muitos prêmios pelo ensino em MBA. Por mais de 20 anos, ele ensinou, aos clientes das empresas Fortune 500, como usar o Excel para tomar decisões empresariais mais inteligentes. Wayne e seu parceiro de negócios, Jeff Sagarin, desenvolveram o sistema de rastreamento e classificação de informações estatísticas de jogadores, que é utilizado pela equipe profissional de basquete Dallas Mavericks. Ele também venceu duas vezes o Jeopardy!

Para saber mais sobre outros livros sobre o 2007 Microsoft Office system, visite a Microsoft Press.


Neste artigo


Vamos supor que eu tenha uma lista de músicas tocadas no rádio. Para cada música, eu sei o cantor, a data em que a música foi tocada e a duração da música. Como posso responder perguntas, tais como as listadas abaixo, sobre as músicas da lista?

  • Cada artista cantou quantas músicas?
  • Quantas são mais longas do que a duração média de todas as músicas da lista?
  • Quantas foram cantadas por cantores cujos sobrenomes começam com S?
  • Quantas foram tocadas após 15 de junho de 2005?
  • Quantas foram tocadas antes de 2009?
  • Quantas têm exatamente quatro minutos de duração?
  • Quantas foram cantadas pelo meu cantor favorito e têm exatamente quatro minutos de duração?

Em um contexto mais geral, como executo operações como estas a seguir?

  • Contagem do número de células em um intervalo contendo números.
  • Contagem do número de células em branco, em um intervalo.
  • Contagem do número de células preenchidas, em um intervalo.

Em geral, contamos o número de células de um intervalo com base em critérios fornecidos. Por exemplo, se uma planilha contiver informações sobre vendas de cosméticos, pode ser interessante contar o número de transações de venda feitas pela vendedora Jennifer ou o número de transações de venda ocorridas após 10 de junho. Este artigo mostra como usar a função COUNTIF para contar o número de células em um intervalo, as quais atendam aos critérios definidos com base em uma linha ou coluna da planilha.

Sintaxe da função COUNTIF

A sintaxe da função COUNTIF éCOUNTIF(intervalo,critério).

  • Intervalo é o intervalo de células em que você deseja contar as células que atendem a um determinado critério.
  • Critério é um número, data ou expressão que determina se uma célula específica deve ser contada no intervalo.

A sintaxe de COUNTIFS (nova no Microsoft Office Excel 2007) é COUNTIFS(intervalo1,critério1,intervalo2,critério2,…,intervalo_n,critério_n).

COUNTIFS conta o número de linhas em que a entrada intervalo1 corresponde ao critério1, a entrada intervalo2 corresponde ao critério2, a entrada intervalo_n corresponde ao critério_n etc. Dessa forma, COUNTIFS permite que os critérios englobem mais de uma coluna ou várias condições em uma coluna.

Início da página Início da página

Exemplos de critérios aceitos pelo Excel para COUNTIF

Para usar com êxito a função COUNTIF (e outras funções similares), é preciso entender a ampla variedade de critérios que o Excel aceitará. Os tipos de critérios que podem ser usados ficam mais bem explicados com o uso de exemplos. Além dos exemplos da função COUNTIF, fornecerei exemplos das funções COUNT, COUNTA e COUNTBLANK:

  • A função COUNT conta o número de células em um intervalo contendo números.
  • A função COUNTA conta o número de células preenchidas, em um intervalo.
  • A função COUNTBLANK conta o número de células em branco, em um intervalo.

Como ilustração sobre o uso dessas funções, considere um banco de dados que, para cada música tocada na estação de rádio WKRP, fornece estas informações:

  • O cantor
  • A data em que a música foi tocada
  • A duração da música

A Figura 1 mostra um subconjunto dos dados usados nos próximos exemplos.


O banco de dados de músicas para os exemplos de COUNTIF

Figura 1  O banco de dados de músicas para os exemplos de COUNTIF

Cada artista cantou quantas músicas?

Para começar, seleciono a primeira linha do banco de dados, o intervalo D6:G6. Depois, seleciono todo o banco de dados pressionando CTRL+SHIFT+SETA PARA BAIXO. Em seguida, no grupo Nomes Definidos da guia Fórmulas, cliquei em Criar a partir da Seleção e escolhi Linha Superior. Agora temos nomeados o intervalo D7:D957 Número da Música, o intervalo E7:E957 Cantor, o intervalo F7:F957 Data e o intervalo G7:G957 Minutos. Para determinar quantas músicas foram cantadas por cantor, copiamos em C5 até C6:C12 a fórmula COUNTIF(Cantor,B5). Na célula C5, essa fórmula agora exibe o número de células do intervalo Cantor que correspondem ao valor em B5 (Eminem). O banco de dados contém 114 músicas cantadas por Eminem. De maneira semelhante, Cher cantou 112 músicas etc., como você pode ver na Figura 2. Também consegui encontrar o número de músicas cantadas por Eminem usando a fórmula COUNTIF(Cantor,"Eminem"). Observe que é preciso colocar o texto Eminem entre aspas (" ") e que os critérios não diferenciam maiúsculas e minúsculas.


Linhas que listam quantas músicas foram cantadas por cantor

Figura 2  Usando COUNTIF para determinar quantas músicas foram cantadas por cantor.

Quantas músicas não foram cantadas por Eminem?

Para resolver esse problema, você precisa saber que o Excel interpreta a combinação de caracteres <> como “diferente de”. A fórmula COUNTIF(Cantor,"<>Eminem"), inserida na célula C15, informa que 837 músicas do banco de dados não foram cantadas por Eminem, como mostra a Figura 3. É preciso colocar <>Eminem entre aspas porque o Excel trata a combinação de caracteres que indica diferente de (<>) como texto e Eminem é, obviamente, um texto. O mesmo resultado pode ser obtido com a fórmula COUNTIF(Cantor,"<>"&B5), que usa o símbolo E comercial (&) para concatenar a referência à célula B5 e o operador <>.


Músicas do banco de dados que não foram cantadas por Eminem

Figura 3  É possível combinar a função COUNTIF com o operador diferente de (<>).

Quantas músicas têm pelo menos quatro minutos de duração?

Na célula C16, calculei o número de músicas tocadas que tinham pelo menos quatro minutos de duração usando a fórmula COUNTIF(Minutos,">=4"). É preciso colocar >=4 entre aspas porque a combinação de caracteres indicativa de maior que ou igual a (>=), como <>, é tratada como texto. Encontramos 447 músicas com duração mínima de quatro minutos.

Quantas músicas são mais longas do que a duração média de todas as músicas da lista?

Para resolver essa questão, primeiro calculei, na célula G5, a duração média de uma música usando a fórmula AVERAGE(Minutos). Depois, na célula C17, calculei o número de músicas com duração maior do que a média usando a fórmula COUNTIF(Minutos,">"&G5). Posso referenciar outra célula (neste caso, G5) nos critérios, usando o caractere &. Você pode ver que 477 músicas tinham duração maior do que a média, o que corresponde ao número de músicas com duração mínima de 4 minutos. O motivo desses números se equivalerem é que eu assumi o pressuposto de que cada música era um inteiro. Uma música de 3,48 minutos tem duração mínima de 4 minutos.

Quantas músicas foram cantadas por cantores cujos sobrenomes começam com S?

Para responder essa pergunta, usei um caractere coringa, o asterisco (*), nos critérios. Um asterisco representa qualquer sequência de caracteres. Assim, a fórmula COUNTIF(Cantor,"S*") na célula C18 seleciona todas as músicas cantadas por algum cantor cujo sobrenome comece com S. (Os critérios não diferenciam maiúsculas e minúsculas.) Duzentas e trinta e duas músicas foram cantadas por cantores com sobrenomes começando com S. Esse número é simplesmente o total de músicas cantadas por Bruce Springsteen ou Britney Spears (103+129=232).

Quantas músicas foram cantadas por cantores cujos sobrenomes contêm exatamente seis letras?

Nesse exemplo, usei o ponto de interrogação (?) como caractere coringa. O ponto de interrogação corresponde a qualquer caractere. Portanto, a inserção da fórmula COUNTIF(Cantor,"??????") na célula C19 conta o número de músicas cantadas por cantores cujos sobrenomes têm seis letras. O resultado é 243. (Dois cantores têm sobrenomes com seis letras, Britney Spears e Eminem, e ambos cantaram um total de 243 músicas —129+114=243.)

Quantas músicas foram tocadas após 15 de junho de 2005?

Os critérios utilizados com as funções COUNTIF processam datas com base no número serial de uma data. (Uma data posterior é considerada maior que uma data anterior.) A fórmula COUNTIF(Data,">6/15/2005") na célula C20 informa que 98 músicas foram tocadas após 15 de junho de 2005.

Quantas músicas foram tocadas antes de 2009?

Queremos que os critérios encontrem todas as datas coincidentes ou anteriores a 31 de dezembro de 2008. Inseri na célula C21 a fórmula COUNTIF(Data,"<=12/31/2008"). Encontrei 951 músicas (o que corresponde a todas as músicas) que foram cantadas antes do início de 2009.

Quantas músicas têm exatamente quatro minutos de duração?

Na célula C22, calculo o número de músicas com duração exata de quatro minutos usando a fórmula COUNTIF(Minutos,4). Essa fórmula conta o número de células do intervalo G7:G957 que contêm um número 4. Localizamos 247 músicas com duração exata de quatro minutos. De modo semelhante, localizamos na célula C23 que 230 músicas têm duração exata de cinco minutos.

Quantas músicas foram cantadas pelo meu cantor favorito e têm exatamente quatro minutos de duração?

Queremos contar cada linha em que uma entrada na coluna Cantor corresponda a Springsteen e uma entrada na coluna Minutos corresponda a 4. Este é um trabalho para a nova função COUNTIFS. Basta inserir na célula C24 a fórmula =COUNTIFS(Cantor,"Springsteen",Minutos,4).

Essa fórmula conta todas as linhas em que o Cantor é Springsteen e Minutos é igual a 4. Descobrimos que Bruce Springsteen cantou 24 músicas com duração exata de quatro minutos. Minha música favorita de Springsteen é "Thunder Road", mas a duração dessa música é maior que quatro minutos.

Quantas músicas foram cantadas por Madonna, cuja duração era de três a quatro minutos?

Como estamos lidando com vários critérios, este, de novo, é um trabalho para COUNTIFS. A inserção na célula C25 da fórmula =COUNTIFS(Cantor,"Madonna",Minutos,"<=4",Minutos,">=3") conta todas as linhas em que Madonna cantou uma música com duração de três a quatro minutos. Essas são exatamente as linhas que queremos contar. Descobrimos que Madonna cantou 70 músicas com duração de três a quatro minutos (minha favorita é “Crazy for You!”).

Como contar o número de células em um intervalo que contêm números?

A função COUNT conta o número de células em um intervalo contendo números. Por exemplo, a fórmula COUNT(B5:C14) na célula C2 exibe 9 porque nove células (as células em C5:C13) do intervalo B5:C14 contêm números. (Consulte a Figura 2.)

Como contar o número de células em branco, em um intervalo?

A função COUNTBLANK conta o número de células em branco, em um intervalo. Por exemplo, a fórmula COUNTBLANK(B5:C14) inserida na célula C4 retorna um valor 2 porque duas células (B14 e C14) estão em branco no intervalo B5:C14.

Como contar o número de células preenchidas, em um intervalo?

A função COUNTA retorna o número de células preenchidas, em um intervalo. Por exemplo, a fórmula COUNTA(B5:C14) na célula C2 retorna 18 porque 18 células do intervalo B5:C14 não estão em branco.

Início da página Início da página

 
 
Aplica-se a:
Excel 2007