Como Contar Células Pela Cor na Planilha de Excel

Neste artigo, vamos aprender como contar células pela cor na planilha de Excel, de maneira bem fácil para usuários iniciantes e também, os mais experientes. Formatar uma planilha de Excel facilita na revisão e na visualização de relatórios, tanto impressos, como na tela do computador. Nesta postagem vamos aprender como contar as células pela cor. Isso pode servir, para você definir parâmetros destacados de dados que representa cada cor separada.

Existem diferentes formas de contar as células em uma planilha, de acordo com a cor do preenchimento, ou da fonte. Mas, por que colorir as células de uma planilha? Bom, há muitas razões para isso. Você pode querer que algumas células sejam destacadas, porque tem determinados valores importantes e sua planilha é enorme. Ou simplesmente quer mostrar para alguém os pontos mais relevantes de sua planilha.

Então, vamos ver quais são a melhores maneiras de destacar células com cores e contar estas células em uma planilha de Excel.

Contar Células Pela Cor com Filtro e a função Subtotal

Esta é a maneira mais fácil e muito útil, se você tem células coloridas e quer contá-las em apenas uma coluna de sua planilha. É verdade que você pode contar em quantas colunas quiser, mas deve desativar o filtro de uma coluna se quiser contar as células pela cor em outra coluna.

Vamos lá:

Você pode pintar as células manualmente ou com uma formatação condicional. Se deseja aplicar uma cor de preenchimento com formatação condicional, leia esta postagem: Aplicar cor da célula com formatação condicional.

A aplicação de preenchimento da célula com formatação condicional é legal porque é automática e dinâmica, ou seja, você escolher os critérios e quando editar a planilha, os formatos vão se alterando automaticamente.

Etapas para contar as células com cores de preenchimento com filtro e a função subtotal

Este é um exemplo bem simples para contar células pela cor e como pode funcionar uma planilha com células coloridas.

Contar células pela cor na planilha

Escolha uma célula, onde você deseja inserir a fórmula. Geralmente na parte final da planilha, porque quando filtrar as células coloridas, o filtro oculta as células sem preenchimento de cor.

Digite a fórmula =SUBTOTAL(2;referência1:referência2)

Exemplo: =SUBTOTAL(2;B2:B18)

Pode ser usado 102 também. Qualquer um destes dois números faz referência a função CONT.NÚM, que vai contar somente as células selecionadas, ou filtradas, como vamos ver a seguir.

Função SUBTOTAL com a REFERÊNCIA 2

Depois de inserir a fórmula para contar células pela cor, ela contará todas as células da planilha na coluna determinada, que não estiver vazia.

Aplicando filtro:

  • Clique em qualquer parte do cabeçalho da planilha e vá na guia DADOS.
  • Localize a opção Filtro no grupo Classificar e Filtrar.
    • O ícone se parece com um funil.
    • Então clique nele!
  • Uma seta será adicionada em todo o cabeçalho, mas você vai usar a seta da coluna onde há células coloridas que você quer contar.
Células coloridas que você quer contar
  • Clique na seta do filtro para escolher a opção e contar as células pela cor que você selecionar.
  • Selecione Filtrar Por Cor e, em seguida, Filtrar Por Cor de Célula.
    • Clique na cor que deseja filtrar.
    • Ou clique em Sem Preenchimento, caso queira filtrar as células sem a cor.
Filtrar Por Cor de Célula
  • Agora sua planilha exibirá apenas os dados relacionados às células com a cor que você selecionou para contar.
Resultado de Contar Células Pela Cor no Excel

Note também, que a quantidade de células contadas com a fórmula adicionada também foi alterada.

  1. Para desfazer esta ação clique na SETA do Filtro.
  2. Depois escolha a opção: Limpar Filtro de "nome do cabeçalho" e tudo voltará ao normal.

Note, que esta maneira de contar as células só é possível em apenas uma coluna de cada vez. Concira as vantagens de desvantagens.

  • Vantagens:
    • Mais fácil de usar.
    • É compatível com cores da formatação condicional.
    • Usa filtro para minimizar o tamanho da lista selecionada.
    • Altera dinamicamente a contagem de cores.
  • Desvantagens: Só calcula células de uma determinada coluna por vez.

A cor de preenchimento de uma célula é amplamente usada pelos usuários do Excel porque, destacando-a com uma cor diferente, pode-se identificá-la facilmente na tela.

Às vezes, a cor significa que os dados pertencem a uma categoria específica, ou talvez algum status, ou alguma ação, que devemos tomar como base nessa cor. O ponto importante é, que quando temos várias células com uma cor de preenchimento, é quase inevitável que chegue a hora em que precisamos contá-las por cor. Então, vamos aprender agora como contar células pela cor em uma planilha de Excel.

Use o VBA Para Contar as Células Pela Cor de Preenchimento no Excel

Apesar de não usarmos a função CONT.SE, ainda podemos obter os resultados com o uso do VBA. Com o VBA, podemos criar a função definida pelo usuário e salvar no arquivo para que ele possa ser usado para cada pasta de trabalho e de outro computador.

Para começar você precisa saber um pouquinho do código VBA e ter a guia DESENVOLVEDOR habilitada no seu Excel. Se sim, faça o teste!

  • Abra uma nova planilha de Excel.
  • Vá para a faixa de opções Desenvolvedor.
  • No grupo Código, clique em Visual Basic.
  • Com a janela de VBA aberta clique em Inserir --> Módulo.
  • Copie o código a seguir e cole na tela do VBA.
Function CONTAR_COR_CELULA(ColorCells As Range, DataRange As Range)
Dim Data_Range As Range
Dim Cell_Color As Long
Cell_Color = ColorCells.Interior.ColorIndex
For Each Data_Range In DataRange
If Data_Range.Interior.ColorIndex = Cell_Color Then
CONTAR_COR_CELULA = CONTAR_COR_CELULA + 1
End If
Next Data_Range
End Function

Neste exemplo, CONTAR_COR_CELULA é a função criada, para ser adicionada na contagem das células. Aqui é possível contar células pela cor. Mas, você pode alterar o termo CONTAR_COR_CELULA, por qualquer outro nome que desejar. No entanto, lembre-se de alterar em todas as linhas do código, ou não irá funcionar.

  • Feche a tela do Visual Basic Application.
  • Vá para a planilha, que acabou de criar para testes e digite a fórmula:

Nota: se você seguiu o exemplo, conforme a imagem a seguir, o intervalo de cores está em A2 até B15.

Contar Células Pela Cor na Planilha de Excel

Depois de ter feito o código VBA, chegou a hora de criar a fórmula para contar as cores das células. Adicione uma das cores em D1 e na célula D2 digite:

=CONTAR_COR_CELULA(D2;B2:B11)

Se você pintou as células com as mesmas cores da imagem acima, o resultado será exato, para a referência da fórmula. Esta é apenas uma das várias opções para contar células pela cor na planilha de Excel.

Importante: como é uma função definida pelo usuário, sempre que fizer alguma alteração na cor de uma, ou mais células, a fórmula deve ser atualizada, para que as alterações tenham efeito na contagem.

  • Vantagens:
    • Conta as cores da planilha inteira ou dentro de um intervalo de células.
    • Conta mais de uma coluna por vez dentro do parâmetro da fórmula.
  • Desvantagens:
    • Você precisa atualizar a fórmula, sempre que modificar uma cor.
    • Incompatibilidade parcial com as cores aplicadas pela formatação condicional.
    • Mais difícil, para usuários iniciantes.

Estas são as duas melhores opções para contar as células pela cor na planilha de Excel.

Use o Comando Localizar e Selecionar para Contar Células Coloridas no Excel

O comando Localizar e Selecionar é uma das ferramentas mais úteis do Excel para executar qualquer tarefa relacionada ao Excel. Aqui, vamos utilizá-lo para contar células coloridas no Excel.

Considere o mesmo exemplo do VBA que utilizamos para explicar, na imagem, agora com a opção Localizar e Selecionar, para contar células pela cor na planilha de Excel.

  • Vá para a guia Página Inicial e no grupo Edição clique em Localizar e Selecionar.
Opção Localizar no Excel
  • Na caixa pop-up Localizar e substituir, clique em Opções para mostrar mais detalhes se eles estiverem ocultos.
  • Na mesma caixa pop-up Localizar e substituir, clique na lista suspensa em Formatar -> Escolher formato da célula.
Escolher formato da célula
  • A caixa será fechada momentaneamente e o cursor neste instante é modificado.
  • Clique na célula com a cor, na qual você deseja contar.
    • A caixa pop-up volta a ativa, já com a cor selecionada.
  • Clique em Visualizar.
Contar células pela com com a opção localizar

Agora você já pode ver quantas células possuem a cor que foi selecionada, conforme mostra a imagem acima. Esta é uma opção para quem não quer mexer na planilha com adição de fórmulas ou códigos do Visual basic Applications. Compre o melhor notebook pelo menor preço!



Confira Estes Produtos Que Eu Recomendo

Compre Planilhas de Excel

Confira os Preços

Climatizador de Ar

Condira os Preços

Notebook DELL Inspiron

Confira os Preços

Celular da Marca Xiaomi

Confira os Preços

Notebook Lenovo IdeaPad

Confira Os Preços

Smartphone Samsung Galaxy

Confira os Preços

Aspirador de Pó Robô

Confira os Preços

Os Melhores Ventiladores

Confira os Preços

Notebook Gamer

Confira os Preços

Materiais para Escritórios

Confira os Preços

Impressora Multifuncional de Tinta

Confira os Preços

Smart TV Samsung de 32 a 70

Confira os Preços

Edivaldo
Edivaldo

Edivaldo. Analista de funções e fórmulas de Excel, adoro fazer planilhas e compartilhar meus conhecimentos com as pessoas. Seja bem-vindo ao tudo Excel, onde você aprende de graça e tira suas dúvidas.

38 Comentários
Mostrar Todos Mais Útil Classificação Mais Alta Classificação Mais Baixa Adicionar a sua avaliação
  1. Olá, gostaria de contar células com base em texto específico e cor da célula. Você pode me ajudar?

  2. Muito bom, parabéns pelo conteúdo, me ajudou muito.

  3. Preciso contar células com formatação condicional

  4. Olá

    Obrigada pelas dicas.
    Fiz aqui, porém não deu certo. Aparece apenas metade do valor das células naquela cor, por exemplo, tenho 18 células na cor laranja e o resultado da fórmula dá 9.

    Agradeço se puder me ajudar.

    • Olá, Bianca Vilaronga.
      Isso funciona, mas acho que esqueci de mencionar alguns detalhes e já vou corrigir isso. Primeiro: copiar o código VBA e colar no módulo VBA. Depois é preciso determinar as cores, as referências de cores e por último adicionar a fórmula.
      Eu acabei de testar aqui e funcionou. Outra coisa. Após qualquer alteração as fórmulas deverão ser atualizadas. Uma macro ATUALIZAR resolve e é simples de criar.

  5. Boa Tarde .
    Não Funciona Com Formatação condicional.

  6. Obrigada, vou tentar 🙂

  7. Olá! Fiz tudo do jeito que está aí, mas não está contabilizando as cores. É porque as células que quero contar estão com formatação condicional de cor?

    • Oi... Pode ser isso. tente limpar a formatação condicional, ou faça os testes em uma nova planilha primeiro.

  8. Boa tarde...
    Existe alguma forma de fazer com que a formula entenda a troca de cores automaticamente sem ter que atualizar?

  9. Eu fechei e reabri e o modulo sumiu.. .como mantem isso salvo?

    • Respostas
      JACQUELINE RAMOS DE ALMEIDA 05/04/2020 as 13:22

      Boa tarde! A função consta como existente na célula, mas quando reabri o documento o procedimento também perdeu efeito.
      Não sei o que houve. Refiz a função, ela não constava mais como uma função. Voltei à programação, a função reapareceu, mas não funcionou mais.

    • Olá Danilo, o comum é manter salvo, tente de novo!

  10. Boa tarde!
    É possível separar por cor com algum escrito junto? Por exemplo, a cor amarela com um número 1 na célula, e uma amarela com o número 2, contando as cores, mas com separação dos escritos na cor?
    É para um mapa corporativo... Obrigada!

    • Oi Daline. Para este critério, pode ser usado uma formatação condicional. Basta escolher a escala de cores.

  11. Pessoal boa tarde, adorei este código, fiz o teste e deu certo, entretanto não funciona quando eu aplico essa função com a formatação condicional. Alguém sabe como adaptar este código para funcionar com a formatação condicional?

    Desde já agradeço a ajuda e atenção

    Eliana

    • Oi Eliana Reginaldo. Infelizmente ainda não tenho outro código que faça isso. Mas continuarei estudando e quando descobrir adicionarei uma posagem.

  12. Respostas
    Hélio Dias Durval 16/04/2019 as 18:29

    Boa noite Edivaldo, preciso de ajuda, tenho uma Plan Excel com varias colunas/linhas, quero que na coluna 10 ela me dê o resultado de quantas letras eu tenho nas 09 colunas/celulas/na mesma linha anterior, sendo que em uma coluna/celula eu tenho 03 letras (Ex.: PPP) em outra na mesma linha 04 letras (Ex.: PPPP) e nas demais 01 letra em cada coluna/celula (Ex.: P)
    Pergunta - Qual a fórmula a ser aplicada. Excel 2013.

  13. Fala Edivaldo.
    Aqui eu já trabalhava com uma função parecida com a sua e funcionava bem, só que contava as cores como um todo.
    Infelizmente minha planilha antiga corrompeu e perdi o arquivo sem conseguir explorar o VBA para extrair a função.
    Pergunta, como proceder para ele contar inclusive as células que foram mesclada.
    Ex:
    Tenho 6 células em amarelo, destas 6, 2 estão mescladas de 2 em 2, neste caso o correto seriam 4 células amarelas, sendo 2 celulas normais, mais 2 celulas que foram mescladas de 2 em 2.

    A Pergunta é: Como fazer para contar a quantidade de cores, e não a quantidade de células?

    • Oi Alan Cristo. O código teria que ser o que está no post. Se não funcionar, eu vou ter que ver depois e tentar criar outro. Mas confesso, que VBA não é muito minha praia não. Faço pouquíssimas coisas, justamente, por causa disso que você falou "corrompimento". Se acontecer perde tudo, ou dá muito trabalho para recuperar.

  14. Olá! Conseguir fazer a fórmula e funcionou normalmente, porém quando fechei e abri novamente deu erro. O que pode ter acontecido

    • Oi Tarsila.
      Veja se você salvou, antes de fechar, ou se fez alguma alteração e salvou. Se você fez algo na planilha e funcionou, alguma coisa foi alterada para não funcionar posteriormente. Tente de novo!

  15. Bom dia Edivaldo.
    Testei aqui e deu Certo.
    Parabéns!!!

    Qual a possibilidade de aplicar essa FUNÇÃO para célula colorida pela formatação condicional??

    Aguardo Retorno

    • Oi Eurico, obrigado por ter entendido minha explicação e ter conseguido fazer funcionar. Eu tenho tutoriais de formatação condicional, e é muito fácil fazer do jeito que você quiser aí.

      • Desculpe Edivaldo, acho que não fui claro em minha explanação.
        As células que preciso contar pelo CONTACOR, foram coloridas utilizando a Formatação Condicional. Essas células não a Função não consegue contar.

        • OK. A função CONTACOR é uma função criada por mim e deve ser incorporada no VBA. O usuário deve entender de Visual Basic for Applications. Eu não sou bom em VBA não. O meu negócio trabalhar com funções.

        • hammm... entendi.
          Beleza então Edivaldo. Obrigado pela atenção e pelo retorno.

  16. EDIVALDO OLÁ SOU LEIGO EM EXCEL E PRECISO APRENDER DESDE O BÁSICO AO AVANÇADO. POIS PRECISO USAR NESTAS ÁREAS
    Funções Financeiras
    Funções Lógicas
    Gráficos na Planilha
    Finanças e Investimentos
    Macros e VBA
    Formatação Condicional
    PARA SETOR DE PCP . LOGISTICA , SUPLLY CHAIN , COMPRAS , ETC...
    COMO PODE ME AJUDAR ?

    • Oi FRANCISCO FILHO.
      Você está no lugar certo. Pode explorar o site inteiro que você, certamente, vai aprender tudo isso. Qualquer dúvida escreva comentários.

  17. Bom dia, no caso de formatação condicional não está funcionando

  18. Bom dia amigos, eu fiz igual deu certo, porém não consigo por em caso de 2 cores diferentes, só conta uma. (exp 8 vermelhas, 2 verdes, 3 pretas.) só conta 8 vermelhas. ME AJUDEM PFV!

Escreva seu Comentário

Tudo Excel
Logo