Manual Excel Avancado

MICROSOFT EXCEL – AVANÇADO Formatações avançadas Séries de dados Funções avançadas Formatação de gráficos Ferramentas de simulação Automatização de tarefas Introdução a tabelas e gráficos dinâmicos …

FAUSTO MOURATO Maio 2007

Cursos de formação de curta duração – Escola Superior de Tecnologia

Índice Índice .......................................................................................................... 2 Introdução .................................................................................................... 4 Revisão de conceitos ...................................................................................... 5 Trabalhar com várias folhas ......................................................................... 5 Séries ....................................................................................................... 6 Utilização de séries predefinidas ............................................................... 6 Criação de novas séries ........................................................................... 8 Definição de nomes de blocos de células ....................................................... 9 Inserção de comentários nas células ............................................................10 Opções avançadas ........................................................................................11 Formatações avançadas .............................................................................11 Formatos personalizados.........................................................................11 Formatação condicional ..........................................................................14 Formatação automática ..........................................................................16 Funções avançadas....................................................................................17 Consulta e referência..............................................................................17 Funções de data e hora ..........................................................................18 Protecção e oclusão de células ....................................................................19 Proteger e ocultar células ........................................................................19 Proteger o documento ou o livro ..............................................................20 Validação de dados ....................................................................................22 Modelos de documentos (Templates) ...........................................................25 Usar modelos predefinidos ......................................................................25 Criar um novo modelo ............................................................................26 Formatação avançada de gráficos ................................................................27 Assistente de gráficos .............................................................................27 Formatação avançada .............................................................................29 Ferramentas de simulação ..........................................................................37 Cenários ...............................................................................................37 Atingir objectivo ....................................................................................40 Solver ..................................................................................................41 Controlos personalizados ............................................................................44 Automatização de tarefas ...........................................................................45 Macros .................................................................................................45

Microsoft Excel – Avançado

2

Cursos de formação de curta duração – Escola Superior de Tecnologia

Gravador de macros ...............................................................................45 Executar uma macro ..............................................................................46 Associar uma macro a um menu ou botão na barra de ferramentas..............47 Tabelas e gráficos dinâmicos ......................................................................51 Tabelas dinâmicas ..................................................................................51 Gráficos dinâmicos .................................................................................54 Exemplos ....................................................................................................57 Exemplo 1 – Notas de alunos ......................................................................57 Exemplo 2 – Sondagem de votos ................................................................58 Exemplo 3 – Idades cônjuges .....................................................................59 Exemplo 4 – Produção ...............................................................................60 Exemplo 5 – Códigos Postais e Clientes ........................................................61 Exemplo 6 – Arranjos de viaturas ................................................................62

Microsoft Excel – Avançado

3

Cursos de formação de curta duração – Escola Superior de Tecnologia

Introdução Este manual serve de apoio à matéria do curso avançado de Microsoft Excel, leccionado na Escola Superior de Tecnologia de Setúbal entre os dias 28 e 31 de Maio de 2007. Pretende servir para auxílio e referencia às matérias abordadas nesse curso.

Como curso avançado, pretende-se transmitir conhecimentos que permitam a um utilizador de Excel tirar maior partido da aplicação. Assim, algumas das matérias abordadas neste manual são: formatações avançadas, utilização de modelos, ferramentas de simulação, automatização de tarefas, opções avançadas sobre gráficos, criação de tabelas dinâmicas, entre outras.

Algumas notas sobre o manual: •

Nas opções de menu que são referenciadas neste manual, é apresentado entre parêntesis o equivalente para cada uma das respectivas opções nas versões em inglês.

•

Este documento foi construído com base na versão 2003 do Microsoft Excel. Noutras

versões,

algumas

opções

poder-se-ão

apresentar

de

forma

ligeiramente diferente. •

Nos exemplos apresentados em anexo, e os quais servem de auxilio na compreensão de algumas das funcionalidades apresentadas, as células sombreadas a cinzento indicam que o seu valor é calculado por intermédio de fórmulas, ou seja, o seu conteúdo é dinâmico.

Microsoft Excel – Avançado

4

Cursos de formação de curta duração – Escola Superior de Tecnologia

Revisão de conceitos Trabalhar com várias folhas Um livro Excel é um documento que pode ser constituído por várias folhas de cálculo ou gráficos. Quando se cria um documento novo, por norma este é constituído por 3 folhas de cálculo distintas, nas quais se pode alternar, na parte inferior da janela.

Figura 1 – Diversas folhas de cálculo num livro Excel

Para se alternar entre as diversas folhas basta fazer clique no separador correspondente a cada uma.

Para se adicionar uma nova folha de cálculo pode seleccionar-se a opção: Inserir

Folha de cálculo (Insert

Sheet)

Para se remover a folha actual, pode seleccionar-se a opção: Editar

Eliminar folha (Edit

Remove Sheet)

As opções anteriores também pode ser acedidas directamente através um clique com o botão direito na zona dos separadores de folhas, que fará surgir o seguinte conjunto de opções:

Figura 2 – Opções sobre folhas

Microsoft Excel – Avançado

5

Cursos de formação de curta duração – Escola Superior de Tecnologia

Séries Utilização de séries predefinidas O Excel contém conjuntos de sequências que podemos introduzir automaticamente, poupando tempo na inserção de dados. Considere-se por exemplo que se pretende escrever uma lista numérica com valores consecutivos de 1 a 8. Para uma sequência deste tipo, basta inserir os primeiros 2 valores. Depois, arrasta-se o ponto de preenchimento pelas restantes células que se pretende preencher com os valores da série.

Figura 3 – Preenchimento de uma série de valores

Como os 2 números inseridos estavam espaçados de 1 valor entre si, o Excel interpreta que os próximos números da série também estarão.

Para sequências de números de 1 em 1, pode-se também inserir-se somente o primeiro número da série, e arrastar o

ponto

de

preenchimento

pressionando-se

em

simultâneo a tecla CTRL.

Existem também algumas listas de valores não numéricos que vêm definidos com o Excel, como por exemplo, uma lista com os 12 meses do ano. Assim, inserindo-se numa célula o nome de um mês, e arrastando-se o ponto de preenchimento, os meses seguintes são adicionados. Isto é válido também para séries que sejam adicionadas pelo utilizador, assunto que será abordado mais tarde neste manual.

Microsoft Excel – Avançado

6

Cursos de formação de curta duração – Escola Superior de Tecnologia

Figura 4 – Utilização de uma série predefinida

Colocar o nome de um mês e arrastar o ponto de preenchimento escreve nas restantes células os meses seguintes. Se o objectivo fosse copiar o nome do mês para as restantes células ao invés de colocar a sequência, isso podia ser feito escrevendo o mesmo mês em 2 células, e arrastando-se o ponto de preenchimento para a selecção das duas células.

Mais opções sobre séries podem ser acedidas pela opção: Editar

Preencher

Série (Edit

Fill

Series)

Figura 5 – Janela de inserção de séries

Torna-se assim possível inserir não só séries lineares, mas também outras, como por exemplo de crescimento. Nas séries de crescimento, o incremento é obtido pela multiplicação por uma constante.

Microsoft Excel – Avançado

7

Cursos de formação de curta duração – Escola Superior de Tecnologia

Criação de novas séries Para se criar uma série nova, para ser utilizada como as anteriores, os passos são os seguintes: 1. Aceder ao separador Listas Personalizadas (Personalized lists), em Ferramentas

Opções (Tools

Options).

Figura 6 – Criação de listas personalizadas 2. Inserir os valores na série na caixa Entradas da Lista (List entries). 3. Fazer clique no botão Adicionar (Add).

Em seguida a lista já pode ser utilizada automaticamente na folha de cálculo.

Figura 7 – Utilização de uma lista personalizada

Microsoft Excel – Avançado

8

Cursos de formação de curta duração – Escola Superior de Tecnologia

Definição de nomes de blocos de células A atribuição de nomes a conjuntos de células pode tornar-se útil em diversas situações. Em primeiro lugar, pode ser útil para referenciação de blocos de células em determinadas funções, nomeadamente nas de consulta e referência, como poderemos ver em alguns exemplos que serão apresentados posteriormente. Por outro lado, a atribuição de nomes a células ou blocos de células permite a escrita de fórmulas em linguagem natural.

Para se atribuir um nome a uma célula ou conjunto de células, os passos são os seguintes:

1. Seleccionar a célula ou conjunto de células a atribuir o nome 2. Aceder à opção Inserir

Nome

Definir (Insert

Name

Define) Surge a seguinte caixa de diálogo:

3. Escrever o nome a atribuir e fazer clique em OK.

Para se remover um nome, acede-se à mesma caixa de diálogo, selecciona-se o nome e prime-se o botão Eliminar (Delete).

Microsoft Excel – Avançado

9

Cursos de formação de curta duração – Escola Superior de Tecnologia

Inserção de comentários nas células Por diversas razões, pode ser útil colocar comentários em células. Os comentários são marcas que se podem adicionar às células, para informação do utilizador, e que não serão visíveis na impressão.

Para inserir um comentário numa célula basta aceder-se à opção: Inserir

Comentário (Insert

Comment) E em seguida digitar-se o comentário.

Figura 8 – Inserção de um comentário numa célula

O comentário fica então adicionado à célula e esta ficar marcada como estando comentada.

Figura 9 – Célula com a marca a indicar que contém um comentário

Para se editar um comentário que tenha sido adicionado anteriormente, acede-se à opção: Inserir

Editar Comentário (Insert

Edit Comment).

Para se apagar comentários de uma célula, acede-se à opção: Editar

Limpar

Comentários (Edit

Clear

Comments).

Microsoft Excel – Avançado

10

Cursos de formação de curta duração – Escola Superior de Tecnologia

Opções avançadas Formatações avançadas Formatos personalizados Os dados contidos nas células podem ser apresentados em vários formatos distintos. Os formatos predefinidos do Excel permitem desde logo uma vasta gama de representações possíveis para os valores. Uma representação apropriada de valores ajuda em muito a legibilidade dos mesmos, principalmente quando se trata de valores numéricos. A formatação dos números permite visualizá-los de acordo com os dados que estes pretendem representar. Por exemplo, a representação de uma temperatura deverá ser diferente da representação de uma quantia de dinheiro.

Para fazer este tipo de formatação, deverá fazer o seguinte: 1. Seleccionar a célula ou o conjunto de células ao qual pretende aplicar a formatação. 2. Escolher a opção Formatar

Células (Format

Cells). Irá então surgir no ecrã a janela de formatação de células:

Figura 10 – Janela de formatação de células

3. Seleccione o separador Número 4. É apresentada uma lista de várias categorias para os valores das células. Pode escolher o que mais se adaptar aos dados que estiver a representar. Cada categoria permite configurar determinados parâmetros na exibição dos

Microsoft Excel – Avançado

11

Cursos de formação de curta duração – Escola Superior de Tecnologia

valores. Por exemplo, no caso de uma moeda é possível escolher-se qual a unidade monetária. 5. Para concluir as alterações clique no botão OK

Uma forma mais rápida de aceder ao menu de formatação das células é utilizar o atalho de teclas CTRL+1.

Quando os formatos fornecidos pelo Excel não são suficientes para representar os dados da maneira que pretendemos, pode-se utilizar a categoria Personalizado para se configurar um novo formato.

Um novo formato consiste numa cadeira de caracteres em que determinados símbolos têm um significado particular. Essa cadeia representa a forma como o valor deverá ser representado.

Na seguinte tabela apresentam-se os significados dos símbolos que podem ser utilizados na construção de novos formatos para apresentação de valores numéricos.

#

Mostra os dígitos do número, removendo os zeros à esquerda, no caso da parte inteira, e os zeros à direita, no caso da parte decimal.

0

Marcador de posição de dígitos. Permite forçar a exibição de zeros sem significado, tanto à esquerda como à direita do número. Exemplo: 20,45885
0000,000
0020,459

?

Permite especificar o número em formato de fracções. Exemplo: 1,75
?/?
7/4

,.

1,75
? ?/?
1 3/4

Separador decimal e separador para milhares. O significado depende das definições do computador. Normalmente, num sistema em português a vírgula é o separador decimal e o ponto o separador de milhares.

Microsoft Excel – Avançado

12

Cursos de formação de curta duração – Escola Superior de Tecnologia

%

Converte

uma

fracção

no

seu

valor

correspondente

em

percentagem. Corresponde a uma multiplicação por 100 e à inclusão do carácter %. E E+ e e+

Formato cientifico.

*

Coloca o carácter que sucede o asterisco sucessivamente até preencher o conteúdo total da célula. Exemplo: 0,5
#*0
0,500000 (com tantos zeros quantos os necessários para preencher por completo a célula)

“”

Mostra o conteúdo do texto que estiver escrito entre as aspas Ex: 10
#” Quilos”
10 Quilos

md

Mostra o mês ou o dia, não colocando um zero à esquerda nos valores menores que 10. Ex: 1

mm dd

Mostra o mês ou o dia, colocando um zero à esquerda nos valores menores que 10. Ex: 01

mmm ddd

Mostra o mês ou o dia em texto abreviado. Ex: Jan

mmmm dddd

Mostra o mês ou o dia em texto completo Ex: Janeiro

aa

Dom

Domingo

Mostra o ano utilizando dois dígitos Exemplo: 06

aaaa

Mostra o ano usando o valor completo Exemplo: 2006

h

Mostra uma hora sem colocar zeros à esquerda para valores menores que 10.

hh

Mostra uma hora colocando zeros à esquerda para valores menores que 10.

m

Mostra os minutos sem colocar zeros à esquerda para valores menores que 10.

mm

Mostra os minutos colocando zeros à esquerda para valores menores que 10.

s

Mostra os segundos sem colocar zeros à esquerda para valores menores que 10.

ss

Mostra os segundos colocando zeros à esquerda para valores menores que 10.

Microsoft Excel – Avançado

13

Cursos de formação de curta duração – Escola Superior de Tecnologia

[]

Mostra valores de horas e minutos, sem limitar a 24 horas e 60 minutos ou segundos. Exemplo: 32:75

[cor]

Aplica a cor colocada entre os parêntesis rectos. Referências de cores: preto, azul, turquesa, verde, magenta, vermelho, branco, amarelo

[cor N]

Aplica a cor N correspondente na palete de cores.

><=

Permite estabelecer condições para os formatos a aplicar.

>= <= <>

Exemplo:

Formatação condicional A formação condicional permite aplicar formatações às células de forma dinâmica, mediante o seu conteúdo.

Considere-se uma pauta de alunos com uma célula que possui o valor Aprovado ou Reprovado, consoante a sua nota. A situação de um aluno pode ser apresentada com cores diferentes para cada uma das situações. Isto pode ser feito de forma automática, através da formatação automática.

Para se aplicar uma formatação condicional os passos são os seguintes: 1. Seleccionar as células às quais a formatação será aplicada. 2. Aceder a: Formatar
Formatação condicional (Format

Conditional Format). Esta opção irá abrir a seguinte caixa de diálogo:

Figura 11 – Caixa de diálogo de Formatação Condicional

3. Especificar, na primeira caixa, se a formatação é aplicada ao valor da célula ou baseada na respectiva formula. 4. Definir a condição.

Microsoft Excel – Avançado

14

Cursos de formação de curta duração – Escola Superior de Tecnologia

5. Usar o botão Formato (Format) para especificar a formatação associada à condição. 6. Para se adicionar mais condições, premir-se Adicionar (Add) e repetir a partir do passo 3. 7. Clicar em OK quando estiver finalizado.

No exemplo acima referido, a caixa de diálogo da formatação condicional poderá ser:

Figura 12 – Exemplo de formatação condicional

É possível criar-se um máximo de 3 condições.

Qualquer condição que tenha sido adicionada, da forma que foi explicada anteriormente, pode ser removida, utilizando-se para isso o botão Eliminar (Delete).

Microsoft Excel – Avançado

15

Cursos de formação de curta duração – Escola Superior de Tecnologia

Formatação automática A formatação automática consiste na aplicação de formatos preestabelecidos às tabelas. Pode ser feita com os seguintes passos. 1. Seleccionar o conjunto de células a formatar 2. Usar a opção Formatar

Formatação automática (Format

AutoFormat) Surge a seguinte caixa de diálogo:

Figura 13 – Janela de formatação automática

3. O botão Opções (Options) permite mostrar ou ocultar as opções sobre os formatos a aplicar, na parte inferior da janela. Isto permite que um esquema de formatação seja aplicado parcialmente. 4. Para finalizar clique no botão OK.

Figura 14 – Resultado da aplicação de uma formatação automática.

Microsoft Excel – Avançado

16

Cursos de formação de curta duração – Escola Superior de Tecnologia

Funções avançadas Consulta e referência PROCV (VLOOKUP) e PROCH (HLOOKUP) Estas funções permitem procurar numa tabela determinados índices ou intervalos de valores, devolvendo os valores de outras colunas ou linhas correspondentes a esses índices. A função PROCV faz uma pesquisa de forma vertical, e a função PROCH faz a pesquisa na horizontal. Deste modo a explicação irá basear-se no funcionamento da função PROCV. Considere-se o exemplo número 5, apresentado em anexo. Existe uma tabela de dados, e existe uma tabela de clientes, onde é registada a localidade de um cliente. A localidade é colocada automaticamente usando-se a primeira tabela como referência. Vejamos os passos necessários para fazer este tipo de consulta: 1. Atribuir à tabela dos códigos postais um nome, por exemplo “códigos”. 2. Para o primeiro cliente da lista, no campo localidade, preencher a fórmula com: =PROCV(C4;codigos;2).

A função PROCV recebe 3 valores como argumento (e um quarto valor opcional). O primeiro valor é referente ao código com o qual se pretende pesquisar, em que neste caso é o código postal do cliente. O segundo valor é o conjunto de células onde

se

vai

pesquisar,

neste

caso

“códigos”,

que

foi

o

nome

atribuído

anteriormente ao conjunto de células que compõem a tabela dos códigos postais. O terceiro valor indica o número da coluna onde se encontra o valor que nos interessa obter, que neste caso é a localidade, que se encontra na segunda coluna em “códigos”. A fórmula usada desta forma faz a consulta em intervalos, ou seja, o valor que é procurado, caso não exista na tabela onde é pesquisado, é aproximado ao valor inferior mais próximo. Caso não se pretenda que a consulta seja feita por intervalo, deverá adicionar-se FALSO (FALSE) no quarto valor da função. Nesse caso, quando um código procurado não existe o resultado é #N/D (não definido).

Para

a

função

PROCV

ou

PROCH

funcionarem

correctamente com pesquisa em intervalo, o conjunto de dados onde a pesquisa é feita devem estar ordenados.

Microsoft Excel – Avançado

17

Cursos de formação de curta duração – Escola Superior de Tecnologia

Funções de data e hora O Excel possui um vasto leque de funções para trabalhar com valores de datas e horas. Antes de mais é importante perceber a forma como o Excel representa internamente valores de datas e horas. Uma data é então representada como um número inteiro, em que o valor 1 está associado à data 1 de Janeiro de 1900. As restantes datas são representadas a partir dessa referência. Por exemplo o dia 3 de Abril de 2006 é representado com o número 38810. Esta representação permite que certos cálculos se efectuem rápida e directamente. Por exemplo para obter uma data de uma semana depois, basta somar 7 ao valor da data. Uma abordagem semelhante é utilizada para trabalhar com horas. Estas são representadas na parte decimal dos números que representam as datas. Portanto o número 1 representa mais concretamente o dia 1 de Janeiro de 1900 às 0:00. Por sua vez, o número 1,5 já representa o dia 1 de Janeiro de 1900 pelo meio-dia.

A seguinte tabela apresenta algumas funções úteis para se trabalhar com datas e horas, tirando partido da abordagem que acima foi referida: DATA (DATE)

Converte os valores de uma data na sua representação

DATA(ano; mês; dia)

interna. Exemplo: DATA(1997; 9; 1)
35674

TEMPO (TIME)

Converte os valores de uma hora na sua representação

TEMPO(hora; min; seg)

interna. Exemplo: HORA(10;20;30)
0,43

AGORA (NOW)

Devolve a hora e a data actuais.

AGORA()

Exemplo: AGORA()
38810,75

HOJE (TODAY)

Devolve o dia de hoje.

HOJE()

Exemplo: HOJE()
38810

ANO, DIA, MÊS

Obtém para um valor correspondente a uma data, o

(YEAR, DAY, MONTH)

respectivo ano, mês, ou dia.

ANO(data)

Exemplo: MÊS(38810)
4

HORA, MINUTO, SEGUNDO

Obtém para um valor correspondente a uma hora, a

(HOUR, MINUTE, SECOND)

respectiva hora, minuto, ou segundo.

HORA(data_hora)

Exemplo: HORA(38810,6)
14

Microsoft Excel – Avançado

18

Cursos de formação de curta duração – Escola Superior de Tecnologia

Protecção e oclusão de células Proteger e ocultar células Numa folha de cálculo Excel, é possível configurar o estado de “protegido” e “oculto” para qualquer célula.

Uma célula protegida, indica que o seu conteúdo não pode ser modificado. Uma célula oculta significa que a sua fórmula não é visível aos utilizadores.

O acesso a estas opções pode ser feito no menu: Formatar

Células (Format

Cells), no separador Protecção (Protection)

Figura 15 – Protecção e oclusão de células

As opções referidas de proteger e ocultar células só são efectivamente aplicadas após proteger-se a folha em causa. Em modo desprotegido não existe a noção de célula protegida nem oculta.

Microsoft Excel – Avançado

19

Cursos de formação de curta duração – Escola Superior de Tecnologia

Proteger o documento ou o livro Para se proteger uma folha: 1. Acede-se ao menu Ferramentas

Protecção

Proteger Folha (Tools

Protection

Protect sheet) Surge a seguinte caixa de diálogo:

Figura 16 – Janela de protecção de uma folha

2. Definir uma palavra-passe para a protecção (opcional) e definir as opções de protecção. É esta palavra-passe que irá permitir desbloquear novamente a folha de cálculo.

Para proteger um livro Excel completo ao invés de uma só folha, deve aceder-se à opção: Ferramentas

Protecção

Proteger Livro (Tools

Protection

Protect Workbook)

As funções de protecção e oclusão são bastante úteis para partilha de folhas de cálculo. Assim, é possível que os utilizadores de um documento Excel insiram dados unicamente nos locais devidos, sem poderem modificar o funcionamento correcto do mesmo.

Microsoft Excel – Avançado

20

Cursos de formação de curta duração – Escola Superior de Tecnologia

Para voltar a ser possível modificar-se os dados protegidos é necessário desproteger-se a folha. Uma vez que uma folha pode ser protegida com uma palavra passe, só o autor poderá voltar a desprotegê-la.

Para

desproteger-se

uma

folha

de

cálculo

ou

um

livro,

deve

aceder-se

respectivamente às seguintes opções Ferramentas

Protecção

Desproteger folha (Tools
Protection
Unprotect sheet) ou Ferramentas

Protecção

Desproteger livro (Tools
Protection
Unprotect book)

Caso a protecção tenha sido efectuada com utilização de palavra passe, é necessário introduzi-la na caixa de diálogo que é entretanto apresentada.

Figura 17 – Janela para desproteger um documento

Microsoft Excel – Avançado

21

Cursos de formação de curta duração – Escola Superior de Tecnologia

Validação de dados De forma a ser possível construírem-se folhas de cálculo mais fiáveis e mais fáceis de utilizar, o Excel fornece diversos tipos de validações aos dados que são inseridos nas células.

1. Seleccionar a célula ou conjunto de células onde se pretende aplicar a validação. 2. Aceder à opção: Dados

Validação (Data

Validation) Surge a seguinte caixa de diálogo:

Figura 18 – Definições de validação

3. Personalização

da

mensagem

de

introdução

de

dados

no

separador Mensagem de Entrada (Input Message).

Microsoft Excel – Avançado

22

Cursos de formação de curta duração – Escola Superior de Tecnologia

Figura 19 – Configuração da mensagem de entrada numa célula com validação

4. Definição do texto de erro: Separador Aviso de erro (Error Alert)

Figura 20 – Configuração do aviso de erro em células com validação

5. Carregar em OK.

Microsoft Excel – Avançado

23

Cursos de formação de curta duração – Escola Superior de Tecnologia

Assim, quando os dados são inseridos de forma incorrecta, é exibida a mensagem de erro.

Figura 21 – Mensagem de erro numa célula usando validação

Os valores colocados nos critérios de validação podem ser referências a células de uma folha de cálculo. Deste modo,

é

possível

fazer-se

uma

validação

dinâmica

consoante determinados valores inseridos no documento.

Microsoft Excel – Avançado

24

Cursos de formação de curta duração – Escola Superior de Tecnologia

Modelos de documentos (Templates) Um modelo é um esquema preestabelecido para a construção de determinados tipos de documentos, que serve como base para a criação de folhas de cálculo novas.

Usar modelos predefinidos 1. Criar um novo documento através do menu Ficheiro

Novo (File

New) No painel de tarefas escolher No Meu Computador… Surge a seguinte caixa de diálogo:

Figura 22 – Criação de um novo documento

2. Seleccionar o separador Folhas de Cálculo (Worksheets)

Figura 23 – Selecção de um modelo

3. Seleccionar um dos modelos e carregar em OK.

Microsoft Excel – Avançado

25

Cursos de formação de curta duração – Escola Superior de Tecnologia

Criar um novo modelo O primeiro passo para a criação de um modelo é a construção de uma folha Excel de forma normal. Nesta folha definem-se as formatações, as fórmulas que são aplicadas, e todo o conteúdo que deverá manter-se aquando das aplicações do modelo.

Em seguida este ficheiro deve ser gravado como um modelo, ao invés de ser guardado como uma folha comum.

Para isto faz-se o seguinte: 1. Ficheiro

Guardar como (File

Save as). 2. Modificar o tipo de ficheiro a ser guardado para xlt.

Figura 24 – Modificação do formato em que o ficheiro é guardado

3. Premir o botão Guardar (Save).

Seguindo agora os passos da utilização de modelos predefinidos, é possível verificar-se a existência do modelo que foi agora criado, e que pode ser usado.

Microsoft Excel – Avançado

26

Cursos de formação de curta duração – Escola Superior de Tecnologia

Formatação avançada de gráficos Assistente de gráficos A criação de um gráfico, na sua vertente mais simples, parte da selecção dos dados importantes, seguida da execução do Assistente de Gráficos. Para recapitular estes conceitos, considere-se o exemplo 1, apresentado em anexo. Suponha que se pretende fazer um gráfico para visualização das notas dos alunos nos diversos testes bem como a respectiva nota final. A selecção dos dados é relativamente simples. Seleccionam-se as colunas do nome, e as quatro seguintes com os valores das notas. O assistente de gráficos irá detectar que a primeira coluna contém os rótulos que identificam cada linha. Na selecção dos dados incluise também a linha de cabeçalho, que o assistente de gráficos conseguirá também identificar como sendo o rótulo relativo a cada uma das colunas. Após isto, executamos o assistente de gráficos, através da opção de menu Inserir

Gráfico (Insert

Chart) ou no ícone respectivo na barra de ferramentas .

Surge então a janela do assistente de gráficos, cujo processo consiste em 4 passos principais. 1. Tipo de gráfico

Figura 25 – Definição do tipo de gráfico

Microsoft Excel – Avançado

27

Cursos de formação de curta duração – Escola Superior de Tecnologia

No passo 1 define-se qual o tipo de gráfico a utilizar.

2. Dados de origem do gráfico

Figura 26

Figura 27

Nesta opção pode configurar-se a forma como os dados de entrada devem ser interpretados. Neste exemplo, utilizar a série de dados em colunas consiste em ter-se uma barra diferente para cada teste, agrupando-as por alunos. No caso de a série ser interpretada em linhas, cada barra corresponde a uma nota de um aluno, sendo estas notas agrupadas por teste.

3. Opções do gráfico

Figura 28 – Opções do gráfico

Microsoft Excel – Avançado

28

Cursos de formação de curta duração – Escola Superior de Tecnologia

No terceiro passo configuram-se opções de layout do gráfico. É neste passo que se inserem títulos, definem-se a posição e a visibilidade da legenda, entre outras opções.

4. Localização do gráfico

Figura 29 – Localização do gráfico

Finalmente, no passo 4 define-se a localização para o gráfico. É então possível colocar o gráfico como sendo uma nova folha do livro Excel, ou incluída dentro da própria folha de cálculo actual, como um objecto multimédia.

Com o assistente consegue-se uma grande variedade de gráficos, com algum grau de configuração. No entanto, para opções mais avançadas as configurações já não podem ser feitas usando o assistente.

Formatação avançada Neste ponto vamos estudar formas de configurar aspectos de formatação de um gráfico obtido pelo assistente de gráficos.

Todos os elementos que constituem um gráfico são objectos que podem ser movidos dentro da área do gráfico, deste.

podendo-se Cada

objecto

assim pode

personalizar também

a

ser

aparência formatado

individualmente.

Microsoft Excel – Avançado

29

Cursos de formação de curta duração – Escola Superior de Tecnologia

Cores Consideremos o seguinte gráfico obtido pelo assistente de gráficos, aplicado aos dados do exemplo 2, em anexo: Votos dos candidatos

Candit at o 1 Candit at o 2 Candit at o 3 Candit at o 4

Figura 30 – Gráfico obtido pelo assistente de gráficos

Neste gráfico circular as cores foram atribuídas automaticamente pelo Excel. No entanto podemos modificar estas cores. Para modificar a cor de uma secção do gráfico, podemos fazer o seguinte: 1. Clicar na área do gráfico. Pode verificar-se que ficaram marcadas cada uma das 4 secções do gráfico.

Figura 31 – Selecção da área do gráfico 2. Clicar num dos pontos correspondentes a uma das secções. Pode verificar-se agora que só uma das zonas ficou seleccionada.

Figura 32 – Selecção de uma zona particular do gráfico

Microsoft Excel – Avançado

30

Cursos de formação de curta duração – Escola Superior de Tecnologia

3. Aceder a Formatar

Ponto de Dados Seleccionados (Format

Selected Data).

Figura 33 – Formatação dos dados seleccionados no gráfico

5. Escolher a formatação de cor. 6. Clicar no separador Rótulo de dados (Data Labels) para adicionar um rótulo personalizado à fatia seleccionada. 7. Aceder ao separador Opções (Options) para configurar outras opções. 8. Para finalizar premir o botão OK.

Explosão de um gráfico circular Para se dar destaque a uma determinada fatia num gráfico circular é usado frequentemente um efeito denominado de explosão. Este consiste na separação de uma das fatias do resto do gráfico. É um efeito simples de fazer, mas com algum impacto visual. Para tal basta seguir-se os seguintes passos: 1. Seleccionar a fatia que se pretende deslocar (passos 1 e 2 da explicação anterior). 2. Arrastar a zona seleccionada em direcção ao exterior do gráfico.

Microsoft Excel – Avançado

31

Cursos de formação de curta duração – Escola Superior de Tecnologia

O resultado será algo como o gráfico seguinte:

Figura 34 – Efeito de explosão num gráfico circular

Linha de tendência Para facilitar a análise de dados, pode adicionar-se uma linha de tendência. Consideremos o exemplo número 3, apresentado em anexo, e o seguinte gráfico de dispersão criado a partir desses dados:

Figura 35 – Gráfico de dispersão com os dados do exemplo 3

Microsoft Excel – Avançado

32

Cursos de formação de curta duração – Escola Superior de Tecnologia

Olhando-se para o gráfico, e pelo senso comum, verifica-se uma relação directa entre as duas variáveis em estudo. Vamos então adicionar uma linha de tendência ao gráfico. Para isso faz-se o seguinte: 1. Seleccionar a área do gráfico. 2. Aceder

a

Gráfico


Adicionar

linha

de

tendência

(Chart

Add

Trendline).

Figura 36 – Janela para adicionar uma linha de tendência

3. Seleccionar o tipo de tendência a estudar. 4. Usar o separador Opções (Options) para configurar outras opções da linha de tendência. 5. Clicar em OK para finalizar.

Microsoft Excel – Avançado

33

Cursos de formação de curta duração – Escola Superior de Tecnologia

O resultado será um gráfico como o seguinte:

Figura 37 – Gráfico com linha de tendência

Não é possível adicionar-se linhas de tendência a gráficos circulares, anel, radar ou tridimensionais.

Personalizar um gráfico de barras Consideremos novamente o exemplo 1 apresentado em anexo, e consideremos agora o seguinte gráfico obtido a partir dos dados desse exemplo:

Figura 38 – Exemplo de um gráfico de colunas aplicado ao exemplo 1

O azul das barras pode ser substituído por uma outra cor qualquer, como já vimos anteriormente. No entanto podemos optar por fazer uma substituição por um efeito de preenchimento com várias cores, ou incluir inclusivamente uma imagem. Vejamos então os passos necessários para o fazer:

Microsoft Excel – Avançado

34

Cursos de formação de curta duração – Escola Superior de Tecnologia

1. Seleccionar os dados com um clique numa barra do gráfico. Deverão ficar seleccionadas todas as barras, uma vez que o formato que pretendemos aplicar é igual para todas.

Figura 39 – Selecção das colunas de um gráfico

2. Aceder a Formatar

Série de Dados Seleccionada (Format

Selected Data Series).

Figura 40 – Formatação de uma série de dados

Microsoft Excel – Avançado

35

Cursos de formação de curta duração – Escola Superior de Tecnologia

2. Clicar no botão Efeitos de preenchimento (Fill Efects).

Figura 41 - Efeitos de Preenchimento

4. No primeiro separador apresentado (gradação) é possível criar-se um efeito de transições de cores para o preenchimento das barras do gráfico. Usandose os restantes separadores podemos aplicar também uma textura, um padrão ou uma imagem. 5. Clicar em OK para concluir a edição das opções de preenchimento e novamente em OK para agora se fechar a janela de formatação inicial.

Um gráfico é um conjunto de objectos. A este podem ser sempre adicionados novos objectos, tais como imagens, formas automáticas, entre outros, melhorando-se assim o aspecto visual deste.

Microsoft Excel – Avançado

36

Cursos de formação de curta duração – Escola Superior de Tecnologia

Ferramentas de simulação Cenários Um

exemplo

clássico

para

a

utilização

de

cenários

é

em

empréstimos.

Consideremos a seguinte tabela:

Figura 42 – Exemplo de uma tabela de simulação de empréstimo

O utilizador insere o valor do empréstimo, a taxa de juro e o número de anos para fazer o pagamento e a folha calcula automaticamente a mensalidade. Para este exemplo não é importante saber como é feito esse cálculo. Vamos supor agora que queremos gerar numa folha um relatório com algumas das possibilidades de empréstimo, manipulando as variáveis. Consideremos então os seguintes passos para a criação de cenários: 1. Aceder à opção Ferramentas

Cenários (Tools

Scenarios) Surgirá a seguinte caixa de diálogo.

Figura 43 – Gestor de cenários

Microsoft Excel – Avançado

37

Cursos de formação de curta duração – Escola Superior de Tecnologia

2. Clicar no botão Adicionar (Add) para inserir um cenário Surgirá a seguinte caixa:

Figura 44 – Adicionar um novo cenário

3. Preencha o nome do cenário e indique quais as células variáveis, separadas por ponto e virgula. Para este caso podemos considerar as células do valor do empréstimo, e da duração deste. 4. Clique em OK e surgirá uma nova caixa para definir os valores do cenário.

Figura 45 – Definição dos valores para um cenário

Microsoft Excel – Avançado

38

Cursos de formação de curta duração – Escola Superior de Tecnologia

5. Clique em Adicionar (Add) para finalizar os valores e criar novos cenários a partir do ponto 2, ou para finalizar clique no botão OK. O resultado poderá ser algo como:

Figura 46 – Vários cenários

Clique no botão Mostrar (Show) para visualizar na folha de cálculo o cenário seleccionado. Pressione o botão Adicionar (Add) se pretender criar novos cenários. O botão Eliminar (Delete) permite remover o cenário seleccionado. Para editar um cenário prima o botão Editar (Edit). O botão Intercalar (Merge) permite juntar cenários existentes em diversas folhas de cálculo num mesmo livro. Finalmente, o botão Sumário (Summary) gera uma folha com um relatório para os diversos cenários, com o seguinte aspecto:

Figura 47 – Sumário de diversos cenários

Microsoft Excel – Avançado

39

Cursos de formação de curta duração – Escola Superior de Tecnologia

Atingir objectivo Alterações nos valores das células geram resultados diferentes no final. A opção de atingir objectivo permite abordar o problema de forma inversa. O objectivo é obter qual é o valor necessário para se conseguir um determinado resultado final. Considere-se o exemplo anterior, e a seguinte questão: quanto terá que ser a duração do crédito, para a mensalidade ser de 700€? O Excel é capaz de responder facilmente a esta pergunta. Para isso basta seguirem-se os seguintes passos: 1. Aceder a Ferramentas

Atingir objectivo (Tools

Goal Seek) Surge a seguinte caixa de diálogo:

Figura 48 – Janela de atingir o objectivo

2. Neste caso pretende-se definir o valor da célula da mensalidade, para um valor de 700, variando o valor da duração do empréstimo. Podemos então preencher as caixas da seguinte forma:

Figura 49 – Preenchimento da janela de atingir objectivo

3. Clique em OK e o Excel calcula o resultado (aproximadamente 45 anos):

Figura 50 – Resultado de uma procura de valor

Microsoft Excel – Avançado

40

Cursos de formação de curta duração – Escola Superior de Tecnologia

Solver O solver permite calcular as soluções mais apropriadas para problemas com restrições funcionais. Para percebermos melhor o seu funcionamento, consideremos o seguinte problema de programação linear: Uma empresa produz dois tipos de tubos. Para cada 100 metros de produção do primeiro são necessárias 4 horas de extrusão, 2 horas de embalagem, e 2 Kg de aditivos. Por sua vez, para cada 100 metros de produção do segundo tipo de tubo são necessárias 6 horas de extrusão, 2 horas de embalagem, e 1 Kg de aditivo. Os preços de venda são respectivamente para o primeiro e segundo tipo de tubo de 34€ e 40€ por cada 100 metros. Pretende-se planear-se a produção semanal, sabendo que se tem disponível os seguintes recursos: 48 horas de extrusão, 18 horas de embalagem, 16 Kg de aditivo. O objectivo é, naturalmente, o de maximizar o lucro.

Considere-se a folha de cálculo, que representa o problema, apresentada no anexo 4.

Pretende-se que o Excel calcule qual a forma de obter o maior lucro possível, sabendo-se quais as produções necessárias para cada tipo de tubo. O solver consegue resolver esse problema, com os seguintes passos: 1. Aceder a Ferramentas

Solver (Tools

Solver) Surgirá a seguinte janela:

Figura 51 – Janela do solver

Microsoft Excel – Avançado

41

Cursos de formação de curta duração – Escola Superior de Tecnologia

2. É agora necessário adicionar as restrições do problema. Para isso, clique em Adicionar (Add) Surge a seguinte janela:

Figura 52 – Adicionar uma Restrição ao Solver

3. Preencha a primeira restrição (o número de recursos gastos em extrusão tem de ser menor ou igual à disponibilidade desse recurso). Faça clique em Adicionar (Add) para adicionar a restrição sem fechar a janela, de modo a poder adicionar de imediato as restantes restrições: limitações de produção para os outros dois recursos, e a limitação da produção ser igual ou superior a zero. No fim clique em OK. Voltará a janela inicial que deverá ter o seguinte aspecto:

Figura 53 – Janela do solver preenchida com as restrições 4. Clique em Solucionar (Solve) para que o Excel calcule a solução.

Microsoft Excel – Avançado

42

Cursos de formação de curta duração – Escola Superior de Tecnologia

Numa instalação não personalizada o Solver poderá não estar instalado. Se a opção Solver não estiver disponível

no

menu

Ferramentas

(Tools)

poderá

seleccionar para o instalar em: Ferramentas

Suplementos(Tools

Add-Ins)

Microsoft Excel – Avançado

43

Cursos de formação de curta duração – Escola Superior de Tecnologia

Controlos personalizados Uma folha de Excel pode utilizar várias formas de introduzir dados, que não só a forma usual de texto em células. Para isto existem os controlos personalizados, que permitem que os dados sejam inseridos, por exemplo, a partir de uma lista de elementos, de caixas de verificação, entre outras. Para se utilizar os controlos personalizados, deve-se utilizar a barra de ferramentas de formulários. Para a tornar visível acede-se a: Ver

Barra de Ferramentas

Formulário (View

Toolbar

Forms)

Figura 54 – Barra de ferramentas de formulários

Pode-se agora utilizar esta barra para adicionar os controlos à folha de cálculo. Os controlos inseridos podem utilizar informação da folha de cálculo, e pode também devolver valores para células desta. Para isso é necessário, após a inserção do controlo, aceder-se à sua janela de formatação, pela opção: Formatar

Controlo (Format

Control) Isto dará acesso a uma janela com este aspecto:

Figura 55 – Formatação de um controlo do tipo caixa de verificação

Consoante o tipo de controlo inserido, serão apresentadas as opções nesta janela.

Microsoft Excel – Avançado

44

Cursos de formação de curta duração – Escola Superior de Tecnologia

Automatização de tarefas Macros Uma macro é um conjunto de instruções que executam tarefas específicas, de forma rápida e automática. Isto permite converter um conjunto de acções sequenciais numa só, automatizando o processo.

Gravador de macros O gravador de Macros regista as acções que o utilizador faz na sua folha de cálculo Pode-se criar uma macro com o Gravador de Macros, seguindo-se para isso os seguintes passos: 1. Aceder à opção Ferramentas

Macro

Gravar nova Macro (Tools

Macro

Record New Macro) Surge no ecrã a seguinte caixa de diálogo:

Figura 56 – Gravação de uma macro

2. Esta caixa de diálogo permite: definir um nome para a macro; escolher uma tecla de atalho; colocar uma descrição e escolher o local onde a macro será guardada. Guardar uma macro no livro pessoal de macros disponibiliza a macro para qualquer documento que seja usado no programa, enquanto que guardar a macro no livro torna-a de uso exclusivo desse documento. Depois de se definirem as propriedades referidas, clique em OK. Irá ficar visível no ecrã a seguinte caixa:

Figura 57 – Controlo da gravação de uma macro

Microsoft Excel – Avançado

45

Cursos de formação de curta duração – Escola Superior de Tecnologia

O botão de stop dessa caixa permite parar a gravação. As acções que decorreram desde o início da gravação até ao premir do botão de stop ficam então gravadas na macro, e poderão ser reproduzidas posteriormente de forma sequencial, como sendo uma só acção.

Executar uma macro Um modo para executar uma macro que tenha sido previamente gravada é através da opção Ferramentas

Macro

Macros (Tools

Macro

Macros), que faz surgir no ecrã a seguinte caixa de diálogo:

Figura 58 – Janela de macros Escolhe-se na lista a macro pretendida e carrega-se no botão Executar (Run)

Uma forma mais rápida de executar uma macro é usar a combinação de teclas de atalho que é definida aquando da gravação da mesma. No exemplo anterior, o atalho para a macro é CTRL+SHIFT+M.

Microsoft Excel – Avançado

46

Cursos de formação de curta duração – Escola Superior de Tecnologia

Associar uma macro a um menu ou botão na barra de ferramentas Para além das formas anteriores de executar uma macro, é possível colocar um botão na barra de ferramentas, ou uma opção na barra de menu para o fazer. Isto é feito com os seguintes passos: 1. Aceder à opção Ver

Barra de Ferramentas

Personalizar (View

Toolbars

Customize) Irá surgir a seguinte caixa:

Figura 59 – Janela de personalização da barra de ferramentas

2. Seleccionar no separador Comandos (Commands) a categoria Macros (Macros).

Microsoft Excel – Avançado

47

Cursos de formação de curta duração – Escola Superior de Tecnologia

Figura 60 – Botões para comandos na barra de ferramentas

3. Arrastar o Botão personalizado (Custom Button) para uma das barras de ferramentas presentes no ecrã. Pode também arrastar o mesmo botão para o menu em vez da barra de ferramentas, caso pretenda aí criar uma entrada para a execução da macro. 4. Mudar o nome do botão carregando no botão Modificar selecção (Modify Selection). 5. Usando também o botão Modificar selecção (Modify Selection), aceda à opção Alterar imagem do botão (Change Button Image), e seleccione um qualquer ícone da lista.

Microsoft Excel – Avançado

48

Cursos de formação de curta duração – Escola Superior de Tecnologia

Figura 61 – Selecção de um novo ícone num botão da barra de ferramentas

6. Faça clique no botão Fechar (Close).

Microsoft Excel – Avançado

49

Cursos de formação de curta duração – Escola Superior de Tecnologia

7. Clique no botão que entretanto foi adicionado na barra de ferramentas Surge a seguinte janela, para a escolha da macro a associar ao botão.

Figura 62 – Atribuição de uma macro a um botão da barra de ferramentas

8. Seleccione na lista a macro a associar ao botão e prima o botão OK.

Microsoft Excel – Avançado

50

Cursos de formação de curta duração – Escola Superior de Tecnologia

Tabelas e gráficos dinâmicos As folhas comuns criadas em Excel permitem um dinamismo limitado. Embora seja possível

aplicar filtros, ocultar dados e fazer ordenações, a

estrutura

da

apresentação dos dados encontra-se limitada à forma da estrutura inicial. Alterar a apresentação dos dados implica a reconstrução de fórmulas. Certos dinamismos mais avançados não são realizáveis pela utilização das tabelas usuais. Surgem assim as tabelas dinâmicas (pivot tables) e os gráficos dinâmicos (pivot charts)

Tabelas dinâmicas Uma tabela dinâmica permite reorganizar e analisar dados de uma tabela Excel, bem como a criação de sub-totais e outros cálculos. Para criar uma tabela dinâmica devemos efectuar os seguintes passos: 1. Seleccionar a tabela que pretendemos analisar (ou reorganizar) 2. Aceder ao menu Dados (Data), e seleccionar a opção Relatório de tabelas e de gráficos dinâmicos (Pivot Table and Pivot Chart Report). É apresentada a caixa apresentada em seguida:

Figura 63 – Assistente de tabelas e gráficos dinâmicos (passo 1)

3. Seleccionar a primeira opção, indicando que são dados de Excel, e manter a opção de tabela dinâmica na parte inferior. Clicar em seguinte (next). Será apresentada a seguinte janela:

Figura 64 – Assistente de tabelas e gráficos dinâmicos (passo 2)

Microsoft Excel – Avançado

51

Cursos de formação de curta duração – Escola Superior de Tecnologia

4. Como os dados já tinham sido seleccionados previamente, basta-se confirmar o intervalo indicado, clicando-se no botão seguinte (next). É então apresentada a seguinte janela:

Figura 65 – Assistente de tabelas e gráficos dinâmicos (passo 3)

5. Nesta última opção é possível seleccionar se se pretende colocar o resultado numa folha de cálculo nova, ou utilizar uma das existentes. Antes de se terminar, pode-se também definir desde já qual a estrutura que se pretende para a tabela dinâmica, clicando-se no botão Esquema (Layout), que dará acesso à seguinte janela:

Figura 66 – Configuração do esquema de uma tabela dinâmica

6. A relação entre os dados é feita nesta janela, arrastando-se os campos apresentados à direita para as zonas PÁGINA (PAGE), LIN (LIN), COL (COL) ou DADOS (DATA) 7. Finalmente, clica-se em OK para fechar a configuração do esquema, e em terminar para concluir o assistente e gerar a tabela dinâmica.

Microsoft Excel – Avançado

52

Cursos de formação de curta duração – Escola Superior de Tecnologia

Considerando a folha de dados apresentada no exemplo 6, em anexo, é possível construir a tabela dinâmica apresentada nesse mesmo anexo, arrastando o campo Mês para as colunas, Viatura para as linhas, Custo para os dados e Trabalho para a página.

Configuração dos campos a posteriori Após a construção de uma tabela dinâmica é sempre possível reorganizá-la, bastando para isso arrastar novamente os campos, quer dentro da própria tabela como para fora desta, quando se pretende deixar de mostrar determinada informação. Novos campos a serem colocados podem ser arrastados da lista de campos da tabela dinâmica (Figura 67).

Figura 67 – Lista de campos da tabela dinâmica

Formatar a tabela dinâmica É também possível configurar a aparência da tabela dinâmica, utilizando para isso o botão

da barra de ferramentas de tabelas dinâmicas. A formatação poderá

modificar a orientação definida para os dados que tenha sido feita anteriormente.

Filtragem dos dados Os campos colocados na tabela dinâmica permitem automaticamente filtragem, como se pode verificar pelos controlos colocados junto a estes. No exemplo de tabela formatada, no anexo 6, existe a possibilidade de filtrar o mês, a viatura e o tipo de trabalho.

Microsoft Excel – Avançado

53

Cursos de formação de curta duração – Escola Superior de Tecnologia

Gráficos dinâmicos Uma vez vista a forma de criar tabelas dinâmicas, e algumas das opções possíveis com as mesmas, a criação de gráficos dinâmicos torna-se mais simples, uma vez que o procedimento é em muito semelhante. Assim, os passos para a criação de um gráfico dinâmico são: 1. Seleccionar a tabela que se pretende representar graficamente 2. Aceder ao menu Dados (Data), e seleccionar a opção Relatório de tabelas e de gráficos dinâmicos (Pivot Table and Pivot Chart Report). É apresentada a caixa apresentada em seguida:

Figura 68 – Assistente de tabelas e gráficos dinâmicos

3. Seleccionar a primeira opção, indicando que são dados do Excel, e alterar para a opção de gráfico dinâmico na parte inferior. Clicar em seguinte (next). Será apresentada a seguinte janela:

Figura 69 – Assistente de tabelas e gráficos dinâmicos (passo 2)

Microsoft Excel – Avançado

54

Cursos de formação de curta duração – Escola Superior de Tecnologia

4. Como os dados já tinham sido seleccionados previamente, basta-se confirmar o intervalo indicado, clicando-se no botão seguinte (next). É então apresentada a seguinte janela:

Figura 70 – Assistente de tabelas e gráficos (passo 3)

5. Nesta última opção é possível seleccionar se se pretende colocar o resultado numa folha de cálculo nova, ou utilizar uma das existentes. Antes de se terminar, pode-se também definir desde já qual a estrutura que se pretende para o gráfico dinâmico, clicando-se no botão Esquema (Layout), que dará acesso à seguinte janela:

Figura 71 - Configuração do esquema de uma tabela dinâmica

6. A relação entre os dados é feita nesta janela, arrastando-se os campos apresentados à direita para as zonas PÁGINA (PAGE), LIN (LIN), COL (COL) ou DADOS (DATA) 7. Finalmente, clica-se em OK para fechar a configuração do esquema, e em terminar para concluir o assistente e gerar o gráfico dinâmico.

Microsoft Excel – Avançado

55

Cursos de formação de curta duração – Escola Superior de Tecnologia

Alternativamente ao passo 3 apresentado, pode escolher-se a opção de utilizar uma tabela dinâmica criada anteriormente. Em seguida é escolhida a tabela que se pretende utilizar, e os restantes procedimentos são idênticos.

É possível editar o gráfico dinâmico da mesma forma que as tabelas dinâmicas, ou seja, arrastando os campos para os eixos em que se pretende analisá-los.

Microsoft Excel – Avançado

56

Cursos de formação de curta duração – Escola Superior de Tecnologia

Exemplos Exemplo 1 – Notas de alunos Pauta das notas dos alunos com ponderações Num

Nome

Teste 1

Teste 2

Trabalho

Nota Final

Situação

1

João

11

8

8

9

Reprovado

2

Maria

16

11

10

13

Aprovado

3

Pedro

12

13

11

12

Aprovado

4

Ana

11

12

15

12

Aprovado

5

Rita

8

7

9

8

Reprovado

6

Carlos

10

8

7

9

Reprovado

7

Filipa

16

14

15

15

Aprovado

8

Sofia

9

10

12

10

Aprovado

12

10

11

11

Médias

Factores de Ponderação Teste 1

40%

Teste 2

40%

Trabalho

20%

Microsoft Excel – Avançado

57

Cursos de formação de curta duração – Escola Superior de Tecnologia

Exemplo 2 – Sondagem de votos Sondagem Resposta

Nº Respostas

Percentagem

Candidato 1

597

38%

Candidato 2

449

28%

Candidato 3

362

23%

Candidato 4

180

11%

1588

100%

Total de inquiridos

Microsoft Excel – Avançado

58

Cursos de formação de curta duração – Escola Superior de Tecnologia

Exemplo 3 – Idades cônjuges Idades de Cônjuges H

M

43

40

25

23

31

32

51

47

48

48

49

52

60

57

68

65

72

70

35

36

32

31

29

30

Microsoft Excel – Avançado

59

Cursos de formação de curta duração – Escola Superior de Tecnologia

Exemplo 4 – Produção Produção de tubos Tipos de tubo

Extrusão

Embalagem

Aditivo

Custos

Tipo 1

4

2

2

34 €

Tipo 2

6

2

1

40 €

Disponibilidade

48

18

16

Recursos Gastos

48

18

12

Produção 3 6

Lucro

Microsoft Excel – Avançado

342 €

60

Cursos de formação de curta duração – Escola Superior de Tecnologia

Exemplo 5 – Códigos Postais e Clientes Folha 1: Tabela de Códigos Postais Cód Postal

Localidade

1000

Lisboa

1100

Lisboa

2720

Amadora

2900

Setúbal

2910

Setúbal

4000

Porto

Folha 2: Tabela de clientes Nº

Nome

1

João Fernandes

2910

Setúbal

2

Paulo Costa

4000

Porto

3

Maria da Conceição

1000

Lisboa

4

Fernando Brito

2910

Setúbal

5

José Manuel

4000

Porto

6

Manuela Silva

2900

Setúbal

7

Miguel Esteves

2720

Amadora

8

Matilde dos Santos

1100

Lisboa

9

Jorge Semedo

4000

Porto

10

Sandra Pereira

1100

Lisboa

Microsoft Excel – Avançado

Código Postal

Localidade

61

Cursos de formação de curta duração – Escola Superior de Tecnologia

Exemplo 6 – Arranjos de viaturas Folha 1: Arranjos de viaturas

Mês

Viatura

Trabalho

Custo Responsável

Janeiro

Peugeot 206

Mudança de óleo

50 €

Carlos Silva

Janeiro

Peugeot 206

Discos de travões

200 €

Manuel Moreira

Fevereiro

Suzuki Swift

Revisão geral

120 €

Carlos Silva

Fevereiro

Peugeot 206

Pneus

320 €

Manuel Moreira

Março

Toyota Corolla

Trabalho de pintura

240 €

Manuel Moreira

Abril

Suzuki Swift

Inspecção

40 €

Carlos Silva

Abril

Peugeot 206

Reparação eléctrica

180 €

António Fonseca

Tabela dinâmica de exemplo: Trabalho

(Tudo)

Soma de Custo

Viatura

Mês

Peugeot 206

Suzuki Swift

Janeiro

250

Fevereiro

320

Toyota Corolla

Total Geral 250

120

Março

440 240

Abril

180

40

Total Geral

750

160

240 220

240

1150

Tabela dinâmica formatada:

Microsoft Excel – Avançado

62