Como somar por cores no Excel? (2 métodos úteis)
Os 2 principais métodos para somar por cores no Excel são os seguintes -
- Uso da fórmula SUBTOTAL no Excel e filtro por função de cor.
- Aplicar a fórmula GET.CELL definindo o nome na guia da fórmula e aplicando a fórmula SUMIF no Excel para resumir os valores por códigos de cores.
Vamos discutir cada um deles em detalhes -
Você pode baixar este modelo Excel Soma por Cor aqui - Modelo Excel Soma por Cor# 1 - Soma por cor usando a função subtotal
Para entender a abordagem de cálculo da soma dos valores pelas cores de fundo, consideremos a tabela de dados abaixo que fornece detalhes dos valores em US $ por região e mês.
- Passo 1: Suponha que gostaríamos de destacar as células que têm valores negativos para fins de indicação, isso pode ser obtido aplicando a formatação condicional ou destacando as células manualmente, conforme mostrado abaixo.
- Passo 2: Agora, para obter a soma das células coloridas no Excel, insira a fórmula para SUBTOTAL abaixo da tabela de dados. A sintaxe para a fórmula SUBTOTAL é mostrada abaixo.
A fórmula que é inserida para calcular a soma é
= SUBTOTAL (9, D2: D13)
Aqui, o número '9' no argumento function_num refere-se à funcionalidade de soma e o argumento de referência é fornecido como o intervalo de células a serem calculadas. Abaixo está a imagem do mesmo.
- Etapa 3: como pode ser visto na imagem acima, foi calculada uma soma do valor em dólares americanos. Para calcular os valores destacados em uma cor de fundo vermelha clara. Aplique o filtro à tabela de dados acessando Dados e selecionando um filtro.
- Etapa 4: Em seguida, selecione o filtro por cor e escolha a cor vermelha clara da célula em 'Filtrar por cor da célula'. Abaixo está a captura de tela para descrever melhor o filtro.
- Etapa 5: uma vez que o filtro do Excel tenha sido aplicado, a tabela de dados será filtrada apenas para células de fundo vermelho claro e a fórmula de subtotal aplicada na parte inferior da tabela de dados exibirá a soma das células coloridas que são filtradas conforme mostrado abaixo.
Conforme mostrado na imagem acima, o cálculo da célula colorida é obtido na célula E17, fórmula subtotal.
# 2 - Soma por cor usando a função Get.Cell
A segunda abordagem é explicada para chegar à soma das células de cores no Excel é discutida no exemplo abaixo. Considere a tabela de dados para entender melhor a metodologia.
- Passo 1: Agora vamos destacar a lista de células na coluna Valor em USD que desejamos obter na soma desejada de células coloridas, conforme mostrado abaixo.
- Passo 2: Como podemos ver na imagem acima, ao contrário do primeiro exemplo aqui, temos várias cores. Assim, estaremos usando a fórmula = GET.CELL , definindo-o dentro da caixa de nome e não diretamente no excel.
- Passo 3: Agora, uma vez que a caixa de diálogo para 'Definir Nome' aparecer, insira o Nome e a fórmula para GET.CELL em 'Referir a' conforme mostrado na imagem abaixo.
Conforme visto na captura de tela acima, o Nome inserido para a função é 'CellColor' e a fórmula = GET.CELL (38, 'Exemplo 2! $ D2) deve ser inserida em' Refere-se a '. Dentro da fórmula, o número 38 refere-se às informações do código da célula e o segundo argumento é o número da célula D2 refere-se à célula de referência. Agora clique em OK.
- Etapa 4: Agora insira o nome da função 'CellColor' na célula ao lado da célula codificada por cores que foi definida na caixa de diálogo conforme explicado na etapa 3.
Como pode ser visto na imagem acima, a função 'CellColor' é inserida, que retorna o código de cor para a cor da célula de fundo.
Da mesma forma, a fórmula é arrastada para a coluna inteira.
- Passo 5: Agora para chegar à soma dos valores por cores no excel, estaremos inserindo a fórmula SUMIF. A sintaxe da fórmula SOMASE é a seguinte: -
Como pode ser visto na imagem acima, os seguintes argumentos são inseridos na fórmula SUMIF: -
- O argumento de intervalo é inserido para o intervalo de células E2: E13
- Os critérios são inseridos como G2 cujos valores resumidos precisam ser recuperados.
- O intervalo de células é inserido como D2: D13
A fórmula SUMIF é arrastada para baixo para todos os números de código de cores para os quais os valores devem ser somados.
Coisas para lembrar
- Ao usar a abordagem por fórmula SUBTOTAL, esta funcionalidade permite que os usuários filtrem apenas uma cor de filtro por vez. Além disso, esta funcionalidade pode ser usada para adicionar apenas uma coluna de valores por cores de filtro. Se houver mais de uma coluna com cores diferentes por linhas na coluna diferente, o SUBTOTAL pode mostrar apenas o resultado correto para um filtro por cor para uma coluna específica.
- A fórmula GET.CELL em combinação com a abordagem SUMIF elimina essa limitação, pois essa funcionalidade pode ser usada para resumir por cores para vários códigos de cores no plano de fundo da célula.
