excel2010 Avancado, Notas de estudo de Engenharia Civil

Baixe excel2010 Avancado e outras Notas de estudo em PDF para Engenharia Civil, somente na Docsity!

Microsoft

Excel 2010 - Avançado

Desenvolvida exclusivamente para o Apostilando.com por

Marcos Paulo Furlan

ÍNDICE

• ÍNDICE
• INTRODUÇÃO
• PROC, PROCV E PROCH - Distribuição de Frequência..........................................................................
• FUNÇÕES DE DATA E HORA
• RÓTULOS
• FUNÇÕES FINANCEIRAS
• DADOS - Dados da WEB.......................................................................................... - Classificação - Auto Filtro - Filtragem personalizada - Subtotais - Consolidação
• TABELA DINÂMICA
  • Gráficos Dinâmicos
  • Validação
    • Controlar Alterações
  • Validação com filtros
• FUNÇÕES DE BANCOS DE DADOS
• HISTOGRAMA
• SOLVER
• TESTE DE HIPÓTESE
  • Cenários
  • Atingir Meta...................................................................................................
• FORMULÁRIOS EM EXCEL
• MACROS
  • Atribuindo botões que mostram as Macros...................................................
  • VBA – Visual Basic Application.....................................................................
• PASTAS DE TRABALHOS COMPARTILHADAS
• CONCLUSÃO

Temos então uma planilha base com as classes e seus respectivos valores, na planilha ao lado, temos então o hóspede e classe escolhida, vamos então procurar na primeira planilha o tipo de classe para que retorne o valor. =PROCV(E3;A4:B8;2) , Observe que é buscado o tipo da classe E3 no intervalo da tabela base A4:B8 e retorna a segunda coluna representado pelo número 2.

Para preencher as demais células, o cálculo é o mesmo, e neste caso temos valores absolutos A4:B8 que são os valores que estão na primeira tabela. =PROCV(E3;$A$4:$B$8;2)

Vamos agora a um exemplo usando o PROCH Monte a seguinte planilha

Vamos usar o assistente de função. Clique sobre o botão na barra e depois

procure a função.

Clique em OK. Vamos agora preencher os campos das seguinte forma.

• valor_procurado: é a célula B10;
• matriz_tabela é o intervalo de A2:E8;
• num_linha: é 7, pois o intervalo começa na linha 2, e os valores a serem retornados estão na linha 8 (Total do semestre);
• procurar_intervalo: é 0 "zero", pois queremos o valor exato da pesquisa.

Não clique em OK. Clique na barra de fórmulas e pressione CTRL+SHIFT+ENTER. Ele retorna o resultado e acrescenta chaves na função determinando assim a matriz.

FUNÇÕES DE DATA E HORA Podemos trabalhar com diversas funções que se baseiam na data e hora de seu computador. As principais função de data e hora são =HOJE( ) Retorna a data atual.

=MÊS(HOJE()) Retorna o mês atual =ANO(HOJE()) Retorna o ano atual =HORA(AGORA()) Retorna a hora atual =MINUTO(AGORA()) Retorna o minuto atual =SEGUNDO(AGORA()) Retorna o segundo atual =AGORA( ) Retorna a data e à hora =DIA.DA.SEMANA(HOJE()) Retorna o dia da semana em número =DIAS360( ) Calcula o número de dias que há entre uma data inicial e uma data final Para exemplificar monte a seguinte planilha.

Em V.Diário, vamos calcular quantas horas foram trabalhadas durante cada dia. =B3-B2+B5-B4 , pegamos a data de saída e subtraímos pela data de entrada de manhã, com isso sabemos quantas horas foram trabalhadas pela manhã na mesma função faço a subtração da saída no período da tarde pela entrada do período da tarde e somo os dois períodos.

Para os demais cálculos o V.Hora será igual há todos os dias então ele precisa ser fixo para que o cálculo possa ser copiado, o número 60 por ser um número não é muda. =HORA(B6)$B$7+MINUTO(B6)$B$7/** Para sabermos quantas horas o funcionário trabalhou na semana, faça a soma de todos os dias trabalhados.

Ao observar atentamente o valor calculado ele mostra 20:40, porém nessa semana o funcionário trabalhou mais de 40 horas, isso ocorre pois o cálculo de horas zera ao chegar em 23:59:59, então preciso fazer com que o Excel entenda que ele precisa continuar a contagem. Clique na faixa do grupo número na ABA Inicio, na janela que se abre clique na categoria Hora e escolha o formato 37:30:55 esse formato faz com que a contagem continue.

Crie um novo campo abaixo da Tabela e coloque V. a receber e faça a soma dos valores totais.

RÓTULOS

Podemos nomear os dados de uma planilha para que a escrita das fórmulas seja feita de maneira mais simples. Os “NOMES” podem fazer referência a um valor, a uma fórmula ou até mesmo a um conjunto de células. Na planilha abaixo temos que calcular o volume de vários objetos com diferentes alturas. Todos os objetos têm a mesma base, que é o resultado da multiplicação da largura pelo comprimento. Monte a planilha.

Para a data para que tenha sempre a data atual clique sobre a célula e coloque a seguinte função.

Para o valor unitário, selecione toda a faixa de valores e nomeie como unitário. Para calcular os valores o processo é simples. Clique na célula onde será feito o calculo de conversão do valor pelo dólar compra e digite: = unitário, observe que o Excel já vai mostrar o rótulo.

Para confirmar o rótulo basta dar um duplo clique sobre o mesmo.

Repita o processo para todos os demais cálculos. Formate as células de acordo com suas respectivas moedas.

Ao clicar em gerenciar nomes, você pode modificar, excluir os nomes de seus rótulos.

FUNÇÕES FINANCEIRAS Existe um grupo de funções do Excel que permitem cálculos financeiros. As principais são NPER - Retorna o número de períodos para investimento de acordo com pagamentos constantes e periódicos e uma taxa de juros constante. A sintaxe é =NPER(TAXA;PGTO;VP;VF;TIPO) onde:

• Taxa Necessário. A taxa de juros por período.

• Taxa Necessário. A taxa de juros por período. Por exemplo, se você tiver um empréstimo para um automóvel com taxa de juros de 10% ano e fizer pagamentos mensais, sua taxa de juros mensal será de 10%/ ou 0,83%. Você deveria inserir 10%/12 ou 0,83%, ou 0,0083, na fórmula como taxa.
• Nper Necessário. O número total de períodos de pagamento em uma anuidade. Por exemplo, se você conseguir um empréstimo de carro de quatro anos e fizer pagamentos mensais, seu empréstimo terá 4*12 (ou
  48. períodos. Você deveria inserir 48 na fórmula para nper.
• Pgto Necessário. O pagamento feito em cada período e não pode mudar durante a vigência da anuidade. Geralmente, pgto inclui o principal e os juros e nenhuma outra taxa ou tributo. Por exemplo, os pagamentos mensais de R$ 10.000 de um empréstimo de quatro anos para um carro serão de R$ 263,33. Você deveria inserir -263,33 na fórmula como pgto. Se pgto for omitido, você deverá incluir o argumento vf.
• Vf Opcional. O valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de um empréstimo, por exemplo, é 0). Por exemplo, se você deseja economizar R$ 50.000 para pagar um projeto especial em 18 anos, então o valor futuro será de R$ 50.000. Você poderia então fazer uma estimativa conservadora na taxa de juros e concluir quanto economizaria por mês. Se vf for omitido, você deverá incluir o argumento pgto.
• Tipo Opcional. O número 0 ou 1 e indica as datas de vencimento

Vamos montar um exemplo da função VP Monte a seguinte planilha Uma aplicação com duração de 12 meses e taxa de 4% ao mês rendeu um resgate de R$ 90.000,00. Desejamos saber qual foi o valor investido.

Clique onde será colocado o valor de VP e abra o assistente de função.

Preencha os dados conforme adicionado na planilha. Clique em OK e assim sabemos qual é o valor presente. Mais um exemplo agora usando a função PGTO - Retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante. Sua sintaxe é: PGTO(taxa, nper, pv, fv, tipo)

Vamos usar uma função financeira para calcular o valor dos pagamentos de um empréstimo. Neste caso, a taxa de juros e o número de pagamentos devem estar expressos da mesma forma, ou seja, se os pagamentos forem mensais, a taxa de juros também deverá ser.

O valor atual é o valor que será disponibilizado para o empréstimo.

O valor futuro é o saldo que deverá restar após serem efetuados todos os pagamentos, neste caso zero.

No tipo de empréstimo “1” o primeiro pagamento é efetuado somente no final do período, ou seja, depois de um mês. No tipo “0” o primeiro pagamento é efetuado no mesmo ato do empréstimo.

Planilha de empréstimo - informando o tipo de empréstimo "1", primeiro pagamento para o próximo mês. O valor do campo “Pagamento” será calculado através da função financeira “PAGAMENTO”. Vamos inseri-la.