Índice Geral
Autores............................................................................................................................
V
Prefácio...........................................................................................................................
VI
Introdução . ....................................................................................................................
1
© FCA – Editora de Informática
Parte I – O Essencial do Excel 1. A Folha de Cálculo...................................................................................................... 1.1. Elementos do Ecrã de Abertura do Excel........................................................... 1.2. Introdução de Dados.......................................................................................... 1.3. Efetuar Somas de Forma Fácil............................................................................ 1.4. Realização de Fórmulas...................................................................................... 1.5. Copiar Células..................................................................................................... 1.6. Referências.......................................................................................................... 1.7. Como Acrescentar e Eliminar Linhas e Colunas.................................................
5 5 7 9 11 15 19 23
2. Tabelas e Gráficos...................................................................................................... 2.1. Tabelas e Listas................................................................................................... 2.2. Ordenação em Tabelas e Listas.......................................................................... 2.3. Filtros.................................................................................................................. 2.3.1. Filtros Automáticos................................................................................... 2.3.2. Filtros Avançados...................................................................................... 2.4. Gráficos a Partir de Tabelas................................................................................ 2.5. Formulários do Utilizador (User Forms)............................................................. 2.6. Tabelas Dinâmicas (Pivot Tables) e Gráficos Dinâmicos (Pivot Charts)..............
29 29 29 33 33 38 40 45 50
3. Funções...................................................................................................................... 3.1. Cuidados a Ter na Introdução de Fórmulas e Funções...................................... 3.2. Matemáticas e Estatística................................................................................... 3.3. Lógicas................................................................................................................. 3.4. Outros Tipos de Funções....................................................................................
57 57 58 73 80
III
Excel Aplicado
Parte II – Elementos Avançados 4. Auditoria de Fórmulas................................................................................................ 93 4.1. Ferramenta Auditoria de Fórmulas (Formula Auditing)..................................... 93 4.2. Mensagens de Erro............................................................................................. 109 5. Ferramentas para Análise de Hipóteses.................................................................... 5.1. Atingir Objectivo (Goal Seek).............................................................................. 5.2. Solver (Solver)..................................................................................................... 5.3. Gestor de Cenários (Scenarios Manager) ......................................................... 5.4. Tabela de Dados . ............................................................................................... 5.5. Análise de Dados (Analysis Toolpak)..................................................................
111 111 116 127 132 138
6. Macros........................................................................................................................ 6.1. Analisar Macros.................................................................................................. 6.1.1. Introdução................................................................................................ 6.1.2. Gravação Relativa e Absoluta................................................................... 6.1.3. Examinar e Autorizar Macros................................................................... 6.1.4. Criar uma Macro...................................................................................... 6.1.5. Executar a Macro..................................................................................... 6.1.6. Associar uma Macro à Barra de Ferramentas de Acesso Rápido............ 6.1.7. Personalizar a Barra de Ferramentas com as várias macros criadas....... 6.1.8. Associar uma Macro a um Botão numa Folha.........................................
149 149 150 151 151 153 156 157 159 160
7. Programação em VBA................................................................................................ 7.1. O VBA.................................................................................................................. 7.2. O Editor VBA....................................................................................................... 7.3. Module (Módulo)................................................................................................ 7.4. Ajuda no Editor de VBA......................................................................................
165 165 168 170 176
Parte III – Aplicações Práticas 8. Exercícios Propostos................................................................................................... 181 9. Exercício de Revisão Global....................................................................................... 253 Índice Remissivo............................................................................................................. 271
IV
Funções
3
As funções têm um papel muito importante na maioria das folhas de cálculo realizadas pelos utilizadores. Porquê? A primeira razão prende-se com o facto de as funções realizarem tarefas que de outro modo seriam muito difíceis ou mesmo impossíveis de fazer. A segunda passa, naturalmente, por permitirem realizar muitas tarefas de forma mais rápida do que as alternativas. O Excel tem diversas categorias de funções com dezenas de funções em cada uma delas. Neste capítulo, são privilegiadas as categorias matemáticas, estatísticas e lógicas, na medida em que acreditamos serem os tipos de funções mais utilizadas pelos utilizadores no seu dia a dia. Nesse sentido, vamos apresentar algumas das funções de utilização mais usual dessas categorias. Complementarmente, apresentaremos algumas funções das outras categorias para demonstrarmos de forma mais abrangente as potencialidades do Excel.
3.1. Cuidados a Ter na Introdução de Fórmulas e Funções A introdução de fórmulas e funções na folha de cálculo deve ser feita com algum cuidado, de forma a evitar erros que possam comprometer o funcionamento das mesmas ou resultar em mensagens de erro. Certifique-se sempre de que: ■■
© FCA – Editora de Informática
■■
■■
■■
Inicia todas as fórmulas com o sinal igual (=). Quando omite o sinal igual no início de uma fórmula ou função, aquilo que escrever poderá ser apresentado como texto ou como uma data; Contrabalança todos os parênteses de abertura e de fecho, ou seja, sempre que abre um parêntese, tem de o fechar. O Excel atribui cores aos parênteses à medida que vão sendo introduzidos, o que ajuda a verificar se todos têm o respetivo par; Utiliza dois pontos para indicar um intervalo. Quando referir um intervalo de células, utilize dois pontos (:) para separar a referência à primeira célula do intervalo e a referência à última célula do intervalo. Por exemplo, A1:A5; Introduz todos os argumentos necessários. Algumas funções têm argumentos necessários, ou seja, indispensáveis para que a função possa ser aplicada. Certifique-se igualmente de que não são introduzidos demasiados argumentos; 57
Excel Aplicado ■■
■■ ■■
Introduz o tipo de argumentos corretos. Há funções que necessitam de argumentos numéricos, mas outras funções necessitam de um valor de texto para, pelo menos, um dos respetivos argumentos. Se utilizar o tipo de dados errado como argumento, o Excel poderá devolver resultados inesperados ou apresentar um erro; Não utiliza mais de 64 funções aninhadas; Delimita os nomes de outras folhas por plicas. Se a fórmula se referir a valores ou células noutras folhas de cálculo ou livros e os nomes dessas folhas de cálculo ou livros contiverem carateres não alfabéticos, deve delimitar esses nomes por plicas (‘);
■■
Inclui os caminhos para livros externos, quando necessários;
■■
Introduz números sem formatação, pois as formatações podem dar origem a erros.
3.2. Matemáticas e Estatística Este tipo de funções é normalmente o mais usado por quem faz folhas de cálculo. Apesar de o Excel distinguir entre funções matemáticas e estatísticas, decidimos apresentá-las em conjunto devido às óbvias relações entre elas. Nesse sentido, vamos mostrar exemplos de utilização de funções matemáticas e estatísticas como a Soma (Sum), Média (Average), Contar (Count), Máximo (Max) e Mínimo (Min). 1. Crie um novo ficheiro. 2. Escreva nas células:
A1: Produção por fábrica; A2: Fábricas; A3: A; A4: B; A5: C; A6: D; A7: Soma; A8: Média; A9: Número de Fábricas; A11: Produção Máxima das Fábricas; A13: Produção Mínima das Fábricas; B2: Efetuada; B3: 3423; B4: 2344; 58
Funções
B5: 5555; B6: 1255; C2: Prevista; C3: 3300; C4: 2400; C5: 5000; C6: 1300. Assim, a sua folha de cálculo deverá ter o seguinte aspeto:
Vamos, então, prosseguir com a soma da produção efetuada. 1. Clique na célula B7 se não estiver já lá.
© FCA – Editora de Informática
2. Escolha o separador Fórmulas (Formulas).
59
Excel Aplicado
3. Clique no comando Inserir Função (Insert Function)
Carregar simultaneamente nas teclas Shift e F3 é equivalente a clicar no comando Inserir
Função (Insert Function)
60
.
. Experimente.
Funções
Aparece a seguinte janela.
4. Escolha a categoria (category) Matemática e Trigonometria (Math & Trig).
© FCA – Editora de Informática
Este processo pode ser, muitas vezes, apressado. Porquê? Porque o Excel apresenta uma ligação direta para as funções utilizadas mais recentemente na categoria Utilizados Mais Recentemente (Most Recently Used).
5. Percorra as opções até encontrar a função Soma (Sum). 6. Escolha a função Soma (Sum). 61
Excel Aplicado
7. Clique no botão OK da janela ativa. 8. Em Núm1 (number1), selecione a área B3:B6. Para tal, basta que faça clique na célula
em frente de Núm1 (number1) e, com o rato, selecione a área pretendida. Se for necessário, afaste a janela da área que pretende. Também pode simplesmente escrever B3:B6, mas normalmente é melhor selecionar com o rato.
Pode selecionar duas ou mais áreas diferentes. Como? Coloca a primeira área em Núm1 (number1). Depois, coloca a segunda área em Núm2 (number2). Se forem três áreas diferentes, utiliza o Núm3 (number3), e assim sucessivamente.
9. Clique no botão OK da janela aberta. O resultado da soma é 12 577. 62
Funções
10. Agora, clique na célula C7 e repita o processo, de forma a somar a área C3:C6. O resul-
© FCA – Editora de Informática
tado da célula C7 deverá ser 12 000.
63
Excel Aplicado
Vamos agora saber a média da produção efetuada pelas fábricas. 1. Clique na célula B8 se não estiver já lá.
2. Escolha o separador Fórmulas (Formulas) e clique no comando Inserir Função (Insert
Function).
64