Dicas do Excel: Contar/somar células por cor (fundo, fonte, formatação condicional)

No dia a dia, a marcação por cores é um método popular para distinguir e destacar rapidamente dados cruciais. Mas, como podemos contar ou somar os dados das células com base em uma cor específica (cor de preenchimento, cor da fonte, formatação condicional)? Por padrão, o Excel não oferece uma função direta para contar ou somar por cor. No entanto, com alguns truques e métodos indiretos, ainda podemos alcançar isso. Este artigo explorará como contar ou somar dados por cor.

Contar e somar células com base na cor de fundo

• Usando Função Definida pelo Usuário
• Usando um recurso poderoso – Kutools para Excel
• Usando Filtro e SUBTOTAL

Contar e somar células com base na cor da fonte

• Usando Função Definida pelo Usuário
• Usando um recurso fácil – Kutools para Excel

Contar e somar células com base na cor da formatação condicional

• Usando código VBA
• Usando um recurso inteligente – Kutools para Excel

Vídeo: Contar e somar células com base na cor

Contar e somar células com base na cor de fundo

Por exemplo, se você tiver um intervalo de dados onde os valores estão preenchidos com diferentes cores de fundo, conforme mostrado na captura de tela abaixo. Para contar ou somar as células com base em uma cor específica, o Excel não oferece uma função direta para contar ou somar células com base em sua cor de fundo. No entanto, com um pouco de criatividade e algumas técnicas úteis, você pode realizar essa tarefa. Vamos explorar alguns métodos úteis nesta seção.

Contar e somar células pela cor de fundo com Função Definida pelo Usuário

Aqui, mostraremos como criar e usar tal Função Definida pelo Usuário para resolver esta tarefa no Excel. Siga os passos abaixo:

Passo 1: Abra o editor de módulo VBA e copie o código

1. Pressione Alt + F11 para abrir a janela do Microsoft Visual Basic for Applications.
2. Na janela aberta, clique em Inserir > Módulo para criar um novo módulo em branco.
3. Depois, copie e cole o código abaixo no módulo em branco.
   Código VBA: Contar e somar células com base na cor de fundo

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Passo 2: Crie fórmulas para contar e somar células pela cor de fundo

Após colar o código acima, feche a janela do módulo, depois aplique as seguintes fórmulas:

• Contar células com base na cor de fundo específica:
  Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Depois, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Observação: Nesta fórmula, G2 é a célula de referência com a cor de fundo específica que você deseja combinar; $B$2:$E$12 é o intervalo onde você quer contar o número de células da cor de G2; FALSO é usado para contar células com cor correspondente.
  
• Somar células com base na cor de fundo específica:
  Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Depois, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Observação: Nesta fórmula, G2 é a célula de referência com a cor de fundo específica que você deseja combinar; $B$2:$E$12 é o intervalo onde você quer contar o número de células da cor de G2; VERDADEIRO é usado para somar células com cor correspondente.
  

Contar e somar células pela cor de fundo com um recurso poderoso

Para aqueles que não estão familiarizados com programação, o VBA pode parecer bastante complexo. Aqui, apresentaremos uma ferramenta poderosa - Kutools para Excel, cujo recurso Contar por Cor permite calcular facilmente (contar, somar, média, etc.) com base na cor de fundo em apenas alguns cliques. Impressionantemente, o recurso Contar por Cor vai além das cores de fundo – ele também pode diferenciar e calcular com base nas cores da fonte e na formatação condicional.

Após baixar e instalar o Kutools para Excel, primeiro selecione o intervalo de dados que você deseja contar ou somar células com base em uma cor de fundo específica. Em seguida, navegue até Kutools Plus e escolha Contar por Cor.

Na caixa de diálogo Contar por Cor, especifique as operações:

1. Selecione Formatação Padrão na lista suspensa Método de Cor;
2. Especifique Fundo na lista suspensa Tipo de Contagem, e você pode visualizar os resultados estatísticos para cada cor de fundo na caixa de diálogo;
3. Por fim, clique em Gerar Relatório para exportar os resultados calculados para uma nova pasta de trabalho.

Resultado:

Agora você terá uma nova pasta de trabalho com as estatísticas. Veja a captura de tela:

Contar e somar células pela cor de fundo com Filtro e função SUBTOTAL

Suponhamos que temos uma tabela de vendas de frutas como mostrado na captura de tela abaixo, e contaremos ou somaremos as células coloridas na Quantidade coluna.

Passo 1: Aplique a função SUBTOTAL

Selecione células em branco para inserir a função SUBTOTAL.

• Para contar todas as células com a mesma cor de fundo, insira a fórmula:

  =SUBTOTAL(102, F2:F16)

• Para somar todas as células com a mesma cor de fundo, insira a fórmula;

  =SUBTOTAL(109, F2:F16)

• Observação: nas fórmulas acima, 102 representa contar valores numéricos em uma lista filtrada enquanto exclui células ocultas; 109 representa somar valores em uma lista filtrada excluindo células ocultas; F2:F16 é o intervalo sobre o qual a contagem ou soma será calculada.
• 

Passo 2: Filtre células com base em uma cor específica

1. Selecione o cabeçalho da tabela e clique Dados > Filtro. Veja a captura de tela:
   
2. Clique no Filtro ícone  na célula do cabeçalho da Quantidade coluna, e clique Filtrar por Cor e a cor específica que você irá contar sucessivamente. Veja a captura de tela:
   

Resultado:

Após filtrar, as fórmulas SUBTOTAL automaticamente contam e somam as células coloridas na Quantidade coluna. Veja a captura de tela:

Contar e somar células com base na cor da fonte

Quer contar ou somar células com base na cor da fonte no Excel? Digamos que você tenha os dados, como na captura de tela fornecida, com células contendo textos em vermelho, azul, laranja e preto. O Excel não facilita isso por padrão. Mas não se preocupe! Nesta seção, mostraremos alguns truques simples para fazer exatamente isso.

Contar e somar células com base na cor da fonte com Função Definida pelo Usuário

Para contar e somar células com cores específicas de fonte, a seguinte Função Definida pelo Usuário pode ajudá-lo a resolver esta tarefa. Siga os passos abaixo:

Passo 1: Abra o editor de módulo VBA e copie o código

1. Pressione Alt + F11 para abrir a janela do Microsoft Visual Basic for Applications.
2. Na janela aberta, clique em Inserir > Módulo para criar um novo módulo em branco.
3. Depois, copie e cole o código abaixo no módulo em branco.
   Código VBA: Contar e somar células com base na cor da fonte

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Passo 2: Crie fórmulas para contar e somar células pela cor da fonte

Após colar o código acima, feche a janela do módulo, depois aplique as seguintes fórmulas:

• Contar células com base na cor específica da fonte:
  Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Depois, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Observação: Nesta fórmula, G2 é a célula de referência com a cor específica da fonte que você deseja combinar; $B$2:$E$12 é o intervalo onde você quer contar o número de células da cor de G2.
  
• Somar células com base na cor específica da fonte:
  Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Depois, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Observação: Nesta fórmula, G2 é a célula de referência com a cor específica da fonte que você deseja combinar; $B$2:$E$12 é o intervalo onde você quer contar o número de células da cor de G2.
  

Contar e somar células com base na cor da fonte com um recurso fácil

Procura contar ou somar valores de células no Excel com base na cor da fonte de forma eficiente? Explore o recurso Contar por Cor do Kutools para Excel! Com esta ferramenta inteligente, contar e somar células por uma cor específica de fonte se torna muito fácil. Descubra como o Kutools pode transformar sua experiência no Excel.

Após baixar e instalar o Kutools para Excel, primeiro selecione o intervalo de dados que você deseja contar ou somar células com base em uma cor específica de fonte. Depois, clique em Kutools Plus > Contar por Cor para abrir a caixa de diálogo Contar por Cor.

Na caixa de diálogo Contar por Cor, especifique as operações:

1. Selecione Formatação Padrão na lista suspensa Método de Cor;
2. Especifique Fonte na lista suspensa Tipo de Contagem, e você pode visualizar os resultados estatísticos para cada cor de fonte na caixa de diálogo;
3. Por fim, clique em Gerar Relatório para exportar os resultados calculados para uma nova pasta de trabalho.

Resultado:

Agora, você tem uma nova pasta de trabalho exibindo as estatísticas detalhadas com base na cor da fonte. Veja a captura de tela:

Contar e somar células com base na cor da formatação condicional

No Excel, você pode usar frequentemente a Formatação Condicional para aplicar uma cor específica às células que atendem a determinados critérios, tornando a visualização dos dados intuitiva. Mas e se você precisar contar ou somar essas células formatadas especificamente? Embora o Excel não ofereça uma maneira direta para isso, aqui estão formas de contornar essa limitação.

Contar e somar células formatadas condicionalmente com código VBA

Contar e somar células formatadas condicionalmente no Excel não é direto usando funções embutidas. No entanto, você pode realizar essa tarefa usando código VBA. Vamos ver como você pode usar o VBA para isso:

Passo 1: Abra o editor de módulo VBA e copie o código

1. Pressione Alt + F11 para abrir a janela do Microsoft Visual Basic for Applications.
2. Na janela aberta, clique em Inserir > Módulo para criar um novo módulo em branco.
3. Depois, copie e cole o código abaixo no módulo em branco.
   Código VBA: Contar e somar células com base na cor da formatação condicional

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Passo 2: Execute este código VBA

1. Após colar o código, pressione F5 para executar este código, uma caixa de prompt aparecerá, por favor, selecione o intervalo de dados onde você deseja contar e somar células com base na formatação condicional. Depois, clique OK, Veja a captura de tela:
   
2. Em outra caixa de prompt, selecione uma cor específica de formatação condicional que você deseja contar e somar, e clique OK botão, veja a captura de tela:
   

Resultado:

Agora, o resultado, que inclui tanto a contagem quanto a soma de células com a cor específica de formatação condicional, será exibido na caixa pop-up. Veja a captura de tela:

Contar e somar células formatadas condicionalmente com um recurso inteligente

Se você está procurando outros métodos rápidos e fáceis para contar e somar células formatadas condicionalmente, o Kutools para Excel é sua solução ideal. Seu recurso Contar por Cor pode resolver essa tarefa em poucos cliques. Confira para descobrir a eficiência e precisão que o Kutools pode trazer ao seu fluxo de trabalho.

Após baixar e instalar o Kutools para Excel, primeiro selecione o intervalo de dados que você deseja contar ou somar células com base em uma cor específica de formatação condicional. Depois, clique em Kutools Plus > Contar por Cor para abrir a caixa de diálogo Contar por Cor.

Na caixa de diálogo Contar por Cor, especifique as operações:

1. Selecione Formatação Condicional na lista suspensa Método de Cor;
2. Especifique Fundo na lista suspensa Tipo de Contagem, e você pode visualizar os resultados estatísticos para cada cor de formatação condicional na caixa de diálogo;
3. Por fim, clique em Gerar Relatório para exportar os resultados calculados para uma nova pasta de trabalho.

Resultado:

Agora, você tem uma nova pasta de trabalho exibindo as estatísticas detalhadas com base na cor da formatação condicional. Veja a captura de tela:

Artigos Relacionados: