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 + 1End If
Next
' Define que a função (valor a retornar) tem o valor de x
CountColors = xEnd 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!
48 comentários:
Muito útil, por acaso nunca pensei que o Excel permitisse a contagem das cores das células.
Obrigado!
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
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!
Quero saber se há como fazer esta fórmula pelo excel mesmo, sem utilizar o Visual basic?
Obrigada
»» 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.
E se eu quiser somar o valor representado pela cor?
o código muda, não é?
como seria?
Bárbara
>> 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.
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!
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.
Olá,
Não entendo a questão! A formula calcula automaticamente caso exista alguma alteração.
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.
Veja se as formulas estão em automático (Tools - Options - Calculation)
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
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.
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.
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
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!
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
>>>>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
Olá,
Para contar as células sem cor é só indicar o código -4142.
Exemplo:
=CountColors(A1:A100;-4142)
Espero que ajude!
Obrigado pela dica sobre contar células sem preenchimento.
Um Abraço
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
deu-me jeito!
Obrigado
Muito bom...
Parabéns e obrigado.
Sugestão: Tabela das cores com os códigos das cores para as macros, seria fantástico.
Gostaria de saber se esta forma também se pode aplicar aos caracteres cloridos, de cores diferentes
Olá,
Sim, pode-se fazer um ciclo nos caracteres e ver a cor que ele tem, usando Range("a1").Characters
Essa fórmula não dei certo no excel 2010. Nãoconsigo achar o porque.
Sem mostrar o que tem ou mais detalhes, ninguém consegue adivinhar!
aqui funcionou perfeito!!
OBRIGADO!
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...
Não estará o filtro a contemplar também a formula e por isso ela desaparece?
Dê lá mais detalhes.
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
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!
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
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?
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!
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
Ola bom dia.
Em relação ao Index de cores, temos que criar isso num separador à parte ou o excel já assume isso?
Obrigado
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
Por acaso da para fazer igual no drive permitir a contagem das cores das células.
Delcruz
Obrigado!
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 ?
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?
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.
Amigo a formula funcionou perfeitamente, porém, ela não atualiza automaticamente quando eu altero a cor da célula. Toda vez que altero a cor de uma célula eu tenho que executar a função novamente.
Como faço pra atualizar a contagem automaticamente????
Tente isso ...
Entre no VBA e na pasta onde estiver as cores a serem somadas insira o código abaixo:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub
Bom dia,
Pretendo saber se existe alguma formula que conte pela cor e pelo texto dentro da célula.Na planilha tenho um horário escolar mensal, e por exemplo:
Na 2ªfeira tenho ás 8.30h. português, ás 9.30h. inglês, ás 10.30h. francês,etc. Cada célula tem o português, o inglês e o francês, e quando a aluno falta, eu pinto a célula de azul(justificada) ou vermelha(injustificada) na hora que ele falta. O que pretendo é uma formula que conte as vezes em que o português está azul, ou vermelho, assim como qualquer outra disciplina.
Agradeço desde já as possiveis respostas.
Enviar um comentário