Digamos que você tenha uma lista de produtos com seus preços em uma tabela do Excel. Agora você precisa criar pacotes que podem conter produtos diferentes dos itens disponíveis. Agora, no final, você gostaria de calcular o preço total de cada pacote. Por uma vez, você pode fazer isso manualmente, mas se essa é sua tarefa normal, então é melhor automatizar essa tarefa com algumas fórmulas elegantes. E é para isso que serve este artigo. Aprenderemos como calcular o preço de pacotes ou grupos de itens usando uma fórmula.
Fórmula genérica:
| = SUMPRODUCT (price_range, - (check_range = "y")) |
faixa de preço:É a faixa que contém o preço dos produtos.
intervalo_de_ verificação: É a faixa em que queremos colocar nosso cheque. Se um produto faz parte do pacote, colocamos y na seção transversal do pacote e do produto.
"y"= Esta é a verificação que estamos colocando para incluir um produto no pacote.
Vamos dar um exemplo para esclarecer o conceito.
Exemplo: Criar Fórmula para Precificação de Pacotes no Excel.
Pegamos o mesmo cenário que discutimos no início. Preparamos a tabela no intervalo B2: F9. Vamos identificar as variáveis de que precisamos.
Faixa de preço:A faixa de preço é C2: C9. Uma vez que é fixo, podemos ou intervalo nomeado ou usar a referência absoluta dele. Neste exemplo, usarei a referência absoluta $ C $ 2: $ C $ 9.
Check_range:Este é o intervalo que contém os cheques (a coluna do pacote). Eles são D3: D9, E3: E9 e F3: f9.
Vamos colocar esses valores na fórmula genérica.
Escreva esta fórmula em D10 para calcular o preço do pacote.
| = SUMPRODUCT ($ C $ 3: $ C $ 9, - (D3: D9 = "y")) |
Pressione Enter. Você tem o custo do pacote do pacote 1 calculado na célula D10. Copie esta fórmula para células adjacentes para calcular o preço de todos os pacotes.
Como funciona?
A fórmula funciona de dentro para fora. Então primeiro -(D3: D9 = "y") está resolvido.
Isso retorna uma matriz de 1 e 0 como. 1 para cada y e 0 qualquer outra coisa no intervalo D3: D9.
| {1;1;0;1;0;1;0} |
Em seguida, $ C $ 3: $ C $ 9 é convertido em uma matriz que contém o preço de cada item / produto.
| {100;200;20;10;12;15;25} |
Agora, a função SUMPRODUCT inclui isso.
| = SUMPRODUTO ({100; 200; 20; 10; 12; 15; 25}, {1; 1; 0; 1; 0; 1; 0}) |
Agora, como a função SUMPRODUCT faz, ela multiplica cada valor em um array para o mesmo array indexado em outro array e finalmente soma esses valores. Isso significa que cada preço que corresponde a 0 em outra matriz é transformado em 0. {100; 200; 0; 10; 0; 15; 0}. Agora, esta matriz é somada. Isso nos dá 325 para o pacote 1. O mesmo é feito para todos os pacotes.
Fórmula alternativa:
A fórmula alternativa é, obviamente, a função SOMASE e SOMASE.
| = SOMASE (D3: D9, "y", $ C $ 3: $ C $ 9) |
e
| = SUMIFS ($ C $ 3: $ C $ 9, D3: D9, "y") |
Estas são as respostas clássicas, mas a fórmula SUMPRODUCT é mais rápida e sofisticada também.
Então sim pessoal, é assim que você pode calcular o preço do pacote no Excel facilmente. Espero que tenha sido explicativo o suficiente. Se eu perdi algum ponto ou você tiver alguma dúvida a respeito deste artigo ou qualquer outra dúvida relacionada ao excel, pergunte na seção de comentários abaixo.
Contar correspondências totais em dois intervalos no Excel | Aprenda a contar o total de correspondências em dois intervalos usando a função SUMPROUDCT.
SUMIFS usando lógica AND-OR | O SUMIFS também pode ser usado com a lógica OR. A lógica padrão que SUMIFS usa é a lógica AND.
SUMPRODUCT com lógica IF | Aprenda a usar SUMPRODUCT com a lógica IF sem usar a função IF na fórmula.
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar 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.
COUNTIF 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.