Dicas do Excel: Contar/somar células por cor (fundo, tipo de letra, Usar Formatação Condicional)

Nas tarefas diárias, a marcação por cores é um método popular para distinguir e destacar rapidamente dados importantes. Mas como contar ou somar os valores de células com base numa cor específica — seja cor de preenchimento, cor da fonte ou formatação condicional? Por predefinição, o Excel não inclui uma funcionalidade direta para contar ou somar por cor. Ainda assim, com alguns truques e abordagens indiretas, é possível alcançar esse objetivo. Este artigo mostra-lhe como contar ou somar dados por cor.

Contar e somar células com base em Cor de Fundo

• Ao utilizar uma Função Definida pelo Utilizador
• Ao utilizar uma funcionalidade poderosa – Kutools para Excel
• Ao utilizar Filtro e SUBTOTAL

Contar e somar células com base em Cor da Fonte

• Ao utilizar uma Função Definida pelo Utilizador
• Ao utilizar uma funcionalidade fácil – Kutools para Excel

Contar e somar células com base na cor Usar Formatação Condicional

• Ao utilizar código VBA
• Ao utilizar uma funcionalidade inteligente – Kutools para Excel

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

Contar e somar células com base em Cor de Fundo

Por exemplo, se tiver um intervalo de dados cujos valores estão preenchidos com diferentes Cor de Fundo, conforme mostrado na captura de ecrã abaixo. Para contar ou somar as células com base numa cor específica, o Excel não oferece uma funcionalidade direta para contar ou somar células com base na sua Cor de Fundo. Contudo, com um pouco de engenho e algumas técnicas práticas, pode realizar esta tarefa. Vamos explorar alguns métodos úteis nesta secção.

Contar e somar células por Cor de Fundo com Função Definida pelo Utilizador

Aqui mostramos como criar e utilizar esta Função Definida pelo Utilizador para resolver a tarefa no Excel. Siga os passos seguintes:

Passo 1: Abrir o editor de módulos VBA e copiar o código

1. Prima Alt + F11 para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Na janela aberta, clique em Inserir > Módulo para criar um novo módulo em branco.
3. Em seguida, copie e cole o código abaixo no módulo em branco.
   Código VBA: Contar e somar células com base em 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: Criar fórmulas para contar e somar células por Cor de Fundo

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

• Contar células com base na cor de fundo específica:
  Copie ou introduza a fórmula abaixo na célula pretendida para obter o resultado. Depois, arraste a alça de preenchimento para baixo para aplicar a fórmula a outras células. Veja a captura de ecrã:

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

  
  
  Nota: nesta fórmula, G2 é a célula de referência com a cor de fundo específica que pretende corresponder; $B$2:$E$12 é o intervalo onde pretende contar o número de células com a mesma cor de fundo que G2; e FALSE indica que se pretende contar apenas as células cuja cor de fundo corresponda exatamente.
  
• Some células com base numa cor de fundo específica:
  Copie ou introduza a fórmula abaixo na célula pretendida para obter o resultado. Depois, arraste a alça de preenchimento para baixo para aplicar a fórmula a outras células. Veja a captura de ecrã:

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

  
  
  Nota: nesta fórmula, G2 é a célula de referência com a cor de fundo específica que pretende corresponder; $B$2:$E$12 é o intervalo onde pretende somar as células com a mesma cor de fundo que G2; e TRUE indica que se pretende somar células com cor correspondente.
  

Contar e somar células por Cor de Fundo com uma funcionalidade poderosa

Para quem não está familiarizado com programação, o VBA pode parecer bastante complexo. Apresentamos-lhe uma ferramenta poderosa – Kutools for Excel – cuja funcionalidade Contar por Cor lhe permite calcular facilmente (contar, somar, obter a média, etc.) com base na cor de fundo em apenas alguns cliques. De forma impressionante, a funcionalidade Contar por Cor vai além da simples cor de fundo: também consegue distinguir e calcular com base na cor da fonte e na formatação condicional.

Após descarregar e instalar Kutools para Excel, selecione primeiro o intervalo de dados relativamente ao qual pretende contar ou somar células com base numa determinada cor de fundo. Em seguida, aceda a KUTOOLS PLUS e escolha Contar por Cor.

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

1. Selecione Formatação Padrãoda lista suspensa Modo de Cor;
2. Especifique Fundoda lista suspensa Tipo de Contagem, podendo pré-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 um novo livro.

Resultado:

Agora terá um novo livro com as estatísticas. Veja a captura de ecrã:

Contar e somar células por Cor de Fundo com a função Filtro e SUBTOTAL

Imagine que temos uma tabela de vendas de frutas, como mostrada na captura de ecrã abaixo, e queremos contar ou somar as células coloridas na coluna Montante.

Passo 1: Aplicar a função SUBTOTAL

Selecione células vazias para introduzir a função SUBTOTAL.

• Para contar todas as células com a mesma Cor de Fundo, introduza a fórmula:

  =SUBTOTAL(102, F2:F16)

• Para somar todas as células com a mesma Cor de Fundo, introduza a fórmula;

  =SUBTOTAL(109, F2:F16)

• Nota: nas fórmulas acima, 102 representa a contagem de valores numéricos numa lista filtrada, excluindo células ocultas; 109 representa a soma de valores numa lista filtrada, excluindo células ocultas; e F2:F16 é o intervalo sobre o qual será efetuada a contagem ou a soma.
• 

Passo 2: Filtrar células com base numa cor específica

1. Selecione o cabeçalho da tabela e clique em Dados > Filtrar. Veja a captura de ecrã:
   
2. Clique no ícone Filtrar na célula do cabeçalho da coluna Montante, depois em Filtrar por Cor e, de seguida, na cor específica pela qual pretende filtrar. Veja a captura de ecrã:
   

Resultado:

Após a filtragem, as fórmulas SUBTOTAL contam e somam automaticamente as células coloridas na coluna Montante. Veja a captura de ecrã:

Contar e somar células com base em Cor da Fonte

Pretende contar ou somar células com base na cor da fonte no Excel? Imagine que tem os seus dados organizados como na captura de ecrã apresentada, com texto em cores como vermelho, azul, laranja e preto. O Excel não torna esta tarefa fácil por definição — mas não se preocupe! Nesta secção, revelamos alguns truques simples para o conseguir.

Contar e somar células com base em Cor da Fonte com uma Função Definida pelo Utilizador

Para contar e somar células com uma determinada Cor da Fonte, a seguinte Função Definida pelo Utilizador poderá ajudá-lo a resolver esta tarefa. Proceda da seguinte forma:

Passo 1: Abrir o editor do módulo VBA e copiar o código

1. Prima Alt + F11 para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Na janela aberta, clique em Inserir > Módulo para criar um novo módulo em branco.
3. Em seguida, copie e cole o código abaixo no módulo em branco.
   Código VBA: Contar e somar células com base em 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: Criar fórmulas para contar e somar células por Cor da Fonte

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

• Contar células com base na cor da fonte específica:
  Copie ou introduza a fórmula abaixo na célula pretendida para obter o resultado. Depois, arraste a alça de preenchimento para baixo para aplicar a fórmula a outras células. Veja a captura de ecrã:
  

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

  Nota: nesta fórmula, G2 é a célula de referência com a cor da fonte específica que pretende corresponder; $B$2:$E$12 é o intervalo onde pretende contar o número de células com a mesma cor de fonte que em G2.
  
• Somar células com base na cor da fonte específica:
  Copie ou introduza a fórmula abaixo na célula pretendida para obter o resultado. Depois, arraste a alça de preenchimento para baixo para aplicar a fórmula a outras células. Veja a captura de ecrã:
  

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

  Nota: nesta fórmula, G2 é a célula de referência com a cor da fonte específica que pretende corresponder; $B$2:$E$12 é o intervalo onde pretende somar as células com a mesma cor de fonte que em G2.
  

Contar e somar células com base em Cor da Fonte com uma funcionalidade fácil

Precisa de contar ou somar valores de células no Excel com base na cor do texto de forma simples? Experimente a funcionalidade Kutools para Excel do Contar por Cor! Com esta ferramenta inteligente, contar e somar células com base numa determinada cor do texto torna-se incrivelmente fácil. Descubra como o Kutools pode transformar a sua experiência no Excel.

Após descarregar e instalar Kutools para Excel, selecione primeiro o intervalo de dados relativamente ao qual pretende contar ou somar células com base numa determinada cor da fonte. Em seguida, 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ãoda lista suspensa Modo de Cor;
2. Especifique Tipo de letrada lista suspensa Tipo de Contagem, podendo pré-visualizar os resultados estatísticos para cada Cor da Fonte na caixa de diálogo;
3. Por fim, clique em Gerar Relatório para exportar os resultados calculados para um novo livro.

Resultado:

Agora, tem um Novo Workbook que apresenta as estatísticas detalhadas com base em Cor da Fonte. Veja a captura de ecrã:

Contar e somar células com base na cor Usar Formatação Condicional

No Excel, é comum usar a formatação condicional para aplicar uma cor específica às células que cumprem determinados critérios, tornando a visualização dos dados mais intuitiva. Mas e se precisar de contar ou somar essas células formatadas? Embora o Excel não ofereça uma forma direta de o fazer, existem soluções alternativas para 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 é simples com as funções incorporadas. No entanto, pode realizar esta tarefa utilizando código VBA. Vejamos como pode usar o VBA para este fim:

Passo 1: Abrir o editor do módulo VBA e copiar o código

1. Prima Alt + F11 para abrir a janela do Microsoft Visual Basic para Aplicações.
2. Na janela aberta, clique em Inserir > Módulo para criar um novo módulo em branco.
3. Em seguida, copie e cole o código abaixo no módulo em branco.
   Código VBA: Contar e somar células com base na cor Usar 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: Executar este código VBA

1. Após colar o código, prima a tecla F5 para executá-lo. Aparecerá uma caixa de diálogo — por favor, selecione o intervalo de dados onde pretende contar e somar células com base na formatação condicional e, em seguida, clique em OKVeja a captura de ecrã:
   
2. Numa outra caixa de diálogo, selecione a cor específica da formatação condicional que pretende contar e somar e clique no botão OK. Veja a captura de ecrã:
   

Resultado:

Agora, o resultado, que inclui tanto a contagem como a soma das células com a cor Usar Formatação Condicional especificada, será apresentado na caixa que surge. Veja a captura de ecrã:

Contar e somar células formatadas condicionalmente com uma funcionalidade inteligente

Se procura outros métodos rápidos e simples para contar e somar células formatadas condicionalmente, o Kutools para Excel é a sua solução ideal. A sua funcionalidade Contar por Cor resolve esta tarefa em apenas alguns cliques. Descubra a eficiência e precisão que o Kutools pode trazer ao seu fluxo de trabalho.

Após descarregar e instalar Kutools para Excel, selecione primeiro o intervalo de dados relativamente ao qual pretende contar ou somar células com base numa determinada cor de formatação condicional. Em seguida, 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 Usar Formatação Condicionalda lista suspensa Modo de Cor;
2. Especifique Fundoda lista suspensa Tipo de Contagem, podendo pré-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 um novo livro.

Resultado:

Agora, tem um novo livro com estatísticas detalhadas com base na cor utilizada na Formatação Condicional. Veja a captura de ecrã:

Artigos Relacionados: