Passo a passo Excel Avançado

Prévia do material em texto

Recursos avançados do Excel 
2013 – Passo a Passo 
Formulários, macros, recursos de análise de dados, cenários, 
atingir meta, restrições, proteção e Básico de VBA 
CLENIO EMIDIO 
 UNIFEL- EDUCAÇÃO CORPORATIVA 
 
 
Professor Clenio Emidio 2017® 
 
2 Excel Avançado 
Sumário 
 
Ativar Guia Desenvolvedor ............................................................................................................ 1 
Controle de Formulários ............................................................................................................... 2 
Macros ........................................................................................................................................... 9 
VBA .............................................................................................................................................. 13 
Função Via VBA ........................................................................................................................... 16 
Atingir Meta ................................................................................................................................ 17 
Gerenciado de Cenários .............................................................................................................. 19 
Solver ........................................................................................................................................... 22 
Validação de dados ..................................................................................................................... 24 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
file:///C:/Users/Fonseca/Desktop/Unifel/material/Passo%20a%20passo%20Excel%20Avançado.docx%23_Toc499072967
 
Professor Clenio Emidio 2017® 
 
3 Excel Avançado 
Para inicializarmos o nosso passo a passo, consideraremos a utilização do Excel versão 2013. 
No entanto, poderá ser utilizado qualquer outra versão de 2007 adiante. 
Para trabalharmos com formulários, macros e recursos de VBA, precisamos ativar a Guia 
Desenvolvedor. 
Clique no Menu Arquivo e Opções. Na caixa de diálogo clique em Personalizar Faixa de 
Opções `a esquerda e então marque Desenvolvedor à direita conforme a imagem abaixo. 
 
 
Será habilitada a Guia Desenvolvedor e a parte dos Formulários está disponível na opção 
Inserir da caixa de opções – Controles. 
 
A parte dos Controles de Formulário, são botões que não precisam implementar códigos de 
programação, ao contrário dos Controles ActiveX. 
 
Professor Clenio Emidio 2017® 
 
4 Excel Avançado 
Na opção visualizada acima, vamos clicar no primeiro botão (Controle de formulário) e então 
arraste o botão esquerdo na área da planilha, desenhando este botão. Ao soltar o botão 
esquerdo será exibido uma caixa de diálogo conforme abaixo: 
 
Este botão permite inserir uma macro que poderá ser gravada ou atribuída a este botão, caso 
já exista a macro gravada. Por enquanto, vamos apenas clicar em cancelar para vermos o 
botão e mais tarde atribuirmos uma funcionalidade. 
Caso deseje renomeá-lo, basta clicar com o botão direito sobre ele e selecionar editar texto, e 
então digite o nome desejado. 
O próximo botão na sequência – na opção Inserir da caixa de grupo Controles é a caixa de 
combinação conforme abaixo, basta clicar no botão e então arrastar sobre a área de planilha 
desenhando a caixa de combinação conforme o tamanho desejado. 
 
No entanto, o botão por si só não tem função, há não ser que possa ser vinculado. Inicialmente 
é necessário planejarmos sua funcionalidade, que vai aparecer como uma lista de opções em 
um “DropDown”. Por exemplo as opções abaixo: 
 
 
 
Professor Clenio Emidio 2017® 
 
5 Excel Avançado 
Depois de inserida a caixa de controle, clique com o botão direito sobre ela e então clique em 
Formatar Controle, será aberta a caixa abaixo onde iremos selecionar o intervalo de dados 
para aparecer na caixa. 
 
 
 
 
 
 
 
 
Clique sobre a primeira “seta” vermelha e então o Excel fica a espera para selecionar o 
intervalo de dados desejado, assim que selecionar ficará algo como: 
 
Após, pressione Enter e então clique em Ok na tela acima. Para ver o resultado da caixa, basta 
clicar fora e então clique sobre a seta da caixa de combinação e então aparecerá as opções. 
 
 
Professor Clenio Emidio 2017® 
 
6 Excel Avançado 
No entanto, somente as opções 
assim sem uma identificação interna 
poderá se tornar quase inútil, mas se 
atribuirmos um vinculo na forma de 
numero para o “masculino” e outro 
para “feminino” que por hora é 
definido de forma automática essa 
numeração, podemos então utilizá-
la para ser inserida em outra função 
. Para isso, clique com o botão 
direito sobre a caixa de combinação, 
e então Formatar Controle, na caixa 
vínculo da Célula, clique sobre a seta 
vermelha e então clique em alguma célula da planilha (preferencialmente próximo da caixa), 
em seguida pressione Enter e Ok na tela ao lado. 
Para testarmos o vínculo criado, basta clicar fora da caixa de combinação, clique na seta e 
selecione um dos dados, vai aparecer a numeração correspondente. Através desse recurso 
podemos utilizar fórmulas ou funções com base nessa numeração. No exemplo abaixo, foi 
utilizado a função SE para alternar entre o número 1(masculino) e número 2 (feminino). Dessa 
forma temos muitas possibilidades de uso dos vínculos em várias situações diferentes. 
Na caixa de combinação podemos utilizar em várias opções, como lista de cidades, lista de dias 
da semana, numeração entre outras. 
 
 
O próximo controle temos a caixa de seleção, utilizada para listar diversos itens ou opções 
como uma lista de produtos e seus preços, lista de itens, enfim várias utilidades. Basta clicar na 
caixa de seleção na caixa de grupo Controles e selecionar Caixa de Seleção e arrastar na área 
da planilha. 
Após a criar a primeira caixa, é possível duplica-la ou copiar e colar, basta selecioná-la e então 
CTRL + C e CTRL + V para colar, para quantas cópias desejadas use o CTRL + V. 
Para criar o vinculo com cada opção, repita os passos dos 
controles anteriores, clique com o botão direito sobre a caixa de 
seleção (uma a uma) Formatar Controle. Clique na seta e então 
selecione a célula desejada a qual deseja vincular, normalmente 
na célula ao lado, então pressione enter e ok. 
 
Professor Clenio Emidio 2017® 
 
7 Excel Avançado 
As opções do vínculo são: VERDADEIRO (quando marcado/ativado) e FALSO (quando 
desmarcado). 
Novamente poderíamos atribuir uma fórmula ou função para as opções do Verdadeiro ou falso 
como no exemplo abaixo. 
 
O próximo controle é botão de Rotação, inserido da mesma forma dos demais controles. 
Podemos criar um vínculo com numeração incremental conforme as opções da caixa de 
diálogo. 
 
 
 
 
 
 
 
 
Nosso próximo botão é a caixa de listagem, utilizada para mostrar opções dentro da caixa 
semelhante a caixa de combinação, porém a listagem aparece dentro de uma caixa. É possível 
realizar seleção múltipla, para isso basta marcar a opção Múltipla do Tipo de seleção.Professor Clenio Emidio 2017® 
 
8 Excel Avançado 
Na sequência, temos o botão de opção. Utilizado quando precisamos realizar seleção única! 
Dentre as opções deste botão, é possível selecionar apenas um item por vez. É necessário 
tomar um cuidado com a sequência dos botões, uma vez que são numerados 
automaticamente na utilização do vínculo. Podemos também criar funções a partir desse 
vínculo. 
 
 
 
 
 
 
 
O próximo controle é a caixa de grupo, que serve apenas para delimitar uma área específica 
ou para isolar numeração de vínculos em botões de opção. 
 
Outro botão é o Rotulo, que serve apenas com um título aplicado a uma área da planilha, 
semelhante a uma caixa de texto, porém não aceita formatação. 
 
 
 
Já o botão Barra de Rolagem é semelhante ao botão de rotação. A cada clique nas setas de 
rolagem, incrementa o valor do vínculo na quantidade que você determinar na caixa abaixo. 
Sua aplicação é restrita pelo fato de não ter muita aplicabilidade no dia a dia. 
 
Professor Clenio Emidio 2017® 
 
9 Excel Avançado 
 
 
 
 
 
 
 
 
MACROS 
Uma macro é uma sequência de ações gravadas sob a forma de um arquivo executável. Serve 
para programar certas ações repetitivas ou ações simples como emitir mensagens, executar 
outras macros, formatar informações entre várias outras possibilidades. No entanto devem ser 
utilizadas com cautela, uma vez que podem ser utilizadas para executarem códigos maliciosos 
no computador. Além disso, elas demandam mais recursos de memória e podem executar 
instruções que podem até travar o Excel ou o computador. 
Devem ser planejadas, gravadas cuidadosamente, testadas e avaliadas posteriormente. 
Importante: Ao gravar macros em uma pasta de trabalho, estas devem ser salvas em um formato 
de arquivo compatível com as próprias macros. Uma extensão de arquivo comum do Excel tem 
extensão XLSX e arquivos compatíveis com macros recebe a extensão XLSM. 
Ao gravar uma macro em um arquivo, planilha ou pasta de trabalho, é imprescindível salvar o 
arquivo habilitado para macros, para isso, ao clicar em salvar, em tipo de arquivo escolha Pasta 
Habilitada para macros. Além disso, é importante que desabilite as opções de segurança de 
macro. 
Vá no menu arquivo, clique em Opções, Central de Confiabilidade, Configurações da central de 
Confiabilidade – Configurações de Macro – ativar a opção – Habilitar todas as macros. 
 
Professor Clenio Emidio 2017® 
 
10 Excel Avançado 
Para gravar uma macro, é necessário planejamento prévio de sua funcionalidade. Após se 
certificar-se que ela é necessária, siga os passos abaixo: 
1- Podemos clicar no botão que fica na barra de Status ou Guia Desenvolvedor – Gravar 
Macro 
 
 
 
 
 
 
 
Será aberta a caixa de diálogo abaixo para podermos atribuir um nome a macro e até definir 
uma combinação de teclas (CTRL + “alguma letra”). Poderá ser escolhida onde será 
armazenada a macro, na pasta atual, em uma específica ou uma nova pasta. 
 
 
 
 
 
 
 
Ao pressionar o botão ok, poderá ser verificado o andamento da gravação da macro. 
 
 
 
 
 
Supondo que a macro seria para inserir uma frase em uma célula (poderia ser qualquer 
atividade repetitiva dentro do Excel) basta clicar em uma célula, digitar a mensagem e 
pressionar Enter para confirmar a entrada da informação na célula conforme exemplo abaixo: 
Botão para gravar macro 
 
Professor Clenio Emidio 2017® 
 
11 Excel Avançado 
Nesse momento vamos clicar em Parar Gravação, que pode ser na faixa de opções ou na Barra 
de Status. 
 
 
 
 
 
 
 
A macro já está gravada. Para conferir e executá-la, basta clicar no botão Macros, da caixa de 
grupo Código, da faixa de opções da Guia Desenvolvedor, aparecendo a tela abaixo. Basta 
selecionar a macro que gravou e então clicar em Executar. 
Vale lembrar que, depende onde o cursor estiver para inserir a frase contida na macro. Ou 
durante a gravação da macro tenha clicado sobre o endereço desejado. 
 
Porém fica burocrático, toda vez que desejar executar uma macro, ter que clicar sobre macros, 
na guia Desenvolvedor e então selecionar a macro e clicar em Executar. Sendo assim, o ideal é 
atribuir uma macro a um botão ou inserir um botão gravando a macro. 
Para isso, vá na Guia Desenvolvedor, caixa de grupo – Controles – Inserir – Botão (controle de 
formulário) e “desenhar” (arrastar e soltar) o botão na planilha. Ao soltar vai aparecer a tela de 
macros (conforme abaixo) e então escolher o nome da macro e clicar em Gravar. 
Ao terminar apenas pare a gravação da macro. 
 
 
Professor Clenio Emidio 2017® 
 
12 Excel Avançado 
 
Caso deseje facilitar ainda mais, podemos inserir uma macro em um botão inserido na Barra 
de Acesso rápido. Basta clicar no botão Personalizar Barra de Ferramentas de Acesso Rápido 
(seta pequena que fica no final da própria Barra ) clicar em Mais Comandos. 
 
 
 
 
 
 
 
 
 
Em Escolher comandos em: - selecione Macros. Será aberta a lista de macros existente na 
pasta de trabalho. Selecione a macro desejada (é importante nomear adequadamente e 
previamente as macros) e clique no botão Adicionar. É possível alterar a aparência(símbolo) 
do ícone da macro, para isso clique em Modificar. Selecione o ícone desejado e confirme com 
ok e ok novamente. 
 
 
 
 
 
 
Professor Clenio Emidio 2017® 
 
13 Excel Avançado 
 
 
 
 
 
 
 
 
 
 
 
 
 
Observe na Barra de acesso rápido, que apareceu o ícone correspondente a macro. Toda vez 
que precisar executar a macro, basta clicar sobre o botão. 
VBA- Visual Basics Applications 
É possível trabalhar com macros, digitando as linhas de código. Ou ainda inserir Userforms, 
functions e módulos no Excel usando a linguagem de programação disponível no Office da 
Microsoft. 
Na Guia Desenvolvedor, clique em Visual Basic . 
 
Professor Clenio Emidio 2017® 
 
14 Excel Avançado 
 
Caso não apareça a tela em branco para digitar o código, basta clicar em Inserir – Módulo. 
Para testar uma caixa de diálogo, digite o seguinte código: 
Private Sub CommandButton1_Click() 
Application.InputBox "Toca Bip?", "Responda SIM ou NÃO" 
End Sub 
Para testar, clique no botão Executar ou tecla f5. Vai exibir tela semelhante abaixo: 
 
Para comentar parte do código, basta iniciar a frase por aspas simples ‘ . Ao fechar a tela do 
VBA, o Excel salva o conteúdo. Veja abaixo outros exemplos de códigos VBA e suas funções. 
Desvios Condicionais 
 
Agora digite o código com as condicionais “if” onde a macro vai executar o Bip se for digitado a 
resposta esperada(SIM.) 
Insira um novo modulo: Menu inserir – Modulo dentro do Editor do Visual Basic e digite os 
dois códigos abaixo: 
 
 
'Essa macro toca o bip se for digitada a resposta única "SIM" ou um 
clique em Ok” 
 
 Sub Toca_Bip() 
resposta = Application.InputBox("Toca Bip?", "Responda SIM ou NÃO") 
 
Professor Clenio Emidio 2017® 
 
15 Excel Avançado 
 If resposta = "SIM" Then 
 Beep 
 End If 
 End Sub 
 
 Sub Toca_Bip2() 
 resposta = MsgBox("Toca Bip?", 4 + 32, "Caixa Toca Bip?") 
 If resposta = vbYes Then 
 Beep 
 End If 
 End Sub 
 
 
 
 
 
Insira um novo módulo e digite o código, abaixo 
 
Esta macro usa um loop 
 
Sub responda() 
Dim resp As Variant 
resp = "" 
Do While resp <> "S" And resp <> "s" And resp <> "N" And resp <> "n" 
resp = Application.InputBox("você tem uma dívida, pagará?: S/N?", , , 
, , , , 2) 
 Loop 
 If resp = "S" Or resp = "s" Then 
 MsgBox "Que bom, você é honesto", 48 
 Else 
 MsgBox "Que mal heim? Você é um pilantra", 16 
 End If 
End Sub 
 
 
 
 
 
Um código parecido mas usando If... Then...Else 
 
(pode ser digitado no mesmo módulo) 
 
Sub RespondaCerto() 
 Nome$ = Application.InputBox("Entre com o seu nome: ", "Entrada de 
Dados", , , , , , 2) 
 
 
Professor Clenio Emidio 2017® 
 
16 Excel Avançado 
 resp% = MsgBox("vocêtem uma dívida " & Nome$ & Chr(13) & "Pagará: 
Sim/Não?", 4) 
 If resp% = vbYes Then 
 MsgBox "Que bom, você é honesto!", 48, "Respeito muito você, 
Parabéns!!" 
 Else 
 MsgBox "Que mal, você é pilantra!", 16, "Não te respeito, eu 
te detesto!" 
 End If 
 
End Sub 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Inserir uma função no Excel através do VB 
 
Estando no Editor do VB execute o comando: Inserir – Modulo. 
 
Função Raiz Total 
Esta função – Raiz Total – deverá retornar como resposta o valor de uma raiz de um índice 
qualquer. Desta forma esta função calculará raiz cúbica e quarta de um valor. 
 
 
Function RaizT(Valor, Indice) 
' Função =RaizT(Raiz Total) 
 
 RaizT = Valor ^ (1 / Indice) 
End Function 
 
 
Vá em qualquer célula da planilha e digite a fórmula: 
=RaizT(10;2) 
Deverá retornar: 3,162278 que é a raiz quadrada de 10. 
 
Função Hipotenusa 
Esta função calcula o valor do comprimento da hipotenusa de um triangulo retângulo, sendo 
fornecidos os valores dos catetos. Para tanto, deverá ser utilizada a fórmula da hipotenusa, em 
que ela é igual à soma dos quadrados dos catetos. 
 
Function hipotenusa(Cateto1, Cateto2) 
' Função: =Hipotenusa 
 
 hipotenusa = Sqr(Cateto1 ^ 2 + Cateto2 ^ 2) 
End Function 
 
 
Em qualquer célula da planilha digite a formula: 
 
Professor Clenio Emidio 2017® 
 
17 Excel Avançado 
=hipotenusa(12;16) 
Deverá retornar 20, que é o valor da hipotenusa deste triangulo retângulo com essas medidas. 
 
Criar a Função Fibonacci 
Calcula o valor do cálculo do n-ésimo termo de uma seqüência de Fibonacci. A função de 
Fibonacci deverá retornar, por exemplo, 55 se for fornecido como parâmetro o valor 10. O 
valor 55 é o valor do décimo termo da seqüência. (1,1,2,3,5,8,13,21,34,55...) 
 
Function Fibonacci(Numero) 
 
' Função: =Fibonacci 
 
 If Numero <= 2 Then 
 Fibonacci = 1 
 Else 
 Anterior = 1 
 Atual = 1 
 For Conta = 3 To Numero 
 Proximo = Atual + Anterior 
 Anterior = Atual 
 Atual = Proximo 
 Next Conta 
 Fibonacci = Atual 
 End If 
End Function 
 
 
Em uma célula digite =fibonacci(10) 
 
 
Função Atingir Meta 
O recurso Atingir Meta no Excel, tem por finalidade alterar o valor de uma célula para que uma 
fórmula atinja determinado valor. Ao executar atingir metas, o Excel varia o valor contigo em 
uma célula que você especifica, até que uma fórmula dependente daquela célula produza o 
resultado desejado. Vamos exemplificar através de uma atividade prática. 
1. Caso da empresa Tabajara que vende tortas. Neste caso, vamos ainda considerar que 
Tabajara vende apenas um tipo de torta. O objetivo deste exercício é que você elabore 
uma planilha para fazer a análise financeira da Tabajara para exemplificarmos o uso do 
Atingir Meta e posteriormente o Cenários. 
Primeiro considere alguns valores constantes pré-definidos para Tabajara: 
- O custo fixo é igual a R$ 400,00. 
- O custo variável de cada torta é igual a R$ 3,00. 
- O preço de venda de cada torta é igual a R$ 8,00. 
Coloque estes valores constantes em células diferentes da planilha eletrônica. Não esqueça de 
adicionar comentários ao lado da célula para você não se perder. Reserve também uma célula 
da planilha para colocar o número de tortas vendidas por mês. Coloque o valor inicial de 100 
tortas nesta célula. 
Agora, vamos trabalhar com as fórmulas, considerando o seguinte: 
 
Professor Clenio Emidio 2017® 
 
18 Excel Avançado 
- A margem de contribuição é igual ao preço de venda subtraído do custo 
variável de cada torta. 
- O ponto de equilíbrio em unidades é igual ao custo fixo dividido pela margem 
de contribuição. 
- O índice de margem de contribuição é igual à margem de contribuição dividida 
pelo preço de venda. 
- O ponto de equilíbrio em valor monetário é igual ao custo fixo dividido pelo 
índice de margem de contribuição. 
- O faturamento mensal é igual ao número de tortas vendidas no mês 
multiplicado pelo preço de venda da torta. 
- A margem de contribuição total é igual ao número de tortas vendidas no mês 
multiplicado pela margem de contribuição. 
- O lucro é igual a margem de contribuição total subtraído do custo fixo. 
Adicione todas estas fórmulas na sua planilha em células diferentes. Documente sua planilha 
para torna-la clara e organizada. 
Você deve ter percebido que o lucro estimado para a Tabajara considerando que ela vendeu 
100 tortas no mês é de R$ 100,00. 
Agora, vamos imaginar que você espera um lucro maior. Consideremos um lucro esperado 
igual a R$ 1.500,00. Quantas tortas a Tabajara teria que vender? 
Para fazer este cálculo utilize o recurso “Atingir meta” do Excel. 
Vá até a Guia Dados – Teste de Hipóteses e selecione a opção “Atingir meta”. 
No campo “Definir célula”, selecione a célula da planilha que informa o lucro da empresa. No 
campo “Para valor”, digite 5000. E no campo “Alternando célula”, selecione a célula que 
informa o número de tortas vendidas no mês. 
 
Perceba que o número de tortas que a empresa deve vender é igual a 1080 para que o 
lucro seja de R$ 5000,00. Agora, faça outros testes variando o valor do lucro e perceba 
quantas tortas é necessário vender para que os resultados esperados sejam alcançados. O 
recurso “Atingir Meta” do Excel é muito simples, porém muito útil e pouco utilizado. 
2. Vamos agora considerar que a Tabajara vende mais que um produto. No total são três 
tipos de torta: torta de frango, torta de camarão e torta de calabreza. O objetivo deste 
exercício é que você elabore uma planilha para fazer uma análise financeira com vários 
produtos. No final, você fará uma demonstração de cenários variando o número de 
unidades vendidas de cada produto. 
 
Professor Clenio Emidio 2017® 
 
19 Excel Avançado 
 
Cenários 
 
Essa é uma ferramenta para trabalhar com hipóteses, é uma evolução do Atingir Meta. Com 
ela é possível criar situações, alterando os valores de determinadas células e alcançar 
resultados diferentes. 
Primeiramente, vamos colocar na planilha os valores constantes: 
- O custo fixo é igual a R$ 877,50. 
- Os custos variáveis das tortas de frango, camarão e calabreza são 
respectivamente: R$ 3,00; R$ 6,00 e R$ 4,00. 
- Os preços de venda das tortas de frango, camarão e calabreza são 
respectivamente: R$ 8,00, R$ 15,00 e R$ 8,00. 
A previsão inicial de vendas é de 150 tortas de frango, 240 tortas de camarão e 150 tortas de 
frango. 
Adicione ainda as seguintes fórmulas à sua planilha: 
- Margem de contribuição mensal de cada produto 
- Margem de contribuição total 
- Faturamento por produto 
- Faturamento total 
- Índice médio de margem de contribuição (IMMC = MCT / FT) 
- Ponto de equilíbrio em valor monetário (PEVM = CF / IMMC) 
- Lucro (L = MCT – CF) 
Consulte a apostila de empreendedorismo e verifique as fórmulas citadas acima. Agora que 
você já concluiu a planilha, de acordo com a previsão de vendas, a célula de lucro deveria estar 
informando o valor de R$ 2.632,50. Confira se realmente é este valor. Caso contrário, você 
cometeu algum engano na elaboração das fórmulas na sua planilha. 
Bem, se tudo está certo, vamos passar agora à elaboração dos cenários de previsão de vendas. 
O objetivo é definir 3 cenários de vendas e comparar o lucro alcançado nos diferentes casos. 
Cenário 1: 
- 100 tortas de frango 
- 80 tortas de camarão 
- 70 tortas de calabreza 
Cenário 2: 
- 80 tortas de frango 
- 20 tortas de camarão 
- 100 tortas de calabreza 
Cenário 3: 
- 35 tortas de frango 
- 50 tortas de camarão 
- 90 tortas de calabreza 
 
Professor Clenio Emidio 2017® 
 
20 Excel Avançado 
3. Vá até a Guia Dados – Teste de Hipóteses e selecione a opção “Gerenciador 
de Cenários”. 
 
 
Clique no botão “Adicionar”. 
Indique um nome para o primeiro cenário. Sugestão: “Cenário 1”. No campo “Células 
variáveis”, selecione o intervalo de células que contém a previsão de vendas das tortas.Depois 
pressione OK. 
 
Na próxima tela, digite os valores para o primeiro cenário: 100 tortas de frango, 80 tortas de 
camarão e 70 tortas de calabreza. Depois clique em “Adicionar”. 
 
Professor Clenio Emidio 2017® 
 
21 Excel Avançado 
 
Repita os mesmos procedimentos para os outros 2 cenários. Depois que você criou os 3 
cenários, agora clique no botão “Resumir”. 
 
 
Na próxima tela, selecione a opção “Resumo do cenário”. E no campo células de resultado, 
selecione a célula que informa o lucro da Tabajara. 
 
Você deverá receber uma tela semelhante à tela abaixo: 
 
Professor Clenio Emidio 2017® 
 
22 Excel Avançado 
 
Nesta tela você consegue comparar o lucro alcançado em cada um dos cenários. Para exercitar 
o recurso “Cenários”, experimente remover estes cenários que você criou e adicione outros 
cenários variando as células de resultado. Assim como o recurso “Atingir meta”, o recurso 
“Cenários” é muito interessante e pouco explorado. 
Solver 
É uma ferramenta poderosa do Excel que permite fazer vários tipos de simulações na sua 
planilha, sendo utilizado principalmente para análise de sensibilidade com mais de uma 
variável e com restrições de parâmetros. 
Este recurso é um suplemento do Excel, e para tal é necessário Ativar este recurso. Na Guia 
Desenvolvedor, clique em Suplementos. Aparece a tela abaixo e então marque Solver e Ok. 
ou 
Você poderá também ativar o Solver através do Menu Arquivo, Opções, Suplementos e 
marcar Solver. 
 
 
 
 
Professor Clenio Emidio 2017® 
 
23 Excel Avançado 
Para trabalhar com o Solver, vá na Guia Dados e clique em Solver (geralmente o último botão) 
 
Exemplo: Fabricar rádios e calculadoras sem comprar mais produtos ou matéria prima. 
Lucro no rádio = 10,00 – lucro na calculadora = 15,00 
Cada rádio precisa de 4 diodos, 4 resistores, 12 min de teste 
Cada calculadora precisa de 10 diodos, 4 resistores e 9,6min de teste 
Temos disponível no estoque 8000 diodos, 3000 resistores e as máquinas estão livres 160hs 
para realizar os testes. 
Qual a combinação de produtos para obter o maior lucro? 
 
Fórmulas utilizadas. 
 
 
Basta clicar em Resolver depois de aplicar as restrições e ok. 
 
 
Professor Clenio Emidio 2017® 
 
24 Excel Avançado 
 
Resultados. 
 
 
Validação de Dados 
Podemos restringir o que o usuário vai digitar e aplicar validação de dados em certas células 
para evitar erros de digitação e preenchimento para se adequar a uma regra ou limitação. 
Vá a Guia Dados – Caixa de grupo Ferramentas de Dados e Clicar em Validação de Dados e 
selecionar Validação de Dados.