Excel Básico e Avançado
Aula 3
Prof. Dr . Marco Antonio Leonel Caetano
Mudanças Abruptas
www.mudancasabruptas.com.br
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Fórmulas
Se distinguem das células de texto
por serem iniciadas pelo símbolo
“=“
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Calculando Capital Final
Fornecidos o Capital Inicial, a taxa de Juros a fórmula para o Capital Final é
Cf = Ci*(1+n*i/100)
n: número de meses que se deseja calcular o capital final
i: taxa de juros mensal
Exemplo: Capital Final para n = 6 meses
Capital Inicial Taxa de Juros Capital Final
“Ci”
“i”
“Cf”
100
2
100
3
200
2,5
200
3,5
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Inserindo uma fórmula
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Repetindo uma fórmula
Arrastar o sinal “+” com o botão
esquerdo do mouse
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado
=A2*(1+6*(B2/100))
=A3*(1+6*(B3/100))
=A4*(1+6*(B4/100))
=A5*(1+6*(B5/100))
Os números das linhas se alteram sozinhos
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Fixando Linhas
Utilizando-se “$” entre a letra da coluna e o número
da linha, fixa-se a linha para os próximos cálculos.
Exemplo: Fixando a célula A2
Valores se alteram mantendo
a célula A2 fixa
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Fixando Linhas e Colunas
Utilizando-se “$” entre ANTES e DEPOIS da letra da coluna,
fixa-se a CÉLULA para os próximos cálculos.
=$A$1+$B$1
Está fixando a célula A1 e B1 nos cálculos
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Determinação de Lote Ótimo
Q
2.C p .D
Ca
Q: lote econômico
Cp: custo do pedido
D: demanda anual
Ca: custo do armazenamento
Qual a fórmula?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Fórmula RAIZ
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo
Uma empresa de laticínios vende 4.500 litros/mês de leite. Cada vez que chegam os
carregamentos os produtores cobram R$ 7.500 e o custo de armazenamento por
litro é de R$ 45,00. Quantos litros a empresa deverá encomendar por mês?
Q
2.C p .D
Ca
Demanda D = 4500 x 12 = 54.000 litros/ano
Custo do pedido = Cp = 7.500
Custo de armazenamento = Ca = 45
2.7500.54000
Q
 4.242litros
45
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Na Planilha
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exercício
A temperatura das máquinas de resfriamento de produtos de um laticínio devem
estar em Farenheit. Criar uma tabela de conversão.
C F  32

5
9
Construa uma tabela com as informações:
Temperatura Farenheit Temperatura em Celsius
10
20
32
40
60
80
100
150
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
A função soma
Calcula a soma total automática de todas as células
desde o início das linhas da coluna até a célula anterior ao comando
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Demanda Total Anual
Deixar o cursor aqui
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Seleção para autosoma
Barra de fórmulas
mostrando a seleção
da região
Comando autosoma
indicando a área
selecionada.
Área selecionada
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Funções Pré-definidas - Primeira maneira
Usando o Botão direto das funções
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Funções Pré-definidas - Segunda Maneira
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exercício
Calcular a demanda média mensal do produto do exemplo anterior
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Cuidado!
Errado!
B2 - B12
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado
Média
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exercício
Qual a volatilidade (desvio-padrão) da demanda?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Atenção
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Funções Lógicas
Funções que verificam se uma expressão matemática é
verdadeira ou falsa, e então obrigam o programar a
assumir valores pré-estabelecidos pelo usuário
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
O comando “SE”
SE (expressão lógica; valor se verdadeiro; valor se falso)
Exemplo:
Retorno = Venda - Custo
Se retorno <0 “prejuízo”
Se retorno >0 “lucro”
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Caso de Estudo – A demanda do produto
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Margem de lucro
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
A função SE no Excel
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Ajustando a função
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Marcando a margem de lucro
Se num mês ocorreu margem maior que R$2,00 na coluna ao lado colocar
o sinal positivo “+”. Caso contrário colocar“-”.
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado do mês 1.
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Extensão do resultado
Clicar e arrastar o mouse
com o botão da esquerda.
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exercício
Usar a função lógica SE para separar os valores de DENTRO DA MARGEM dos valores
FORA DA MARGEM de lucro. Depois usar a função AUTOSOMA para dizer o total lucro
dentro e fora da margem.
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Separando os valores dentro da margem de lucro
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Separando os valores FORA da margem de lucro
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
A autosoma do cumprimento de meta
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
A autosoma do NÃO cumprimento de meta
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado Final
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Funções Financeiras
EXCEL
VALOR PRESENTE
VF
VP 
n
1  j 
VALOR FUTURO
VF  VP1  j 
n
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo Valor Presente
Uma empresa tem um contrato para vencer em 5 anos no valor de
R$500.000. A taxa SELIC (juros básicos do Brasil) é de 12,75% ao ano.
De quanto deve ser a provisão da empresa nesse ano?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo Valor Presente
Uma empresa tem um contrato para vencer em 5 anos no valor de
R$500.000. A taxa SELIC (juros básicos do Brasil) é de 12,75% ao ano.
De quanto deve ser a provisão da empresa nesse ano?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo Valor Presente
Uma empresa tem um contrato para vencer em 5 anos no valor de
R$500.000. A taxa SELIC (juros básicos do Brasil) é de 12,75% ao ano.
De quanto deve ser a provisão da empresa nesse ano?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo Valor Futuro
No caso da empresa anterior, ela pretende alocar o valor de R$274.401,99
na poupança com rendimento de 6% ao ano pois tem medo de aplicar em
fundos ou ações. No período de 5 anos, quanto vai faltar de provisão
para pagamento do contrato?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo Valor Futuro
No caso da empresa anterior, ela pretende alocar o valor de R$274.401,99
na poupança com rendimento de 6% ao ano pois tem medo de aplicar em
fundos ou ações. No período de 5 anos, quanto vai faltar de provisão
para pagamento do contrato?
A firma precisará de mais R$132.788,24
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Caso
Em vista da dificuldade financeira, a empresa estuda investir em ações
da Petrobras para diminuir a diferença para cumprir o contrato do produto.
Ela estuda aplicar os R$274.401,99 em ações. Olhe o histórico do preço
médio da ação da Petrobras em 5 anos:
Qual o risco dessa operação?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Primeiro calcule a média e desvio padrão das ações
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Primeiro calcule a média e desvio padrão das ações
Quantas ações a empresa consegue comprar com R$274.401,99
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Primeiro calcule a média e desvio padrão das ações
Quantas ações a empresa consegue comprar com R$274.401,99
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Crie o cenário otimista e pessimista usando :
otimista  média 
1,96.desviopadrão
5
pessimista  média 
1,96.desviopadrão
5
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Crie o cenário otimista e pessimista usando :
otimista  média 
1,96.desviopadrão
5
pessimista  média 
1,96.desviopadrão
5
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Calcule a rentabilidade de cada cenário em relação à média
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Calcule a rentabilidade de cada cenário em relação à média
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Quanto a empresa pode esperar de valor futuro nos próximos 5 anos?
VF otimista:
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Quanto a empresa pode esperar de valor futuro nos próximos 5 anos?
Tem que dividir a rentabilidade por 5 anos
VF otimista:
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Quanto a empresa pode esperar de valor futuro nos próximos 5 anos?
VF otimista:
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Quanto a empresa pode esperar de valor futuro nos próximos 5 anos?
VF pessimista:
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Quanto a empresa pode esperar de valor futuro nos próximos 5 anos?
VF pessimista:
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Alocação com Sistemas Lineares
Um sistema linear é um conjunto de equações que se interligam para uma
representação de dependência entre variáveis. Um sistema pode ser do tipo:
2 x1  x 2  3x3  2 x 4  1
 3x  x  2 x  x  2
 1
2
3
4

 - x 1  2x 2 - 4x 3  3x 4  4
 x 1  2x 2 - x 3  4x 4  4
Como resolver? Ou seja, como encontrar os valores de
x1, x2, x3 e x4?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Uma forma interessante de representação de sistemas lineares é com o uso
de matrizes.
 a11 a12

A   a21 a22
a
 31 a32
a13 

a23 
a33 
O produto de duas matrizes no Excel é realizado pelo comando “ =matriz.mult ”
com o uso das teclas CTRL+SHIFT+ENTER
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
1 2 1  2 1 4

 

 0 2 1   1 2 3 
 3 4 2  2 1 3

 

Exemplo
Antes do uso do comando deve-se selecionar a dimensão de onde será a resposta,
no caso células 3x3
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Aqui!
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Não clicar em ok!
Direitos autorais: prof. Dr. Marco Antonio
Leonel
Caetano
Usar
ctrl+shift+enter
Ctrl+shift+enter
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Matriz Inversa
Representação A-1
Sendo
 2 5 4


A   3 1 2
5 4 6


Para saber sua inversa basta usar “=matriz.inverso” da mesma forma que o
produto de matrizes, selecionando regiões e usando CTRL+SHIFT+ENTER
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
E o sistema linear? Como resolver?
2 x1  x2  3x3  1

 3x1  x 2  2 x3  2
 - x  2x - 4x  4
2
3
 1
Podemos transformar o sistema anterior na forma de matrizes:
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
E o sistema linear? Como resolver?
2 x1  x2  3x3  1

 3x1  x 2  2 x3  2
 - x  2x - 4x  4
2
3
 1
Podemos transformar o sistema anterior na forma de matrizes:
 2  1 3   x1   1 

   
 3 1  2 . x2    2 
 1 2  4   x   4 

 3  
Para encontrar x1, x2 e x3, basta passa a matriz que multiplica as
variáveis para o outro lado da igualdade (inversa de A)
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
E o sistema linear? Como resolver?
2 x1  x2  3x3  1

 3x1  x 2  2 x3  2
 - x  2x - 4x  4
2
3
 1
Podemos transformar o sistema anterior na forma de matrizes:
 2  1 3   x1   1 

   
 3 1  2 . x2    2 
 1 2  4   x   4 

 3  
Para encontrar x1, x2 e x3, basta passa a matriz que multiplica as
variáveis para o outro lado da igualdade (inversa de A)
 x1 
1
 
 
 x2   inversa( A). 2 
x 
 4
 3
 
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Agora multiplica-se a inversa pela matriz do lado direito:
 x1 
1
 
 
x

inversa
(
A
).
 2
 2
x 
 4
 3
 
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Agora multiplica-se a inversa pela matriz do lado direito:
 x1 
1
 
 
x

inversa
(
A
).
 2
 2
x 
 4
 3
 
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Aplicação
O setor de transporte de cargas de uma central logística, que opera em São Paulo,
dispõe de 3 modelos de caminhões A, B e C. Existe uma carga com 165 toneladas para
ser remetida para o RS, outra no máximo 300 toneladas para o MT e outra com 290
toneladas para MG. A capacidade de transporte por tonelada por caminhão é:
SP-RS: Modelo A: 20 ton.
Modelo B: 12 ton.
Modelo C: 1,5 ton.
SP-MT: Modelo A: 35 ton.
Modelo B: 22 ton.
Modelo C: 3,5
SP-MG: Modelo A: 40 ton.
Modelo B: 20 ton.
Modelo C: 2,0
Quantos caminhões devem ser enviados para RS, MT e MG?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Aplicação
O setor de transporte de cargas de uma central logística, que opera em São Paulo,
dispõe de 3 modelos de caminhões A, B e C. Existe uma carga com 165 toneladas para
ser remetida para o RS, outra no máximo 300 toneladas para o MT e outra com 290
toneladas para MG. A capacidade de transporte por tonelada por caminhão é:
SP-RS: Modelo A: 20 ton.
Modelo B: 12 ton.
Modelo C: 1,5 ton.
SP-MT: Modelo A: 35 ton.
Modelo B: 22 ton.
Modelo C: 3,5
SP-MG: Modelo A: 40 ton.
Modelo B: 20 ton.
Modelo C: 2,0
Quantos caminhões devem ser enviados para RS, MT e MG?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Solução
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
PROCURA VERTICAL - PROCV
Objetivos
•Busca vertical de identificadores e valores associados em planilhas diferentes
•Localização de produtos e valores através de identificadores de tabelas
•Verificação de produtos de uma lista-chave usando planilhas variadas
Lista
mestre
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Comando : “= procv()”
Índice de busca
Tabela mestre
Número da coluna de
Busca na tabela
mestre
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo – Montagem tabela mestre
Tabela mestre
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Exemplo – Busca do nome pela
identificação
Comando procv
Tabela de busca
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Tabela mestre. Deve selecionar
toda tabela e travar as células
com F4.
Coluna 2 para mostrar nomes
Resultado
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado para nomes
Dois cliques para buscar toda a lista
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado para preços – A coluna agora é 3
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
PROCURA VERTICAL com 2 critérios
Supor a seguinte tabela mestre
Como buscar as taxas se as compras aparecem com um único critério e
a tabela com 2?
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Passo-1: transformação da tabela mestre – União de critérios unindo células
Passo-2: arrastar
Agora todos os critérios
se tornaram um único
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Passo-3: buscar taxa pelo procV
Coluna “4” onde está a taxa de administração
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Passo-4: Cálculo da taxa em R$ (venda x taxa)
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Passo-4: Cálculo da taxa em R$ (venda x taxa)
Passo-5: Cálculo da compra com valor líquido (sem as taxas)
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Passo-4: Cálculo da taxa em R$ (venda x taxa)
Passo-5: Cálculo da compra com valor líquido (sem as taxas)
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Passo-6: Resultado completo
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Procura Horizontal - PROCH
Objetivos
•Busca horizontal de identificadores e valores associados em planilhas diferentes
•Localização de linhas inteiras para cada produto associado
Busca pelo nome do campo
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Comando : “= procH()”
Diferente do procv aqui
Coloca-se o nome da
Coluna onde deseja-se
Buscar a linha
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Nome do campo
(B1)
destravado para
buscar a linha
toda
Seleciona o campo (coluna)
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Seleciona na lateral das linhas
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Travar para quando arrastar
Não mudar de coluna
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Resultado para nomes
Arrastar para buscar preços
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano
Quando se altera esses números de linha
os produtos e seus respectivos campos aparecem
nessa nova planilha
Direitos autorais: prof. Dr. Marco Antonio Leonel Caetano