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

procv - Guia do Excel
Procv contém – Procv com uso de coringas * ? contém
30 de outubro de 2017
Planilha - Guia do Excel
Solver e VBA no Excel – Automatizar a solução de problemas
13 de novembro de 2017

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:

Contar somar média pela cor da célula no Excel

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.

Abraço
Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Básico – 1 ano de acesso

R$99,00

COMPRAR
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR

8 Comentários

  1. Cláudio disse:

    Boa tarde Marcos!

    Será que teria como melhorar a função para que encontra-se por cor também o valor máximo, mínimo e também o segundo maior ou terceiro menor, ou qualquer maior ou menor usando parametrização no maior e menor na própria função gfTotalCor?

    Gostaria muito de saber se tem como?

  2. Nilton disse:

    Amigo fiz um cadastro no forum para poder acompanhar melhor o site, mas não recebo o e-mail de confirmação.

  3. Bruno Ziliotto disse:

    Não sei se ainda lêem esses coments, mas lá vai a minha dúvida.
    Não quero apenas que o Excel conte as células de determinada cor, preciso que ele conte as células de determinada cor que contenham uma outra condição, como o texto “x” junto.
    Essa função aí é simples, funcionou bem, mas neste caso ele está contando todas as células de determinada cor, mas não está selecionando aquelas que eu quero que ele conte conforme o texto que tem nela. Tem jeito de fazer isso?

    Obrigado!!
    Bruno

  4. Riellys Cacilhas disse:

    Ola Marcus minha Duvida e a mesma do Bruno. vou te dar um exemplo:
    Criei um cronograma com duas cores ( verde e vermelho). O verde representa oque cada parceiro (nome), plantou de cana, ja em vermeho representa oq cara parceiro tratou de solo. no final do cronograma, fiz um somatorio para cada ( plantio e preparo de solo) de acordo com as cores correspondente. a formulo funcional perfeitamente. depois eu fiz um resumo, onde eu tenho dois Criterios; pegar o nome de cada parceiro e saber qual foi a quantidade que cada um Plantou e preparou o solo. eu teria que ter uma fomula mais ou menos assim:

    “João (resumo)= João (intervalo cronograma); somar as celulas verdes (plantio) de joão”

    Entendeu, consegue me ajudar

  5. Marcelo de Castro Pagnon disse:

    Gostaria de Saber como contar duas células mesclas uma, em vez de duas na contagem de cores, pois quando mesclo duas células na função ele conta como duas em vez de uma. Teriam alguma solução para isso?
    Grato

  6. Ana disse:

    boa Tarde. Entendi perfeitamente. Ótima explicação. Mas não está alterando automaticamente. Isto é, se depois de inserir a função eu preencho mais uma célula com a cor AMARELA, por exemplo, ele só soma se eu clicar novamente na célula onde se encontra a fórmula de soma. Tem como alterar isso?

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.