Criando Fórmulas Complexas no Excel

Dê um Like ❱❱+2
Dê um Like ❱❱+2

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 descontoDigite apenas nas células pintadas
TAXA MENSALDataDESCONTOTotal  parcelasPrazo medio
2,00%18-jan-1820,00%6100 dias
APARELHOS NOVOS
NoMODELOPREÇO AVISTAENTRADA FIXAVALOR FINANCIADO 6 VEZESVALOR A PAGARPREÇO DESCONTO
1Produto 01         100,00           106,82             17,80         106,82             83,33
2Produto 02           41,00             43,80               7,30           43,80             34,17
3Produto 03           54,00 R$  15,00             42,68               7,11           57,68             45,00
4Produto 04         111,00           118,57             19,76         118,57             92,50
5Produto 05           38,00             40,59               6,77           40,59             31,67
6Produto 06         100,00           106,82             17,80         106,82             83,33
7Produto 07         413,00           441,18             73,53         441,18           344,17
8Produto 08         512,00           546,94             91,16         546,94           426,67
9Produto 09         869,00           928,30          154,72         928,30           724,17
10Produto 10     1.750,00       1.869,41          311,57      1.869,41        1.458,33
11Produto 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.

Edivaldo. Louco por funções e fórmulas de Excel, adoro fazer planilhas e compartilhar meus conhecimentos com as pessoas. Seja bem-vindo. Eu amo fazer isso e quero compartilhar com você!

4 Comentários
  1. André Luis Fernandes Minoru 30/06/2019 as 08:42

    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.

  2. 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!

    Deixe um Comentário

    Tudo Excel
    Logo