Ir para o conteúdo principal

Dicas do Excel: contar/soma células por cor (fundo, fonte, formatação condicional)

Nas tarefas diárias, a marcação por cores é um método popular para distinguir e destacar rapidamente dados cruciais. Mas, como contamos ou somamos os dados das células com base em cores específicas (cor de preenchimento, cor da fonte, formatação condicional)? Por padrão, o Excel não oferece um recurso direto para contar ou somar por cor. No entanto, com alguns truques e métodos indiretos, ainda podemos conseguir isso. Este artigo explorará como contar ou somar dados por cor.

Conte e some células com base na cor de fundo

Contar e somar células com base na cor da fonte

Contar e somar células com base na cor da formatação condicional


Vídeo: contar e somar células com base na cor


Conte e some células com base na cor de fundo

Por exemplo, se você tiver um intervalo de dados onde os valores são preenchidos com diferentes cores de fundo, conforme mostrado na imagem abaixo. Para contar ou somar as células com base em uma cor específica, o Excel não oferece um recurso direto para contar ou somar células com base na cor de fundo. No entanto, com um pouco de criatividade e algumas técnicas úteis, você pode realizar essa tarefa. Vamos explorar alguns métodos úteis nesta seção.


Conte e some células por cor de fundo com função definida pelo usuário

Aqui, mostraremos como criar e usar essa função definida pelo usuário para resolver esta tarefa no Excel. Siga as seguintes etapas:

Passo 1: Abra o editor do módulo VBA e copie o código

  1. Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
  2. Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
  3. Em seguida, copie e cole o código abaixo no módulo em branco.
    Código VBA: conte e some células com base na cor de fundo
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Etapa 2: crie fórmulas para contar e somar células por cor de fundo

Após colar o código acima, feche a janela do módulo e aplique as seguintes fórmulas:

  • Conte as células com base na cor de fundo específica:
    Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Em seguida, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note: Nesta fórmula, G2 é a célula de referência com a cor de fundo específica que você deseja combinar; $B$2:$E$12 é o intervalo onde se deseja contar o número de células da cor do G2; FALSE é usado para contar células com cores correspondentes.
  • Soma as células com base na cor de fundo específica:
    Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Em seguida, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note: Nesta fórmula, G2 é a célula de referência com a cor de fundo específica que você deseja combinar; $B$2:$E$12 é o intervalo onde se deseja contar o número de células da cor do G2; VERDADEIRO é usado para somar células com cores correspondentes.

Conte e some células por cor de fundo com um recurso poderoso

Para quem não está familiarizado com programação, o VBA pode parecer bastante complexo. Aqui, apresentaremos uma ferramenta poderosa - Kutool para Excel, as TIC Contagem por Cor recurso permite calcular facilmente (contagem, soma, média, etc.) com base na cor de fundo em apenas alguns cliques. Impressionantemente, Contagem por Cor O recurso vai além das cores de fundo – ele também pode diferenciar e calcular com base nas cores das fontes e na formatação condicional.

Depois de baixando e instalando Kutools para Excel, primeiro selecione o intervalo de dados que deseja contar ou somar células com base em uma cor de fundo específica. A seguir, navegue até Kutools Plus e escolha Contagem por Cor.

Na série Contagem por Cor caixa de diálogo, especifique as operações:

  1. Selecionar Formatação padrão do Método de cor lista suspensa;
  2. Especificar BACKGROUND do Tipo de contagem lista suspensa e você pode visualizar os resultados estatísticos para cada cor de fundo na caixa de diálogo;
  3. Finalmente, clique em Gerar relatório para exportar os resultados calculados para uma nova pasta de trabalho.

Resultado:

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

Tips:
  1. A Contagem por Cor O recurso também suporta contagem e soma de células com base na cor da fonte padrão, fundo ou cor da fonte da formatação condicional e uma combinação de cores de preenchimento e formatação condicional.
  2. Interessado neste recurso, por favor clique para baixar para obter uma avaliação gratuita por 30 dias.

Conte e some células por cor de fundo com a função Filtro e SUBTOTAL

Supondo que temos uma tabela de vendas de frutas conforme a captura de tela mostrada abaixo, e contaremos ou somaremos as células coloridas no Valor coluna.

Etapa 1: aplique a função SUBTOTAL

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

  • Para contar todas as células com a mesma cor de fundo, insira a fórmula:
    =SUBTOTAL(102, F2:F16)
  • Para somar todas as células com a mesma cor de fundo, insira a fórmula;
    =SUBTOTAL(109, F2:F16)
  • Note: nas fórmulas acima, 102 representa contar valores numéricos em uma lista filtrada enquanto exclui células ocultas; 109 representa somar valores em uma lista filtrada excluindo células ocultas; F2: F16 é o intervalo no qual a contagem ou a soma serão calculadas.

Etapa 2: filtrar células com base em cores específicas

  1. Selecione o cabeçalho da tabela e clique em Data > filtros. Veja a imagem:
  2. Clique na filtros ícone  na célula de cabeçalho do Valor coluna e clique Filtrar por Cor e a cor especificada pela qual você contará sucessivamente. Veja a imagem:

Resultado:

Após a filtragem, as fórmulas SUBTOTAL contam e somam automaticamente as células coloridas no Valor coluna. Veja a imagem:

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

Contar e somar células com base na cor da fonte

Quer contar ou somar células com base na cor da fonte no Excel? Digamos que você tenha os dados, como na captura de tela fornecida, com células contendo textos nas cores vermelho, azul, laranja e preto. O Excel não facilita isso por padrão. Mas não se preocupe! Nesta seção, mostraremos alguns truques simples para fazer exatamente isso.


Conte e some células com base na cor da fonte com função definida pelo usuário

Para contar e somar células com cores de fonte específicas, a seguinte função definida pelo usuário pode ajudá-lo a resolver esta tarefa. Siga as seguintes etapas:

Passo 1: Abra o editor do módulo VBA e copie o código

  1. Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
  2. Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
  3. Em seguida, copie e cole o código abaixo no módulo em branco.
    Código VBA: conte e some células com base na cor da fonte
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Etapa 2: crie fórmulas para contar e somar células por cor da fonte

Após colar o código acima, feche a janela do módulo e aplique as seguintes fórmulas:

  • Conte as células com base na cor da fonte específica:
    Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Em seguida, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note: Nesta fórmula, G2 é a célula de referência com a cor de fonte específica que você deseja combinar; $B$2:$E$12 é o intervalo onde você deseja contar o número de células da cor do G2.
  • Soma as células com base na cor da fonte específica:
    Copie ou digite a fórmula fornecida abaixo na célula desejada para o resultado. Em seguida, arraste a alça de preenchimento para baixo para obter outros resultados. Veja a captura de tela:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note: Nesta fórmula, G2 é a célula de referência com a cor de fonte específica que você deseja combinar; $B$2:$E$12 é o intervalo onde você deseja contar o número de células da cor do G2.

Conte e some células com base na cor da fonte com um recurso fácil

Procurando contar ou somar facilmente os valores das células no Excel com base na cor da fonte? Mergulhar Kutools for Excel's Contagem por Cor recurso! Com esta ferramenta inteligente, contar e somar células por cor de fonte específica torna-se muito fácil. Descubra como Kutools pode transformar sua experiência no Excel.

Depois de baixando e instalando Kutools para Excel, primeiro selecione o intervalo de dados que deseja contar ou somar células com base em uma cor de fonte específica. Então clique Kutools Plus > Contagem por Cor para abrir o Contagem por Cor caixa de diálogo.

Na série Contagem por Cor caixa de diálogo, especifique as operações:

  1. Selecionar Formatação padrão do Método de cor lista suspensa;
  2. Especificar fonte do Tipo de contagem lista suspensa e você pode visualizar os resultados estatísticos para cada cor de fonte na caixa de diálogo;
  3. Finalmente, clique em Gerar relatório para exportar os resultados calculados para uma nova pasta de trabalho.

Resultado:

Agora, você tem uma nova pasta de trabalho exibindo estatísticas detalhadas com base na cor da fonte. Veja a captura de tela:

Tips: Interessado neste recurso, por favor clique para baixar para obter uma avaliação gratuita por 30 dias.

Contar e somar células com base na cor da formatação condicional

No Excel, normalmente você pode usar a Formatação Condicional para aplicar cores específicas às células que atendem a determinados critérios, tornando a visualização dos dados intuitiva. Mas e se você precisar contar ou somar essas células especialmente formatadas? Embora o Excel não ofereça uma maneira direta para isso, aqui estão algumas maneiras de contornar essa limitação.


Conte e some células formatadas condicionalmente com código VBA

Contar e somar células formatadas condicionalmente no Excel não é simples usando funções integradas. No entanto, você pode realizar essa tarefa usando código VBA. Vejamos como você pode usar o VBA para isso:

Passo 1: Abra o editor do módulo VBA e copie o código

  1. Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
  2. Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
  3. Em seguida, copie e cole o código abaixo no módulo em branco.
    Código VBA: contar e somar células com base na cor de formatação condicional
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Etapa 2: execute este código VBA

  1. Depois de colar o código, pressione F5 chave para executar este código, uma caixa de prompt aparecerá, selecione o intervalo de dados onde deseja contar e somar as células com base na formatação condicional. Então clique OK, Veja a captura de tela:
  2. Em outra caixa de prompt, selecione uma cor de formatação condicional específica que você deseja contar e somar e clique em OK botão, veja a captura de tela:

Resultado:

Agora, o resultado, que inclui a contagem e a soma das células com a cor de formatação condicional especificada, será exibido na caixa exibida. Veja a captura de tela:


Conte e some células formatadas condicionalmente com um recurso inteligente

Se você está procurando outros métodos rápidos e fáceis para contar e somar células formatadas condicionalmente, Kutools for Excel é a sua solução ideal. Isso é Contagem por Cor recurso pode resolver essa tarefa em apenas alguns cliques. Mergulhe para descobrir a eficiência e a precisão que o Kutools pode trazer ao seu fluxo de trabalho.

Depois de baixando e instalando Kutools para Excel, primeiro selecione o intervalo de dados que deseja contar ou somar células com base em uma cor de formatação condicional específica. Então clique Kutools Plus > Contagem por Cor para abrir o Contagem por Cor caixa de diálogo.

Na série Contagem por Cor caixa de diálogo, especifique as operações:

  1. Selecionar Formatação condicional do Método de cor lista suspensa;
  2. Especificar BACKGROUND do Tipo de contagem lista suspensa e você pode visualizar os resultados estatísticos para cada cor de formatação da condição na caixa de diálogo;
  3. Finalmente, clique em Gerar relatório para exportar os resultados calculados para uma nova pasta de trabalho.

Resultado:

Agora, você tem uma nova pasta de trabalho exibindo estatísticas detalhadas com base na cor da formatação condicional. Veja a captura de tela:

Tips: Interessado neste recurso, por favor clique para baixar para obter uma avaliação gratuita por 30 dias.

Artigos relacionados:

  • Se a cor da fonte for vermelha, retorne um texto específico
  • Como você pode retornar um texto específico se a cor da fonte for vermelha em outra célula, conforme a captura de tela mostrada abaixo? Neste artigo, apresentarei alguns truques para realizar algumas operações com base no texto da fonte vermelha no Excel.
  • Filtrar dados por várias cores
  • Normalmente, no Excel, você pode filtrar rapidamente as linhas com apenas uma cor, mas você já pensou em filtrar as linhas com várias cores ao mesmo tempo? Neste artigo, vou falar sobre um truque rápido para você lidar com esse problema.
  • Adicionar cor à lista suspensa
  • No Excel, criar uma lista suspensa pode ajudar muito e, às vezes, você precisa codificar por cores os valores da lista suspensa dependendo do correspondente selecionado. Por exemplo, criei uma lista suspensa com os nomes das frutas, quando seleciono Apple, preciso que a célula seja colorida com vermelho automaticamente, e quando escolho Laranja, a célula pode ser colorida com laranja.
  • Colorir linhas alternativas para células mescladas
  • É muito útil formatar linhas alternadas com uma cor diferente em dados grandes para que possamos verificar os dados, mas, às vezes, pode haver algumas células mescladas em seus dados. Para destacar as linhas alternadamente com uma cor diferente para as células mescladas, conforme a captura de tela abaixo, como você pode resolver esse problema no Excel?