Utilização do Excel para Economia e Gestão by Grupo Lidel

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-775-4 1.ª edição impressa: novembro 2016 Paginação: Alice Simões Impressão e acabamento: Tipografia Lousanense, Lda - Lousã Depósito Legal n.º 417919/16 Capa: José M. Ferrão – Look-Ahead

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

Este livro encontra-se escrito ao abrigo das regras de ortografia aprovadas pelo Novo Acordo Ortográfico de 1990. No entanto, as figuras mantêm a terminologia técnica de acordo com o software, o qual ainda se encontra na grafia antiga. 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

Introdução ......................................................................................................................................1 Parte I - FUNCIONALIDADES BÁSICAS ..........................................................................................3 1. Ambiente de Trabalho ...............................................................................................................5

1.1 Friso ............................................................................................................................... 7 1.1.1 Separador Ficheiro/Vista Backstage .................................................................. 10 1.2 Barra de Ferramentas de Acesso Rápido ...................................................................... 13 1.3 Livros e Folhas ............................................................................................................. 15 1.4 Exercícios Resolvidos (ER) ......................................................................................... 16 2. Operações Básicas ...................................................................................................................19

2.1 Operações com Livros e Folhas ................................................................................... 19 2.2 Edição e Manipulação de Células ................................................................................ 21 2.2.1 Formatação Básica de Células............................................................................ 22 2.2.2 Formatação Condicional .................................................................................... 34 2.3 Área de Transferência: Opções de Colar ...................................................................... 37 2.4 Exercícios Resolvidos (ER) ......................................................................................... 39 2.5 Exercícios Complementares (EC) ................................................................................ 53 3. Configuração de Páginas e Impressão ....................................................................................55

3.1 Configuração de Páginas .............................................................................................. 55 3.1.1 Tamanho e Orientação da Página ....................................................................... 57 3.1.2 Margens .............................................................................................................. 58 3.1.3 Cabeçalhos e Rodapés ........................................................................................ 60 3.1.4 Quebras de Página .............................................................................................. 63 3.2 Imprimir ....................................................................................................................... 63 3.3 Criação de Ficheiros PDF/XPS .................................................................................... 66 3.4 Exercícios Resolvidos (ER) ......................................................................................... 67 3.5 Exercícios Complementares (EC) ................................................................................ 71 Parte II - FÓRMULAS E FUNÇÕES ................................................................................................73 4. Introdução a Fórmulas e Funções ............................................................................................75

4.1 Operadores ................................................................................................................... 76 4.1.1 Precedência de Operadores ................................................................................ 78 4.2 Referências ................................................................................................................... 78 4.2.1 Referências Relativas ......................................................................................... 79 4.2.2 Referências Absolutas ........................................................................................ 80 4.2.3 Referências Mistas ............................................................................................. 80 © FCA - Editora de Informática

Utilização do Excel para Economia & Gestão 4.2.4 Referências por Nome ........................................................................................ 80 4.2.5 Referências Externas .......................................................................................... 81 4.2.6 Referências Circulares........................................................................................ 84 4.3 Funções ........................................................................................................................ 84 4.3.1 Funções Aninhadas ............................................................................................ 86 4.3.2 Erros em Fórmulas e Funções ............................................................................ 88 4.4 Exercícios Resolvidos (ER) ......................................................................................... 89 4.5 Exercícios Complementares (EC) ................................................................................ 93 5. Funções Matemáticas ..............................................................................................................95

5.1 Cálculo de Totais Simples e Condicionais ................................................................... 95 5.2 Arredondamento de Valores ........................................................................................ 97 5.3 Geração de Números Aleatórios .................................................................................. 99 5.4 Cálculos Matemáticos ................................................................................................ 101 5.5 Lista de Funções Matemáticas e de Trigonometria .................................................... 103 5.6 Exercícios Resolvidos (ER) ....................................................................................... 107 5.7 Exercícios Complementares (EC) .............................................................................. 109 6. Funções de Estatística ........................................................................................................... 111

6.1 Medidas de Tendência Central ................................................................................... 111 6.2 Medidas de Dispersão ................................................................................................ 116 6.3 Contagem de Células.................................................................................................. 118 6.4 Frequência de Valores/Dados..................................................................................... 120 6.5 Máximo, Mínimo e N-ésimos Valores ....................................................................... 124 6.6 Lista de Funções de Estatística................................................................................... 126 6.7 Exercícios Resolvidos (ER) ....................................................................................... 131 6.8 Exercícios Complementares (EC) .............................................................................. 136 7. Funções Financeiras ............................................................................................................. 139

7.1 Cálculo de Valor Inicial e Valor Final ....................................................................... 140 7.2 Cálculo de Taxas ........................................................................................................ 142 7.3 Cálculo de Períodos.................................................................................................... 143 7.4 Cálculo de Prestações, Juros e Amortizações ............................................................ 144 7.5 Lista de Funções Financeiras ..................................................................................... 146 7.6 Exercícios Resolvidos (ER) ....................................................................................... 149 7.7 Exercícios Complementares (EC) .............................................................................. 154 8. Funções de Data e Hora........................................................................................................ 155

8.1 Representação de Datas e Horas ................................................................................ 155 8.2 Cálculos com Datas .................................................................................................... 156 8.3 Cálculos com Horários (Horas) .................................................................................. 161 8.4 Lista de Funções de Data e Hora ................................................................................ 163 8.5 Exercícios Resolvidos (ER) ....................................................................................... 165 8.6 Exercícios Complementares (EC) .............................................................................. 167 VI

Índice 9. Funções Lógicas .................................................................................................................... 169

9.1 Lista de Funções Lógicas ........................................................................................... 173 9.2 Exercícios Resolvidos (ER) ....................................................................................... 174 9.3 Exercícios Complementares (EC) .............................................................................. 178 10. Funções de Consulta e Referência ...................................................................................... 181

10.1 Lista de Funções de Consulta e Referência .............................................................. 185 10.2 Exercícios Resolvidos (ER) ..................................................................................... 186 10.3 Exercícios Complementares (EC) ............................................................................ 190 11. Funções de Texto e de Informações ................................................................................... 193

11.1 Funções de Texto ..................................................................................................... 193 11.1.1 Conversão do Tipo de Dados ......................................................................... 193 11.1.2 Tratamento de Texto ...................................................................................... 195 11.1.3 Localização e Extração de Caracteres ............................................................ 197 11.1.4 Lista de Funções de Texto .............................................................................. 200 11.2 Funções de Informações ........................................................................................... 202 11.2.1 Lista de Funções de Informações ................................................................... 203 11.3 Exercícios Resolvidos (ER) ..................................................................................... 204 11.4 Exercícios Complementares (EC) ............................................................................ 210 12. Funções de Bases de Dados ............................................................................................... 213

12.1 Lista de Funções de Bases de Dados ........................................................................ 216 12.2 Exercícios Resolvidos (ER) ..................................................................................... 217 12.3 Exercícios Complementares (EC) ............................................................................ 223 Parte III - ANÁLISE E GESTÃO DE DADOS ................................................................................ 225 13. Gráficos .............................................................................................................................. 227

13.1 Gráficos .................................................................................................................... 227 13.1.1 Categorias e Tipos de Gráficos ...................................................................... 227 13.1.2 Elementos de um Gráfico ............................................................................... 229 13.1.3 Configuração e Formatação de Gráficos ........................................................ 231 13.2 Gráficos Sparkline .................................................................................................... 234 13.3 Exercícios Resolvidos (ER) ..................................................................................... 238 13.4 Exercícios Complementares (EC) ............................................................................ 252 14. Operações sobre Tabelas ................................................................................................... 255

14.1 Ordenação de Tabelas .............................................................................................. 256 14.2 Conversão de Texto para Colunas ............................................................................ 259 14.3 Preenchimento Automático ...................................................................................... 263 14.4 Remoção de Duplicados ........................................................................................... 267 14.5 Validação de Dados.................................................................................................. 270 © FCA - Editora de Informática

VII

Utilização do Excel para Economia & Gestão 14.6 Consolidação de Dados ............................................................................................ 273 14.7 Subtotais ................................................................................................................... 276 14.8 Importação de Dados Externos ................................................................................ 278 14.8.1 Importação de Ficheiros do Microsoft Access ............................................... 279 14.8.2 Importação de Ficheiros de Texto .................................................................. 280 14.8.3 Importação de Dados da Web ......................................................................... 281 14.9 Exercícios Resolvidos (ER) ..................................................................................... 282 14.10 Exercícios Complementares (EC) .......................................................................... 298 15. Filtros de Dados .................................................................................................................. 301

15.1 Filtros Automáticos .................................................................................................. 302 15.1.1 Critérios de Filtros Automáticos .................................................................... 304 15.2 Filtros Avançados..................................................................................................... 306 15.2.1 Critérios de Filtros Avançados ....................................................................... 308 15.3 Exercícios Resolvidos (ER) ..................................................................................... 312 15.4 Exercícios Complementares (EC) ............................................................................ 318 16. Tabelas e Gráficos Dinâmicos ............................................................................................ 321

16.1 Tabelas Dinâmicas ................................................................................................... 321 16.1.1 Áreas de uma Tabela Dinâmica...................................................................... 325 16.1.2 Configuração de Tabelas Dinâmicas .............................................................. 326 16.2 Gráficos Dinâmicos .................................................................................................. 336 16.2.1 Configuração de Gráficos Dinâmicos ............................................................ 338 16.3 Slicers ou Segmentações de Dados .......................................................................... 339 16.4 Exercícios Resolvidos (ER) ..................................................................................... 341 16.5 Exercícios Complementares (EC) ............................................................................ 347 17. Análise de Hipóteses .......................................................................................................... 349

17.1 Tabelas de Dados/Simulação ................................................................................... 350 17.2 Cenários ................................................................................................................... 355 17.2.1 Sumários de Cenários ..................................................................................... 358 17.3 Atingir Objetivo ....................................................................................................... 359 17.4 Solver (Solucionador)............................................................................................... 361 17.4.1 Instalação do Suplemento do Solver .............................................................. 361 17.4.2 Utilização do Solver ....................................................................................... 362 17.5 Exercícios Resolvidos (ER) ..................................................................................... 368 17.6 Exercícios Complementares (EC) ............................................................................ 377 18. Ferramentas de Análise ...................................................................................................... 381

18.1 Instalação do Suplemento Analysis ToolPak............................................................ 381 18.2 Descrição das Ferramentas Analysis ToolPak .......................................................... 382 18.2.1 Análise de Variância (ANOVA) .................................................................... 382 18.2.2 Correlação ...................................................................................................... 383 18.2.3 Covariância .................................................................................................... 383 VIII

Índice 18.2.4 Estatística Descritiva ...................................................................................... 384 18.2.5 Ajuste Exponencial ........................................................................................ 384 18.2.6 Teste F: Duas Amostras para Variâncias ....................................................... 384 18.2.7 Análise de Fourier ......................................................................................... 384 18.2.8 Histograma ..................................................................................................... 384 18.2.9 Média Móvel .................................................................................................. 385 18.2.10 Geração de Número Aleatório...................................................................... 385 18.2.11 Ordem e Percentil ......................................................................................... 385 18.2.12 Regressão ..................................................................................................... 385 18.2.13 Amostragem ................................................................................................. 385 18.2.14 Teste T .......................................................................................................... 386 18.2.15 Teste Z .......................................................................................................... 386 18.3 Exercícios Resolvidos (ER) ..................................................................................... 387 18.4 Exercícios Complementares (EC) ............................................................................ 393 Parte IV - AUTOMATIZAÇÃO DE TAREFAS.............................................................................. 395 19. Macros de Comandos ......................................................................................................... 397

19.1 Ativação do Separador Programador ....................................................................... 398 19.2 Segurança nas Macros .............................................................................................. 398 19.3 Gravador de Macros de Comandos .......................................................................... 400 19.3.1 Referências Relativas vs. Absolutas ............................................................... 402 19.4 Execução de Macros ................................................................................................ 402 19.4.1 Tecla de Atalho .............................................................................................. 403 19.4.2 Associar uma Macro a um Botão ................................................................... 404 19.5 Edição de Macros ..................................................................................................... 406 19.6 Eliminação de Macros .............................................................................................. 407 19.7 Exercícios Resolvidos (ER) ..................................................................................... 407 19.8 Exercícios Complementares (EC) ............................................................................ 412 20. Funções Definidas pelo Utilizador ...................................................................................... 413

20.1 Editor de Visual Basic .............................................................................................. 414 20.2 Módulos VBA .......................................................................................................... 415 20.3 Linguagem Visual Basic .......................................................................................... 416 20.3.1 Estrutura de uma Função ................................................................................ 416 20.3.2 Variáveis e Tipos de Dados ............................................................................ 417 20.3.3 Operadores e Funções VBA ........................................................................... 419 20.3.4 Estruturas de Controlo .................................................................................... 421 20.4 Exercícios Resolvidos (ER) ..................................................................................... 423 20.5 Exercícios Complementares (EC) ............................................................................ 426 Índice Remissivo ....................................................................................................................... 427

Introdução NotaPrévia

O Excel é indiscutivelmente uma ferramenta de grande utilidade e aplicação prática em diversos domínios e, em particular, em Economia e Gestão. Para além de permitir realizar cálculos numéricos complexos, entre muitas outras funcionalidades, possibilita a criação personalizada de gráficos e de diagramas elaborados, a organização, gestão e análise de dados em listas ou tabelas, a importação de dados de diferentes origens e a automatização de tarefas complexas através de macros de comandos e de funções. Neste livro procurar-se-á abordar e apresentar as principais funcionalidades do Excel 2016, cuja utilidade para o domínio da Economia e Gestão são indiscutíveis. Na elaboração deste livro foi utilizada a versão standard do Excel 2016 para Windows, pelo que algumas das funcionalidades poderão não estar disponíveis ou funcionar de modo diferente em outras versões, como por exemplo, a versão online disponível na web através do Office 365, a versão app para os dispositivos móveis ou a versão para Mac. A maioria dos exercícios apresentados neste livro podem ser realizados no Excel 2013, com a exceção daqueles que cobrem as funcionalidades introduzidas no Excel 2016. Em termos de estrutura, os diversos capítulos que constituem este livro encontram-se organizados e agrupados em quatro partes, nomeadamente:  Parte I – Funcionalidades Básicas: esta parte reúne os capítulos iniciais onde são apresentados os conceitos e as funcionalidades básicas, fundamentais para a compreensão e utilização do Excel.  Parte II – Fórmulas e Funções: os capítulos aqui disponibilizados abordam com algum detalhe as regras para a construção de fórmulas e as funções mais utilizadas nas principais categorias de funções do Excel.  Parte III – Análise e Gestão de Dados: neste grupo de capítulos é dada particular atenção às principais funcionalidades do Excel que são utilizadas frequentemente na análise, tratamento e gestão de dados.  Parte IV – Automatização de Tarefas: inclui os dois capítulos finais, onde, sem grandes detalhes, é apresentado o essencial de macros de comandos e de funções no Excel, para que qualquer utilizador possa tirar partido da sua criação e utilização. © FCA - Editora de Informática

Utilização do Excel para Economia & Gestão Relativamente à estrutura de cada um dos capítulos, foi seguida uma lógica de apresentação em primeiro lugar dos conteúdos e, em seguida, de prática e consolidação desses mesmos conteúdos. Desta forma, em cada um dos capítulos é realizada sequencialmente:  A apresentação dos fundamentos teórico-práticos associados à aplicação e utilização das funcionalidades do Excel consideradas.  A resolução assistida de um conjunto de exercícios (ER) com a finalidade de promover a prática e o teste das funcionalidades apresentadas.  A disponibilização de um conjunto de exercícios complementares (EC) para consolidação dos conhecimentos adquiridos. No que se refere aos exercícios resolvidos e complementares, nos respetivos enunciados estão identificados os nomes dos ficheiros e das folhas onde estes deverão ser resolvidos. Por exemplo, para o Exercício Resolvido 25 (ER 25), é solicitada a resolução deste exercício em “FTExcel14xlsx/ER25”, ou seja, na folha “ER25” do ficheiro “FTExcel14.xlsx”. De forma a facilitar a sua resolução, estará disponível em http://www.fca.pt um conjunto de ficheiros de Excel com a estrutura-base das folhas necessárias para cada exercício. Para terminar, quaisquer comentários e/ou sugestões sobre os conteúdos deste livro serão muito bem-vindos, pelo que poderei ser contactado pelo endereço de correio eletrónico: luissilvarodrigues@gmail.com.

Ambiente de Trabalho

1.AmbientedeTrabalho

Um conhecimento adequado do ambiente de trabalho do Excel 2016 é fundamental para uma utilização eficiente da ferramenta. Este conhecimento adequado pressupõe não apenas o saber identificar os principais componentes (Figura 1.1), mas, fundamentalmente, conhecer as funcionalidades que estes permitem aceder, que, por sua vez, possibilitam a introdução, manipulação e análise dos conteúdos de uma folha de cálculo. Separadores

Barra de Acesso Rápido

Friso

Folha/ Área de Trabalho Grupo(s)

Barra de Fórmulas Célula Ativa

Comando(s)

Separador de Folhas Barra de Estado

Modos de Visualização

Zoom

Fig. 1.1: Ambiente de Trabalho do Excel 2016

Utilização do Excel para Economia & Gestão De acordo com o apresentado na figura anterior, os principais componentes que constituem o ambiente de trabalho do Excel 2016 são:  Folha/Área de Trabalho: matriz composta por 17.179.869.184 células, que resultam da interseção de 1.048.576 linhas com 16.384 colunas (ver Secção 1.3).  Célula Ativa: indica a célula da folha onde poderão ser introduzidos os dados ou aplicadas as funcionalidades desejadas.  Barra de Fórmulas: permite observar ou editar o conteúdo da célula ativa.  Friso: agrupa, através de Separadores e Grupos, os Comandos necessários para aceder às diversas funcionalidades (ver Secção 1.1).  Barra de (Ferramentas de) Acesso Rápido: permite o acesso simples e rápido (no topo da janela do Excel) aos comandos utilizados com maior frequência (ver Secção 1.2).  Separador de Folhas: permite visualizar as folhas/áreas de trabalho existentes num livro (nome genérico dado a um documento do Excel).  Barra de Estado: apresenta diversas mensagens e o estado das teclas NUM LOCK, CAPS LOCK e SCROLL LOCK.  Área de Modos de Visualização: disponibiliza os botões que permitem alterar o modo como a folha é visualizada (vistas normal, esquema de página ou pré-visualização).  Área de Zoom: disponibiliza os botões que permitem aumentar ou reduzir o tamanho de visualização das células. Para além destes componentes, são ainda de salientar os Menus de Atalho/Contexto, onde se pode incluir o Ícone da Ferramenta de Análise Rápida, que permitem aos utilizadores acederem mais rapidamente a determinadas funcionalidades do Excel (Figura 1.2). No que se refere à Ferramenta de Análise Rápida, quando selecionada uma tabela de dados, esta disponibiliza os seguintes separadores:  Formatação: com atalhos para as funcionalidades de formatação condicional (a abordar na Secção 2.2.2).  Gráficos: com atalhos para a geração de gráficos (a abordar na Secção 13.1).  Totais: com atalhos para a inserção de colunas e linhas de estatísticas na tabela selecionada. 6

Operações Básicas

2.OperaçõesBásicas

Como referido no capítulo anterior, o Excel utiliza uma estrutura de armazenamento dos dados baseada em livros, folhas e células. Um livro poderá ser constituído por uma ou mais folhas (por defeito um livro é criado apenas com uma folha) que, por sua vez, disponibilizam uma área de trabalho composta por uma matriz de células, identificadas pela letra da coluna e pelo número da linha onde se posicionam. Nas secções seguintes serão apresentadas as principais operações relacionadas com os livros e folhas (Secção 2.1), com a edição e manipulação de células (Secção 2.2) e com a transferência de dados (Secção 2.3).

2.1 OPERAÇÕES COM LIVROS E FOLHAS No que se refere às operações com livros, na Tabela 2.1 são apresentadas as principais operações com livros e a localização dos respetivos comandos no Excel. Operação

Localização dos comandos no Excel

Criar um novo livro

Separador Ficheiro, comando/separador Novo.

Abrir um livro

Separador Ficheiro, comando Abrir.

Guardar um livro

Separador Ficheiro, comando Guardar (para livros já guardados); ou Separador Ficheiro, comando Guardar Como (para guardar pela primeira vez).

Partilhar um livro4

Separador Ficheiro, comando Partilhar.

Fechar um livro

Separador Ficheiro, comando Fechar.

Imprimir um livro

Separador Ficheiro, comando/separador Imprimir.

Tab. 2.1: Principais operações com livros 3

Ao guardar um livro pode optar pela gravação do mesmo na Nuvem (Cloud), nomeadamente no OneDrive, pelo que será necessário que o utilizador tenha uma conta no mesmo.

A partilha de livros no Excel na Nuvem (Cloud) pressupõe que o utilizador tenha uma conta no OneDrive. © FCA - Editora de Informática

Utilização do Excel para Economia & Gestão Nota Na secção de Exercícios Resolvidos, no ER 2 serão apresentados alguns exemplos destas operações com livros.

Relativamente às operações com folhas, na Tabela 2.2 são apresentadas as principais operações e a respetiva localização dos comandos no Excel 2016. Operação

Localização dos comandos no Excel

Inserir nova folha

Separador Base, grupo Células, comando Inserir, opção Inserir Folha.

Eliminar folha

Separador Base, grupo Células, comando Eliminar, opção Eliminar Folha.

Mover ou copiar folha

Separador Base, grupo Células, comando Formatar, opção Mover ou Copiar; ou Separador de Folhas, fazer clique com o botão direito do rato e escolher a opção Mover ou Copiar.

Mudar o nome da folha

Separador Base, grupo Células, comando Formatar, opção Mudar o Nome da Folha; ou Separador de Folhas, sobre a folha desejada fazer duplo clique e editar o nome da folha.

Proteger/Desproteger folha

Separador Base, grupo Células, comando Formatar, opção Proteger Folha.

Ocultar/Mostrar folha

Separador Base, grupo Células, comando Formatar, opção Ocultar e Mostrar; ou Separador de Folhas, sobre a folha desejada fazer clique com o botão direito do rato e escolher a opção Ocultar ou Mostrar.

Alterar cor do separador da folha

Separador Base, grupo Células, comando Formatar, opção Cor do Separador; ou Separador de Folhas, sobre a folha fazer clique com o botão direito do rato e escolher a opção Cor do Separador.

Tab. 2.2: Operações básicas com folhas

Nota Na secção de Exercícios Resolvidos, no ER 3, serão apresentados alguns exemplos de operações básicas com folhas.

Introdução a Fórmulas e Funções

4.IntroduçãoaFórmulaseFunções

Uma fórmula é uma expressão, introduzida numa determinada célula, calculada automaticamente pelo Excel a partir de valores introduzidos na própria fórmula e/ou disponíveis noutras células. As fórmulas possibilitam a realização de diversas operações, desde simples cálculos matemáticos (e.g., adição ou multiplicação) a comparação de valores, concatenação de texto ou cálculos complexos com recurso a funções. Para introduzir uma fórmula numa determinada célula é obrigatória a colocação, no início da mesma, do símbolo “=”, pois caso isso não seja feito o Excel assumirá que o conteúdo introduzido não constitui uma fórmula mas sim um valor, não efetuando por essa razão o cálculo desejado. Assim, o símbolo “=” informa o Excel de que os caracteres/elementos que se seguem constituem uma fórmula e que os deverá interpretar como uma expressão, considerando várias operações e valores. O Excel processa as expressões das fórmulas da esquerda para a direita, respeitando uma ordem específica definida pela prioridade de cálculo (precedência) de cada um dos operadores utilizados na fórmula. Em termos genéricos, uma fórmula poderá ser constituída pelos seguintes elementos:  Valores.  Operadores.  Referências (a células).  Funções. A título de exemplo, na fórmula =(A1+10)/SOMA(A2:A4;5) utiliza-se:  Os valores 10 e 5.  Os operadores de cálculo de adição “+” e de divisão “/”.  A função SOMA.  As referências A1, A2:A4.  E ainda os parênteses (para alterar a ordem ou prioridade de cálculo dos operadores).

Utilização do Excel para Economia & Gestão Tendo em conta a prioridade de cálculo dos operadores e os agrupamentos estabelecidos pelos parênteses, em primeiro lugar, o Excel adiciona o valor contido na célula A1 ao valor 10 e, em seguida, divide esse resultado pelo valor resultante da aplicação da função SOMA ao intervalo de células A2:A4 e ao valor 58.

4.1 OPERADORES Os operadores especificam o tipo de cálculo que se pretende realizar com os restantes elementos de uma fórmula. O Excel inclui quatro tipos de operadores:  Aritméticos.  De comparação.  De referência.  De texto. Os Operadores Aritméticos apresentados na Tabela 4.1 são utilizados para executar operações matemáticas básicas (e.g., adição, subtração ou multiplicação) e produzir resultados numéricos. Operador

Significado

Exponencial (e.g., 5^2).

Percentagem (e.g., 5%).

Multiplicação.

Divisão.

Adição.

Subtração (e.g., 5-2) ou Negação (e.g., -2).

Tab. 4.1: Operadores Aritméticos

Os Operadores de Comparação apresentados na Tabela 4.2 possibilitam a comparação de valores, sendo o resultado de uma comparação um valor lógico que poderá assumir o valor VERDADEIRO ou o valor FALSO. 8

Em termos práticos a função SOMA(A2:A4;5) representa a expressão A2+A3+A4+5. 76

Funções Matemáticas

5.FunçõesMatemáticas

As funções disponibilizadas pelo Excel na categoria de Matemática e Trigonometria permitem realizar inúmeros cálculos, desde simples cálculos de totais até cálculos mais complexos com matrizes. A extensa lista de funções disponibilizada nesta categoria poderá ser dividida em três grupos de funções: matemáticas, trigonométricas e matriciais. Obviamente, nem todas as funções de Matemática e Trigonometria têm utilidade para resolver problemas de Economia e Gestão, no entanto, algumas destas funções poderão ser bastante úteis nomeadamente no cálculo de totais simples e condicionais, no arredondamento de números e na geração de números aleatórios.

5.1 CÁLCULO DE TOTAIS SIMPLES E CONDICIONAIS As funções que permitem calcular totais a partir de um conjunto de dados são indiscutivelmente das mais utilizadas no Excel. Neste particular destacam-se duas funções: a função SOMA e a função SOMA.SE, cujas sintaxes são as seguintes: SOMA(núm1; núm2; ...) núm1; núm2;...: conjunto de valores numéricos (até 255) que se pretende somar. SOMA.SE(intervalo; critérios; [intervalo_soma]) intervalo: intervalo de células onde se aplicará o critério de seleção. critérios: critério na forma de um número, expressão ou texto que será aplicado sobre o intervalo. Exemplos: 100, “100”, “>100” ou “Porto”. intervalo_soma: intervalo de células onde será efetuada a operação de soma de valores. Apenas serão somadas as células que correspondam aos critérios definidos. Se intervalo_soma for omitido, por defeito, serão consideradas no cálculo as células definidas no argumento intervalo.

A função SOMA permite calcular um total a partir de um conjunto de valores numéricos passados como argumentos da função. É importante referir que os argumentos da função SOMA poderão ser números, valores lógicos ou representações em forma de texto numérico. Nas células formatadas com os formatos de © FCA - Editora de Informática

Utilização do Excel para Economia & Gestão Número, Data, Hora, Moeda, Contabilidade, Percentagem e Fração, apesar de na sua apresentação/visualização conterem letras e/ou outros caracteres, os seus valores continuam a ser considerados pelo Excel como valores numéricos. Considere o seguinte exemplo de uma tabela (Figura 5.1) que contém informação de vendas, nomeadamente o nome do vendedor, a região de vendas e o respetivo valor de vendas.

Fig. 5.1: Tabela-exemplo

Suponha que se pretende calcular o total das vendas de todos os vendedores em todas as regiões. Utilizando a função SOMA apenas é necessário definir como argumento o intervalo de células a calcular, ou seja, o intervalo C2:C8, pelo que a fórmula adequada para o cálculo do total de vendas é: =SOMA(C2:C8)  devolve como resultado o valor 37.659€

Na função SOMA, à semelhança do que acontece com outras funções do Excel, quando são utilizadas referências de células como argumentos, as células em branco ou as que contêm texto são ignoradas pelo Excel. Quando uma célula contém um valor de erro12, a função SOMA não consegue efetuar qualquer cálculo e devolve como resultado esse mesmo erro. Se experimentar a fórmula apresentada em baixo, poderá verificar que o resultado devolvido será precisamente o mesmo do exemplo anterior, isto porque as células de texto são ignoradas pelo Excel. =SOMA(A2:C8)  devolve o valor 37.659€

Se colocar diretamente texto como argumento da função SOMA, como no exemplo apresentado de seguida, o Excel devolverá o valor de erro #VALOR!. =SOMA(2;5;”texto”)  devolve o valor de erro #VALOR! 12

Os principais tipos de erro devolvidos pelo Excel poderão ser consultados na Tabela 4.5 (Secção 4.3.2).

Gráficos

13. Gráficos

Os gráficos são representações visuais que poderão ser particularmente úteis para realizar observações, análises e comparações de dados. Um gráfico bem concebido poderá tornar uma análise de dados mais simples e ajudar a identificar padrões e tendências através, entre outros, da sumarização de um conjunto de dados e seus relacionamentos, e da comparação de itens ou dados ao longo do tempo. Para além dos gráficos ditos tradicionais, o Excel disponibiliza um outro tipo de gráficos, os gráficos Sparkline. Basicamente, um gráfico Sparkline é um pequeno gráfico simplificado que é gerado como fundo de uma célula. Uma das vantagens dos Sparkline é a possibilidade de estes serem gerados em células contíguas aos dados e, assim, permitirem uma interpretação mais fácil dos mesmos. Neste capítulo, na Secção 13.1 será abordada a construção e configuração dos gráficos tradicionais e, na Secção 13.2, será abordada a construção e configuração dos gráficos Sparkline.

13.1 GRÁFICOS 13.1.1 CATEGORIAS E TIPOS DE GRÁFICOS Um gráfico representa, como o próprio nome indica, graficamente uma ou mais séries de dados, cuja aparência depende da categoria e do tipo de gráfico selecionado. No Excel são disponibilizadas ao todo 16 categorias de gráficos que se subdividem em diversos tipos ou subcategorias. As 16 categorias de gráficos disponíveis são as seguintes:  Colunas: permitem ilustrar comparações entre itens ou visualizar as alterações de dados durante determinados períodos de tempo.  Linhas: permitem apresentar dados contínuos ao longo do tempo num eixo de escala uniforme e são, por isso, ideais para representarem tendências em dados em intervalos de tempo iguais.  Circulares (e em anel): permitem ilustrar as proporções dos itens em relação ao seu total; os pontos de dados são apresentados como uma percentagem do círculo total. © FCA - Editora de Informática

227

Utilização do Excel para Economia & Gestão  Barras: semelhantes aos gráficos de colunas, diferem apenas na orientação das barras que, neste caso, estão dispostas na horizontal.  Áreas: permitem representar alterações ao longo do tempo e para chamar a atenção para o valor total ao longo de uma tendência.  Dispersão (e de bolhas): permitem visualizar a relação entre dois valores numéricos; podem ser utilizados para apresentar e comparar valores numéricos, tais como dados científicos, estatísticos e de Engenharia.  Cotações: permitem mostrar as flutuações dos preços ou outro tipo de dados, como a precipitação diária ou as temperaturas anuais.  Superfície: permitem visualizar combinações ótimas entre dois conjuntos de dados.  Radar: permitem comparar os valores agregados de um conjunto de séries de dados.  Treemap: permitem visualizar de forma hierárquica os dados e facilitar a comparação de níveis diferentes de categorização.  Circulares de vários níveis: permitem visualizar dados hierárquicos e podem ser desenhados quando existem células vazias (em branco) dentro da estrutura hierárquica.  Histogramas: permitem visualizar as frequências de uma distribuição.  Caixa de Bigodes: permitem visualizar a distribuição dos dados em quartis e destacar a média e os valores atípicos.  Cascata: permitem visualizar o total corrente de dados financeiros à medida que os valores são adicionados ou subtraídos; são úteis para compreender como um valor inicial é afetado por uma série de valores negativos e positivos.  Funil: permitem visualizar valores ao longo de várias fases num processo.  Combinação: permitem combinar dois ou mais tipos de gráficos para facilitar a interpretação dos dados, sobretudo quando os dados são de diversas naturezas.

228

Operações sobre Tabelas

14. OperaçõessobreTabelas

Além de ser uma aplicação vocacionada para a realização de cálculos, o Excel disponibiliza um conjunto significativo de ferramentas que auxiliam na obtenção, tratamento, gestão e análise de bases de dados. Neste sentido, ferramentas como a ordenação, a conversão de texto para colunas, a remoção de duplicados, a validação de dados, a consolidação e os subtotais apresentam-se como ferramentas de grande utilidade e de fácil aplicação. A maioria das ferramentas referidas tem a sua aplicação prática em pequenas listas de dados ou tabelas. Uma tabela pode ser entendida como uma lista ou coleção de dados relacionados cuja estrutura e organização permite localizar e extrair determinada informação. Basicamente, uma tabela é constituída por (Figura 14.1):  Colunas ou Campos: representam um determinado tipo de dados, por exemplo, códigos, nomes ou preços.  Rótulos ou Cabeçalhos: nomes que identificam univocamente cada uma das colunas da tabela.  Linhas ou Registos: conjunto de dados relacionados que no seu conjunto descrevem alguma coisa. Rótulos ou Cabeçalhos

Linha ou Registo

Coluna ou Campo

Fig. 14.1: Componentes de uma tabela

A criação de tabelas no Excel não requer grande planeamento como seria necessário, por exemplo, em aplicações de bases de dados como o Microsoft Access. A única regra que é aconselhável respeitar na construção de tabelas é a © FCA - Editora de Informática

255

Utilização do Excel para Economia & Gestão definição e utilização de rótulos ou cabeçalhos únicos em todas as colunas de uma tabela. É importante que um rótulo nunca seja repetido, pelo que se for necessário podem utilizar-se números ou letras para diferenciar colunas com tipos de dados semelhantes (e.g., telefone1, telefone2, telefoneA, telefoneB). A existência dos rótulos numa tabela facilita não só a interpretação dos dados como é imprescindível para se trabalhar corretamente com algumas ferramentas do Excel como, por exemplo, as Funções de Bases de Dados (Capítulo 12), os Filtros de Dados (Capítulo 15) e as Tabelas e Gráficos Dinâmicos (Capítulo 16).

14.1 ORDENAÇÃO DE TABELAS A ordenação é uma das operações mais simples do Excel que consiste na reorganização dos dados de uma lista ou tabela, em função de um critério de ordenação ascendente ou descendente dos valores contidos numa ou mais colunas. Tomando como exemplo a tabela apresentada na Figura 14.1, esta poderá ser ordenada utilizando apenas um critério (e.g., ordenação ascendente por loja) ou então utilizando vários critérios consecutivos (e.g., ordenação ascendente por região e loja, sendo que neste tipo de critérios a tabela é ordenada em primeiro lugar ascendentemente por nome da região e depois, para cada região, as lojas são ordenadas de forma ascendente). Nas ordenações de tabelas com apenas um critério poderá ser utilizado um dos comandos de ordenação disponibilizados pelo Excel: para ordenações ascendentes o comando Ordenar de A a Z / Ordenar do Mais Pequeno ao Maior24 ( );para ordenações descendentes Ordenar de Z a A / Ordenar do Maior ao Mais Pequeno25 ( ); ou Ordenar / Ordenação Personalizada26. Para ordenações de tabelas com vários critérios é obrigatória a utilização do comando Ordenar / /Ordenação Personalizada. Basicamente, para ordenar uma tabela em função dos valores de apenas uma coluna deverá realizar os seguintes passos:

O nome do comando depende do tipo de dados a ordenar: Ordenar de A a Z para texto; Ordenar do Mais Pequeno ao Maior para valores numéricos, mas o símbolo mantém-se.

Tal como para a ordenação ascendente o nome depende do tipo de dados.

O comando Ordenar está disponível no separador Dados, grupo Ordenar e Filtrar; e o comando Ordenação Personalizada no separador Base, grupo Editar.

256

Macros de Comandos

19. MacrosdeComandos

As macros do Excel constituem um excelente e prático meio de automatização de tarefas que numa folha de cálculo são repetidas com alguma frequência. As macros poderão ser utilizadas, por um lado, para facilitar e acelerar a execução de tarefas e, por outro, para assegurar que determinadas tarefas são executadas sempre da mesma forma e assim evitar a ocorrência de erros e/ou de inconsistências numa folha de cálculo. Genericamente, uma macro poderá ser definida como um conjunto de passos executados sequencialmente que, no seu todo, constituem e realizam uma determinada tarefa. No Excel, as macros são desenvolvidas utilizando uma linguagem específica, o VBA (Visual Basic for Applications), cujo código poderá ser gerado de duas formas:  Utilizando o Gravador de Macros que, após a sua ativação, traduz automaticamente para o código VBA correspondente todas as ações realizadas pelo utilizador.  Escrevendo o código VBA diretamente no Editor de Visual Basic. A forma mais simples de se criar uma macro é, sem dúvida, utilizando o Gravador de Macros, uma vez que este não exige ao utilizador qualquer conhecimento da linguagem VBA. Contudo, uma das limitações do Gravador de Macros prende-se com o facto de apenas permitir a automatização de tarefas cujos passos possam ser realizados, na sua totalidade, manualmente na folha de cálculo. Por sua vez, o Editor de Visual Basic, embora limitado à experiência e conhecimentos do utilizador em técnicas de programação e da própria linguagem VBA, permite a criação de qualquer tipo de macro, desde as mais simples às mais complexas. Utilizando a linguagem VBA poderão ser criados dois tipos de macros:  Macros de Comandos: são programas que executam sequencialmente um conjunto de comandos do Excel.  Macros de Funções: são programas que realizam um determinado cálculo e que devolvem no final um único resultado. Este tipo de macros visa criar funções cujo funcionamento é semelhante às disponibilizadas na biblioteca de funções do Excel (Parte II deste livro). © FCA - Editora de Informática

397

Utilização do Excel para Economia & Gestão Nota É importante esclarecer que neste livro não se pretende fazer uma abordagem completa sobre a criação de macros ou sobre a linguagem VBA. Nesse sentido, neste livro procurar-se-á apenas apresentar as funcionalidades básicas necessárias para a criação de macros de comandos (utilizando o gravador) e de funções.

19.1 ATIVAÇÃO DO SEPARADOR PROGRAMADOR A criação e edição de macros no Excel exigem o acesso a um conjunto de comandos que são disponibilizados no separador Programador (Figura 19.1). Caso este separador não esteja visível, será necessário proceder à sua ativação.

Fig. 19.1: Separador Programador

Para ativar o separador Programador deverá realizar os seguintes passos: 1. No separador Ficheiro, selecione o comando Opções. 2. Na caixa de diálogo Opções do Excel: 2.1. Selecione a opção/separador Personalizar Friso. 2.2. Na lista Personalizar o Friso selecione a caixa de verificação do separador Programador. 2.3. Termine fazendo clique no botão OK.

19.2 SEGURANÇA NAS MACROS Com as restrições de segurança existentes no Excel, para se criar e guardar uma macro num livro, obrigatoriamente tem de ser utilizado um livro próprio para macros (“XLSM”) ou um livro de formato binário (“XLSB”). Por outro lado, a execução de macros passou a estar dependente das opções de segurança configuradas no Centro de Confiança/Fidedignidade do Excel. Em termos de segurança, a questão essencial é a de se saber se a origem das macros (e do próprio livro) é de confiança ou não. Objetivamente, se um livro for sempre manipulado pelo próprio autor, não há qualquer razão para se duvidar da 398

Funções Definidas pelo Utilizador

20. FunçõesDefinidaspeloUtilizador

Na sua biblioteca de funções o Excel disponibiliza dezenas de funções predefinidas que permitem realizar inúmeros cálculos e operações. Contudo, a utilização frequente de fórmulas e funções torna claro que nem todas as situações estão previstas e que, em casos complexos, as fórmulas necessárias à resolução dos problemas são também elas complexas. A criação de Funções Definidas pelo Utilizador (FDU) poderá permitir, entre outros aspetos, reduzir a complexidade de uma folha de cálculo, na medida em que cálculos complexos poderão ser integrados numa função que depois é utilizada de forma bastante simples, como qualquer outra função do Excel. A grande dificuldade na criação de FDU é que estas exigem alguns conhecimentos de programação e da linguagem VBA (Visual Basic for Applications) e não podem ser gravadas como as macros de comandos. Uma função é um programa, um algoritmo construído com o objetivo de produzir um resultado, executando um conjunto de instruções e utilizando um conjunto de argumentos fornecidos aquando da sua utilização. A título de exemplo, é apresentada em baixo uma função com o nome DOLARES, cuja finalidade é a de calcular um valor em Dólares a partir de dois valores reais: um valor em Euros e uma taxa de câmbio. Function DOLARES(EUROS As Single, TxEURUSD As Single) DOLARES = EUROS * TxEURUSD End Function

Qualquer função, depois de construída num dos módulos do Editor de Visual Basic, poderá ser utilizada numa fórmula como qualquer outra função do Excel. Por exemplo, a função DOLARES poderia ser utilizada do seguinte modo: =DOLARES(1000;1,112) =DOLARES(B5;1,123)

Nota A seguir, sem entrar em grandes detalhes serão apresentadas as principais funcionalidades do Editor de Visual Basic e as instruções básicas e fundamentais da linguagem Visual Basic de modo a possibilitar a construção de FDU mais elaboradas. © FCA - Editora de Informática

413

Utilização do Excel para Economia & Gestão 20.1 EDITOR DE VISUAL BASIC O conhecimento das principais funcionalidades e áreas do Editor de Visual Basic é fundamental para quem deseja criar e editar macros no Excel. Antes de mais, o acesso ao editor poderá ser realizado de duas formas:  Premindo simultaneamente as teclas ALT e F11.  Ou selecionando o comando Visual Basic disponível no separador Programador, grupo Código. Em termos de estrutura, o ambiente de trabalho do Editor de Visual Basic poderá ter várias configurações, no entanto, os componentes mais comuns e importantes são os seguintes (Figura 20.1):  Barra de Ferramentas Padrão: contém os comandos mais utilizados do editor onde se destacam os comandos: → Opções de Inserir: permite decidir o tipo de objeto a inserir/criar no VBA, nomeadamente módulos (module), módulos de classe (class module), formulários (form) ou procedimentos (procedure). → Executar macro (Run): permite executar e testar uma macro de comandos ou de funções. → Suspender execução da macro (Break): permite suspender/parar momentaneamente a execução de uma macro. → Terminar execução da macro (Reset): permite terminar definitivamente a execução de uma macro.  Janela de Projeto (Project – VBA Project): é essencial para a navegação entre os diferentes objetos dos projetos de VBA, nomeadamente para localizar e abrir um determinado módulo; nesta janela são listados todos os livros abertos no Excel e, para cada um deles, os objetos neles contidos: folhas, módulos (modules) e formulários (forms), entre outros.  Janela de Propriedades (Properties): apresenta a lista de propriedades e respetivos valores do objeto do projeto selecionado.  Janelas de Módulos: é o local do editor onde é introduzido e editado o código VBA das macros de comandos e de funções. Para cada um dos módulos criados de novo ou abertos o editor disponibiliza uma janela própria. 414