Neste artigo, você vai aprender como contar células por cor no Excel e obter a soma das células coloridas. Essas soluções funcionam tanto para células coloridas manualmente quanto com formatação condicional. Você também vai ver como filtrar células por várias cores no Excel 2010, 2013, 2016 e 2019.
Se você costuma usar várias cores de preenchimento e fonte para diferenciar vários tipos de células ou valores, eu aposto que é de seu interesse aprender contar quantas células são realçadas com uma determinada cor na planilha. Se os valores das células forem números, é possivel calcular automaticamente a soma das células sombreadas com a mesma cor.
Talvez você já saiba que o Microsoft Excel fornece uma variedade de fórmulas para diferentes propósitos, e seria lógico supor que existam algumas para contar células por cor. Mas, é uma pena que ainda não exista uma fórmula que nos permita somar ou contar por cores em uma planilha Excel comum.
Como contar e somar por cor no Excel
Suponha que a gente tenha uma tabela listando os pedidos de sua empresa onde as células na coluna Entrega são coloridas com base em seu valor – as células “Vence em X dias” são laranja, os itens “Entregues” são verdes e os pedidos “Vencidos” são vermelhos.
O que queremos agora é contar automaticamente as células por cor, ou seja, calcular o número de células vermelhas, verdes e laranja na planilha. Como expliquei, não existe uma solução direta para essa tarefa. Mas, para nossa alegria, podemos desenvolver isso no VBA. Então, prossiga com as 5 etapas rápidas abaixo e você saberá o número e a soma de seus células coloridas em poucos minutos.
- Abra sua pasta de trabalho do Excel e pressione Alt + F11 para abrir o Editor do Visual Basic.
- Clique com o botão direito do mouse no nome da pasta de trabalho em “EstaPastaDeTrabalho” na parte direita da tela e escolha Inserir > Módulo no menu de contexto.
3. Adicione o seguinte código na planilha:
Function PegarCorDaCelula(xlRange As Range)
Dim indLinha, indColuna As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColuna = 1 To xlRange.Columns.Count
arResults(indLinha, indColuna) = xlRange(indLinha, indColuna).Interior.Color
Next
Next
PegarCorDaCelula = arResults
Else
PegarCorDaCelula = xlRange.Interior.Color
End If
End Function
Function PegarCorDaFonte(xlRange As Range)
Dim indLinha, indColuna As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indLinha = 1 To xlRange.Rows.Count
For indColuna = 1 To xlRange.Columns.Count
arResults(indLinha, indColuna) = xlRange(indLinha, indColuna).Font.Color
Next
Next
PegarCorDaFonte = arResults
Else
PegarCorDaFonte = xlRange.Font.Color
End If
End Function
Function ContarCelulaPorCor(rData As Range, CorCelulaRfe As Range) As Long
Dim indRefCor As Long
Dim cellAtual As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefCor = CorCelulaRfe.Cells(1, 1).Interior.Color
For Each cellAtual In rData
If indRefCor = cellAtual.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellAtual
ContarCelulaPorCor = cntRes
End Function
Function SomarCelulaPorCor(rData As Range, cellRefCor As Range)
Dim indRefCor As Long
Dim cellAtual As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefCor = cellRefCor.Cells(1, 1).Interior.Color
For Each cellAtual In rData
If indRefCor = cellAtual.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellAtual, sumRes)
End If
Next cellAtual
SomarCelulaPorCor = sumRes
End Function
Function ContarCelulaCorFonte(rData As Range, cellRefCor As Range) As Long
Dim indRefCor As Long
Dim cellAtual As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefCor = cellRefCor.Cells(1, 1).Font.Color
For Each cellAtual In rData
If indRefCor = cellAtual.Font.Color Then
cntRes = cntRes + 1
End If
Next cellAtual
ContarCelulaCorFonte = cntRes
End Function
Function SomarCelulaCorFonte(rData As Range, cellRefCor As Range)
Dim indRefCor As Long
Dim cellAtual As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefCor = cellRefCor.Cells(1, 1).Font.Color
For Each cellAtual In rData
If indRefCor = cellAtual.Font.Color Then
sumRes = WorksheetFunction.Sum(cellAtual, sumRes)
End If
Next cellAtual
SomarCelulaCorFonte = sumRes
End Function
4. Salve sua pasta de trabalho como “Pasta de trabalho habilitada para macro do Excel (.xlsm)”.
5. Escolha a célula onde deseja enviar os resultados e insira a função que acabamos de criar: ContarCelulaPorCor
= ContarCelulaPorCor (intervalo; código de cor)
Neste exemplo, usamos a fórmula = ContarCelulaPorCor ($F$2: $F$14, A17) onde $F$2: $F$14 é o intervalo contendo células codificadas por cores que se deseja contar e A17 é a célula com uma determinada cor de fundo, vermelha no nosso caso .
De forma semelhante, escrevemos a fórmula para as outras cores que desejamos contar, laranja e verde no caso.
Se tiver dados numéricos em células coloridas (por exemplo, a coluna Quatidade no nosso exemplo), podemos adicionar os valores com base em uma determinada cor usando a função análoga: SomarCelulaPorCor
= SomarCelulaPorCor (intervalo; código de cor)
Conforme demonstrado acima, usamos a fórmula = SomarCelulaPorCor ($D$2:$D$14; A17) onde $D$2:$D$14 é o intervalo e A17 é a célula com um padrão de cor.
De maneira semelhante, podemos contar células e somar os valores das células pela cor da fonte usando as funções ContarCelulaCorFonte e SomarCelulaCorFonte:
Nota: Se depois de aplicar o código VBA apresentado acima você precisar colorir mais algumas células manualmente, a soma e a contagem das células coloridas não serão recalculadas automaticamente para refletir as alterações.
Como contar e somar células por cor em toda pasta de trabalho
O código VBA abaixo conta e soma as células de uma determinada cor em todas as planilhas da pasta de trabalho:
Function ContarCelulaPorCorNaPlanilha(cellRefCor As Range)
Dim vWbkRes
Dim PlanilhaAtual As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
vWbkRes = 0
For Each PlanilhaAtual In Worksheets
PlanilhaAtual.Activate
vWbkRes = vWbkRes + ContarCelulaPorCorNaPlanilha(PlanilhaAtual.UsedRange, cellRefCor)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
ContarCelulaPorCorNaPlanilha = vWbkRes
End Function
Function SomarCelulaPorCorNaPlanilha(cellRefCor As Range)
Dim vWbkRes
Dim PlanilhaAtual As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
vWbkRes = 0
For Each wshCurrent In Worksheets
PlanilhaAtual.Activate
vWbkRes = vWbkRes + SomarCelulaPorCorNaPlanilha(PlanilhaAtual.UsedRange, cellRefCor)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
SomarCelulaPorCorNaPlanilha = vWbkRes
End Function
Use essa macro da mesma maneira que usou a anterior e produza a contagem e a soma das células coloridas com a ajuda das seguintes fórmulas, = ContarCelulaPorCorNaPlanilha () e = SomarCelulaPorCorNaPlanilha (). Basta inserir qualquer uma das fórmulas em qualquer célula vazia de qualquer planilha sem definir um intervalo, especificar o endereço de qualquer célula da cor necessária entre colchetes, por exemplo, = SomarCelulaPorCorNaPlanilha (A1), e a fórmula exibirá a soma de todas as células sombreadas com a mesma cor na pasta de trabalho.
Funções personalizadas para obter a cor de fundo de uma célula, cor da fonte e código de cor
Aqui você vai encontrar um resumo de todas as funções que usamos neste exemplo, e outras novas que recuperam códigos de cores.
Observação: lembre-se de que todas essas fórmulas funcionam apenas se você tiver adicionado a função definida pelo usuário à pasta de trabalho do Excel, conforme demonstrado anteriormente neste artigo.
Funções para contar por cor:
- ContarCelulaPorCor (intervalo; código de cor): conta as células com a cor de fundo especificada. No exemplo acima, usamos a seguinte fórmula para contar células por cor = ContarCelulaPorCor ($F$2:$F$14, A17) onde $F$2:$F$14 é o intervalo selecionado e A17 é a célula com a cor de fundo necessária. Você pode usar todas as outras fórmulas listadas abaixo de maneira semelhante.
- ContarCelulaCorFonte (intervalo; código de cor) : conta as células com uma cor de fonte específica.
Fórmulas para somar por cor:
- SomarCelulaPorCor (intervalo; código de cor): calcula a soma das células com uma determinada cor de fundo.
- SomarCelulaCorFonte (intervalo; código de cor): calcula a soma das célulascom uma cor de fonte específica.
Fórmulas para obter o código de cores:
- PegarCorDaFonte (cell): retorna o código de cor da cor da fonte de uma célula específica.
- PegarCorDaCelula (cell): retorna o código de cor da cor de fundo de uma célula específica.
Conclusão
Neste artigo vimos como criar funções no Excel, usando VBA, para contar e somar células coloridas, ou com formatação diferenciada.
Caso tenha interesse em aprender mais VBA, nesse link aqui eu escreve sobre o tema.
O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.
Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.
Portanto, continue lendo mais sobre Dicas de Excel aqui.
Um comentário sobre “Como Contar e Somar Células por Cor de Fundo no Excel”