Como contar células COLORIDAS no Excel (guia passo a passo + VÍDEO)

Assistir ao vídeo - Como contar células coloridas no Excel

Não seria ótimo se houvesse uma função que pudesse contar células coloridas no Excel?

Infelizmente, não há nenhuma função embutida para fazer isso.

MAS…

Isso pode ser feito facilmente.

Como contar células coloridas no Excel

Neste tutorial, mostrarei três maneiras de contar células coloridas no Excel (com e sem VBA):

1. Usando filtro e função SUBTOTAL
2. Usando a função GET.CELL
3. Usando uma função personalizada criada usando VBA

Nº 1 Contagem de células coloridas usando filtro e SUBTOTAL

Para contar células coloridas no Excel, você precisa seguir as duas etapas a seguir:

• Filtrar células coloridas
• Use a função SUBTOTAL para contar células coloridas que são visíveis (após a filtragem).

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Existem duas cores de fundo usadas neste conjunto de dados (verde e laranja).

Aqui estão as etapas de contagem de células coloridas no Excel:

1. Em qualquer célula abaixo do conjunto de dados, use a seguinte fórmula: = SUBTOTAL (102, E1: E20)
   
2. Selecione os cabeçalhos.
3. Vá para Dados -> Classificar e Filtrar -> Filtrar. Isso aplicará um filtro a todos os cabeçalhos.
4. Clique em qualquer um dos menus suspensos do filtro.
5. Vá para ‘Filtrar por cor’ e selecione a cor. No conjunto de dados acima, uma vez que existem duas cores usadas para destacar as células, o filtro mostra duas cores para filtrar essas células.

Assim que filtrar as células, você notará que o valor na função SUBTOTAL muda e retorna apenas o número de células que ficam visíveis após a filtragem.

Como é que isso funciona?

A função SUBTOTAL usa 102 como o primeiro argumento, que é usado para contar células visíveis (linhas ocultas não são contadas) no intervalo especificado.

Se os dados não forem filtrados, retornarão 19, mas se forem filtrados, retornarão apenas a contagem das células visíveis.

Experimente você mesmo … Baixe o arquivo de exemplo

# 2 Contar células coloridas usando a função GET.CELL

GET.CELL é uma função Macro4 que foi mantida por motivos de compatibilidade.

Não funciona se for usado como funções regulares na planilha.

No entanto, ele funciona em intervalos nomeados do Excel.

Veja também: Saiba mais sobre a função GET.CELL.

Aqui estão as três etapas para usar GET.CELL para contar células coloridas no Excel:

• Crie um intervalo nomeado usando a função GET.CELL
• Use o intervalo nomeado para obter o código de cor em uma coluna
• Usando o número da cor para contar o número de células coloridas (por cor)

Vamos mergulhar fundo e ver o que fazer em cada uma das três etapas mencionadas.

Criando um intervalo nomeado

• Vá para Fórmulas -> Definir nome.
• Na caixa de diálogo Novo nome, digite:
  • Nome: GetColor
  • Escopo: Livro de exercícios
  • Refere-se a: = GET.CELL (38, Folha1! $ A2)
    Na fórmula acima, usei Folha1! $ A2 como o segundo argumento. Você precisa usar a referência da coluna onde estão as células com a cor de fundo.

Obtendo o código de cores para cada célula

Na célula adjacente aos dados, use a fórmula = GetColor

Esta fórmula retornaria 0 se NÃO houvesse cor de fundo em uma célula e retornaria um número específico se houvesse uma cor de fundo.

Este número é específico para uma cor, então todas as células com a mesma cor de fundo recebem o mesmo número.

Conte as células coloridas usando o código de cores

Se você seguir o processo acima, terá uma coluna com números correspondentes à cor de fundo.

Para obter a contagem de uma cor específica:

• Em algum lugar abaixo do conjunto de dados, dê a mesma cor de fundo a uma célula que você deseja contar. Certifique-se de fazer isso na mesma coluna que usou na criação do intervalo nomeado. Por exemplo, usei a coluna A e, portanto, usarei apenas as células da coluna 'A'.
• Na célula adjacente, use a seguinte fórmula:

= CONT.SE ($ F $ 2: $ F $ 20, GetColor)

Esta fórmula fornecerá a contagem de todas as células com a cor de fundo especificada.

Como funciona?

A função CONT.SE usa o intervalo nomeado (GetColor) como critério. O intervalo nomeado na fórmula se refere à célula adjacente à esquerda (na coluna A) e retorna o código de cor dessa célula. Portanto, este número de código de cor é o critério.

A função CONT.SE usa o intervalo ($ F $ 2: $ F $ 18) que contém os números do código de cores de todas as células e retorna a contagem com base no número do critério.

Experimente você mesmo … Baixe o arquivo de exemplo

# 3 Contagem colorida usando VBA (criando uma função personalizada)

Nos dois métodos acima, você aprendeu como contar células coloridas sem usar o VBA.

Mas, se você estiver satisfeito com o uso do VBA, este é o mais fácil dos três métodos.

Usando o VBA, criaríamos uma função personalizada, que funcionaria como uma função CONT.SE e retornaria a contagem de células com a cor de fundo específica.

Aqui está o código:

'Código criado por Sumit Bansal de https://trumpexcel.com Função GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Defina rCell = CountRange para cada rCell em CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Para criar esta função personalizada:

• Com sua pasta de trabalho ativa, pressione Alt + F11 (ou clique com o botão direito na guia da planilha e selecione Exibir código). Isso abriria o Editor VB.
• No painel esquerdo, sob a pasta de trabalho na qual você está trabalhando, clique com o botão direito em qualquer uma das planilhas e selecione Inserir -> Módulo. Isso iria inserir um novo módulo. Copie e cole o código na janela de código do módulo.
• Clique duas vezes no nome do módulo (por padrão, o nome do módulo no Módulo1) e cole o código na janela de código.
• Feche o Editor VB.
• É isso! Agora você tem uma função personalizada na planilha chamada GetColorCount.

Para usar esta função, simplesmente use-a como qualquer função regular do Excel.

Sintaxe: = GetColorCount (CountRange, CountColor)

• CountRange: o intervalo no qual você deseja contar as células com a cor de fundo especificada.
• CountColor: a cor para a qual você deseja contar as células.

Para usar esta fórmula, use a mesma cor de fundo (que você deseja contar) em uma célula e use a fórmula. O argumento CountColor seria a mesma célula em que você está inserindo a fórmula (conforme mostrado abaixo):

Observação: Como há um código na pasta de trabalho, salve-o com uma extensão .xls ou .xlsm.

Experimente você mesmo … Baixe o arquivo de exemplo

Você conhece alguma outra maneira de contar células coloridas no Excel?

Se sim, compartilhe comigo deixando um comentário.