SOMASE e CONT.SE pela cor da célula ou da fonte

As funções SOMA, SOMASE e SOMASES conseguem dar cor de boa parte dos nossos problemas com soma de células no Excel. Mas se tem uma coisa que faz falta é conseguir somar as células com base nas suas cores de preenchimento ou de texto, e aqui você vai aprender a como fazer um SOMASE e CONT.SE pela cor da célula ou da fonte.

Antes de continuar, que tal dar uma conferida no artigo Funções de Soma e conhecer as 3 principais funções de soma do Excel? Clique aqui para conferir. Ou, se preferir, clique aqui para conhecer as 5 funções de contagem mais utilizadas no Excel.

Não deixe de ler o ‘ATENÇÃO!!!’ no final deste artigo

---

Função Definida pelo Usuário (UDF)

Como não existe um função pronta no Excel que leve em consideração a formatação da célula ou seu conteúdo, para resolver o problema iremos criar duas funções, as chamadas User Defined Functions (UDF). Para saber um pouco mais sobre as UDFs, clique aqui.

Uma delas fara as somas e a chamaremos de SOMACOR e a outra servirá para fazer a contagem, e a chamaremos de CONTCOR. Poderíamos criar tudo em uma mesma função, mas para ficar mais didático e fácil na hora de utilizar vamos deixar tudo separado mesmo.

---

Mais do que palavras…

Para facilitar todo o entendimento e funcionamento das funções que criaremos, sugiro que assista ao vídeo abaixo, embora não seja crucial, ele ajuda bastante.

Coloque o vídeo em tela cheia para assistir normalmente

---

Função SOMACOR

Abra o editor VBA (Alt + F11) e no menu à esquerda clique com o botão direito em um espaço livre e selecione Inserir > Módulo.

Abra o módulo recém inserido e cole na parte direita da tela o código abaixo.

Function SOMACOR(Referência As Range, Matriz As Range, Fonte As Boolean)
 Application.Volatile
 
 Dim rCell As Range
 Dim rCor As Long
 Dim rResult As Variant
 
 If Fonte = False Then
 rCor = Referência.Interior.ColorIndex
 For Each rCell In Matriz
 If rCell.Interior.ColorIndex = rCor Then
 rResult = WorksheetFunction.SUM(rCell, rResult)
 End If
 Next rCell
 Else
 rCor = Referência.Font.ColorIndex
 For Each rCell In Matriz
 If rCell.Font.ColorIndex = rCor Then
 rResult = WorksheetFunction.SUM(rCell, rResult)
 End If
 Next rCell
 End If
 
 SOMACOR = rResult
End Function

Com isso a função SOMACOR já está criada e pronta para ser usada. (Caso queira entender todos os passos do código, assista o vídeo acima).

Esta função possui 3 argumentos:

SOMACOR(Referência; Matriz; Fonte)

Onde:

Referência = Uma célula qualquer cujas cores de fundo ou texto (fonte) serão utilizadas como referência ou critério para definir quais células deverão ser somadas.

Matriz = É uma área, conjunto de células, que terão suas cores de fundo ou texto (fonte) avaliados, caso sejam iguais ao da célula de Referência então entraram na soma.

Fonte = É um argumento de tipo VERDADEIRO ou FALSO. Caso seja FALSO, a soma será feita com base na cor de fundo da célula de Referência. Caso seja VERDADEIRO, a soma será feita com base na cor da fonte da célula de Referência.

Ela deve ser utilizada exatamente como qualquer outra função comum do Excel.

---

Função CONTCOR

Siga os mesmo passos descritos para a função SOMACOR acima, porém dessa vez não há necessidade de criar um novo módulo, apenas insira no editor VBA, no mesmo módulo que o anterior, o seguinte código:

Function CONTCOR(Referência As Range, Matriz As Range, Fonte As Boolean)
 Application.Volatile
 
 Dim rCell As Range
 Dim rCor As Long
 Dim rResult As Variant
 
 If Fonte = False Then
 rCor = Referência.Interior.ColorIndex
 For Each rCell In Matriz
 If rCell.Interior.ColorIndex = rCor Then
 rResult = 1 + rResult
 End If
 Next rCell
 Else
 rCor = Referência.Font.ColorIndex
 For Each rCell In Matriz
 If rCell.Font.ColorIndex = rCor Then
 rResult = 1 + rResult
 End If
 Next rCell
 End If
 
 CONTCOR = rResult
End Function

Com isso a função CONTCOR já está criada e pronta para ser usada. (Caso queira entender todos os passos do código, assista o vídeo acima).

O funcionamento desta é o mesmo que o descrito para a função SOMACOR, com os exatos mesmo argumentos. Mas de qualquer forma, seguem os detalhes.

CONTCOR(Referência; Matriz; Fonte)

Onde:

Referência = Uma célula qualquer cujas cores de fundo ou texto (fonte) serão utilizadas como referência ou critério para definir quais células deverão ser contadas.

Matriz = É uma área, conjunto de células, que terão suas cores de fundo ou texto (fonte) avaliados, caso sejam iguais ao da célula de Referência então entraram na contagem.

Fonte = É um argumento de tipo VERDADEIRO ou FALSO. Caso seja FALSO, a contagem será feita com base na cor de fundo da célula de Referência. Caso seja VERDADEIRO, a contagem será feita com base na cor da fonte da célula de Referência.

Ela deve ser utilizada exatamente como qualquer outra função comum do Excel.

---

Exemplo prático

A imagem abaixo apresenta as células que iremos usar de exemplo para somar/contar com base em sua formatação.

Usaremos neste exemplo, sempre a célula B4 como a célula de Referência para as cores de fundo e fonte. Assim para cada uma das células do intervalo D4:G4 inseriremos as seguintes fórmulas:

D4 =SOMACOR(B4;B4:B10;FALSO)

Soma as células do intervalo B4:B10 cuja cor de fundo é a mesma que a da célula B4.

E4 =SOMACOR(B4;B4:B10;VERDADEIRO)

Soma as células do intervalo B4:B10 cuja cor da fonte é a mesma que a da célula B4.

F4 =CONTCOR(B4;B4:B10;FALSO)

Conta as células do intervalo B4:B10 cuja cor de fundo é a mesma que a da célula B4.

G4 =CONTCOR(B4;B4:B10;VERDADEIRO)

Conta as células do intervalo B4:B10 cuja cor da fonte é a mesma que a da célula B4.

E eis o resultado:

---

ATENÇÃO!!!

Existem alguns pontos que devem ser observados sobre estas duas funções.

• Elas não funcionam com formatação condicional, logo a cor de preenchimento ou fonte que elas avaliam é a cor real da célula e não a utilizada por formatação condicional. Isso não é necessariamente um problema, já que caso seu intuito é fazer uma soma/contagem com base em formatação condicional, então basta fazer um SOMASE ou CONT.SE normal usando como critério os mesmo utilizado na formatação condicional.
• Elas não se atualizam quando uma célula muda de cor. Isso acontece porque o Excel não interpreta a mudança de formatação como um evento de cálculo, para ele é como se a planilha não tivesse sofrido alterações e logo nenhuma de suas fórmulas precisa ser recalculada. Para que estas funções sejam recalculadas você pode (1) alterar qualquer célula do arquivo; (2) Apertar F2 e depois ENTER em qualquer célula do arquivo; (3) Apertar F9; (4) realizar qualquer evento que leve o Excel a recalcular suas fórmulas.
• Você irá notar que estas funções não apresentam os balões de ajuda na hora em que se está digitando elas, como normalmente acontece quando digitamos uma outra função qualquer do Excel. Infelizmente não há uma solução prática e simples para tal, UDFs não possuem este recurso nativo no Excel. Uma forma de pelo menos ler o nome de cada argumento das função, sem entrar no editor VBA, é apertar as teclas CTRL + ALT + A quando se estiver escrevendo essas funções.
• Não aconselho selecionar grande áreas para o argumento Matriz. Como é feito um teste de verificação de cor para cada uma das células da Matriz, selecionar uma área muito grande pode deixar as coisas lentas. Sugiro que o intervalo seja reduzido ou expansível com a ajuda da função INDIRETO, o que tornaria possível deixar o intervalo de avaliação exatamente do tamanho necessário. Para os exemplos demonstrados aqui, a Matriz posta como B4:B10 poderia ser substituída por: INDIRETO(“B4:B”&CONT.VALORES(B:B)+4). Isso faria com que, caso fossem inseridos mais valores abaixo de B10, o intervalo de critério cresceria junto e, caso fossem removidos dados de forma que os valores terminasse na célula B5, por exemplo, o intervalo de avaliação também diminuiria automaticamente.

---

Acompanhe o Função Excel