EXCEL AVANÇADO 5 DEMAIS FUNÇÕES

Prévia do material em texto

EXCEL AVANÇADO 5
DEMAIS FUNÇÕES
Muito bem! Agora que você já estudou as quatro primeiras unidades 
deste curso, está na hora de assimilar a última unidade. Veremos como utilizar 
funções ainda mais avançadas do Excel, como as ferramentas “Consolidar”, 
“Vínculos”, “Cenários”, “Atingir meta”, “Solver”, “Tabelas variáveis”, “Barra de 
formulários”, “Macros” e as funções “CONCATENAR” e “ARRUMAR”.
Estas ferramentas visam oferecer subsídios para que você possa fazer 
do Excel uma ferramenta completa do ponto de vista analítico e gestor, pois 
por meio delas pode-se, por exemplo, identificar índices ótimos de produção, 
que farão toda a diferença na hora da tomada de uma decisão estratégica na 
gerência de um processo produtivo. Também será possível otimizar tarefas 
repetitivas para que todos os procedimentos possam ser executados a partir 
de um comando único.
Não menos importante, a manipulação e a consistência de múltiplos 
arquivos também serão abordadas nesta unidade. Isso é fundamental, na 
medida em que o volume de informações a ser manipulado aumenta e a 
probabilidade de falhas/inconsistências aumenta na mesma direção.
Por fim, você será capaz de unir várias informações para apresentação de 
relatórios concisos e com aspecto profissional, sempre mantendo o dinamismo 
que é inerente aos conjuntos de dados, os quais podem ser modificados 
livremente e, por isso, apresentar desafios para a obtenção de informações 
em “tempo real”.
Como esta é a unidade que encerra este curso livre de Excel Avançado, 
ficam aqui os nossos sinceros votos de que, ao término desta disciplina, você 
se sinta mais familiarizado com o ambiente versátil e poderoso do Excel, 
fazendo uso de habilidades que, sem dúvida, lhe conferirão um diferencial 
no mercado de trabalho!
APRESENTAÇÃO
Organização
Greisse Moser 
Badalotti
Reitor da 
UNIASSELVI
Prof. Hermínio Kloch
Pró-Reitora do EAD
Prof.ª Francieli Stano 
Torres
Autor
Paolo Moser
Edição Gráfica 
e Revisão
UNIASSELVI
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
FORMAÇÃO DO 
PROFESSOR
.05
5 INTRODUÇÃO
Muito bem! Agora que você já estudou as quatro primeiras unidades deste 
curso, está na hora de assimilar a última unidade. Veremos como utilizar funções 
ainda mais avançadas do Excel, como as ferramentas “Consolidar”, “Vínculos”, 
“Cenários”, “Atingir meta”, “Solver”, “Tabelas variáveis”, “Barra de formulários”, “Macros” 
e as funções “CONCATENAR” e “ARRUMAR”.
Estas ferramentas visam oferecer subsídios para que você possa fazer do 
Excel uma ferramenta completa do ponto de vista analítico e gestor, pois por meio 
delas pode-se, por exemplo, identificar índices ótimos de produção, que farão toda 
a diferença na hora da tomada de uma decisão estratégica na gerência de um 
processo produtivo. Também será possível otimizar tarefas repetitivas para que 
todos os procedimentos possam ser executados a partir de um comando único.
Não menos importante, a manipulação e a consistência de múltiplos arquivos 
também serão abordadas nesta unidade. Isso é fundamental, na medida em que 
o volume de informações a ser manipulado aumenta e a probabilidade de falhas/
inconsistências aumenta na mesma direção.
Por fim, você será capaz de unir várias informações para apresentação de 
relatórios concisos e com aspecto profissional, sempre mantendo o dinamismo 
que é inerente aos conjuntos de dados, os quais podem ser modificados livremente 
e, por isso, apresentar desafios para a obtenção de informações em “tempo real”.
Como esta é a unidade que encerra este curso livre de Excel Avançado, ficam 
aqui os nossos sinceros votos de que, ao término desta disciplina, você se sinta 
mais familiarizado com o ambiente versátil e poderoso do Excel, fazendo uso de 
habilidades que, sem dúvida, lhe conferirão um diferencial no mercado de trabalho!
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
5.1 CONSOLIDAR
A ferramenta CONSOLIDAR permite “juntar” informações de várias planilhas 
em uma planilha única, que pode servir de relatório, por exemplo. Para elucidar o 
contexto, imagine a situação a seguir.
Você é da administração de uma instituição educacional e tem em suas mãos 
uma bolsa de estudos no exterior para ser entregue ao aluno mais “dedicado” da 
quarta fase do curso de Sistemas de Informação, por exemplo. Existem 10 alunos 
regulares nesta fase, eles estão matriculados, conjuntamente, em três disciplinas. O 
nome das disciplinas é irrelevante para o exemplo, mas para concretizar ainda mais 
as ideias, suponha que sejam elas: Programação I, Banco de Dados e Programação 
para Web. Ao término do semestre letivo, você importa do sistema as notas destes 
alunos, porém as disciplinas são geradas em planilhas separadas. Agora, suponha 
que você queira obter uma média geral de cada aluno, para oferecer a bolsa de 
estudos de forma justa. Como você faria?
É neste ponto que a ferramenta CONSOLIDAR aparece como um ótimo 
recurso. Ela pode ser acessada na aba “DADOS”, na seção “Ferramentas de Dados”, 
no botão “Consolidar” (DADOS >> Ferramentas de Dados > Consolidar), conforme 
a Figura 1.
FIGURA 1 – LOCALIZAÇÃO DO BOTÃO CONSOLIDAR NO EXCEL 2013
FONTE: O autor
Como faremos as demais ferramentas apresentadas nesta unidade, 
vamos aprender fazendo. Portanto, acompanhe o exemplo a seguir, em que 
desenvolveremos a situação hipotética apresentada acima.
Exemplo 1: supondo que você esteja ambientado com a situação 
hipotética apresentada acima, a Figura 2 apresenta as médias dos 10 alunos1 
concorrentes à bolsa de estudos, nas disciplinas Programação I, Banco 
de Dados e Programação para Web. Cada disciplina está em uma planilha 
separada. Utilize a ferramenta CONSOLIDAR para obter a média geral de cada 
aluno e identificar quem é o mais indicado à bolsa de estudos.
1 Por simplicidade, os alunos foram nomeados de {a, b, c, ... , j}. As médias foram geradas aleatoriamente utilizando a 
função =ALEATORIOENTRE(4;10).
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
FIGURA 2 – MÉDIAS (HIPOTÉTICAS) DE 10 ALUNOS DO CURSO DE SISTEMAS DE INFORMAÇÃO NAS 
DISCIPLINAS DE (a) PROGRAMAÇÃO I, (b) BANCO DE DADOS E (c) PROGRAMAÇÃO PARA WEB
FONTE: O autor
(b) (c)(a)
Resolução 1: primeiramente, vamos criar uma pasta de trabalho com três 
planilhas, presumidamente chamadas de “disc_a”, “disc_b” e “disc_c”. Em cada 
planilha, insira os dados das subfiguras a, b, c da Figura 2. Agora, em uma quarta 
planilha (chamaremos esta de “consolidar”), acesse a ferramenta CONSOLIDAR. 
Na caixa de diálogo que aparece, mude o campo “Função” para a opção “Média”. 
Utilize o botão de seleção de intervalos para selecionar todas as células válidas 
(células com valores, inclusive cabeçalhos) da planilha “disc_a” e adicioná-los 
ao campo “Referência”. Se tudo ocorrer bem, este campo deverá mostrar o 
intervalo disc_a!$A$1:$B$11. Selecione as opções “Usar rótulos na linha superior e 
coluna esquerda” (lembre-se de que você selecionou os cabeçalhos) e clique em 
“Adicionar”. Repita o procedimento para as outras duas planilhas. Neste instante, 
a caixa de diálogo da ferramenta CONSOLIDAR deve apresentar-se da mesma 
forma que na Figura 3.
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
FIGURA 3 – CONFIGURAÇÃO DA CAIXA DE DIÁLOGO DA FERRAMENTA 
CONSOLIDAR PARA O EXEMPLO 1.
FONTE: O autor
Agora, basta clicar em OK e pronto, na planilha “consolidar” aparecem 
os cabeçalhos, os nomes das linhas e as médias de cada um dos alunos, 
considerando as três disciplinas.
5.2 VÍNCULOS
Os “Vínculos” (também chamados de “Referências Externas”) são 
simplesmente links que o Excel estabelece entre diferentes pastas de trabalho. 
Essa funcionalidade tem especial importância na era atual, onde a computação 
em nuvem está em alta. Você não está familiarizado com isso? Vamos explicar.
Imagine que você tenha uma pasta de trabalho que contém um valor que 
é constantemente atualizado em outra pasta de trabalho. Por exemplo: uma 
rede de lojas possui as filiais A, B e C. O gerente desta rede de lojas possui 
uma planilhaúnica, onde deseja visualizar o faturamento diário de cada filial. 
Este valor é atualizado diariamente, ao término do expediente de trabalho. 
Para complicar um pouco mais, suponha que as filiais se encontram em três 
cidades diferentes. Qual é a melhor solução? Talvez você pense: “Simples! Os 
setores financeiros de cada filial têm acesso a uma planilha única; ao término 
do expediente, cada filial preenche o seu respectivo campo nesta planilha e 
envia ao gerente”. Cuidado, isso não é eficiente, pois pode haver desencontros 
no momento dos preenchimentos, além de vazamento de informações entre 
as filiais. Estrategicamente, o gerente deve evitar isso.
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
Uma solução mais coerente seria cada filial preencher a sua respectiva planilha 
em um banco de dados on-line e o gerente ler o valor de cada filial diretamente 
destas planilhas. Você concorda? Pois é, estes documentos localizados na web e 
que são imediatamente atualizados após upload do usuário constituem o conceito 
de “nuvem”; este conceito é uma tendência dos dias atuais. Quanto ao gerente, 
mais prático do que abrir cada uma das planilhas das filiais, seria ter uma planilha 
única (chamada aqui de “planilha mestra”) onde estes valores fossem atualizados 
em tempo real. Neste momento, a ideia de vínculos faz-se necessária.
Para criar um vínculo em uma planilha, basta escolher a célula onde se deseja 
que o valor vinculado seja exibido, digitar “=” e selecionar a célula na planilha de 
origem do dado (que pode, inclusive, estar localizada em outra pasta de trabalho). 
Uma vez vinculada, a célula será atualizada sempre que a planilha mestra for aberta 
ou sempre que a opção “Atualizar” for acionada. Acompanhe no Exemplo 2 o 
desenvolvimento do procedimento hipotético apresentado nesta seção.
Exemplo 2: as filiais A, B e C de uma rede de lojas digitam o valor de 
seu faturamento diário na célula A1 de suas respectivas planilhas, que se 
encontram em suas pastas de trabalho particulares (filial_a.xlsx, filial_b.xlsx e 
filial_c.xlsx), que estão armazenadas em um serviço de armazenamento em 
nuvem1. Suponha os seguintes faturamentos das filiais em um dia qualquer:
• Filial A: R$ 32.000,00.
• Filial B: R$ 20.000,00.
• Filial C: R$ 23.000,00.
 
Construa a “planilha mestra” gerencial, que permita ao gerente da rede 
de lojas obter os valores atualizados, em tempo real, das filiais A, B e C.
Resolução 2: primeiramente, vamos criar as planilhas fi l ial_a.xlsx, 
filial_b.xlsx e filial_c.xlsx. Na primeira planilha de cada uma destas pastas, 
na célula A1, digite os valores R$ 32.000,00, R$ 20.000,00 e R$ 23.000,00, 
respectivamente (digite apenas o valor e utilize a opção “formato de número 
de contabilização” para transformá-lo em valor de moeda). Agora, crie uma 
nova pasta de trabalho chamada planilha_mestra.xlsx (mantenha as demais 
planilhas abertas, precisaremos delas). Nas células A1, A2 e A3 digite “Filial 
A”, “Filial B” e “Filial C”, respectivamente. Agora, selecione a célula B1. Digite 
“=”, vá na pasta de trabalho “filial_a.xlsx” e selecione a célula A1, onde você 
digitou o valor R$ 32.000,00, e aperte a tecla ENTER. Faça o mesmo para as 
células B2 e B3, referenciando a célula A1 da primeira planilha das pastas de 
trabalho filial_b.xlsx e filial_c.xlsx, respectivamente. Salve seu trabalho. Pronto, 
você percebe que as células da coluna B da Planilha Mestra estão com os 
valores digitados nas planilhas das filiais? Agora, sempre que estas planilhas 
forem atualizadas e salvas, a Planilha Mestra será atualizada, quando acessada. 
1 Empresas como Google, Microsoft e Amazon (entre outras), fornecem serviços de armazenamento em nuvem.
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
Perguntas com o teor de “atualizar vínculos” podem ser feitas pela Excel, 
basta aceitar, para garantir que o valor apresentado seja sempre o valor atual 
(último valor digitado pelas filiais). Tente, você mesmo, modificar os valores 
das filiais para ver o que acontece! 
5.3 CENÁRIOS
A ferramenta CENÁRIOS permite criar uma base de dados com relação a 
determinados atributos, que pode ser acessada de acordo com o interesse do 
usuário. Ela é muito útil para fazer pesquisas em relatórios, por exemplo.
Imagine, por exemplo, um cadastro de pessoa física que contenha os atributos 
“Nome”, “Idade”, “Profissão” e “Sexo”. Neste contexto, cada pessoa cadastrada 
corresponde a um “cenário”, pois apresenta uma combinação específica dos 
atributos supracitados. O problema reside em organizar estas informações de forma 
a obter, de forma rápida e segura, o cenário relativo a uma determinada pessoa. 
Também, espera-se que, ao pesquisar o cenário de uma determinada pessoa, os 
demais cenários sejam ocultados, para manter a pesquisa a mais clara e objetiva 
possível. A ferramenta CENÁRIOS fornece os requisitos necessários para esta tarefa.
Para acessar esta ferramenta, utiliza-se o caminho Dados >>> Ferramenta 
de Dados >> Teste de Hipóteses > Gerenciador de Cenários, conforme ilustra 
a Figura 5.3, a seguir:
FIGURA 3 – LOCALIZAÇÃO DA FERRAMENTA “GERENCIADOR DE CENÁRIOS” NO EXCEL 2013
FONTE: O autor
Agora, acompanhe no Exemplo 3 o uso do Gerenciador de Cenários para 
criar uma base de dados de pessoas físicas, conforme hipotetizado nesta seção.
Exemplo 3: utilize o “Gerenciador de Cenários” para gerenciar um banco 
de dados de pessoas físicas, que contenha os atributos “Nome”, “Idade”, 
“Profissão” e “Sexo” com as seguintes entradas (“cenários”):
• Nome: José Martins
Idade: 27
Profissão: Programador
Sexo: Masculino
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
• Nome: Maria Silva
Idade: 32
Profissão: Professora
Sexo: Feminino
• Nome: Samara de Oliveira
Idade: 31
Profissão: Analista
Sexo: Feminino
Resolução 3: começamos inserindo os dados do que identificam os campos 
do banco de dados. Nas células A1 a A4 digite Nome, Idade, Profissão e Sexo 
(cada um em uma célula). Agora, nas células B1 a B4 insira as informações de 
José Martins. Então, proceda ao caminho Dados >>> Ferramenta de Dados >> 
Teste de Hipóteses > Gerenciador de Cenários. Clique em “Adicionar”. Na caixa 
de diálogo subsequente, digite um nome para o cenário (no caso, “José”, parece 
uma boa opção). No campo “células variáveis”, insira o intervalo B1:B4. Você pode 
habilitar a opção “evitar alterações”, caso queira manter os valores do cenário 
inalteráveis. Clique em OK. Note que “José” foi incluído nos cenários. Feche esta 
caixa de diálogo e repita o procedimento para Maria e Samara. Se tudo ocorreu 
corretamente, você deve estar diante da seguinte caixa de diálogos (Figura 4):
FIGURA 4 – CAIXA DE DIÁLOGO DA FERRAMENTA “GERENCIADOR DE 
CENÁRIOS”, PARA O EXEMPLO 3.
FONTE: O autor
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
Pronto, agora basta selecionar o cenário do relativo à pessoa desejada. Clicar 
em MOSTRAR e pronto, os valores na planilha são atualizados. Interessante, não? 
Tenha em mente que esta técnica é fundamental para gerenciar um grande número 
de pessoas cadastradas, facilitando a localização dos indivíduos e possibilitando a 
geração rápida de relatórios.
5.4 ATINGIR META
A ferramenta ATINGIR META serve como uma calculadora para valores de 
variáveis de entrada em uma fórmula, uma vez que se conhece o valor final. Calma, 
é mais simples do que parece. Vamos desenvolver um raciocínio intuitivo que 
mostrará como você já faz cálculos de “atingir meta” mentalmente, sem sequer ter 
se dado conta disto. Imagine que existam quatro números a serem somados. Você 
desconhece um deles, mas sabe o valor total da soma, por exemplo: 2+1+3+x=10. 
Automaticamente, utilizando aritmética elementar, você determina que o x=4, 
certo? Pronto, você determinou o valor de x para atingir a meta 10. Você percebe 
que se mudássemos o valor da soma, você automaticamente adequaria o valor 
de x para atribuir esta meta? Pronto, é exatamente isso que o Excel faz, com a 
diferença de que a fórmula inserida pode ser de uma complexidademuito maior 
do que uma simples soma.
ATINGIR META pode ser acessada no caminho DADOS >>> Ferramentas 
de Dados >> Teste de Hipóteses > Atingir Meta, conforme a Figura 5.
FIGURA 5 – LOCALIZAÇÃO DA FERRAMENTA “ATINGIR META” NO EXCEL 2013
FONTE: O autor
A função, cuja meta deve ser atingida, pode ser tanto uma função 
nativa do Excel quanto uma função estabelecida pelo usuário. No Exemplo 
4 utilizaremos a função ATINGIR META para determinar a última nota de um 
aluno que precisa atingir média 7 em uma determinada disciplina. Lembre-
se de que, obedecendo às regras da aritmética, para que um valor em uma 
fórmula/função com n valores seja determinado, é necessário conhecer o 
resultado desta fórmula e os demais n-1 valores.
Exemplo 4: um estudante está matriculado em um curso cuja média 
final para aprovação deve ser igual ou superior a 7. Ele deve realizar quatro 
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
provas, sendo que as três primeiras já foram realizadas. Ele obteve as seguintes 
notas: 6,5, 5,5 e 9,0. Utilize a função ATINGIR META para calcular quanto ele 
deve obter na última prova para ser aprovado no curso.
Resolução 4: organize uma planilha com os dados do problema, 
conforme a Figura 6. Note que o valor do campo “Média Final” foi obtido por 
meio da função =MÉDIA(B1:B4), inserida na célula B5. Note também que a 
nota da “Prova 4” está faltando.
FIGURA 6 – DADOS PARA RESOLUÇÃO DO EXEMPLO 4
FONTE: O autor
Agora, seleciona a célula B4. Em seguida, proceda ao caminho DADOS 
>>> Ferramentas de Dados >> Teste de Hipóteses > Atingir Meta. Configure 
o campo “Definir célula” para a célula B5 (a célula que contém a fórmula para 
a qual desejamos um determinado resultado). No campo “Para valor” digite 
7 (o valor que desejamos que a função média apresente). Por fim, no campo 
“Alternando célula” insira B4 (a célula que deve ser ajustada para que a média 
resulte em 7). Pronto, clique em OK e aguarde o cômputo do Excel. No final, 
note que o Excel encontrou o valor 9 para a Prova 4, ou seja, para passar com 
média 7, dadas as notas das três primeiras provas, o aluno deve obter 9 (ou 
mais) na Prova 4. Tente repetir o procedimento para diferentes combinações 
de notas e médias e perceba a praticidade da ferramenta ATINGIR META.
5.5 SOLVER
O SOLVER é uma das funções mais avançadas do Excel e que permite 
resolver problemas de uma área da matemática chamada “Pesquisa 
Operacional”. Esta área busca otimizar funções sujeitas a restrições em suas 
variáveis de entrada, de forma a obter os valores ótimos para as mesmas. A 
resolução deste tipo de problema exige conhecimentos de álgebra matricial 
e nem sempre é acessível para o usuário que não tenha cursado disciplinas 
como Álgebra Linear e a própria Pesquisa Operacional.
Para entender o problema, pense na seguinte situação: uma empresa deseja 
produzir dois produtos relacionados, por exemplo, um eletrônico e um carregador. O 
eletrônico leva 4 horas para ser produzido, enquanto o carregador leva 1,5 horas (uma 
hora e meia). O custo para produção do eletrônico é de R$ 50,00, e do carregador 
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
é R$ 7,00. A empresa possui 500 horas para produção e R$ 5.000,00 para investir na 
produção dos dois produtos. Quantas peças de cada produto devem ser produzidas 
para maximizar o lucro da empresa, sabendo que ela pretende vender o eletrônico 
por R$ 95,00 e o carregador por R$ 15,00? 
Pois é, o problema não é tão simples, uma vez que existem várias variáveis, 
condicionantes e restrições associadas à produção. O primeiro passo (o mais 
complexo) é modelar matematicamente o problema. Uma vez modelado, podemos 
utilizar o SOLVER para encontrar o número ótimo de eletrônicos e carregadores 
produzidos. Esse desenvolvimento será feito no próximo exemplo.
A ferramenta SOLVER não vem nativamente habilitada no Excel, uma vez que 
não é de uso popular. Entretanto, basta habilitá-la – não são necessários downloads 
adicionais, apenas um pouco de paciência até que o Excel a instale. Para tanto, você 
precisa seguir o seguinte caminho: ARQUIVO >>>>> Opções >>>> Suplementos 
>>> Ir >> Solver > Ok. Aguarde uns instantes. Agora você pode acessar o SOLVER 
no caminho DADOS >> Análise > Solver, conforme mostra a Figura 7.
FIGURA 7– LOCALIZAÇÃO DA FERRAMENTA “SOLVER”, NO EXCEL 2013
FONTE: O autor
Agora, no Exemplo 5, mostraremos como resolver o problema supracitado 
e hipotetizado da produção do eletrônico e do carregador.
Exemplo 5: você se recorda da situação que mencionamos há pouco? 
Caso não se lembre, vamos citá-la novamente:
“Uma empresa deseja produzir dois produtos relacionados, por exemplo, 
um eletrônico e um carregador. O eletrônico leva 4 horas para ser produzido, 
enquanto o carregador leva 1,5 horas (uma hora e meia). O custo para produção 
do eletrônico é de R$ 50,00, e do carregador é R$ 7,00. A empresa possui 500 
horas para produção e R$ 5.000,00 para investir na produção dos dois produtos. 
Quantas peças de cada produto devem ser produzidas para maximizar o lucro 
da empresa, sabendo que a mesma pretende vender o eletrônico por R$ 95,00 
e o carregador por R$ 15,00?”
Modele matematicamente este problema e utilize o SOLVER do Excel 
para fornecer a resposta.
Resolução 5 : pr imeiramente, modelaremos matematicamente o 
problema. Para simplificar as coisas, chamaremos a quantidade de eletrônicos 
produzidos de “e” e a quantidade de carregadores produzidos de “c”. De acordo 
com o problema, note que temos as seguintes restrições:
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
 (função lucro, a ser maximizada)
 (restrição das horas)
 (restrição do orçamento)
e, c são número inteiros
Tente entender as regras estabelecidas acima. Caso lhe pareça muito 
complexo este tipo de modelagem, procure conhecer um pouco mais sobre 
o ramo da matemática aplicada – chamada “Pesquisa operacional” – ela trata 
de modelagem e solução de problemas desta natureza.
Agora vamos organizar a planilha para utilizar o SOLVER. Uma sugestão 
funcional está apresentada na Figura 8. Observe bem esta configuração e 
depois leia os comentários a seguir:
4 1,5 500e c+ ≤
95e + 15c - (50e + 7c)
50e + 7c 5000≤
FIGURA 8 – CONFIGURAÇÃO DA PLANILHA PARA UTILIZAÇÃO 
DA FERRAMENTA “SOLVER”, PARA SOLUCIONAR O PROBLEMA DO 
EXEMPLO 5
FONTE: O autor
Note que:
a) Configuramos o número inicial de cada produto para 1. Isso é arbitrário! 
Esses valores não influenciam no resultado final.
b) Nas células do intervalo B7:B9 estão codificadas as funções restritivas da 
modelagem da pesquisa operacional. A sintaxe de cada uma das células é:
• B7: =95*B1+15*B2-B8
• B8: =50*B1+7*B2
• B9: =4*B1+1,5*B2
Agora, acesse o SOLVER por meio de DADOS >> Análise > Solver. Na 
caixa de diálogo que surge, devemos fazer a seguinte configuração (Figura 
9) – explicaremos em seguida.
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
FIGURA 9 – CONFIGURAÇÃO DA FERRAMENTA “SOLVER” PARA SOLUCIONAR 
O PROBLEMA DO EXEMPLO 5
FONTE: O autor
Vamos aos comentários:
a) No campo “Definir objetivo”, selecionamos a célula que tem a função LUCRO 
que queremos MAXIMIZAR (por isso a opção Máx foi selecionada).
b) As restrições foram inseridas utilizando o botão Adicionar.
c) As duas primeiras restrições referem-se ao fato de que o número de 
eletrônicos e carregadores deve ser sempre inteiro (e, por consequência, 
não negativo).
Agora, basta clicar em RESOLVER. Note que o SOLVER apresenta como 
solução ótima para a empresa a produção de 86 eletrônicos e 100 carregadores. 
Os valores de cada um dos campos também são automaticamente atualizados 
(pois estão associados através das funções inseridas). Tente mudar os valores 
fornecidos pelo SOLVER; note que: (i) o lucro diminui ou (ii) alguma restrição não 
é satisfeita. Não adianta: os valores fornecidos pelo SOLVER são os melhores, dado o 
cenário modelado. Legal, não?
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
5.6 BARRADE FÓRMULAS
A Barra de Fórmulas está localizada acima da planilha do Excel, de modo 
muito semelhante à barra de endereços de um browser, que você utiliza para 
navegar na internet. No Excel, esta barra é precedida pelo símbolo matemático 
de função ( ( )f x ), apesar de que, não necessariamente, os valores inseridos 
na mesma precisem ser funções/fórmulas do Excel1. Confira na Figura 5.10 a 
localização da barra de fórmulas no Excel 2013.
1 Lembre-se: “funções”, para serem interpretadas pelo Excel, devem ser precedidas pelo sinal “=”. Se omitido este sinal, 
o Excel interpreta o argumento como uma sequência de caracteres (string).
FIGURA 10 – LOCALIZAÇÃO BARRA DE FÓRMULAS, NO EXCEL 2013
FONTE: O autor
A Barra de Fórmulas serve para realizar entradas para uma célula 
específica, selecionada a priori. Por exemplo, ao invés de selecionar uma 
célula e digitar a fórmula nesta célula, você pode selecionar a célula e digitar 
a fórmula (ou uma sequência de caracteres) na barra de fórmulas. A fórmula 
será avaliada após você teclar ENTER. Além da legibilidade, parece não haver 
vantagem no uso desta estratégia. De fato, não há! A barra de fórmulas se 
torna útil quando utilizada com o botão de função localizado a sua esquerda. 
Neste atalho são encontradas todas as funções do Excel, organizadas por 
categorias (estatística, financeira etc.). De fato, todas as “funções de linha” 
abordadas neste material (E, OU etc.) são encontradas neste atalho.
Incentivamos que o aluno pesquise as funções desejadas neste atalho e 
as utilize, principalmente, quando não se lembrar de todos os argumentos da 
respectiva função de linha, uma vez que o atalho “funções” fornece caixas de 
diálogo para preenchimento sequencial dos argumentos da função. Explore 
o atalho ( )f x sem medo, ele se tornará um dos seus melhores aliados no uso 
do Excel!
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
5.7 MACRO
A ferramenta MACRO está presente em todos os aplicativos do pacote MS Office 
e tem por objetivo automatizar procedimentos repetitivos. Digamos que, toda vez 
que você digita uma planilha, ao final, você a formata de maneira que: (i) a primeira 
coluna e a primeira linha fiquem em negrito; (ii) a segunda coluna fique em itálico, 
exceto a primeira célula da mesma; (iii) a terceira e quarta coluna sejam números de 
contabilização (moeda) e (iv) a quinta coluna exiba a média da terceira e quarta colunas, 
também formatada como números de contabilização (moeda). Você concorda que, 
toda vez, terá que executar o mesmo procedimento após a digitação dos dados? Uma 
MACRO, devidamente configurada, pode fazer isso por você.
As MACROS podem ser acessadas pelo caminho EXIBIÇÃO >> Macros > 
Macros, conforme ilustra a Figura 11.
FIGURA 11 – LOCALIZAÇÃO DA FERRAMENTA “MACRO” NO EXCEL 2013
FONTE: O autor
A maneira mais prática de configurar uma macro é gravando os 
procedimentos. Nesta abordagem, o usuário executa o procedimento desejado 
uma vez e os “cliques” são salvos, de maneira que possam ser reproduzidos.
Usuários mais avançados e com conhecimento de programação podem 
criar macros mais complexas, utilizando a linguagem VBA (Visual Basic for 
Applications). Essa abordagem está fora do escopo deste curso livre, sendo 
que ela mesma, por si só, seria motivo de um curso dedicado. Portanto, 
abordaremos aqui o procedimento de gravação de procedimentos.
Acompanhe o Exemplo 6, em que desenvolveremos o procedimento 
citado nesta seção.
Exemplo 6: sejam os dados da Figura 12 relativos a preços (fictícios) de 
5 produtos (a,b,c,d,e) em dois estabelecimentos distintos. A última coluna 
apresenta a média dos preços (obtida com a função =MÉDIA) e a planilha já 
está formatada nos parâmetros desejados.
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
FIGURA 12 – PREÇOS (FICTÍCIOS) DE 5 PRODUTOS (A,B,C,D,E) EM DOIS 
ESTABELECIMENTOS DISTINTOS
FONTE: O autor
Grave uma macro que reproduza esta formatação para uma nova planilha 
que contenha os mesmos atributos da Figura 12.
Resolução 6: primeiramente, olhando para a Figura 12, copie os dados 
para uma planilha do Excel, até a coluna D, sem aplicar formatação alguma. 
Agora, acesse a ferramenta MACRO por meio de EXIBIÇÃO >> Macros > 
Gravar macro. Digite um nome para ela, como “Formatação”, por exemplo, 
e, se quiser, escolha um atalho para acessá-la. Após clicar em OK execute, 
rigorosamente, os passos que você gostaria que a macro repetisse no futuro1, 
a saber:
a) Selecionar coluna A, pôr em negrito.
b) Selecionar linha 1, pôr em negrito (2 vezes).
c) Selecionar coluna B, pôr em itálico.
d) Selecionar célula B1, remover itálico.
e) Selecionar o intervalo de colunas C:E; aplicar formato de número de 
contabilização.
f) Selecionar célula E2, inserir a função =MÉDIA(C2:D2) e teclar ENTER.
g) Selecionar célula E2, expandir a função por meio de dois cliques no ponto no 
canto inferior direito da seleção.
Agora, basta clicar no símbolo de stop, no canto inferior esquerdo da 
planilha do Excel. A MACRO está gravada.
Para verificar a funcionalidade da macro, abra uma nova planilha, na 
mesma pasta de trabalho e, novamente, copie os dados da Figura 12, até a 
coluna D, sem aplicar nenhuma formatação. Após isso, acesse a macro gravada, 
por meio de EXIBIÇÃO >> Macros > Exibir macros. Encontre a macro que 
você acabou de salvar e clique em EXECUTAR. Pronto, a formatação que você 
desejava foi feita automática, e pode ser repetida para qualquer planilha nova 
que você abrir nesta pasta de trabalho.
1 É interessante executar o procedimento todo antes de gravar a macro, para garantir que todos os passos a serem 
seguidos estão bem assimilados.
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
5.8 TABELAS DE DADOS VARIÁVEIS
As Tabelas de Dados Variáveis permitem testar o resultado de uma 
fórmula para diferentes combinações de suas variáveis de entrada. Para 
entender melhor o que isto quer dizer, suponha que você tenha várias cotações 
para o preço unitário de um determinado produto e deseja visualizar qual 
será o orçamento para a compra de diferentes quantidades deste mesmo 
produto. Obviamente, o valor será diferente para diferentes combinações de 
quantidades e valores unitários deste produto. Para obter um panorama geral 
destes orçamentos, você poderia aplicar, manualmente, o algoritmo “preço 
unitário x quantidade” para cada combinação. A Tabela de Dados Variáveis 
permite estimar estes orçamentos através de um único procedimento.
Para acessar esta ferramenta, proceda ao seguinte caminho: DADOS >> 
Teste de Hipóteses > Tabela de Dados, conforme Figura 13.
FIGURA 13 – LOCALIZAÇÃO DA FERRAMENTA “TABELA DE DADOS” NO EXCEL 2013
FONTE: O autor
Agora, acompanhe uma aplicação desta ferramenta no Exemplo 7.
Exemplo 7: suponha que você tenha as seguintes cotações (obtidas de 
3 fornecedores distintos) para o preço unitário de um determinado produto: 
R$ 50,00; R$ 65,00 e R$ 72,00. Suponha também que você planeja comprar 
10, 13, 15 ou 20 unidades deste produto (dependendo da demanda) e, para 
tanto, deseja visualizar qual será o orçamento para a compra com cada um 
dos fornecedores supracitados. Utilize uma Tabela de Dados Variáveis para 
estabelecer todos os orçamentos possíveis. 
Resolução 7: primeiramente, vamos preparar uma planilha com as 
combinações de valores e quantidade. Para isto, disporemos os valores em 
uma coluna e as quantidades em uma linha, formando uma matriz (é possível 
organizar de forma inversa, sem problemas). Também listaremos, separada, 
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
uma combinação específica (qualquer) das variáveis preço e quantidade. Por 
fim, na primeira célula da matriz formada digitaremos a fórmula que calcula 
o orçamento que desejamos. Se utilizarmos a formatação da FIGURA 13, a 
fórmula está inserida na célula C3 e tem a forma: =A1*A2. 
FIGURA 14 – PREPARAÇÃO DA PLANILHA PARA USO DA FERRAMENTA 
TABELA DE DADOS
FONTE: O autor
Agora, selecione o intervalo A3:E6 e proceda ao caminho DADOS >> 
Teste de Hipóteses> Tabela de Dados. Na caixa de diálogo que aparece, no 
campo “Célula de entrada da linha”, selecione a célula A1 (ela representa o 
valor unitário) e no campo “Célula de entrada da coluna”, selecione a célula 
A1 (ela representa as quantidades). Clique em OK e pronto: a matriz será 
preenchida com o cálculo da fórmula digitada em A3, para cada combinação 
das suas respectivas variáveis de entrada.
5.9 FUNÇÃO CONCATENAR
A função CONCATENAR, como o próprio nome sugere, tem por objetivo 
“juntar” informações provenientes de diferentes células para apresentar a 
informação de forma unificada em uma célula desejada. Além disso, dentro da 
própria função, texto ou caracteres podem ser digitados de forma a completar 
a concatenação, lembrando que os textos sempre devem ser digitados entre 
aspas.
A sintaxe da função CONCATENAR é =CONCATENAR(texto1; [texto2]; ...). Os 
argumentos texto devem ser preenchidos com o texto desejado ou o endereço 
da célula que contém a informação desejada.
No Exemplo 8 , a seguir, apresentamos a função concatenar para 
apresentar os resultados de uma pesquisa eleitoral (fictícia) em favor do 
Candidato A, com seu respectivo erro percentual inerente à estatística. Confira!
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
Exemplo 8: imagine que existam dois candidatos (A e B) à prefeitura de 
uma cidade. Uma pesquisa de intenção de voto foi conduzida, com margem 
de erro de 2 pontos percentuais (para mais e para menos). Os resultados estão 
tabelados na Figura 15.
FIGURA 15– RESULTADOS DE UMA PESQUISA (FICTÍCIA) DE INTENÇÃO 
DE VOTOS PARA OS CANDIDATOS A E B, À PREFEITURA DE UMA CIDADE 
QUALQUER
FONTE: O autor
Suponha agora que você deseja apresentar os resultados na forma textual, 
por exemplo: “Intenção de votos no candidato A: 37% ± 2%” (o mesmo para o 
candidato B). Utilize a função CONCATENAR para obter este resultado.
Resolução 8: primeiramente, vamos inserir os dados conforme a Figura 13. 
Em seguida, colocaremos o símbolo ± em uma célula qualquer, digamos, H1 (utilize 
o caminho INSERIR >> Símbolos > Símbolo). Agora, em outra célula qualquer, 
digamos E2, insira a função =CONCATENAR(B2;$H$1;C2). Note que travamos a 
célula H1, pois agora expandiremos a função para a célula E3, e queremos que o 
mesmo símbolo permaneça na expressão. Pronto, os valores estão concatenados, 
para ambos os candidatos.
5.10 FUNÇÃO ARRUMAR
Como você já deve ter percebido, além de números, o Excel permite 
manipular dados de qualquer natureza, inclusive sequências de texto. O 
problema é que, utilizando as funções COPIAR e COLAR, muitas vezes, os 
textos trazidos de bases externas acabam ficando com múltiplos espaçamentos 
no Excel. A função ARRUMAR se propõe exatamente a isso: remover espaços 
desnecessários, mantendo somente espaços únicos entre as palavras.
Para tanto, sua sintaxe é muito simples: =ARRUMAR(texto), em que o 
argumento texto é autoexplicativo.
Apesar de simples, esta função ajuda muito, principalmente quando os 
textos são copiados a partir de arquivos .pdf, que tendem a gerar espaços 
duplos aleatórios no texto.
Acompanhe, no Exemplo 9, uma aplicação prática da função ARRUMAR.
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
Exemplo 9: digite na célula A1 o seguinte texto, com espaços duplos: 
“Texto com espaços a arrumar”. Utilize a função ARRUMAR para remover 
os espações excedentes. 
Resolução 9: em uma célula qualquer, digite a seguinte expressão: 
=ARRUMAR(A1). Tecle ENTER e perceba que os espaços duplos foram 
convertidos a espaços simples, o que é esperado em uma digitação correta. 
Simples, não?
 CURSO LIVRE - EXCEL AVANÇADO 5 - DEMAIS FUNÇÕES
REFERÊNCIAS
CINTO, Antonio Fernando; GÓES, Wilson Moraes. Excel avançado. Editora 
Novatec, 2015.
LOESCH, Cláudio; HEIN, Nelson. Pesquisa Operacional: fundamentos e 
modelos. Saraiva, 2009.
MARTINS, António. Excel aplicado à gestão. Lisboa, Silabo, 2003.
PERES, Paula. Excel avançado. Lisboa, Silabo, 2011.