Veja como realizar cálculos pela cor da célula no Excel como somar, contar, média, ou até retornar as células de uma determinada cor no Excel.
Download Planilha Somar, Contar e Média pela Cor da Célula Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
Como Retornar Somente as Células de Determinada Cor
Para retornar as células de uma determinada cor utilizamos uma função VBA Excel.
A primeira etapa para realizarmos o retorno apenas das células de uma determinada cor você deve habilitar a guia desenvolvedor.
Após isso clique na guia Desenvolvedor e no botão Visual Basic.
Clique em Inserir->Módulo e no módulo incluso coloque o seguinte código VBA:
Public Function gfIntervaloCor(ByVal vInterval As Range, ByVal vColor As Range) As Range
Application.Volatile
Dim vCel As Range
Dim rngResult As Range ' Variável para armazenar o resultado do Union
Set rngResult = Nothing ' Inicializa a variável rngResult
For Each vCel In vInterval.Cells
If CLng(vCel.Interior.Color) = vColor.Interior.Color Then
If rngResult Is Nothing Then
' Se rngResult ainda não estiver definido, define-o como a primeira célula encontrada
Set rngResult = vCel
Else
' Se rngResult já estiver definido, adiciona a célula atual a ele usando Union
Set rngResult = Union(rngResult, vCel)
End If
End If
Next vCel
Set gfIntervaloCor = rngResult ' Retorna o resultado do Union
End Function
Após isso a função já estará pronta para uso.
Como Consultar as Células por Determinada Cor
Para consultar e retornar as células pela cor da célula você pode usar o Excel 365.
Para isso usamos a seguinte fórmula:
=SEERRO(QUEBRARLINS(PARACOL(gfIntervaloCor(D5:D834;J5));7);””)
A função gfIntervaloCor retorna todas as células que estão no intervalo entre D5 e D834 que tenham a cor definida na célula J7.
Para que retorne a matriz de dado usamos a função PARACOL e em seguida quebramos o retorno em várias linhas usando a função QuebrarLins.
Por fim usamos a função SEERRO que retorna vazio para os dados não preenchidos em uma das células da matriz.
Contar pela Cor da Célula Excel
Para contar pela cor da célula usamos a função CONT.VALORES.
A função utilizada será: =CONT.VALORES(gfIntervaloCor($D$5:$D$834;$J5))
Perceba que simplesmente passamos para a função CONT.VALORES a contagem de itens usando a função gfIntervaloCor que retorna somente os valores das células preenchidas com a determinada cor.
Somar pela Cor da Célula Excel
A função gfIntervaloCor sempre retorna as células de determinada cor.
Por isso pode ser usada para vários cálculos
Para somar as células de determinada cor use: =SOMA(gfIntervaloCor($D$5:$D$834;$J5))
Com isso o retorno dos dados da função gfIntervaloCor será somado pela função SOMA.
Com isso temos então a soma de todas as células de determinada cor.
Lembrando que se mudar a cor ele não irá recalcular de imediato, mudará apenas se alterar um valor ou então pressionar F9.
Média pela Cor da Célula Excel
Com isso podemos também retornar a média de valores pela cor da célula.
Veja que no cálculo abaixo temos o uso da função gfIntervaloCor para retornar as células com determinada cor e em seguida usamos a função MÉDIA que retorna a média de valores de determinada cor.
Com isso temos as médias dos valores à partir do intervalo de cores e imagens.
Resumo
Como pode notar o uso da função gfIntervaloCor retorna os valores de células de determinada cor e com isso podemos realizar cálculos à partir do intervalo selecionado.
Download Planilha Somar, Contar e Média pela Cor da Célula Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
