No Excel, você pode criar fórmulas aninhadas em várias funções. É necessário compreender várias funções como: Logica, Matemática, Procura de referência, entre outras, para incorporar uma dentro da outra. Pegamos como base uma planilha de preços, onde o proprietário dos produtos concede descontos.
Para trabalhar com fórmulas complexas do Excel, é importante criar referências. Se você vai criar uma tabela para promover descontos, por exemplo, você cria em uma célula a opção Desconto, em seguida determina o valor, ou o percentual.
Exemplo: A2 = desconto e em A3 digita o valor. A3 é a referência.
Na tabela a seguir, temos os exemplos de fórmulas complexas. Copie e cole na célula A1 de uma nova planilha e tente reproduzir as fórmulas da última célula na células acima. Os valores vão surgindo e quando você alterar as referências os dados mudarão automaticamente.
| Tabela de preços com desconto | Digite apenas nas células pintadas | ||||||
| TAXA MENSAL | Data | DESCONTO | Total parcelas | Prazo medio | |||
| 2,00% | 18-jan-18 | 20,00% | 6 | 100 dias | |||
| APARELHOS NOVOS | |||||||
| No | MODELO | PREÇO AVISTA | ENTRADA FIXA | VALOR FINANCIADO | 6 VEZES | VALOR A PAGAR | PREÇO DESCONTO |
| 1 | Produto 01 | 100,00 | 106,82 | 17,80 | 106,82 | 83,33 | |
| 2 | Produto 02 | 41,00 | 43,80 | 7,30 | 43,80 | 34,17 | |
| 3 | Produto 03 | 54,00 | R$ 15,00 | 42,68 | 7,11 | 57,68 | 45,00 |
| 4 | Produto 04 | 111,00 | 118,57 | 19,76 | 118,57 | 92,50 | |
| 5 | Produto 05 | 38,00 | 40,59 | 6,77 | 40,59 | 31,67 | |
| 6 | Produto 06 | 100,00 | 106,82 | 17,80 | 106,82 | 83,33 | |
| 7 | Produto 07 | 413,00 | 441,18 | 73,53 | 441,18 | 344,17 | |
| 8 | Produto 08 | 512,00 | 546,94 | 91,16 | 546,94 | 426,67 | |
| 9 | Produto 09 | 869,00 | 928,30 | 154,72 | 928,30 | 724,17 | |
| 10 | Produto 10 | 1.750,00 | 1.869,41 | 311,57 | 1.869,41 | 1.458,33 | |
| 11 | Produto 11 | 208,00 | 222,19 | 37,03 | 222,19 | 173,33 | |
Copie as fórmulas a seguir e cole nas células indicadas e depois copie para as células seguintes na mesma coluna, se solicitado.
Fórmula para a célula G3.
=SE(F3>=1;PROCV(F3;Plan2!B2:G25;2);0)
Fórmula para a Célula E6. Cole também nas células seguintes da mesma coluna.
=SE($F$3<=0;0;ARRED(VF($B$3;$G$3/30;0;-C6);2))-D6
Fórmula para a Célula F6. Cole também nas células seguintes da mesma coluna.
=SE($F$3>=1;E6/$F$3;0)
Fórmula para a Célula G6. Cole também nas células seguintes da mesma coluna.
=E6+D6
Fórmula para a Célula H6. Cole também nas células seguintes da mesma coluna.
=VPL($D$3;C6)
Você pode criar várias planilhas com base nas fórmulas complexas e quando quiser alterar qualquer dado, nas células de referências, todos os dados vinculados a ela, serão automaticamente atualizados. O ganho de tempo é significativo, quando você trabalha com planilhas eletrônicas construídas com fórmulas.
6 Comentários
Estou automatizando um relatório, porém não consigo montar uma fórmula para uma das colunas cujo os valores estão em outra aba da mesma planilha, mas possuem diversos valores que se repetem então só com o Procv não é possível buscar os valores. Como poderia fazer?
Há várias formas de fazer isso. Se não quiser valores repetidos deve criar uma coluna para excluir valores repetidos, ou somar os valores com. Valores podem ser somados com a função SOMASE. depois é só buscar os dados na outra planilha com PROCV pode resolver.
Bom Dia!
Edivaldo,
Preciso de ajuda, tenho uma planilha para fazer e nao estou conseguindo fazer, é um pouco complexa, vou te explicar,
tenho que limitar na coluna, se for abaixo de 70% tem que 0 e se for acima de 130% tem que dar 130 e se for entre 70 a 130% tenho que deixar o valor real.
Oi André Luis Fernandes Minoru.
Obrigado por acessar o site tudoexcel.com.br este cálculo pode ser feito com a função SE. Por exemplo: se você tiver uma célula como referência, para o valor real. Provavelmente você á o valor total, ou valores totais, para criar o percentual e a partir dele, ou deles, achar o resultado da porcentagem.
Este link tem exemplos de fórmulas com a função SE: https://www.tudoexcel.com.br/planilhas/funcao-se-do-excel-24.html
SUA FÓRMULA FICA MAIS OU MENOS ASSIM:
Só tenho uma dúvida. Abaixo de 70% do que? Você tem que ter o valor bruto e o valor líquido, este seria o valor calculado de 70%. Bom, você também deve colocar os valores na ordem crescente na fórmula. Por exemplo =SE(A1<70%;0;SE(A1<=130%;130;b1)) Lendo a fórmula: se A1 for menor que 70%;mostra ZERO;se A1 for maior ou igual a 130%;mostra 130;caso contrário mostra o valor real. O que você precisa é ter as referências para os valores reais.
Olá! Pode me ajudar ?
Preciso saber como deixar quatro células de uma função "fixas" onde há soma e multiplicação .
Ex.: = (D5*E5*J5 )+(G5*H5*AN5).
Preciso que D5, E5 e G5, H5 permaneçam assim nas 20 colunas seguintes e só variem as outras duas células.. Obrigada.
Oi Erika.
Para fixar as células na fórmula é necessário usar $ antes e depois da letra da coluna.
Eu vou pegar seu exemplo que deve ficar mais ou menos assim: =($D$5*$E$5*$J$5)+($G$5*H5*AN5).
É isso!