A criar tabela dinâmica para analisar dados de folha de cálculo

Excel Avançado Submódulo: Criar uma Tabela Dinâmica para Analisar Dados de uma Folha de Cálculo Manual do Formando

Um Projeto desenvolvido para:

www.novaetapa.com

Índice

A. Criar uma Tabela Dinâmica para Analisar Dados de uma Folha de Cálculo ...................... 3 Alterar o Esquema de Dados de uma Tabela Dinâmica ............................................................ 4 Alterar a Forma como os Subtotais e os Totais Gerais são Mostrados..................................... 6 Filtrar Dados numa Tabela Dinâmica ........................................................................................ 6 Filtrar Dados Manualmente ...................................................................................................... 7 Mostrar Apenas Texto, Valores ou Datas Específicos ............................................................... 9 Mostrar os 10 Itens Iniciais ou Finais ........................................................................................ 9 Ligar e Desligar as Opções de Filtragem.................................................................................. 11 Ordenar Dados numa Tabela Dinâmica .................................................................................. 12 Ordenar uma Coluna que Não Tem um Botão de Seta ........................................................... 13 Configurar Opções de Ordenação Personalizadas .................................................................. 14

2

A. Criar uma Tabela Dinâmica para analisar dados de uma folha de cálculo Poder analisar todos os dados de uma folha de cálculo poderá ajudá-lo a tomar melhores decisões empresariais. Contudo, por vezes é difícil saber por onde começar, especialmente quando tem muitos dados. O Excel pode ajudá-lo, recomendando e, em seguida, criando automaticamente Tabelas Dinâmicas, que constituem uma excelente forma de resumir, analisar, explorar e apresentar os seus dados.

1. Certifique-se de que os seus dados possuem títulos de coluna ou cabeçalhos de tabela e de que não existem linhas em branco.

2. Clique em qualquer célula do intervalo de células ou tabela.

3. Clique em Inserir > Tabelas Dinâmicas Recomendadas.

4. Na caixa de diálogo Tabelas Dinâmicas Recomendadas, clique em qualquer esquema de Tabela Dinâmica para obter uma pré-visualização e, em seguida, escolha o esquema que apresenta os dados conforme pretende.

5. Clique em OK.

3

O Excel coloca a Tabela Dinâmica numa folha de cálculo nova e mostra a Lista de Campos para que possa reordenar os dados da Tabela Dinâmica conforme necessário.

6. Para ver apenas os dados e organizá-los da maneira que pretende, pode filtrar e ordenar a Tabela Dinâmica. Consulte Filtrar dados numa Tabela Dinâmica e Ordenar dados numa Tabela Dinâmica.

Neste exemplo apresentado abaixo, a opção Etiquetas de Linha está filtrada para mostrar apenas vendas para as regiões Sul e Oeste.

Alterar o esquema de dados de uma Tabela Dinâmica Quando criar uma Tabela Dinâmica e adicionar os campos que pretende analisar, pode alterar o esquema de dados para tornar a Tabela Dinâmica mais fácil de ler e de analisar. Basta selecionar um esquema de relatório diferente para obter alterações de esquema imediatas.

1. Clique em qualquer sítio da Tabela Dinâmica para mostrar as Ferramentas da Tabela Dinâmica no friso.

2. Clique em Estrutura > Esquema de Relatório.

4

3. Escolha uma das três opções de formato: Mostrar no Formato Compacto impede os dados relacionados de se espalharem horizontalmente para fora do ecrã e minimiza o deslocamento. Este esquema é aplicado automaticamente quando cria uma Tabela Dinâmica.

Tal como é mostrado na imagem, os itens de diferentes campos de área de linha estão numa coluna e os itens de campos diferentes estão avançados (como Tri1 e Canadá). Os rótulos de linha ocupam menos espaço no formato compacto, o que deixa mais espaço para os dados numéricos. Os botões Expandir e Fechar são mostrados para que possa apresentar ou ocultar os detalhes. 

Mostrar em Formato de Destaques destaca os dados na Tabela Dinâmica.

Tal como é mostrado na imagem, os itens são destacados numa hierarquia ao longo das colunas. 

Mostrar em Formato Tabular mostra tudo num formato de tabela, o que facilita a cópia de células para outra folha de cálculo.

Tal como é mostrado na imagem, este esquema utiliza uma coluna por campo.

4. Se escolher os formatos de destaque ou tabular, também pode clicar em Repetir todas as Etiquetas de Item no menu Esquema de Relatório para mostrar as etiquetas de item de cada item.

5

Alterar a forma como os subtotais e os totais gerais são mostrados Para aperfeiçoar ainda mais o esquema dos dados da Tabela Dinâmica, pode alterar a forma como os subtotais, totais gerais e itens são apresentados.

1. Clique em qualquer sítio da Tabela Dinâmica para mostrar a Tabela Dinâmica.

2. No separador Estrutura, efetue uma das seguintes ações:

  

Clique em Subtotais para alterar a forma como serão apresentados para grupos de dados.

Clique em Totais Gerais para alterar a forma como serão apresentados para colunas e linhas.

Clique em Linhas em Branco para inserir uma linha em branco a seguir a cada agrupamento na Tabela Dinâmica.

Filtrar dados numa Tabela Dinâmica Para se concentrar numa pequena parte de uma grande quantidade dos dados da Tabela Dinâmica para uma análise mais aprofundada, pode filtrar os dados. Existem várias formas de o fazer. Comece por inserir uma ou mais segmentações de dados para obter uma forma rápida e eficaz de filtrar os dados. As segmentações de dados possuem botões onde pode clicar para filtrar os dados e ficam visíveis juntamente com os dados para que saiba sempre quais os campos que são mostrados ou ocultados na Tabela Dinâmica filtrada.

6

1. Clique em qualquer sítio da Tabela Dinâmica para mostrar as Ferramentas da Tabela Dinâmica no friso.

2. Clique em Analisar > Inserir Segmentação de Dados.

3. Na caixa de diálogo Inserir Segmentação de Dados, marque as caixas dos campos para os quais pretende criar segmentações de dados.

4. Clique em OK. É apresentada uma segmentação de dados para cada campo que marcou na caixa de diálogo Inserir Segmentação de Dados. a. Em cada segmentação de dados, clique nos itens que pretende mostrar na Tabela Dinâmica.

Para selecionar mais do que um item, prima e mantenha premido CTRL e, em seguida, escolha os itens que pretende mostrar.

Para alterar o aspeto de uma segmentação de dados, clique na segmentação para mostrar as Ferramentas de Segmentação de Dados no friso e, em seguida, aplique um estilo de segmentação ou altere as definições no separador Opções.

Filtrar dados manualmente a. Na Tabela Dinâmica, clique na seta

nos Rótulos de Linha ou nos Rótulos de

Coluna.

7

b. Na lista de rótulos de linha ou de coluna, desmarque a caixa (Selecionar Tudo) no topo da lista e, em seguida, marque as caixas dos itens que pretende mostrar na Tabela Dinâmica.

Para ver mais itens na lista, arraste a alça no canto inferior direito da galeria de filtros para a expandir. c.

Clique em OK.

8

A seta de filtragem muda para este ícone para indicar que está aplicado um filtro. Clique na mesma para o alterar ou remova o filtro clicando em Remover Filtro De <Nome do Campo>.

Para remover todas as filtragens de uma só vez, clique em qualquer sítio da Tabela Dinâmica e, em seguida, clique em Analisar > Remover > Remover Filtros.

Mostrar apenas texto, valores ou datas específicos a. Na Tabela Dinâmica, clique com o botão direito do rato em qualquer etiqueta de campo de texto, valor ou data e, em seguida, clique em Filtros de Rótulo, Filtros de Valores ou Filtros de Data. b. Clique no comando de operador de comparação que pretende utilizar.

Por exemplo, para filtrar texto que comece por um determinado caráter, selecione Começa Com ou, para filtrar texto que contenha determinados carateres independentemente da sua posição, selecione Contém. c.

Efetue um dos seguintes procedimentos: 

Na caixa de diálogo Filtro de Etiqueta <Nome de campo> introduza o texto pelo qual pretende fazer a filtragem.

Por exemplo, para filtrar com base em texto começado pela letra "J", introduza J ou, para filtrar com base em texto que contenha a sequência de carateres "bola" em qualquer parte do texto, introduza bola.

Se a Tabela Dinâmica for baseada numa origem de dados não OLAP, pode utilizar os seguintes carateres universais para localizar dados que possuam carateres específicos.

9

UTILIZE

PARA LOCALIZAR

? (ponto de interrogação)

Qualquer caráter único Por exemplo, s?lva localiza "silva" e "salva"

* (asterisco)

Qualquer número de carateres Por exemplo, *este localiza "Nordeste" e "Sudeste"

~ (til) seguido de ?, * ou ~

Um ponto de interrogação, asterisco ou til Por exemplo, fy06~? localiza "fy06?"

 

Na caixa de diálogo Filtro de Valor <Nome de campo> introduza os valores que pretende filtrar.

Na caixa de diálogo Filtro de Data <Nome de campo>, introduza as datas que pretende filtrar.

Se a Tabela Dinâmica for baseada numa origem de dados OLAP, os filtros de data necessitam da hierarquia de tipo de dados de tempo do campo de cubo OLAP. Se uma data for introduzida como texto no campo de texto, o filtro de data não estará disponível.

Mostrar os 10 itens iniciais ou finais Também pode aplicar filtros de forma a mostrar os 10 valores de dados que correspondem a determinadas condições.

1. No relatório da Tabela Dinâmica, clique na seta

em Rótulos de Linha ou Rótulos

de Coluna, clique em Filtros de Valor e, em seguida, clique em 10 Mais.

2. Na caixa de diálogo Filtro dos 10 Mais <Nome do Campo>, execute os seguintes passos. a. Na primeira caixa, clique em Superior ou Inferior. b. Na segunda caixa, introduza um número. c.

Na terceira caixa escolha a opção pela qual pretende filtrar.  Para filtrar por número de itens, escolha Itens.  Para filtrar por percentagem, escolha Percentagem.  Para filtrar pela soma, escolha Soma.

d. Na quarta caixa, selecione o campo que pretende filtrar.

10

Ligar e desligar as opções de filtragem Se pretende aplicar vários filtros por campo ou se pretender mostrar os botões de Filtro na Tabela Dinâmica, saiba como pode ligar e desligar estas e outras opções de filtragem: a. Clique em qualquer sítio da Tabela Dinâmica para mostrar as Ferramentas de Tabela Dinâmica no friso.

b. No separador Opções, no grupo Tabela Dinâmica, clique em Opções.

c.

Na caixa de diálogo Opções da Tabela Dinâmica, clique no separador Totais e Filtros.

d. Em Filtros, efetue um dos seguintes procedimentos:  





Para usar ou não vários filtros por campo, marque ou desmarque a caixa Permitir vários filtros por campo.

Para incluir ou não os itens filtrados nos totais, marque ou desmarque a caixa Incluir itens filtrados nos totais e, em seguida, marque ou desmarque a caixa Marcar totais com *. Se esta for marcada, aparece um indicador de asterisco (*) nos totais fora dos campos que têm os totais visuais desligados. Esta opção só está disponível em Tabelas Dinâmicas que estão ligadas a uma origem de dados OLAP que suporte a expressão de idioma MDX. Afeta todos os campos de uma Tabela Dinâmica.

Para incluir ou excluir os itens filtrados em totais de conjuntos de nomes, marque ou desmarque a caixa Incluir itens filtrados nos totais dos conjuntos. Esta opção só está disponível em Tabelas Dinâmicas que estão ligadas a uma origem de dados OLAP. Para incluir ou excluir itens filtrados em subtotais, marque ou desmarque a caixa Subtotal de itens de página filtrados.

e. Para mostrar ou ocultar legendas de campos ou filtros pendentes, clique no separador Apresentar e, em seguida, marque ou desmarque a caixa de verificação Apresentar legendas de campos e filtros pendentes.

11

Ordenar dados numa Tabela Dinâmica A ordenação de dados por ordem alfabética ou dos valores maiores para os menores (ou vice-versa) é útil quando possui grandes quantidades de dados na Tabela Dinâmica que criou.

A ordenação permite-lhe organizar os dados para que seja mais fácil localizar os itens que pretende analisar.

Por exemplo, as células Etiquetas de Linha e Etiquetas de Tabela têm um botão de seta junto a elas que pode utilizar para ordenar. a. Clique num campo na linha ou coluna que pretende ordenar. b. Clique na seta

em Rótulos de Linha ou Rótulos de Coluna e, em seguida, clique

na opção de ordenação que pretende.

c.

Para ordenar os dados por ordem ascendente ou descendente, clique em Ordenar de A a Z ou Ordenar de Z a A.

12

As entradas de texto são ordenadas por ordem alfabética, os números são ordenados do maior para o mais pequeno (ou vice-versa) e as datas ou horas são ordenadas da mais antiga à mais recente (ou vice-versa).

Ordenar uma coluna que não tem um botão de seta Pode ordenar em valores individuais ou em subtotais ao clicar com o botão direito do rato numa célula, ao escolher Ordenar e ao selecionar um método de ordenação. A sequência de ordenação é aplicada a todas as células ao mesmo nível na coluna que contém a célula.

No exemplo apresentado abaixo, os dados no nível de categoria (Capacete, Mala de viagem) estão ordenados por ordem alfabética, de A a Z.

13

Para ver os totais gerais para produtos ordenados do maior para o menor, selecione qualquer número na coluna Total Geral e ordene.

Configurar opções de ordenação personalizadas Para ordenar itens específicos manualmente ou para alterar a ordem de ordenação, pode configurar as suas próprias opções de ordenação.

1. Clique num campo na linha ou coluna que pretende ordenar.

2. Clique na seta

em Rótulos de Linha ou Rótulos de Coluna e, em seguida, clique

em Mais Opções de Ordenação.

14

3. Na caixa de diálogo Ordenar, escolha o tipo de ordenação que pretende:



Clique em Manual para reordenar os itens arrastando-os.

Não é possível arrastar os itens que são apresentados na área Valores da Lista de Campos da Tabela Dinâmica.



Clique em Ascendente (de A a Z) por ou Descendente (A a Z) por e, em seguida, selecione o campo que pretende ordenar.

15

4. Para opções adicionais, clique em Mais Opções e, em seguida, escolha a opção que pretende na caixa de diálogo Mais Opções de Ordenação:







Em Ordenar Automaticamente, marque ou desmarque a caixa Ordenar automaticamente sempre que o relatório for atualizado para permitir ou não a ordenação automática sempre que os dados da Tabela Dinâmica forem atualizados.

Em Primeira chave de ordenação, escolha a ordenação personalizada que pretende utilizar. Esta opção só se encontra disponível quando a caixa Ordenar automaticamente sempre que o relatório for atualizado em Ordenação Automática não está selecionada.

Em Ordenar Por, clique em Total Geral ou Valores nas colunas selecionadas para ordenar por estes valores. Esta opção não está disponível quando configura a ordenação para Manual.

Quando estiver a ordenar dados numa Tabela Dinâmica, tenha em conta o seguinte:



Os dados que possuem espaços iniciais irão afetar os resultados de ordenação. Para otimizar os resultados, remova quaisquer espaços iniciais antes de ordenar os dados.

Não pode ordenar entradas de texto sensíveis a maiúsculas.



Não pode ordenar os dados por um formato específico, como a cor da célula ou do



tipo de letra, nem por indicadores de formatação condicional, como conjuntos de ícones.

16