Excel Avançado

Prévia do material em texto

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 
Excel Avançado 
 
 
 
 
Í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 
FA CONSULTORIA E TREINAMENTO 1 
Tabela Dinâmica 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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... 
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. 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 2 
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. 
 
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. 
 
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. 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. Clique no botão Layout. 
4 
5 
6 
7 
8 
9 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 3 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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. 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. 
 
10 
11 
12 
13 
Dados 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 4 
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 oassistente. 
 
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. 
 
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. 
 
 
15 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 5 
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. Para alterar o Intervalo, 
basta selecionar 
novamente a tabela, agora 
incluindo a nova linha. 
 
7. 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. 
 
 
6 
7 
4 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 6 
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. 
 
 
 
 
 
 
3 
A operação Soma é definida 
automaticamente 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 7 
Trabalhando com um conjunto de hipóteses 
 
 
 
 
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... 
 
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. 
 
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! 
5 
6 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 8 
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. 
 
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%. 
 
 
 
8 
7 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 9 
Trabalhando com dois conjuntos de hipóteses 
 
 
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. 
 
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. 
4 
5 
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. 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 10 
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 informaras 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? 
 
 
 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 11 
Atingindo Metas 
 
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). 
 
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. 
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. 
2 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 12 
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. 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 13 
Atingindo metas em gráficos 
 
 
 
 
 
 
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. 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. 
Você também pode aproveitar o recurso Atingir meta a partir de gráficos. É 
muito mais visual, facilita o entendimento! 
5 
6 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 14 
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. 
 
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. 
Veja 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 15 
Cenários 
 
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. 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. 
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. 
3 
4 
5 
6
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 16 
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. 
 
É 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. 
 
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. 
 
8 
9 
10 
11 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 17 
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. 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. 
 
 
2 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 18 
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. 
 
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. 
 
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. 
3 
4 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 19 
Solver 
 
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. 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. 
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. 
2 
3 
4 
5 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 20 
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. Clique em OK. 
 
Veja: O quadro Submeter 
às restrições mostra as 
restrições definidas (uma 
só por enquanto neste 
exemplo). 
 
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. 
 
 
Veja 
10 
6 8 
7 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 21 
O Solver encontrou 
uma solução, 
conforme informe o 
quadro ao lado. Você 
pode aceitar as 
sugestões do Solver 
(Manter solução do 
Solver) ou descartá-las (Restaurar valores originais). 
 
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. 
 
Veja 
12 
11 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 22 
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. 
3 
4 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 23 
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. 
 
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, asmelhores 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! 
6 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 24 
Auditoria 
 
 
 
 
 
 
 
 
 
 
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. 
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. 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 25 
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. 
 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 26 
Estrutura de tópicos, organização automática 
 
 
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. 
 
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). 
 
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. 
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! 
3 
4 5 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 27 
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. 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 28 
Consulta a banco de dados 
 
 
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. 
 
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. 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. 
 
6. Clique em Avançar. 
2 
4 
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. 
5 
6 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 29 
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. 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. 
 
Por fim o Assistente pergunta 
onde você quer colocar estes 
dados. 
 
9. Claro, queremos que os 
dados apareçam em nossa 
planilha. Mantenha assinalada 
a opção Retornar dados ao 
Microsoft Excel. 
 
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. 
 
7 
11 
8 
9 
10 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 30 
Planilha com jeito de formulário 
 
 
 
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. 
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. 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 31 
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). 
 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 32 
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. 
 
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. 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. 
12 
14 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 33 
Macros 
 
Vamos gravar uma macro que faz uma formatação simples. 
 
1. Clique no menu Ferramentas, Macro, 
Gravar nova macro... 
 
2. No quadro Gravar macro que parece, 
digite o Nome da macro. 
 
3. Faça uma breve descrição da macro. 
 
4. Clique em OK. 
 
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... 
 
10. Selecione a macro feita e clique em Executar. 
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. 
2 
3 
9 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 34 
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. 
 
Pronto, agora é só você repetir o processo feito na página anterior. 
Você verá que a macro funcionará em qualquer célula. 
 
12 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 35 
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 temfunção 
alguma. É por isso que no quadro ao lado 
você vê que a tela de atalho é 
Ctrl+Shift+G (o G é maiúsculo). 
 
4. Faça o teste, pressione na planilha Shift-Ctrl-G. 
 
Pronto! Sua macro deve ter sido executada. 
 
4 
2 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 36 
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. 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: 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. 
6 
Dica 
2 
3 
4 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 37 
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 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 38 
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. 
 
É o seu nome que aparece na 
célula, não é? 
 
2 
3 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 39 
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” 
 
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. 
 
 
3 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 40 
Altere o valor de uma propriedade com uma macro 
 
 
 
 
 
 
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. 
 
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. 
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. 
3 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 41 
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. 
 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 42 
 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. 
 
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). 
 
1. 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! 
 
EXCEL AVANÇADO 
FA CONSULTORIA E TREINAMENTO 43 
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?