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: [email protected]
Copyright © 2000-2001 Leonardo Breyer
3
I
Í N D I C E
INTRODUÇÃO ...1
CRIANDO MACROS...2
iniciando uma macro ...2
EXECUTANDO MACRO...3
PROGRAMANDO OBJETOS EM MACROS...5
Sintaxe de comandos...6
INSERINDO OBJETOS EM UM PLANILHA...8
CRIANDO MENUS PERSONALIZADOS...9
EXERCÍCIOS DE MACRO...10
CRIANDO MÓDULOS...11
PROGRAMANDO MÓDULOS...11
EXERCÍCIO DE MÓDULO...12
TABELA DINÂMICA ...13
MENU TABELA DINÂMICA...17
ALTERANDO UMA TABELA DINÂMICA...20
EXERCÍCIOS DE TABELA DINÂMICA...23
APÊNDICE ...24
programando teclas...24
LOOPS E CONDIÇÕES...24
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 4 Para executarmos a Macro criada seguiremos os seguintes passos:
1. Selecione na planilha a célula C16 e entre com o valor de 10%;
2. Selecione na Barra de Menu o comando Ferramentas, Macro, Macros...;
Será exibida a tela abaixo:
3. Selecione a nome da macro Crescimento e em seguida pressione o botão Executar
Perceba que a macro executa todas as tarefas gravadas anteriormente. Desta forma, toda vez que o usuário desta planilha tiver que verificar o crescimento do próximo mês, basta apenas digitar o valor de Crescimento e executar a macro.
APOEXCEL_AVANC 5
PROGRAMANDO OBJETOS EM MACROS
Um objeto é um controle do Visual Basic. Através dele poderemos definir que função terá em nossa planilha. Cada objeto possui características que o tornam útil ao usuário e podem ser configurados individualmente. Essas características são chamadas de propriedades que, controlam a aparência ou comportamento de um objeto. Além das propriedades, os objetos possuem também métodos ou eventos que, são ações que os objetos podem executar.
No Excel avançado você poderá utilizar códigos de programação para automação do seu trabalho. Abaixo alguns exemplos:
• Pasta de Trabalho → Workbooks
• Planilhas → Worksheets
• Intervalos → Ranges
• Gráficos → Charts
• Botões → Buttons
NOTA:
Quando programamos objetos em Visual Basic no Excel, temos que, obrigatóriamente, escrever os comandos em inglês, diferentemente de quando escrevemos fórmulas.
Os objetos no Microsoft Excel possuem propriedades. A seguir, como exemplo, listamos resumidamente algumas propriedades do objeto intervalo:
Propriedade Descrição
Columns Retorna a primeira coluna da primeira área do Intervalo
Fórmula Retorna a fórmula do intervalo, em notação de estilo A1
Height Retorna a altura de um intervalo em
pontos(1/72 de polegadas)
Retorno Automático de texto Determina se o texto deve retornar automaticamente dentro do intervalo
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)
