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
- Usando a função definida pelo usuário
- Usando um recurso poderoso – Kutools para Excel
- Usando Filtro e SUBTOTAL
Contar e somar células com base na cor da fonte
Contar e somar células com base na cor da formatação condicional
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
- Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
- Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
- 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 fundoFunction 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.
No Contagem por Cor caixa de diálogo, especifique as operações:
- Selecionar Formatação padrão do Método de cor lista suspensa;
- 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;
- 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:
- 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.
- 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
- Selecione o cabeçalho da tabela e clique em Data > filtros. Veja a imagem:
- 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:
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
- Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
- Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
- 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 fonteFunction 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.
No Contagem por Cor caixa de diálogo, especifique as operações:
- Selecionar Formatação padrão do Método de cor lista suspensa;
- 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;
- 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:
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
- Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
- Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
- 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 condicionalSub 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
- 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:
- 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.
No Contagem por Cor caixa de diálogo, especifique as operações:
- Selecionar Formatação condicional do Método de cor lista suspensa;
- 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;
- 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:
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?
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab 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!
Índice
- Vídeo
- Conte e some células com base na cor de fundo
- Usando a função definida pelo usuário
- Usando um recurso poderoso – Kutools para Excel
- Usando Filtro e SUBTOTAL
- Contar e somar células com base na cor da fonte
- Usando a função definida pelo usuário
- Usando um recurso fácil – Kutools para Excel
- Contar e somar células com base na cor da formatação condicional
- Usando código VBA
- Usando um recurso inteligente – Kutools para Excel
- Artigos Relacionados
- As melhores ferramentas de produtividade para escritório
- Comentários