Excel Aplicativo

  • - Acesso Livre
  • Documentos

    Ordenar por : Nome | Data | Acessos [ Descendente ]

    Excel financiamento calculos formulas Excel financiamento calculos formulas

    popular!
    Adicionado em: 04/12/2010
    Modificado em: 04/12/2010
    Tamanho: 146.15 KB
    Downloads: 1021

    Dicas do Aplicativo Excel
    Saberexcel – Informática
     

    Como calcular juros no Excel

    Download do exemplo no final da página

    Vamos mostrar como criar uma aplicação para calcular e montar tabelas detalhadas para a amortização de empréstimos. Você, que vai construir o sisteminha, terá de encarar todas as fórmulas e truques necessários para fazê-lo funcionar. Mas o objetivo é esconder todas as complexidades, criando um resultado final que qualquer usuário possa utilizar. Na verdade, se você não está nem aí para fórmulas, a planilha esta disponível no site http://www.saberexcel.com para pessoas cadastradas no site.

    Mãos à obra. Para começar, vamos definir o formato geral de nossa planilha. Ela deve conter três áreas. Primeiro, as células nas quais o usuário deve digitar os dados básicos do financiamento (valor, taxa de juros, prazo e data inicial). Depois, um painel-resumo, com o valor do pagamento mensal, número de pagamentos, total de juros e custo total do financiamento. O terceiro bloco apresenta uma tabela com a discriminação dos dados de cada pagamento previsto. Cada linha dessa tabela mostra a previsão de pagamento de um mês, evoluindo desde o valor financiado total até o saldo final zero. Na elaboração dessa planilha, tomamos como ponto de partida um modelo publicado no site da Microsoft, que foi redefinido para este tutorial. Para acompanhar adequadamente as tarefas, é aconselhável que você faça o download de nossa planilha no site de http://www.saberexcel.com

    A principal técnica utilizada na elaboração do projeto é a definição de nomes para células, grupos de células e fórmulas. Assim, em vez de operar com endereços, trabalha-se com variáveis, o que facilita bastante a compreensão. É muito mais confortável, por exemplo, lidar com itens como Valor_Financiado e Taxa_Juros do que com E4, C7. Vamos, então, montar a área 1 da planilha, dedicada à entrada de dados. Em nosso exemplo, ela ocupa as células de E4 a E7. Vamos dar um nome a cada uma dessas células.

    Coloque o cursor em E4 e acione o comando Inserir/Nome/Definir. Na caixa Definir Nome, digite, em cima, Valor_Financiado (o nome não pode ter espaços). Embaixo, o programa já inclui, automaticamente, o endereço de E4. Acione OK. Repita a operação para as células E5 a E7, nomeando-as, respectivamente, como Taxa_Juros, Prazo_Meses e Data_Inicio. Faça as adequadas formatações nas quatro células, ajustando cada uma conforme o conteúdo esperado: moeda, decimal, inteiro e data. Para orientar o usuário da calculadora de financiamentos, escreva, à esquerda de cada célula, as indicações dos dados a serem digitados.

    Passemos à segunda área. Na mesma coluna, no intervalo E11:E14, vamos nomear quatro células: Pagamento_Mensal, Num_Pagamentos, Total_Juros e Custo_Total. A cada um desses nomes deve corresponder uma fórmula:

    =-PGTO(Taxa_Juros;Num_Pagamentos;Valor_Financiado)

    Acima, a fórmula para Pagamento_Mensal. Ela usa a função PGTO e baseia-se em outras variáveis nomeadas. Observe o sinal de menos: ele indica que cada pagamento será subtraído do valor financiado. A fórmula para Num_Pagamentos é a seguinte:

    =SE(Tudo_Preenchido;Prazo_Meses;"")

    A rigor, o número de pagamentos é igual ao valor digitado na célula Prazo_Meses. No entanto, ele só deve aparecer quando o usuário tiver preenchido as células da área 1 para o

     

    Saberexcel – Curso Introdução a Informática Básica – Dicas do Excel 6

    cálculo de novo financiamento. Por extensão, as células da área 2 e da área 3 só devem exibir alguma informação se as quatro células da área 1 estiverem preenchidas. Para garantir isso, vamos criar uma fórmula nomeada (sempre em Inserir/Nome/Definir), Tudo_Preenchido, que funciona como um teste lógico, do tipo sim/não:

    =SE(Valor_Financiado*Taxa_Juros

    *Prazo_Meses*Data_Inicio>0;1;0)

    Aqui, o truque é o seguinte: multiplicam-se os quatro valores das células na área 1. Se todos estiverem preenchidos, o resultado será um número positivo; se pelo menos um estiver em branco, será zero. Assim, se a fórmula Tudo_Preenchido responde sim (valor 1), os cálculos devem ser executados para preencher as áreas 2 e 3. Se for não (valor 0), aquelas regiões são mostradas em branco.

    As fórmulas para as células Total_Juros (E13) e Custo_Total (E14) são, respectivamente:

    =-IPGTO(Taxa_Juros;Pagamento_Num;Num_Pagamentos;Valor_Financiado)

    =SE(Tudo_Preenchido;ARRED(Pagamento_Mensal;2)*Num_Pagamentos;"")

    Em Total_Juros, usa-se a função IPGTO, do Excel, que fornece os juros acumulados de um investimento. Também nesse caso, como se trata de débitos, o sinal é negativo. Na fórmula do Custo_Total, preste atenção para a função ARRED (arredondamento), aplicada apenas ao valor do pagamento mensal. Ela garante que a multiplicação dos valores mostrados para Pagamento_Mensal e Num_Pagamentos produz, exatamente, o número mostrado em Custo_Total. Sem essa função, apareceria um número aproximado.

    A área 3 da planilha, que apresenta a discriminação de cada pagamento, é encimada por um cabeçalho com sete títulos de colunas: Número; Data do Pagamento; Balanço Inicial; Pagamento; Principal; Juros; e Balanço Final. Selecione essas sete células e nomeie-as como Linha_Cabeçalho, definida pela seguinte fórmula:

    =LIN(´Tabela de Amortização´! $17:$17)

    Ainda não havíamos dito que nossa folha de cálculo foi batizada como Tabela de Amortização. Nessa tabela, o cabeçalho corresponde à linha 17. Como a área 3 pode se estender por mais de uma página impressa, acione Arquivo/Configurar Página/orelha Planilha e, na caixa Linhas a Repetir na Parte Superior, digite Linha_Cabeçalho. Avancemos, agora, para o miolo da área 3. Vamos construir a primeira linha, logo abaixo do cabeçalho. Naturalmente, as sete células dessa linha têm fórmulas específicas. A célula Número apresenta apenas o valor Pagamento_Num, ou seja, a seqüência 1, 2, 3 etc. Sua fórmula é:

    =SE(Nao_Pago*Tudo_Preenchido; Pagamento_Num;"""")

    Aqui, entra em cena novo teste lógico, que é a variável Nao_ Pago, também definida por uma fórmula nomeada:

    =SE(Pagamento_Num<=Num_Pagamentos;1;0)

     

    Saberexcel – Curso Introdução a Informática Básica

    Esse teste indica se a linha deve ou não ser preenchida. A resposta será sim enquanto o número do pagamento for menor ou igual ao total de pagamentos. A célula Data do Pagamento deve exibir a data inicial do financiamento acrescida de um mês:

    =SE(Nao_Pago*Tudo_Preenchido; Data_Pagamento;"")

    Ela se baseia na fórmula nomeada Data_Pagamento:

    =DATA(ANO(Data_Inicio);MÊS(Data_Inicio)+Pagamento_ Num;DIA (Data_Inicio))

    A célula Balanço Inicial também depende de outra fórmula nomeada, Balanço_Inicial, que, por sua vez, aplica a fórmula do valor futuro:

    =-VF(Taxa_Juros;Pagamento_ Num-1;-Pagamento_Mensal;Valor _Financiado)

    Balanço Final, a última célula, tem base idêntica:

    =-VF(Taxa_Juros;Pagamento_Num;-Pagamento_Mensal;Valor_Financiado)

    O valor a ser exibido na coluna Pagamento é sempre o mesmo e corresponde a Pagamento_Mensal. Como em todas as células da área 3, ele só deve ser escrito se os dados para o cálculo foram preenchidos e se a linha se refere a uma prestação não paga:

    =SE(Nao_Pago*Tudo_Preenchido; Pagamento_Mensal;"")

    Por fim, vêm as células para os valores Principal e Juros, que discriminam, no valor da prestação, quanto equivale à restituição do capital e quanto corresponde a juros. Principal e Juros são, também, duas fórmulas nomeadas. A primeira baseia-se na fórmula PPGTO, do Excel:

    =-PPGTO(Taxa_Juros;Pagamento_Num;Num_Pagamentos;Valor _Financiado)

    Juros, por sua vez, usa a fórmula interna IPGTO:

    =-IPGTO(Taxa_Juros;Pagamento_ Num;Num_Pagamentos;Valor_Financiado)

    Todas as células da primeira linha ativa da área 3 devem ser copiadas para as linhas seguintes. Na planilha-exemplo, essas cópias foram estendidas até a linha 377, que corresponde à prestação número 360 - ou seja, um financiamento de 30 anos, o prazo máximo aceito pela aplicação.

    A essa altura, sua calculadora de financiamentos já está completa. Faltam apenas detalhes de acabamento. Para concluir o trabalho, selecione as células ativas da área 1 e dê o comando Formatar/Células. Na orelha Proteção, desligue a caixa Travada. Agora, acione Ferramentas/Proteger/Proteger Planilha (a definição de senha é opcional). Você acaba de criar uma espécie de formulário. As células para entrada de dados estão livres e todas as outras, protegidas. Assim, qualquer usuário poderá usar a planilha sem o risco de modificá-la.
    Essa aplicação foi testada nas versões anteriores como 2003 funciona também nas versoes atuais

     


    Aprenda Microsoft Excel VBA - Saberexcel - o site das macros

    Download do exemplo planilha com a dica acima




     

    Excel funcao modo retorna numero maior ocorrencia range Excel funcao modo retorna numero maior ocorrencia range

    popular!
    Adicionado em: 04/12/2010
    Modificado em: 04/12/2010
    Tamanho: 12.25 KB
    Downloads: 1647

    Dicas do Aplicativo Excel
    Saberexcel – Informática

    Como determinar o numero que mais aparece numa planilha Excel

    O Excel oferece uma função que faz esse cálculo. Trata-se de Modo - tradução incorreta, nesse contexto, do inglês Mode. Em português, o nome dessa figura estatística é moda, o valor que mais se repete numa amostra. No Excel, a Função Modo se aplica a qualquer região de células.

    Exemplo: =MODO(D5:D19)

    Se na região não existem valores repetidos, a função retorna um erro: #N/D (não disponível). Se há mais de um valor modal, a moda é o menor.
    Na seqüência 1, 1, 2, 2, a moda é 1.



    Baixe o Exemplo simples de planilha com a Função MODO (retornando o numero que mais se repete)


    Aprenda Microsoft Excel VBA - Saberexcel -

    Excel inserir dias da semana sem sabados e domingos Excel inserir dias da semana sem sabados e domingos

    popular!
    Adicionado em: 04/12/2010
    Modificado em: 04/12/2010
    Tamanho: 21.18 KB
    Downloads: 950

    Dicas do Aplicativo Excel
    Saberexcel – Informática

    Para listar apenas os dias úteis no Excel

    Você viu como fazer uma seqüência automática de datas. E se você quisesse listar somente os dias úteis, deixando de fora os fins de semana? Também é possível. Em vez de arrastar a alça da célula com o botão esquerdo, de uso mais comum, faça-o com o direito. No final, solte o botão do mouse. Na lista que surge, escolha a opção Preencher Dias da Semana. Observe: entre as datas da seqüência não constam nem sábados nem domingos.

    Se pretender usar a macro abaixo para automatizar em uma folha de planilha poderá obter a macro a seguir.
    Domine o conhecimento de como produzir suas próprias macros com o Curso Completo Microsoft Excel VBA - Saberexcel.
    Voce aprenderá como desenvolver suas próprias macros e como depurá-las já na primeira lição do Curso Comprelto Microsoft Excel VBA - Saberexcel
    copie a macro cole-a em um módulo comum do VBE(Visual Basic Editor) e execute-a para visualizar o resultado.

    Sub dias_da_semana()
    ' verficando inconsistência.....
    If [E4].Value = "" Then
    MsgBox ("Insira uma data na célula(E4)"), vbCritical, "Saberexcel - site das macros"
    Exit Sub
    End If
    Range("F4").FormulaR1C1 = "=RC[-1]"
    Range("E4:F4").AutoFill Destination:=Range("E4:F34"), Type:=xlFillWeekdays
    Range("F4:F34").NumberFormat = "dddd"
    Range("G4").Select
    End Sub

    Sub limpar_teste()
    Range("E4:F34").ClearContents
    End Sub


    Aprenda tudo sobre o Aplicativo Microsoft Excel VBA com SaberExcel


    Google Associados

    Depoimentos

    Adicione Saberexcel Favoritos

     
     

    Aprenda tudo sobre o Aplicativo Microsoft Excel VBA

    Aprenda tudo sobre o Aplicativo Microsoft Excel VBA(Visual Basic Application), sozinho, com baixo custo, praticando com os produtos didáticos Saberexcel,


       Sobre as WorksheetFunctions Funções de Planilhas que retornam valores do VBA