Contar, Somar e Média pela cor da célula no Excel

Neste artigo você verá como contar, somar e fazer a média pela cor da célula no Excel.

O Excel é uma ferramenta extraordinária, mesmo funções que não existem é possível criar muitas vezes, como é o caso desta situação.

Nela criamos uma UDF, ou seja User Defined Function para realizar operações pela cor da célula no Excel.

Veja nesta imagem como ela funciona de forma semelhante ás funções SOMA, MÉDIA E CONTAR padrão do Excel, mas considerando apenas as células de uma determinada cor:

Veja que a função utilizada é a gfTotalCor, que é a uma função que criamos em VBA e que pode pode ser utilizada em uma planilha específica ou podemos colocá-la em um suplemento do Excel e utilizá-la em qualquer planilha.

Atenção, ela só efetua os cálculos para células efetivamente com o fundo colorido, não efetua cálculos em formatações condicionais que tenham cores de fundo, pois a formatação condicional não colore efetivamente a célula, apenas o faz logicamente. Nestes casos use a SOMASE ou SOMASES com as regras que aplicou na formatação condicional.

Há algum tempo fiz duas funções para somar Somar cor da célula e Contar cor da célula, mas esta ficou muito mais completa e atual, use esta deste artigo.

Funcionamento da função

A sintaxe da função é:

Padrão:    gfTotalCor(Operação;Intervalo;Célula de cor de referência)

Exemplo: =gfTotalCor(1;$D$8:$D$837;J8) aonde temos os parâmetros

Operação – 1 -> O primeiro parâmetro define qual a função que será realizada, se SOMA, CONTAR ou MÉDIA. 1 para contar, 2 para somar e 3 para média.

Célula de cor de referência – Intervalo – $D$8:$D$837 -> Intervalo aonde será executada a função, não selecione a coluna inteira, porque é necessário fazer loop para realizar as operações, por isso ficará muito lento se selecionar a coluna inteira. De preferência use uma tabela no Excel, assim ao incluir dados ela se auto-ajustará.

J8 -> Uma célula de referência que possui a cor de fundo que deseja realizar as operações.

Incluindo a Função no Excel

Para incluir uma função feita em VBA no Excel que possa ser utilizada na planilha abra o Excel e habilite a guia Desenvolvedor, siga estes procedimentos: Habilitar guia desenvolvedor e copiar funções próprias, UDF da internet.

Mas copie o código abaixo no lugar do código que está no link citado acima.

Option Explicit

'Realiza operações de contagem, soma e média pela cor da célula
Public Function gfTotalCor(ByVal vTipo As Integer, ByVal vInterval As Range, ByVal vColor As Range) As Double
    'Recalcula a função em qualquer alteração da planilha
    Application.Volatile
    
    Dim vCel    As Range
    Dim vContar As Double
    Dim vSomar  As Double
    
    For Each vCel In vInterval.Cells
        If CLng(vCel.Interior.Color) = vColor.Interior.Color Then
            Select Case vTipo
                'Contar
                Case 1
                    vContar = vContar + 1
                'Somar
                Case 2
                    vSomar = vSomar + vCel.Value2
                'Média
                Case 3
                    vContar = vContar + 1
                    vSomar = vSomar + vCel.Value2
            End Select
        End If
    Next vCel
    
    If vTipo = 3 Then
        gfTotalCor = vSomar / vContar
    Else
        gfTotalCor = vSomar + vContar
    End If
End Function

Veja no vídeo com detalhes como funciona o código VBA, também como colocar ela em uma planilha ou como suplemento do Excel, além de como utilizá-la.

Faça o download da planilha com o exemplo.

Baixe a planilha

Abraço
Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: