Baixe o app para aproveitar ainda mais
Prévia do material em texto
58 Roteiro Aula 16 Tabelas Dinâmicas e Gráficos Dinâmicos. Filtros Interativos. Subtotais. Banco de dados na planilha. Proteção de Planilhas - Abrir arquivo Aula16.xlsx 1- Tabelas Dinâmicas e Gráficos Dinâmicos. - São ferramentas interativas que permitem visualizar resumos de planilhas que contém grande volume de dados. Exemplo: Clientes / Histórico de Compras / Cidades - Planilha 1 - Selecionar a área útil da tabela. Guia Inserir / Tabelas / Tabelas Dinâmicas 59 60 2- Filtros Interativos Permite trabalhar com planilhas como se fossem bancos de dados, identificando grupos de informações e classificando-as em diferentes categorias. Para ativar o recurso de AutoFiltro é necessário selecionar uma célula útil da tabela. Guia Início / Edição / Classificar e Filtrar / Filtro Quando a filtragem estiver ativada, clique na seta do cabeçalho da coluna para escolher um filtro para a coluna. - Planilha 2 - Selecione uma célula da tabela. Guia Início / Edição / Classificar e Filtrar / Filtro - Os títulos das colunas se transformaram em caixas de combinação. - Clique na seta da coluna CARGO. Selecione a opção Analista de Sistemas. A seta da coluna CARGO passa a mostrar o ícone de filtro. Isso é uma indicação que nessa coluna foi realizado algum tipo de filtragem. - Na coluna ESCOLARIDADE para filtrar de tal forma que mostre apenas aqueles com nível superior selecione a opção Superior Completo. A seta da coluna ESCOLARIDADE também passou a mostrar o ícone de filtro. - Clique na seta da coluna CARGO. Selecione a opção (Selecionar Tudo) para mostrar todos os itens. - Clique na seta da coluna ESCOLARIDADE. Selecione a opção (Selecionar Tudo) para mostrar todos os ítens. 61 Para “limpar” as filtragens, ou seja, para mostrar todos os dados, basta clicar no ícone Limpar. - Classificação de coluna que contém texto: - Classificar de A a Z - Classificar de Z a A - Filtros de Texto 62 - Classificação de coluna numérica: - Classificar do menor para o maior - Classificar do maior para o menor - Filtros de números Por exemplo, na coluna SALÁRIO selecione a opção Filtros de Números / 10 primeiros - Selecione os 5 últimos. 63 - Personalizar AutoFiltro. Na coluna CIDADE selecione a opção Filtros de Texto / Personalizar Filtro É possível usar duas combinações de filtragem conectadas pelos operadores E e OU. O primeiro campo do filtro apresenta as seguintes opções: - é igual a - é diferente de - é maior do que - é maior ou igual a - é menor do que - é menor ou igual a - começa com - não começa com - termina com - não termina com - contém - não contém No segundo campo de filtro estão os valores de comparação. Por exemplo, clique em CIDADE e selecione a opção Filtros de Texto / Personalizar Filtro. No primeiro campo da janela escolha “é igual a” e no campo ao lado selecione “São Paulo”. Escolha a opção “OU”. No segundo conjunto selecione “é igual a” e “Osasco”. Para retirar todos os filtros de uma maneira rápida basta: Limpar Filtro de ... 64 - Para visualização de SUBTOTAIS por grupos basta utilizar a opção: Guia Dados / Estruturas de Tópicos / Subtotal. Para que o comando seja acionado, uma célula válida deve estar selecionada, ou seja, uma célula abaixo de uma das colunas com dados. Abre a janela Subtotais. - Há três (03) partes principais: 1 - A cada alteração em... 2 - Usar função: - Soma - Contar Número - Média - Máximo - Mínimo - Produto - Desvio Padrão... 3 - Adicionar subtotal a - Abaixo há outras três opções: - Substituir subtotais atuais - Quebra de página entre grupos - Resumir abaixo dos dados - Por fim, se desejar retirar os somatórios basta clicar em “Remover todos”. 65 - Por exemplo, escolha: A cada Alteração em CARGO, Usar função ContNum, Adicionar subtotal a CIDADE, Substituir subtotais atuais, Resumir abaixo dos dados. - Por exemplo, escolha: A cada Alteração em CARGO, Usar função Média, Adicionar subtotal a SALÁRIO, Substituir subtotais atuais, Resumir abaixo dos dados. Observe os números no canto superior esquerdo da planilha. Eles são utilizados para mostrar ou esconder as informações do Subtotal. 66 3- Banco de dados na planilha Importar e analisar informações de um arquivo de banco de dados utilizando o Excel. - Planilha 3 Guia Dados / Obter dados externos / Do Access Selecionar arquivo AULA16_MDB Onde deseja colocar os dados? Como Tabela, na planilha existente: Célula A3. OK. 67 Criar um gráfico com as colunas NOME e SALÁRIO. (Dica: pressione a tecla CTRL e seleciona com o mouse as colunas NOME e SALARIO) - Tipo de gráfico: Colunas / Colunas 3D agrupadas. - Titulo do gráfico – Acima do Gráfico: Salários - Titulo do Eixo Horizontal Principal: – Título abaixo do eixo: Indivíduos - Tamanho da fonte: 12 - Titulo do Eixo Vertical Principal – Título Girado: – Título girado: Valores em Reais - Tamanho da fonte: 12 - Rótulos de dados: nenhum - Tabela de dados: nenhuma - Linhas de grade Horizontais Principais - mostrar linhas de grade principais. - Linhas de grade Verticais Principais - mostrar linhas de grade secundárias. - Mostrar legenda abaixo. - Posicionar gráfico como objeto na planilha 3. 68 Filtrar: Filtrar para que mostre apenas os dados dos moradores do Rio de Janeiro. Mostrar todos novamente. Filtrar para que mostre apenas os dados dos moradores do Brasil. A cada opção de filtragem acionada, o gráfico é alterado automaticamente! Para provar que as informações da planilha 3 estão vinculadas com o arquivo AULA16_MDB , vamos alterar alguns dados no arquivo Access e em seguida verificar a mudança automática no arquivo do Excel. Salve e em seguida feche o arquivo Aula 16.xlsx Abrir o arquivo AULA16_MDB e alterar o salário do George para 250 000. Salvar e fechar. Abrir novamente o arquivo Aula 16.xlsx No arquivo Excel: Guia Dados / Conexões / Atualizar Tudo Veja que o valor do salário do George foi alterado. 69 4- Proteção de Planilhas Objetivo: Criar uma planilha protegida que tenha as fórmulas ocultas, mas que permita que o usuário digite dados em determinadas células. Guia Revisão / Alterações / Proteger Planilha - Planilha 4 - Calcular os somatórios da coluna TOTAL e da linha TOTAL. - Para liberar as células desejadas, clique em Permitir que os Usuários Editem Intervalos. Surgirá a janela abaixo. Clique em Novo: 70 - Selecione o intervalo (D4:F8). OK. - Esse intervalo estará desbloqueado quando a planilha for bloqueada. Para esconder a fórmula, inicialmente devemos selecionar todas as células com fórmulas. Selecione o intervalo (D9:F9) e o intervalo (G4:G8). Em seguida: Guia Início / Edição / Localizar e Selecionar /Ir para Especial Na janela “Ir para especial”. Selecione “Fórmulas”. OK. Pronto, todas as células com fórmulas estão selecionadas. Mantenha as células com fórmulas selecionadas. Para finalizar a etapa para ocultar as fórmulas selecionadas, clique com o botão direito sobre a seleção e clique em Formatar Células. Na última aba, a aba de proteção marque as opções “Bloqueadas” e “Ocultas”. OK. 71 No entanto para concluir a operação total de proteção é necessário um último comando: Guia Revisão / Alterações / Proteger Planilha A senha é opcional. Observe agora que apenas as células do intervalo (D4:F8) podem ser modificadas,e quando tentamos modificar as demais células da planilha surge uma mensagem indicando que a planilha está protegida. Além disso, as fórmulas estão ocultas. Para editar ou modificar as células bloqueadas é preciso desproteger a planilha: Guia Revisão / Alterações / Desproteger Planilha 72 5- Tabelas Dinâmicas e Gráficos Dinâmicos. - Planilha VENDAS Visualizar as Vendas por Região. Clicar em alguma “célula útil”, ou seja, com conteúdo ou informações. Em seguida: Guia Inserir / Tabelas / Tabelas Dinâmicas Surgirá uma nova planilha. 73 Selecionando na Lista de Campos da Tabela Dinâmica as opções REGIÃO e VENDAS, temos a visualização das vendas por região. Na coluna Soma de Vendas encontra-se o somatório das vendas feitas em cada região. 74 No caso de desejar outra função (que não seja SOMA), é necessário selecionar a uma das células onde está o resultado da função e na Guia Opções, Campo Ativo e clicar em Configurações de Campo. Surgirá a seguinte janela com opções de funções: ContNúm, Média, Máximo, Mínimo, ... 75 Para visualizarmos as Vendas Trimestrais por Região, basta arrastar o CAMPO Trimestre para a área de Rótulos de Coluna. 76 - Planilha PEDIDOS Subtotais na Tabela Dinâmica Iniciaremos o procedimento de criação de tabela dinâmica do mesmo modo que anteriormente. 77 Selecionando na Lista de Campos da Tabela Dinâmica as opções PRODUTO e QUANTIDADE, temos a soma das quantidades de cada produto. Arrastando o CAMPO Promoção para a área de Rótulos de Linha e arrastando o CAMPO Publicidade para a área de Rótulos de Coluna temos a tabela organizada com os SUBTOTAIS.
Compartilhar