Apostila excel avançado, Trabalhos de Administração Empresarial

Baixe Apostila excel avançado e outras Trabalhos em PDF para Administração Empresarial, somente na Docsity! Excel Avançado Autor: Leonardo Breyer 2 APOSTILA DE EXCEL - AVANÇADO - Relacional Consultoria Ltda Rua da Candelária, 60 – 7º andar Tel: (0xx21) 219-5161 Fax: (0xx21) 219-5155 e-mail: claudiad@relacional.com.br Copyright © 2000-2001 Leonardo Breyer APOEXCEL_AVANC 1 INTRODUÇÃO O Excel 97 possui uma Linguagem de programação chamada de Visual Basic para Aplicações (VBA). Com ela você poderá automatizar seus trabalhos e ganhar rendimento os recursos existentes em suas planilhas. No Excel podemos também automatizar tarefa repetidas. A este recurso chamamos de Macros. As macros são rotinas que utilizam o Visual Basic e que podem ser criadas sem o conhecimento de programação mas, utilizam lógica. Além disso um dos melhores, talvez o melhor, recurso que o MS-Excel apresenta para os seus usuários é a Tabela Dinâmica, que permite a apresentação dos dados de diversas formas, a partir de uma mesma base de informação. Uma Tabela Dinâmica (ou Pivot Table Report na versão do MS-Excel 97 em inglês), é uma tabela interativa de planilha, utilizada para resumir e analisar dados de uma lista ou tabela já existente. Colunas ou linhas de sumarização ou dados, podem ser invertidos, automaticamente, pelo usuário, criando novas versões de relatório em fração de segundo. Um exemplo prático deste tipo de funcionalidade é estruturar um relatório de vendas por Vendedor e por Produto. Caso queira visualizar os dados por Produto e por Vendedor (ou Cliente), será necessário a elaboração de um outro relatório, código adicional, etc. Com a Tabela Dinâmica do MS-Excel basta inverter, com o auxilio do mouse, a posição destas duas colunas, e pronto, o resultado está disponível para consulta. APOEXCEL_AVANC 2 CRIANDO MACROS A partir de agora iremos demonstrar como funciona o gravador de macro de Excel. Se você já utilizou o gravador de macro do Word, por exemplo, verá que este não é muito diferente. Para iniciar a gravação o usuário aciona o gravador e, em seguida, executa as tarefas na planilha. Neste momento o gravador estará gravando todas as ações executadas na planilha(Click de mouse, digitação etc), certas ou erradas e, depois, finalizar o gravador. Ao término deste processo, foi criada uma Macro(Módulo) que, contém uma série de linhas de comandos(Códigos, Instruções) sobre as ações executadas. INICIANDO UMA MACRO Você pode criar uma macro sempre que você achar que uma determinada tarefa é muito repetitiva. Com isso, utilizando a execução de uma macro, esta tarefa que possui uma série de passos é executada automaticamente. Exemplo: A planilha abaixo tem o objetivo de calcular a projeção mensal de gastos através da Receita do mês anterior e o % de Crescimento EMPRESA BRASILEIRA DE SERVI OS S.ª Taxas Abril Maio Junho Receita 5.000,00 6.250,00 7.062,50 % Crescimento 22% 25% 13% Despesas Gerais 8% Materiais 400 500 565 5% Sal rios 250 312,5 353,13 3% Benefícios Adicionais 7,5 9,38 10,59 7% Outros 350 437,5 494,38 % Crescimento Mês: 20% Taxas deverá estar na célula – A4 Fórmulas: Célula Fórmula Copiar para D5 C5*(1+D6) E5 C9 C5*$A$9 D9 e E9 C10 C5*$A$10 D10 e E10 C11 C10*$A$11 D11 e E11 C12 C5*$A$12 D12 e E12 APOEXCEL_AVANC 3 Iremos agora automatizar esta planilha criando a macro. Abaixo a sequência dos passos a serem seguidos. 1. Ative o gravador de Macros da Barra de Menu Ferramentas, Macro, Gravar nova Macro...; 2. Digite Crescimento na Caixa Texto Nome da Macro. O nome não poderá possuir espaços em branco; 3. Digite m na Caixa Texto Tecla de Atalho; 4. Pressione OK. A partir de agora suas ações estarão sendo gravadas; 5. Selecione a região da planilha D4:D12; 6. Utilize o Comando Editar; Copiar; 7. Selecione a célula C4; 8. Utilize o Comando Editar, Colar Especial, Valores; 9. Selecione a região E4:E12; 10. Utilize o Comando Editar; Copiar; 11. Selecione a célula D4 e pressione <Enter>; 12. Selecione a região da planilha E4:E12 e pressione <Enter>; 13. Selecione a região D4:E12; 14. Utilize o Comando Editar, Preencher, Sequência; 15. Selecione AutoPreenchimento; 16. Selecione a célula C16; 17. Selecione a célula E6 e pressione <Enter>; 18. Selecione a célula C16 e pressione a tecla DEL; 19. Termine a gravação da macro pressionando no Parar Macro. EXECUTANDO MACRO Existem várias maneiras de executar uma macro. Uma delas é através da Barra de Menu, Ferramentas, Macro. Outra opção é utilizar as teclas de atalho Alt↔F8. APOEXCEL_AVANC 6 SINTAXE DE COMANDOS Quando nos referimos a uma propriedade, o objeto cuja propriedade desejamos definir ou retornar valor vém na frente do comando, seguido de um ponto e, depois, do nome da propriedade. Exemplos: Cell.Columns.Heighs = 16 MsgBox(“Mensagem a ser Exibida”, Tipo) O objeto é separado da propriedade por um ponto. Ao utilizar propriedade no Visual Basic, você realiza uma das seguintes ações. - Definir o valor de um propriedade Objeto.Propriedade = expressão - Retornar o valor de uma propriedade Variável = objeto.propriedade É importante sabermos a diferença entre propriedades de um objeto e o método de um objeto. A diferença é que as propriedades possuem valores que são definidos ou retornados, enquanto os métodos são ações que você deseja que um objeto execute. Além disso, a maioria das propriedades recebe um único valor, enquanto os métodos podem receber um ou mais argumentos. Vamos ver agora como identificamos e criamos uma variável. A Instrução Dim declara(cria) uma variável no Visual Basic Exemplo: Dim NOME DA VARIÁVEL as TIPO DA VARIÁVEL Abaixo alguns dos Tipos que as variáveis podem ser: Tipo Tamanho(Memória utilizada) Boolean 2 Bytes Byte 1 Byte Currency 8 Bytes Date 8 Bytes Double 8 Bytes (ponto flutuante, dupla precisão) Integer 2 Bytes Long 4 Bytes String 1 Byte(por caracter) APOEXCEL_AVANC 7 NOTA: É importante de, sempre que criarmos uma variável ou uma funcionalidade(regra de negócio) descrever um comentário ou uma observação para que, não caia no esquecimento e facilite a qualquer pessoa que for efetuar alterações ou melhorias na Aplicação. Para inserir uma ou mais linhas de comentário em uma programação no Visual Basic devemos iniciar a linha com o caracter “PLIC” Exemplo: Sub Exemplo() ' ' Exemplo Macro ‘ ' Macro gravada em 25/01/2001 por Relacional Consultaria Ltda ‘ ' Esta rotina copia e cola uma área selecionada na Planilha Vendas, ‘ Pasta Executa Macro If C4 > 100 then Range("C8:D15").Select Selection.Copy Range("G8:H15").Select ActiveSheet.Paste Range("J8").Select ActiveSheet.Paste Application.CutCopyMode = False Endif End Sub Desta forma, o Visual Basic, irá ignorar as linha que iniciam com o caracter PLIC( ‘ ) executando somente as demais linhas APOEXCEL_AVANC 8 INSERINDO OBJETOS EM UM PLANILHA Para melhorar a utilização de seus aplicativos, o Excel oferece o recurso de controles e caixas de diálogos personalizadas que podem melhorar a apresentação e a forma como os dados são incluídos em suas planilhas. As Caixas de Diálogos imitam o que já estamos acostumados a utilizar no nosso dia-a-dia, quando abrimos ou gravamos um arquivo, quando pedimos para definir as características de impressão, quando utilizamos o item opções do menu Ferramentas e assim por diante. Quando criamos Objetos(controles) ou Caixas de Diálogos, podemos através do Visual Basic, definir como controlá-los através de procedimentos programáveis ou condições de execução. Por exemplo, podemos fazer com que uma macro seja executada dependendo do valor digitado em uma Célula ou Caixa de Edição. A figura abaixo lista alguns dos Objetos que podem ser criados no Excel e são exibidos na Barra de Ferramentas: Caixa Texto Caixa de Listagem Caixa de Combinação Botão Botão Alternar Botão de Opção Botão de Rotação Barra de Rolagem Rótulo Imagem Modo Estrutura Exibir Código Propriedades Caixa de Seleção Mais Controles APOEXCEL_AVANC 11 CRIANDO MÓDULOS Semelhante à Macro, o recurso de Módulo possui as mesmas funcionalidade de e recursos de uma programação de Macros. Então, a diferença básica é por conta do modo de execução. A Macro executamos pela Barra de Menus ou, então, associando-a a um Botão de Controle que inserimos em uma Planilha. O Módulo executamos através da própria célula do Excel e, se desejarmos, poderemos executar passo-a-passo as linhas programadas do código para identificarmos um eventual erro, ou então, verificar se as variáveis criadas estão armazenando os valores corretos. PROGRAMANDO MÓDULOS Iremos agora criar um Módulo que Calcula os dias úteis trabalhos para efeito de Horas Extras. 1. Na Barra de Menu Ferramentas, Macro, Editor Visual Basic; 2. No modo programável do VB, selecione na Barra de Menu Inserir, Módulo; 3. Na janela de Módulo digite o código conforme figura abaixo: 4. Utilizando a Planilha Dias Úteis, digite na célula H7 a função como fórmula digitando as colunas de Dia Início e Dia Fim para ver o resultado. APOEXCEL_AVANC 12 EXERCÍCIO DE MÓDULO 1. Baseado na figura abaixo crie um módulo que calcule o percentual de gastos em uma administração residencial. A base de cálculo será o salário. 2. Inclua uma coluna ao lado de percentual e utilize como base de cálculo o Total Geral. APOEXCEL_AVANC 13 TABELA DINÂMICA Na introdução vimos as vantagens de utilizar uma Tabela Dinâmica. Com interatividade podemos resolver problemas utilizando o Assistente numa sequência de 04 (quatro) caixas de diálogo interativas que nos guia pelas etapas de localização e recuperação dos dados que desejamos analisar. O Assistente de Tabela Dinâmica também permite que possamos definir como desejamos organizar uma Tabela Dinâmica utilizando rótulos de linha e de coluna, e como desejamos apresentar os dados nos campos. Podemos criar uma Tabela Dinâmica a partir de dados fonte que residam em: · Uma lista ou um banco de dados do MS-Excel. · Um banco de dados externo, como uma tabela ou arquivo criado em um aplicativo, ou sistema de gerenciamento de banco de dados externo ao MS-Excel, como por exemplo, MS-SQL Server, MS-Access, Oracle, Sybase etc. · Vários intervalos de consolidação de dados em planilhas do MS-Excel. · Outra Tabela Dinâmica na mesma pasta de trabalho. Uma vez criada a Tabela Dinâmica, você pode organizar, reorganizar e analisar os dados arrastando e soltando os campos na Tabela Dinâmica. Também é possível incluir subtotais, modificar a função de resumo de um campo, alterando a função de soma pela média, valor máximo, mínimo, ou selecionar cálculos personalizados para a Tabela Dinâmica. Observação: Se você deseja criar uma Tabela Dinâmica utilizando dados externos, utilize o Microsoft Query e os controladores da conectividade de banco de dados (ODBC - Open Database Connectivity) adequados e defina as fontes de dados que deseja utilizar. Neste artigo estaremos apresentando uma conexão com uma base de dados em MS-Access. A partir de agora iremos iniciar a confecção de uma Tabela Dinâmica. 1. Abra uma planilha nova no Excel; 2. Digite os dados da planilha conforme figura abaixo: APOEXCEL_AVANC 16 8. Arraste os campos relacionados no lado direito da tela para as posições indicadas conforme a Figura abaixo. Deste modo teremos criada a estrutura de Tabela Dinâmica. Feito isso pressione o botão Avançar; 9. A geração da Tabela Dinâmica poderá ser feita na mesma planilha que a origem de dados ou, então, ser gerada em nova planilha conforme Tela abaixo. Iremos selecionar a opção Nova Planilha na Caixa de Diálogo e pressionar o botão Concluir. APOEXCEL_AVANC 17 10. Definidos os padrões solicitados na tela anterior e pressionar o botão Concluir você verá o resultado apresentado como na figura abaixo: Você poderá redefinir a distribuição dos campos pela Tabela Dinâmica simplesmente arrastando o botão cinza indicativo do campo para o local desejado, invertendo posições de botões de campo de coluna com linha ou então de página. Deste modo podemos obter resultados diferentes com a mesma Tabela Dinâmica. A posição padrão de apresentação das informações dos campos de Dados da Tabela Dinâmica é na posição vertical, ou seja, um abaixo do outro. MENU TABELA DINÂMICA Iremos agora conhecer a funcionalidade das Ferramentas do Menu: Tabela Dinâmica: Possui resumidamente as Ferramentas da Barra de Menu Assistente da Tabela Dinâmica: Exibe o Assistente na Caixa de Diálogo de Lay-Out APOEXCEL_AVANC 18 Campo da Tabela Dinâmica: Define as funções de resumo usadas para calcular os valores no campo de dados selecionado. Você também pode criar um cálculo personalizado, ou alterar o nome do campo de dados. Insere um subtotal para um campo de linha ou um campo de coluna em uma Tabela dinâmica existente. Você também pode ocultar itens para que eles não sejam exibidos na Tabela dinâmica, renomear o campo, ou alterar a orientação do campo (de linha para coluna, por exemplo). Mostrar páginas: Copia cada página de um campo de página para uma nova planilha da pasta de trabalho atual. Você pode então passar para uma planilha diferente para imprimir ou para aprofundar a análise dos dados. Desagrupar: Separa um conjunto de itens agrupados. Cada ocorrência do grupo será substituída pelos itens nele contidos. Agrupar: Agrupa itens em uma Tabela dinâmica por categoria para criar um item único a partir de múltiplos itens. Este recurso poderá ser útil, por exemplo, quando você quiser agrupar meses em trimestres para fins de análise, elaboração de gráficos ou impressão. Ocultar detalhe: Oculta os dados de detalhe retraindo um item externo de um campo de linha ou de coluna em uma Tabela dinâmica. Este recurso poderá ser útil quando você quiser isolar segmentos de dados para análise, impressão ou elaboração de gráficos. Mostrar detalhe: Exibe os dados de detalhe que foram ocultados com o subcomando Ocultar detalhe, expandindo um campo de linha ou um campo de coluna em uma Tabela dinâmica. Este comando é equivalente a um clique duplo sobre um item cujo detalhe foi ocultado. APOEXCEL_AVANC 21 Após isso será exibida a Tela de Lay-out da Tabela Dinâmica conforme figura abaixo: Nossa modificação consistirá em colocar o Campo Nome do Produto no Lay- Out da Tabela Dinâmica na área de Linha conforme figura abaixo: APOEXCEL_AVANC 22 Efetivada a alteração na Tabela pressione o Botão Avançar. A Tabela Dinâmica passará a Ter a nova visualização conforme figura abaixo: Podemos reparar que agora a visualização da Tabela ficou mais completa pois, incluímos o Campo Nome do Produto. Além disso, a Tabela apresenta quebra por Nome do Cliente, facilitando a visualização de todos os pedidos feitos. APOEXCEL_AVANC 23 EXERCÍCIOS DE TABELA DINÂMICA 1. Baseado na Planilha montada do exemplo anterior, crie uma Tabela Dinâmica com uma Caixa de Listagem do campo Data. 2. Baseado na Planilha montada do exemplo anterior, crie uma Tabela Dinâmica com Caixa de Listagem dos Campos Data e Vendedor. Faça uma seleção de leitura por Colunas e perceba o novo Lay-Out aplicado. 3. Baseado na Planilha Tabela_Access monte uma Tabela Dinâmica que liste todas as ocorrências Motivo com quebra por Número de Loja. A Tabela deverá fazer o somatório de ocorrências por Loja.