Note: The other languages of the website are Google-translated. Back to English

Como contar e somar células com base na cor de fundo no Excel?

Supondo que você tenha um intervalo de células com cores de fundo diferentes, como vermelho, verde, azul e assim por diante, mas agora você precisa contar quantas células nesse intervalo têm uma determinada cor de fundo e somar as células coloridas com a mesma cor . No Excel, não existe uma fórmula direta para calcular Soma e Contagem de células coloridas, aqui vou apresentar algumas maneiras de resolver este problema.


Contar e somar células coloridas por Filtro e SUBTOTAL

Supondo que tenhamos uma tabela de vendas de frutas conforme a imagem abaixo mostrada, iremos contar ou somar as células coloridas na coluna Quantidade. Nessa situação, podemos filtrar a coluna Quantidade por cor e, em seguida, contar ou somar as células coloridas filtradas pela função SUBTOTAL facilmente no Excel.

1. Selecione células em branco para entrar na função SUBTOTAL.

  1. Para contar todas as células com a mesma cor de fundo, insira a fórmula = SUBTOTAL (102, E2: E20);
  2. Para somar todas as células com a mesma cor de fundo, insira a fórmula = SUBTOTAL (109, E2: E20);


Nota: Em ambas as fórmulas, E2: E20 é a coluna Quantidade que contém as células coloridas e você pode alterá-las conforme necessário.

2. Selecione o cabeçalho da tabela e clique em Dados > filtros. Veja a imagem:

3. Clique no ícone Filtro  na célula de cabeçalho da coluna Valor e clique em Filtrar por Cor e a cor especificada pela qual você contará sucessivamente. Veja a imagem:

Após a filtragem, ambas as fórmulas SUBTOTAL contam e somam todas as células coloridas filtradas na coluna Quantidade automaticamente. Veja a imagem:

Nota: Este método requer que as células coloridas que você contará ou somará estejam na mesma coluna.

Um clique para contar, somar e calcular a média das células coloridas no Excel

Com o excelente Contagem por Cor característica de Kutools for Excel, você pode contar, somar e calcular a média de células rapidamente por cor de preenchimento ou cor de fonte especificada com apenas um clique no Excel. Além disso, este recurso também descobrirá os valores máximos e mínimos das células pela cor de preenchimento ou cor da fonte.


contagem de anúncios por cor 2

Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Avaliação gratuita de recursos completos 30-dia, sem necessidade de cartão de crédito! Get It Now

Contar ou somar células coloridas pela função GET.CELL

Neste método, criaremos um intervalo nomeado com a função GET.CELL, obteremos o código de cores das células e então contaremos ou somaremos pelo código de cores facilmente no Excel. Faça o seguinte:

1. Clique Fórmulas > Definir o Nome. Veja a imagem:

2. Na caixa de diálogo Novo nome, siga as instruções abaixo na captura de tela:
(1) Digite um nome na caixa Nome;
(2) Insira a fórmula = GET.CELL (38, Folha4! $ E2) na caixa Refere-se a (nota: na fórmula, 38 significa retornar o código da célula, e Folha4! $ E2 é a primeira célula na coluna Amount, exceto o cabeçalho da coluna, que você precisa alterar com base nos dados da tabela.)
(3) Clique no OK botão.

3. Agora adicione uma nova coluna Color à direita da tabela original. Em seguida digite a fórmula = NumColor e arraste a alça de Preenchimento automático para aplicar a fórmula a outras células na coluna Cor. Veja a imagem:
Nota: Na fórmula, NumColor é o intervalo nomeado que especificamos nas 2 primeiras etapas. Você precisa alterá-lo para o nome especificado definido.

Agora o código de cor de cada célula na coluna Amount retorna na Coluna Color. Veja a imagem:

4. Copie e liste a cor de preenchimento em um intervalo em branco na planilha ativa e digite as fórmulas ao lado dela, conforme a captura de tela abaixo:
A. Para contar células por cor, insira a fórmula = CONT.SE ($ F $ 2: $ F $ 20, NumColor);
B. Para somar células por cor, insira a fórmula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Nota: Em ambas as fórmulas, $ F $ 2: $ F $ 20 é a coluna Color, NumColor é o intervalo nomeado especificado, $ E $ 2: $ E $ 20 é a coluna Valor e você pode alterá-los conforme necessário.

Agora você verá que as células na coluna Quantidade são contadas e somadas por suas cores de preenchimento.


Conte e some células com base na cor de preenchimento específica com a função definida pelo usuário

Supondo que as células coloridas se espalhem em um intervalo conforme a captura de tela abaixo mostrada, os dois métodos acima não podem contar ou somar as células coloridas. Aqui, este método apresentará um VBA para resolver o problema.

1. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o código a seguir na janela do módulo.

VBA: conte e some células com base na cor de fundo:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Em seguida, salve o código e aplique a seguinte fórmula:
A. Conte as células coloridas: = função de cor (A, B: C, FALSE)
B. Some as células coloridas: = função de cor (A, B: C, VERDADEIRO)

Nota: nas fórmulas acima, A é a célula com a cor de fundo específica que você deseja calcular a contagem e a soma, e B: C é o intervalo de células onde você deseja calcular a contagem e a soma.

4. Faça a captura de tela a seguir, por exemplo, insira a fórmula= função de cor (A1, A1: D11, FALSE) para contar as células amarelas. E use a fórmula = função de cor (A1, A1: D11, TRUE) para somar as células amarelas. Veja a imagem:

5. Se você quiser contar e somar outras células coloridas, repita a etapa 4. Em seguida, você obterá os seguintes resultados:


Contar e somar células com base na cor de preenchimento específica com funções do Kutools

O Kutools para Excel também oferece suporte a algumas funções úteis para ajudar os usuários do Excel a fazer cálculos especiais, como contagem por cor de fundo de célula, soma por cor de fonte, etc.

Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Avaliação gratuita de recursos completos 30-dia, sem necessidade de cartão de crédito! Teste grátis agora!

1. Selecione a célula em branco onde colocar os resultados da contagem e clique Kutools > Funções Kutools > Estatística e Matemática > CONT.PELA COR DA CÉLULA. Veja a imagem:

2. Na caixa de diálogo Argumentos da função, especifique o intervalo em que contará as células coloridas dentro do Referência caixa, escolha a célula que é preenchida com a cor de fundo especificada no Color_index_nr caixa, e clique no OK botão. Veja a imagem:

notas:
(1) Você também pode digitar a função Kutools especificada = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  na célula em branco ou barra de fórmula diretamente para obter os resultados da contagem;
(2) Clique Kutools > Funções Kutools > Estatística e Matemática > SUBYCELLCOLOR ou digite = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) na célula em branco diretamente para somar células com base na cor de fundo especificada.
Aplique o CONT.PELA COR DA CÉLULA e SUBYCELLCOLOR funções para cada cor de fundo separadamente e você obterá os resultados conforme a captura de tela abaixo:

Funções Kutools contêm uma série de funções integradas para ajudar os usuários do Excel a calcular facilmente, incluindo Contagem / soma / células visíveis médias, Contagem / soma por cor de célula, Contagem / soma por cor da fonte, Contar personagens, Contar por fonte em negrito, Etc. Faça um teste grátis!


Contar e somar células com base na cor de preenchimento específica com o Kutools para Excel

Com a função definida pelo usuário acima, você precisa inserir a fórmula uma a uma; se houver muitas cores diferentes, esse método será tedioso e demorado. Mas se você tiver Kutools for Excel'S Contagem por Cor utilitário, você pode gerar rapidamente um relatório das células coloridas. Você não só pode contar e somar as células coloridas, mas também pode obter os valores médio, máximo e mínimo da faixa colorida.

Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Avaliação gratuita de recursos completos 30-dia, sem necessidade de cartão de crédito! Teste grátis agora!

1. Selecione o intervalo que deseja usar e clique em Kutools Plus > Contagem por Cor, veja a captura de tela:

2. E no Contagem por Cor caixa de diálogo, faça como abaixo a imagem mostrada:
(1) Selecione Formatação padrão de Método de cor lista suspensa;
(2) Selecione Contexto de Tipo de contagem lista suspensa.
(3) Clique no botão Gerar relatório.

Nota: Para contar e somar células coloridas por cor de formatação condicional específica, selecione Formatação condicional de Método de cor lista suspensa na caixa de diálogo acima, ou selecione Formatação padrão e condicional na lista suspensa para contar todas as células preenchidas pela cor especificada.

Agora você obterá uma nova pasta de trabalho com as estatísticas. Veja a imagem:

A Contagem por Cor recurso calcula (contagem, soma, média, máximo, etc.) células por cor de fundo ou cor de fonte. Faça um teste grátis!


Artigo relacionado:


Demonstração: conte e some células com base no plano de fundo, cor de formatação condicional:


Kutools for Excel inclui mais de 300 ferramentas úteis para Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comentários (235)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Ferramenta muito útil, muito obrigado
Este comentário foi feito pelo moderador no site
Após inserir a função , quando alteramos o valor a soma não é atualizada automaticamente , alguma sugestão
Este comentário foi feito pelo moderador no site
Alt-Ctrl-F9 irá recalcular
Este comentário foi feito pelo moderador no site
Você pode adicionar e atualizar o botão se outra pessoa estiver usando o arquivo e não souber sobre o Alt-Ctrl-F9. basta adicionar esta linha de código à macro "Application.CalculateFull". é o mesmo que Alt-Ctrl-F9 apenas programado em um botão.
Este comentário foi feito pelo moderador no site
Em que parte agrego a linha para criar o botão
Este comentário foi feito pelo moderador no site
Adicione uma segunda linha diretamente abaixo da primeira que diz Application.volitile faz com que recalcule depois que algo for atualizado
Este comentário foi feito pelo moderador no site
Eu tentei colocar Aplicação.Volátil na primeira linha e nada acontece :(
Este comentário foi feito pelo moderador no site
[quote]Adicione uma segunda linha diretamente abaixo da primeira que diz Application.volitile faz com que recalcule depois que algo for atualizadopor 12345678998765432154[/quote] Application.Volatile é a função correta não Application.volitile
Este comentário foi feito pelo moderador no site
Não vejo nenhuma linha que diga application.volatile. Onde encontro?
Este comentário foi feito pelo moderador no site
Cliquei duas vezes na célula com a fórmula ColorFunction e pressionei Enter. Ele atualizou.
Este comentário foi feito pelo moderador no site
Muito útil. Obrigada
Este comentário foi feito pelo moderador no site
Eu também tentei a fórmula. Funciona na primeira linha e não funciona com a segunda e a terceira. ????
Este comentário foi feito pelo moderador no site
Concordo com Jarod. As duas primeiras linhas da minha função dizem: Função ColorFunction(rColor As Range, rRange As Range, Opcional SUM As Boolean) Application.Volatile O problema é que apenas um Alt-Ctrl-F9 irá recalcular. Estamos procurando uma solução mais automatizada. Ideias?
Este comentário foi feito pelo moderador no site
Tente assim: 1. certifique-se de ter a função salva conforme descrito 2. permita-me usar um exemplo * eu tenho centenas de linhas de dados * eu uso as colunas A a AB com mais dados * sempre que tenho um problema com meu dados, eu destaco em amarelo * Eu uso essa fórmula legal para 'contar' o número de destaques em cada linha 3. Como contar o número de meus destaques por linha a. figura o intervalo de célulasque poderia ter destaques que você deseja contar (ou somar) * para mim, quero COUNT a quantidade de meus destaques sinalizados em cada linha (meu intervalo) b. escolha uma célula onde você irá relatar a contagem (ou soma) * para mim, eu coloquei na extrema direita dos meus dados... na coluna AE c. insira a seguinte fórmula na célula que você escolheu no item b (acima) =colorfunction(AE3,A3:AB3,FALSE) * para mim, coloquei essa fórmula na célula AE3 (final da minha linha) E E destaquei a mesma célula de fórmula Amarelo d. Eu então copiei esta fórmula para todas as minhas linhas de dados 4. Eu observei a. nenhuma contagem foi feita. (ruim) 5 QUESTÃO CHAVE AQUI: a. Eu pressionei CTL + ALT + F9 . PRESTO!!! funciona.! espero que esta brincadeira seja de alguma ajuda. Saúde faça isso: ABCD 1 2 3 4
Este comentário foi feito pelo moderador no site
Muito bom, muito obrigado!!!
Este comentário foi feito pelo moderador no site
Artigo muito útil, muito obrigado
Este comentário foi feito pelo moderador no site
Maravilhoso!!! Muito obrigado!!!
Este comentário foi feito pelo moderador no site
Incrível, muito obrigado
Este comentário foi feito pelo moderador no site
Esta é uma ótima ferramenta, obrigado por compartilhar! Eu tenho uma pergunta: notei que a fórmula da macro não se atualiza quando você altera a cor de uma célula de uma cor para outra. Existe uma maneira de fazer com que ele seja atualizado automaticamente quando as cores das células mudam? Se eu clicar duas vezes na célula com a fórmula de adição e pressionar enter, ela atualiza, mas eu tenho uma planilha com muitas dessas células e não quero ter que atualizá-la manualmente a cada vez.
Este comentário foi feito pelo moderador no site
Obrigado pela sua resposta, desculpe, na situação atual, o código VBA não pode resolver a questão que você apontou, você precisa atualizá-los manualmente.
Este comentário foi feito pelo moderador no site
Também descobri que usar o formatador para atualizar a cor da célula fará com que as fórmulas funcionem conforme o esperado. Ainda não é perfeito.
Este comentário foi feito pelo moderador no site
Olá, eu tentei. e a planilha só precisa de um "Enter" Não importa qual célula. Então, depois de colorir sua célula, basta digitar um memorando nas células coloridas e "entrar"
Este comentário foi feito pelo moderador no site
tente CTL + ALT + f9, ele atualizará todas as células com fórmulas de adição de uma só vez. Não é totalmente automático, mas pelo menos é melhor do que clicar em cada célula com a fórmula de adição individualmente.
Este comentário foi feito pelo moderador no site
isso não fez nada para mim :(
Este comentário foi feito pelo moderador no site
Olá, dando erro. erro de conformidade, erro ambíguo está chegando.
Este comentário foi feito pelo moderador no site
Apliquei a fórmula mas a célula dizia NOMES? o que eu preciso consertar
Este comentário foi feito pelo moderador no site
Olá RAMON, talvez você não tenha copiado o código acima no módulo. Você deve primeiro copiar o código no Módulo e salvá-lo, depois aplicar as fórmulas. Você pode experimentá-lo. Se não te ajudar, por favor me avise.
Este comentário foi feito pelo moderador no site
Olá, funcionou da primeira vez, mas agora, quando tenho que adicionar uma célula na soma... tentei introduzir novamente o código, salvar, escrever a fórmula novamente... diz "nome?"
Este comentário foi feito pelo moderador no site
Estou tendo o mesmo problema - funcionou lindamente no início, mas recebendo o erro NAME depois de adicionar outra célula. Já tentei deletar o módulo e adicionar novamente, reinsira a função... sem alegria.
Este comentário foi feito pelo moderador no site
Talvez seja necessário habilitar Marcos novamente. Deve piscar na parte superior da tela, dependendo da versão do Excel. :)
Este comentário foi feito pelo moderador no site
Skyyang, se eu lhe enviar um documento por e-mail, você pode me ajudar? Mike
Este comentário foi feito pelo moderador no site
Muito obrigado! Isso é genial, obrigado pela ajuda!
Este comentário foi feito pelo moderador no site
Muito obrigado, estive procurando o dia todo por um resultado bem-sucedido e agora temos um :)
Este comentário foi feito pelo moderador no site
Obrigado. Funcionou para mim como descrito. No entanto, depois de reabrir o arquivo na próxima vez, todas as células com essa fórmula estavam mostrando erro. Eu tive que re-copiar a codificação novamente. Existe algo que eu estou perdendo? Precisarei compartilhar o arquivo com outras pessoas e elas não poderão "consertar" o problema. Obrigado, Ker.
Este comentário foi feito pelo moderador no site
Você precisa salvá-lo como pasta de trabalho do Excel Macro
Este comentário foi feito pelo moderador no site
bem feito..mas..como funciona em cores de células de formatação condicional ???
Este comentário foi feito pelo moderador no site
Isso é incrível, obrigado!
Este comentário foi feito pelo moderador no site
funcionou direitinho!!!
Este comentário foi feito pelo moderador no site
caro senhor , na verdade é um código maravilhoso mas enfrentei um problema que ele continuará calculando a célula mesmo quando mudarmos a cor da célula
Este comentário foi feito pelo moderador no site
Obrigado Senhor, Este é um trabalho maravilhoso e me ajuda muito.
Este comentário foi feito pelo moderador no site
Eu tenho um arquivo do Excel com muitas células coloridas. Existe alguma maneira de converter essas cores em dados? Então, uma célula vermelha terá "vermelho" como dados, uma célula de cor azul terá "azul" e assim por diante? James
Este comentário foi feito pelo moderador no site
Artigo oportuno e apto, como acertar o alvo que estou procurando :) obrigado
Este comentário foi feito pelo moderador no site
Eu não consigo fazer isso funcionar. Funciona apenas para células que foram coloridas manualmente? Eu preciso contar células que foram coloridas via Formatação Condicional, mas não está jogando bola no momento.
Este comentário foi feito pelo moderador no site
Estou tendo o mesmo problema que Ian, estou tentando somar números com base nas regras de cores definidas pela formatação condicional, mas isso não parece pegar isso. Alguma sugestão?
Este comentário foi feito pelo moderador no site
Tentaremos aprimorá-lo nas próximas versões. :-)
Este comentário foi feito pelo moderador no site
Eu joguei um script do Powershell que funciona como uma espécie de solução alternativa: " #setup Excel $excelApp = New-Object -comobject Excel.Application $excelApp.Quit() $excelApp.Visible = $True $workbook = $excelApp.Workbooks .Open("H:\Desktop\test.xlsx")#MUDE ISSO PARA SEU ENDEREÇO ​​DE ARQUIVO DO EXCEL $worksheet = $workbook.Worksheets.Item("Sheet1")#MUDE ISSO SE SUA PLANILHA NÃO É CHAMADA "Sheet1" #static variables $row = 1 $column = 1#MUDE ESTA VARIÁVEL PARA A COLUNA QUE VOCÊ ESTÁ PROCURANDO $totalRow = $worksheet.UsedRange.Rows.Count do{ $currentCell = $worksheet.cells.item($row, $column ) if($currentCell.text -eq "SEARCH_FOR_THIS") { $worksheet.cells.item($row,$column).Interior.ColorIndex = 44#MUDE ESTE NÚMERO PARA MUDAR A COR DA NOVA CÉLULA "$row, $column = BRANCO. Coloring" } $row++ $row } while($row -lt $totalRow) "Script concluído." "SAVING..." $excelApp.Save $excelApp.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject ($excelApp) " Este script irá pesquisar em sua planilha e se encontrar uma célula que contenha "SEARCH_FOR_THIS" (você provavelmente deve alterar essa parte no script) então ele irá transformar essa célula em qualquer número de cor que esteja no script (atualmente definido para 44, uma cor laranja/castanha de aparência estranha). Quando o script terminar, ele tentará dizer ao Excel que deseja salvar, mas o Excel não é um programa muito confiável, portanto, solicitará que você clique em salvar. Depois de fazer isso, ele será fechado. Em seguida, abra o arquivo de volta e faça todos os negócios listados nesta página, certificando-se de que a cor que meu script está procurando é da mesma cor que o script listado nesta página está procurando. Espero que isto ajude!
Este comentário foi feito pelo moderador no site
Lenda! Obrigado funcionou um encanto
Não há comentários postados aqui ainda
carregar mais
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL