Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

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);


Importante: 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:

Importante: 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. Avaliação gratuita de recursos completos por 30 dias!
contagem de anúncios por cor 2

Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Teste gratuito completo por 30 dias, 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:
Importante: 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).

Importante: 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, diz contar por cor de fundo de célula, soma por cor de fonte, etc.

Kutools for Excel - Inclui mais de 300 ferramentas úteis para Excel. Teste gratuito completo por 30 dias, 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 > COUNTBYCELLCOLOR. 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 > SUMBYCELLCOLOR 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 COUNTBYCELLCOLOR e SUMBYCELLCOLOR 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. Teste gratuito completo por 30 dias, 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.

Importante: 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 ...
  • Super Formula Bar (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-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. 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!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.