Neste artigo, aprenderemos como criar coluna (s) calculada (s) no Excel.
Cenário
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 os diferentes cenários primeiro.
Como criar uma coluna calculada em uma tabela:
- Selecione uma célula em uma das colunas de uma Tabela ou uma célula em branco até a última coluna, no exemplo abaixo a célula selecionada é G2.
- Insira a fórmula que calcula as vendas por unidade, digite o símbolo de igual (=), selecione a célula F2, digite o símbolo de divisão (/), selecione a célula E2 e pressione Enter.
A fórmula é copiada automaticamente para todas as células da coluna.
Exemplo :
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 retornar a soma daquele 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ção C2: 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 é:
| =SOMA(ÍNDICE(colunas,,PARTIDA(título, 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 ,,PARTIDA(B10, C2: N2,0))) |
Isso retorna:
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(cabeçalho)) |
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 e "]")) |
Espero que este artigo sobre como criar coluna (s) calculada (s) no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
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ências.
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 :
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
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.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.