Se quiser obter a soma de uma coluna apenas usando o nome da coluna, você pode fazer isso de 3 maneiras fáceis no Excel. Vamos explorar essas maneiras.
Ao contrário de outros artigos, vamos ver o cenário primeiro.
Aqui eu tenho uma tabela de vendas feita por vendedores diferentes em meses diferentes.
Agora, a tarefa é obter a soma das vendas de determinado mês na Célula C10. Se alterarmos o mês em B10, a soma deve mudar e retorna a soma desse mês, sem alterar nada na fórmula.
Método 1: Soma toda a coluna na tabela usando a função SUMPRODUCT.
A sintaxe do método SUMPRODUCT para somar a coluna correspondente é:
| = SUMPRODUCT ((colunas) * (cabeçalhos = cabeçalho)) |
Colunas:É o intervalo bidimensional das colunas que você deseja somar. Não deve conter cabeçalhos. Na tabela acima, é C3: N7.
Cabeçalhos:É o intervalo de cabeçalho de colunas que você deseja somar. Nos dados acima, é C2: N2.
Cabeçalho: É o título que você deseja corresponder. No exemplo acima, está em B10.
Sem mais delongas, vamos usar a fórmula.
| = SUMPRODUTO ((C3: N7) * (C2: N2 = B10)) |
e isso vai retornar:
Como funciona?
É simples. Na fórmula, a declaraçãoC2: N2 = B10 retorna uma matriz que contém todos os valores FALSE, exceto um que corresponda a B10. Agora a fórmula é
| =SUMPRODUCT ((C3: N7) * {FALSE, FALSE, FALSE, FALSE,VERDADE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Agora C3: N7 é multiplicado para cada valor desta matriz. Cada coluna se torna zero, exceto a coluna que é multiplicada por TRUE. Agora a fórmula se torna:
| = SUMPRODUCT ({0,0,0,0,6,0,0,0,0,0,0,0; 0,0,0,0,12,0,0,0,0,0,0, 0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0,0}) |
Agora, esta matriz é somada e obtemos a soma da coluna que corresponde à coluna na célula B10.
Método 2: Soma da coluna inteira na tabela usando a função INDEX-MATCH.
A sintaxe do método para somar o título da coluna correspondente no Excel é:
| = SUM (INDEX (colunas ,, MATCH (cabeçalho, cabeçalhos, 0))) |
Todas as variáveis neste método são iguais ao método SUMPRODUCT. Vamos apenas implementá-lo para resolver o problema. Escreva esta fórmula em C10.
| = SOMA (ÍNDICE (C3: N7,, CORRESPONDÊNCIA (B10, C2: N2,0))) |
Isso retorna:
Como funciona?
A fórmula é resolvida de dentro para fora. Primeiro, a função MATCH retorna o índice do mês correspondente no intervalo C2: N2. Como temos maio em B1o, obtemos 5. Agora, a fórmula torna-se
| = SOMA (ÍNDICE (C3: N7,, 5)) |
Em seguida, a função INDEX retorna valores da 5ª coluna de C3: N7. Agora a fórmula se torna:
| = SOMA ({6; 12; 15; 15; 8}) |
E, finalmente, obtemos a soma desses valores.
Método 3: soma da coluna inteira na tabela usando intervalo nomeado e função INDIRETA
Tudo fica mais simples se você nomear seus intervalos como títulos de coluna. Neste método, primeiro precisamos nomear as colunas como seus nomes de cabeçalho.
Selecione a tabela incluindo os títulos e pressione CTRL + SHIFT + F3. Ele abrirá uma caixa de diálogo para criar um nome a partir dos intervalos. Verifique a linha superior e clique no botão OK.
Ele nomeará todas as colunas de dados como seus títulos.
Agora, a fórmula genérica para somar a coluna correspondente será:
| = SOMA (INDIRETO (título)) |
Cabeçalho: É o nome da coluna que você deseja somar. Neste exemplo, é B10 que contém maio a partir de agora.
Para implementar essa fórmula genérica, escreva-a na célula C10.
| = SOMA (INDIRETO (B10)) |
Isso retorna a soma do mês de maio:
Outro método é semelhante a este. Neste método, usamos tabelas do Excel e sua nomenclatura estruturada. Digamos que você tenha nomeado a tabela acima como tabela1. Então, essa fórmula funcionará da mesma forma que a fórmula acima.
| = SOMA (INDIRETO ("Tabela1 [" & B10 & "]")) |
Como funciona?
Nesta fórmula, a função INDIRETO pega a referência do nome e a converte em referência de nome real. O procedimento em diante é simples. A função SUM resume o intervalo nomeado.
Então, sim pessoal, é assim que você pode somar a coluna correspondente no Excel. Espero que seja útil e explicativo para você. Se você tiver alguma dúvida sobre este artigo ou qualquer outro tópico relacionado ao Excel / VBA, pergunte na seção de comentários abaixo.
Como somar por correspondência de linha e coluna no Excel.O SUMPRODUCT é a função mais versátil quando se trata de somar e contar valores com critérios complicados. A função genérica para somar por correspondência de coluna e linha é …
SUMIF com referência 3D no Excel |O fato engraçado é que a referência normal do Excel 3D não funciona com funções condicionais, como a função SOMASE. Neste artigo, aprenderemos como fazer a referência 3D funcionar com a função SUMIF.
Referência relativa e absoluta no Excel | Referenciar no Excel é um tópico importante para todo iniciante. Até mesmo usuários experientes do Excel cometem erros ao fazer referência.
Referência de planilha dinâmica | Forneça planilhas de referência dinamicamente usando a função INDIRETA do Excel. Isso é simples …
Expandindo referências no Excel | A referência de expansão se expande quando copiada para baixo ou para a direita. Usamos o sinal $ antes do número da coluna e da linha para fazer isso. Aqui está um exemplo …
Tudo sobre referência absoluta | O tipo de referência padrão no Excel é relativo, mas se você quiser que a referência de células e intervalos seja absoluta, use o sinal $. Aqui estão todos os aspectos da referência absoluta no Excel.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos tornarão seu trabalho ainda mais rápido no Excel.
A função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
CONT.SE no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar um valor específico. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.