CÁLCULOS ELEMENTARES COM EXCEL 74 EXERCÍCIOS
Adelaide Carvalho
FCA – Editora de Informática, Lda. www.fca.pt
EDIÇÃO FCA – Editora de Informática, Lda. www.fca.pt Copyright © 2016, FCA – Editora de Informática, Lda. ISBN eBook: 978-972-722-851-5 1.ª edição eBook: setembro 2016 Capa: José Manuel Ferrão – Look-Ahead Todos os nossos livros passam por um rigoroso controlo de qualidade, no entanto aconselhamos a consulta periódica do nosso site (www.fca.pt) para fazer o download de eventuais correções. Não nos responsabilizamos por desatualizações das hiperligações presentes nesta obra, que foram verificadas à data de publicação da mesma. Os nomes comerciais referenciados neste livro têm patente registada. Reservados todos os direitos. Esta publicação não pode ser reproduzida, nem transmitida, no todo ou em parte, por qualquer processo eletrónico, mecânico, fotocópia, digitalização, gravação, sistema de armazenamento e disponibilização de informação, sítio Web, blogue ou outros, sem prévia autorização escrita da Editora.
ÍNDICE O Autor Prólogo 1 – Operações Gerais ? Exercício 1.1: Ativação de suplementos ? Exercício 1.2: Ativação do Programador ? Exercício 1.3: Ativação do cálculo manual ? Exercício 1.4: Definição de iterações e precisão ? Exercício 1.5: Ilíquido, desconto, IVA e total a pagar ? Exercício 1.6: Total, maior e menor vencimentos ? Exercício 1.7: Vários formatos ? Exercício 1.8: De euros para dólares ? Exercício 1.9: Subida de preços ? Exercício 1.10: Distribuição percentual das vendas ? Exercício 1.11: Maior vencimento ? Exercício 1.12: Poupança imediata ? Exercício 1.13: Pauta de exame ? Exercício 1.14: Formatos para provas de computação ? Exercício 1.15: Formatos para artigos em stock ? Exercício 1.16: Controlo de faturas ? Exercício 1.17: Extrato bancário
? Exercício 1.18: Concurso televisivo ? Exercício 1.19: Classificação de testes ? Exercício 1.20: Organograma ? Exercício 1.21: Botão de controlo ? Exercício 1.22: Gráfico de setores ? Exercício 1.23: Gráfico de barras ? Exercício 1.24: Gráfico de função linear ? Exercício 1.25: Efetivos de escalões etários ? Exercício 1.26: Agrupamento de turmas ? Exercício 1.27: Totais regionais ? Exercício 1.28: Totais por vendedor ? Exercício 1.29: Peso relativo dos vendedores ? Exercício 1.30: Escalões de vendas ? Exercício 1.31: Cursos por sexo ? Exercício 1.32: Segmentação de dados ? Exercício 1.33: Vendedores por zona ? Exercício 1.34: Vendedores complementares ? Exercício 1.35: Conjunção e disjunção de vendedores ? Exercício 1.36: Eliminação de duplicados ? Exercício 1.37: Lista personalizada ? Exercício 1.38: Validação de classificações ? Exercício 1.39: Lista de valores possíveis ? Exercício 1.40: Proteção de fórmulas ? Exercício 1.41: Concurso de perguntas e respostas
2 – Matemática ? Exercício 2.1: Expressão aritmética ? Exercício 2.2: Função composta ? Exercício 2.3: Sistemas de numeração ? Exercício 2.4: Medidas de comprimento ? Exercício 2.5: Medida de ângulos ? Exercício 2.6: Arredondamentos ? Exercício 2.7: Somatórios ? Exercício 2.8: Extração de dígitos ? Exercício 2.9: Máximo divisor comum (MDC) ? Exercício 2.10: Mínimo múltiplo comum (MMC) ? Exercício 2.11: Teorema de Pitágoras ? Exercício 2.12: Equação de 1.° grau ? Exercício 2.13: Equação de 2.° grau ? Exercício 2.14: Parâmetro da equação de 3.° grau ? Exercício 2.15: Funções trigonométricas ? Exercício 2.16: Sinusoide e cossinusoide ? Exercício 2.17: Resolução de triângulos ? Exercício 2.18: Operações lógicas ? Exercício 2.19: Conjunções, disjunções e negações ? Exercício 2.20: Fatoriais ? Exercício 2.21: Permutações simples ? Exercício 2.22: Arranjos e combinações ? Exercício 2.23: Arranjos simples
? Exercício 2.24: Combinações de bombons ? Exercício 2.25: Combinações de apostas ? Exercício 2.26: Multiplicação de matriz por escalar ? Exercício 2.27: Multiplicação de matrizes ? Exercício 2.28: Determinante e matriz inversa ? Exercício 2.29: Sistema de 2 equações a 2 incógnitas ? Exercício 2.30: Método de Gauss-Jordan para sistemas de equações ? Exercício 2.31: Ajustamento a C=aR+b ? Exercício 2.32: Ajustamento a Y=aW+bP+c ? Exercício 2.33: Ajustamento a C=abT Bibliografia Índice Remissivo Complementos na Web
O AUTOR Adelaide Carvalho Doctor of Philosophy in Management Science (Lancaster University, Reino Unido), Doutora em Economia e Gestão (Universidade do Porto, Portugal), Master of Science in Management Science (University of Kent at Canterbury, Reino Unido), Master of Science in Computing Science (University of London, Reino Unido), Eq. Mestre em Ciência dos Computadores (Universidade do Porto, Portugal), Licenciada em Economia (Universidade do Porto, Portugal). Docente, desde 1983, em diversos estabelecimentos de ensino superior. Organizou e ministrou, de 1991 até ao presente, vários cursos de aplicação da Informática à Economia e à Gestão em Portugal, Grécia, Finlândia, Moçambique, Macau e Timor-Leste. Autora de vários livros de Informática aplicada à Economia e à Gestão editados pela FCA.
PRÓLOGO “Assim como se podem escrever asneiras com uma máquina de escrever do último modelo, se podem fazer disparates com os sistemas e aparelhos mais perfeitos para ajudar a não fazê-los. Sistemas, processos, móveis, máquinas, aparelhos são – como todas as coisas mecânicas e materiais – elementos puramente auxiliares. O verdadeiro processo é pensar; a máquina fundamental é a inteligência…” Fernando Pessoa (1926). Textos para Dirigentes de Empresas
A publicação de Cálculos Elementares com Excel – 74 Exercícios visa a construção de uma infraestrutura de conhecimento das funcionalidades do Microsoft Excel 2016 que facilite o estudo e a aprendizagem dos métodos quantitativos que compõem os conteúdos programáticos de diversas disciplinas de cursos de licenciatura e de mestrado em Economia, Gestão e Engenharia. Por outro lado, esta infraestrutura de conhecimento de funcionalidades que ora propomos contribuirá certamente para sustentar a aprendizagem e a utilização crescente de mais funcionalidades do Excel, quer a nível académico quer a nível profissional. Cálculos Elementares com Excel – 74 Exercícios é constituído por dois capítulos: “1. Operações Gerais” e “2. Matemática”. O primeiro capítulo resolve 41 exercícios e o segundo 33 exercícios de Economia, Gestão e disciplinas auxiliares (vide Figura 1).
Figura 1. Estrutura de Cálculos Elementares com Excel – 74 Exercícios
Os 41 exercícios do primeiro capítulo ilustram a aplicação e a utilização de um conjunto de funcionalidades essencial para a posterior resolução de problemas de Economia e Gestão com o Excel (vide Figura 2).
Figura 2. Infraestrutura de funcionalidades essencial à resolução de problemas de Economia e Gestão com o Excel
Os 33 exercícios do segundo capítulo apresentam os números – inteiros e reais –, a matemática em que se interrelacionam e as funcionalidades do Excel que a implementam (vide Figura 3).
Figura 3. Matemática e respetivas funcionalidades do Excel necessárias à resolução de exercícios de Economia e Gestão
A resolução destes 74 exercícios observa metodologias de programação estruturada, nomeadamente a aproximação descendente (top-down) e privilegia a facilidade de compreensão da solução face à complexidade dos cálculos, incluindo na maioria dos exercícios, a explicitação da formulação dos problemas. A vocação académica orientada para a gestão desta publicação reforça o seu uso no universo profissional, tratando questões que fazem parte do quotidiano da Economia e Gestão. Crê-se, e a experiência da autora como professora confirma-o, que este livro poderá constituir um instrumento precioso para a motivação dos formandos e aceleração do processo de aprendizagem. Adelaide Carvalho
1. OPERAÇÕES GERAIS Este capítulo versa as funcionalidades que compõem a infraestrutura mais profunda da aprendizagem da resolução de problemas de Economia e Gestão com o Excel. Os 41 exercícios que apresentamos ilustram: utilização de formatos e de endereços absolutos, relativos e mistos; operações com diversos tipos de dados, séries e funções embutidas (entre as quais se destacam as que implementam estruturas de decisão e pesquisas em tabelas); desenho de organogramas e gráficos; passagem de dados entre folhas de cálculo; operações com bases de dados; tabelas dinâmicas; validação, segmentação e agrupamento de dados; análise de hipóteses; botões de comando definidos pelo utilizador; listas personalizadas; e proteção de células.
? Exercício 1.1: Ativação de suplementos Ative os suplementos Analysis ToolPak e Solver. Resolução do problema 1. Selecione Ficheiro – Opções – Suplementos 2. Selecione Suplementos do Excel em Gerir 3. Prima Ir 4. Ative 4.1. Analysis ToolPak 4.2. Solver
? Exercício 1.2: Ativação do Programador Ative o Programador. Resolução do problema 1. Selecione Ficheiro – Opções – Personalizar Friso 2. Ative Programador em Separadores Principais
? Exercício 1.3: Ativação do cálculo manual Ative o cálculo manual e, em seguida, reponha o cálculo automático. Resolução do problema 1. Selecione Ficheiro – Opções – Fórmulas 2. Ative Manual em Cálculo do Livro
3. Verifique 3.1. digitando 3.1.1. 4 em A1 3.1.2. =3*A1 em B1 3.1.3. 6 em A1 3.2. premindo F9 para efetuar e visualizar o cálculo 4. Ative Automático em cálculo do Livro 4.1. selecione Ficheiro – Opções – Fórmulas 4.2. ative Automático em Cálculo do Livro
? Exercício 1.4: Definição de iterações e precisão Defina o número de iterações e a precisão a usar em processos iterativos de cálculo. Resolução do problema 1. Selecione Ficheiro – Opções – Fórmulas 2. Ative cálculo iterativo 3. Digite
3.1. 500 em N.° Máximo de Iterações 3.2. 0,000001 em Alteração Máxima
? Exercício 1.5: Ilíquido, desconto, IVA e total a pagar Calcule o total a pagar de uma fatura com valor ilíquido de 1200 €. A fatura beneficia de um desconto de pronto pagamento de 5%, e sobre o líquido de desconto incide IVA à taxa de 23%. Formulação do problema
Resolução do problema 1. Digite em A3:B7
A 3 Valor ilíquido
B 1200
4 Desconto (5%) 5 Valor líquido 6 IVA (23%) 7 Total a pagar 2. Calcule o valor líquido da fatura 2.1. digitando 2.1.1. =B3*0,05 em B4 2.1.2. =B3-B4 em B5 3. Calcule o valor do IVA 3.1. digitando =B5*0,23 em B6 4. Calcule o valor a pagar 4.1. digitando =B5+B6 em B7 Resultado esperado A 3 Valor ilíquido 4 Descontos (5%) 5 Valor líquido
B 1200 60 1140
6 IVA (23%)
262,2
7 Total a pagar 1402,2
? Exercício 1.6: Total, maior e menor vencimentos Considere a seguinte tabela, que apresenta os vencimentos dos vendedores de uma empresa, e determine o total dos vencimentos, o maior vencimento, o menor vencimento e o vencimento médio: Vendedor A. Silva
Vencimento atual (€) 1200
B. Gomes
670
C. Costa
1489
E. Espregueira
1200
F. Fernandes
1450
G. Gaivão
500
T. Taveira
1500
Resolução do problema 1. Digite os registos dos vencimentos dos vendedores em A3:B10 2. Determine o total dos vencimentos, o maior vencimento, o menor vencimento e o vencimento médio
2.1. digitando 2.1.1. =SOMA(B4:B10) em E4 2.1.2. =MÁXIMO(B4:B10) em E5 2.1.3. =MÍNIMO(B4:B10) em E6 2.1.4. =ARRED(MÉDIA(B4:B10);2) em E7 Resultado esperado
3
A
B
Vendedor
Vencimento atual (€)
4 A. Silva
D
E (€)
1200
Total dos vencimentos
8009
5 B. Gomes
670
Maior vencimento
1500
6 C. Costa
1489
Menor vencimento
500
7 E. Espregueira
1200
Vencimento médio
1144,14
8 F. Fernandes
1450
9 G. Gaivão
500
10 T. Taveira
1500
? Exercício 1.7: Vários formatos Digite a seguinte tabela, respeitando os formatos:
Descrição
Valor em 103 Percentagem do total (€)
Computadores
10
12,50%
Impressora
25
31,25%
Tinteiros
35
43,75%
Discos externos
10
12,50%
Total
80
100,00%
Resolução do problema 1. Digite a tabela em A3:C8, ignorando os formatos 2. Formate o expoente de 103 2.1. selecionando 2.1.1. B3 2.1.2. o expoente 3 2.1.3. Base – Tipo de Letra – Superior à linha 3. Formate o cabeçalho da tabela 3.1. selecionando 3.1.1. A3 3.1.2. Base – Células – Formatar – Altura da Linha 3.2. digitando 30 3.3. selecionando 3.3.1. A3:C3 3.3.2. Base – Células – Formatar – Largura da Linha 3.4. digitando 16 3.5. selecionando 3.5.1. A3:C3 3.5.2. Base – Células – Formatar – Formatar Células
3.5.3. 3.5.4. 3.5.5. 3.5.6.
Alinhamento Centro Na horizontal Centro Na vertical Moldar texto
4. Formate a coluna Percentagem do Total 4.1. selecionando 4.1.1. C4:C8 4.1.2. Base – Células – Formatar – Formatar Células 4.1.3. Número 4.1.4. Percentagem 4.1.5. 2 em Casas decimais 5. Formate os bordos da tabela 5.1. selecionando 5.1.1. A3:C3 5.1.2. Base – Células – Formatar – Formatar Células 5.1.3. Limite 5.2. escolhendo 5.2.1. o traço em Estilo 5.2.2. a posição dos bordos em Limite 5.3. selecionando A4:C7 5.4. premindo F4 5.5. selecionando A8:C8 5.6. premindo F4 Resultado esperado
3
A
B
C
Descrição
Valor em 103 (€)
Percentagem do total
10
12,50%
4 Computadores
5 Impressoras
25
31,25%
6 Tinteiros
35
43,75%
7 Discos externos
10
12,50%
8 Total
80
100,00%