Excel Avançado
F.A. Consultoria e Treinamento
Rua General Jardim, 618 cj. 32
01223-010 - São Paulo - SP
Tel: (11) 3256-4706
Fax: (11) 3256-0666
www.faconsultoria.com
Todos os direitos reservados. Nenhuma parte desta publicação poderá ser
reproduzida, guardada por algum sistema de recuperação ou transmitida de
qualquer modo ou por qualquer outro meio, seja este eletrônico, mecânico, de
fotocópia, de gravação, ou outros, sem prévia autorização, por escrito, da F.A.
Consultoria e Treinamento.
Responsável: Fernando Andrade
Redação e organização: Fabíola Luz
Índice
Tabela Dinâmica.......................................................................................................... 1
Atualizando sua tabela dinâmica ................................................................................ 5
Alterando a fórmula que reúne os campos da tabela dinâmica ................................ 6
Trabalhando com um conjunto de hipóteses............................................................. 7
Trabalhando com dois conjuntos de hipóteses ......................................................... 9
Atingindo Metas......................................................................................................... 11
Atingindo metas em gráficos .................................................................................... 13
Cenários..................................................................................................................... 15
Trabalhando com cenários ....................................................................................... 17
Resumo de cenários.................................................................................................. 18
Solver ......................................................................................................................... 19
Adicionando mais restrições ao Solver .................................................................... 22
Auditoria..................................................................................................................... 24
Dependentes ............................................................................................................. 25
Erros........................................................................................................................... 25
Estrutura de tópicos, organização automática ......................................................... 26
Estrutura de tópicos, organização manual ............................................................... 27
Suplementos.............................................................................................................. 27
Consulta a banco de dados ...................................................................................... 28
Planilha com jeito de formulário................................................................................ 30
Macros ....................................................................................................................... 33
Tecla de atalho .......................................................................................................... 35
Atribuindo macros a um botão no menu .................................................................. 36
Atribuindo macros um comando no menu ............................................................... 37
Editando a macro ...................................................................................................... 38
Quadros de diálogo em macros ............................................................................... 39
Altere o valor de uma propriedade com uma macro................................................ 40
Respondendo perguntas e tomando decisões ........................................................ 41
Executando uma mesma instrução várias vezes (o Loop For Each)....................... 43
EXCEL AVANÇADO
Tabela Dinâmica
Você pode mostrar seus dados na forma de linha e coluna (como na figura
abaixo) ou você pode organizar seus dados e exibi-los por assunto (como na
figura acima). É o que faz uma tabela dinâmica. E ela é dinâmica porque você
pode alterar dinamicamente a representação dos dados.
Para ver como funciona uma tabela
dinâmica, vamos precisar de uma
tabela como esta ao lado.
1.
Digite sua planilha.
Dica: Esta planilha também é
chamada de banco de dados,
porque tem uma linha com
cabeçalhos de colunas e as outras
linhas são dados.
Para trabalhar com banco de
dados, você precisa mostrar ao
Excel que planilha usar como banco de dados. Você faz isso deixando o
cursor em qualquer célula preenchida.
2.
Clique em qualquer célula preenchida da planilha.
3.
Escolha Dados, Relatório de tabela e gráfico dinâmicos...
FA CONSULTORIA E TREINAMENTO
1
EXCEL AVANÇADO
A elaboração da tabela dinâmica é feita em 3 passos. No primeiro você
precisa identificar onde estão os dados: uma planilha, uma fonte de dados
externa (um banco de dados SQL, por exemplo) ou até mesmo outra tabela
dinâmica.
4.
Neste exemplo os dados estão
em uma planilha. Assinale a
primeira opção, Banco de
dados ou lista do Microsoft
Excel.
4
5
Você pode construir uma tabela
dinâmica propriamente dita ou
um gráfico dinâmico junto com
uma tabela dinâmica. É isto que
informa a parte inferior do
quadro de diálogos ao lado.
6
5.
Construiremos apenas uma tabela dinâmica, ative Tabela dinâmica.
6.
Clique em Avançar.
No passo 2 você deve selecionar os dados que serão usados para a
construção da tabela dinâmica. Como tivemos o cuidados de deixar o cursor
em uma célula preenchida da planilha usada como base, o assistente já
identifica todos os dados.
7.
8.
Selecione os dados que serão
usados como base para a tabela
dinâmica. Neste nosso exemplo
tais dados já foram
identificados.
8
Clique em Avançar.
No terceiro e último passo você
mostra onde será criada a tabela
dinâmica. No entanto, é preciso
antes definir um layout para
nossa tabela (que dado será
colocado na linha, que dado
será colocado na coluna etc).
9.
7
9
Clique no botão Layout.
FA CONSULTORIA E TREINAMENTO
2
EXCEL AVANÇADO
Dados
O que significam cada uma das informações que o assistente pede para você
preencher?
Dados: São os números que aparecerão na tabela dinâmica. Perceba que não
são exatamente os mesmos números que aparecem na planilha usada como
base, mas sim um cálculo feito com estes números.
Coluna: Contém as informações mais abrangentes da planilha. Neste exemplo
as colunas conterão o tipo de produto vendido (frutas/legumes ou laticínios).
Linha: Contém os detalhes. Na figura acima você vê o vendedor e o ano em
que foi feita a venda.
Página: Mostra as informações agrupadas por critérios mais amplos. No
nosso exemplo separamos nossos dados por região em que foi feita a venda.
10. Arraste para a área de Dados o
campo Vendas.
Perceba que o assistente já
agrupa as vendas somando cada
um dos valores. Em vez de somálos, você também pode calcular a
média, obter o valor máximo etc.
Veja como fazer isso nas páginas
seguintes.
11
13
12
10
11. Para Coluna arraste o campo Tipo.
12. Para Linha arraste primeiro o campo Vendedor e depois o campo Ano.
13. Para a Página arraste Região.
FA CONSULTORIA E TREINAMENTO
3
EXCEL AVANÇADO
Dica: Como você pôde ver, é possível colocar mais de um campo em cada
área.
14. Clique em OK para
finalizar a definição do
layout e depois clique
em Concluir para
finalizar o assistente.
Veja ao lado como ficou
nossa tabela dinâmica.
15. Os campos possuem
filtros para que
possamos escolher os
dados que serão
exibidos. Por exemplo,
para ver apenas os
dados de um ano
específico, clique na
seta do campo Ano e
deixe assinalado apenas
o ano desejado.
15
16. Em caso de querer retirar algum campo, clique sobre o campo (cinza) e
arraste-o para fora da tabela. Veja que aparece um X vermelho indicando que
o campo será eliminado quando você soltar o mouse.
17. Você também pode trocar os campos de lugar, basta arrastá-los para a nova
posição. Por exemplo, você pode retirar Ano da linha e colocá-lo na coluna. É
por isso que chamamos este recurso de Tabela Dinâmica.
FA CONSULTORIA E TREINAMENTO
4
EXCEL AVANÇADO
Atualizando sua tabela dinâmica
Embora a tabela seja dinâmica, sua atualização não é automática. E você
precisa tomar alguns cuidados. Por exemplo, se você inserir mais uma linha
no final do banco de dados original, ela não será considerada. Veja.
1.
Escreva Laticínios, 1994, 10000, Oliveira, Sul na última linha da tabela.
2.
Retorne para a tabela dinâmica e verifique: nada aconteceu, certo?
Você precisa informar o acréscimo desta última linha.
3.
Clique dentro da sua tabela dinâmica.
Aparece a barra de ferramentas de trabalho com tabelas dinâmicas
4.
Clique no botão Assistente da tabela dinâmica.
5.
Volte até o passo 2, aquele
passo onde você define o
intervalo a ser usado para a
tabela dinâmica.
6.
7.
Para alterar o Intervalo,
basta selecionar
novamente a tabela, agora
incluindo a nova linha.
6
7
4
Clique em Concluir para ver o resultado.
A tabela dinâmica também não é atualizada automaticamente se você alterar
os dados na tabela original. É você quem comanda esta atualização, veja.
8.
Mude um dos valores na tabela original.
9.
Para efetivar a alteração clique no botão Atualizar dados da barra de
ferramentas da tabela dinâmica.
FA CONSULTORIA E TREINAMENTO
5
EXCEL AVANÇADO
Alterando a fórmula que reúne os campos da tabela dinâmica
O assistente de tabela dinâmica
automaticamente soma os
valores dos campos que você
arrasta a região Dados. Mas é
possível definir outro tipo de
cálculo para eles.
1.
Clique em cima do campo Soma
de vendas na tabela dinâmica.
2.
Na barra de ferramentas que
aparece, clique no botão
Configurações de campo.
3.
Escolha o tipo de cálculo que você
deseja fazer. No exemplo mostrado na
figura ao lado escolhemos Média.
4.
Clique no botão OK.
FA CONSULTORIA E TREINAMENTO
A operação Soma é definida
automaticamente
3
6
EXCEL AVANÇADO
Trabalhando com um conjunto de hipóteses
Na planilha ao lado o salário líquido é de 1900
quando o Imposto é de 5%. Qual será o líquido se o
imposto for de 7%? E ser for de 10%? O Excel
permite a criação de uma tabela com todas estas
hipóteses. De forma simples e rápida!
1.
Prepare sua planilha conforme a ilustração ao lado.
2.
A fórmula da célula B4 é: =B32-B32*B33.
É hora então de avaliarmos outras hipóteses possíveis de impostos. Para isso
precisamos de 2 colunas, uma com os possíveis impostos e outra com os
valores correspondentes de salários líquidos.
3.
Selecione o intervalo de
célula B36:C46 para estas
colunas.
4.
Clique no menu Dados,
Tabela...
5
6
Aparece o quadro Tabela
para você preencher. Já
dissemos que a primeira
coluna selecionada será
usada para a digitação dos
vários impostos possíveis. Em que célula de nossa planilha de exemplo já
digitamos um valor específico de imposto? Claro, na célula B33.
5.
Passe esta informação ao Excel. Indique a célula B33 como a Célula de
entrada de coluna.
Por enquanto ainda não faremos referência à Célula de entrada da linha.
Você entenderá seu uso nas próximas páginas.
6.
Pronto, está tudo feito. Clique em OK para ver o resultado.
FA CONSULTORIA E TREINAMENTO
7
EXCEL AVANÇADO
Provavelmente você não está gostando do resultado, sua planilha deve estar
cheia de 0’s. Isto acontece porque você ainda não indicou qualquer tipo de
cálculo na tabela de hipóteses. E qual fórmula seria essa?
Vamos pensar? O que estamos procurando aqui? Queremos ver os vários
salários líquidos possíveis para vários impostos possíveis. Em qual célula de
nossa planilha de exemplo já digitamos a fórmula que calcula salário líquido?
Claro, na célula B34.
Por outro lado, em nossa tabela de hipóteses os vários salários líquidos
aparecem na segunda coluna (coluna C neste exemplo). Para que o recurso
de tabela do Excel funcione, você deve indicar no alto desta coluna a fórmula
de cálculo.
7.
Então, na célula C36, indique que a fórmula
encontra-se na célula B34. Para isso digite
a fórmula (atenção) =B34.
8.
Na célula B37 digite 1%, na B38 digite 2% e
assim sucessivamente até os 10% - para
termos uma variação.
7
8
Pronto! Agora você tem os valores e as
possíveis hipóteses.
Dica: Não perca seu tempo digitando todas as
porcentagens na coluna B. Use o recurso de
preenchimento automático do Excel. Digite apenas
1% e 2% nas duas primeiras células. Selecione-as.
Posicione o cursor no canto inferior direito da
segunda célula e arraste-o até o cursor indicar 10%.
FA CONSULTORIA E TREINAMENTO
8
EXCEL AVANÇADO
Trabalhando com dois conjuntos de hipóteses
Na página anterior você viu como trabalhar com uma única hipótese (a variação
do imposto). O Excel também permite a variação de duas hipóteses ao mesmo
tempo – e esta é uma situação muito comum.
Vamos aproveitar a mesma planilha da página
anterior e adaptá-la para duas hipóteses. Para isso,
vamos inserir uma nova linha com Outros
impostos, conforme mostra a figura ao lado..
1.
Para incluir Outros impostos no cálculo, digite a
seguinte fórmula na célula B35 =B32-B32*B33-B32*B34.
Como você vê, esta fórmula desconta do salário líquido a parcela do Imposto
de renda (B32*B33) e a parcela dos Outros impostos (B32*B34).
Na página anterior você selecionou 2 colunas para a tabela de hipóteses: uma
coluna para as hipóteses (o imposto) e outra coluna para os resultados. Agora
sua seleção será maior, deve incluir dois tipos de hipóteses – o Imposto de
renda e os Outros impostos.
2.
Selecione a área da tabela de hipóteses (B37:G47).
3.
Clique no menu Dados,
Tabelas...
Veja a área selecionada na
figura ao lado. A primeira
coluna continua sendo o
local onde aparecerão os
vários Impostos de renda
possíveis. Já na primeira
linha estaremos
informando as hipóteses
para os Outros impostos.
5
4
4.
Então, na Célula de entrada da coluna, informe onde o valor do Imposto de
renda aparece na fórmula original: B33.
5.
Na Célula de entrada da linha informe onde aparece o valor dos Outros
impostos na fórmula original: B34.
FA CONSULTORIA E TREINAMENTO
9
EXCEL AVANÇADO
6.
Clique em OK.
Não se preocupe, sua tabela
deva estar cheia de 0’s. É que
ainda não informamos o que
será usado nos cálculos.
Para calcular o salário líquido
nesta tabela, repita na célula
B37 (o cruzamento entre a
primeira linha e a primeira
coluna) a fórmula que faz este cálculo na planilha original (que está em B35).
Claro, você não precisa montar a fórmula de novo, é só indicar em que célula
está esta fórmula.
7.
Clique na célula B37 e digite a fórmula =B35.
8.
Para informar as várias hipóteses para o Imposto de renda, preencha as
células B38 a B47 com valores iniciando em 1% e terminando em 10%, com
incrementos de 1% (confira na figura abaixo).
9.
Para informar as várias hipóteses para os Outros impostos, preencha as
células C37 a G37 com valores iniciando em 20% e terminando em 40%, com
incrementos de 5% (confira na figura abaixo).
A tabela é preenchida
automaticamente
enquanto você digita
as informações
necessárias. Assim
fica muito fácil
analisar o quanto o
Imposto de renda e os
Outros impostos
impactam no Salário
líquido, concorda?
FA CONSULTORIA E TREINAMENTO
10
EXCEL AVANÇADO
Atingindo Metas
Podemos dizer que o recurso Atingir Meta faz cálculos de trás para frente. Em
outras palavras, você já sabe qual deve ser o resultado final de um cálculo e
precisa que o Excel diga quais devem ser os valores para isso. É um recurso
importantíssimo para quem trabalha muito com planilhas.
Vamos usar a mesma planilha anterior para
exemplificar. A pergunta a responder com o Atingir
Meta aqui é: quanto deve ser o Salário Bruto para
que o Salário Líquido seja igual a R$ 1.500,00?
Perceba que assim você já define qual deve ser o
resultado do cálculo do Salário Líquido. Cabe ao Excel dizer quanto deve ser o
Salário Bruto.
1.
Selecione a célula B35 (aquela que contém o Salário Líquido) e escolha os
comandos Ferramentas, Atingir metas...
Aparece o quadro Atingir meta com algumas linhas que você deve preencher:
Definir célula: é a célula
que contém o cálculo final
(Salário Líquido neste
nosso exemplo).
Para valor: é o valor
desejado pela o cálculo.
Variando célula: é a célula
cujo valor o Excel deve
alterar para que o cálculo
mostre o valor desejado (Salário Bruto neste nosso exemplo).
2
Comentário: A célula que atingirá a meta deverá ser obrigatoriamente uma
fórmula e a célula variável deve ser um valor e não uma fórmula.
2.
Preencha o quadro Atingir meta conforme mostra a figura acima: Definir
célula:B35, Para valor: 1500, Variando célula: B32.
Dica: Você não precisa digitar os endereços das células, basta clicar sobre
elas.
3.
Clique em OK.
FA CONSULTORIA E TREINAMENTO
11
EXCEL AVANÇADO
O Atingir
meta
encontra
uma
solução, ele
informa que
o Salário
Bruto deve
ser de R$
2.142,86
para que o Salário Líquido seja de R$ 1.500,00.
4.
Para aceitar o valor proposto, clique em OK. Para voltar ao valor original clique
em Cancelar.
Comentário: O Atingir meta varia uma célula de cada vez. Se você quiser
variar mais de uma célula use o Solver, recurso que será explicado mais à
frente nesta planilha.
FA CONSULTORIA E TREINAMENTO
12
EXCEL AVANÇADO
Atingindo metas em gráficos
Você também pode aproveitar o recurso Atingir meta a partir de gráficos. É
muito mais visual, facilita o entendimento!
Para preparar um gráfico, precisamos de uma planilha.
1.
Faça
uma
planilha
igual a
esta ao
lado.
2.
A coluna E3 (Total) é calculada (Quantidade * Valor Unit). Não digite estes
valores, faça a fórmula correspondente, senão o Atingir meta não funciona.
Vamos fazer um gráfico que mostre os vendedores, o total de vendas e a meta
a ser atingida.
3.
Para isso, selecione os intervalos A2:A8, pressione o Ctrl e selecione os
intervalos de E2:F8.
4.
Clique no botão Assistente de gráfico na
barra de ferramentas do Excel
5.
Clique na guia Tipos personalizados e
logo escolha o gráfico Linhas-Colunas.
Queremos um gráfico simples e padrão,
não precisamos passar por cada um dos
passos que o Assistente de gráfico
oferece.
6.
5
6
Então, para concluir agora o gráfico, clique
no botão Concluir.
Pronto! O gráfico aparece em sua tela. Agora que você está vendo o gráfico
pronto (veja a ilustração na próxima página), fica mais fácil entender porque
fizemos uma coluna inteira só para as metas. Elas são representadas por uma
linha no gráfico, que facilita quem atingiu as metas ou não.
FA CONSULTORIA E TREINAMENTO
13
EXCEL AVANÇADO
Vamos
então usar
os gráficos
para
calibrar as
metas de
cada
vendedor.
No gráfico
ao lado você vê que o Henrique não atingiu a meta. Vamos trabalhar com sua
barra no gráfico.
7.
Clique na barra correspondente ao Henrique.
Todas as barras são marcadas.
8.
Para marcar só a do Henrique, clique novamente em Henrique.
Agora sim
podemos
trabalhar
com as
metas
deste
vendedor.
9.
Perceba
que há um ponto maior no alto desta barra. Clique sobre ele e arraste-o para
cima, até encostá-lo na linha dos 100.
Veja que enquanto você faz isso o Excel
sinaliza as vendas representadas pelo novo
tamanho da barra do gráfico. Em outras
palavras, enquanto você arrasta a barra do
Henrique para cima, você está sinalizando
qual deve ser o novo valor de vendas.
Veja
10. Quando o Excel sinalizar 100, solte o mouse.
Aparece o quadro Atingir meta, que você já
conhece. Daqui para frente é só repetir o
processo que você aprendeu nas páginas
anteriores.
FA CONSULTORIA E TREINAMENTO
14
EXCEL AVANÇADO
Cenários
Em uma planilha muitas vezes você precisa mudar valores de algumas células e
ver os resultados correspondentes. Cada mudança traz uma resposta diferente.
O Excel pode controlar estas mudanças para você, guardando cada alteração
em um cenário. Assim você pode exibir qualquer cenário a qualquer instante –
sem precisar digitar tudo de novo – e ainda ter de volta sua planilha original.
Para ver como o Excel trabalha com cenários, nada melhor do que uma
planilha de aplicações financeiras – como esta que você vê a seguir.
1.
Digite a seguinte
planilha e faça os
devidos cálculos.
Veja que a coluna C
(Aplicado) e a coluna
F (Retorno) são colunas calculadas. Não
digite estes valores, senão o recurso de
cenários não funciona, você deve calcular
estes valores. Pergunte ao professor como
fazer estes cálculos.
2.
3
Clique no menu Ferramentas, Cenários...
Aparece o quadro com o Gerenciador de
cenários. Como não existe ainda qualquer
cenário gravado, o quadro aparece vazio. É
hora de adicionar um cenário.
3.
Clique no botão Adicionar para adicionar
um novo cenário.
4.
Todo cenário tem um nome. No primeiro
campo, coloque um nome para este
cenário. Neste exemplo estamos chamando
nosso cenário de aplicação.
5.
Um cenário serve para guardar valores
diferentes para determinadas células. São
as Células variáveis. Neste nosso exemplo
estaremos variando os valores das células
B2 a B5.
FA CONSULTORIA E TREINAMENTO
4
5
6
15
EXCEL AVANÇADO
6.
Em Comentários digite uma descrição que melhor descreva seu cenário.
7.
Clique em OK.
Aparece uma quadro com os valores já
digitados originalmente nas células. Não
há o que fazer aqui, até porque este
primeiro cenário será um retrato de sua
planilha original, sem qualquer tipo de
alteração.
8
É hora então de adicionarmos outros cenários. Agora, por exemplo, vamos
digitar outros valores para as aplicações.
8.
Clique no botão Adicionar.
Aparece novamente o quadro de cenários
para você informar como será o novo cenário.
Vamos como exemplo digitar um valor maior
para a aplicação em CDB.
9.
Por isso chame o novo cenário de CDB.
9
10
11
10. As Células variáveis continuam as mesmas,
mantenha o intervalo como B2:B5.
11. Digite um comentário para este novo cenário.
12. Clique em OK.
13. Novamente aparece o quadro Valores
de cenário. Você precisa agora digitar
novos valores para cada célula variável
neste novo cenário.
Lembrete: Chamamos este cenário de
CDB porque a maior porcentagem agora
seria aplicada em CDB (célula B3). Na figura acima definimos 0,40 para B3 por
este motivo.
Atenção: Neste exemplo específico estamos distribuindo as porcentagens de
um capital que será aplicado. Assim, a soma das porcentagens indicadas no
quadro acima tem quer ser 100% (ou 1). O Excel não controla isso, este
cuidado é você que tem que ter.
FA CONSULTORIA E TREINAMENTO
16
EXCEL AVANÇADO
Daqui para frente você deve continuar
construindo cenários conforme sua
necessidade. A figura ao lado mostra um
exemplo com quatro cenários diferentes.
Se quiser, construa você também mais
alguns cenários para praticar. Não se
esqueça: neste exemplo específico em que
estamos trabalho os cenários dizem respeito
à distribuição de um valor em várias
aplicações diferentes. Por isso, ao variar os
valores das células, não se esqueça de que a
soma tem que ser igual a 100%.
Trabalhando com cenários
Agora que temos estes vários cenários, podemos exibir cada um deles
facilmente.
1.
2.
Para fazer aparecer os valores que salvamos
nos cenários, basta exibir o Gerenciador de
cenários. Para isso clique no menu
Ferramentas, Cenários...
2
Selecione o cenário a exibir e clique no botão
Mostrar.
Dica 1: Caso exista mais cenários em outras
planilhas do mesmo arquivo, podemos
mesclá-los..
Dica 2: Para alterar um cenário, selecione-o e
clique no botão Editar.
FA CONSULTORIA E TREINAMENTO
17
EXCEL AVANÇADO
Resumo de cenários
Esta é a melhor parte do trabalho com cenários. Já que você tem cenários
diferentes para várias situações, exibi-los ao mesmo tempo pode ser bastante
esclarecedor.
1.
Clique no menu Ferramentas, Cenários...
2.
Clique no botão Resumir.
Aparece o quadro Resumo do cenário, onde você define que tipo de resumo
quer fazer.
3.
Queremos apenas um
Resumo de cenário.
Assinale esta opção.
3
Veja que o Excel percebeu
que todos os cenários
contribuem para o cálculo
de uma mesma célula, F6. É
nela que teremos o retorno de nossa aplicação.
4.
Assim, mantenha a Célula de resultado como F6.
5.
Clique em OK.
4
O Resumo do
cenário
aparece em
uma nova
planilha.
A grande
vantagem de
ver tudo ao
mesmo tempo é a possibilidade de comparar os vários cenários. Na figura
acima você vê que o cenário que proporciona a maior rentabilidade é o
Poupança.
Dica: A planilha Resumo do cenário é uma planilha normal. Você pode digitar
o que quiser nas células. Assim, é uma boa idéia mudar os nomes $B$2 para
Ações, $B$3 para CDB e assim por diante.
FA CONSULTORIA E TREINAMENTO
18
EXCEL AVANÇADO
Solver
Você já aprendeu a trabalhar com o Atingir metas do Excel nas páginas
anteriores. Lá você alterava o valor de 1 ou 2 células para que a fórmula final
chegasse a um número específico. Se você precisar mexer em mais de 2
células, o Solver é a solução.
Vamos a uma
aplicação
prática do
Solver. Olhe a
planilha ao lado:
aplicamos R$
10.000,00 e o retorno foi de 10.418,78 para 30% em Ações, 25% em CDB, 25%
em Poupança e 20% em Commodities.
E se quiséssemos um valor final de R$ 11.000,00? Qual deveria ser a
distribuição do capital aplicado (em ações, CDB etc) para que isso
acontecesse?
1.
Clique no menu
Ferramentas, Solver...
Dica: Se o Solver não
aparecer no menu
Ferramentas, você pode
adicioná-lo. É simples:
escolha Ferramentas,
Suplementos e assinale a
opção Solver.
2
3
4
5
2.
A Célula de destino é aquela que mostra o retorno final do investimento.
Nesse exemplo é a célula F6.
3.
Indique ao Solver que para esta célula você quer um Valor de 11000.
4.
Queremos que o Solver sugira uma nova distribuição para a aplicação.
Indique então quais são Células variáveis: B2:B5.
Para oferecer uma solução, o Solver oferece uma solução matematicamente
possível. Mas nem sempre esta é a melhor solução. Por exemplo, ele poderia
sugerir que você aplicasse 120% em poupança – o que não é possível! É por
isso que você precisa adicionar restrições aos valores que o Solver sugere.
FA CONSULTORIA E TREINAMENTO
19
EXCEL AVANÇADO
Como exemplo, vamos imaginar uma situação muito próxima do mundo real.
Não é recomendado aplicar grande parte de um capital apenas em ações – é
muito arriscado. Então, vamos pedir ao Solver para não aplicar mais do que
50% em ações.
5.
Para definir uma restrição clique no botão Adicionar que aparece no quadro
Submeter às restrições.
Aparece o quadro Adicionar restrição. A célula que contém a porcentagem
de aplicação em ações é a B2. Vamos adicionar a restrição desejada.
6.
Indique a célula B2 em
Referência de célula.
7.
Esta célula deve ser
sempre <= a valor
específico.
8.
O valor para a Restrição é
de 50%.
9.
7
8
6
10
Clique em OK.
Veja: O quadro Submeter
às restrições mostra as
restrições definidas (uma
só por enquanto neste
exemplo).
Veja
Dica: Você pode adicionar
uma mesma restrição para
várias células. Por exemplo,
se você quisesse aplicar pelo
menos 10% em cada tipo de
aplicação, seria só definir
B2:B5>=10%, tal como mostra a figura ao lado.
10. Finalmente, para resolver o problema, clique em Resolver.
FA CONSULTORIA E TREINAMENTO
20
EXCEL AVANÇADO
O Solver encontrou
uma solução,
conforme informe o
12
quadro ao lado. Você
pode aceitar as
sugestões do Solver
(Manter solução do
Solver) ou descartá-las (Restaurar valores originais).
Veja
11
Veja, além disso o Solver pode gerar alguns relatórios, mostrando quais os
critérios usados para chegar às soluções propostas. O primeiro relatório,
Resposta, é o mais interessante: mostra como o Solver mudou os dados da
planilha para chegar à resposta que você queria.
11. Selecione o relatório Resposta.
12. Deixe assinalada a opção Manter soluções do Solver e clique em OK.
Os dados
em sua
planilha
foram
mudados e
uma nova
planilha,
Relatório de
resposta, é
criada
mostrando
como o
Solver
mudou os
dados.
FA CONSULTORIA E TREINAMENTO
21
EXCEL AVANÇADO
Adicionando mais restrições ao Solver
Comentamos
nas páginas
anteriores que
o Solver
oferece
soluções
matemáticas.
E nem sempre
elas são
satisfatórias.
Na planilha acima você pode perceber melhor o problema. Se você somar as
porcentagens de aplicação que o Solver sugeriu, verá que a soma delas é
maior do que 100% - e isso não é possível. Veja, na célula B6 acima somamos
as porcentagens, o total é de R$ 106%.
A solução aqui é refazer o Solver adicionando uma nova restrição: a célula
B6, que contém a somatória das porcentagens, deve obrigatoriamente ter um
valor de 100%.
1.
Antes de entrar no Solver, crie em B6 uma fórmula que some as células B2
até B5.
2.
Inicie o Solver novamente, clique no menu Ferramentas, Solver...
Repare que o
Excel se lembra do
último Solver
realizado.
3.
Para adicionar
uma nova
restrição, você já
sabe, basta clicar
em Adicionar.
4.
Informe que a célula B6 deve
ser igual a 100% .
5.
Clique em OK.
FA CONSULTORIA E TREINAMENTO
3
4
22
EXCEL AVANÇADO
6.
Agora que você tem
a nova restrição, é
só clicar em
Resolver
novamente.
7.
Quando aparecer a
mensagem que o
Solver encontrou
uma resposta,
escolha Manter soluções do Solver e clique em OK.
6
Quanto mais
restrições você
adicionar, mais
próximo da resposta
desejada você
chega. O que não
foi o caso neste
exemplo ainda. Veja
que, para respeitar o limite de 100% para a célula B6 o Solver atribuir valores
negativos para algumas porcentagens. O que certamente não é possível!
Deixamos este
exemplo assim
nesta apostila para
você ver como as
restrições são
importantes. Você
deve também
restringir valores
negativos nas
células. Na verdade, as melhores restrições para este exemplo são estas
mostradas na ilustração acima:
- as células variáveis devem ser no máximo de 50% (para forçar o Solver a
distribuir o capital entre as aplicações): $B2:$B5 <= 50%
- as células variáveis não devem ser negativas: $B2:$B5 >= 0%
- o total aplicado não pode ultrapassar 100%: $B$6=100%
Importante: neste exemplo, restrições como esta são matematicamente
impossíveis de atender. Por isso acrescentamos a célula E9 às células
variáveis. Assim informamos ao Solver que ele também pode alterar um
pouco o capital inicial investido. É o único jeito!
FA CONSULTORIA E TREINAMENTO
23
EXCEL AVANÇADO
Auditoria
Quando você precisa ver quais as células
usadas em uma fórmula, o recurso Auditoria
é ótimo. Ele indica tais células, como você vê
na figura ao lado. Além disso, pode ser muito
bom exibir tais setas em um telão: fica mais
fácil explicar os cálculos de sua planilha.
1.
Abra uma planilha e clique sobre uma fórmula qualquer.
2.
Clique no menu Ferramentas,
Auditoria, Rastrear
precedentes.
No caso da planilha ao lado, o
valor que precede esta fórmula
é a porcentagem e o preço da
cesta. Em outras palavras, a
fórmula em Valor Total
Corrigido usa valores digitados
em Preço Unitário e Reajuste Mensal.
Veja: Uma seta azul vinda de duas direções foi adicionada a sua planilha.
3.
Faça isso com todas as fórmulas e notará que várias setas azuis serão
colocadas em sua planilha.
Em vez de escolher comandos (como você fez no passo 2 acima), pode ser
muito mais fácil trabalhar com a barra de ferramenta de auditoria.
4.
Para exibi-la, clique no menu Ferramentas, Auditoria, Mostrar barra de
ferramentas de auditoria.
Aparece a barra com vários botões
relacionados à auditoria. Passe o mouse
sobre este botões para ver o que eles fazem, é tudo muito intuitivo.
5.
Veja o funcionamento de um deles: clique no botão Remover todas as setas.
Como o nome do botão indica, todas as setas foram removidas.
FA CONSULTORIA E TREINAMENTO
24
EXCEL AVANÇADO
Dependentes
Assim como os precedentes, existem fórmulas que são dependentes, ou seja,
que geram outros valores. Veja
na planilha.
1.
Selecione uma fórmula e clique
no botão Rastrear dependentes.
Aparecem setas indicando que
células dependem da fórmula
selecionada.
2.
Se quiser removê-las, utilize o
botão Remover setas
dependentes.
Dica: Se preferir, você pode remover todas as setas (dependentes e
precedentes) de uma só vez. Clique no botão Remover todas as setas
na barra de auditoria.
Erros
Muitas vezes aparecem alguns erros em sua planilha. O Excel até os indica
com uma expressão que começa com um #. Com o recurso de auditoria, o
Excel também pode rastrear as possíveis células que contribuem para tais
erros.
1.
Para isso, selecione a fórmula com erro e clique no botão Rastrear
erro.
Agora que você já sabe onde
estão as possíveis células que
permitem a correção do erro, é
só corrigir. Claro, depois você
certamente irá querer remover
estas setas.
2.
Para removê-las, você já sabe,
basta clicar no botão Remover
todas as setas.
FA CONSULTORIA E TREINAMENTO
25
EXCEL AVANÇADO
Estrutura de tópicos, organização automática
Veja as duas planilhas acima. Qual é
melhor: aquela que mostra todas as
informações ou aquela que mostra
apenas os totais? Depende da utilização,
não é? A grande notícia é que o Excel
pode organizar a planilha da esquerda (a
maior) em tópicos e exibi-la como a
planilha da direita (a menor) – e tudo
automaticamente!
1.
Clique em uma célula preenchida qualquer da planilha que você quer
organizar e escolha Dados, Organizar estrutura de tópicos, Agrupar.
2.
Escolha AutoTópicos no menu que aparece.
4
Aparece uma barra lateral
esquerda em sua planilha.
3.
Para esconder os detalhes da
Loja 1 e deixar apenas o total
de unidades vendidas, clique
no sinal - à esquerda da linha
de total (linha 4).
5
3
4.
Para esconder todos os
detalhes e deixar visíveis
apenas as linhas com totais,
clique no número 1 que
aparece nas colunas à
esquerda em sua tela.
5.
Para ver tudo novamente, clique no número 2 nestas mesmas colunas.
6.
Se você não quiser mais sua planilha organizada em tópicos, escolha Dados,
Organizar estrutura de tópicos, Limpar estrutura de tópicos.
FA CONSULTORIA E TREINAMENTO
26
EXCEL AVANÇADO
Estrutura de tópicos, organização manual
Muitas vezes você não precisa agrupar
automaticamente os dados de uma planilha – até
porque o conceito nem se encaixa. Veja a figura ao
lado. Vamos imaginar que por algum motivo você não
queira mais ver os produtos alimentícios. A estrutura de
tópicos do Excel também vai ajudar aqui, veja.
1.
Selecione as linhas que você não quer mais ver.
2.
Clique no menu Dados, Organizar estrutura de tópicos, Agrupar.
Aparecem as colunas à esquerda que você já viu
na página anterior.
3.
Agora você já sabe, para ocultar as linhas
desejadas, basta clicar no sinal - que aparece
nestas colunas.
Suplementos
Na página que abordamos o Solver comentamos que nem sempre ele está
instalado em seu Excel. Lá na dica dissemos que o Solver é um suplemento
do Excel, e que você pode instalá-lo rapidamente. Além dele, o Excel tem
vários outros suplementos que podem ser bastante úteis em seu trabalho.
1.
Clique no menu Ferramentas,
Suplementos.
O quadro que se abre mostra os
suplementos disponíveis.
Não vamos nos estender neste
assunto aqui, marcamos na figura
ao lado apenas os suplementos
mais úteis: AutoSalvamento, que
habilita o salvamento automático de seus arquivos em intervalos regulares
(que você define), as Ferramentas de análise, que trazem mais funções para
o Excel além daquelas tradicionais e o Solver, que você já conhece.
FA CONSULTORIA E TREINAMENTO
27
EXCEL AVANÇADO
Consulta a banco de dados
Muitas vezes você tem um banco de dados externo e precisa trabalhar com ele.
Com o Excel, é muito fácil extrair deste banco as informações necessárias ao seu
trabalho.
1.
O primeiro passo é localizar este banco de dados. Clique no menu Dados,
Obter dados externos, Criar uma nova consulta ao banco de dados.
2.
Escolha o banco de dados.
Neste exemplo estamos
escolhendo um banco de
dados Access.
3.
Clique em OK.
4.
Aparece a janela para você
selecionar seu banco de
dados. Selecione-o e clique
em OK.
2
4
Entra em cena o Assistente de
consulta, muito útil para a
construção da consulta
desejada.
Um banco de dados Access
tem várias tabelas, você está
vendo agora as tabelas e
colunas correspondentes
existentes no banco
escolhido.
5.
6.
Selecione as tabelas e
colunas desejadas e clique no
botão > para inseri-las na
consulta que você está
construindo com a ajuda do Assistente.
5
6
Clique em Avançar.
FA CONSULTORIA E TREINAMENTO
28
EXCEL AVANÇADO
Se quiser, você pode filtrar os
dados que serão trazidos para
o Excel. Por exemplo, apenas
quem morar na cidade de São
Paulo, por exemplo. Para não
alongar muito este exemplo,
não vamos fazer qualquer
filtragem.
7
7.
Clique em Avançar.
Por último, você ainda tem a
chance de organizar os dados
em ordem crescente ou
decrescente. De novo, não
vamos nos preocupar com
este assunto aqui.
8.
Clique em Avançar.
8
Por fim o Assistente pergunta
onde você quer colocar estes
dados.
9
9.
Claro, queremos que os
dados apareçam em nossa
planilha. Mantenha assinalada
a opção Retornar dados ao
Microsoft Excel.
10
10. Para terminar, clique em claro - Concluir.
11. Por último - agora é para terminar
mesmo, você só precisa informar
em que célula inicial os dados serão
inseridos. Faça isso e finalmente
clique em OK para terminar.
FA CONSULTORIA E TREINAMENTO
11
29
EXCEL AVANÇADO
Planilha com jeito de formulário
A planilha ao lado lembra um
formulário. E funciona como um!
Para descobrir o valor da
parcela a pagar por um carro,
basta você rolar as setas e
escolher o carro, definir as
parcelas e estabelecer a
porcentagem de entrada.
Vamos construir um formulário simples, tal como este que aparece na figura
acima.
Comentário: para facilitar a explicação, estamos admitindo que as parcelas a
pagar pelo carro são calculadas sem levar em conta eventuais juros.
1.
Para que estes botões funcionem, precisamos de uma
relação de itens digitada. Então, digite esta seqüência
mostrada na planilha ao lado.
2.
Há uma barra de ferramentas específica para a criação
de formulários. Para exibi-la, clique no menu Exibir, Barra de ferramentas,
Formulários.
Vamos colocar em nossa
planilha uma caixa de combinação tal como esta que aparece na linha 1 da
figura acima.
3.
Para desenhá-la, clique no botão Caixa de combinação.
4.
Desenhe a caixa de combinação na região da célula C3, conforme mostra a
figura acima.
Para que esta caixa de combinação mostre o nome de um carro, precisamos
vinculá-la aos nomes já digitados no passo 1 (lembra-se?).
5.
Clique sobre a caixa de combinação com o botão direito do mouse e escolha
Formatar controle no menu que aparece.
FA CONSULTORIA E TREINAMENTO
30
EXCEL AVANÇADO
6.
Na guia Controle que
aparece, clique no
campo Intervalo de
entrada e indique as
células onde
aparecem os nomes
dos carros: J8:J12.
Pronto, só isso já faz
o nome do carro
aparecer na caixa de
combinação. Mas
nosso objetivo é
exibir o nome do
carro e também seu
preço. É para isso
que serve a caixa
Vínculo da célula. É
na célula aí indicada que mostramos qual é a posição do carro na lista. Esta
posição será usada depois na função Índice.
7.
Clique no campo Vínculo da célula e indique a célula J1 (uma célula de
apoio, ele nem precisa ser exibida). Clique em OK quando terminar.
Dica: A caixa Linhas suspensas define quantos carros aparecem na lista.
O grande truque agora é aproveitar o número que será
colocado na célula J1 e assim descobrir o preço do
carro. É um trabalho para a função Índice.
8.
Para a função Índice funcionar melhor, vamos primeiro
atribuir um nome para as células K7:K12.
Selecione estas células e escolha os comandos
Inserir, Nome, Criar.
A opção Linha superior já vem assinalada.
Desta forma a palavra que aparece na linha
superior, Valor, será usada como nome para as
células todas.
9.
Clique em OK para chamar as células de Valor.
10. Pronto, agora é só digitar a função: =ÍNDICE(Valor;J1).
FA CONSULTORIA E TREINAMENTO
31
EXCEL AVANÇADO
Esta função olha a lista Valor e pega o valor cuja posição está indicada na
célula J1. Em outras palavras, se na lista aparece o Vectra (ele é o 4º. da lista),
a função Índice mostra o 4º. preço da lista, R$ 30.000,00.
Para exibir as parcelas mostradas no exemplo no início deste assunto,
você deve usar o botão Controle giratório.
11. Desenhe o botão na linha 2 tal como
aparece na figura ao lado.
12. Pressione o botão direito do mouse sobre
o Controle giratório, escolha Formatar
controle e configure o quadro tal como
mostra a ilustração ao lado.
12
De acordo com nosso exemplo do início
deste assunto, o último botão que
falta agora é o Barra de rolagem.
13. Desenhe o botão na linha 3
tal como aparece na figura
ao lado.
14
14. Pressione o botão direito do mouse sobre
a Barra de rolagem, escolha Formatar
controle e configure o quadro tal como
mostra a ilustração ao lado.
Veja que aqui também usamos uma célula
de apoio, J3. Ela nem aparece em nossa
planilha, mas será muito útil. Veja: a linha
3 exibe uma porcentagem de entrada.
Ora, o que temos a fazer agora é dividir o valor da célula J3 por 100 e formatála como porcentagem.
15. Na célula B3 digite a fórmula =J3/100
e a formate como porcentagem.
Pronto, seu formulário está pronto.
Agora você só precisa completar os
outros cálculos conforme ilustração ao
lado. São cálculos simples que não
serão abordados nesta apostila.
FA CONSULTORIA E TREINAMENTO
32
EXCEL AVANÇADO
Macros
Uma macro nada mais é do que uma série de trabalhos repetitivos reunidos.
Para executar todos estes trabalhos de uma só vez, você executa sua macro.
Depois de pronta, você pode vincular sua macro a um botão, tornando suas
planilhas mais inteligentes e mais práticas.
Vamos gravar uma macro que faz uma formatação simples.
1.
2.
Clique no menu Ferramentas, Macro,
Gravar nova macro...
No quadro Gravar macro que parece,
digite o Nome da macro.
3.
Faça uma breve descrição da macro.
4.
Clique em OK.
2
3
Atenção, a partir de agora o Gravador de Macros está ligado. Tudo o que você
fizer na planilha está sendo gravado. Tome muito cuidado. Veja que também
aparece uma barra com dois botões: Parar gravação e
Referencia Relativa. Eles são muito importantes no trabalho com
macros e serão discutidos mais adiante.
5.
Vamos fazer alguma coisa para o gravador de macros registrar: digite seu
nome completo na célula A1. Pressione Enter quando terminar
6.
Volte para A1 e formate seu nome como negrito, fonte Arial, tamanho 18, cor
vermelha. Pressione Enter para sair da célula formatada.
7.
Clique no botão Parar gravação.
8.
Vamos testar o funcionamento da macro.
Deixe o cursor em outra célula qualquer,
diferente daquela onde estava o cursor
quando você gravou a macro.
9.
Clique no menu Ferramentas, Macro,
Macros...
9
10. Selecione a macro feita e clique em Executar.
FA CONSULTORIA E TREINAMENTO
33
EXCEL AVANÇADO
Se você seguiu rigorosamente os passos sugeridos até aqui, é bem provável
que sua macro não tenha funcionado. Seu nome até deve ter aparecido nesta
nova célula, mas ele não foi formatado.
Isto acontece porque você gravou a macro no modo de referência absoluta. A
macro só funciona quando o cursor estiver na mesma célula em que estava
quando a macro foi gravada. O modo de referência absoluta é o modo normal
do gravador de macros. Mas podemos alterá-lo: neste nosso exemplo, a
macro vai funcionar como esperado se fizermos a gravação no modo de
referência relativa.
11. Vamos gravar uma nova macro. Escolha Ferramentas, Macro, Gravar nova
macro e atribua um nome para a macro.
Dica: Se você informar o mesmo nome da macro anterior, o Excel informa que
a macro já existe e se você quer substituí-la. Pode ser uma boa alternativa
para eliminar macros erradas.
Antes de começar a fazer qualquer coisa, precisamos mudar para o modo de
referência absoluta.
12. Clique no botão Referência relativa.
12
Pronto, agora é só você repetir o processo feito na página anterior.
Você verá que a macro funcionará em qualquer célula.
FA CONSULTORIA E TREINAMENTO
34
EXCEL AVANÇADO
Tecla de atalho
Se você precisa executar uma
macro várias vezes, é melhor
associá-la a uma tecla de atalho.
1.
Clique no menu Ferramentas,
Macro, Macros...
2.
Selecione a macro e clique no
botão Opções.
3.
Na caixa Tecla de atalho digite a
letra desejada.
Dica: Para que suas teclas de atalho não
coincidam com as teclas do Excel, prefira
usar letras maiúsculas. Explicando: Ctrl+c
no Excel copia a célula selecionada mas
Ctrl+C (C maiúsculo) não tem função
alguma. É por isso que no quadro ao lado
você vê que a tela de atalho é
Ctrl+Shift+G (o G é maiúsculo).
4.
2
4
Faça o teste, pressione na planilha Shift-Ctrl-G.
Pronto! Sua macro deve ter sido executada.
FA CONSULTORIA E TREINAMENTO
35
EXCEL AVANÇADO
Atribuindo macros a um botão no menu
Além de vincular uma macro a uma tecla de atalho, você pode pendurá-la ao
menu do Excel.
1.
Clique no menu Exibir, Barra de ferramentas, Personalizar.
2.
No quadro
Personalizar que
aparece, clique na guia
Comandos.
3.
Escolha a categoria
Macro.
Para pendurar a macro
no menu, precisamos
associá-la a um botão.
Só há um botão
disponível no quadro
Personalizar.
4.
2
3
4
Arraste o botão com a carinha para a barra de ferramentas do Excel.
Por enquanto este botão não tem serventia, é só um desenho. Vamos associar
nossa macro a este botão.
5.
Selecione a carinha na barra de ferramentas e clique
no botão Modificar seleção no quadro
Personalizar.
6.
Na lista de comandos que aparece, escolha Atribuir
macro.
7.
O Excel exibe um quadro com as macros
disponíveis. Selecione a macro desejada e clique
em OK para fechar o quadro de diálogos.
Dica
Dica: Você pode trocar a imagem do botão, se
quiser. Basta escolher no quadro ao lado Alterar
imagem de botão para escolher uma outra imagem já pronta, ou Editar
imagem de botão para você mesmo desenhar seu botão.
FA CONSULTORIA E TREINAMENTO
6
36
EXCEL AVANÇADO
Atribuindo macros um comando no menu
Muitas vezes pode ser melhor associar sua macro a um comando mesmo, em
vez de a um botão. Veja a diferença e escolha o que for melhor para você.
1.
O processo é muito semelhante ao de atribuição de macros a um botão:
Escolha Exibir, Barra de ferramentas, Personalizar...
2.
Na guia Comandos
escolha a categoria
Macros.
3.
Arraste Personalizar
item de menu para a
linha de comandos do
Excel (onde já aparece
Arquivo, Editar etc).
4.
Mantenha Personalizar
item de menu
selecionado e clique no
botão Modificar
seleção.
5.
No menu que aparece, defina um nome para o
novo item em Nome.
6.
O item ainda não funciona, precisamos associar
uma macro a ele. Clique em Atribuir macro .
7.
O Excel exibe um quadro com as macros
disponíveis. Selecione a macro desejada e clique
em OK.
8.
Feche os quadros de diálogo e experimente sua
macro!
3
4
5
6
FA CONSULTORIA E TREINAMENTO
37
EXCEL AVANÇADO
Editando a macro
Uma macro nada mais é do que um programa. Felizmente o gravador de
macros cria este programa para você. É um programa em linguagem VBA.
Vamos ver como ficou a macro que você gravou nas páginas passadas.
1.
Na planilha que contém
a macro, escolha
Ferramentas, Macro,
Macros...
2.
No quadro que aparece,
selecione a macro cujo
programa você quer ver
e clique no botão Editar.
A janela do VBA
aparece. Aqui você pode alterar
o que quiser. Uma alteração
simples seria mudar o nome
que a macro digita na célula.
Basta digitar o novo nome na
linha apropriada.
3.
Troque Fabíola Luz pelo seu
próprio nome.
4.
Volte à planilha do Excel agora e
execute sua macro.
2
3
É o seu nome que aparece na
célula, não é?
FA CONSULTORIA E TREINAMENTO
38
EXCEL AVANÇADO
Quadros de diálogo em macros
Você também pode digitar linhas inteiras de comandos neste programa VBA.
Só para você ter uma idéia de como isso funciona, vamos fazer nossa macro
abrir um quadro de diálogos simples, com uma mensagem de boas vindas.
1.
Se você estiver na planilha, volte à janela onde aparece o programa (olha a
barra de tarefas do Windows, ela ainda deve estar lá – é só clicar nela).
Dica: Se preferir, você pode pressionar Alt-F11 para exibir esta janela.
2.
Logo depois dos comentários
em verde (no início da
macro), digite:
Msgbox “Bem vindo ao VBA.”,
vb0k0only,”Mensagem”
3
Comentário: Este texto deve
ser digitado em uma única
linha, conforme mostra a
figura ao lado.
3.
Volte ao Excel e execute a
macro.
Aparece a mensagem ao lado, certo?
4.
A mensagem é só para informação. Clique em OK para
fechá-la e o restante da macro será executado normalmente.
FA CONSULTORIA E TREINAMENTO
39
EXCEL AVANÇADO
Altere o valor de uma propriedade com uma macro
Você pode criar uma macro para desativar as linhas de grade – se elas estiverem
aparecendo – ou ativá-las – se elas não estiverem aparecendo. Você faz isso
alterando a propriedade que exibe – ou não – a grade na planilha.
1.
Escolha Ferramentas, Macro, Gravar nova macro...
2.
Chame a macro RemoveGrade e clique em OK.
Agora, enquanto a macro está sendo gravada, vamos ocultar as linhas de
grade.
3.
Escolha
Ferramentas,
Opções... e
na guia
Exibir
desmarque a
caixa de
seleção
Linhas de
grade. Clique
em OK para
fechar o
quadro.
3
4.
Clique no botão Parar gravação.
5.
Vamos ver como ficou o programa da macro: escolha Ferramentas, Macro,
Macros, selecione a macro RemoveGrade e clique em Editar.
6.
Teremos o seguinte código (sem as linhas de comentário):
Sub RemoveGrade()
ActiveWindow.DisplayGridlines = False
End Sub
Do jeito que está, esta macro apenas oculta as linhas de grade. Mas e se as
grades não estiverem aparecendo? Queremos que a macro as exiba. O
segredo aqui é fazer a macro perceber se as grades estão aparecendo ou
não. Veja como fazer isso.
FA CONSULTORIA E TREINAMENTO
40
EXCEL AVANÇADO
7.
Mude as linhas de comando de sua macro assim:
Sub RemoveGrade()
Minhagrade = ActiveWindow.DisplayGridlines
ActiveWindow.DisplayGridlines =Not Minhagrade
End Sub
Esta macro usa praticamente as mesmas instruções da macro anterior, mas
com algumas melhorias. Vamos interpretá-las:
Minhagrade = ActiveWindow.DisplayGridlines
ActiveWindow.DisplayGridlines é a propriedade que informa o status da linha
de grade: estão aparecendo (True) ou não (False)? Através desta instrução
você guarda este status na variável Minhagrade.
ActiveWindow.DisplayGridlines =Not Minhagrade
A palavra chave Not alterna o valor que está em Minhagrade. Se estiver como
True (grade aparecendo) ela passa a ser False (grade oculta). E vice-versa.
8.
É por isso que a macro funciona. Volte ao Excel e experimente!
Respondendo perguntas e tomando decisões
Você já usou a função MsgBox nas páginas anteriores – de forma bem
simples. No entanto, você pode aproveitá-la ainda mais! Considere a macro a
seguir:
Sub VerificarExecucao()
minhaVerificacao = MsgBox("Isso levará muito tempo. Continuar?", vbYesNo)
If minhaVerificacao = vbNo Then
Exit Sub
End If
MsgBox ("Continuar com a macro lenta...")
End Sub
Temos duas grandes novidades aqui: vbYesNo e If ... Then. Estes são
comandos essenciais para quem trabalha com VBA. Vamos interpretá-los.
FA CONSULTORIA E TREINAMENTO
41
EXCEL AVANÇADO
minhaVerificacao = MsgBox("Isso levará muito tempo. Continuar?", vbYesNo)
MsgBox exibe uma mensagem com uma pergunta e apresenta as opções Yes
ou No (estipuladas em vbYesNo). A resposta é armazenada na variável
minhaVerificacao.
If minhaVerificacao = vbNo Then
If verifica se a resposta guardada em vbNo é Sim ou Não.
1.
a)
Se for Sim, a linha seguinte, Exit Sub, é executada (Exit Sub encerra a
macro).
b)
Se for Não, a linha logo após End If, MsgBox ("Continuar com a macro
lenta..."), é executada (a mensagem Continuar com a macro lenta...
aparece).
Vamos experimentar esta macro: no Editor do VisualBasic, escolha Inserir,
Módulo e digite as instruções conforme aparece na figura acima.
Comentário: Preste atenção quando você estiver digitando a segunda linha da
macro. Assim que você digitar a vírgula depois de Continuar?”, aparece uma
lista de sugestões sobre o que você pode usar. É só escolher vbYesNo. Assim
fica mais fácil lembrar das instruções, concorda?
2.
Pronto, agora é só você testar a macro. Volte ao Excel e execute-a!
FA CONSULTORIA E TREINAMENTO
42
EXCEL AVANÇADO
Executando uma mesma instrução várias vezes (o Loop For Each)
Muitas vezes você precisa executar uma mesma instrução várias vezes. Por
exemplo, você pode querer formatar várias células (e não uma só) ou pode
precisar modificar várias planilhas em um arquivo.
A macro a seguir protege todas as planilhas de um arquivo Excel:
Sub ProtectSheets ()
Dim mySheet As Worksheet
For Each mySheet in WorkSheets
mySheet.Select
mySheet.Protect “Senha”, True, True, True
Next mySheet
End Sub
Vamos interpretar cada linha principal:
• Dim mySheet As Worksheet armazena em mySheet o número de planilhas
no arquivo.
• For Each mySheet in WorkSheets executa as instruções que aparecem nas
linhas seguintes para cada planilha em WorkSheets
• mySheet.Select selecione cada planilha
• mySheet.Protect “Senha”, True, True, True protege cada planilha, atribuindo
a senha “Senha” para cada uma delas
• Next mySheet faz o processo ser repetido para a próxima planilha
Se você quiser uma outra macro que desproteja todas as planilhas, digite as
seguintes instruções:
Sub UnprotectSheets ()
Dim mySheet As Worksheet
For Each mySheet in WorkSheets
mySheet.Select
mySheet.Unprotect “Senha”
Next mySheet
End Sub
Esta macro é muito parecida com a anterior. Só vamos chamar sua atenção
para a linha mySheet.Unprotect “Senha”, que fornece a senha “Senha” para
desproteger cada planilha.
Que tal agora digitar e executar estas macros para ver o resultado?
FA CONSULTORIA E TREINAMENTO
43