Gestão de dados. Organização da Informação => Base de Dados (BD) => Informação Estruturada

(1)

Pág. 1 MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

Gestão de dados

• Organização da Informação em Bases de Dados

– Conceitos básicos

• Organização da Informação em Folhas de Cálculo

– Tabelas de dados – Ordenação – Formulários – Filtros

– Funções pré-definidas da Categoria Gestão de Listas e Base de Dados – Níveis de Dados ou Destaques

– Importação de dados – Tabelas Dinâmicas Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

Organização da Informação => Base de Dados (BD) => Informação Estruturada

Base de Dados = conjunto de informação organizada de acordo com uma determinada estrutura (relacional; rede; hierárquica; ...). Com base nessa estrutura, é possível efectuar um conjunto de operações sobre a informação:

consulta; alteração; reordenação; etc ...

Programa de Base de Dados = aplicação ou programa que permite a automatização do tratamento e gestão da informação contida na Base de Dados. Tipicamente:

- programas criados para lidar com dados específicos: Programas de Contabilidade (Primavera) - SGBDs (Sistemas de Gestão de Bases de Dados): Programas de carácter genérico (Access) Os SGBDs mais divulgados (Access) seguem o Modelo de BD => MODELO RELACIONAL

Organização da Informação

(Bases de Dados)

Organização da Informação

(Bases de Dados)

(2)

Tabelas => Peças Básicas de uma Base de Dados

Tabela = estrutura de campos e registos onde é armazenada a informação

A informação é dividida em categorias (Campos), e é registada em Registos Campo = categoria de informação específica (ex. Nome, Morada, Localidade, ...) Registo = conjunto de dados, estruturado de acordo com os campos da tabela

Registos = Linhas

Campos = Colunas _TABELA

DE DADOS

1. Conceitos básicos de Bases de Dados

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

O Modelo Relacional considera que:

- a informação está organizada em N Tabelas de Dados

- as Tabelas de Dados podem estar relacionadas entre si (Ligações)

TABELAS DE DADOS

2. O Modelo Relacional

(3)

Quer em EXCEL quer no WORD, temos Tabelas de Dados as quais constituem uma forma de organizar a informação. Numa Tabela de Dados:

- a primeira linha define os nomes dos Campos (Cabeçalho) - as linhas seguintes contêm os registos

Organização da Informação em Folhas de Cálculo

(Tabelas ou Listas de Dados)

Organização da Informação em Folhas de Cálculo

(Tabelas ou Listas de Dados)

Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

Limitações do EXCEL com Bases de Dados:

- não temos mecanismos para relacionar tabelas

- a Base de Dados é constituída por uma única tabela => BD Monotabela

BD Monotabela => Redundância (repetição) da informação

(4)

Pág. 7 MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Ordenação de dados:

- permite fazer a ordenação dos registos da TD

Formulários:

- inserir/remover/alterar Registos numa TD

- procurar Registos numa TD (utilizando critérios de pesquisa simples)

Filtros (Automático ou Avançado):

- permitem visualizar apenas os Registos da TD que satisfaçam um dado critério

Funções pré-definidas da Categoria Gestão de Listas e Base de Dados:

- permitem analisar e obter dados através de operações sobre a TD

Tabelas Dinâmicas:

- tipo especial de tabela que resume a informação com base nos campos de uma TD

Níveis de Dados ou Destaques:

- permitem de forma rápida e eficaz, visualizar diferentes níveis de informação quando esta está organizada de forma Hierárquica

Ferramentas disponíveis em Excel

para Tabelas de Dados

Ferramentas disponíveis em Excel

para Tabelas de Dados

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Rótulos de coluna

•Criar rótulos de coluna na primeira linha da lista.

O Excel utiliza os rótulos para criar relatórios e para localizar e organizar dados.

•Utilizar um tipo de letra, alinhamento, formato, padrão, limite ou estilo de maiúsculas ou minúsculas para os rótulos de coluna que seja diferente do formato atribuído aos dados da lista.

Tamanho e localização da lista

•Evitar possuir mais do que uma lista numa FC.

Algumas funções de gestão de listas, tais como filtrar, só podem ser utilizadas numa lista de cada vez.

•Deixar, no mínimo, uma coluna e uma linha em branco entre a lista e outros dados da FC.

O Excel pode detectar e seleccionar mais facilmente a lista quando ordenar, filtrar ou inserir subtotais automáticos.

•Evitar colocar linhas e colunas em branco na lista,

de modo que o Excel a possa detectar e seleccionar mais facilmente.

•

Evitar colocar dados essenciais à esquerda ou à direita da lista.

Os dados poderão estar ocultos quando filtrar a lista.

(5)

1. Criar uma Tabela Dinâmica através da Importação de dados

Objectivo: criar uma Tabela Dinâmica com dados que não estão no Excel, para que

o tratamento destes dados seja feito usando as ferramentas do Excel

Situações típicas:

- dados que estão em aplicações tipo Primavera ou Infologia

- dados que estão em aplicações antigas que apenas correm no MS-DOS

- dados que estão em aplicações que correm num Sistema Operativo tipo Unix ou Linux

Abordagem:

1- na aplicação que contém os dados, é gerado um ficheiro de texto (ASCII) contendo os dados

2- no Excel, importar o conteúdo dos ficheiro texto para uma folha de cálculo, i.e., gerar uma folha de cálculo com o conteúdo do ficheiro de texto devidamente organizado numa Tabela de Dados. Para o efeito, usar:

Assistente de Importação de Texto

Exemplo: importar os dados do ficheiro facturação.txt

Observações:

1- os items do campo Numero são números com 6 dígitos

2- datas aparecem sobre a forma de sequência de dígitos ddmmaaaa (ex.02121999) 3- os items do campo NõCliente são números com 8 dígitos

4- os items do campo Nome são sequências de 16 caracteres

5- os valores do campo Total surgem com o separador decimal (,) e o separador de milhares (.)

6- os campos da tabela não têm largura fixa (ver items do campo Total) 7- os dados da tabela são delimitados pelo caracter |

(6)

Passo1: No Excel, abrir o ficheiro facturação.txt com o comando Abrir o_Ficheiro

=> executa o Assistente de Importação de Texto

Linha

Se os itens do ficheiro de texto forem separados por caracteres de tabulação, dois pontos, pontos e vírgulas ou outros caracteres, seleccionar Delimitado.

Se todos os itens do ficheiro de texto tiverem o mesmo comprimento, seleccionar Largura fixa. ERRO Formato do ficheiro de texto MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

(7)

Passo3: Indicação dos delimitadores dos dados

1- Seleccionar a coluna em Simulação.

2- Seguidamente, em Formato dos dados da coluna, seleccionar o formato de dados a aplicar à coluna de texto seleccionada (o título da coluna em Simulação apresenta o formato aplicado)

Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Resultado: Facturação.txt O resultado é um Ficheiro de Texto

Guardar como: Ficheiro de texto

Guardar como: Livro do Excel

os items de dados são separados por tabulações

(8)

Para ordenar uma Tabela de Dados ou um qualquer outro conjunto de células:

- não é necessário efectuar a sua selecção, basta que a célula activa seja uma qualquer célula da TD - a opção Tem linha de cabeçalho deve estar marcada, pois permite que na construção dos

critérios de ordenação sejam indicados os nomes dos campos

2. Ordenação de dados

Podem ser aplicados até 3 critérios de ordenação (1 para cada campo) de acordo com a sequência:

1- aplicar o 1º critério

2- aplicar o 2º critério apenas aos empates de 1 3- aplicar o 3º critério apenas aos empates de 2

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Pré-definidos ou normais:

• os dados são ordenados de acordo com o valor e não com o formato

• texto é ordenado da esquerda para a direita, caracter a caracter ("A” < "Ana” < "As”) • numa ordenação ascendente, é utilizada a seguinte ordem:

– números: são ordenados do menor número negativo para o maior número positivo – texto: quer inclua números ou não, é ordenado do seguinte modo:

0 1 2 3 4 5 6 7 8 9 ' - (espaço) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

– texto: se maiúsculas diferentes de minúsculas então a mesma letra minúscula é menor do que a mesma letra maiúscula (ex. “aaa” < “AAA”)

– valores lógicos: FALSO é ordenado antes de VERDADEIRO – valores de erro: são todos iguais

(9)

2.1.1 Sequências de ordenação personalizadas

Para criar uma nova lista => Seleccione NOVA LISTA na caixa Listas personalizadas.

Introduza os elementos da nova lista na caixa Entradas da lista.

=> O primeiro caracter não pode ser um número. Premir ENTER para separar cada elemento.

Para alterar uma lista => Seleccione a lista na caixa Listas personalizadas.

Altere os seus elementos na caixa Entradas da Lista. Para acrescentar nova lista, seleccionar:

Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc

ulo Primeira chave de ordenação - indicação do tipo de critério de ordenação_{(pré-definido, i.e. normal ou personalizado)}

Maiúsculas e minúsculas - se activa, na ordenação as letras minúsculas < Maiúsculas Ordenar de cima para baixo - ordenar linhas por uma única coluna (campos são colunas) Ordenar da esquerda para a direita - ordenar colunas por uma única linha (campos são linhas)

2.2. Opções de ordenação

Para o 1º critério:

Para os 3 critérios:

Como ordenar os dias da semana ? Seg Ter Qua Qui Sex Sáb Dom Por ordem alfabética ?

(10)

Um formulário de dados é um modo conveniente de introduzir, alterar e visualizar,de uma só vez, uma linha completa de informações (registo) de uma lista.

Um formulário pode ser utilizado para dois tipos de operações: - edição

- procura

Operações de edição:

- visualizar um registo - introduzir um novo registo

- alterar um ou + campos de um registo - eliminar um registo

Operações de procura:

- pesquisar um registo que obedece a um conjunto de critérios

3. Formulários

3.1. Operações de Edição em Formulários

ENTER = passa para o registo seguinte

(11)

Associado a um formulário, temos especificados um conjunto de critérios a aplicar na pesquisa de registos.

Os botões Localizar permitem localizar o registo anterior/seguinte que satisfaz o conjunto de critérios definidos

O botão Critérios leva a que o formulário passe a ser utilizado para definir/alterar/visualizar os critérios definidos para cada Campo.

Para especificar o conjunto de critérios é utilizado o próprio formulário.

Quando o formulário está em modo Critérios, o botão Formulário permite retomar o modo Formulário.

3.2. Operações de procura em Formulários

Localizar o registo seguinte que obedece ao critério

Idade > 20 e Sexo M e Curso CCE 3.2.1. Definição de critérios de pesquisa utilizando o Formulário

(12)

Resultado da pesquisa anterior:

A filtragem constitui um modo rápido para localizar um subconjunto de dados numa lista que obedeçam a um determinado critério(s).

Numa lista filtrada são mostradas apenas as linhas (registos) que satisfazem o(s) critério(s) especificados.

Dois tipos de Filtros podem ser utilizados: - Filtro Automático

- Filtro Avançado

4. Filtragem

(13)

Apenas ficam visíveis os registos dos alunos do sexo M

Para remover os Filtros desmarcar Filtro automático no menu Filtro 4.1. Filtro Automático

A seta em azul indica que este campo tem

um filtro já definido A operação Mostrar Tudo permite remover os critérios Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

(Tudo) => mostrar todos os elementos da coluna (remoção de filtro na coluna)

(10 Mais...) => mostrar todos os elementos que se encontrem dentro dos limites especificados

(superior ou inferior), por item ou percentagem

(Personalizar) => construir Filtros complexos (expressões lógicas com operadores de comparação)

(14)

Exemplo: Lista de todos os alunos do sexo feminino e do 1º ano

Resultado final 4.1.1. Filtrar em simultâneo vários campos

Filtrar campo Sexo

Filtrar campo Ano

Exemplo: Lista dos 5 alunos mais novos

(15)

Exemplo: Lista dos 5 alunos mais velhos

Resultado final Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

Exemplo: Lista de todos os alunos de CCE ou CFP

com idade compreendida entre os 20 e os 22 anos inclusive

Escolher o critério

Personalizar... para curso

Escolher o critério

Personalizar... para idade

(16)

Pág. 31 MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Intervalo de Critérios 4.2. Filtro Avançado

Oculta duplicações de registos, i.e.,

se houver dois ou mais registos iguais, apenas é apresentado um deles

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Exemplo:

Lista de todos os alunos:

- com idade menor que 30 anos e do sexo F e do 1º ano juntamente com todos os alunos

(17)

Funções que analisam dados armazenados em listas ou em Tabelas de Dados

5. Funções de Gestão de Listas e Base de Dados

As Funções de gestão de listas e base de dados têm uma SINTAXE COMUM:

base_de_dados = é o intervalo de células que formam a lista ou TD (a 1ª linha contém rótulos) campo = indica a coluna utilizada para obter o resultado da função.

Pode ser indicada: através do rótulo (entre aspas) ou pelo número de ordem da coluna

critérios = é uma referência a um intervalo de células que especifica condições para a função. A função devolve a informação da lista que satisfaz condições especificadas no intervalo de critérios.

Sugestões:

• Pode utilizar qualquer intervalo para o argumento de critérios, desde que inclua pelo menos um rótulo de coluna e uma célula abaixo do rótulo da coluna para especificar a condição. • Para executar uma operação numa coluna inteira numa tabela de dados,

introduza uma linha em branco por baixo dos rótulos da coluna no intervalo de critérios. • Apesar do intervalo de critérios poder estar localizado em qualquer parte da folha de cálculo,

não coloque o intervalo de critérios por baixo da lista.

(18)

Pág. 35 MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo 5.1. Exemplo “Pomar”

Base de dados para registo de informações sobre as árvores de um pomar.

2 =BDCONTAR.VAL($A$5:$E$11;"Árvore";A1:B2) 185 =BDSOMA($A$5:$E$11;"Lucro";A1:B2) 110 =BDMÁX($A$5:$E$11;5;A1:A3)

15 =BDMÉDIA($A$5:$E$11;"Idade";D1:F2)

Número de Macieiras com altura >10

Soma do lucro das Macieiras com altura >10

Lucro máximo entre as Macieiras e as Pereiras

Média da idade das Macieiras

com altura entre 10 e 16

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo 5.2. Critérios de Filtragem Condições Simples:

Conjunção de condições (E):

critério = registos com o campo Árvore = “Macieira” critério = registos com o campo Altura>10

(19)

Disjunção de condições (OU):

critério = registos com o campo Árvore = “Macieira” ou= “Pereira” ou= “Nogueira”

critério = registos com o campo Árvore = “Macieira” ouo campo Altura=12

ouo campo Idade=14

critério = registos com o campo Árvore = “Macieira” eo campo Altura>10 juntamente com(ou)

registos com o campo Árvore = “Pereira” eo campo Altura>12

Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo 5.3. Exemplo “Despesas” =BDCONTAR.VAL($A$5:$L$15;"Parceiro";C1:C2) =BDSOMA($A$5:$L$15;"Quantia";D1:D2) =BDMÁX($A$5:$L$15;"Valor EUR";L1:L2) =BDSOMA($A$5:$L$15;"Valor EUR";A1:L2) =BDSOMA($A$5:$L$15;L5;A1:L3)

Número de despesas pagas pelo IPCA Soma das despesas efectuadas em PTE

Valor em Euros da maior despesa

Total em Euros das despesas pagas pelo IPCA em PTE e cuja quantia é >200000

Total em Euros das despesas pagas por Barcelos juntamente

Base de dados para registo de informações sobre as despesas pagas por parceiros de um projecto

(20)

Vários modelos típicos de Folhas de Cálculo são construídos de forma Hierárquica. Os destaques (ou níveis de dados) permitem de forma rápida e eficaz,

visualizar diferentes níveis de informação da folha de cálculo

Têm também um papel fundamental na utilização de subtotais e tabelas dinâmicas

6. Destaques ou Níveis de Dados

=> AGREGAÇÃO DE DADOS => Menu Dados MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

Exemplo de Folha de Cálculo construída de forma Hierárquica:

(21)

Alternativa: ocultar linhas/colunas

Utilizando Destaques:

Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc

ulo As linhas 8, 9, 10 e 11 são os DETALHES associados à linha 12 (o DESTAQUE) As colunas F, G e H são os DETALHES associados à coluna I (o DESTAQUE)

Botão mostrar detalhes (surge ao lado do destaque) Botão ocultar detalhes (surge ao lado do destaque)

Detalhes Destaque Botões ocultar/mostrar detalhes de destaques de diferentes níveis

(22)

Exemplo de utilização de destaques em Listas (Tabelas de Dados):

Destaques - os meses de Janeiro a Abril Detalhes - as datas das vendas

(23)

Pág. 45 MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo 2ºCriar os destaques

Exemplo: Criação do DESTAQUE Abril

- Seleccionar linhas 19 e 20 - Executar a operação AGRUPAR

AGRUPAR - agrupa linhas ou colunas passando a constituir um destaque com detalhes

DESAGRUPAR - desagrupa linhas ou colunas removendo o destaque a que pertence a célula activa

DEFINIÇÔES... Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

Uma Forma automática de obter informação detalhada sobre uma lista. • São acrescentadas linhas de subtotais a cada grupo de entradas da lista • Pode-se também fornecer outros cálculos de agregação ao nível do grupo Podemos usar Subtotais para por exemplo:

• calcular a média dos valores numa particular coluna para cada grupo de linhas • determinar o número de linhas em cada grupo (nº de elementos)

• determinar o número de células (items) em branco em cada grupo • calcular o desvio padrão para cada grupo

Em algumas situações, para uma correcta utilização de Subtotais,

Ordenar previamente os itens da Tabela

(24)

Analisemos o exemplo seguinte:

Obs: numa linha de Subtotal apenas se utiliza uma única FUNÇÃO para um ou mais campos A tabela deve estar ordenada pelo campo Cliente

(25)

Uma Tabela Dinâmica é um tipo especial de tabela que resume informação contida numa lista ou Tabela de Dados (TDad), numa base de dados externa, ou em outra Tabela Dinâmica (TDin). Uma Tabela Dinâmica:

- é criada automaticamente

- permite visualizar as relações e informações sobre os dados constantes numa lista ou TDad, podendo incluir Subtotais e outro tipo de cálculos.

Sempre que forem feitas alterações nos dados de origem da tabela dinâmica, esta terá de ser actualizada (esta actualização não é automática)

Para criar uma TDin, utiliza-se o Assistente de Tabelas Dinâmicas

Para manipular uma TDin, utiliza-se a Barra de Ferramentas para Tabelas Dinâmicas

Tabelas Dinâmicas

Com base na Tabela de Dados seguinte, pretende-se uma análise que forneça a distribuição para cada região, dos totais de vendas de cada categoria de produtos, distribuídos pelos respectivos vendedores, ao longo dos meses.

(26)

O resultado pretendido, é obtido com a construção automática da Tabela Dinâmica:

1. Os elementos de uma Tabela Dinâmica

Campo de Coluna Campos de Linha

Campo de Página Item do Campo de página

Campo de Dados

(27)

Campo de Página - é um campo da lista ou TDad de origem que é atribuído a uma orientação de página numa TDin Exemplo: Região é um campo de página que podemos utilizar para filtrar os dados resumidos por região. Item do Campo de Página - é cada uma das entradas ou valores únicos do campo (ou coluna da lista de origem)

que está a ser utilizado como Campo de Página

Exemplo: o item Norte está a ser apresentado no campo de página Região o que faz com que a TDin apresente

os dados resumidos referentes apenas à região Norte. Os outros itens são Centro e Sul.

Campos de linha - são campos da lista de origem aos quais é atribuída uma orientação de linha numa TDin.

Os campos de linha interiores encontram-se mais próximos da área de dados. Os campos de linha exteriores situam-se à esquerda dos campos de linha interiores.

Exemplo: Categoria e Vendedor são campos de linha.

Vendedor é um campo de linha interior. Categoria é um campo de linha exterior.

Campo de Coluna - é um campo de uma lista de origem ao qual é atribuído uma orientação de coluna numa TDin.

Os campos de coluna interiores são aqueles cujos itens se encontram mais próximos da área de dados. Os campos de coluna exteriores são os que estão situados acima dos campos de coluna interiores

Exemplo: Mês é um campo de coluna constituído por cinco itens, Janeiro, Fevereiro, Março, Abril e Maio.

Apenas campo de coluna está definido, pelo que, não se classifica como interior ou exterior.

Campo de dados - é um campo de uma lista de origem que contém dados. Regra geral, um campo de dados

resume dados numéricos (exemplo, valores estatísticos ou quantidades vendidas), no entanto, os dados subjacentes também podem ser texto.

Por predefinição:

- os dados de texto são resumidos numa Tabela dinâmica através da função de sumário Contar - os dados numéricos são resumidos através da função Soma.

Exemplo: Soma do Total dos valores das encomendas é o campo de dados que resume

as entradas do campo, ou coluna, Total dos dados de origem.

Itens - são uma subcategoria de um campo da Tabela Dinâmica.

Os itens representam entradas únicas pertencentes ao mesmo campo, ou coluna, dos dados de origem. Os itens aparecem como rótulos de linhas ou de colunas, ou em listas pendentes para campos de página.

Exemplo: Material Eléctrico e Pintura constituem itens do campo Categoria.

Área de Dados - corresponde à parte de uma Tabela Dinâmica que contém dados de resumo (ou sumário).

As células da área de dados mostram dados resumidos, relativos aos itens dos campos de linha e de coluna. Os valores contidos em cada uma das células da área de dados representam um sumário dos dados provenientes dos registos ou linhas de origem.

Exemplo: o valor da célula C5 constitui um sumário do valor Total das encomendas encontrado em todos os

registos dos dados de origem que contêm os itens Material Eléctrico, António e Janeiro.

(28)

As operações mais comuns: 1- Criar uma Tabela Dinâmica

=> utilizar o Assistente de Tabelas Dinâmicas 2- Actualizar os dados

=> sempre que houver alteração nos dados de origem da TDin esta não é actualizada

automaticamente, pelo que é necessário executar a operação Actualizar dados

3- Alterar a composição de uma Tabela Dinâmica: - acrescentar/remover Campos

- alterar a posição de um campo

- criar novos campos a partir do agrupamento de itens => AGRUPAR/DESAGRUPAR + Mostrar/Ocultar Detalhes 4- Alterar as propriedades dos campos da Tabela Dinâmica

- alterar o calculo de Subtotais para os campos de Linha e Coluna

- alterar a forma como os dados do campo de dados são calculados (resumidos) 5- Formatar a Tabela Dinâmica

- de modo a que a formatação não seja perdida quando os dados da TDin forem actualizados

2. Como trabalhar com Tabelas Dinâmicas

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo A barra de ferramentas para Tabelas Dinâmicas

Seleccionar Agrupar e destacar

Fórmulas Menu de Contexto

(29)

Obs: para construir uma tabela dinâmica para esta lista, não é necessário seleccionar toda a lista.

Basta apenas que a célula activa pertença à lista.

Com base na Tabela de Dados seguinte, pretende-se uma análise que forneça a distribuição para cada região, dos Totais dos valores das vendas de cada produto, distribuídos pelos respectivos vendedores, ao longo dos meses.

4. Criação de Tabela Dinâmica utilizando o Assistente

O Assistente de Tabelas Dinâmicas é activado com:

Normalmente, a origem dos dados é uma Lista ou Tabela de Dados do Excel

(30)

Obs: em princípio, o Assistente detecta automaticamente o intervalo de células que contém a lista

Indicação do intervalo de células onde estão os dados a analisar, i.e., os dados sobre os quais vai ser construída a Tabela Dinâmica

4.2. Passo2 - Indicação de onde estão os dados a analisar

- indicação dos campos de Página, Linha, Coluna e Dados (arrastar botões para o diagrama) - é obrigatória a definição do campo de Dados (os outros são opcionais)

(31)

4.4. Passo4 - Indicação do local onde a Tabela Dinâmica é criada

Locais alternativos:

- numa nova folha de calculo

- na folha de cálculo activa (neste caso indicar a referência da primeira célula da TDin)

Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo 4.5. Resultado Final

(32)

4.6. Criação de uma Tabela dinâmica a partir de outra Tabela dinâmica

Ao criarmos uma Tabela Dinâmica, se já existir uma Tabela Dinâmica sobre a mesma Tabela de Dados, surge a seguinte mensagem:

Ao utilizar uma Tabela dinâmica como origem de uma nova Tabela dinâmica, no mesmo livro, ambas ficam ligadas aos mesmos dados de origem.

Uma vez que utilizam os mesmos dados internos, o tamanho do ficheiro do livro é diminuído e diminui também a quantidade de dados que precisam de estar residentes em memória.

Sempre que actualizar os dados de uma Tabela dinâmica, o Microsoft Excel também actualiza os dados da outra Tabela dinâmica.

Quando agrupar ou desagrupar dados numa Tabela dinâmica, ambas as tabelas são afectadas. Os campos calculados e os itens calculados que criar numa Tabela dinâmica também irão afectar a outra Tabela dinâmica.

Para utilizar uma Tabela dinâmica para criar outra Tabela dinâmica,

ambas têm de pertencer ao mesmo livro.

Se a Tabela dinâmica de origem estiver num livro diferente, copie a Tabela dinâmica de origem para o livro onde deseja colocar a nova Tabela dinâmica.

(33)

Pág. 65 MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Estas opções são definidas para cada Tabela Dinâmica

O RESULTADO FINAL depende das OPÇÕES definidas para a Tabela Dinâmica

4.7. Opções da Tabela Dinâmica

Botão Pág. MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo

5. Formatar Tabelas Dinâmicas

Formatação automática: Todas as formatações manuais que aplicar à Tabela dinâmica antes ou

depois de executar a formatação automática têm prioridade em relação à formatação aplicada com o comando Formatação automática.

Uma Tabela dinâmica é formatada da mesma forma que qualquer outra tabela.

seleccionada, para que as alterações de formatação efectuadas

sejam mantidas quando:

- os dados da Tabela dinâmica forem actualizados - ou o esquema da Tabela dinâmica for alterado

Manter formatação

Para que as formatações sejam consideradas,

o botão Activar selecção tem de estar premido,

(34)

Pode ser feita:

1) Activando o Assistente de Tabelas Dinâmicas

2) Alterando a posição de um Campo => RATO: arrastar e largar o campo 3) Utilizando comandos (Barra de Ferramentas para Tabelas Dinâmicas)

Situações típicas:

1) Acrescentar ou remover um campo

a) activar o Assistente de Tabelas Dinâmicas com a célula activa a pertencer à TDin b) executar os passos 3 e 4 do Assistente

2) Alterar a posição de um campo

a) Utilizar o Assistente de Tabelas Dinâmicas com a célula activa a pertencer à TDin b) Utilizando o Rato => Arrastar e Largar de modo a mudar a posição do campo

3) Criar campos a partir do agrupamentos de itens

6. Alteração da constituição de uma Tabela Dinâmica

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Agrupar e destacar Lista com informações dos trabalhadores de uma empresa

(35)

1ª abordagem: utilizando apenas o Assistente de Tabelas Dinâmicas

Limitação na representação de escalões etários Solução => agregação de itens do Campo Idade

2ª abordagem: na abordagem anterior, agregar os itens do Campo Idade

de acordo com os escalões etários a considerar

1- Agrupar

2- Alterar nomes dos agrupamentos

construir um agrupamento por cada escalão etário é automaticamente criado o campo de coluna Idade2

(36)

Resultado final: os itens do campo Idade passam a ser

detalhes dos itens do Campo Escalões

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Mostrar

detalhes: os itens do campo Idade passam a ser detalhes dos itens do Campo Escalões

(37)

Pág. 73 MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Ocultar detalhes:

Resultado: os detalhes do item [25,35[ ficam ocultos

6.2. Alteração das propriedades dos campos de uma Tabela Dinâmica

Alterar propriedades de campos de Página, Linha e Coluna

- orientação

- mostrar/ocultar itens - mostrar/ocultar itens que não

tenham dados associados (vazios)

Alterar propriedades do campo de Dados

- alterar formatação numérica - alterar função para resumir dados - efectuar cálculos personalizados

(38)

Propriedades que podem ser alteradas:

- orientação

- mostrar/ocultar itens - mostrar/ocultar itens que não

tenham dados associados (vazios)

6.2.1. Alterar propriedades de campos de Página, Linha e Coluna

- Subtotais automáticos

=> a função para calculo dos subtotais do campo são calculados é igual à função utilizada no campo de dados para resumir os dados

- Subtotais personalizados

=> a função para calculo dos subtotais

do campo pode ser escolhida de entre um conjunto de funções disponíveis - Nenhuns Subtotais

=> os subtotais do campo não são calculados

Função Sumário obtido

Soma A soma dos valores. Esta é a função predefinida para dados de origem numéricos.

Contagem O número de itens. A função de sumário Contar funciona do mesmo modo que a função de folha de cálculo CONTAR.VAL.

Média A média dos valores.

Máximo O valor mais alto.

Mínimo O valor mais baixo.

Produto O produto dos valores.

Contar números O número de linhas que contém dados numéricos. A função de sumário Contar números funciona do mesmo modo que a função de folha de cálculo CONTAR. Funções disponíveis para

- calcular subtotais - resumir dados

Soma = resumo de dados Máximo = calculo de subtotais

(39)

Propriedades que podem ser alteradas:

- alterar formatação numérica - alterar função para resumir dados - efectuar cálculos personalizados

6.2.2. Alterar propriedades do campo de Dados

É possível personalizar os campos de dados de Tabelas dinâmicas utilizando outro tipo de cálculo em alternativa, ou em conjugação, à função de sumário predefinida:

1- Para cálculos simples (ex. Somas, Médias, Mínimos e Máximos), utilizar uma função de sumário. 2- Para comparar ou indexar dois campos, ou mostrar uma %, utilizar um cálculo personalizado. 3- Para criar uma fórmula pessoal que utilize dados da Tabela Dinâmica, utilize um campo

calculado ou um item calculado num campo

(40)

Cálculos personalizados para campos de dados da Tabela dinâmica

Mostrar dados como:

escolha do cálculo personalizado desejado. O campo base e o item base fornecem os dados utilizados no cálculo personalizado.

Campo base: campo que contém os dados que

desejamos utilizar no cálculo.

Item base: item que contém os dados que

desejamos utilizar no cálculo.

MTC 2001/2002 M S E X C E L -G es tã o d e D ad os e m F olh as d e C álc ulo Função Resultado

Apresenta todos os dados da área de dados ...

Diferença de ... como a diferença entre o valor do Campo base e o item base especificados. O campo base e o item base fornecem os dados utilizados no cálculo personalizado.

% de ... como uma percentagem do valor do Campo base e Item base especificados. O campo base e o item base fornecem os dados utilizados no cálculo personalizado.

% Diferença de ... como a diferença entre o valor do Campo base e o Item base especificados, mas mostra a diferença como uma percentagem dos dados base.

O campo base e o item base fornecem os dados utilizados no cálculo personalizado.

Total corrente em Apresenta os dados de itens sucessivos como um total corrente.

(41)

6.2.3. Exemplos com alteração das propriedades dos campos de uma T.D.

A percentagem do total dos salários dos vários cargos relativamente ao total global dos salários, considerando os vários escalões etários

A diferença do total dos salários dos vários cargos relativamente ao total dos salários dos Administradores, considerando os vários escalões etários