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 -
# 1 - Soma por cor usando a função subtotal
Para entender a abordagem de cálculo da soma dos valores por 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 com valores negativos para fins de indicação, isso pode ser obtido aplicando a formatação condicional ou destacando as células manualmente, como 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 visto na imagem acima, um somatório do valor em dólares norte-americanos foi calculado 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 da célula vermelha clara 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 foi 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 captura de tela acima, o cálculo da célula colorida é realizado 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 coloridas no Excel, conforme discutido 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 estamos dispostos a chegar 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 usando 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 esquecendo .CELL em 'Referir a' como 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 se refere às informações do código da célula e o segundo argumento é o número da célula D2 se refere à 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 SUMIF é a seguinte: -
Como pode ser visto na imagem acima, os seguintes argumentos são inseridos na fórmula SOMASE: -
- O argumento de intervalo é inserido para o intervalo de células E2: E13.
- Os critérios são inseridos como G2, cujos valores resumidos devem 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 nas diferentes colunas, o SUBTOTAL só pode mostrar o resultado correto de 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 códigos de cores múltiplos no plano de fundo da célula.
