Visual Basic em Português

Página pessoal de Jorge Paulino sobre o Visual Basic (VB.NET, ASP.NET, VB6, VBA) e algumas noticias de tecnologia

Excel: Contar Cores na Formatação Condicional

Num artigo anterior, foi mostrado como Contar Cores das Células, de acordo com a indicação da área (range) e o código do cor. Esta função, embora simples, é muito útil, funcionando no entanto apenas se o utilizador aplicar a cor manualmente e não com o uso de formatação condicional. 
 
Com a formatação condicional podemos criar várias formatações para a mesma área, interferindo em diferentes aspectos (fonte, preenchimento, etc), ficando a formatação base inalterável. O que acontece é que o Excel cria camadas nas células, definidas na formatação, ficando a informação original preservada. Por exemplo, se tivermos uma tabela com todas as células com a cor de fundo verde e se a formatação condicional colocar algumas células com a cor de fundo azul, na prática a cor de fundo de todas as células é verde e existe uma camada azul por cima das células que correspondem ao critério definido para a formatação condicional.
 
Além disso, a versão 2007 ou superior do Excel, sofreu um conjunto de alterações na formatação condicional, sendo agora possível utilizar ícones, shapes, tops, etc, definidos pelo tipo XlFormatConditionType, o que pode obrigar a mais algumas validações (não será no entanto abordado neste artigo)
 
Em termos práticos não é possível verificar directamente se a formatação está aplicada ou não, o que é possível verificar são os critérios da formatação condicional e com isso, validarmos se a célula corresponde a esses critérios ou não.
 
Neste exemplo, vamos verificar se os valores das células (xlCellValue) correspondem a um critério definido (maior do que, menor do que, entre, etc), definidos pelo tipo XlFormatConditionOperator, e se corresponderem, então a formatação condicional está visível e usamos essa informação para contar, somar, etc.


Para começar esta pequena função auxiliar, que serve simplesmente para remover o sinal de igual e garantir que o resultado final é numérico.

  ' Limpa o sinal de igual da fórmula
  Public Function ClearValue(ByVal value As String) As Double
    Dim tempValue As String

    On Error Resume Next

    tempValue = Replace(value, "=", "")

    If IsNumeric(tempValue) Then
      ClearValue = CDbl(tempValue)
    Else
      ClearValue = 0
    End If

  End Function

Depois, a função GetFormatConditionIndex() que verifica se a célula corresponde ao critério definido na formatação condicional, retornando 0 caso não corresponda e o número da formatação, caso corresponda (como já foi dito uma célula pode ter diversas formatações condicionais).

  Public Function GetFormatConditionIndex(ByVal rng As Range) As Integer
    On Error GoTo errorGetFormatConditionIndex

    ' Caso não tenha qualquer formatação condicional
    If rng.FormatConditions.Count = 0 Then
      Exit Function
    Else

      Dim x As Long
      Dim cf As FormatCondition

      ' Ciclo nas formatações condicionais atribuidas
      For x = 1 To rng.FormatConditions.Count

        ' Verifica qual a FormatConditions actual
        Set cf = rng.FormatConditions(x)

        ' Verifica o tipo de formatação
        If cf.Type = xlCellValue Then

          ' Verifica se algumas da opções escolhidas para a
          ' formatação condicional é valida, ou seja, está visivel
          Select Case cf.Operator

            Case xlGreater
              If rng.value > ClearValue(cf.Formula1) Then
                GetFormatConditionIndex = x
              End If
            Case xlLess
              If rng.value < ClearValue(cf.Formula1) Then
                GetFormatConditionIndex = x
              End If
            Case xlEqual
              If rng.value = ClearValue(cf.Formula1) Then
                GetFormatConditionIndex = x
              End If
            Case xlNotEqual
              If rng.value <> ClearValue(cf.Formula1) Then
                GetFormatConditionIndex = x
              End If
            Case xlGreaterEqual
              If rng.value >= ClearValue(cf.Formula1) Then
                GetFormatConditionIndex = x
              End If
            Case xlLessEqual
              If rng.value <= ClearValue(cf.Formula1) Then
                GetFormatConditionIndex = x
              End If
            Case xlBetween
              If rng.value >= ClearValue(cf.Formula1) And _
                 rng.value <= ClearValue(cf.Formula2) Then
                GetFormatConditionIndex = x
              End If
            Case xlNotBetween
              If rng.value < ClearValue(cf.Formula1) Or _
                 rng.value > ClearValue(cf.Formula2) Then
                GetFormatConditionIndex = x
              End If
          End Select

        End If

        ' Caso tenha encontrada uma opção válida
        If GetFormatConditionIndex > 0 Then Exit Function

      Next

      ' Caso não encontre retorna 0
      GetFormatConditionIndex = 0

    End If

    Exit Function
errorGetFormatConditionIndex:

    ' Em caso de erro retorna 0
    GetFormatConditionIndex = 0

  End Function

Finalmente as funções que contam (neste caso podendo ser adaptado para outro objectivo), o número de células com a fonte igual a um determinado número (ColorIndex), com ou sem formatação condicional:

  Public Function CountFontColor( _
              ByVal rng As Range, _
              ByVal ColorIndex As Integer, _
              Optional ByVal ConditionalFormat As Boolean = False) As Long

    Dim r As Range
    Dim result As Integer

    On Error GoTo errorCountFontColor

    ' Verifica na formatação condicional
    If ConditionalFormat Then
      For Each r In rng

        Dim cfIndex As Integer
        cfIndex = GetFormatConditionIndex(r)

        If cfIndex > 0 Then
          Dim temp As FormatCondition
          Set temp = r.FormatConditions(cfIndex)
          If temp.Font.ColorIndex = ColorIndex Then
            result = result + 1
          End If
        End If

      Next
    Else
      For Each r In rng
        If r.Font.ColorIndex = ColorIndex Then
          result = result + 1
        End If
      Next
    End If

    CountFontColor = result

    Exit Function
errorCountFontColor:
    CountFontColor = 0

  End Function

E a função que conta o número de células com a fundo (background) igual a um determinado número (ColorIndex), com ou sem formatação condicional:

  Public Function CountFillColor( _
            ByVal rng As Range, _
            ByVal ColorIndex As Integer, _
            Optional ByVal ConditionalFormat As Boolean = False) As Long

    Dim r As Range
    Dim result As Integer

    On Error GoTo errorCountFillColor

    ' Verifica na formatação condicional
    If ConditionalFormat Then
      For Each r In rng

        Dim cfIndex As Integer
        cfIndex = GetFormatConditionIndex(r)

        If cfIndex > 0 Then
          Dim temp As FormatCondition
          Set temp = r.FormatConditions(cfIndex)
          If temp.Interior.ColorIndex = ColorIndex Then
            result = result + 1
          End If
        End If

      Next
    Else
      For Each r In rng
        If r.Interior.ColorIndex = ColorIndex Then
          result = result + 1
        End If
      Next
    End If

    CountFillColor = result

    Exit Function
errorCountFillColor:
    CountFillColor = 0

  End Function

 

Com estas funções, podemos facilmente usar em qualquer célula:

=CountFontColor(A1:A10;3;TRUE)
=CountFillColor(A1:A10;3;TRUE)

Onde A1:A10 é a área a verificar, 3 o número da cor(neste caso vermelho) e TRUE indicando que a verificação é na formatação condicional. Para não ser verificada na formatação condicional podemos fazer:

=CountFontColor(A1:A10;3)
=CountFillColor(A1:A10;3)

As cores, são definidas na propriedade ColorIndex (podem ser mais detalhes e a palete de cores neste link)

NOTA: Embora este artigo refira as versões 2007 e superior do Excel, também funciona em versões mais antigas.

Espero que este artigo vos seja útil!

11 comentários:

Anónimo disse...

Brutal!!! Vou ser se consigo utilizar estas fórmulas. Obrigado :)

Anónimo disse...

Quando vamos inserir a função, é pedido 3 informações: range, colorIndex e FormatCond. O que devo escrever na FormatCond?

Anónimo disse...

Boa tarde,

Procurei em vários sites em inglês por essa fórmula e este artigo serviu exatamente para o que estava precisando.
Precisava de contagem de cores somente no texto das células, através da formatação condicional.


Valeu

Franklyn disse...

Goestei não adorei más não minha planilha em formatacao condicional está dando erro da dando valor
o q pode ser? se poder mim ajudar fico muito grato amigo.

Franklyn disse...
Este comentário foi removido pelo autor.
Anónimo disse...

Boa tarde,

e caso eu queira somar o que está nas celulas coloridas ao invés de contar o numero?

Alberto Cesar Silva disse...

Bom dia.

fiz todo o procedimento em uma formatação condicional de 08 sintaxes, porém a resposta não sai nem a contagem de células e nem a soma das mesmas, tem somente #NOME?, não consigo descobrir o erro, já copiei e colei direto da aula e não deu certo.

Ana disse...

a fórmula está retornando o número total de células em que apliquei a formatação condicional e não apenas as que atendem as condições e estão marcadas em vermelho, por exemplo.

wiharlley disse...

Bom dia!

Existe alguma formula para conta pela cor dos ícones?

wiharlley disse...

Boa tarde!

Galera!

tenho uma tabela de controle de materiais, em uma coluna inserir ícones da formação condicional para informar "verde=ok, amarelho=alerta e vermelho estoque baixo ou zerado".


A pergunta é existe alguma formula ou macro para contar as cores destes ícones?

Meu e-mail e wiharlley@gmail.com

Carlos Lavrador disse...

Boa tarde,
Segui os passos para contar as células com cor por meio da formatação condicional, mas devolve-me na célula "#VALOR".
Utilizo o Excel 2013, onde poderá estar o erro?
Obrigado
Carlos (caipira69@gmail.com)

Mensagens Recentes



Microsoft Office Especialist

Membro da Comunidade
Experts-Exchange


Administ. da Comunidade
Portugal-a-Programar



Twitter

Artigos no CodeProject

Artigos no CodeProject

Subscrever Novidades

Endereço de Email:

Delivered by FeedBurner

Seguidores

Histórico