Baixe o app para aproveitar ainda mais
Prévia do material em texto
Excel Básico ao Intermediário Versão 8.0 1 Módulo 3 - Organização de Dados Excel Básico ao Intermediário Versão 8.0 2 Direitos desta edição reservados A Voitto Treinamento e Desenvolvimento www.voitto.com.br Supervisão editorial: Thiago Coutinho de Oliveira Versão 8.0 É PROIBIDA A REPRODUÇÃO Nenhuma parte desta obra poderá ser reproduzida, copiada, transcrita ou mesmo transmitida por meios eletrônicos ou gravações sem a permissão, por escrito, do editor. Os infratores serão punidos pela Lei n° 9.610/98. Módulo 3 – Organização de Dados http://www.voitto.com.br/ Excel Básico ao Intermediário Versão 8.0 3 Neste módulo trabalharemos com o tratamento e organização dos dados. 3.1 Congelar Painéis No Excel, é comum trabalharmos com planilhas muito grandes e constantemente precisamos rolar a janela para baixo a ponto de não visualizar cabeçalho. Para solucionar esse problema, existe um recurso bem simples chamado Congelar Painéis, que veremos a seguir. Quando trabalhamos com base de dados, por exemplo: cadastro de clientes, registro de vendas, controle de estoque e outros é quase que obrigatório esse recurso, pois ele auxilia a identificação das informações do cabeçalho e evita erros de referência. Uma pequena loja, cujo negócio é vender artigos para uma empresa de peças, pretende analisar suas vendas. Para isso, os dados foram compilados em um registro de vendas com as principais informações que o gerente julgou importante. Para utilizarmos esse recurso: Selecione a célula C5 > clique na Guia Exibição > Congelar Painéis > Congelar Painéis: Podemos agora navegar pelos dados da planilha e notar que o cabeçalho continua fixo, facilitando a identificação dos dados contidos na tabela. Exemplo 3 – Venda Artigos (Aba 1) Excel Básico ao Intermediário Versão 8.0 4 3.2 Classificação em Níveis Muitas vezes recebemos uma base de dados na qual é necessário fazer uma ordenação dos dados. Essa ordenação pode ser alfabética, numérica (crescente ou decrescente), por data e outros. O Excel nos permite fazer uma classificação em vários níveis, sendo que a classificação ocorre do primeiro para o último nível. Trabalhando ainda exemplo “Venda Artigos”, iremos classificar nossa base de dados. Para isso: Selecione a Guia Dados > Classificar; No item congelar painéis, podemos ver outras duas opções: Congelar linha superior e congelar primeira coluna (congelam apenas a primeira linha e a primeira coluna, respectivamente). No caso da primeira opção, congela todas as linhas e colunas acima e à esquerda da célula selecionada. Caso queira voltar ao normal, na guia Exibir marque a opção Congelar Painéis > Descongelar Painéis. Dica Voitto: A classificação de níveis é muito utilizada para organizar a sua base de dados. Logo, é interessante utilizá-la para “agrupar” os seus clientes, ordenar os valores de faturamento ou quantidade vendida. Dica Voitto: Exemplo 3 – Venda Artigos (Aba 1) Excel Básico ao Intermediário Versão 8.0 5 No item Classificar por escolha a opção “Produto”; Clique em Adicionar Nível; Em seguida, na opção E depois por selecione “Quantidade”, Ordem “Do Menor para o Maior” e clique em OK. Você verá que os dados selecionados foram ordenados de forma crescente com base em “Produto” como critério principal e em “Quantidade” como critério secundário, como mostrado abaixo. 3.3 Autofiltro (Ctrl + Shift + L) Filtrar é um meio fácil de localizar e trabalhar com um subconjunto de dados de uma lista. Ao utilizar o filtro, o Excel exibe apenas as linhas que contêm um determinado valor ou que atendam a determinadas condições de pesquisa denominadas “critérios”. O autofiltro serve para filtrar uma lista rapidamente, ajustando o conteúdo de uma célula ou usando critérios simples de comparação. Excel Básico ao Intermediário Versão 8.0 6 No exemplo “Vendas Artigos”, o dono da loja pretende verificar o desempenho dos vendedores. Além de entender melhor o processo das vendas, ele pretende estipular uma comissão com base no valor total vendido. Para criar o “Autofiltro” selecione qualquer célula dentro da tabela que se deseja trabalhar, vá em Guia Dados > Filtro: Observe que, ao clicar no botão, apareceram algumas setas ao lado do título de cada coluna. Clique na seta da coluna “Vendedor” e veja as opções de filtragem. Selecione, por exemplo, apenas o Vendedor “André Saraiva”. Quando utilizamos o filtro, deve ficar claro que os dados não mudam de ordem. Ocorre apenas uma visualização diferente do “total”. Sendo assim, ao coletar informações específicas de, por exemplo, um cliente, faça o filtro e depois utilize o Ctrl+C e cole “valores” em outro espaço. Fazendo isso, você garante que não venham informações a mais, além de fórmulas e referências da tabela filtrada. Dica Voitto: Exemplo 3 – Venda Artigos (Aba 1) Excel Básico ao Intermediário Versão 8.0 7 Dê OK e repare que em nossa tabela, aparecerão apenas as vendas do vendedor selecionado. Navegue pelas setas de filtragem e veja as opções de filtragem já existentes no Excel. Para desfazer os filtros, basta seguir o mesmo caminho para a criação destes: Dados > Filtro ou clicar em Limpar. Os filtros aparecem de acordo com os dados na base e cada coluna apresenta uma característica diferente. No entanto, você pode recorrer a filtros que não são padronizados no software. Esse recurso está disponível em Filtros de Texto, Filtros de Número ou qualquer classe esteja trabalhando. Veja também: Excel Básico ao Intermediário Versão 8.0 8 3.4 Validação de Dados Ao trabalhar com planilhas, é comum cometermos erros de digitação ou mesmo a inserção de um dado não compatível pelo usuário. Para minimizar esses erros, o Excel possui uma ferramenta chamada “Validação de Dados”. Como desenvolvedor é possível impor algumas regras de digitação e ainda enviar mensagens de alerta sobre o erro cometido pelo usuário. Uma empresa que trabalha com a venda de vários tipos de produtos deseja fazer o controle de estoques para saber quanto possui de cada produto em seu estoque. No exemplo abaixo, a primeira tabela demonstra o controle de entradas e saídas e a segunda tabela o estoque total da empresa. De acordo com as movimentações, o estoque é atualizado. Para garantir que o controle seja realizado, a inserção de produtos, tipo de movimentação e data da operação devem seguir algumas regras. Os produtos devem ser os mesmos contidos na tabela de estoque total, a movimentação só pode ser entrada ou saída e a data deve ser maior ou igual à hoje. O principal erro na Classificação de Dados e Filtros ocorre quando é feita a seleção de um determinado conjunto de células de forma não proposital. Quando isso acontece, o Excel entende que o filtro e/ou classificação devem ser aplicados apenas naquele intervalo. Portanto, se for aplicar na tabela toda, selecione qualquer célula dentro da tabela que o Excel já entende que o recurso será aplicado na tabela toda. Principais Erros: Se você deseja restringir o preenchimento a uma lista de produtos, procedimentos, meses e outros, pode ser criada uma lista com esses critérios em algum espaço da planilha para ser utilizada como referência para a validação de dados. Isso ficará mais claro no exemplo a seguir. Dica Voitto: Exemplo 3 – Controle de Estoque – Aba 2 Excel Básico ao Intermediário Versão 8.0 9 Vamos inserir no campo de produto (B6:B23) uma validação de dados do tipo lista com os produtos no estoque total(H7:H15). Para isso, selecione o intervalo (B6:B23) > selecione a Guia Dados > Validação de Dados > Validação de Dados; Veja que abriu a caixa de diálogo Validação de Dados na guia Configurações, cujos critérios definiremos: A configuração acima permite que no intervalo selecionado B6:B23 sejam inseridos apenas os argumentos contidos na lista H7:H15. Sendo assim, nosso controle de entradas e saídas garante que só serão inseridos produtos contidos no estoque. Excel Básico ao Intermediário Versão 8.0 10 Para inserir o tipo de movimentação, trabalharemos com apenas dois tipos: Entradas, que aumentam o estoque total, e Saídas, que reduzem o estoque total. Para inserir esses dois argumentos, vamos selecionar o intervalo C6:C23 e clicar novamente na Guia Dados > Validação de Dados > Validação de Dados. Em seguida, vamos definir a seguinte configuração: Perceba que a opção de lista pode ser inserida a partir de uma lista já elaborada, semelhante à o que fizemos anteriormente ou pode ser redigida, com os argumentos separados por ponto e vírgula. Como estamos trabalhando com produtos por unidade, não é possível retirar ou adicionar ao nosso estoque partes fracionadas de produto, por exemplo, 0,5 Produto 1. Outra restrição é a movimentação de produtos negativos, por exemplo, -1 Produto 1. Sendo assim, colocaremos uma validação de dados que garanta a inserção apenas de valores inteiros e positivos. Selecionaremos o intervalo D6:D23 e seguiremos o mesmo procedimento, porém as configurações serão: Excel Básico ao Intermediário Versão 8.0 11 Perceba que ao tentar inserir valores fracionados, a validação de dados não permite. Essa mensagem de erro pode ser personalizada. Vamos criar o nosso próprio alerta de erro: Selecionar o intervalo (D6:D23) > Dados > Validação de Dados > Validação de Dados; Selecionar a aba “Alerta de Erro” e preencher os campos com os seguintes dados: No campo “Estilo” existem três opções. Para o nosso caso, utilizaremos a opção “Parar”. Essa condição não permite que sejam inseridos valores que não atendam a validação de dados inserida. Os outros dois tipos “Aviso” e “Informações” alertam sobre a não conformidade, porém permitem a inserção dos dados. Veja também: Excel Básico ao Intermediário Versão 8.0 12 Para finalizar, a data da movimentação deve ser igual ou inferior a hoje, não podendo registrar uma movimentação futura. Selecionaremos agora o intervalo F6:F23 e aplicaremos a seguinte validação: Para orientar os usuários, podemos inserir um alerta que auxilia no preenchimento. Ainda na janela demonstrada acima, utilizaremos a aba “Mensagem de Entrada”. Ao clicar em qualquer célula do intervalo selecionado, aparecerá o aviso para o preenchimento correto: Excel Básico ao Intermediário Versão 8.0 13 Agora vamos inserir alguns valores para ver o funcionamento do nosso controle de estoque e olhar a variação do estoque final e valor em estoque. 3.5 Proteger Planilhas A proteção de planilha é usada basicamente para proteger determinadas partes do arquivo contra remoções ou mudanças acidentais das fórmulas inseridas. Essa proteção torna-se bastante útil, uma vez que, em alguns casos, podemos demorar horas produzindo uma fórmula e esta pode ser removida acidentalmente a qualquer momento, principalmente se a planilha for manuseada por mais de uma pessoa. Para proteger a planilha, devemos primeiro decidir se queremos proteger a planilha inteira, ou parte dela. O recurso de proteção permite que se proteja parte da planilha e que se liberem outras partes, tais como áreas onde são lançados dados que precisam ser “acrescentados” diariamente a um banco de dados, preservando-se as fórmulas, formatação, inclusão, exclusão de linhas e colunas e etc. A validação de dados não permite que sejam inseridos dados que não esteja nos critérios definidos. No entanto, existem duas formas de a validação de dados não funcionar. A primeira é quando você copia o conteúdo e uma célula e cola no intervalo da validação de dados. A segunda é quando você cria a validação de dados em um intervalo já preenchido. Logo, o dado permanecerá, mesmo que incorreto. Principais Erros: Muitos acreditam que a proteção de planilha com uso de uma senha resguarda informações sigilosas presentes. No entanto, pessoas que possuem conhecimento um pouco mais avançado podem desbloquear a planilha sem possuir a senha. Isso torna-se possível com o uso do Visual Basic for Applications (apresentado no módulo 6). Para quem já tiver conhecimento, basta pesquisar no Google por: “Macro para desbloqueio de planilha” > copiar no ambiente VBA > executar. Dica Voitto: Excel Básico ao Intermediário Versão 8.0 14 A empresa “Guloseimas” fabrica 20 tipos de biscoitos diariamente e precisa registrar e acompanhar os custos e a produção. Em uma simples planilha é anotado, pelo pessoal do chão de fábrica, a quantidade de biscoitos fabricados por dia para que se possa levantar corretamente os custos dos mesmos. Todas as células do Excel são bloqueadas por padrão. Logo, devemos selecionar o intervalo de células o qual não desejamos proteger, que nesse caso é apenas a quantidade produzida, pois, este campo deverá ser preenchido frequentemente pelo chão de fábrica. Neste caso, os outros campos ficam inalteráveis, pois são dados fixos da empresa ou dados calculados. Selecione o intervalo C6:C25; Clique com o botão direito do mouse sobre a seleção e escolha a opção Formatar Células; Acesse a Guia Proteção e desmarque a opção Bloqueadas, clicando em OK em seguida. 3.5.1 Protegendo a Planilha Inteira Na guia Revisão selecione a opção Proteger Planilha: Vamos colocar uma senha de fácil memorização para aprender o conceito. Na janela a seguir, digite a senha 1234: Exemplo 3 – Rateio – Aba 3 Excel Básico ao Intermediário Versão 8.0 15 Você pode simplesmente proteger a planilha, sem a utilização de senha. Veja que você pode editar o que será permitido que o usuário faça com as células bloqueadas. Em nosso caso, permitiremos que o usuário apenas selecione as células. Portanto, clique em “OK”, redigite a mesma senha e clique em “OK” novamente. Faça um teste agora tentando editar as células que não foram desbloqueadas e a células que foram desbloqueadas. Desproteja a planilha para que possamos continuar nosso trabalho! 3.5.2 Protegendo uma planilha de modo que usuários específicos possam alterar campos específicos Outro recurso bastante interessante é o de bloquear a planilha e permitir que apenas alguns usuários possam editar certos intervalos, ou seja, é como se criássemos usuários para um arquivo com senhas diferentes. Hoje em dia, grande parte das empresas possuem um sistema intranet, em que vários usuários acessam o mesmo arquivo. Em alguns casos, nem todas os departamentos devem possuir acesso à todas as informações e ainda poder editar essas. Portanto, este tipo de recurso pode ser utilizado para criar uma senha para cada departamento (financeiro, comercial, recursos humanos e outros). Dica Voitto: Excel Básico ao Intermediário Versão 8.0 16 Consideremos que a coluna “Preço de Venda” possa ser editada apenas pelo escritório de vendas. Para isso criaremos uma senha de edição que será disponibilizada apenas para esse escritório. Ainda na guia Revisão clique no botão Permitir a Edição de Intervalos; Na tela a seguir, clique em Novo e preencha os campos conforme a figura abaixo, colocando a senha 123: Clique em OK, redigite a senha e OK novamente. Veja que a permissão foi criada: Exemplo 3 – Rateio (Aba 3) Excel Básico ao Intermediário Versão 8.0 17 Clique em Protegerplanilha e coloque a senha 1234 e finalize a proteção. Agora, tente modificar qualquer célula da coluna “Preço de Venda”. Percebeu a diferença? Agora o Excel permite que o usuário modifique a célula apenas com a senha criada para o intervalo. 3.5.3 Protegendo uma pasta de trabalho Você pode também proteger toda sua pasta de trabalho. Ao proteger uma pasta de trabalho, você estará bloqueando a estrutura da pasta, de modo que as planilhas não possam ser excluídas, movidas, ocultas, reexibidas, renomeadas e novas planilhas não possam ser incluídas. Você pode também impedir que as janelas de uma pasta de trabalho sejam movidas, redimensionadas, reexibidas ou fechadas. Selecione a guia Revisão > Proteger Pasta de Trabalho; Coloque a senha 1234 novamente e dê OK. Agora, tente modificar a estrutura da pasta de trabalho, criando uma nova planilha por exemplo. Desproteja nossa pasta de trabalho, clicando em “Proteger Pasta de Trabalho” e desmarcando a opção “Proteger Estrutura e Janelas”. Excel Básico ao Intermediário Versão 8.0 18 3.5.4 Protegendo um arquivo com senha Ainda temos uma última opção de proteção, que é proteger o arquivo com senha. Esse é o formato de proteção mais seguro, o qual limita o acesso ao arquivo. Vejamos: Selecione o menu Arquivo > Informações > Proteger Pasta de Trabalho > Criptografar com Senha. Criptografar o conteúdo com a senha 123. Dê um OK confirmando as senhas e salve o arquivo por cima do arquivo existente. Feche o arquivo e tente abri-lo novamente. Agora, o Excel pede senha para abrir o arquivo. Para retirar a senha, basta ir ao mesmo lugar, apagar a senha e salvar novamente por cima do arquivo existente. 3.6 Consolidar Dados Para resumir e relatar os resultados de planilhas separadas, você pode consolidar dados de cada planilha separada em uma planilha principal. As planilhas podem estar na mesma pasta de trabalho que a planilha principal ou em outras pastas de trabalho. Ao consolidar dados, você está montando dados para que possa atualizá-los e agregá-los com mais facilidade. Lembre-se de que para utilizar este recurso, os dados a serem consolidados devem estar em matrizes de mesmo tamanho e na mesma posição. Dica Voitto: Excel Básico ao Intermediário Versão 8.0 19 Abaixo temos o mesmo caso da Voitto Metais que vimos no Módulo 2, porém utilizaremos um novo recurso para fazer o somatório das produções das peças. Selecione a célula C6; Na Guia Dados, clique no ícone consolidar. Veja a tela que vai aparecer: Em Referência, selecionaremos os dados a serem consolidados, que são os valores contidos nas planilhas “Turno 1”, “Turno 2” e “Turno 3”. Selecione cada intervalo (um por um), sempre clicando em Adicionar, até que todos os três estejam no campo Todas as referências. Marque também a opção de Criar Vínculo com os Dados de Origem. Exemplo 3 – Resumo da Produção (Aba 4) Excel Básico ao Intermediário Versão 8.0 20 Clique em OK e veja o que vai acontecer. Todos os valores foram somados (consolidados) na tabela “Resumo da Produção”. Veja agora que interessante, apesar de parecer o mesmo resultado do nosso exemplo com “Referência 3D”, existe um diferencial em consolidar os dados. Repare que no lado direito do nome das linhas, apareceram 4 símbolos de “+”. Clique em um deles para ver o que acontece. Excel Básico ao Intermediário Versão 8.0 21 Veja que ao clicar, os dados que estão consolidados aparecem na planilha principal. Essa é a grande vantagem de consolidar os dados, pois agora você pode exibir ou ocultar sempre que quiser os dados detalhados ou consolidados. 3.7 Subtotais Existe uma maneira muito prática de organizar planilhas muito extensas e gerar informações dentro do Excel: é o comando Subtotais, encontrado na guia Dados. Ele organiza as planilhas por blocos de informação, estes podem ser exibidos ou não. Quando ocultos, a planilha fica simplificada e de fácil leitura. Quando exibidos, permitem observar os dados com mais detalhes. Caso deseje apenas realizar a consolidação dos dados sem a visualização dos símbolos “+”, basta desmarcar a opção criar vínculos com os dados de origem. Sendo assim, o resultado será bem parecido com o que vimos no módulo 2. Veja também: Por tratar-se de uma consolidação de dados da tabela toda, muitas pessoas selecionam todo o intervalo, porém só é necessária a primeira célula. Outro erro é tentar trabalhar com tabelas de tamanhos diferentes e em posições diferentes. Principais Erros: Ao utilizar o recurso de subtotais, os dados que você deseja calcular devem estar ordenados, em outras palavras, devem estar classificados (recurso aprendido neste módulo). Portanto, primeiro passo é verificar se os dados estão classificados e agrupados para depois utilizar os subtotais. Dica Voitto: Excel Básico ao Intermediário Versão 8.0 22 A empresa “Pneumáticos Ltda.” possui operações em todas as regiões do Brasil. O gerente de finanças deseja efetuar uma análise nos relatórios de vendas por região e por mês, mas essa informação tem que ser entregue em menos de 10 minutos. Vamos agir a fim de obter esses resultados de maneira ágil. Temos aqui alguns dados referentes à venda de pneus tais como, região, mês e valor da venda. Não é difícil de perceber que podemos juntar nossos dados em dois tipos de grupos: o grupo “Região” e o grupo “Mês”. Primeiramente, vamos fazer os agrupamentos por região: Assim como explicado anteriormente, pode ser selecionada apenas uma célula dentro do intervalo que o Excel já entende que o recurso será aplicado na tabela toda; Em seguida, selecione a guia Dados > Estrutura de Tópicos > Subtotal e preencha os campos conforme imagem abaixo: Observe que vamos adicionar o subtotal em Vendas e separada por Região. Exemplo 3 – Venda de Pneus (Aba 8) Excel Básico ao Intermediário Versão 8.0 23 Um erro muito comum é o não agrupamento da categoria que se deseja calcular os subtotais. Com os dados desorganizados, são calculados vários subtotais, pois este recurso faz a soma de termos até aparecer um item diferente abaixo, ou seja, sem agrupamento possuem diversos itens diferentes. Outro principal erro é quando é feita a seleção incorreta da tabela, não aparecendo os itens para o cálculo do subtotal. Portanto, sempre selecione apenas uma célula qualquer na tabela que o próprio software irá expandir o intervalo de seleção. Principais Erros: Vimos acima o cálculo de subtotais por região. Agora, vamos testar os subtotais por mês. Para isso, temos que fazer uma reorganização na base de dados de modo a ter uma classificação por mês e não mais por região. Para isso, utilize o recurso Classificar na Guia Dados e escolha a coluna mês; Repita o procedimento de criar Subtotais; Como ficam os resultados com a nova classificação? Veja também: Excel Básico ao Intermediário Versão 8.0 24 3.8 Resumo do Módulo 3 • Congelar Painéis para fixação das informações no cabeçalho de banco de dados; • Recurso de Classificar para ordenação e agrupamento de dados; • Filtro para facilitar a localização de dados ou visualização de dados específicos; • Bloqueio de planilhas; • Consolidação de dados com a função SOMA (semelhante ao recurso do módulo 2); • Subtotais para fazer cálculo de grupos de dados. Lembrar de classificar antes. A empresa Voitto Materiais de Escritório possui três principais produtos que são vendidos em quantidades consideráveis em suas quatro lojas. O gerente da loja solicitou a você que seja criada uma restrição ao fornecimento de desconto paraum valor máximo de 6%. A segunda solicitação é apresentar o somatório do total vendido por loja. l antes de aplicar o recurso. Logo, o Excel entende que é para aplicar o recurso apenas Desafio Voitto:
Compartilhar