Dica: outros idiomas são traduzidos pelo Google. Você pode visitar o English versão deste link.
Entrar
x
or
x
x
Registre-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 plano de fundo diferentes, como vermelho, verde, azul e assim por diante, mas agora é necessário 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 apresentarei algumas maneiras de resolver esse problema.

Contagem e soma de células com base na cor de preenchimento específica por Filtro e SUBTOTAL

Contagem e soma de células com base na cor de preenchimento específica pela função GET.CELL

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

Contagem e soma de células com base na cor de preenchimento específica com as funções do Kutools

Contagem e soma de células com base na cor de preenchimento específica (ou cor de formatação condicional) com o Kutools for Excel

Um clique para contar, somar e calcular células coloridas no Excel

Com o excelente Count by Color característica de Kutools for Excel, você pode rapidamente contar, somar e calcular a média de células por cor de preenchimento ou cor de fonte especificada com apenas um clique no Excel. Além disso, esse recurso também descobrirá os valores máximos e mínimos das células pela cor de preenchimento ou pela cor da fonte. Versão completa de avaliação gratuita 30-dia!
contagem de anúncios por cor 2


Contagem e Soma de células coloridas por Filtro e SUBTOTAL

Suponhamos que tenhamos uma tabela de vendas de frutas, como mostrado abaixo, e contaremos ou somaremos as células coloridas na coluna Montante. Nessa situação, podemos filtrar a coluna Valor por cor e, em seguida, contar ou somar 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, por favor insira a fórmula = SUBTOTAL (109, E2: E20);


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

2. Selecione o cabeçalho da tabela e clique em Dados > filtros. Ver captura de tela:

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

Após a filtragem, as duas fórmulas SUBTOTAL contam e somam todas as células coloridas filtradas na coluna Montante automaticamente. Veja a captura de tela:

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


Contagem ou soma de células coloridas pela função GET.CELL

Nesse método, criaremos um intervalo nomeado com a função GET.CELL, obteremos o código de cores das células e, em seguida, contamos ou somamos pelo código de cores facilmente no Excel. Por favor, faça o seguinte:

1. Clique Fórmulas > Definir nome. Ver captura de tela:

2. Na caixa de diálogo New Name, faça como mostrado abaixo:
(1) Digite um nome na caixa Nome;
(2) Digite a fórmula = GET.CELL (38, Sheet4! $ 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 Valor, 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 o arraste o identificador de preenchimento automático para aplicar a fórmula a outras células na coluna cor. Veja a captura de tela:
notas: Na fórmula, NumColor é o intervalo nomeado que especificamos nas primeiras etapas 2. Você precisa alterá-lo para o nome especificado que você definiu.

Agora, o código de cores de cada célula na coluna Valor retorna na Coluna de cores. Veja a captura de tela:

4. Copie e liste a cor de preenchimento em um intervalo em branco na planilha ativa e digite as fórmulas próximas a ela, conforme mostrado abaixo:
A. Para contar as células por cor, por favor insira a fórmula = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Para somar as células por cor, por favor insira a fórmula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

notas: Em ambas as fórmulas, $ F $ 2: $ F $ 20 é a coluna Cor, 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 Valor são contadas e somadas por suas cores de preenchimento.


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


Supondo que as células coloridas se espalhem em um intervalo como mostrado abaixo, ambos os métodos acima não podem contar ou somar as células coloridas. Aqui, este método irá introduzir um VBA para resolver o problema.

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

2. Clique inserção > Módulo, e cole o seguinte código na janela do módulo.

VBA: contagem e soma de 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, guarde o código e aplique a seguinte fórmula:
A. Conte as células coloridas: = função de cor (A, B: C, FALSE)
B. Soma 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 em que você deseja calcular a contagem e a soma.

4. Pegue a seguinte captura de tela, 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, VERDADEIRO) para somar as células amarelas. Ver captura de tela:

5. Se você deseja contar e somar outras células coloridas, repita o passo 4. Então você obterá os seguintes resultados:


Contagem e soma de células com base na cor de preenchimento específica com as funções do Kutools

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

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

1. Selecione a célula em branco onde você colocou os resultados da contagem e clique em Kutools > Funções de Kutools > Estatística e Matemática > COUNTBYCELLCOLOR. Ver captura de tela:

2. Na caixa de diálogo Argumentos da Função, especifique o intervalo em que você contará as células coloridas dentro da Referência caixa, escolha a célula que é preenchida pela cor de fundo especificada no Color_index_nr caixa, e clique no OK botão. Ver captura de tela:

notas:
(1) Você também pode digitar a função Kutools especificada = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) na célula em branco ou na barra de fórmulas diretamente para obter os resultados da contagem;
(2) Clique Kutools > Funções de Kutools > Estatística e Matemática > SUMBYCELLCOLOR ou digite = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) na célula em branco diretamente para somar as 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 como mostrado abaixo:

Funções de Kutools contém um número de funções internas para ajudar os usuários do Excel a calcular facilmente, incluindo Células de contagem / soma / média visível, Contagem / soma pela cor da célula, Contagem / Soma por cor da fonte, Contar personagens, Contar por fonte em negrito, Etc. Tenha um teste grátis!


Contagem e células Sum com base na cor de preenchimento específica com o Kutools for Excel

Com a função definida pelo usuário acima, você precisa inserir a fórmula um a um, se houver muitas cores diferentes, esse método será tedioso e demorado. Mas se você tiver Kutools for Excel'S Count by Color 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 do alcance colorido.

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

1. Selecione o intervalo que você deseja usar e clique em Kutools Plus > Count by Color, veja a captura de tela:

2. E no Count by Color caixa de diálogo, faça como mostrado abaixo:
(1) Selecione Formatação padrão de Método de cor lista suspensa;
(2) Selecione fundo de Tipo de contagem lista suspensa.
(3) Clique no botão Gerar relatório.

notas: 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ê receberá uma nova pasta de trabalho com as estatísticas. Veja a captura de tela:

O Count by Color O recurso calcula as células (Contagem, Soma, Média, Máx, etc.) pela cor de fundo ou cor da fonte. Tenha um teste grátis!


Artigo relacionado:

Como contar / somar células com base nas cores da fonte no Excel?


Demo: Contagem e soma de células com base no plano de fundo, cor de formatação condicional:

Kutools for Excel inclui mais do que as ferramentas úteis do 300 para Excel, gratuitas para testar sem limitações em dias do 30. Faça o download e teste gratuito agora!

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

  • armadilha para peixes: Inserir rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de endereços e enviar e-mails ...
  • Bar Super Fórmula (facilmente editar várias linhas de texto e fórmula); Layout de leitura (leia e edite facilmente grandes números de células); Colar para intervalo filtrado...
  • Mesclar células / linhas / colunas sem perder dados; Conteúdo de células divididas; Combinar linhas / colunas duplicadas... Prevenir Células Duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Exclusivo Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros de trabalho; Seleção aleatória ...
  • Cópia exata Múltiplas Células sem alterar a referência da fórmula; Criar automaticamente 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 (salve e aplique 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 ...
  • Combinar pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Dividir dados em várias planilhas; Lote Converter xls, xlsx e PDF...
  • Mais de recursos poderosos do 300. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos Avaliação gratuita de um dia de 30.
kte tab 201905

A guia Office traz a interface com guias para o Office e torna seu trabalho muito mais fácil

  • Ativar edição e leitura com guias no 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!
fundo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.