Cálculos Elementares com Excel - 74 exercícios by Grupo Lidel

CÁLCULOS ELEMENTARES COM EXCEL 74 EXERCÍCIOS Este livro, o primeiro da coleção Cadernos de Excel, visa a construção de uma base de conhecimento das funcionalidades do Excel que facilite o estudo e a aprendizagem dos conteúdos programáticos de diversas disciplinas e cursos. Através de 74 exercícios resolvidos, o leitor experimenta a formulação de problemas, uma melhor compreensão das soluções e uma automatização através de cálculos repetidos. Destinado a estudantes e docentes do ensino superior e de outros níveis de ensino, este livro, a par dos restantes títulos da coleção, é um instrumento precioso também para os profissionais das áreas de Economia, Gestão, Engenharia e outras. Cobre o Excel 2016 e 365 e é útil para as versões anteriores.

Operações Gerais: Dados e tipos de dados • Endereços • Formatos • Operações aritméticas • Funções embutidas • Gráficos • Passagem de dados entre folhas de cálculo • Validação de dados • Agrupamento de dados • Segmentação de dados • Tabelas dinâmicas • Proteção de dados • Listas personalizadas • Organogramas • Botões • Análise de hipóteses

ISBN 978-972-722-873-7

Figuras a cores e outros materiais disponíveis em www.fca.pt, até o livro se esgotar ou ser publicada nova edição atualizada ou com alterações.

978-972-722-873-7_k.indd 1

www.fca.pt

Matemática: Formatos • Expressões aritméticas • Unidades de medida • Composição de funções embutidas • Expressões lógicas • Equações • Gráficos • Sistemas de equações • Matrizes • Análise combinatória • Trigonometria • Simulação de cenários • Regressões lineares • Regressões exponenciais

ADELAIDE CARVALHO

CONTEÚDOS

CÁLCULOS ELEMENTARES COM EXCEL 74 EXERCÍCIOS

Cadernos de Excel

Cadernos de Excel 10-04-2017 22:29:50

EDIÇÃO FCA – Editora de Informática, Lda. Av. Praia da Vitória, 14 A – 1000-247 Lisboa Tel: +351 213 511 448 fca@fca.pt www.fca.pt DISTRIBUIÇÃO Lidel – Edições Técnicas, Lda. Rua D. Estefânia, 183, R/C Dto. – 1049-057 Lisboa Tel: +351 213 511 448 lidel@lidel.pt www.lidel.pt LIVRARIA Av. Praia da Vitória, 14 A – 1000-247 Lisboa Tel: +351 213 511 448 * Fax: +351 213 522 684 livraria@lidel.pt Copyright © 2016, FCA – Editora de Informática, Lda. ISBN edição impressa: 978-972-722-873-7 1ª edição eBook: setembro 2016 1ª edição impressa: abril 2017 Impressão e acabamento: Tipografia Lousanense, Lda. – Lousã Depósito Legal n.º 424572/17 Paginação de miolo e capa: FCA – Editora de Informática, Lda. Imagem de capa: José Manuel Ferrão – Look-Ahead

Marcas Registadas de FCA – Editora de Informática, Lda. –

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, exceto o permitido pelo CDADC, em termos de cópia privada pela AGECOP – Associação para a Gestão da Cópia Privada, através do pagamento das respetivas taxas.

ÍNDICE A Autora .......................................................................................................................... IX Prólogo ............................................................................................................................. XI 1. Operações Gerais ........................................................................................................... 1 ? Exercício 1.1: Ativação de suplementos ....................................................................... 1 ? Exercício 1.2: Ativação do Programador ...................................................................... 2 ? Exercício 1.3: Ativação do cálculo manual .................................................................. 3 ? Exercício 1.4: Definição de iterações e precisão .......................................................... 3 ? Exercício 1.5: Ilíquido, desconto, IVA e total a pagar.................................................. 4 ? Exercício 1.6: Total, maior e menor vencimentos ........................................................ 5 ? Exercício 1.7: Vários formatos ..................................................................................... 6 ? Exercício 1.8: De euros para dólares ............................................................................ 7 ? Exercício 1.9: Subida de preços .................................................................................... 9 ? Exercício 1.10: Distribuição percentual das vendas ..................................................... 9 ? Exercício 1.11: Maior vencimento.............................................................................. 11 ? Exercício 1.12: Poupança imediata ............................................................................. 12 ? Exercício 1.13: Pauta de exame .................................................................................. 13 ? Exercício 1.14: Formatos para provas de computação ............................................... 14 ? Exercício 1.15: Formatos para artigos em stock ......................................................... 15 ? Exercício 1.16: Controlo de faturas ............................................................................ 17 ? Exercício 1.17: Extrato bancário ................................................................................ 18 ? Exercício 1.18: Concurso televisivo ........................................................................... 20 ? Exercício 1.19: Classificação de testes ....................................................................... 22 ? Exercício 1.20: Organograma ..................................................................................... 23 ? Exercício 1.21: Botão de controlo .............................................................................. 24 ? Exercício 1.22: Gráfico de setores .............................................................................. 25 © FCA V

Cálculos Elementares com Excel – 74 Exercícios ? Exercício 1.23: Gráfico de barras ............................................................................... 26 ? Exercício 1.24: Gráfico de função linear .................................................................... 27 ? Exercício 1.25: Efetivos de escalões etários ............................................................... 29 ? Exercício 1.26: Agrupamento de turmas .................................................................... 31 ? Exercício 1.27: Totais regionais ................................................................................. 33 ? Exercício 1.28: Totais por vendedor ........................................................................... 35 ? Exercício 1.29: Peso relativo dos vendedores............................................................. 36 ? Exercício 1.30: Escalões de vendas ............................................................................ 37 ? Exercício 1.31: Cursos por sexo ................................................................................. 38 ? Exercício 1.32: Segmentação de dados....................................................................... 39 ? Exercício 1.33: Vendedores por zona ......................................................................... 41 ? Exercício 1.34: Vendedores complementares ............................................................. 42 ? Exercício 1.35: Conjunção e disjunção de vendedores ............................................... 43 ? Exercício 1.36: Eliminação de duplicados .................................................................. 44 ? Exercício 1.37: Lista personalizada ............................................................................ 46 ? Exercício 1.38: Validação de classificações ............................................................... 47 ? Exercício 1.39: Lista de valores possíveis .................................................................. 49 ? Exercício 1.40: Proteção de fórmulas ......................................................................... 50 ? Exercício 1.41: Concurso de perguntas e respostas .................................................... 51 2. Matemática ................................................................................................................... 55 ? Exercício 2.1: Expressão aritmética............................................................................ 55 ? Exercício 2.2: Função composta ................................................................................. 56 ? Exercício 2.3: Sistemas de numeração ....................................................................... 57 ? Exercício 2.4: Medidas de comprimento .................................................................... 58 ? Exercício 2.5: Medida de ângulos............................................................................... 58 ? Exercício 2.6: Arredondamentos ................................................................................ 59 ? Exercício 2.7: Somatórios ........................................................................................... 60 ? Exercício 2.8: Extração de dígitos .............................................................................. 61

VI © FCA

Índice ? Exercício 2.9: Máximo divisor comum (MDC).......................................................... 62 ? Exercício 2.10: Mínimo múltiplo comum (MMC) ..................................................... 63 ? Exercício 2.11: Teorema de Pitágoras ........................................................................ 64 ? Exercício 2.12: Equação de 1.º grau ........................................................................... 65 ? Exercício 2.13: Equação de 2.º grau ........................................................................... 66 ? Exercício 2.14: Parâmetro da equação de 3.º grau...................................................... 67 ? Exercício 2.15: Funções trigonométricas.................................................................... 69 ? Exercício 2.16: Sinusoide e cossinusoide ................................................................... 69 ? Exercício 2.17: Resolução de triângulos..................................................................... 71 ? Exercício 2.18: Operações lógicas .............................................................................. 73 ? Exercício 2.19: Conjunções, disjunções e negações ................................................... 74 ? Exercício 2.20: Fatoriais ............................................................................................. 75 ? Exercício 2.21: Permutações simples ......................................................................... 76 ? Exercício 2.22: Arranjos e combinações .................................................................... 78 ? Exercício 2.23: Arranjos simples ................................................................................ 79 ? Exercício 2.24: Combinações de bombons ................................................................. 80 ? Exercício 2.25: Combinações de apostas .................................................................... 81 ? Exercício 2.26: Multiplicação de matriz por escalar .................................................. 82 ? Exercício 2.27: Multiplicação de matrizes.................................................................. 83 ? Exercício 2.28: Determinante e matriz inversa ........................................................... 85 ? Exercício 2.29: Sistema de 2 equações a 2 incógnitas ................................................ 86 ? Exercício 2.30: Método de Gauss-Jordan para sistemas de equações ........................ 87 ? Exercício 2.31: Ajustamento a C=aR+b ..................................................................... 89 ? Exercício 2.32: Ajustamento a Y=aW+bP+c.............................................................. 91 ? Exercício 2.33: Ajustamento a C=abT......................................................................... 93 Bibliografia ....................................................................................................................... 95 Índice Remissivo .............................................................................................................. 97

A AUTORA 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).

Cálculos Elementares com Excel 74 Exercícios

1. Operações Gerais (41 exercícios) 2. Matemática (33 exercícios)

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).

Cálculos Elementares com Excel – 74 Exercícios

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.

XII © FCA

Prólogo 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

Nota: Figuras a cores e outros materiais disponíveis para download gratuito na página do livro em www.fca.pt.

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

Cálculos Elementares com Excel – 74 Exercícios

? 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

2 © FCA

Operações Gerais

? 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

Cálculos Elementares com Excel – 74 Exercícios 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 Desconto = Ilíquido ∗ Taxa de Desconto Líquido = Ilíquido − Desconto IVA = Líquido ∗ Taxa de IVA Total a Pagar = Líquido + IVA

Resolução do problema 1. Digite em A3:B7 A

Valor ilíquido

1200

Desconto (5%)

Valor líquido

IVA (23%)

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 4 © FCA

2. MATEMÁTICA Este capítulo versa os números – inteiros e reais − e as operações em que estes podem ser operadores, apresentando funcionalidades do Excel que as resolvem. Os 33 exercícios que contém ilustram: cálculo de expressões aritméticas; composição de funções embutidas; conversão entre unidades de medida; cálculo de operações com frações; resolução de equações de 1.º e 2.º graus; cálculo de funções trigonométricas; desenho de gráficos; resolução de operações lógicas; cálculo matricial; resolução de sistemas de equações; contagens da análise combinatória; e regressões lineares e exponenciais. Antes de iniciar a resolução dos exercícios deste capítulo: I – Defina, recorrendo ao sistema operativo Windows, a vírgula como separador decimal e o ponto e vírgula como separador de argumentos das funções. II – Ative o suplemento Analysis ToolPak.

? Exercício 2.1: Expressão aritmética Calcule a seguinte expressão aritmética: 4 ± √3 ∗ 5 + 66 3 ∗ 25

Formulação do problema

4 + √3 ∗ 5 + 66 13 = 3 ∗ 25 96

4 ± √3 ∗ 5 + 66 = 3 ∗ 25 4 − √3 ∗ 5 + 66 5 =− 5 3∗2 96

Cálculos Elementares com Excel – 74 Exercícios Resolução do problema 1. Digite em A3 1.1. =(4+RAIZQ(3*5+66))/(3*2^5) 2. Digite em C3 2.1. =(4-RAIZQ(3*5+66))/(3*2^5) 3. Formate A3 e C3 para fração 3.1. selecionando 3.1.1. A3:C3 3.1.2. Base – Células – Formatar – Formatar Células 3.1.3. Fração em Número 3.1.4. Até 3 algarismos (312/943) em Tipo Resultado esperado A 13/96

B ou

C - 5/96

? Exercício 2.2: Função composta Calcule a seguinte função para valores de X=100, 101, 102, 103, 104, 105: f(X) = |5 − log10 X|

Resolução do problema 1. Digite os expoentes das potências de 10 em A4:A9 2. Calcule os valores de X 2.1. digitando =10^A4 em B4 2.2. copiando B4 para B5:B9 3. Calcule F(X) para cada valor de X 3.1. digitando =ABS(5-LOG10(B4)) em C4 3.2. copiando C4 para C5:C9 Resultado esperado A 3 4 5

0 1

B X 1 10

C F(X) 5 4 (continua)

56 © FCA

Matemática (continuação)

6 7 8 9

2 3 4 5

100 1000 10000 100000

3 2 1 0

? Exercício 2.3: Sistemas de numeração Converta os seguintes números escritos em binário para os sistemas decimal e hexadecimal: Sistema binário

1010

10000

10010

100000

Formulação do problema Por exemplo: (1010)2= 1*23+0*22+2*21+0*20 =(10)10 (100000)2=1*25+0*24+0*23+0*22+0*21 +0*20 =(32)10 (100000)2=(20)16

Resolução do problema 1. Digite os números escritos no sistema binário em B3:F3 2. Converta o primeiro número para o sistema decimal 2.1. digitando =BINADEC(B3) em B4 3. Converta o primeiro número para o sistema hexadecimal 3.1. digitando =BINAHEX(B3) em B5 4. Converta os restantes números binários 4.1. copiando B4:B5 para C4:F5 Resultado esperado 3

A Sistema binário

B 10

C 1010

D 10000

E 10010

F 100000

4 5

Sistema decimal

Sistema hexadecimal