Os cookies nos ajudam a oferecer nossos serviços. Ao utilizar nossos serviços, você concorda com a nossa utilização de cookies.
Dica: outros idiomas são traduzidos pelo Google. Você pode visitar o English versão deste link.
Entrar
x
or
x
x
Cadastre-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 (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. Full Free Trial 60-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 data > 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 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.

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.

Kutools for Excel - Inclui mais de 300 ferramentas úteis do Excel. Avaliação gratuita de recurso completo 60-day, sem necessidade de cartão de crédito! Comece já!

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:


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:

Neste vídeo, o Kutools guia e Kutools Plus guia são adicionados por Kutools for Excel. Se precisar, por favor clique aqui para ter um teste gratuito do dia 60 sem limitação!



Ferramentas de produtividade recomendadas

Office Tab

ouro star1 Traga guias úteis para o Excel e outros softwares do Office, como o Chrome, o Firefox e o novo Internet Explorer.

Kutools for Excel

ouro star1 Surpreendente! Aumente sua produtividade em minutos 5. Não precisa de habilidades especiais, economize duas horas por dia!

ouro star1 300 Novos recursos para o Excel, Faça o Excel muito fácil e poderoso:

  • Mesclar células / linhas / colunas sem perder dados.
  • Combine e Consolide várias folhas e pastas de trabalho.
  • Comparar intervalos, copiar intervalos múltiplos, converter texto para data, unidade e conversão de moeda.
  • Contagem de cores, subtotais de paginação, classificação avançada e super filtro,
  • Mais Selecionar / Inserir / Excluir / Texto / Formato / Link / Comentário / Guias de trabalho / Ferramentas de trabalho ...

Tira de tela de Kutools para Excel

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.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 25 days ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 9 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 25 days ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.
  • To post as a guest, your comment is unpublished.
    Hredoy · 7 months ago
    i want to colour in excel by using if function.