FURG - Curso Excel - Manual

Prévia do material em texto

Treinamento de Excel Avançado Aplicado à Engenharia
Lean Solutions – Treinamento & Consultoria | contato@leanbr.com | www.leanbr.com 
Excel Aplicado à Engenharia
Microsoft
Parceiro
Treinamento de Excel Avançado 
 Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
SUMÁRIO 
Introdução ..................................................................................................................................................................................... 1 
Comandos do teclado .................................................................................................................................................................... 1 
Autopreenchimento ...................................................................................................................................................................... 1 
Formatação de Planilha ................................................................................................................................................................. 4 
Auto Dimensionar Coluna .............................................................................................................................................................. 4 
Cor de Preenchimento ................................................................................................................................................................... 5 
Cor da Fonte .................................................................................................................................................................................. 5 
Formatar como Contábil ................................................................................................................................................................ 6 
Formatar como Porcentagem ........................................................................................................................................................ 6 
Formatar Bordas ............................................................................................................................................................................ 7 
Introdução a Funções .................................................................................................................................................................... 7 
Introdução a Funções – Desafio .................................................................................................................................................... 8 
Autopreenchimento - Avançado ................................................................................................................................................... 9 
Insumos - ETA .............................................................................................................................................................................. 11 
Desenvolvimento Simultâneo de Planilhas ................................................................................................................................. 11 
Referência 3D .............................................................................................................................................................................. 14 
Vendas Semestral ........................................................................................................................................................................ 15 
Hiperlink ...................................................................................................................................................................................... 15 
Apontamento HRS ....................................................................................................................................................................... 17 
Auditoria de Fórmulas ................................................................................................................................................................. 17 
Cidades ........................................................................................................................................................................................ 19 
Filtrar e Classificar ....................................................................................................................................................................... 19 
Classificar ..................................................................................................................................................................................... 19 
Filtrar ........................................................................................................................................................................................... 21 
Congelar Painéis .......................................................................................................................................................................... 23 
Encomendas I ............................................................................................................................................................................... 24 
Validação de Dados – Lista Suspensa .......................................................................................................................................... 24 
Encomendas II .............................................................................................................................................................................. 28 
Validação de Dados – Lista Suspensa Condicionada ................................................................................................................... 28 
Validação de Dados – Lista Suspensa Dinâmica .......................................................................................................................... 31 
Análise Visual ............................................................................................................................................................................... 34 
Formatação Condicional .............................................................................................................................................................. 34 
É menor do que ........................................................................................................................................................................... 34 
É Maior do que ............................................................................................................................................................................ 35 
É Maior ou igual a ........................................................................................................................................................................ 37 
Texto que Contém ....................................................................................................................................................................... 38 
Valores Duplicados ...................................................................................................................................................................... 40 
Remover Duplicatas ..................................................................................................................................................................... 41 
Barra de Dados ............................................................................................................................................................................ 43 
Conjunto de Ícones ...................................................................................................................................................................... 46 
Inserir Objeto Tabela ...................................................................................................................................................................48 
Treinamento de Excel Avançado 
 Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Linha de Totais ............................................................................................................................................................................. 49 
Nova Regra .................................................................................................................................................................................. 50 
Uma Data que Ocorre .................................................................................................................................................................. 52 
Hierarquia de regras de formatação ............................................................................................................................................ 53 
Funções I ...................................................................................................................................................................................... 54 
Funções de Data e Hora ............................................................................................................................................................... 54 
Operações com Horas .................................................................................................................................................................. 58 
Funções II ..................................................................................................................................................................................... 60 
Funções de Manipulação de Texto .............................................................................................................................................. 60 
Preenchimento Relâmpago ......................................................................................................................................................... 63 
Texto para Colunas - Delimitado ................................................................................................................................................. 65 
Texto para Colunas – Largura Fixa ............................................................................................................................................... 67 
Formatação de Célula - CPF ......................................................................................................................................................... 69 
Funções III .................................................................................................................................................................................... 70 
Funções de Contagem e Soma Condicionada .............................................................................................................................. 70 
Funções de Contagem ................................................................................................................................................................. 71 
Funções de Soma Condicionada .................................................................................................................................................. 72 
Somar Produtos ........................................................................................................................................................................... 73 
Minigráficos ................................................................................................................................................................................. 74 
Case Pareto .................................................................................................................................................................................. 75 
Diagrama de Pareto – Princípio 80-20 ......................................................................................................................................... 76 
Pareto .......................................................................................................................................................................................... 76 
Gráficos combinados ................................................................................................................................................................... 78 
Gráficos combinados – Excel 2007 e 2010 .................................................................................................................................. 78 
Funções IV .................................................................................................................................................................................... 80 
Função PROCV ............................................................................................................................................................................. 80 
Função PROCH ............................................................................................................................................................................. 81 
Função PROCV – correspondência aproximada .......................................................................................................................... 81 
Funções V ..................................................................................................................................................................................... 82 
Funções ÍNDICE e CORRESP ......................................................................................................................................................... 82 
Plano de Saúde ............................................................................................................................................................................ 83 
Tabela Dinâmica .......................................................................................................................................................................... 83 
Filtro em Tabela Dinâmica ........................................................................................................................................................... 86 
Relatório de Detalhes .................................................................................................................................................................. 87 
Gráfico Dinâmico ......................................................................................................................................................................... 87 
Item Calculado ............................................................................................................................................................................. 88 
Resumo de Valores ...................................................................................................................................................................... 91 
Campo Calculado ......................................................................................................................................................................... 92 
Excluir Item Calculado ................................................................................................................................................................. 95 
Exibição de Valores ...................................................................................................................................................................... 96 
Rótulo da série ............................................................................................................................................................................. 97 
Agrupar Valores ...........................................................................................................................................................................98 
Treinamento de Excel Avançado 
 Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Dashboard com Tabela Dinâmica ................................................................................................................................................ 99 
Segmentação de Dados ............................................................................................................................................................. 100 
Linha do tempo .......................................................................................................................................................................... 100 
Investimento .............................................................................................................................................................................. 102 
Função Pagamento .................................................................................................................................................................... 102 
Teste de Hipóteses - Tabela ....................................................................................................................................................... 103 
Teste de Hipóteses – Atingir Meta ............................................................................................................................................ 104 
Cadastro de Vendas ................................................................................................................................................................... 104 
Gravação de Macro .................................................................................................................................................................... 104 
Guia Desenvolvedor ................................................................................................................................................................... 105 
Proteção de Planilha .................................................................................................................................................................. 109 
Certificado de Calibração ........................................................................................................................................................... 112 
Mala direta vinculada a uma planilha ........................................................................................................................................ 112 
Mala direta ................................................................................................................................................................................ 113 
Lista de Contatos ....................................................................................................................................................................... 114 
Importar Dados Externos – Texto .............................................................................................................................................. 114 
1
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Comandos do teclado
1. No ARQUIVO PRINCIPAL clique no botão “Introdução”
2. Selecione a planilha Comandos_teclado e execute os comandos na tabela ao lado
Autopreenchimento
2. Selecione o intervalo I7:I13
1. Selecione a planilha Autopreenchimento I
3. Digite o seu nome e pressione Ctrl + Enter
4. Selecione as células K7 e K8 (números 1 e 2). Clique com o 
botão esquerdo do mouse na alça de preenchimento e arraste 
até a célula K13.
Alça de 
preenchimento
Ctrl
2
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Autopreenchimento
Missão:
Fazer o Autopreenchimento na coluna M 
sem selecionar o intervalo com o mouse.
5. Selecione a célula M7 e na guia Página Inicial, em 
Preencher, clique em Série.
Autopreenchimento
6. Em Série em selecione Colunas e em Tipo selecione
Linear. Em Incremento digite 1 e em Limite digite 7.
Clique em OK
1
7
Resultado
3
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Autopreenchimento
7. Selecione a célula O7. Clique com o botão direito 
do mouse na alça de preenchimento e a arraste até a 
célula O13. Clique em Preencher dias da semana.
Ao clicar em “Preencher dias da semana”, o Excel
irá preencher a sequência de datas considerando
somente os dias da semana (segunda a sexta‐
feira).
8. Selecione a célula Q7. Clique com o botão direito 
do mouse na alça de preenchimento e a arraste até a 
célula Q13. Clique em Preencher meses.
Ao clicar em “Preencher meses”, o Excel irá preencher
a sequência de datas considerando o dia 31 de cada
mês. Caso o mês tenha menos de 31 dias, será
considerado o último dia daquele mês.
Autopreenchimento
9. Selecione o intervalo de S7:S8. Clique com o botão 
direito do mouse na alça de preenchimento e arraste 
até a célula S13. Clique em Preencher dias da 
semana.
Ao clicar em “Preencher dias da semana”, o Excel irá
preencher a sequência com o dia 23 de cada mês. Caso
o dia 23 de um determinado mês não seja um dia útil,
será considerado o último dia útil anterior ao dia 23.
Resultado
4
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatação de Planilha
1. Selecione a planilha “Formatação”
Auto Dimensionar Coluna
2. Posicione o cursor entre os rótulos de coluna B e C e 
clique duas vezes com o botão esquerdo
3. Posicione o cursor entre os rótulos de coluna C e D 
e clique duas vezes com o botão esquerdo
5
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Cor de Preenchimento
4. Selecione o intervalo de A1:D1
5. Na guia PÁGINA INICIAL, em Cor de Preenchimento, em Cores Padrão, selecione a cor Azul‐Escuro
Cor da Fonte
6. Ainda com o intervalo de A1:D1 selecionado
7. Na guia PÁGINA INICIAL, em Cor da Fonte, em Cores do Tema, selecione a cor Branco
6
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatar como Contábil
8. Selecione o intervalo de B2:B18
9. Na guia PÁGINA INICIAL, em Formato de Número, clique em Contábil
10. Repita o procedimento para o intervalo de D2:D18
Formatar como Porcentagem
11. Selecione o intervalo de C2:C18
12. Na guia PÁGINA INICIAL, em Formato de Número, clique em Porcentagem
7
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatar Bordas
13. Selecione o intervalo de A1:D18
14. Na guia PÁGINA INICIAL, em Bordas, clique 
em Todas as Bordas
15. Na guia EXIBIÇÃO, em Mostrar, desmarque a opção Linhas 
de Grade
Resultado:
Introdução a Funções
Funções
SOMA(núm1; [núm2]; ...) – Retorna a soma de um intervalo passado para a função
SE(teste_lógico; [valor_se_verdadeiro]; [valor_se_falso]) – Realiza um teste lógico e caso a resposta seja verdadeira realiza
uma ação, caso contrário realiza outra
Referência Absoluta e Relativa
Exemplo:
• C5 – Referência relativa de coluna e linha
• C$5 – Referência relativa de coluna e absoluta de linha
• $C5 – Referência absoluta de coluna e relativa de linha
• $C$5 – Referência absoluta de coluna e linha
Ao arrastar C5 C$5 $C5 $C$5
Para baixo Altera índice de linha Não altera índice de linha Altera índice de linha Não altera índice de linha
Para direita Altera índice de coluna Altera índice de coluna Não altera índice de 
coluna
Não altera índice de coluna
Observação: A tecla F4 é o atalho
para alternar entre referência relativa
e absoluta
E(lógico1; [lógico2]; ...) – Retorna verdadeirose todos os testes lógicos forem verdadeiros, senão retorna falso
OU(lógico1; [lógico2]; ...) – Retorna verdadeiro se ao menos um dos testes lógicos for verdadeiro, senão retorna falso
8
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Introdução a Funções
1. Clique na planilha “Introdução Fórmulas”
2. Resolva as operações
=C4+E4
=SOMA(D12:F14)
=SE(K5>M5;”Sim”;”Não”)
=E(K11>L11;K11>M11)
=OU(K18>M18;L18>M18)
=U7*V$4
3. Selecione o intervalo de D12:F14
Auto Cálculo
Introdução a Funções ‐ Desafio
1. Clique na planilha “Multiplicação”
2. Multiplique os números digitando a fórmula somente na célula  C5, para as demais utilize o 
Autopreenchimento. DICA: Utilize os conceitos de referência absoluta e/ou relativa de linha e/ou coluna
=C$4*$B5
9
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Autopreenchimento ‐ Avançado
1. Selecione a planilha Autopreenchimento II
Autopreenchimento ‐ Avançado
2. Selecione a célula I2 e pressione Ctrl + ; para inserir a 
data atual.
Ctrl ;
Ctrl :
3. Selecione a célula I3 e pressione Ctrl + : para inserir a 
hora atual.
4. Tecle Ctrl + G Ctrl g
5. Em referência, digite I6:I20 e clique em OK.
I6:I20
10
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Autopreenchimento ‐ Avançado
Ctrl g
7. Clique em Especial...6. Com o intervalo I6:I20 selecionado, tecle Ctrl + g.
Autopreenchimento ‐ Avançado
8. Selecione Em Branco e clique em OK 9. Sem clicar em nada digite =I6 e tecle Ctrl + Enter.
=I6
Resultado
11
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Autopreenchimento ‐ Avançado
10. Selecione o intervalo H6:H7 e dê um duplo clique na 
alça de preenchimento com o botão esquerdo do 
mouse. Resultado
2x
Desenvolvimento Simultâneo de Planilhas
1. No ARQUIVO PRINCIPAL clique no botão “Insumos – ETA”
2. Clique na planilha “Jan”
3. Segure a tecla Shift e clique na planilha “Trimestre”
Todas as planilhas selecionadas
Resultado:
12
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Desenvolvimento Simultâneo de Planilhas
4. Construa a tabela como mostra a figura abaixo
Soda Cáustica
Cloro
Barrilha
Coagulante
Semana 1 Semana 2 Semana 3 Semana 4 Total
B4
B5
B6
B7
C3 D3 E3 F3 G3
Desenvolvimento Simultâneo de Planilhas
5. Selecione o intervalo de G4:G7 e na guia Página Inicial clique no botão de Auto Soma
Resultado:
13
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Desenvolvimento Simultâneo de Planilhas
6. Selecione o intervalo de C4:G7, clique com o botão direito sobre a seleção e em Formatar 
células
Desenvolvimento Simultâneo de Planilhas
7. Clique em Personalizado e em tipo digite 0,00“ kg”
0,00“ kg”
Resultado8. Clique em OK
Selecione a célula C4 e observe a barra de fórmulas. Note que
o conteúdo da célula continua sendo 35 e não 35,00 kg. A
formatação criada funciona apenas como uma máscara para
melhor visualização dos dados.
14
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Desenvolvimento Simultâneo de Planilhas
9. Pressione a tecla Shift e clique na planilha "Jan" para desfazer a seleção múltipla de planilhas
Somente a planilha "Jan" selecionada
10. Clique nas outras abas e perceba que as alterações foram feitas em todas as 
planilhas
Referência 3D
1. Clique na planilha Trimestre
2. Clique na célula C4, digite =SOMA(Jan:Mar!C4) e pressione Enter
3. Faça as somas para as demais células utilizando o 
autopreenchimento
=SOMA(Jan:Mar!C4)
Ao digitar Jan:Mar!C4 estamos nos
referindo a todas as células C4 do
intervalo de planilhas de Jan até Mar
(Jan, Fev e Mar).
15
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Hiperlink
1. No ARQUIVO PRINCIPAL clique no botão “Vendas Semestral”
2. Selecione a planilha “vendas”
Hiperlink
4. Clique em Colocar neste documento
D187
5. Em Digite a referência de célula, digite D187 e clique em OK
3. Clique com o botão direito sobre a célula F7 e 
clique em Hiperlink
6. Clique no Hiperlink criado
16
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Hiperlink
7. Clique com o botão direito sobre a célula F187 e 
clique em Hiperlink
8. Clique em Colocar neste documento, clique em gráfico e clique em OK
9. Clique no Hiperlink criado
Hiperlink
10. Clique com o botão direito sobre a 
célula B2 e clique em Hiperlink
11. Clique em Página da Web ou arquivo, clique em Pasta Atual, 
selecione o arquivo Próximo Passo e clique em OK
12. Clique no Hiperlink criado
17
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Hiperlink
13. Clique com o botão direito sobre 
a célula B2 e clique em Hiperlink
14. Clique em Página da Web ou arquivo, em Endereço 
digite o site da Lean Solutions e clique em OK
http://www.leansolutions.com.br
15. Clique no Hiperlink criado
http://www.leansolutions.com.br
Observação: Caso não esteja conectado 
a internet o hiperlink exibirá um erro. 
Auditoria de Fórmulas
1. No ARQUIVO PRINCIPAL, clique no botão “Apontamento (Hrs)”. Selecione a célula L13
2. Na guia Fórmulas clique em Rastrear Precedentes
18
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Auditoria de Fórmulas
Indica com quais células compõe aquela fórmula
Auditoria de Fórmulas
3. Na guia Fórmulas, clique em Avaliar Fórmula
4. Clique em Avaliar e perceba a fórmula ser resolvida passo a passo
19
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Filtrar e Classificar
1. No ARQUIVO PRINCIPAL, clique no botão “Cidades”
2. Selecione a planilha “Censo”
Organizar as informações da planilha por:
• Estado em Ordem Alfabética;
• População em ordem crescente.
Missão:
Classificar
3. Selecione o intervalo de A1:D53
4. Na guia DADOS, clique em Classificar
20
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Classificar
5. Em Classificar por, selecione UF
6. Clique em Adicionar Nível
Observação: É importante manter a opção Meus dados contêm cabeçalhosmarcada, 
uma vez que a linha de cabeçalho (primeira linha) foi selecionada. Caso a opção não 
esteja marcada, o Excel entenderá que a primeira linha contém informação e esta 
também será classificada. 
Classificar
7. Em E depois por, selecione População
8. Clique em OK
21
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Classificar
Resultado:
Filtrar
9. Ainda com o intervalo de A1:D53 selecionado
10. Na guia DADOS, clique em Filtro
Exibir apenas as cidades do estado de
Minas Gerais com população entre
600.000 e 2.000.000 de habitantes.
Missão:
22
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Filtrar
11. Clique no Filtro UF e desmarque a opção 
Selecionar Tudo (para desmarcar todos os itens 
simultaneamente).
12. Em seguida, selecione somente o estado de 
Minas Gerais
13. Clique em OK
Filtrar
14. Clique no filtro População 
15. Em Filtros de Número, selecione Está 
Entre...
23
Treinamento de Excel Avançado
Lean Solutions –Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Filtrar
16.  Em é maior ou igual a, digite 600.000
17. Em é menor ou igual a, digite 2.000.000
18. Clique em OK
600.000
2.000.000
Resultado:
Congelar Painéis
2. Selecione a célula B2
Ao clicar em congelar painéis será congelada a(s) coluna(s) a esquerda da célula
selecionada e a(s) linha(s) acima da célula selecionada.
1. Selecione a planilha “Historico_Populacao”
3. Na guia EXIBIÇÃO, em Congelar Painéis, clique em Congelar Painéis
24
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa
1. No ARQUIVO PRINCIPAL clique no botão “ Encomendas I”
• Como essa é  uma planilha de Encomendas, a coluna Previsão de Entrega somente deve aceitar datas 
futuras (posteriores ao dia de hoje);
• Uma encomenda pode ter prioridade normal ou urgente;
• A coluna Equipamento somente deve ser preenchida com equipamentos constantes na planilha Dados.
Observações:
Validação de Dados – Lista Suspensa
2. Na planilha “Encomendas”, selecione toda a 
coluna B a partir da célula B7
3. Na guia Dados, clique em Validação de Dados e 
em Validação de Dados
4. Na guia Configurações, configure os campos Permitir, Dados 
e Data de Início
Data
é maior do que
=hoje()
HOJE() – Retorna a data atual
Função
25
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa
5. Na guia Mensagem de entrada, configure os campos Título e Mensagem de Entrada
DATAS 
VÁLIDAS
A data inserida deve ser 
posterior ao dia de hoje
Validação de Dados – Lista Suspensa
6. Na guia Alerta de erro, configure os campos Estilo, Título e 
Mensagem de erro e clique em OK
Parar
DATA 
INVÁLIDA
Insira uma data 
posterior ao dia de 
hoje
Parar: Exibe um janela informando que o valor
inserido na célula é inválido e não permite a
inserção do valor inválido;
Aviso: Exibe um janela informando que o valor
inserido na célula é inválido, mas permite a
inserção do valor;
Informação: Exibe um janela informando que o
valor inserido na célula é inválido, mas permite
a inserção do valor.
26
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa
7. Na planilha “Encomendas”, selecione toda a coluna D a partir da célula D7
8. Na guia Dados, clique em Validação de Dados e em Validação de Dados
Validação de Dados – Lista Suspensa
9. Na guia Configurações, configure os campos Permitir, Fonte e clique em OK
Lista
Normal;Urgente
27
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa
10. Na planilha “Encomendas”, selecione toda a coluna E a partir da célula E7
11. Na guia Dados, clique em Validação de Dados e em Validação de Dados
Validação de Dados – Lista Suspensa
12. Na guia Configurações, configure os campos Permitir
Lista
13. No campo Fonte, selecione os equipamentos 
(A2:A8) da planilha “Dados” e clique em OK
Resultado
=Dados!$A$2:$A$8
OBS: No Excel 2007 não é possível selecionar o 
intervalo com o mouse, neste caso, digite o 
intervalo como indicado.
28
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa Condicionada
1. No ARQUIVO PRINCIPAL clique no botão “Encomendas II ”
Validação de Dados – Lista Suspensa Condicionada
2. Na planilha “Encomendas”, selecione toda a coluna C a partir da célula C7
3. Na guia Dados, clique em Validação de Dados e 
em Validação de Dados
29
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa Condicionada
4. Na guia Configurações, configure os campos 
Permitir, Fonte e clique em OK
Lista
dados;energia
dados;energia
5. Na planilha “BaseDados”, selecione o intervalo de A2:A5
6. Na guia FÓRMULAS clique em Gerenciador de Nomes
dados
Validação de Dados – Lista Suspensa Condicionada
7. Clique em Novo 8. Em Nome, dê o nome “dados” e clique em OK
Perceba que o campo “Refere‐se a” já faz
referência ao intervalo previamente
selecionado.
dados
=BaseDados!$A$2:$A$5
30
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa Condicionada
9. Clique em Novo
10. Em Nome, dê o nome “energia”
=BaseDados!$B$2:$B$5
11. Em Refere‐se, selecione o intervalo 
B2:B5
12. Clique em OK
energia
13. Feche o Gerenciador de Nomes
Validação de Dados – Lista Suspensa Condicionada
14. Na planilha “Encomendas”, selecione toda a coluna D a partir da 
célula D7
15. Na guia Dados, clique em Validação de Dados e em Validação de 
Dados
31
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa Condicionada
16. Na guia Configurações, configure os campos 
Permitir, Fonte e clique em OK
Lista
=INDIRETO(C7)
Vídeo – Função indireto
Observação: Caso ocorra a mensagem mostrada, clique em Sim
Observação: Para que o exemplo funcione
corretamente, a referência C7 não pode estar travada
em linha.
INDIRETO(texto_ref) – Retorna uma referência
indicada por um valor de texto
Função
Resultado
Validação de Dados – Lista Suspensa Dinâmica
1. Selecione a planilha EncomendasProd
• Note que as colunas Previsão de Entrega e Prioridade já possuem validação de dados;
• Missão: Criar na coluna Produto da planilha EncomendasProd uma lista suspensa dinâmica, 
ou seja, que cresça a medida que novos produtos forem adicionados à planilha ListaProd.
Observações:
32
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa Dinâmica
2. Selecione o intervalo de A1:A8 da planilha ListaProd e 
na guia INSERIR clique em Tabela
3. Clique em OK
Note que o intervalo de dados foi
convertido em um objeto do tipo
Tabela. Uma característica
importante de uma tabela é sua
capacidade em aumentar de
tamanho sempre que novos dados
forem adicionados.
Resultado
Validação de Dados – Lista Suspensa Dinâmica
4. Selecione o intervalo de A2:A8 da planilha 
ListaProd.
5. Na guia Fórmulas, clique em Gerenciador de Nomes
6. Clique em Novo
33
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Validação de Dados – Lista Suspensa Dinâmica
7. Em Nome, dê o nome lista_produtos e 
clique em OK
OBS: Perceba que como selecionamos toda a 
coluna Produto da tabela que criamos (Tabela 
1), a referência ao intervalo ficou na forma de 
Tabela1[Produto].
lista_produtos
=Tabela1[Produto]
8. Feche o Gerenciador de Nomes
9. Na planilha “EncomendasProd”, selecione toda a coluna E a 
partir da célula E7
10. Na guia Dados, clique em Validação de Dados e em Validação 
de Dados
Validação de Dados – Lista Suspensa Dinâmica
11. Na guia Configurações, configure o campo Permitir como 
Lista, em Fonte digite =lista_produtos e em seguida clique 
em OK.
Lista
=lista_produtos
12. Adicione o Produto H na planilha ListaProd.
Perceba que o Produto H foi
adicionado automaticamente a
lista suspensa da planilha
EncomendasProd.
34
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatação Condicional
1. No ARQUIVO PRINCIPAL clique no botão “Análise Visual”
2. Selecione a planilha “Formatação Condicional 1”
Pergunta
Quais vendedores venderam abaixo da
meta?
É menordo que
3. Selecione o intervalo de D4:D21
4. Na guia PÁGINA INICIAL, em Formatação Condicional, em Realçar Regras das Células, clique em É Menor do 
que...
35
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
É menor do que
5. Em “Formatar células que são MENORES DO QUE:” faça uma 
referência à célula F4
6. Em com selecione Preenchimento Vermelho Claro e Texto Vermelho 
Escuro
7. Clique em OK
=$F$4
Resultado:
Atente em travar os índices de linha e 
coluna da célula F4
É Maior do que
8. Selecione o intervalo de D4:D21
9. Na guia PÁGINA INICIAL, em Formatação Condicional, em Realçar 
Regras das Células, clique em É Maior do que...
Quais vendedores atingiram a Meta?
Pergunta
36
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
É Maior do que
10. Em Formatar células que são MAIORES DO QUE: faça uma referência à célula F4
11. Em com selecione Preenchimento Verde e Texto Verde Escuro
12. Clique em OK
=$F$4
Atente em travar os índices de linha e coluna 
da célula F4
É Maior do que
Resultado:
Perceba que Lucas vendeu um valor igual a meta, 
porém a célula não está pintada de Verde.
Está Correto ?
Como Corrigir ?
Alterando a regra de “É Maior do que” para “É Maior 
ou Igual a” 
37
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
É Maior ou igual a
13. Na guia PÁGINA INICIAL, em Formatação Condicional, clique em Gerenciar Regras
14. Em Mostrar regras de formatação para selecione Esta Planilha
15. Selecione a regra verde e em seguida clique em Editar Regra 
É Maior ou igual a
38
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
É Maior ou igual a
16. Em Formatar apenas células  com selecione é maior ou igual a
17. Clique em OK
18. Feche o Gerenciador de Regras
Resultado:
Texto que Contém
19. Selecione o intervalo de C4:C21
20. Na guia PÁGINA INICIAL, em Formatação Condicional, em Realçar 
Regras das Células, clique em Texto que Contém... Destacar as lojas de mesmo estado
Problema
39
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Texto que Contém
21. Em Formatar célula que contêm o texto: digite SP
22. Em com selecione Formato Personalizado...
SP
Texto que Contém
23. Clique na guia Preenchimento e selecione a cor Azul
24. Clique em OK
25. Repita o procedimento para MG como uma cor de 
preenchimento a sua escolha.
Resultado:
40
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatação Condicional
1. Selecione a planilha “Formatação Condicional 2”
Existem itens duplicados nessa lista de 
compras?
Pergunta
Valores Duplicados
2. Selecione o intervalo de A3:D20
3. Na guia PÁGINA INICIAL, em Formatação Condicional, em Realçar Regras das Células, clique em Valores Duplicados
41
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Valores Duplicados
4. Em Formatar células que contêm selecione Duplicados 
5. Em valores com selecione Preenchimento Vermelho Claro e Texto Vermelho Escuro
6. Clique em OK Resultado:
Remover Duplicatas
7. Ainda com o intervalo de A3:D20 selecionado
8. Na guia DADOS, clique em Remover Duplicatas
Remover da lista os itens duplicados
Problema
42
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Remover Duplicatas
9. Clique em OK
11. Clique em OK
Todas as colunas foram marcadas uma vez que 
os itens efetivamente duplicados são aqueles 
que possuem Quantidade, Fornecedor e Preço 
iguais.
Remover Duplicatas
Resultado: Antes:
Depois:
Conclusão:
Evitou‐se um prejuízo de 
R$190,00!!
43
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatação Condicional
1. Selecione a planilha “Formatação Condicional 3”
Como variaram as vendas do Produto A 
ao longo do mês de janeiro?
Pergunta
Barra de Dados
2. Selecione o intervalo de B2:B22
3. Na guia PÁGINA INICIAL, em Formatação Condicional, em Barra de Dados, clique em algum tipo de preenchimento. 
Sugestão: Barra de Dados Verde
44
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Barra de Dados
4. Ainda com o intervalo de B2:B22 selecionado
5. Na guia PÁGINA INICIAL, em Formatação Condicional, clique em Gerenciar Regras...
6. Selecione a regra Barra de Dados e em seguida clique em Editar Regra 
Barra de Dados
45
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Barra de Dados
7. Em Tipo, na opção Mínimo, selecione Valor
Mais Baixo e na opção Máximo, selecione
Valor Mais Alto
8. Clique em Ok
Barra de Dados
Resultado:
Perceba que os valor das vendas caiu
gradativamente até o final do mês.
Podemos chegar a alguma conclusão?
Pergunta
Ao final do mês os consumidores
compram menos
Conclusão
46
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatação Condicional
1. Selecione a planilha “Formatação Condicional 4”
Destacar na coluna Semestral os produtos que:
• Não atingiram as metas;
• Atingiram a meta de aceitável;
• Atingiram a meta alvo
Missão:
Conjunto de Ícones
2. Selecione o intervalo de H8:H12
3. Na guia PÁGINA INICIAL, em Formatação Condicional, em Conjunto de Ícones, escolha a primeira opção
47
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Conjunto de Ícones
4. Ainda com o intervalo de H8:H12 selecionado
5. Na guia PÁGINA INICIAL, em Formatação Condicional, clique em Gerenciar Regras...
6. Selecione a regra e em seguida clique em Editar Regra 
Conjunto de Ícones
48
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Conjunto de Ícones
7. Em Tipo selecione Número para as
Setas Verde e Amarela
8. Para a Seta verde faça uma referência
a célula F4
9. Para a Seta Amarela faça uma
referência a célula F3
10. Clique em OK
=$F$4
=$F$3
Inserir Objeto Tabela
1. Selecione o intervalo de A7:H12
2. Na guia INSERIR clique em Tabela 3. Deixe Minha tabela tem cabeçalhos marcado e 
clique em OK
49
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Linha de Totais
4. Clique com o botão direito sobre a seleção, em Tabela e em Linha de Totais
5. Clique nas células da linha Total e selecione a opção desejada
Formatação Condicional
1. Selecione a planilha “Formatação Condicional 5”
• Destacar em verde toda a linha das contas
que possuem o status Pago;
• Destacar em vermelho a data das contas
que vencem na próxima semana.
Missões:
50
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Nova Regra
3. Na guia PÁGINA INICIAL, em Formatação Condicional, clique em Nova Regra..
2. Selecione o intervalo de A4:D23
Nova Regra
4. Em “Selecione um Tipo de Regra:”, clique em Usar 
uma fórmula para determinar quais células devem ser 
formatadas
5. Em “Formatar valores em que esta fórmula é 
verdadeira:” digite =$D4=“Pago”
6. Clique em Formatar...
=$D4=“Pago”
51
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.brNova Regra
7. Na aba Fonte, em Cor, selecione Preto 8. Na aba Preenchimento, selecione a quinta cor (Verde)
9. Clique em OK
Nova Regra
10. Clique em OK Resultado:
52
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Uma Data que Ocorre
11. Selecione o intervalo de C4:C23
12. Na guia PÁGINA INICIAL, em Formatação Condicional,  em Realçar Regras das Células, clique em Uma Data que 
ocorre...
Uma Data que Ocorre
13. Em Formatar célula que contêm uma data que ocorre
selecione Próxima Semana
14. Em com selecione Preenchimento Vermelho Claro e Texto 
Vermelho Escuro
15. Clique em OK
Resultado:
Observe que a Conta 7 já foi paga porém ainda 
está com a coluna do vencimento pintada na cor 
vermelho. Como corrigir isso?
53
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Uma Data que ocorre
16. Na guia PÁGINA INICIAL, em Formatação Condicional, clique em Gerenciar Regras...
Hierarquia de regras de formatação
17. Em Mostrar regras de formatação para: selecione Esta Planilha
18. Clique na regra verde
19. Clique no botão seta para cima
20. Clique em OK
Resultado:
54
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Data e Hora
1. No ARQUIVO PRINCIPAL clique no botão “ Funções I”
Funções:
=HOJE() – Retorna a data de hoje
=AGORA() – Retorna a data e a hora atuais
=DIA(data) – Retorna o dia da data passada como parâmetro
=MÊS(data) – Retorna o mês da data passada como parâmetro
=ANO(data) – Retorna o ano da data passada como parâmetro
=DIA.DA.SEMANA(data) – Retorna dia da semana, onde 1 é 
domingo e 7 sábado
=DATADIF(data_inicial; data_final; unidade) – Calcula a diferença 
entre duas datas, podendo retornar em dias meses ou anos
Unidade:
• “Y” – O número de anos completos no período
• “M” – O número de meses completos no período
• “D” – O número de dias no período
Observação: A função DATADIF não aparece na indicação
Autocompletar do Excel, entretanto a função existe e pode ser utilizada.
Exemplo:   =DIA(C2)     ou      =DIA(“13/09/2020”)
Funções de Data e Hora
2. Utilizando as funções aprendidas, resolva o exemplo
=HOJE()
=AGORA()
=DIA(C2)
=MÊS(C2)
=ANO(C2)
=DIA.DA.SEMANA(C8)
=DATADIF(C8;C2;”Y”)
55
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Data e Hora
3. Clique com o botão direito sobre a célula C3 
e em Formatar células
4. Clique em Personalizado e escolha a 
formatação de hh:mm:ss
hh:mm:ss
5. Clique em OK
Funções de Data e Hora
6. Clique com o botão direito sobre a célula C9 
e em Formatar células
7. Clique em Personalizado e em Tipo digite dddd
dddd
8. Clique em OK
Observação:
Tipo: Exemplo:
d 1 a 7
dd 01 a 07
ddd dom, seg, ter...
dddd domingo, segunda‐feira, terça‐feira...
56
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Data e Hora
9. Clique na planilha “Dias_Úteis”
Determinar quantos dias úteis teve o ano de 2015,
descontando:
• Sábados, Domingos e Feriados;
• Domingos e Feriados.
Missão:
Funções de Data e Hora
DIATRABALHOTOTAL(data_inicial; data_final; [feriados]) –
Retorna o número de dias úteis no intervalo entre as datas 
inicial e final. Descontam‐se também, os feriados caso estes 
sejam passados para função.
Funções
DIATRABALHOTOTAL.INTL(data_inicial; data_final; [fins de 
semana]; [feriados])– Retorna o número de dias úteis inteiros 
entre duas datas usando parâmetros para indicar quais e 
quantos dias são dias de fim de semana. Dias de fim de 
semana e quaisquer dias especificados como feriados não são 
considerados como dias úteis.
*Existe a partir da versão 2010 do Excel
Número de fim de semana Dias de fim de semana
1 ou omitido Sábado, domingo
2 Domingo, segunda‐feira
3 Segunda‐feira, terça‐feira
4 Terça‐feira, quarta‐feira
5 Quarta‐feira, quinta‐feira
6 Quinta‐feira, sexta‐feira
7 Sexta‐feira, sábado
11 Domingo apenas
12 Segunda‐feira apenas
13 Terça‐feira apenas
14 Quarta‐feira apenas
15 Quinta‐feira apenas
16 Sexta‐feira apenas
17 Sábado apenas
(Os parâmetros na sintaxe de uma função que se encontram 
entre colchetes, são parâmetros opcionais.)
57
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Data e Hora
10. Utilizando as funções aprendidas, responda as perguntas
=DIATRABALHOTOTAL(C1;C2;E5:E16)
=DIATRABALHOTOTAL.INTL(C1;C2;11;E5:E16)
Funções de Data e Hora
10. Clique na planilha “Pagamento”
Função
DIATRABALHO(data_inicial; dias; [feriados]) – Definida uma data inicial, retorna uma data antes ou após a quantidade de 
dias úteis informados para a função. Os dias úteis excluem fins de semana e/ou feriados.
Sabendo que o valor do pagamento de um boleto “cai na conta” 5 dias úteis após a data que foi realizado o pagamento, a 
partir da data do pagamento do boleto, encontre a data em que o pagamento  “cairá” na conta.
Missão
=DIATRABALHO(C3;5;$F$3:$F$14)
58
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Data e Hora
12. Clique na planilha “Projeto”
Função
DIATRABALHO.INTL(data_inicial; dias; [fins_de_semana]; [feriados]) – Definida uma data inicial, retorna uma data antes ou 
após a quantidade de dias úteis informados para a função. O parâmetro de fim de semana indicam quais e quantos dias são 
de fim de semana. *Existe a partir da versão 2010 do Excel
Sabendo que em um certo projeto considera‐se apenas o domingo como fim de semana, calcule a data de Término das 
tarefas com base nada data de Início e na Duração (Dias Úteis).
Missão
=DIATRABALHO.INTL(D3;E3;11;$H$3:$H$14)
Operações com Horas
14. Clique na planilha “Folha_de_Ponto”
=E8‐D8+G8‐F8
=SE(H8>$D$4;H8‐$D$4;"00:00")
=SOMA(H8:H14)‐D18
=SOMA(I8:I14)
15. Calcule: 
a) A quantidade de horas
trabalhadas por dia;
b) A quantidade de horas
extras por dia;
d) A quantidade de horas 
normais trabalhadas na 
semana. 
c) A quantidade de horas 
extras trabalhadas na 
semana; 
59
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Operações com Horas
Funcionou?????
16. Clique com o botão direito na célula D17 e em 
Formatar células
17. Em Tipo digite [hh]:mm clique em OK
[hh]:mm
O total correto, seriam 38:00
[ANALISANDO]: 38:00 – 14:00 = 24:00, ou seja 
estão faltando 24:00 que equivale a 1 dia
Mas esse fato é um questão de formatação para 
exibição, o verdadeiro valor que está na célula, 
sempre esteve correto.
Por padrão, ao atingir 24:00 horas, o Excel zera 
as horas e incrementa 1 dia
Curiosidade
d:hh:mm
Operações com Horas
60
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Operações com Horas
Observação
Tipo Descrição
[ss] Acumula segundos
[mm]:ss Acumula minutos
[hh]:mm:ss Acumula horas
18. Clique na célula F17 e observe na barra de fórmulas
• Você concorda que 38:00 (célula D17) x R$45,00 (célula E17) é igual a R$1.710,00??
• Porque precisamos do 24 na fórmula (=D17*E17*24)???
Perguntas:
Vídeo – Manipulando data e hora
Funções de Manipulação de Texto
1. No ARQUIVO PRINCIPAL clique no botão “Funções II ”
2. Clique na planilha Identidade
61
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Manipulação de Texto
Funções
CONCATENAR(texto1; texto2; ...) – Retorna um texto concatenado de todos os parâmetros passados
Observação: O operador & faz a mesma função que CONCATENAR Ex: CONCATENAR(“José”;”Silva”)= “José”&“Silva”
LOCALIZAR(texto_procurado; no_texto; [num_inicial]) – Retorna a posição de um texto procurado dentro de um 
texto analisado (OBS: Esta função também é encontrada com o nome PROCURAR)
Exemplo:                          LOCALIZAR(“a”;”banana”) = 2                                       LOCALIZAR(“a”;”banana”;3) = 4
ESQUERDA(texto; [num_caractl]) – Retorna o número de caracteres desejado de um texto, contando da esquerda 
para direita
EXT.TEXTO(texto; num_inicial; num_caractl) – Retorna o número de caracteres desejado de um texto, contando da 
esquerda para direita, a partir de uma determinada posição
Exemplo:                          EXT.TEXTO("Joaquina";5;3) = uin
DIREITA(texto; [num_caractl]) – Retorna o número de caracteres desejado de um texto, contando da direita para 
esquerda
Funções de Manipulação de Texto
3. Utilizando as funções aprendidas, resolva o exemplo
=CONCATENAR(D4;" ";D5)
=LOCALIZAR("‐";D6)
=ESQUERDA(D6;2)
=EXT.TEXTO(D6;4;2)
=DIREITA(D6;3)
62
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Manipulação de Texto
1. Clique na planilha Telegrama
Missões:
• Determinar o número de caracteres do telegrama;
• Retirar o excesso de espaços em branco do telegrama e determinar a novo número de caracteres.
Funções de Manipulação de Texto
Funções
NÚM.CARACT(texto1) – Retorna o número de caracteres de uma cadeia de texto
Exemplo: NÚM.CARACT(“Abacate”) = 7
ARRUMAR(texto1) – Remove todos os espaços do texto exceto os espaços únicos entre palavras.
Exemplo: ARRUMAR(“  Lean   Solutions”) = “Lean Solutions”
=NÚM.CARACT(B3) =ARRUMAR(B3)
=NÚM.CARACT(E3)
Observação: Perceba que o caractere de quebra de linha (Enter) também é contabilizado pela função NÚM.CARACT. Para 
fazer uma quebra de linha dentro de uma célula deve ser utilizada a combinação de teclas Alt+Enter.
63
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Preenchimento Relâmpago
1. Clique na planilha Preenchimento Relâmpago
Missões:
• Transformar os números de telefone para a seguinte formatação: (XX) XXXXX‐XXXX;
• Na coluna H, colocar os nomes no formato (Sobrenome, Primeiro Nome Inicial do Nome do Meio).
O Preenchimento Relâmpago só 
está disponível a partir da versão 
2013 do Excel.
Preenchimento Relâmpago
2. Na célula C3, digite o número de telefone já no 
formato desejado.
(11) 12345‐6789
3. Na célula C4, comece a digitar o número de
telefone no formato desejado e observe o
comportamento do Excel. Em Seguida, tecle Enter.
Observação: Perceba que o Excel reconhece o
padrão de digitação e entende que você deseja
aplica‐lo para toda a coluna C
64
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Preenchimento Relâmpago
4. Na célula H4, digite o nome conforme o padrão: 
Ferreira, Marta C.. Em seguida, tecle Enter.
5. Na guia Dados, clique em Preenchimento Relâmpago
Ferreira, Marta C.
Preenchimento Relâmpago
Resultado
Observação: Perceba que o Excel completou a coluna H 
parcialmente, uma vez que ele não sabe o que fazer no 
caso em que a célula do nome do meio está vazia.
6. Na célula H6, digite o nome conforme o padrão: 
Brasil, Antônio. Tecle Enter.
Brasil, Antônio
Observação: Se após teclar Enter o Excel não preencher
automaticamente as células vazias restantes, vá até a
guia DADOS e clique em Preenchimento Relâmpago.
65
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Preenchimento Relâmpago
Resultado:
Texto para Colunas ‐ Delimitado
1. Clique na planilha Codigo_Produto
Dividir a Coluna B em outras 4 
colunas, de modo que as divisões 
sejam feitas a cada barra 
encontrada
2. Selecione o intervalo de B4:B22
Missão:
66
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Texto para Colunas ‐ Delimitado
3. Na Guia DADOS, clique em Texto para Colunas 4. Selecione Delimitado e clique em Avançar.
Texto para Colunas ‐ Delimitado
5. Em Delimitadores, selecione Outros e digite /. 6. Em Destino, digite $C$4. Clique em Concluir.
/ $C$4
67
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Texto para Colunas ‐ Delimitado
Resultado
Texto para Colunas – Largura Fixa
1. Clique na planilha Número_Chassi Missão:
• Dividir a Coluna B em outras 3 
colunas.
2. Selecione o intervalo de B4:B19
3. Na Guia DADOS, clique em Texto para Colunas
68
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Texto para Colunas ‐ Delimitado
4. Selecione Largura fixa e clique em Avançar. 5. Crie os delimitadores (setas) conforme indicado e clique 
em Avançar.
Texto para Colunas ‐ Delimitado
6. Em Destino, digite $C$4 e clique em Concluir. Resultado
$C$4
69
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatação de Célula ‐ CPF
1. Clique na planilha CPF Missão:
Formatar a Coluna F no formato 
tradicional de CPF:
Exemplo: 123.456.789‐12
Formatação de Célula ‐ CPF
2. Selecione o intervalo de F4:F18 3. Na guia Número, em Categoria, clique 
em Personalizado.
4. Em Tipo, digite: 000”.”000”.”000”‐”00
5. Clique em OK.
000”.”000”.”000”‐”00
70
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Formatação de Célula ‐ CPF
Resultado:
Observação: Ao selecionarmos um CPF,
podemos verificar observando na barra de
fórmula que os pontos e o traço são apenas
uma formatação e exibição, eles não são um
conteúdo da célula.
Funções de Contagem e Soma Condicionada
O que é um TAP?
TAP é a abreviação de Teste de Aceitação e Performance;
Todo produto fabricado passa por um TAP, seja de forma individual ou seja por lote;
Pois o fabricante deseja atestar se o produtos fabricados  atendem aos requisitos de qualidade.
As funções de contagem são de extrema importância;
Elas são a base para que possamos transformar dados em informação;
Exemplo de testes por lote
71
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Contagem
2. Clique na planilha “TAP”
1. No ARQUIVO PRINCIPAL clique no botão “Funções III ”
Funções de Contagem
Funções
CONT.VALORES(valor1; [valor2]; ...) – Retorna a contagem de quantas células de um intervalo(s) não estão vazias
CONT.NÚM(valor1; [valor2]; ...) – Retorna a contagem de quantas células de um intervalo(s) contém número
CONT.SE(intervalo; critério) – Retorna a contagem de quantas células de um intervalo(s) atendem a um critério
estabelecido
CONTAR.VAZIO(intervalo) – Retorna a contagem de quantas células de um intervalo(s) são vazias
CONT.SES(intervalo_critérios1; critérios1; [intervalo_critérios2]; [critérios2];...) – Retorna a contagem de quantas
células de um intervalo(s) atendem a mais de um critério estabelecido
MÉDIASE(intervalo; critérios; [intervalo_média]) – Retorna a média das células de um intervalo(s) que atendem a
um critério estabelecido
72
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Contagem
3. Utilizando as funções aprendidas, responda as perguntas =CONT.VALORES(A4:A21)
=CONT.VALORES(C4:C21)
=CONT.NÚM(C4:C21)
=CONT.SE(C4:C21;O5)
=CONT.SE(C4:C21;O6)
=CONTAR.VAZIO(C4:C21)
=MÉDIASE(B4:B21;”montagem”;C4:C21)
=CONT.SES($B$4:$B$21;G16;$C$4:$C$21;$O$5)
=MÉDIASE(C4:C21;O5)
Funções de Soma Condicionada
1. Clique na planilha “Relatório_de_Vendas”
73
Treinamento de ExcelAvançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções de Soma Condicionada
2. Utilizando as funções aprendidas, responda as perguntas =SOMASE(B4:B21;“Carlos";D4:D21)
=SOMASES(D4:D21;B4:B21;“Lucas";C4:C21;“motor")
=SOMASE($B$4:$B$21;H13;$D$4:$D$21)
Funções
SOMASE(intervalo;critérios;[intervalo_soma]) – Retorna a soma das células de um intervalo que atendem a um
determinado critério
SOMASES(intervalo_soma; intervalo_critérios1;critérios1;[intervalo_critérios2];[critérios2];...) – Retorna a soma
das células de um intervalo atendem a mais de um determinado critério
Somar Produtos
1. Clique na planilha “Custos_Produção”
2. Calcule o valor Total em R$ para cada uma das quantidades 
de produto. Faça isso sem criar colunas auxiliares.
SOMARPRODUTO(matriz1;[matriz2];...) – Retorna a
soma dos produtos das matrizes correspondentes
Função
=SOMARPRODUTO($C$4:$C$15;D4:D15)
Observação: Criar colunas
auxiliares em tabelas não é
uma boa prática de Excel.
74
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Minigráficos
1. Selecione a planilha “Bolsa_Valores”
2. Selecione o intervalo de I4:I8
3. Na guia Inserir, no grupo Minigráficos, clique na opção Linha
Observação: minigráfico é um recurso existente a 
partir da versão 2010
Minigráficos
4. Selecione o intervalo de C4:H8 e clique em OK
=C4:H8
5. Com todos os minigráficos selecionados, na guia Design, clique em Desagrupar
75
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Minigráficos
6. Na guia DESIGN é possível destacar o Ponto mais alto, o Ponto mais baixo, colorir os Minigráficos, etc
Resultado:
Case Pareto
Buscando aumentar as nossas
vendas, nós vamos criar um novo
setor em nossa empresa com
foco no desenvolvimento dos
nossos menores clientes, de
forma que possamos aumentar o
volume das vendas para estes
clientes.
Em quais clientes devemos focar
nossos esforços?
Pergunta do Chefe
76
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Diagrama de Pareto – Princípio 80‐20
‐ Princípio empírico criado pelo economista e sociólogo italiano, Vilfredo Pareto;
‐ O princípio 80‐20 diz que 80% dos resultados são obtidos por 20% dos esforços.
‐ Pareto observou que 80% das terras da Itália, pertenciam a 20% da população; 
‐ 80% das riquezas da Inglaterra estavam concentradas nas mãos de 20% da população; 
Exemplos:
‐ 80% do lucro de uma empresa está relacionado com 20% dos clientes;
‐ 80% do lucro de uma empresa está relacionada com 20% dos produtos;
‐ 80% dos acidentes de trânsito são causados por 20% dos motoristas;
‐ 80% dos usuários de computador usam apenas 20% dos recursos disponíveis;
‐ 80% do tempo, usamos 20% de nossas roupas;
‐ 80% das pessoas preferem 20% dos sabores ou cores disponíveis;
‐ 80% dos resultados são obtidos por 20% dos funcionários.
Não é uma teoria exata, mas atualmente é amplamente utilizada em diversas áreas, como negócio, 
política, economia, gestão empresarial, etc.
Pareto
1. Clique na planilha Pareto
2. Na coluna J, calcule o Valor Total vendido para cada cliente
=J4/SOMA($J$4:$J$15)
=SOMASE($B$4:$B$100;I4;$F$4:$F$100)
=SOMA($H$4:H4)
3. Na coluna H, calcule a porcentagem do Valor Total vendido para cada cliente
4. Na coluna K, calcule a porcentagem acumulada das vendas
77
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Pareto
5. Selecione o intervalo H3:L15
6. Na guia DADOS, clique em Classificar
7. Em Classificar por, selecione Total
8. Em Ordem, selecione Do Maior para o Menor
9. Clique em OK
Resultado
Pareto
10. Selecione o intervalo I3:L15 11. Na guia INSERIR, Insira um gráfico de Coluna 2D
Resultado
78
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gráficos combinados
12. Clique com botão direito sobre o gráfico e em 
Alterar Tipo de Gráfico
13. Clique em Combinação
14. Em % Acumulada Vendas, marque Linhas e Eixo 
Secundário, Paretomarque Linhas e Eixo Secundário
Gráficos combinados – Excel 2007 e 2010
12. Clique pausadamente 2x sobre a legenda 
% Acumulado Vendas
13. Clique com o botão direito sobre %
Acumulado Vendas e clique em Alterar Tipo
de Gráfico de Série
14. Clique em Linha e clique na primeira opção de gráfico de
linha
15. Clique em Ok
79
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gráficos combinados – Excel 2007 e 2010
16. Clique com o botão direito sobre %
Acumulado Vendas e clique em Formatar
Série de Dados...
17. Clique em Opções de série e em Eixo Secundário
18. Clique em Fechar
19. Repita os passos para a série Pareto
Gráficos combinados
Resultado
80
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Função PROCV
1. No ARQUIVO PRINCIPAL clique no botão “Funções IV”
2. Selecione a planilha “Produtos”
Utilizando a função PROCV, a partir de um Produto 
selecionado, retorne o Preço correspondente ao Produto.
Missão
Função PROCV
PROCV(valor_procurado; matriz_tabela; índice_coluna; [procurar_intervalo]) – Procura um valor na primeira coluna à 
esquerda da matriz tabela e encontrada uma ocorrência, retorna qualquer valor da linha de ocorrência
Função
Lembre‐se, queremos como resultado da função PROCV o preço do
produto selecionado na lista suspensa:
valor_procurado: por quem estamos procurando;
matriz_tabela: a coluna de pesquisa e todas as outras colunas à
direita que se possa querer como retorno;
índice_coluna: número da coluna da matriz tabela que se deseja de
retorno;
procurar_intervalo:
FALSO ou 0 – correspondência exata;
VERDADEIRO ou 1 – correspondência aproximada.
*Sempre que o valor_procurado for texto, usaremos o FALSO.
=PROCV(C5;C12:F23;4;FALSO)
Observação: Para o correto funcionamento da
função PROCV, não pode haver repetição na
primeira coluna da matriz tabela (coluna de
pesquisa).
81
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Função PROCH
Função
PROCH(valor_procurado; matriz_tabela; índice_linha; [procurar_intervalo]) – Procura um valor na primeira linha da 
matriz tabela e encontrada uma ocorrência, retorna qualquer valor da coluna de ocorrência
Utilizando a função PROCH, a partir de um País selecionado, retorne a Taxa correspondente ao País.
=PROCH(J5;K11:N12;2;FALSO)
=F5+(F5*M5)
Missão
Função PROCV – correspondência aproximada
1. Clique na planilha Imposto
=PROCV(C3;$G$3:$H$7;2;VERDADEIRO)
Na coluna Alíquota IR (coluna D), “busque” a alíquota correta de acordo com o Salário Bruto (coluna C). Utilize a função
PROCV.
=C3‐(C3*D3)
Parâmetro procurar_intervalo:
 FALSO – correspondência exata:
‐ usado para encontrar exatamente o valor procurado;
‐ usado quando o valor procurado for texto.
 VERDADEIRO – correspondência aproximada:
‐ usado para encontrar valor próximo ao valor procurado;
‐ usado somente quando o valor procurado for número;
‐ arredonda sempre para o menor valor;
‐ valores da coluna de pesquisa (primeira coluna da matriz tabela)
estejam classificados em ordem crescente.
Missão
82
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções ÍNDICE e CORRESP
1. No ARQUIVO PRINCIPAL clique no botão “Funções V”
Funções ÍNDICE e CORRESP
CORRESP(valor_procurado; vetor_procurado; [tipo_correspondência]) – Retorna a posição relativa de um item 
procurado em um vetor.
Observação: O vetor procurado pode ser uma linha ou uma coluna
Funções:
ÍNDICE(matriz;núm_linha;[núm_coluna])– Retorna o valor (conteúdo) de uma célula de uma matriz, de acordo 
com uma referência de linha e/ou coluna.
83
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Funções ÍNDICE e CORRESP
2. Utilizando as funções aprendidas, de acordo com a escolha do cliente, pesquise o seu estado e o seu código
Dica: Utilize a célula “Linha”  para auxiliá‐lo
3. De acordo com a escolha do Produto e do Fabricante, pesquise o preço
Dica: Utilize as células “Linha” e “Coluna”  para auxiliá‐lo
=PROCV(D5;Cliente!C3:D19;2;FALSO)
=ÍNDICE(Cliente!B3:B19;H5)
=CORRESP(Compra!D5;Cliente!C3:C19;0)
=CORRESP(Compra!D10;Produto!B3:B13;0)
=CORRESP(Compra!D11;Produto!C2:F2;0)
=ÍNDICE(Produto!C3:F13;Compra!H10;Compra!H11)
Tabela Dinâmica
1. No ARQUIVO PRINCIPAL clique no botão “Plano de Saúde”
84
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
3. Na guia Inserir  clique em Tabela Dinâmica
2. Clique em qualquer célula dentro da base de dados
Perceba que o Excel já entende com quais dados ele 
criará a Tabela Dinâmica
4. Clique em OK
Vendas!$C$3:$I$153
Qual o total de vendas de cada tipo de 
plano por vendedor?
Pergunta do Chefe
É possível responder a esta pergunta com os
conhecimentos aprendidos até agora? Sim.
Observação: Poderíamos usar a função SOMASES,
mas perceba como seria trabalhoso.
Tabela Dinâmica
Perceba que foi criado uma tabela dinâmica em Plan1
Tabela 
Dinâmica
Lista de
Campos
85
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
5. Clique e arraste “Plano” para 
COLUNAS
6. Clique e arraste “Vendedor” 
para LINHAS
7. Clique e arraste “Valor 
Vendido” para VALORES
Tabela Dinâmica criada a 
partir dos campos 
selecionados
Tabela Dinâmica
8. Selecione o intervalo de B5:E10 9. Na guia PÁGINA INICIAL aplique a formatação Monetária
Resultado
86
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Filtro em Tabela Dinâmica
Do total, qual foi o valor 
vendido para pessoa física?
Pergunta do Chefe
10. Arraste “Personalidade” para FILTROSFiltro criado
Filtro em Tabela Dinâmica
11. Clique no Filtro criado
12. Clique em Selecionar Vários Itens
13. Desmarque apenas a opção Jurídica
14. Clique em OK
Resultado – Tabela Filtrada
15. Clique no Filtro, marque Tudo e 
clique em Ok
87
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Relatório de Detalhes
Gostaria de saber os detalhes das 
vendas feitas por Joaquina de 
Plano Ouro
Pergunta do Chefe
16. Dê um duplo clique na célula C7
Perceba que foi criada uma nova planilha 
(Plan2) com as informações detalhadas das 
vendas de Plano Ouro feitas por Joaquina
Gráfico Dinâmico
Para analisarmos de forma visual, 
você poderia criar um gráfico a 
partir dessa tabela?
Pergunta do Chefe17. Volte para a Tabela Dinâmica (Plan1)
18. Selecione qualquer célula da tabela dinâmica
19. Na guia INSERIR, selecione o Gráfico de Barras 2D
Gráfico dinâmico é um gráfico construído a partir de 
uma tabela dinâmica
88
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Item Calculado
No gráfico, também gostaria de 
ver as barras do total dos planos 
Ouro, Prata e Bronze ?
Pergunta do Chefe
20. Selecione a Vendedora Maria (célula A9)
21. Na guia ANALISAR clique em Campos, Itens e Conjuntos e em Item 
calculado
Tabela Dinâmica
22. Em nome, digite Total
23. Em fórmula, insira a fórmula como mostrado na figura
Total
=Alfredo+João+Joaquina+José+Maria
24. Clique em OK
Resultado
Perceba que o item calculado é somado ao Total Geral
25. Clique com o botão direito dentro da tabela dinâmica 
e em Opções da Tabela Dinâmica
89
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
26. Na guia Totais e Filtros, desmarque Mostrar totais 
gerais das colunas e clique em OK
Resultado
Tabela Dinâmica
Gostaria de ver o total de
vendas de cada vendedor
por personalidade
Pergunta do Chefe
Perceba como seria 
trabalhoso e improdutivo
fazer todas essas análises 
usando a função 
SOMASES 
27. Arraste Personalidade para LINHAS (Abaixo de Vendedor)
90
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
Perceba que ao alterar a estrutura da tabela 
dinâmica, o gráfico tem sua estrutura alterada 
automaticamente
28. Clique com o botão direito sobre o vendedor Alfredo, em 
Expandir/Recolher, clique em Recolher Todo o Campo
Tabela Dinâmica
Resultado Perceba que ao recolher os campos da tabela 
dinâmica, o gráfico tem sua estrutura alterada 
automaticamente
Qual a quantidade de cada tipo de 
plano vendido por personalidade?
Pergunta do Chefe
1. Volte a planilha Vendas
2. Selecione qualquer célula dentro dos dados
91
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
3. Na guia Inserir  clique em Tabela Dinâmica
4. Clique em OK
Vendas!$C$3:$I$153
5. Clique e arraste “Personalidade” para COLUNAS
6. Clique e arraste “Plano” para LINHAS
7. Clique e arraste “Valor Vendido” para VALORES
Resumo de Valores
8. Clique com o botão direito sobre uns dos números da 
tabela, em Resumir Valores por, clique em Contagem
Resultado
1. Volte a planilha Vendas
2. Selecione qualquer célula dentro dos dados
Em qual estado temos a maior 
margem de lucro?
Pergunta do Chefe
Lucro = (valor vendido – custo operacional)
92
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Campo Calculado
3. Na guia Inserir  clique em Tabela Dinâmica
4. Clique em OK
Vendas!$C$3:$I$153
Perceba que não existe um campo que nos informe 
diretamente a margem de lucro
5. Na guia ANALISAR clique em Campos, Itens e Conjuntos e em 
Campo calculado
Campo Calculado
6. Em nome, digite %Lucro
7. Em fórmula, insira a fórmula como mostrado na figura
%Lucro
=1 ‐ ‘Custo Operacional’ / ‘ Valor vendido ’
8. Clique em OK
93
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Campo Calculado
9. Clique e arraste “Estado” para LINHAS
10. Clique e arraste “Custo Operacional” para VALORES (acima de Soma de %Lucro)
11. Clique e arraste “Valor Vendido” para VALORES (acima de Soma de Custo Operacional)
Tabela Dinâmica
12. Na guia PÁGINA INICIAL, formate as colunas Soma de 
Valor Vendido e Custo Operacional como monetário
13. Na guia PÁGINA INICIAL, formate a coluna Soma de % 
Lucro como porcentagem
Você poderia criar um gráfico dos 
resultados?
Pergunta do Chefe
14. Selecione qualquer célula da tabela dinâmica
15. Na guia INSERIR e selecione o Gráfico de Coluna 2D
94
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gráficos Combinados
16. Clique com o botão direito sobre o gráfico e em 
Alterar Tipo de Gráfico
17. Em Combinação, em Tipo de Gráfico, selecione Linhas para 
Soma de %Lucro e marque Eixo Secundário e clique em OK
Gráficos Combinados
18. Clique com o botão direito sobre a linha (série %Lucro), em Adicionar Rótulos 
de Dados, clique em Adicionar Rótulos de Dados
Resultado
Conseguimos perceber facilmente as 
margens de lucro por estados
95
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
Gostariade ver o total das vendas e a 
porcentagens das vendas de cada 
vendedor
Pergunta do Chefe
1. Volte a planilha Vendas
2. Selecione qualquer célula dentro dos dados
3. Na guia Inserir  clique em Tabela Dinâmica
4. Clique em OK
Vendas!$C$3:$I$153
Excluir Item Calculado
5. Clique e arraste “Vendedor” para LINHAS 6. Na guia ANALISAR clique em Campos, Itens e Conjuntos e em Item 
calculado
7. Em Nome, selecione Total, clique em Excluir e em OK
96
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
8. Clique e arraste “Valor Vendido” para VALORES
9. Clique e arraste “Valor Vendido” para VALORES novamente
Exibição de Valores
10. Clique com o botão direito sobre um número da coluna B, 
em Mostrar Valores como, clique em % do Total Geral
11. Formate a coluna C como monetário
Resultado
12. Selecione qualquer célula da tabela dinâmica
13. Na guia INSERIR, selecione o Gráfico de Pizza 2D
97
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Rótulo da série
14. Clique com botão direito sobre a pizza, em Adicionar Rótulos de 
Dados, clique em Adicionar Rótulos de Dados
15. Clique com botão direito sobre o rótulo 
inserido e em Formatar Rótulos de Dados
Rótulo da série
16. Na janela que se abriu, 
marque Nome da Categoria
Resultado
Gostaria de ver um gráfico de colunas 
com o total das vendas por meses
Pergunta do Chefe
1. Volte a planilha Vendas
2. Selecione qualquer célula dentro dos dados
98
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
3. Na guia Inserir  clique em Tabela Dinâmica
4. Clique em OK
Vendas!$C$3:$I$153
5. Clique e arraste “Data” para LINHAS
6. Clique e arraste “Valor Vendido” para VALORES
Agrupar Valores
7. Clique com o botão direito sobre 
uma das datas e clique em Agrupar
8. Selecione Meses e Trimestres e 
clique em OK
9. Formate a coluna B como monetário
Resultado
99
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
10. Selecione qualquer célula da tabela dinâmica
11. Na guia INSERIR, selecione o Gráfico de Coluna 2D
Resultado
13. Selecione o gráfico criado e copie‐o
12. Salve o arquivo
Ctrl C
14. Crie a planilha Dashboard
15. Cole o gráfico copiado Ctrl V
Dashboard com Tabela Dinâmica
16. Selecione a planilha Plan1 (ou planilha correspondente)
17. Selecione o gráfico da planilha e copie‐o Ctrl C
18. Selecione a planilha Dashboard e selecione uma 
célula da planilha Dashboard
19. Cole o gráfico copiado Ctrl V
20. Selecione a planilha Plan5 (ou planilha correspondente)
21. Selecione o gráfico da planilha e copie‐o Ctrl C
22. Selecione a planilha Dashboard e selecione uma 
célula da planilha Dashboard
23. Cole o gráfico copiado Ctrl V
100
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Segmentação de Dados
24. Organize os gráficos
25. Selecione o gráfico de pizza
26. Clique na guia ANALISAR e em Inserir Segmentação de Dados
26. Marque Estado, Cidade e 
clique e Ok
Linha do tempo
A segmentação de dados é um filtro
Resultado
Perceba que ao selecionar algum(ns) 
estado(s) na segmentação de dados 
Cidade, só estarão disponíveis as cidades 
correspondentes ao(s) estado(s) 
selecionado(s)
27. Selecione o gráfico de pizza novamente
28. Clique na guia ANALISAR e em Inserir Linha do Tempo
29. Marque Data e clique e Ok
A linha do tempo é um filtro para tempo
Resultado
*Existe a partir da versão 2013 do Excel
*Existe a partir da versão 2010 do Excel
101
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Tabela Dinâmica
Utilize as segmentações de dados e a linha do 
tempo. Perceba que ambas filtram apenas o 
gráfico de pizza
30. Clique com o botão direito sobre a segmentação 
Estado e clique em Conexões do Relatório
31. Marque todas as tabelas dinâmicas e clique em OK
32. Repita o passa para a segmentação de dados Cidade
e para a Linha do Tempo
Utilize as segmentações de dados e a linha do 
tempo novamente. Perceba que agora ambas 
filtram todos os gráficos
33. Organize os gráficos e os filtros
Dashboard com Tabela Dinâmica
Exemplo de um resultado
102
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Função Pagamento
1. No ARQUIVO PRINCIPAL clique no botão “Investimento”
Nossa empresa deseja aumentar a produtividade através da
ampliação da linha de produção atual. Para isso, foram orçados
2 (dois) projetos, o projeto 1 (um) aumentará a nossa
capacidade de produção em 15% e custará R$1.500.000 e o
projeto 2 aumentará nossa capacidade de produção em 20 % e
custará R$2.000.000.
O valor do investimento para o novo projeto virá de um
empréstimo bancário em que o prazo máximo para pagamento
é de 6 anos e a taxa de 0,62% a.m.
A prestação máxima que a empresa pode pagar é de
R$42.000,00 a.m.
Qual projeto podemos implementar?
Qual o menor prazo em que conseguiremos pagar o
empréstimo?
2. Na célula C6, comece a digitar =PGTO(
3. Clique no botão de função
Função Pagamento
$C$4
$C$5
$C$3
Função Pagamento (PGTO)
Taxa: taxa de juros
Nper: número de período
Vp: valor presente
Vf: valor futuro
4. Preencha os Campos como na figura
5. Clique em OK
103
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Teste de Hipóteses ‐ Tabela
7. Na célula F3, faça um referência a célula C6
8. Selecione o intervalo F3:L10
Observamos que temos condições de pagar o empréstimo 
de 1,5 milhão. E o empréstimo de 2 milhões? 6. Substitua o Valor do Empréstimo por 2 milhões
=C6
Teste de Hipóteses ‐ Tabela
9. Na guia Dados, clique em Teste de Hipóteses e 
em Tabela de Dados
10. Em Célula de entrada de linha, selecione a célula C3
11. Em Célula de entrada de coluna, selecione a célula C5 e clique em Ok
$C$5
$C$3
Observando os resultados concluímos o menor prazo em que conseguiremos 
pagar o empréstimo de 2 milhões está entre 54 e 60 meses
104
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Teste de Hipóteses – Atingir Meta
12. Na guia Dados, clique em Teste de Hipóteses e 
em Atingir Meta
Vamos fazer uma análise a fim de descobrir a quantidade mínima de meses em que conseguiremos pagar 
o empréstimo de 2 milhões
13. Selecione as células como mostra a figura e clique em OK
$C$6
‐42000
$C$5
 Definir célula: Campo o qual faz referência a 
célula em que está contida a fórmula.
 Para valor: É o valor objetivo (meta).
 Alternando célula: É a variável da fórmula que será
variada com a finalidade de atingir o valor objetivo.
Resultado = 57 meses
Gravação de Macro
• São códigos em VBA (Visual Basic for Applications) que executam tarefas;
• VBA é a linguagem de programação do Excel.
O que é uma Macro?
• Tarefa muito complexa a ser realizada, onde será mais simples fazer uma Macro do que 
realizar a própria tarefa;
• Tarefa repetitiva.
Quando é vantajoso fazer uma Macro?
• Escrever um código VBA diretamente no VBE (Visual Basic Editor);
• Gravador de Macro (Converte ações em código VBA).
Formas de fazer uma Macro:
105
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gravação de Macro
1. No ARQUIVO PRINCIPAL clique no botão “Cadastro de Vendas ”
Guia Desenvolvedor
1. Clique na guia ARQUIVO
2. Clique em Opções
3. Clique em Personalizar Faixa de Opções, marque a opção Desenvolvedor e clique em OK
106
Treinamento de ExcelAvançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gravação de Macro
2. Clique na planilha “Cadastrar”
3. Na guia Desenvolvedor, clique em Gravar Macro
4. Em Nome da macro, dê o nome “Cadastrar” e clique em OK
Cadastrar
Gravação de Macro
5. Selecione a planilha “Pedidos”
6. Selecione o intervalo de A2:E2
7. Clique com o botão direito sobre o intervalo selecionado.
8. Clique em Inserir
9. Clique em OK
107
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gravação de Macro
Resultado
10. Selecione a planilha “Cadastrar”
11. Selecione o intervalo de B3:B7
12. Copie (Ctrl+C) Ctrl C
Gravação de Macro
13. Selecione a planilha “Pedidos”
14. Selecione a célula A2
15. Na guia Página Inicial, clique na seta
embaixo de Colar e clique em Colar especial
108
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gravação de Macro
16.Marque a opção Valores, marque a opção Transpor e clique em OK
17. Selecione a planilha “Cadastrar”
18. Na planilha “Cadastrar” selecione a célula 
B5 e tecle Delete
Delete
Gravação de Macro
19. Na guia Desenvolvedor, clique em Parar gravação
20. Na planilha “Cadastrar”, clique na guia 
Desenvolvedor, em Inserir e clique no Botão 
21. Desenhe o Botão como mostra a figura
109
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Gravação de Macro
22. Na janela Atribuir Macro, escolha a macro Cadastrar 
e clique em OK
23. Renomeie o Botão para Cadastrar
24. Teste a macro criada
Proteção de Planilha
1. Na planilha “Cadastrar”, selecione as células B4 e B5
2. Clique com o botão direito sobre a seleção 
e clique em Formatar células
3. Na guia Proteção desmarque a opção Bloqueadas e clique em OK
110
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Proteção de Planilha
4. Na guia Revisão, clique em Proteger planilha 5. Digite a senha 123 e clique em OK
6. Digite a senha 123 novamente e clique em OK
Tente alterar qualquer célula da planilha Cadastrar, exceto 
as células B4 e B5
Perceba que exceto as células B4 e B5, todas as  outras 
células estão bloqueadas para edição
123
123
Proteção de Planilha
7. Clique na planilha “Preço” 8. Na guia Revisão, clique em Permitir que os Usuários Editem Intervalos
9. Clique em Novo
111
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Proteção de Planilha
10. Em Referência de células, digite =A:B, digite a
senha 789 para o intervalo e clique em OK
11. Digite a senha 789 novamente e clique em OK
12. Clique em OK
Intervalo1
=A:B
789
789
Proteção de Planilha
13. Na guia Revisão, clique em Proteger planilha 14. Digite a senha 456 e clique em OK
15. Digite a senha 456 novamente e clique em 
OK
Tente alterar células da coluna C em diante da planilha Preço.
Perceba que as células da coluna C em diante estão bloqueadas 
para edição.
Tente alterar células das colunas A e B da planilha Preço.
Perceba que será solicitada a senha do intervalo.
456
456
112
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Mala direta vinculada a uma planilha
3. Preencha a cabeçalho da planilha como mostra a figura
1. No ARQUIVO PRINCIPAL clique no botão “Calibração”
4. Salve e feche o arquivo do Excel
2. Na pasta do treinamento, abra o arquivo Certificado calibração.docx
Nome ResponsávelFabricante IndicaçãoUnidade DataCidade Tipo
Mala direta vinculada a uma planilha
5. Na guia Correspondências, clique em Selecionar Destinatários e selecione Usar uma Lista Existente
6. Selecione o arquivo 535_Calibração_Base de dados.xlsm e clique em Abrir 7. Com a tabela Calibração selecionada, clique em OK
535_Calibração_Base de dados.xlsm
É importante manter marcada a opção “Primeira 
linha de dados contém cabeçalhos de coluna”.
113
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Mala direta vinculada a uma planilha
8. Na guia Correspondências, clique em
Inserir Campo de Mesclagem e irá exibir
todos os campos da planilha calibração
(535_Calibração_Base de dados.xlsm)
9. Insira os respectivos Campos de Mesclagem
Nome
Cidade
Fabricante
Tipo
Data
Unidade Indicação
Responsável
Mala direta
10. Na guia Correspondências, clique em Concluir e Mesclar e 
selecione Editar Documentos Individuais
11.Marcar a opção Todos e clicar em OK
Foi gerado um certificado para cada linha do Excel
114
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Importar Dados Externos ‐ Texto
2. No guia Dados, clique em De Texto
3. Na pasta do treinamento, selecione o arquivo de texto
Contatos e clique em Importar
1. No ARQUIVO PRINCIPAL clique no botão
“Lista de Contatos”
Contatos
Importar Dados Externos ‐ Texto
4. Selecione “Delimitado – Caracteres como vírgulas ou tabulações separam cada campo”
5. Clique em Avançar
115
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Importar Dados Externos ‐ Texto
6. Selecione Vírgula
7. Clique em Avançar
Importar Dados Externos ‐ Texto
8. Clique em Concluir
116
Treinamento de Excel Avançado
Lean Solutions – Treinamento & Consultoria | contato@leansolutions.com.br | www.leansolutions.com.br 
Importar Dados Externos ‐ Texto
9. Na opção Onde você deseja colocar os dados?, 
selecione a célula A7
10. Clique em OK
=Contatos!$A$7
Resultado
	Capa
	indice
	apostila