Contar Células Coloridas
Hoje a dica é bem simples, embora os fundamentos apresentados sejam de grande aplicabilidade na manipulação avançada do Excel.
Para desenvolvermos esse nosso projeto vamos trabalhar com fórmulas, só que são fórmulas que não são nativas do Excel, ou seja, nós é que vamos criar essas fórmulas. São as chamadas “Fórmulas Personalizadas ou Desenvolvidas pelo usuário“.
Essas fórmulas personalizadas nada mais são do que um código desenvolvido no ambiente VBA para ser chamado diretamente na célula onde queremos o resultado. Como fazer isso? Ao invés de criarmos uma macro simples através de uma subrotina (Sub) devemos criar uma função (Function). Por quê? Eu explico.
As subrotinas são macros que executam uma série de eventos porém não carregam consigo resultado algum, elas criam esses resultados e tudo mais, porém só os retorna se nós criarmos mecanismos para isso. As funções já são diferentes. Elas tem a capacidade de carregar todo o resultado calculado dentro dela. E a fórmula nada mais é do que o resultado de um cálculo em uma função.
A Função pode carregar critérios para executar seus cálculos. Um exemplo é a SOMA(A1:A4). Nessa fórmula é informado os valores presentes nas células A1, A2, A3 e A4. O cálculo é feito com base neles e o resultado é informado na célula onde a fórmula foi digitada.
Bem, é basicamente isso. No nosso exemplo prático de hoje, vamos desenvolver uma função que conte todas as células em um determinado intervalo que tenha uma cor específica.
Abra o Ambiente Visual Basic (ALT + F11) e insira um Módulo. Digite o seguinte código nele:
Function ContaCelulaColorida(rngColorInfo As Range, Intervalo As Range) As Long Dim rConta As Range For Each rConta In Intervalo.Cells If rConta.Interior.ColorIndex = rngColorInfo.Interior.ColorIndex Then ContaCelulaColorida = ContaCelulaColorida + 1 End If Next End Function |
Pronto. Só isso!
Agora crie uma tabela com várias células coloridas. E crie ao lado outra tabelinha para exibir a contagem das células. Nesta tabelinha, coloque uma coluna com as cores a ser contadas e outra com o resultado da contagem. E na célula correspondente, digite a fórmula. Assim:
ContaCelulaColorida (rngColorInfo, Intervalo)
Onde:
rngColorInfo é a célula que contém a cor a ser procurada;
Intervalo é o conjunto de células coloridas que deverão ser contadas.
Sua planilha deve ficar parecida com a figura abaixo.
Um abraço a todos.
46 Comentários
Olá!
Gostei muito da dica e consegui aplicá-la, mas gostaria de um aprimoramento.
Gostaria que a contagem fosse atualizada se eu alterar a cor da célula, o que não consegui. Se eu mudar a cor de uma das células, o valor não muda.
Espero que você possa me ajudar!
Obrigada!
Reply
Comentário feito em dezembro 9th, 2008 às 14:08
Olá.
Ótimo Artigo! Parabéns.
Gostaria de poder fazer algo mais: somar os valores das células colocaridas.
Como faço isso?
Atenciosamente,
Joyce Pardinho
Reply
exceldoseujeito Reply:
agosto 21st, 2009 at 20:31
Simples, Joyce, substitua a linha
ContaCelulaColorida = ContaCelulaColorida + 1
Por
ContaCelulaColorida = ContaCelulaColorida + rConta.Value
Abração…
Reply
Comentário feito em julho 4th, 2009 às 10:50
Oi.
Ótima a fórmula, foi exatamente o que estava procurando.
Me ajudou e muito!
Obrigado.
Reply
exceldoseujeito Reply:
julho 30th, 2009 at 18:03
Disponha sempre.
Reply
Comentário feito em julho 22nd, 2009 às 14:35
Muito foda!
Valeu mesmo…isso que eu precisava!
Att.,
Bacana
Reply
Reinaldo Coral Reply:
setembro 4th, 2009 at 18:48
Obrigado.
Volte sempre
Reply
Comentário feito em setembro 4th, 2009 às 8:16
Putzzz…agora n esta dando mais certo!
Da como resultado “#NOME?” …Me dÊ uma Luz?
Att.,
Bacana
Reply
Reinaldo Coral Reply:
setembro 4th, 2009 at 18:51
Alguma referência de célula deve estar causando isso.
Reply
Comentário feito em setembro 4th, 2009 às 17:39
Seguinte, quando faço referência em outra planilha tipo (A3;’ACOMPANHAMENTO SUP_FHD’!E5:E3970) ele não calcula, dá (#NOME?)…me ajude please!
Att.,
Bacana
Reply
Reinaldo Coral Reply:
setembro 11th, 2009 at 12:06
Cara, alguma coisa tu está fazendo errado, porque dá certo sim.
Vai ver alguma informação de referência de célula não esteja batendo.
Reply
Comentário feito em setembro 9th, 2009 às 11:59
Veja bem amigo, estou batendo cabeça aqui…o que eu preciso é de uma fórmula para contar as cores das celulas ao mesmo tempo contar as informações contidas nela. Segue abaixo o exemplo.
Obs.: *O que esta entre parenteses é a cor da celula.
*Cada cor significa uma informação como mostra a (Tab.2).
*Quero saber se tem uma maneira de somar as cores com a referência nos municípios. Tipo somar quantos V.Velha (amarelo) possuem no intervalo da (Tab.1)
Tab.1 Tab.2
Municipio Legenda V.Velha Cariacica Serra Vitoria
V. Velha (amarelo) DCC (amarelo) 2 … … …
V. Velha (amarelo) FHD (vermelho) 1
V. Velha (cinza) PPP (roxo) 1
V. Velha (vermelho) Óbitos (verde) 2
Vitoria (roxo) Encerrado (azul) 2
V. Velha (roxo) Descartados (cinza) 1
Serra (amarelo)
Serra (cinza)
Cariacica (roxo)
Serra (amarelo)
Serra (roxo)
Vitoria (vermelho)
V. Velha (verde)
V. Velha (verde)
V. Velha (azul)
Serra (azul)
Vitoria (cinza)
V. Velha (azul)
* Se quiser mando a tabela em excel para melhor visualização.
Agradeço sua atenção!
Fico no aguardo
Att.,
Bacana
Reply
Comentário feito em setembro 9th, 2009 às 16:43
Vixe foi tudo desconfigurado…agora só mandando a tabela por e-mail…:(
Me manda um e-mail que respondo com a tabela em anexo!
Att.,
Bacana
Reply
Comentário feito em setembro 9th, 2009 às 16:47
Ola…desculpe pelos transtornos, desconsidere esses últimos, pois consegui resolver. Mas queria te pertubar mais uma vez, preciso que esta fórmula atualize automaticamente quando acrescento mais cores.
Att.,
Bacana
Reply
Reinaldo Coral Reply:
setembro 11th, 2009 at 12:01
Olá.
Primeira coisa que deves fazer é extender o intervalo onde as cores serão adicionadas. No exemplo deste artigo, o intervalo descrito na fórmula é da coluna “A” da linha 2 até a 30. Modifique isto, para a totalidade onde serão pintadas as células.
Segunda coisa, adicione, na planilha em questão, a linha de comando abaixo:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)ActiveSheet.Calculate
End Sub
Prontinho.
Abraço
Reply
Comentário feito em setembro 10th, 2009 às 11:17
Blz, esse comando eu add em outro módulo? ele vai fazer com que o cálculo se atualize automaticamente quando modificado a cor? Testei add em um outro módulo mas não atualizou.
Agradeço muito a sua atenção e por estar compartilhando o seu conhecimento, valeu mesmo. A funcionalidade da minha planilha está excelente, só falta mesmo atualizar.
Você é o cara! :)
Abrção.
Reply
Reinaldo Coral Reply:
setembro 11th, 2009 at 12:57
Adicione no módulo da planilha que você está usando as células coloridas.
Vlw
Reply
Mario Reply:
dezembro 8th, 2009 at 13:07
Eu coloquei o codigo mas não actualiza a contagem das celulas coloridas sempre que preencho uma nova ou removo uma existente. O que estou a fazer de errado?
O código é inserido logo abaixo do end sub do primeiro codigo correcto?
Reply
Reinaldo Coral Reply:
dezembro 31st, 2009 at 15:29
Sua solução pode ser encontrada aqui mesmo.
http://www.exceldoseujeito.com.br/2008/11/21/contar-celulas-coloridas/#comment-311
Abraços
Reply
Comentário feito em setembro 11th, 2009 às 12:51
Interessante, mas minha necessidade é a seguinte:
Tenho as células pintadas, e dentro delas tenho valores.
Exemplo: No intervalo A1:A10, tenho as células A3 (com valor 10), A5 (com valor 12), A6 (com valor 4) e A9 (com valor 7) pintadas de vermelho.
Desejo obter a soma SOMENTE dessas células, ou seja, que a fórmula pegue as células pintadas de vermelho e me dê o valor da soma dos conteúdos dessas células coloridas, no caso, seria 34. Pode me ajudar?
Reply
Reinaldo Coral Reply:
outubro 17th, 2009 at 13:30
Fala cara!!
Já havia respondido uma parada dessas aqui nos coments… você pode fazer assim:
Substitua a linha
ContaCelulaColorida = ContaCelulaColorida + 1
Por
ContaCelulaColorida = ContaCelulaColorida + rConta.Value
Ok…
Abração…
Reply
Comentário feito em outubro 11th, 2009 às 12:17
Reinaldo, parabéns pelo código (e pelo site). Esse código de atualização só funciona se a pessoa alterar algum valor no intervalo… não funciona se a pessoa simplesmente alterar a cor da célula.
Acho que é essa a questão dos demais … e a minha também.
De fato, usar o _selectionchange é o mais perto possível de se alterar essa fórmula aí, mas acho que precisaria de algo que force a mudança de algum valor no intervalo… ou antes da pessoa sair da planílha, ou de tempos em tempos (com algum temporizador), nao sei.
Não achei ainda nenhum código para isso, talves você tenha e possa compartilhar. Caso tenha, me manda por e-mail?
Reply
Comentário feito em outubro 15th, 2009 às 16:40
entao… o que fiz pra solucionar isso foi simples… criei um botão de ação e nas ações do botao coloquei o tal calculate… segue o código
Sub Botão2_Clique()
ActiveSheet.Calculate
End Sub
Reply
Comentário feito em outubro 15th, 2009 às 16:43
Valeu, funcionou perfeitamente. Agora só falta uma coisa: a soma dos valores está aparecendo arredondado. Quando as casas depois da vírgula são 75 centavos, arredonda para mais. Quando é 50 centavos, arredonda para menos. Quero que apareça o valor real. Já tentei “Definir precisão conforme exibido” nas propriedades avançadas do Excel, mas não funcionou…
Reply
Comentário feito em outubro 18th, 2009 às 18:26
Valeu Reinaldo.
Deixei outra dúvida, mas não apareceu, vou refazer:
A fórmula deu certinho (Substitua a linha
ContaCelulaColorida = ContaCelulaColorida + 1
Por
ContaCelulaColorida = ContaCelulaColorida + rConta.Value), só que está arredondando os valores. Até cinquenta centavos, arredonda para menos; daí em diante arredonda para mais. Como é planilha de orçamento, não quero que arredonde. Como corrigir isso na própria fórmula? Já tentei “Definir precisão conforme exibido”, mas não funcionou…
Reply
Reinaldo Coral Reply:
outubro 20th, 2009 at 12:19
Oi… tenho estado com o tempo muito reduzido….. será que o dia não tem mais 24 horas??!!! rsrsrsrs
Bem… sua solução é simples!
A função carrega valores inteiros longos, sem precisão decimal, ok! (É do tipo Long).
Você deve alterar para um tipo que suporte números decimais, tipo Double. Faça assim:
Onde está:
Function ContaCelulaColorida(rngColorInfo As Range, Intervalo As Range) As Long
Substitua por:
Function ContaCelulaColorida(rngColorInfo As Range, Intervalo As Range) As Double
Vai ficar PERFEITO.
Abração.
Reply
Comentário feito em outubro 20th, 2009 às 0:23
Perfeito mesmo Reinaldo! Você salvou uma vida!! Muito obrigado!!!
Reply
Comentário feito em outubro 21st, 2009 às 22:47
Olá, estou quebrando a cabeça para terminar um planilha com o resultado da lotofácil. Consegui que os acertos ficassem na cor verde (usando a formatação condicional). No entanto, usei a tua fórmula acima, baixei o exemplo e , depois de várias tentativas, não consegui lograr êxito. Será que é pelo fato dos dados em minha range se encontrarem na horizontal? Vc pode me ajudar ?
Grato
Reply
Reinaldo Coral Reply:
novembro 28th, 2009 at 11:23
Oi Moisés. O problema não é o posicionamento das células não. A fórmula calcula qualquer intervalo de células.
Mas, você não me disse qual o problema que está ocorrendo. Como você está informando o intervalo?
Abraço.
Reply
Comentário feito em novembro 25th, 2009 às 13:47
Olá amigo:
Deixei uma duvida pra vc e vc não me retornou, por favor, estou no aguardo.
Um abraço
Reply
Reinaldo Coral Reply:
novembro 28th, 2009 at 11:25
Poxa Moisés, desculpe-me a demora em responder…. o tempo está muito corrido mesmo.
Mas agora respondi lá no tópico.
Abração.
Reply
Comentário feito em novembro 27th, 2009 às 13:23
Obrigado irmão…
ainda não resolvi o problema. Se vc me deixar um e-mail te mando a planilha para vc analizar.
Um grande abraço
Reply
Comentário feito em dezembro 1st, 2009 às 10:53
ACHEI FANTÁSTICA A FUNÇÃO PARA CONTAR CÉLULAS POR CORES. MAS ESTOU ENCONTRANDO DIFICULDADE PARA CONTAR CÉLULAS LARANJAS DO INTERVALO C19:I24 QUE POSSUI DATAS ENTRE AS DATAS DAS CÉLULAS G19 E E22.
OU SEJA, O INTERVALO C19:I24 CORRESPONDE AS DATAS DO MÊS DE OUTUBRO, EU QUERO CONTAR AS CÉLULAS LARANJAS ( RECESSOS) QUE TEM DATAS DEPOIS DE C19 ( 01/10/2010) E ANTERIORES OU IGUAIS E22 ( 20/10/2010) QUE REPRESENTA O TÉRMINO DO 3º BIMESTRE DA ESCOLA.
OBRIGADO!
RANGEL
Reply
Reinaldo Coral Reply:
dezembro 31st, 2009 at 14:33
Rangel,
Seria necessário fazer modificações nesta fórmula para que retornasse o resultado no intervalo que você precisa. A função teria que trabalhar por áreas. Enfim Quando eu tiver um tempinho disponibilizarei algo desse tipo. No momento, sem alterações na fórmula, você pode adaptar o seu uso para poder atender as suas necessidades. Faça assim:
Execute a função por blocos.
=ContaCelulaColorida (rngColorInfo, G19:I19) + ContaCelulaColorida (rngColorInfo, C20:I20) + ContaCelulaColorida (rngColorInfo, C22:E22)
Espero ter ajudado.
Abraços
Reply
Comentário feito em dezembro 30th, 2009 às 9:28
olá!
Gostei muito da dica e consegui aplicá-la, mas gostaria de um aprimoramento.
Gostaria que a contagem fosse atualizada se eu alterar a cor da célula, o que não consegui. Se eu mudar a cor de uma das células, o valor não muda.
Espero que você possa me ajudar!
Obrigada!
Reply
Reinaldo Coral Reply:
dezembro 31st, 2009 at 14:15
Obrigado.
Veja a solução neste tópico http://www.exceldoseujeito.com.br/2008/11/21/contar-celulas-coloridas/#comment-311
Pode te ajudar.
Abraço
Reply
Comentário feito em dezembro 30th, 2009 às 19:43
Olá, de fato muito boa a dica
mas sou iniciante e gostaria de algo mais simples
qual a função para contar o total de células coloridas no intervalo? de qualquer cor
por exemplo, em um intervalo de 5 celulas tenho duas verdes e uma amarela, gostaria que o resultado fosse 3
Além disso, como faço para multiplicar os valores das células coloridas? novamente de qualquer cor
O valor das células verdes eh 5 e 3 e da célula amarela é 1, gostaria que o resultado fosse 15
Obrigado
Reply
Reinaldo Coral Reply:
janeiro 18th, 2010 at 11:23
Cara… numa breve oportunidade, postarei um tutorial básico para iniciantes no VBA.
Essas funções que você precisa em sua planilha podem perfeitamente ser adaptadas desse código original. Enfim, inclusive, nos comentários deste artigo já apresentei soluções para SOMAR o conteúdo das células coloridas. Por aí, você pode adaptar para multiplicação. E quanto, a inclusão de todas as células coloridas no somatório, você deve, no código, restringir as células diferentes de interior.colorindex = -4142.
Espero ter ajudado.
Abraço
Reply
Comentário feito em janeiro 13th, 2010 às 21:59
Olá…gostaria de saber como fazer para q o Excel não arredonde. Estou usando a formula para multiplicar e ele está arredondando os valos, sendo q eu so posso usar 2 casa apos a virgula, pois é uma planilha já montada e dependo do valor final.
Desde já agradeço!
Reply
Reinaldo Coral Reply:
janeiro 20th, 2010 at 18:24
Oi Thamyres,
Existem várias maneiras de obter este resultado. Segue uma dica:
Primeiro selecione todas as células onde estão digitados os seus valores a multiplicar e onde estarão os resultados.
Em seguida, vá ao menu Formatar > Células…
Na tela que será exibida, selecione a aba Número, e depois na caixa Categoria, escolha Número. Nas opções que surgirão à direita da tela, digite o número de casas decimais que deseja, no seu caso, 2 (duas).
Abraço.
Reply
Comentário feito em janeiro 20th, 2010 às 15:39
Olá, estou a criar uma base de dados para contagem de féias, em que uso células com cores. Você ajudou-me imesno, só não consigo que o total actualize automaticamente. Ou seja, se eu adicionar ou retirar células com cor, o excel não faz a correcção automática.
Vi em cima num post que tinha de inserir uma 2ª fórmula nas macros, mas não sei em que linha inserir.
Pode-me ajudar?
Cumps. from portugal
Reply
Comentário feito em janeiro 29th, 2010 às 9:45
Olá, estou a criar uma base de dados para contagem de férias, em que uso células com cores. Você ajudou-me imesno, só não consigo que o total actualize automaticamente. Ou seja, se eu adicionar ou retirar células com côr, o excel não faz a correcção automática.
Vi em cima num post que tinha de inserir uma 2ª fórmula nas macros, mas não sei em que linha inserir.
Pode-me ajudar?
Cumps. from portugal
Reply
Reinaldo Coral Reply:
janeiro 29th, 2010 at 22:34
Daniel, todo o código para esta função foi descrito aqui neste artigo para ser inserido em um Módulo Global.
O código descrito aqui nos comentários (transcrito abaixo) é para ser digitado no módulo da própria planilha que você quer contar as células coloridas:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)ActiveSheet.Calculate
End Sub
Exemplo: Se você está utilizando uma planilha chamada “Plan1″, você pode observar que dentro do ambiente VBA (Projeto – VBAProject) existe, por padrão:
Microsoft Excel Objetos
EstaPasta_de_trabalho
Plan1 (Plan1)
Plan2 (Plan2)
Plan3 (Plan3)
Módulos
Módulo1
Dê um duplo clique em Plan1 e será exibida o “source code” desta planilha. Digite o código descrito acima neste espaço.
Espero que tenha ajudado,
Abraço
Reply
Comentário feito em janeiro 29th, 2010 às 15:55
Efectivamente não está a funcionar.
Não sei o que estou a fazer mal, vou descrever o meu processo:
Na célula que qeuero a soma das cores tenho:
=ContaCelulaColorida(E2;C1:C16) em que E2 é uma célula solta com a cor que quero contar
No VBA criei o módulo1:
Function ContaCelulaColorida(rngColorInfo As Range, Intervalo As Range) As Long
Dim rConta As Range
For Each rConta In Intervalo.Cells
If rConta.Interior.ColorIndex = rngColorInfo.Interior.ColorIndex Then
ContaCelulaColorida = ContaCelulaColorida + rConta.Value
End If
Next
End Function
No VBA em “folha1″ criei:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
Na folha de cálculo o total de cores aparece agora como “0″.
Pode-me dar uma ajuda?
Muito obrigado
Reply
Reinaldo Coral Reply:
fevereiro 12th, 2010 at 13:10
Daniel,
Você quer contar a quantidade total de células com uma determinada cor ou calcular a soma total de seus conteúdos?
Se deseja a soma dos valores de seus conteúdos… ok… deixe com está… mas teremos que ver o que possa estar acontecendo mais profundamente porque o código descrito está perfeitamente correto.
Mas se você deseja apenas contar quantas células com a cor informada estã aparecendo no intervalo. Você deve substituir a linha:
ContaCelulaColorida = ContaCelulaColorida + rConta.Value
Por:
ContaCelulaColorida = ContaCelulaColorida + 1
Espero ter ajudado. Se ainda tiver dúvida, pode escrever.
Abraço.
Reply
Comentário feito em fevereiro 5th, 2010 às 8:15
Agradecia imenso que me ajudasse pois tenho um trabalho pendente e isto ia ser crucial para o sucesso deste.
Obrigado
Reply
Comentário feito em fevereiro 12th, 2010 às 9:27
Deixar um comentário