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 das Célula

Muitas vezes utilizam-se no Excel cores para identificar diferentes prioridades ou estados de terminados pontos/itens. Essas identificações são preciosas ajudas na visualização mas podem também ser quantificadas para uma melhor contabilização ou para uma visualização gráfica.

No entanto, esta possibilidade apenas pode ser conseguida como recurso a VBA (Visual Basic for Applications), uma vez que as fórmulas disponíveis no Excel não o permitem.

Para adicionarmos esta função a uma folha de Excel devemos fazer o seguinte:

1 – Menu Tools – Macros – Visual Basic Editor ou ALT+F11
2 – Adicionar um novo módulo clicando com o botão direito ou através do menu Insert (Figura 1)


(Figura 1) 

3 – Colocar a seguinte função no módulo criado

' -------------------------------------------------------------------------
' Função que irá retornar o número de cores encontrada numa área

  (deverá ser inserido a área e o código da cor)
' -------------------------------------------------------------------------
Public Function CountColors(rng As Range, color As Integer) As Integer
Dim rg As Range
Dim x As Integer

' Valor inicial
CountColors = 0

    ' Ciclo que irá percorrer todas as células definidas
    For Each rg In rng   

      ' Caso a cor interior (background) seja a escolhida
      If rg.Interior.ColorIndex = color Then

           ' Incrementa o contador
           x = x + 1          

      End If

    Next

    ' Define que a função (valor a retornar) tem o valor de x
    CountColors = x 

End Function

4 – Fechar o Editor de VBA e na folha de cálculo utilizar a função criada. No exemplo (Figura 2) deverá ser inserido o seguinte:

(Figura 2)

Célula C12: =CountColors(C3:C10;4)
Célula C13: =CountColors(C3:C10;19)
Célula C14: =CountColors(C3:C10;46)

O índice de cores do Microsoft Excel 2003 (ColorIndex) é composto por 56 cores (1 - 56) e podem verificar os códigos e correspondência de cores na imagem seguinte (Figura 3)

(Figura 3)

PS: Como sempre, qualquer dúvida, comentário ou correcção ao artigo é sempre bem vinda!

44 comentários:

fdomingos disse...

Muito útil, por acaso nunca pensei que o Excel permitisse a contagem das cores das células.

Obrigado!

LWA disse...

Show de bola este recurso, preciso contar as cores geradas pelo formato condicional, é possivel? apliquei esta função só que não recomeçeu qdo a cor é gerada por formatação condicional!

desde já Obrigado
At.LWA

jpaulino disse...

Olá LWA,
Sim é possível mas é um pouco mais complexo. Se quiser envie-me um email para vbtuga(at)gmail.com que eu respondo com uma solução.

Olá Fábio e obrigado pelo comentário!

Anónimo disse...

Quero saber se há como fazer esta fórmula pelo excel mesmo, sem utilizar o Visual basic?

Obrigada

jpaulino disse...

»» Quero saber se há como fazer esta fórmula pelo excel mesmo, sem utilizar o Visual basic?

Não, sem a utilização de Visual Basic (VBA) não é possível.

Bárbara disse...

E se eu quiser somar o valor representado pela cor?
o código muda, não é?
como seria?

Bárbara

jpaulino disse...

>> E se eu quiser somar o valor representado pela cor? o código muda, não é? como seria?

Basicamente é apenas necessário alterar no incremento do contador para x = x + rg.Value, ao invés de x = x + 1.

É obvio que se deve validar se o valor é numérico, usando a função IsNumeric(), e alterar o nome da função para algo mais ajustado.

Thiago disse...

Cara, não manjo nada de VB, mas consegui fazer.

Mas quando vou fazer o lance que soma, não dá certo, e eu não sei usar essa IsNumeric()... tem como me ajudar??

meu mail é libbos(at)gmail.com

obrigado!! abraço!

Anónimo disse...

A minha dúvida prende-se com o recálculo automático da função, isto é, como fazer com que o Excel recalcule a funcão quando se alteram os dados, neste caso, as cores.

Muito obrigado e bem haja.

jpaulino disse...

Olá,

Não entendo a questão! A formula calcula automaticamente caso exista alguma alteração.

Anónimo disse...

Olá outra vez,

Peço desculpa pela maçada.
Não sei se as configurações do meu Excel foram alteradas, mas de cada vez que altero as cores, tenho que editar a fórmula e terminar com Enter.

jpaulino disse...

Veja se as formulas estão em automático (Tools - Options - Calculation)

Anónimo disse...

O cálculo está em automático.
Pesquisando na Net encontrei a seguinte instrução a colocar no inicio da função, mas tb não dá.

Application.Volatile

jpaulino disse...

Sugiro que coloque uma questão no fórum onde sou moderador http://www.portugal-a-programar.org/forum/index.php/board,226.0.html e se possível mostre o ficheiro (deve remover informação confidêncial)

Pode ser algo muito simples mas só mesmo analisando.

Anónimo disse...

Gostei da função apresentada, apesar de estar com o mesmo problema em relação ao calculo automático das formulas.

E como posso somar as células que estao sem preenchimento? Qual é o código que coloco no ultimo argumento da formula?

Obrigado.

Física - E.E.C.A./Itanhomi disse...

PESSOAL SOU NOVO POR AQUI!
MAS FIQUEI CURIOSO EM SABER COMO FARIA PARA CONTAR CÉLULAS VERMELHAS DO INTERVALOR K10:Q15 ,PORÉM TEM QUER SER CÉLULAS VERMELHAS COM VALORES MAIORES OU IGUAIS AO VALOR DA CÉLULA L62 E MENORES OU IGUAIS AO VALOR DA CÉLULA M62.

UM ABRAÇO

RANGEL

jpaulino disse...

Olá,

E como posso somar as células que estao sem preenchimento? Qual é o código que coloco no ultimo argumento da formula?


Quer dizer sem cor ? Transparente ?


MAS FIQUEI CURIOSO EM SABER COMO FARIA PARA CONTAR CÉLULAS VERMELHAS DO INTERVALOR K10:Q15 ,PORÉM TEM QUER SER CÉLULAS VERMELHAS COM VALORES MAIORES OU IGUAIS AO VALOR DA CÉLULA L62 E MENORES OU IGUAIS AO VALOR DA CÉLULA M62.


É só acrescentar no código essa condição!

Mais ou menos: (não testado)

If rg.Interior.ColorIndex = color And (rg.Value >= [L62].Value Or rg.Value <= [M62].Value) Then



Espero que ajude!

Física - E.E.C.A./Itanhomi disse...

JPAULINHO, MAS ASSIM MEU CALENDÁRIO ESCOLAR QUE ESTÁ CONTANDO OS DIAS LETIVOS DE CADA MÊS POR CORES DE CÉLULAS PÁRA DE FUNCIONAR.
O MEU PEDIDO SERIA UM COMPLEMENTO AO MEU CALENDÁRIO PARA COMPUTAR OS DIAS LETIVOS POR BIMESTRE. POR EXEMPLO :

O PRIMEIRO BIMESTRE COMEÇA EM 01/02/2010 (L62) E TERMINA EM 20/04/2010(M62).

DEPOIS VEM AS DATAS DE INÍCIO E TÉRMINO DO 2 BIMESTRE (L63) E (M63), DEPOIS 3 BIMESTRE E DEPOIS BIMESTRE 4.
O QUE EU PRECISO É UMA FORMA DE ESPECIFICAR NO CÓDIGO UMA MANEIRA DE CONTAR CORES RELACIONADA A CONDIÇÃO DOS NÚMEROS QUE ESTÃO DENTRO DELAS. OU SEJA PRECISO INSERIR NA FUNÇÃO A LÓGICA QUE CONTE AS CORES "X" NO INTERVALO [a,b]que cujas células tem datas acima de um valor (variável)e abaixo de outro valor variável)

Obrigado,

rangel

Anónimo disse...

>>>>E como posso somar as células que estao sem preenchimento? Qual é o código que coloco no ultimo argumento da formula?


>>>>Quer dizer sem cor ? Transparente ?

Exactamente isso. Como conto células que podem conter valores, mas não tem nenhuma cor do fundo.
Pretendo é que a função me dê a soma das células que não estão preenchidas, não a soma dos valores!

Tenho uma Tabela(tipo calendário) Alguns dias tem cor de fundo e outras não, mas todas contem valores.
Como faço só para somar as células transparentes ou sem preenchimento.

Obrigado

Jorge Paulino disse...

Olá,

Para contar as células sem cor é só indicar o código -4142.

Exemplo:

=CountColors(A1:A100;-4142)


Espero que ajude!

Anónimo disse...

Obrigado pela dica sobre contar células sem preenchimento.

Um Abraço

Anónimo disse...

quando fecho o editor ele salva com o nome da planilha e não com o nome da função e daí qdo peço função definida pelo usuário não dá certo

Anónimo disse...

deu-me jeito!
Obrigado

Anónimo disse...

Muito bom...
Parabéns e obrigado.

Sugestão: Tabela das cores com os códigos das cores para as macros, seria fantástico.

Anónimo disse...

Gostaria de saber se esta forma também se pode aplicar aos caracteres cloridos, de cores diferentes

Jorge Paulino disse...

Olá,

Sim, pode-se fazer um ciclo nos caracteres e ver a cor que ele tem, usando Range("a1").Characters

Anónimo disse...

Essa fórmula não dei certo no excel 2010. Nãoconsigo achar o porque.

Jorge Paulino disse...

Sem mostrar o que tem ou mais detalhes, ninguém consegue adivinhar!

Anónimo disse...

aqui funcionou perfeito!!

OBRIGADO!

Anónimo disse...

Eu fiz a fórmula e deu certo, porém a minha planilha é filtrada, então quando seleciono uma informção para ser filtrada toda essa formatação some...teria como usar uma fórmula para que toda vez eu utilize o filtro ela NÃO SUMA...

Jorge Paulino disse...

Não estará o filtro a contemplar também a formula e por isso ela desaparece?

Dê lá mais detalhes.

Rah e Paty ' feche os olhos e imagine disse...
Este comentário foi removido pelo autor.
Patricia Levorato disse...

Boa Tarde Jorge,

Primeiramente obrigada por disbonibilizar tal conteudo, que já me ajudou bastante, porém estou com um probleminha:

Eu gostaria que a soma fosse "atualizada" a cada nova cor, pois no jeito atual, a formula só conta inicialmente os dados, e ao inserir novos preenchimentos de cores, ela não atualiza.

Vc pode por gentileza me ajudar?!

Obrigada

Jorge Paulino disse...

Olá Patricia,

Quando colocamos uma cor ou formatamos uma célula, nenhum evento é executado o que não permite fazer isso :(

A unica forma é actualizar quando mudar de célula, usando o evento Worksheet_SelectionChange, mas ainda assim isso implica mudar de célula e pode tornar o ficheiro lento/pesado.

Não há uma forma simples de o fazer!

Patricia Levorato disse...

Olá Jorge,

No caso, como poderia aplicar esse evento? Worksheet ?

É muito complicado? Quero ao menos tentar, se ficar muito lento, retiro o comando.

Aguardo orientação, se possivel!

Obrigada

Jorge Paulino disse...

Vai certamente ficar, pois sempre que alterasses a selecção de uma célula para outra, o código iria correr :(

Ainda assim teria de haver uma mudança de célula para poder funcionar, ou seja, se não se alterasse de célula, a formatação não funcionaria.

Não existe outra solução? Não podes usar a formatação condicional?

Patricia Levorato disse...

Olá Jorge,
Minha planilha funciona assim:
Ela é uma avaliação de desempenho, e existem "aspectos" onde a pessa é avaliada, e a avaliação funciona por cor, sendo ela Vermelho: Ruim, Amarelo: Regular e Verde: Bom.

Ao longo das semanas, o gestor irá preencher - com a cor, como foi o comportamento da pessoa, classificando-a nesses três critérios. Por isso preciso que a planilha faça a contagem manualmente, pois a acada semana o gestor irá acrescentar uma cor, avaliativa.
Deu pra entender?? SE puder me ajudar agradeço!

Obrigada!

Anónimo disse...

Ando há horas à procura disto.

E este funciona certinho. Basta mudar o número e pressionar enter para actualizar os números.

Muito obrigado. Salvou-me do... "apocalipse"... eh eh eh eh eh
:)


Bom Natal e Bom Ano Novo

José Faria disse...

Ola bom dia.

Em relação ao Index de cores, temos que criar isso num separador à parte ou o excel já assume isso?

Obrigado

Jamille Ribeiro disse...

boa noite.
vc tem uma PLAN já pronta com os seguinte form. calendário 2015 - com feriados e que possa somar as células por COR, cada celular vale (4horas/aulas) portanto exemplo: se selecionar 10 dias de cor vermelha o resultado será 40 horas/aulas = vermelho. 20 células azuis = 80 horas/aulas .
entra em contato: fone: 91- 81296505
reinascimentotecnico@yahoo.com.br
reinaldo.brg@senaipa.org.br

delcruz disse...

Por acaso da para fazer igual no drive permitir a contagem das cores das células.

Delcruz
Obrigado!

Adilson R.Santos disse...

Quando a celula sofre alteracoes tenho que sempre editar a célula para que a função recalcule .... vi as configurações e estão no cálculo automático ... e quando preciono ou mesmo F9 não calcula. Pode me ajudar ?

Unknown disse...

Estou com o office 2013, e não estou conseguindo executar a fórmula na célula, ele me retorna o seguinte erro:

"Erro de compilação:

Inválido fora de um procedimento"

Alguma idéia de qual o motivo?

Danielle Arins disse...

Bom dia, é possível mudar a código para informar o total de acordo com a cor da fonte, sem considerar a cor da célula?

Ex. Gostaria de contar todas as células de uma coluna em que existe a fonte na cor vermelha.

Muito obrigada.

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