APOSTILA - EXCEL AVANÇADO (Leiaut)

Prévia do material em texto

Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
1 
Bem vindo, a Escola Técnica Leiaut Carielo. Esperamos que o CURSO que se inicia atenda integralmente 
suas expectativas e que sua permanência na Leiaut seja agradável e produtiva. 
 
1. MATRÍCULA 
Se você ainda não assinou a sua Ficha de Matrícula procure a Recepcionista que lhe atendeu. 
 
2. SALA DE AULA 
 Evite concentrações e barulhos próximos às salas de aula em atividade; 
 Evite o uso de celulares após o início da aula; 
 Evite atrasos para não prejudicar o aprendizado e o bom desenvolvimento da aula; 
 Não é permitida a presença do aluno em sala de aula, ANTES ou DEPOIS do seu horário de 
curso, sem a presença do Instrutor; 
 Copie em um PEN DRIVE os seus exercícios de sala de aula. A Leiaut não se responsabilizará 
pela manutenção dos mesmos; 
 Para evitar danos aos computadores, não é permitido entrar com bebidas e comidas em 
Sala de Aula. 
 
3. FREQUÊNCIA 
Ao assinar a ATA DE PRESENÇA, verifique se o seu NOME e seu telefone estão corretos. O nome 
que está na ATA é o que será impresso no seu CERTIFICADO. Assine a ATA, pois ela é a garantia da 
sua presença em sala de aula. Para receber o CERTIFICADO, você deverá ter no mínimo 80% de 
freqüência. 
 
4. AVALIAÇÃO 
Cada curso tem o seu método de avaliação, que é representado por uma prova no último dia ou 
pelo desenvolvimento de vários projetos ao longo do curso. 
 
5. CERTIFICADOS 
Quando o aluno estiver na última aula do curso, procurar o professor para dar entrada em seu 
CERTIFICADO. O certificado será confeccionado em até 30(trinta) dias úteis após o termino do curso, para 
todos aqueles que efetuarem o pagamento á vista ou cartão de crédito ou débito. Em caso de pagamento 
feito através de cheques pré-datados ou boletos bancários, o CERTIFICADO é liberado após a quitação total 
do valor do curso contratado. 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
2 
Sumário 
INTRODUÇÃO 
• AULA 1 
 DETALHES SOBRE O EXCEL 
 REVISÃO DE EXCEL BÁSICO 
 REVISÃO DE EXCEL BÁSICO 2 
 VALOR PRESENTE LÍQUIDO 
 1ª Exercício de Excel Avançado 
 2ºExercício de Excel Avançado 
 FUNÇÃO SE 
 FUNÇÃO SE COMPOSTO 
 SELEÇÃO ADJASCENTE 
 Exercícios Aplicados do Excel 
 Exercícios Aplicados do Excel 2 
 FUNÇÃO SE/E 
 FUNÇÃO SE/OU 
 3ºExercício do Excel 
 FUNÇÃO CONTAR.VAZIO 
 4ºExercício do Excel 
• AULA 2 
 COMANDO AGRUPAR 
 COMANDO DESAGRUPAR 
 COMANDO SUBTOTAL 
 COMANDO COMENTÁRIOS 
 COMANDO PROTEGER PLANILHA 
 UTILIZANDO FUNÇÕES FINANCEIRAS 
 FUNÇÃO VP 
 5ºExercício do Excel 
 FUNÇÃO PGTO 
 EXERCÍCIO APLIADO AO EXCEL - PGTO 
 FUNÇÃO NPER 
 FUNÇÃO TAXA 
 EXERCÍCIO APLIADO AO EXCEL - TAXA 
• AULA 3 
 VALIDAÇÃO DE DADOS 
 6ª Exercício de Excel Avançado 
 Formatação Condicional 
 7ª Exercício de Excel Avançado 
 Função Índice 
 8ª Exercício de Excel Avançado 
 Função CORRESP 
 EXERCÍCIO DE EXCEL APLICADO 
 EXERCÍCIO DE EXCEL APLICADO 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
3 
 CRIANDO FOLHA DE PAGAMENTO(PROJETO 1) 
 CÁLCULO DO INSS 
• AULA 4 
 SOMAR PRODUTO 
 EXERCÍCIO APLICADO AO EXCEL 
 FUNÇÃO SOMA SE 
 EXERCÍCIO APLICADO AO EXCEL 
 CRIANDO VÍNCULOS ENTRE PLANILHAS 
 9ª Exercício de Excel Avançado 
 REVISÃO 1 
• AULA 5 
 ASSISTENTE DE FUNÇÃO 
 PERSONALIZANDO ATALHOS 
 MESCLAR E DIVIDIR CÉLULAS 
 EXIBINDO PASTAS DE TRABALHO NA MESMA JANELA 
 FUNÇÃO PROCV 
 10ª Exercício de Excel Avançado 
 11ª Exercício de Excel Avançado 
 12ª Exercício de Excel Avançado 
 FUNÇÃO PROCH 
 13ª Exercício de Excel Avançado 
 14ª Exercício de Excel Avançado 
• AULA 6 
 OCULTANDO UMA PLANILHA 
 DEFININDO IMAGEM COMO PLANO DE FUNDO 
 EXCLUÍNDO A IMAGEM DO PLANO DE FUNDO 
 INSERINDO E EDITANDO IMAGENS 
 APLICANDO TEMAS 
 CONTROLANDO ESTOQUE(PROJETO 3) 
 EXERCITANDO 
 FORMATANDO A FORMA DA PLANILHA 
• AULA 7 
 FUNÇÕES DE BANCO DE DADOS 
 FUNÇÃO BDCONTAR 
 FUNÇÃO BDMÁX 
 FUNÇÃO BDMÉDIA 
 FUNÇÃO BDMÍN 
 FUNÇÃO PROCV 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
4 
• AULA 8 
 CRIANDO FORMULÁRIOS(PROJETO 6) 
 CONTROLE GIRATÓRIO 
 NÚMERO DE PARCELAS 
 CRIANDO UMA FUNÇÃO NO VISUAL BASIC 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
5 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
6 
1ª Aula de Excel Avançado 
 
SINOPSE DA AULA 
Nesta 1ºaula do Curso de Excel Avançado, você irá revisar alguns comandos do excel básico, além de 
aprender funções novas como o VPL, a função SE, SE COMPOSTO, SE/E, SE/OU, entre outros. 
DETALHES SOBRE O EXCEL 
Você sabe o que significao símbolos de : (dois pontos), ; (ponto e vírgula), “” (aspas)? 
 
Obs 1: Ponto e Vírgula (;) representará uma separação de informações dentro de uma função. 
Obs 2: Dois Pontos (:) representará um intervalo de informações no qual deverá ocorrer de uma célula a 
outra. 
Obs 3: Aspas (“”) será utilizada para converter texto em número para o programa, imprimindo uma 
função (mostrar na tela). 
ERROS COMUNS 
Você sabe o que significa os seguintes símbolos? 
1)#######, esse símbolo dentro de uma célula, significa que a mesma está suprimida você precisa 
aumentar o tamanho da célula para que o valor apareça. 
 
 
1ºObserve o ; (ponto e 
vírgula) 
2ºObserve o : (dois pontos) 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
7 
REVISÃO DE EXCEL BÁSICO 
 
1ºPasso: Abra o Excel. 
2ºPasso: Construa a seguinte tabela. 
 
3ºPasso: Na Linha B8 informe o valor total de investimento (utilizando uma função). 
4ºPasso: Crie 3 tipos de gráficos que representem os seus investimentos. 
5ºPasso: Na Linha B10 informe se as ações do mercado doméstico caírem 5%, qual será a quantia 
disponível por diego (utilizando uma função). 
6ºPasso: Na Linha B11 informe se as ações do mercadoemergentes aumentarem 3%, qual será a 
quantia disponível por diego (utilizando uma função). 
7ºPasso: Na Coluna C (%) divida a quantia total encontrada na célula B8 pelas quantias individuais para 
que você possa encontrar o seu valor em percentual. Exemplo: B8/B5. 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
8 
REVISÃO DE EXCEL BÁSICO 2 
1ºPasso: Abra o Excel. 
2ºPasso: Construa a seguinte tabela. 
 
 
3ºPasso: Abra o Excel. 
4ºPasso: Total de pontos: função Soma; 
5ºPasso: Média de cada Aluno; 
6ºPasso: 1º Colocado: função Máximo =MÁXIMO(F3:F10); 
7ºPasso: 2º Colocado: função Maior = MAIOR (F3:F10;2); 
8ºPasso: 3º Colocado: função Maior = MAIOR (F3:F10;3); 
9ºPasso: Penultimo Colocado: função Menor = MENOR (F3:F10;2); 
10ºPasso: Último Colocado: função Mínimo = MÍNIMO (F3:F10;2); 
11ºPasso: Média Geral: função Média = Média (G3:G10). 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
9 
VALOR PRESENTE LÍQUIDO 
Você poderá utilizar a função VPL para calcular o Valor Presente Líquido dos custos de suas duas opções 
de valores. O VPL possibilita realizar custos futuros de valores que são atuais, lhe possibilitando fazer 
comparações válidas entre compra e alguel por exemplo. 
FÓRMULA DO VPL 
=VPL(taxa; valores) 
 
1ª Exercício de Excel Avançado 
Vamos estudar o VPL. Aqui está um exemplo de como seus fluxos de caixas anuais podem se parecer 
para alugar um apartamento e pagar uma hipoteca de compra. 
Sua tarefa é conseguir o VPL dessas duas séries de custos. Use uma taxa de desconto de 3,5% para 
representar a inflação. 
Resolução do 1ºExercício 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Na célula B12 insira a seguinte fórmula: =vpl(b3;b6:b10) 
 
Clique aqui e insira sua 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
10 
3ºPasso: Na célula c12 insira a seguinte fórmula: =vpl(b3;c6:c10) 
 
Você irá constatar matematicamente, que é bem mais barato alugar um imóvel do que comprar um, 
nessa suposição. Essa função poderá ser utilizada na hora de comprar um bem móvel, como um carro à 
vista, entre outros bens que você deverá equiparar um valor percentual para um determinado período de 
tempo. 
2ºExercício de Excel Avançado 
Imagine que você deseja comprar uma franquia, com um investimento inicial de R$ 103.000,00. 
Segundo o franqueador, este investimento gerará receitas líquidas anuais estimadas em R$ 30.000,00, 
R$ 35.000,00, R$ 32.000,00, R$ 28.000,00 e R$ 37.000,00. 
Neste exemplo, devemos avaliar se a compra da franquia é um projeto de investimento viável. Isto é 
feito através do cálculo do valor presente líquido: os fluxos de caixa são trazidos para o valor presente a 
partir de uma taxa de desconto; se eles pagam o investimento inicial, o projeto cria valor para o 
investidor. 
Calcularemos o VPL deste investimento utilizando duas taxas de desconto, que representam diferentes 
cenários: 15% e 18%. Observe a planilha abaixo: 
1ºPasso: Construa a seguinte tabela. 
 
 
 
 
 
 
Clique aqui e insira sua 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
11 
Para calcular os VPLs a partir destas taxas, siga os passos a seguir: 
2ºPasso: Selecione a célula onde o resultado será guardado (no exemplo, F9); 
3ºPasso: Na célula desejada, use a função VPL para trazer os fluxos de caixa ao valor presente a partir 
da taxa de desconto dada, ou seja, aplique a fórmula VPL(C13;C6:C10); 
4ºPasso: Complete a fórmula subtraindo o investimento inicial (no exemplo, C2). Devemos ter a fórmula 
abaixo: 
 
5ºPasso: Repita os passos acima para obter os resultados nos dois cenários: 
 
 
Exemplo: Não se esqueça: a função VPL do Excel só traz para o presente os fluxos de caixa a partir do 
primeiro fluxo de caixa futuro do projeto. Para obter o valor presente líquido, você deve subtrair o 
investimento inicial do valor obtido com a função VPL. 
De acordo com o resultado acima, o investimento é viável se considerarmos uma taxa de desconto de 
15%, pois é positivo: R$ 4.997,13. Já para a taxa de desconto de 18%, temos um VPL negativo, de (R$ 
2.348,50). Isto sugere que o projeto de investimento deve ser rejeitado por não cobrir o investimento 
inicial. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
12 
FUNÇÃO SE 
A função SE é usada para testar uma condição ou varias condições dentro de uma formula. Exemplo: 
No nosso exemplo abaixo um aluno será considerado aprovado se tiver sua média final maior ou igual a 
7. 
 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Digite na célula E4 a seguinte fórmula: =SE(c3>=7;”aprovado”;”reprovado”) 
 
 
 
 
 
Clique aqui e insira sua 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
13 
FUNÇÃO SE COMPOSTO 
Supondo que você é um professor de colégio e precisa fazer um sistema com as suas planilhas de excel, 
onde os alunos que tiraram a nota acima ou igual a 7 para estarem aprovados, maior e igual a 4,5 para 
estarem em recuperação e abaixo de 4,5 estarem reprovados 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Digite na célula E4 a seguinte fórmula: 
=SE(D4>=7;"aprovado";SE(D4>=4,5;"recuperação";"reprovado")) 
 
 
Clique aqui e insira sua 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
14 
A quantidade de parenteses abertas para as funções devem ser fechadas ao final. 
=se(d4>=7;”aprovado”;se(d4>=4,5;”recuperação”;”reprovado”)) 
 
 
 
ENTENDA O TESTE LÓGICO(FUNÇÃO SE) 
Quando digitamos em uma célula =SE(, desejamos realizar um teste de comparação entre duas 
suposições como no exemplo abaixo. 
 
Obs: Porém as informações que você deve digitar devem seguir a seguinte sequência. 
 
1ºPasso: Observe a primeira condição, TESTE_LÓGICO nela você deverá inserir a lógica do teste, como 
no exemplo se célula C3 for >(maior) ou igual a 7 deverá ocorrer... 
2ºPasso: se célula C3 for >(maior) ou igual a 7 deverá ocorrer... “APROVADO” – colocar entre “” pelo 
fato de ser um texto. 
3ºPasso: Já a segunda informção diz respeito a informação contrária se célula C3 for >(maior) ou igual 
a 7 deverá ocorrer... “APROVADO”;”REPROVADO” – Logo a segunda informação será a resposta contrária 
ao que deverá ocorrer no teste lógico. 
 
 
 
Observe a quantidade de funções e a 
quantidade de parenteses 
Clique aqui e insira sua 
fórmula 
Excel Avançado – Autoria (Família Carielo)LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
15 
SELEÇÃO ADJASCENTE 
A seleção adjascente será bastante útil, quando em uma planilha houver valores que são relativos ou 
abstolutos na utilização de $ (travar coluna ou linha). 
1ºPasso: Coloque o seu mouse no seguinte ponto de sua célula, observe que apareceu uma cruz preta. 
 
2ºPasso: Clique sobre a mesma e arraste-a para baixo. 
 
 
 
 
 
 
Observe aqui 
Arraste-a dessa 
forma 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
16 
3ºPasso: Observe como deverá ficar. 
 
PARABÉNS POR CONCLUIR O SEU PRIMEIRO TESTE LÓGICO. 
Exercícios Aplicados do Excel 
A utilização de endereços nas fórmulas, em vez de valores constantes, permite que, sempre que exista 
alteração nos valores que influenciam a fórmula, o resultado apresentado seja automaticamente 
atualizado. Dizemos então que a fórmula possui endereços relativos, porque 
ao ser copiada para as linhas seguintes, os seus endereços alteram-se, adaptando-se ás novas 
coordenadas e fazendo com que o utilizador não tenha de digitar várias fórmulas idênticas. 
FÓRMULAS COM ENDEREÇOS RELATIVOS 
 
 
Nem sempre é possível utilizar a cópia das fórmulas e a sua consequente adaptação a novas 
coordenadas, de forma direta. O endereço absoluto difere do relativo utiliza valores em 
posições fixas ($ ou F4). A colocação do símbolo $ em uma coordenada indica que essa coluna 
e linha esta fixa. Ao ser copiado a formula essa posição não sofrera alteração 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
17 
FÓRMULAS COM ENDEREÇOS ABSOLUTOS 
 
 
 
Exercícios Aplicados do Excel 2 
Você tem certa planilha de pagamento e agora você vai calcular o imposto de renda para os seus 
funcionários. Mas, o calculo só será efetuado para aqueles funcionários que ganham mais de um R$ 
650,00, ou seja, se o salário do funcionário dor maior que R$ 650,00, então deverá ser multiplicado por 
uma taxa de 5% em cima do Salário Bruto, mas somente se ele ganhar mais de R$ 650,00, caso 
contrário deverá ficar 0 (zero). 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
18 
FUNÇÃO SE/E 
A função se/e é diferente da função se, com essa função nós conseguiremos realizar o teste lógico com 
duas condições ou mais. Por exemplo, supondo que irá haver uma competição de natação, onde 
para determinar se os participantes que irão participar ou não participar, eles deveram possuir 
mais do que 15 anos e serem maior do que 1,7m. Caso contrário ele não competirá. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Digite na célula E3 a seguinte fórmula: =SE(E(C3>15;D3>=1,7);"competirá";"não 
competirá") 
 
 
 
 
Clique aqui e insira sua fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
19 
3ºPasso: Digite na célula E4 a seguinte fórmula: =SE(E(C4>15;D4>=1,7);"competirá";"não 
competirá") 
 
4ºPasso: Faça isso com os demais competidores. 
 
PARABÉNS POR CONCLUIR O SEU SEGUNDO TESTE LÓGICO. 
 
 
 
 
Clique aqui e insira sua fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
20 
FUNÇÃO SE/E – ENTENDA A LÓGICA 
Você não pode apenas inserir a fórmula, você precisa entender a lógica da função na função SE 
colocavamos o teste lógico dentro da função e depois a consequência do mesmo 
=se(c3>=7;”comp”;”ncompt), já a função SE/E realizará um teste com duas condições 
simultanemanete, por exemplo =se(e(c4>15;d4>=1,7);”comp”;”ncompt”). 
 
Observe que na função se/e teremos duas condições para avaliar, enquanto na função se 
apenas uma. 
FUNÇÃO SE/OU 
Neste exemplo basta que uma condição seja verdadeira para que o aluno participe da condição. 
1ºPasso: Construa a seguinte tabela. 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
21 
3ºExercício do Excel 
 
Supondo que irá haver uma competição de futebol, onde para determinar se os participantes 
que irão participar ou não participar, eles deveram possuir mais do que 19 anos e serem maior 
do que 1,5m. Caso contrário ele não competirá, construa o seguinte teste lógico. 
Supondo que irá haver uma competição de futebol, onde para determinar se os participantes 
que irão participar ou não participar, eles deveram possuir mais do que 19 anos ou maior do 
que 1,7m. Caso contrário ele não competirá, construa o seguinte teste lógico. 
FUNÇÃO CONT.SE 
A função cont.se, servirá para identificar a quantidade de critério(poderá ser contado valor,letra, espaço, 
qualquer caracter) expressas em uma tabela, como por exemplo, descobrir a quantidade de alunos que 
tiraram as notas maiores que 8 em determinada sala de aula ou em todo o colégio. 
1ºPasso: Construa a seguinte tabela. 
 
Você deverá nesse exercício, encontrar a quantidade de alunos que tiraram notas maior do 
que 8. 
2ºPasso: Digite na célula D14 a seguinte fórmula: =CONT.SE(D3:D12;">=8") 
 
Clique aqui e insira sua fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
22 
3ºPasso: Observe o seu resultado. 
 
4ºExercício do Excel 
Informe quantos funcionários ganham mais do que R$400,00. 
 
 
 
 
 
 
 
 
Observe, que possuímos 5 
alunos que tiraram notas 
maiores que 8 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
23 
FUNÇÃO CONTAR.VAZIO 
A função contar.vazio, verifica quantas células estão no exemplo abaixo será utilizada para listar o total 
de alunos que estão sem nota. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Digite na célula c13 a fórmula =CONTAR.VAZIO(C3:C12) 
 
 
 
 
 
 
 
 
 
Clique aqui e insira sua fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
24 
4ºExercício do Excel 
 
Supondo que você é o gerente de um supermercado e deseja diferenciar os produtos que estão 
na validade e fora da validade. Sabendo que passada a data de 10/04/2012 seusprodutos 
estaram fora da validade. Observe a seguinte tabela e crie um sistema lógico. 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
25 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
26 
 
2ª Aula de Excel Avançado 
 
SINOPSE DA AULA 
Nesta 2ºaula do Curso de Excel Avançado, você aprenderá a utilizar o comando Agrupar, subtotal, 
proteger planilhas e aprenderá sobre várias funções financeiras. 
COMANDO AGRUPAR 
O comando agrupar será bastante utilizado por você, quando houver muitas linhas em sua planilha, e 
você não precise visualizar todas em um determinado momento, você poderá utilizar esse comando para 
sintetizar a sua planilha e facilitar a visualização. 
 
1ºPasso: Construa a seguinte planilha. 
 
2ºPasso: Selecione o menu Dados. 
 
 
 
 
 
 
Construa uma planilha 
assim 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
27 
3ºPasso: Selecione agora a sua coluna B e C. 
 
4ºPasso: Clique agora na opção Agrupar. 
 
5ºPasso: Observe que automaticamente será aberto o menu Agrupar, selecione a opção Linhas e clique 
em Ok. 
 
1ºClique aqui 
2ºClique aqui 
Selecione dessa fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
28 
6ºPasso: Observe que automaticamente aparecerá o seguinte componente. 
 
7ºPasso: Clique agora nessa opção de – dada pelo Excel. 
 
8ºPasso: Observe o que deverá acontecer. 
 
Observe aqui 
Observe aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
29 
9ºPasso: Note que automaticamente as linhas de 3 à 8 está comprimidas em sua planilha, o que iria 
faciliatar bastante a visualização das demais. 
 
12ºPasso: Caso, você deseje voltar a visualização anterior. Clique na opção + 
 
COMANDO DESAGRUPAR 
1ºPasso: Selecione o menu Dados. 
 
 
Observe aqui 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
30 
2ºPasso: Clique agora na opção desagrupar. 
 
3ºPasso: Observe as seguintes opções. Clique na opção desagrupar. 
 
4ºPasso: Automaticamente será aberto o menu Desagrupar, selecione a opção Linhas e clique em Ok. 
 
Clique aqui 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
31 
5ºPasso: Observe, como ficará a sua planilha. 
 
COMANDO SUBTOTAL 
O Microsoft Excel pode calcular subtotais automáticos, o Excel organiza a lista em tópicos para que você 
possa exibir e ocultar as linhas de detalhes de cada subtotal. 
Para inserir subtotais, você primeiro classifica a lista para agrupar as linhas das quais deseja calcular o 
subtotal. Em seguida, você pode calcular subtotais de qualquer coluna que contenha números. 
 
Obs: Selecione a ferramenta Subtotal que se encontra no menu Dados. 
 
1ºPasso: Crie a seguinte planilha. 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
32 
2ºPasso: Não se esqueça de classificar os dados, por Departamento, selecione toda a planilha, acesse 
a barra de menu dados e escolha a opção subtotal, será exibida uma janela chamada Subtotais: 
 
Faça as seguintes alterações necessárias e então clique em Ok. 
3ºPasso: Observe como deverá ficar a sua planilha. 
 
1ºDeixe dessa forma 
2ºClique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
33 
COMANDO COMENTÁRIOS 
Esse comando será bastante útil no momento, que você desejar realizar breves comentários, com 
ressalvo de importância. 
1ºPasso: Selecione o menu revisão. 
 
2ºPasso: Clique na opção Novo Comentário. 
 
 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
34 
3ºPasso: Observe que automaticamente será aberta uma caixa de diálogo para que você possa, 
escrever o seu comentário. 
 
4ºPasso: Escreva o seguinte: “Os carros, Gol e Civic precisam ser levados para oficina.” 
 
5ºPasso: Clicando na caixa e arranstando-a, você poderá colocá-la, onde desejar. 
 
 
 
Clique aqui e arraste 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
35 
6ºPasso: Dê um clique na sua planilha. 
 
7ºPasso: Observe que em cima da célula b3 que foi onde fizemos nosso comentário, ela fica grifada, 
com um detalhe vermelho. 
 
8ºPasso: Coloque seu mouse sob esse pequeno detalhe vermelho. 
 
Observe que automaticamente aparecerá o seu comentário, o que é bastante útil para que 
uma próxima pessoa que venha a trabalhar na sua planilha, por exemplo, saiba de seu aviso. 
 
 
 
Observe aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
36 
COMANDO PROTEGER PLANILHA 
Esse comando será bastante útil para proteger sua planilha de outras pessoas que possam vim a querer 
modificar erroneamente sua planilha. 
1ºPasso: Selecione o menu revisão. 
 
2ºPasso: Clique na opção Proteger Planilha. 
 
3ºPasso: Observe o menu Proteger Planilha. 
 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
37 
4ºPasso: Insira uma senha para proteger sua planilha. Coloque “leiaut”. 
 
6ºPasso: Clique na opção Ok. 
 
7ºPasso: Automaticamente será aberto outro menu, pra confirmar a senha que você colocou.Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
38 
8ºPasso: Coloque novamente leiaut e clique no Ok. 
 
9ºPasso: Clique agora na célula B5 e tente alterá-la. 
 
Observe que automaticamente a sua planilha ficará impossibilitada para ser alterada. 
 
 
 
 
 
 
 
 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
39 
UTILIZANDO FUNÇÕES FINANCEIRAS 
FUNÇÃO VP 
A função Valor Presente nos retoma a quantia ao final de um investimento e é usada para ver o quanto é 
necessário aplicar hoje para ter aquela quantia após determinado período de tempo. 
1ºPasso: Construa a seguinte planilha. 
 
Utilizaremos essa função para sabermos por exemplo, quanto será necessário aplicar hoje 
para termos, daqui 48 meses, o valor de R$300.000,00 com juros de 2% ao mês? 
2ºPasso: Insira os seguintes dados e confira a sua tabela abaixo. 
 
3ºPasso: Clique na célula F5, insira a seguinte fórmula: =VP(B5;C5;D5;A5;E5) e pressione enter. 
 
Observe como ficará 
sua planilha 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
40 
O valor retornado está na cor vermelha e entre parênteses. Isso indica que ele é um número 
negativo e que se for desembolsado (R$115.961,28), os R$300.000,00 com juros de 2% 
durante 48 períodos, serão alcançados. 
4ºPasso: Clique na célula A5, insira - R$300.000,00 e pressione Enter. 
 
Desta vez, o valor não aparece mais na cor vermelha. 
 
5ºPasso: Clique na célula D5 e digite R$300,00 
 
O valor agora foi reduzido para R$106.759,35 significando que se forem depositados R$300,00 
todo mês, se resgatará os mesmos R$300.000,00, porém com um valor presente menor. 
Função VP() – Valor Presente 
Sintaxe: =VP(TAXA;NPER;PGTO;[VF];[TIPO]) 
 
Retorna o valor presente de um investimento. O valor é o valor total correspondente ao valor atual de 
uma série de pagamentos futuros. Por exemplo: quando você pede dinheiro emprestado, o valor do 
empréstimo é o valor presente para quem empresta. 
 
 
 
 
 
 
 
 
Clique aqui e troque o 
sinal da célula 
Clique aqui e insira o 
valor 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
41 
5ºExercício do Excel 
Suponha que você está pensando em comprar uma anuidade de seguros que pagará R$ 500,00 ao final 
de cada mês por 1 ano. A quantia paga terá um ganho de 8%. Você quer determinar se não seria melhor 
pagar a quantia à vista. Para isso, usamos a função VP, que determina a quantia atual. Utilize a seguinte 
função em qualquer célula: =VP(8%;12;-500) O resultado será R$ 3.768,04.Esse resultado significa o 
valor total que terei que pagar pelo seguro neste momento. Demonstre em uma planilha. 
 
 FUNÇÃO PGTO 
Essa função, será relacionada com a realização de um pagamento mensal, sobre uma taxa de juros. 
Vamos calcular um pagamento a ser efetuado ao comprar uma televisão no valor de R$750,00, pagando 
em 24 vezes, com juros de 2% ao mês. 
1ºPasso: Construa a seguinte planilha. 
 
2ºPasso: Clique na célula A5 e insira o valor R$750,00 
 
 
 
 
 
 
Clique aqui e insira o 
valor 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
42 
3ºPasso: Clique na célula B5, insira o valor 0 e dê enter, porque você ainda irá descobrir o valor a ser 
encontrado no futuro. 
 
 
4ºPasso: Clique na célula C5, insira o valor 2% e dê enter. 
 
5ºPasso: Clique na célula D5, insira o valor 24 e após pressione a tecla Enter. 
 
 
 
Clique aqui e insira o 
valor 
Clique aqui e insira o 
valor 
Clique aqui e insira o 
valor 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
43 
6ºPasso: Clique na célula E5, insira o valor 1, indicando que o pagamento será efetuado no início do 
período e após, pressione Enter. 
 
7ºPasso: Para calcular o pagamento, clique na célula F5, digite: =PGTO(C5;D5;A5;B5;E5) e pressione 
enter. 
 
Observe que o valor retornado foi (R$38,88) e ele está na cor vermelha. Isto aconteceu 
porque este é o valor a ser desembolsado para o pagamento do produto. 
Função PGTO() – Pagamento mensal 
Sintaxe: =PGTO(TAXA;NPER;VP;VF;TIPO) 
 
Taxa – deve-se informar a taxa de juros por período (constante para todos os períodos); 
Pgto – deve-se indicar o pagamento efetuado em cada período. Também deve ser constante; 
Vp – Valor presente, ou seja, o valor atual da soma total de uma série de pagamentos futuros; 
Vf – Valor futuro, ou seja, o saldo em dinheiro que o usuário deseja obter após o último pagamento ser 
efetuado. Opcional; 
 
Tipo – Tipo é o número 0 ou 1 e indica a data de vencimento dos pagamentos. 
Se tipo for omitido, será considerado 0. 
Defina tipo para Se a data de vencimento for 
 
0 No final do período 
1 No início do período. Opcional; 
Retorna o pagamento periódico de uma anuidade. 
 
 
 
Clique aqui e insira o 
valor 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
44 
EXERCÍCIO APLIADO AO EXCEL - PGTO 
Vamos supor que você fez um empréstimo a alguém de R$ 1.500,00 a serem pagos durante 8 meses a 
uma taxa anual de 10% (0,83% ao mês). Para saber o valor de cada prestação, digite a seguinte função 
em qualquer célula: =PGTO(0,83%;8;1500). Demonstre em uma planilha utilizando a função: 
 
FUNÇÃO NPER 
Essa função possui como finalidade encontrar o período necessário, para atingir determinados valores. 
1ºPasso: Construa a seguinte planilha. 
 
Vamos calcular o número de períodos que serão necessários para atingir R$6200,00 com uma 
taxa de juros de 1,5% e efetuando um pagamento de R$300,00 por período. 
2ºPasso: Clique na célula A8, digite 0 e dê enter. 
 
 
 
 
 
 
 
 
 
Clique aqui e insira o 
valor 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
45 
3ºPasso: Clique na célula B8, digite o valor R$6200,00 e dê enter. 
 
4ºPasso: Clique na célula C8, digite 1,5% e dê enter. 
 
5ºPasso: Clique na célula D8, digite R$-300,00 (pois este é o valor a ser desembolsado) e pressione 
Enter. 
 
 
Clique aqui e insira o 
valor 
Clique aqui e insira o 
valor 
Clique aqui e insira o 
valor 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de LimaCavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
46 
6ºPasso: Clique na célula E8, digite 0 e pressione Enter. 
 
7ºPasso: Vamos calcular os períodos. Clique na célula E8, digite a seguinte fórmula: 
=NPER(C8;D8;A8;B8;E8) e pressione enter. 
 
Observe que o valor obtido foi pouco mais de 18 meses. 
FUNÇÃO TAXA 
Essa função terá como finalidade encontrar uma taxa de juros que está sendo aplicada por exemplo em 
um contrato de empréstimo. 
1ºPasso: Construa a seguinte planilha. 
 
Iremos agorar calcular a taxa de juros de um empréstimo no valor R$20.000,00 a ser pago em 
24 parcelas de R$1070,00 
 
Clique aqui e insira o 
valor 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
47 
2ºPasso: Clique na célula A11, digite R$20.000,00 e após, pressione enter. 
 
 
3ºPasso: Clique na célula B11, digite 0 e pressione enter. 
 
4ºPasso: Clique na célula C11, digite 24 e pressione enter. 
 
 
 
Clique aqui e insira o 
valor 
Clique aqui e insira o 
valor 
Clique aqui e insira o 
valor 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
48 
5ºPasso: Clique na célula D11, digite R$-1070,00. Neste caso, o valor deve ser obrigatoriamente 
negativo para que o cálculo dê certo e pressione enter. 
 
6ºPasso: Clique na célula E11, digite 0 e pressione enter. 
 
7ºPasso: Clique na célula F11, digite a função: =TAXA(C11;D11;A11;B11;E11), pressione Enter. 
 
 
Clique aqui e insira o 
valor 
Clique aqui e insira o 
valor 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
49 
Função TAXA() – Taxa de Juros 
 
Sintaxe: =TAXA(NPER;PGTO;VP;VF;TIPO;ESTIMATIVA) 
 
Retorna a taxa de juros por período um uma anuidade. 
Estimativa – opcionalmente, o usuário dá uma estimativa para a taxa. Se omitido este parâmetro, o valor 
será de 10%, por padrão. 
 
EXERCÍCIO APLIADO AO EXCEL - TAXA 
 
Supomos que você queira saber a taxa de juros que está sendo cobrada por um empréstimo feito por 
você de R$ 2.000,00 em 48 meses, com pagamentos mensais de R$ 200,00. Demonstre em uma planilha 
utilizando a função: =TAXA(48;-200;2000) 
 
 
FUNÇÃO VF – VALOR FUTURO 
A função Valor Futuro nos possibilita saber o valor de um investimento que será realizado mediante o 
período de tempo, percentual de variação e um padrão de pagamento. 
Sintaxe: =VF(TAXA;NPER;PGTO;VP;TIPO) 
 
Supomos que você deseja investir em certo equipamento. Porém, para você atingir sua meta, será 
necessário investir na bovespa, sabendo que ela lhe rende 3% ao mês. Seu depósito inicial será de R$ 
1.000,00 e seu depósito mensal e constante será de R$ 150,00, no início de cada período. Este depósito 
será feito durante 1 ano, que é o tempo que você necessita para obter um valor futuro. E que valor é 
esse? Demonstre em uma planilha utilizando a função: 
 
=VF(3%;12;-150;-1000;1) O resultado será R$ 3.618,43 
 
EXERCÍCIO APLIADO AO EXCEL – VALOR FUTURO 
 
Supondo que você já possui R$1500,00 e deseja guardar mensalmente R$150,00 na poupança, sabendo 
que a poupança dá um retorno mensal de 0,6% quanto você conseguiria juntar em um ano? 
VALOR PRESENTE LÍQUIDO OU VALOR PRESENTE? 
Valor Presente Líquido ou Valor Presente qual eu devo utilizar? Essa é uma dúvida bastante comum pelas 
pessoas que trabalham com fórmulas financeiras. 
1)O VPL será utilizado quando temos várias formas de pagamento diferentes, por exemplo. 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
50 
2)O VP será utilizado quando a forma de pagamento é constante, por exemplo. 
 
3)O VP também será utilizado quando os períodos de pagamento forem bastante amplos, facilitando o 
cálculo em relação ao VPL, na qual precisará que os seus valores estejam estabelecidos na sua planilha. 
 
Observe aqui 
Compare as duas 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
51 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
52 
3ª Aula de Excel Avançado 
 
SINOPSE DA AULA 
Nesta 3ºaula do Curso de Excel Avançado, você aprenderá a utilizar o comando Validação de Dados, 
Formatação Condicional, Função Índice, Corresp, entre outras... 
 
VALIDAÇÃO DE DADOS 
A validação de dados é um recurso do Excel que você pode usar para definir restrições em quais dados 
podem ou devem ser inseridos em uma célula. Você pode configurar a validação de dados para impedir 
que os usuários insiram dados inválidos. Se preferir, pode permitir que os usuários insiram dados 
inválidos, mas avisá-los quando tentarem digitar esse tipo de dados na célula. 
Também pode fornecer mensagens para definir a entrada esperada para a célula, além de instruções 
para ajudar os usuários a corrigir erros. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Clique no menu dados. 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
53 
3ºPasso: Selecione a opção validação de dados. 
 
4ºPasso: Clique na sub opção validação de dados. 
 
5ºPasso: Observe o menu que será aberto automaticamente. 
 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
54 
6ºPasso: Na opção permitir, selecione a opção lista. 
 
7ºPasso: Na opção fonte, insira a seguinte fórmula = A3:A7 
 
 
 
 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
55 
8ºPasso: Clique em Ok. 
 
9ºPasso: Observe como ficará. 
 
 
 
 
 
 
 
 
 
 
 
 
 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
56 
Formatação Condicional 
A formatação condicional permite que você altere a aparência de um controle com base em valores que 
os usuários inserem no formulário do Microsoft Office Excel 2007. Você poderádefinir condições que 
determinam a formatação para cada controle, como estilo da fonte, cor do texto e cor de plano de fundo. 
Você também pode ocultar ou desabilitar um controle. Os controles podem ter várias condições, o que 
significa que a aparência de um controle pode ser alterada de várias formas dependendo dos valores 
inseridos nele. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Clique agora no menu início. 
 
 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
57 
3ºPasso: Selecione todas as notas dos alunos. 
 
4ºPasso: Selecione agora a opção Formatação Condicional. 
 
 
 
 
 
 
 
Clique aqui 
Selecione dessa forma 
as notas dos alunos 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
58 
5ºPasso: Clique na opção Realçar Regras das Células. 
 
6ºPasso: Clique na opção Émaior do que... 
 
 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
59 
7ºPasso: Observe o menu que deverá aparecer. 
 
8ºPasso: Clique na seguinte opção e escolha a opção Preenchimento Verde. 
 
Observe as opções dadas para destacar as células que serão maiores do que o valor que você 
indicar na área ao lado. 
9ºPasso: Insira o valor 4 no retangulo em branco e clique em ok. 
 
 
 
 
 
 
 
 
1ºClique aqui 
1ºInsira aqui 
2ºClique aqui 
2ºClique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
60 
10ºPasso: Observe como ficará a sua planilha. 
 
7ª Exercício de Excel Avançado 
Use agora as opções menor que, igual a e entre para validações da tabela que você está utilizando. 
 
Observe como deverá ficar a sua planilha, considerando as células menores do que 6,25. 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
61 
Função Índice 
Algumas pessoas têm dificuldades em usar o agrupamento das funções =ÍNDICE() e =CORRESP(), mas 
basta entender como cada uma delas funciona fica fácil. 
Primeiros vamos entender a função =ÍNDICE(). 
Essa função busca em qualquer coluna, em uma base pré- estabelecida pelo usuário, um valor com base 
em um número de linha. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Clique na célula a7, e insira a seguinte fórmula: =ÍNDICE(A2:C5;1;2) 
 
 
 
 
 
 
 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
62 
3ºPasso: Altere a sua fórmula da seguinte maneira, delete o número 2 e coloque 3 em seu lugar. 
 
4ºPasso: Observe que o resultado automatcamente passará a ser 28, pois, você alterou seu resultado 
da 2 coluna para 3 coluna. 
 
5ºPasso: Agora apague o número 1 e insira o valor 2, para você observar a diferença. 
 
Observe que automaticamente você escolheu o número da linha dois e da coluna três, ou seja, o 
primeiro número representará a linha e o segundo número representará a coluna. 
 
Clique aqui e insira 3 
É importante sempre 
observar as instruções das 
funções 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
63 
8ª Exercício de Excel Avançado 
Monte a planilha a seguir e responda as questões utilizando a função índice. 
 
Responda as questões na coluna E. 
a)Qual a fruta que tem na loja 2? 
b)Qual o preço da cereja? 
c)Qual loja possui abacaxi? 
d)Qual a validade da larajna? 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
64 
Função CORRESP 
A função CORRESP será utilizada quando você não souber o número da linha. Essa função retorna o 
número da linha com base em alguns critérios estabelecidos pelo usuário. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Por exemplo, a função =CORRESP(“Kelvin”;A2:A5;0) iria retornar o valor 1. Por quê? 
Explicação 1: Simples, o primeiro parâmetro dessa função está dizendo que o valor procurado é Kelvin. 
No intervalo de A2 até A5 e a correspondência é 0, isto é, exata. 
Juntando as duas fórmulas, teríamos: =ÍNDICE(A2:C5;CORRESP(“Kelvin”;A2:A5;0);2) 
 
Viu a simplicidade? No lugar de colocar o valor de linha 1 na função índice, colocamos a função corresp, 
que vai buscar esse valor e botar na índice. Se você mudar o nome do Kelvin para qualquer outro lugar 
na coluna A, automaticamente a função corresp retornará a linha correspondente. 
EXERCÍCIO DE EXCEL APLICADO 
Monte a planilha a seguir e responda as questões utilizando a função Corresp. 
 
Qual a colocação da Vila Isabel? 
Qual a colocação da Porto da Pedra? 
Qual a colocação da Unidos da Tijuca? 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
65 
EXERCÍCIO DE EXCEL APLICADO 
Monte a planilha a seguir e responda as questões utilizando a função Corresp e índice. 
 
CRIANDO FOLHA DE PAGAMENTO(PROJETO 1) 
Agora iremos criar uma folha de pagamento de uma empresa com 10 funcionários, funções diferentes, 
contribuições ficais diferentes. 
1ºPasso: Construa a seguinte planilha. 
 
Na tabela temos os códigos, nomes, função, salário bruto e horas extras. Vamos adicionar 
valores de venda aos vendedores para que possamos calcular as suas comissões. Temos 
abaixo duas tabelas, uma de descontos e uma de comissões. 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
66 
CÁLCULO DO INSS 
Para o cálculo do INSS será baseado no salário bruto em relação a tabela,. Então se o salário do 
funcionário for até R$1000,00 ele terá 8% de INSS, se for até R%1500,00 terá 9% de desconto e acima 
disso 10%. Utilizaremos então a função Se. 
1ºPasso: Clique na célula E5 e insira a seguinte fórmula: =SE(D5<B19;C19;SE(D5<B20;C20;C21)) 
 
Explicando, primeiro verifico se o salário do funcionário D5 é menor que o valor da tabela B19, 
e defino que se for, mostre o valor do desconto C19, se não for, ele repete o teste mas agora 
com o segundo valor B20 e retorne o valor de descontoC20 caso ambos os testes tenham 
dado como falso, retorna o terceito valor C21. 
2ºPasso: Clique na célula E5 e mude sua fórmula para: =D5*SE(D5<B19;C19;SE(D5<B20;C20;C21)) 
 
 
Clique aqui e insira a 
fórmula 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
67 
3ºPasso: Clique na célula E6 e adapte sua fórmula: =D6*SE(D6<B19;C19;SE(D6<B20;C20;C21)) 
 
EXERCITANDO 
Continue com a mesma lógica das células D5 e D6 e continue adaptando a fórmula para as demais 
células da coluna INSS. 
1ºPasso: Observe como deverá ficar seu projeto. 
 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
68 
VALE REFEIÇÃO 
O vale refeição terá valor fixo de R$50,00 então deverá ser colocado em toda a coluna. 
1ºPasso: Observe como deverá ficar. 
 
VALE TRANSPORTE 
Agora vamos calcular o desconto de VT, vamos verificar primeiro se o desconto é maior que R$96,00, se 
for maior então ele retornará para R$96,00, caso contrário ele fará o cálculo. 
1ºPasso: Clique na célula G5 e insira a seguinte fórmula: =SE((D5*B22)>96;96;(D5*B22)) 
 
 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
69 
2ºPasso: Clique na célula G6 e insira a seguinte fórmula: =SE((D6*B22)>96;96;(D6*B22)) 
 
 
 
EXERCITANDO 
Continue com a mesma lógica das células G5 e G6 e continue adaptando a fórmula para as demais 
células da coluna Vale Transporte. 
1ºPasso: Observe como deverá ficar seu projeto. 
 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
70 
VALOR COMISSÃO 
Aqui será feita a multiplicação do valor vendido pela verificação do valor em relação a tabela. 
1ºPasso: Clique na célula I10 e insira a seguinte fórmula: =H10*(SE(H10<E19;F19;SE(H10<E20;F20;F21))) 
 
2ºPasso: Clique na célula H10, insira o valor 150 e dê enter. 
 
 
 
Clique aqui e insira a 
fórmula 
Observe que automaticamente 
foi calculado o valor da 
comissão 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
71 
3ºPasso: Clique na célula I11 e insira a seguinte fórmula: =H11*(SE(H11<E19;F19;SE(H11<E20;F20;F21))) 
 
4ºPasso: Clique na célula H11, insira o valor 350 e dê enter. 
 
 
 
 
Clique aqui e insira a 
fórmula 
Observe que automaticamente 
foi calculado o valor da 
comissão 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
72 
EXERCITANDO 
Continue com a mesma lógica das células I10 e I11 e continue adaptando a fórmula para I12 da Coluna 
das Comissões. 
1ºPasso: Observe como deverá ficar seu projeto. 
 
Observe que a fórmula das comissões só foram utilizadas nas células I10,11 e 12 porquê 
apenas esses funcionários trabalham no setor de vendas. 
SALÁRIO LÍQUIDO 
O salário líquido será a soma do salário bruto mais comissão mais horas extras e subtraído do valor do 
INSS,VT e VR. 
1ºPasso: Clique na célula K5 e insira a seguinte fórmula: =(D5+I5+J5)-(E5+F5+G5) 
 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
73 
EXERCITANDO 
Continue com a mesma lógica da células K5 e continue adaptando a fórmula para as demais da Coluna 
Salário Líquido. 
1ºPasso: Observe como deverá ficar a sua planilha. 
 
É importante lembrar que neste exemplo como nos que já vimos e veremos ao longo do curso, 
são dados fictícios. O objetivo é mostrar como se monta o cálculo. 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
74 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
75 
4ª Aula de Excel Avançado 
 
SINOPSE DA AULA 
Nesta 4ºaula do Curso de Excel Avançado, você aprenderá a utilizar o comando Somar Produto, Criando 
vículo entre as planilhas, irá realizar uma revisão das aulas passadas além de aprender algumas novas 
funcionalidades no excel. 
SOMAR PRODUTO 
Multiplica cada valor de um vetor por seu elemento correspondente em outro vetor, e retorna o valor 
somado. Por exemplo, se temos uma configuração como a mostrada na tabela abaixo. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Insira na célula b5 a seguinte fórmula: =SOMARPRODUTO(B2:B4,C2:C4) 
 
3ºPasso: Observe o resultado. 
 
Esse resultado representa a expressão: (2*10) + (3*20) + (4*30) = 20+60+120 = 200. 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
76 
EXERCÍCIO APLICADO AO EXCEL 
Revisão Somar Produto 
 
1)Vamos lá, suponhamos que eu quero comprar todas as “casa” que sejam “rosa”, quanto ficaria pra 
comprar todas? 
Eis a fórmula = SOMARPRODUTO((A5:A10="Casa")*(B5:B10="rosa")*(D5:D10)) 
Resultado: R$ 63.000,00 
2) Calcule o valor total das horas trabalhadas de todos funcionários. 
 
Resultado do cálculo: 52608 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
77 
FUNÇÃO SOMA SE 
Essa função será utilizada para somar um intervalo de células mediante uma condição estabelecida 
previamente, ou seja, deverá realizar a soma se houver a condição. 
1ºPasso: Construa a seguinte tabela. 
 
2ºPasso: Digite na célula c9 a fórmula =SOMASE(D3:D8;”PG”;C3:C8) 
 
 
 
 
 
 
 
 
 
 
Clique aqui e insira a 
fórmula 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
78 
FUNÇÃO SOMASE OU SOMAR PRODUTO? 
Essas duas funções possuem finalidades muito parecidas, porém quando eu deverei utilizarcada uma 
delas? Esse é um questionamento bastante pertinente dos alunos no curso de Excel. 
1)A função soma se será utilizada para a realização de diversos somatórios mediante uma condição. 
Essa planilha abaixo é um bom exemplo, visto que, seria necessário fazer a soma individual dos valores 
em função dos “pg” (pagos), com o auxílio dessa função, o somatório de planilhas fica muito mais 
dinâmico. 
 
2)A função somar produto será utilizada para a realização de diversas multiplicações entre as planilhas de 
maneira extremamente rápida. 
Essa planilha abaixo é um bom exemplo, visto que, seria necessário fazer a multiplicação de todos esses 
valores para encontrar o número de horas trabalhadas por todos os trabalhadores da empresa. 
 
 
 
 
 
 
Utilizada para realizar 
soma de valores 
mediante uma condição 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
79 
3)A função somar produto também poderá ser utilizada para realizar a soma de produtos que possuem 
qualidades específicas. 
Essa planilha abaixo é um bom exemplo, visto que, seria necessário fazer a soma individual dos valores 
em função das casas que fossem da cor rosa com o auxílio dessa função, o somatório de planilhas fica 
muito mais fácil. 
 
Resumindo = Antes de utilizar qualquer uma das funções no Excel, você deverá raciocinar qual o meio 
mais rápido de realizá-lo e qual função se encaixa melhor para a sua demanda. Lembre-se, para alcançar 
um objetivo sempre temos vários meios, porém a distinção entre eles será a maneira que serão 
realizados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
80 
EXERCÍCIO APLICADO AO EXCEL 
Revisão usando as funções (cont.se, soma.se, contar.vazio) 
 
CRIANDO VÍNCULOS ENTRE PLANILHAS 
1ºPasso: Construa a seguinte planilha. 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
81 
2ºPasso: Clique com o botão direito no seguinte ítem. 
 
3ºPasso: Selecione agora a opção Renomear. 
 
4ºPasso: Agora, você poderá colocar por exemplo o nome Jan. 
 
 
 
Clique aqui com o 
botão direito 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
82 
5ºPasso: Clique na opção Plan2 e construa a seguinte planilha. 
 
6ºPasso: Clique na célula A11 e digite: Total de Jan+Fev. 
 
7ºPasso: Na célula B11 digite o seguinte =SOMA( então clique na célula B7. 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
83 
8ºPasso: digite ; e clique na planilha Jan. 
 
9ºPasso: Clique na célula B7. 
 
 
 
 
 
 
 
 
 
 
Clique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
84 
10ºPasso: Feche o parenteses e dê enter. 
 
Observe que automaticamente aparecerá o seu resultado. 
9ª Exercício de Excel Avançado 
1ºPasso: Insira gastos variáveis em cada um dos meses, como alimentação, academia, telefone celular, 
plano de saúde e gasolina. Determine esses gastos nas duas tabelas com valores diferentes entre eles. 
2ºPasso: Na tabela do mês de Março insira o somatório dos gastos dos dois meses. 
3ºPasso: Crie a planilha porcentagem. 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
85 
REVISÃO 1 
Agora vamos começar a realizar alguns exercícios revisão, para trabalhar tudo aquilo que foi estudado 
durante as aulas anteriores. 
1ºPasso: Construa a seguinte planilha. 
 
 
2ºPasso: Execute os seguintes comandos para as colunas de cálculos: 
a)Com base na quantidade e no valor unitário, calcule o valor total. 
Precisamos apenas multiplicar um pelo outro. Teremos então: =C3*D3 
b)Em saldo atual, calcule quantos produtos ainda têm no estoque, utilizando as quantidades e as saídas. 
Se sabemos a quantidade (C3) e a saída (H3), basta subtrair. Veja abaixo: 
=quantidades – saídas. No Excel teremos: =C3-H3 
c)Em comprar (sim/não), se o saldo atual for menor que o estoque mínimo, aparecerá SIM, caso 
contrário, aparecerá NÃO. 
A própria questão já é explicação. No Excel, teremos: 
=SE(I3<F3;”SIM”;”NÃO”) 
d)Se em Comprar for SIM, utilize o estoque máximo e o saldo atual para informar quantos produtos 
deverão ser comprados, caso contrário, aparecer 0 (zero). 
Nessa questão, também a própria qeustão já é a explicação do que devemos fazer. 
Portanto temos: 
=SE(J3=”SIM”;G3-I3;0) 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
86 
 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
87 
5ª Aula de Excel Avançado 
 
SINOPSE DA AULA 
Nesta 5ºaula do Curso de Excel Avançado, você aprenderá a mesclar e dividir células, irá trabalhar com a 
função PROCV/PROCH além de realizar vários exercícios sobre diversas funções. 
 
ASSISTENTE DE FUNÇÃO 
O Excel ajuda a utilizar uma função cujo conteúdo não conhece com certeza. É o assistente de função. 
Por meio dele você informa ao programa qual função deseja inserir na célula atual e ele, com suas caixas 
de diálogo, ensina e pergunta qual é o conteúdo dos argumentos da função. 
1ºPasso: Crie a seguinte planilha. 
 
2ºPasso: Clique na célula B2 e no menu Fórmulas. 
 
3ºPasso: Selecione a opção Inserir Função. 
1ºClique aqui 
2ºClique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
88 
 
4ºPasso: Automaticamente será aberto o menu Inserir Função. 
 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
89 
5ºPasso: Clique na seguinte seta. 
 
6ºPasso: Observe as opções informadas pelo Excel. 
 
 
 
 
Clique aqui 
Essas opções são de categorias 
de fórmulas quepoderam ser 
utilizadas por você na 
execução dos seus projetos 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
90 
7ºPasso: Selecione a opção Texto, observe todas as fórmulas de texto que poderam ser utilizadas pelo 
Excel. 
 
8ºPasso: Selecione a opção Mais recentemente usada e observe as fórmulas. 
 
 
 
 
Observe aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
91 
9ºPasso: Clique na seguinte setinha da barra de rolagem para que você possa visualizar as demais 
opções informadas na categoria „Mais recentemente usada‟. 
 
10ºPasso: Coloque sua barra de rolagem para cima, selecione a opção média e clique no OK. 
 
 
 
 
Clique aqui 
1ºClique aqui 
2ºClique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
92 
11ºPasso: Automaticamente será aberto o menu Argumentos da Função. 
 
12ºPasso: Na opção Núm2, coloque C1 e clique em Ok. 
 
Observe que automaticamente o Excel lhe fornece uma aba para você inserir um Núm3 para 
que ele possa fazer a média das 3 opções. 
 
 
Observe aqui a célula 
selecionada 
O valor existente na 
célula 
1ºInsira aqui 
Observe aqui, que a 
fórmula é alterada, 
instantaneamente 
2ºClique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
93 
13ºPasso: Observe a sua tabela, como o comando foi inserido instantaneamente. 
 
PERSONALIZANDO ATALHOS 
Agora você aprenderá a personalizar a Barra de Ferramentas de Acesso Rápido para que tenha os 
comandos mais utilizados sempre à disposição. 
1ºPasso: Clique no botão Office e selecione Opções do Excel. 
 
 
 
 
 
 
1ºClique aqui 
2ºInsira aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
94 
2ºPasso: Automaticamente será aberto para você as seguinte opções, observe. 
 
3ºPasso: Clique com o botão esquerdo do seu mouse na opção Personalizar. 
 
1ºClique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
95 
4ºPasso: Selecione a opção Impressão Rápida e depois clique em Adicionar. 
 
5ºPasso: Observe que automaticamente a opção Impressão Rápida foi adicionada a Barra de 
Ferramentas de Acesso Rápido, clique em Ok pra finalizar. 
 
1ºClique aqui 
2ºClique aqui 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
96 
6ºPasso: Observe a parte superior esquerda da sua tela na barra de acesso rápido que agora a opção 
imprimir está lá a sua disposição. 
 
REVISANDO 
1ºPasso: Insira a barra de Acesso Rápido as opções de Inserir Células, Inserir Gráficos e Classificação 
Cresente. Deverá ficar assim o seu arquivo. 
 
MESCLAR E DIVIDIR CÉLULAS 
Para mesclar duas ou mais células adjacentes, sejam elas verticais ou horizontais: 
1ºPasso: Construa a seguinte planilha como exemplo. 
 
Observe aqui 
Observe aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
97 
2ºPasso: Selecione as seguintes células. 
 
Obs: Lembre-se que somente os dados na célula superior esquerda permanecerão na célula mesclada 
sendo o conteúdo das demais descartados. 
3ºPasso: Selecione a opção Mesclar e Centralizar. 
 
 
 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
98 
4ºPasso: Observe como ficará a sua planilha. 
 
 
EXIBINDO PASTAS DE TRABALHO NA MESMA JANELA 
Essa função é de extrema importância para você poder comparar rapidamente duas planilhas na mesma 
pasta de trabalho ou em pastas de trabalho diferentes exibindo-as lado a lado, uma abaixo da outra ou 
até mesmo exibir várias planilhas na mesma janela ao mesmo tempo. 
1ºPasso: Clique no botão Office e selecione a opção Novo. 
 
 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
99 
2ºPasso: Automaticamente será aberto para você a opção Nova Pasta de Trabalho. 
 
3ºPasso: Clique na opção Criar. 
 
 
 
 
 
 
Clique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
100 
4ºPasso: Selecione agora o menu Exibição e clique na opção Exibir lado a lado. 
 
5ºPasso: Automaticamente ficará aberto as duas planilhas para que você possa comparar possíveis 
dados, ou caso você deseje trabalhar nas duas simultaneamente. 
 
 
 
 
 
 
 
1ºClique aqui 
2ºClique aqui 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
101 
FUNÇÃO PROCV 
Procura a partir da primeira coluna e linha de uma matriz para retornar o valor de uma célula. 
1ºPasso: Construa a seguinte planilha. 
 
2ºPasso: Abaixo digite a planilha de resumo, a idéia é quando o nome da pessoa for digitado, o sistema 
utilizara a função PROCV para procurar e mostrar os demais dados como Endereço, Bairro, etc. 
 
FÓRMULAS: 
A)Nesta célula (H15)deverá ser digitado o nome da pessoa que você deseja procurar. 
 
B) =PROCV(H15;A2:E16;2;0) 
C) =PROCV(H15;A2:E16;3;0) 
D) =PROCV(H15;A2:E16;4;0) 
E) =PROCV(H15;A2:E16;5;0) 
=PROCV(CELULA ONDE ESTAR O VALOR A SER PROCURADO;COORDENADA DA PLANILHA ONDE 
O VALOR SERA PROCURADO;COLUNA QUE VOCÊ DESEJA EXIBIR;VALOR LOGICO – 0 SIGNIFICA 
VALOR APROXIMADO) 
Excel Avançado – Autoria (Família Carielo) 
LEIAUT Carielo – Escola de Informática com Eletrônica 
Av. Governador Carlos de Lima Cavalcante, nº 168 – Derby / Recife PE 
Fone: 3223.0387 
 
102 
10ª Exercício de Excel Avançado 
Revisão Função