OPERAÇÕES BÁSICAS
Resultado esperado
3 4 5 6 7 8 9 10 11 12 13 14 15 16
A B C Meses Vendas (€) % Total Janeiro 12000 7,41% Fevereiro 8000 4,94% Março 10000 6,17% Abril 15000 9,26% Maio 20000 12,35% Junho 16000 9,88% Julho 14000 8,64% Agosto 10000 6,17% Setembro 12000 7,41% Outubro 15000 9,26% Novembro 15000 9,26% Dezembro 15000 9,26% Total 162000 100,00%
? Exercício 6: Maior vencimento Assinale com ***** os vendedores que auferem o maior vencimento.
3 4 5 6 7 8 9 10
A Vendedor A. Silva B. Gomes C. Costa E. Espregueira F. Fernandes G. Gouveia T. Taveira
B Vencimento atual (€) 1200 670 1489 1200 1500 465 1500
FCA – Editora de Informática 9
EXERCÍCIOS RESOLVIDOS COM EXCEL PARA ECONOMIA & GESTÃO
ƒ
Formulação do problema
Verificar se o primeiro funcionário é o que ganha mais: Se B4=MÁXIMO($B$4:$B$10) Então Escrever ("*****") Senão Escrever (" ") Fimse
Resolução do problema 1. Digite os registos dos vencimentos dos vendedores em A3:B10 2. Assinale o vendedor que aufere o maior vencimento digitando =SE(B4=MÁXIMO($B$4:$B$10); "*****";" ") em C4 copiando C4 para C5:C10
Resultado esperado
3 4 5 6 7 8 9 10
A B C Vendedor Vencimento atual (€) Obs. A. Silva 1200 B. Gomes 670 C. Costa 1489 E. Espregueira 1200 F. Fernandes 1500 ***** G. Gouveia 465 T. Taveira 1500 *****
? Exercício 7: Extrato bancário Considere o seguinte extrato bancário: A B C D E 3 Data Documento Montante Débito ou crédito Saldo 100000 4 Saldo inicial 5 Ch. 345 10000 D 6 Depósito 25000 C 7 ATM – 1 12500 D 10 FCA – Editora de Informática
OPERAÇÕES BÁSICAS
? Exercício 25: Totais regionais Considerando os seguintes valores de vendas:
3 4 5 6 7 8 9 10
A B Região Valor das vendas Norte 1000 Sul 1000 Norte 1500 Centro 4000 Sul 500 Norte 500 Centro 1000
a) Calcule os totais de vendas por região; b) Construa um gráfico de barras que represente os totais regionais.
Resolução do problema 1. Registe os valores das vendas por região em A3:B10 2. Ordene os dados por ordem alfabética de regiões selecionando A3:B10 Dados – Ordenar e Filtrar – Ordenar Região em Ordenar Por A a Z em Ordem 3. Calcule os totais regionais selecionando A3:B10 Dados – Destaques – Subtotal Região em A cada alteração em Soma em Utilizar a função Valor das Vendas em Adicionar subtotal a
FCA – Editora de Informática 45
EXERCÍCIOS RESOLVIDOS COM EXCEL PARA ECONOMIA & GESTÃO
Resultado esperado
Os valores das vendas regionais são precedidos, à esquerda, por 3 níveis de detalhes: O 1.º nível permite visualizar apenas o total geral; O 2.º nível permite visualizar os 3 totais regionais; O 3.º nível permite visualizar cada uma das transações, os totais regionais e o total geral. b)
1. Escolha o 2.º nível de detalhes 2. Gere o gráfico de barras selecionando A3:B14 Inserir – Gráficos – Coluna – Cilindro – Cilindros Agrupados definindo as características do gráfico
46 FCA – Editora de Informática
OPE ERA AÇÕE ES BÁ ÁSIC CAS
R ultad Resu do eesperradoo
d lhe selec s cionaado. Asssim, se eesco olherrmoss um m outtro níve n l de O gráfico refllete o níível dde detal deetalhhe, o grááficoo é au utom maticcam mentee alteeraddo.
? Exeerrcício 26: Lista personalizada r ste-aa com mo listaa perrson nalizaada. As listaas perso p onaEllaboore uuma listaa de venndeddoress e regis lizzadaas (ppor exem e mplo o, as dessignaaçõees doos m mesees doo ano o) são gerad g das auto omatticam mente a paartir de uum dos seus eleemeentoss; sãão taambéém circu c ularees poorqu ue o últiimo elem mentto é seguuido peloo priimeiiro.
4 5 6 7 8 9 10 1 11 1 12 1
C A. S Silva B. C Costta C. B Belm miroo D. G Gom mes A. F Fern nanddes F. G Góiss L. P Pereeira T. P Pinto o G. H Hen nriquues
FCA A – Edittora de Infor I rmáttica 47
MATEMÁTICA FINANCEIRA
Resultado esperado
3 4 5 6 7 8 9 10
A Taxa anual 5,00%
B Empréstimo 100000
C Duração (anos) 4
D Renda postecipada 0
E Anuidade (€) 25000
Anuidade Anos
Capital em dívida no início do ano
Quota de juro
100000 75000 50000 25000
5000 3750 2500 1250
1 2 3 4
Quota de capital
Pagamento anual
25000 25000 25000 25000
30000 28750 27500 26250
? Exercício 28: Sistema de amortização progressiva Seja um empréstimo de 100000 euros, contraído por um prazo de 4 anos, à taxa anual de 5%. Construa o respetivo quadro de amortização, considerando o sistema de amortização progressiva.
ƒ
Formulação do problema Empréstimo
P 1
5%
P 1
5%
P 1
5%
P – Pagamento anual constante (Termo da renda)
FCA Editora de Informática 131
EXERCÍCIOS RESOLVIDOS COM EXCEL PARA ECONOMIA & GESTÃO
Resolução do problema 1. Digite em A3:E10
3 4 5 6 7 8 9 10
A Taxa anual 5%
B Empréstimo
Anos
Capital em dívida no início do ano
100000
C Duração (anos) 4 Quota de juro
D Renda postecipada 0 Anuidade Quota de capital
1 2 3 4
2. Calcule o valor da anuidade da amortização digitando =PGTO(A4;C4;-B4;;D4) em E4 3. Elabore o quadro de amortização digitando =B4 em B7 =B7*$A$4 em C7 =E7-C7 em D7 =$E$4 em E7 =B7-D7 em B8 copiando B8 para B9:B10 C7:E7 para C8:E10
132 FCA Editora de Informática
E Anuidade (€)
Pagamento anual constante
MATEMÁTICA FINANCEIRA
Resultado esperado A 3 Taxa anual 4
5,00%
5 6 7 8 9 10
B
C
D
E
Empréstimo
Duração (anos)
Renda postecipada
Anuidade (€)
100000 Capital em dívida no início do ano
Anos 1 2 3 4
100000,000 76798,817 52437,574 26858,270
4
0
28201,18326
Anuidade Quota de juro 5000,000 3839,941 2621,879 1342,913
Quota de capital 23201,183 24361,242 25579,305 26858,270
Pagamento anual constante 28201,18326 28201,18326 28201,18326 28201,18326
? Exercício 29: Cenários para sistema de amortização progressiva Construa um quadro de amortização alternativo para o empréstimo do Exercício 28, considerando que, num cenário pessimista, o seu montante atinge 150000 euros e a taxa de juro é de 5,25%. O nome do cenário deve ser visível numa célula da folha de cálculo.
Resolução do problema 1. Construa o cenário alternativo selecionando Dados Ferramentas de Dados – Análise de Hipóteses Gestor de Cenários premindo o botão Adicionar
FCA Editora de Informática 133
GESTÃO
Resultado esperado A
3 Código 4 5 6 7 8 9 10 11 12 13 c)
108 460 100 300 305 451 321 433 504 508
B
C
Nome
Categoria
Fernão Silva João Pinto António Silva Teresa Góis Abel Silva Joana Alves Rui Carvalho António Silva Guilherme Henrique
E A C E C A E D A C
D E F Vencimento Subsídio de Vencimento base deslocação ilíquido 600 30 630 879 43,95 922,95 726 36,3 762,3 600 30 630 726 36,3 762,3 879 43,95 922,95 600 30 630 660 33 693 879 43,95 922,95 726 36,3 762,3
1. Calcule a massa salarial digitando =SOMA(F4:F13) em B32 2. Prepare o valor do vencimento base da categoria E para lhe ser adicionado o aumento digitando 0 em G22 =600+G22 em B24 3. Calcule o efeito dos diversos aumentos sobre a massa salarial digitando 75, 90 e 100 em C31:E31 selecionando B31:E32 selecionando Dados Ferramentas de Dados Análise de Hipóteses Tabela de Dados
Resultado esperado A Aumento do vencimento 31 base para a categoria E 32 Massa salarial
B
7638,75
C
D
E
75
90
100
8595,30
8785,35
8910,30
FCA Editora de Informática 265
EXERCÍCIOS RESOLVIDOS COM EXCEL PARA ECONOMIA & GESTÃO
? Exercício 2: Câmbios Foram efetuadas as seguintes transações em moeda estrangeira durante os primeiros 4 dias de junho de 2011: A B C 3 Dia Montante Moeda 4 1 100 USD 5 1 300 GBP 6 1 1000 JPY 7 2 23000 USD 8 2 5000 SEK 9 2 3456 USD 10 3 12345 GBP 11 3 321 NOK 12 3 120 SEK 13 4 4000 CAD 14 4 5000 CAD 15 4 5000 GBP Calcule o valor em euros das transações de acordo com a seguinte tabela de câmbios:
18 19 20 21 22 23 24 25
A B C D E Câmbios do dia Moeda 1 2 3 4 CAD 0,615 0,617 0,613 0,614 GBP 1,462 1,475 1,465 1,451 JPY 0,0074 0,0073 0,0076 0,0075 NOK 0,118 0,119 0,118 0,117 SEK 0,106 0,111 0,107 0,109 USD 0,801 0,772 0,765 0,796
Resolução do problema 1. Digite a transações em moeda estrangeira em A3:C15 2. Digite a tabela de câmbios em A18:E25
266 FCA Editora de Informática
GESTÃO
3. Calcule o valor em euros das transações digitando =PROCV(C4;$A$20:$E$25;A4+1) em D4 =ARRED(B4*D4;2) em E4 copiando D4:E4 para D5:E15 4. Calcule o valor total das transações digitando =ARRED(SOMA(E4:E15);2) em E16
Resultado esperado
3 4 5 6 7 8 9 10 11 12 13 14 15 16
A B C D Dia Montante Moeda Câmbio 1 100 USD 0,801 1 300 GBP 1,462 1 1000 JPY 0,0074 2 23000 USD 0,772 2 5000 SEK 0,111 2 3456 USD 0,772 3 12345 GBP 1,465 3 321 NOK 0,118 3 120 SEK 0,107 4 4000 CAD 0,614 4 5000 CAD 0,614 4 5000 GBP 1,451 Total
E Valor em euros 80,10 438,60 7,40 17756,00 555,00 2668,03 18085,43 37,88 12,84 2456,00 3070,00 7255,00 52422,28
? Exercício 3: Preçário e valor das vendas Utilizando a seguinte tabela de preços, gravada na folha Preçário de Gestao2010.xlsm:
3 4 5 6 7 8
A Código 10 20 30 40 50
B C Descrição Preço unitário (€) Maçãs (Kg) 1 Peras (Kg) 1,5 Ameixas (Kg) 3,5 Morangos (Kg) 2 Nozes (Kg) 3
FCA Editora de Informática 267
FUNÇÕES DEFINIDAS PELO UTILIZADOR
? Exercício 21: Frequências absolutas Defina uma função que calcule o número de vezes que ocorrem as classes A, B, C, D e E num conjunto de dados.
ƒ
Formulação do problema
Exemplo de atualização dos contadores: Cl(1,Asc(S.Cells(1,j))-64)=Cl(1,Asc(S.Cells(1,j))-64)+1 Letra Código ASCII Índice-coluna Caráter A B C D E Código ASCII do caráter 65 66 67 68 69 Contadores (Cl) Cl(1,1) Cl(1,2) Cl(1,3) Cl(1,4) Cl(1,5) Designação Resultado
FREQ
Parâmetro S Variáveis
S
Significado Tipo Número de vezes que ocorrem as Vetor de inteiros + classes A, B, C, D e E Conjunto de dados a contar Range
Cl
Conjunto de contadores das classes
Inteira
J
Índice-coluna do conjunto Cl
Inteira
Tcols
Número de colunas de S
Inteira
Função FREQ(S) Para J=1 Até 5 Cl(1,J):=0 FimPara Tcols:=A.NúmeroDeColunas Para J=1 Até Tcols Cl(1, Asc(S.Cells(1, J) - 64):=Cl(1, Asc(S.Cells(1, J)) - 64) + 1 FimPara FREQ:=Cl Regressar
FREQ
FCA Editora de Informática 355
EXERCÍCIOS RESOLVIDOS COM EXCEL PARA ECONOMIA & GESTÃO
Definição da função 1. Execute o editor de VBA e insira um módulo e uma função 2. Digite Option Base 1 Public Function FREQ(S As Range) As Variant Dim Cl(1, 5), J As Integer Dim Tcols as Integer For J = 1 To 5 Cl(1, J) = 0 Next j Tcols=S.Columns.Count For J= 1 To Tcols Cl(1,Asc(S.Cells(1,J))-64)=Cl(1,Asc(S.Cells(1,J))-64)+1 Next J FREQ = Cl End Function
Execução da função 1. Digite o conjunto de dados em A3:L3 A B C D E F G H I J K L 3 A A B A A A B C B E E B 2. Digite as classes em A5:F6 A 5 Classes 6 Frequências absolutas 3. Execute a função Freq selecionando B6:F6 digitando =Freq(A3:L3) premindo SHIFT +
B A
C B
D C
E D
F E
CTRL
+
ENTER
5 Classes 6 Frequências absolutas
B A 5
C D E F B C D E 4 1 0 2
Resultado esperado A
356 FCA Editora de Informática