Apostila - Microsoft Excel 2016 - Avançado

•

UFPR

Nicolas Enzo

28/05/2020

E aí, curtiu este material?

Ajude a incentivar outros estudantes a melhorar o conteúdo

Gostou desse material? Compartilhe! 🧡

Excel Avançado

1.610 Materiais compartilhados

Baixe o app para aproveitar ainda mais

Leia os materiais offline, sem usar a internet. Além de vários outros recursos!

Prévia do material em texto

EXCEL 2016
AVANÇADO

Material de apoio ao curso de
Excel Avançado

Criação:
Instrutor Nicolas Alexandre Enzo
nicolasaenzo@hotmail.com
Revisão:
Isabel Mariane de Oliveira

Prezado(a) Aluno(a)
O objetivo ao desenvolver essa apostila é que ela seja utilizada como material de apoio ao curso
ministrado na Upgrade Informática, não recomendamos que está apostila seja utilizada
isoladamente no estudo.
Aproveitem o curso e obrigado!
O autor.

Nome: ________________________________
Tel. Contato: ___________________________
3

Sumário
LIÇÃO 01 - REVISÃO EXCEL BÁSICO ......................................................................................... 5
✓ Equações Matemáticas Básicas ........................................................................................... 5
✓ Revisão função SE: .............................................................................................................. 5
✓ Revisão Função E e OU ....................................................................................................... 7
✓ Exemplo 01: ......................................................................................................................... 7
✓ Exemplo 02: ......................................................................................................................... 8
✓ Exemplo 03: ......................................................................................................................... 8
LIÇÃO 02 - FUNÇÕES DE DATA, HORA ...................................................................................... 9
✓ Funções de Data: ................................................................................................................. 9
✓ Exemplo 01: ......................................................................................................................... 9
✓ Exemplo 02: ....................................................................................................................... 12
✓ Exemplo 03: ....................................................................................................................... 13
✓ Revisão: ............................................................................................................................. 14
LIÇÃO 03 - FUNÇÕES SOMASE, SOMASES ............................................................................. 15
✓ Função SOMASE. .............................................................................................................. 15
✓ Pesquisas diferenciadas ..................................................................................................... 16
✓ Revisão .............................................................................................................................. 17
✓ Função SOMASES() .......................................................................................................... 18
✓ Revisão .............................................................................................................................. 18
LIÇÃO 04 - FUNÇÃO CONT.SE() E VALIDAÇÃO DE DADOS .................................................... 20
✓ Função CONT.SE() ............................................................................................................ 20
✓ Exemplo 01 ........................................................................................................................ 20
✓ Nomenclatura de Células ................................................................................................... 20
✓ Criando a Validação de Dados (Lista Suspensa)................................................................ 21
✓ Função CONT.SES() .......................................................................................................... 23
✓ Outras Validações de dados:.............................................................................................. 23
✓ Revisão .............................................................................................................................. 26
LIÇÃO 05 - FUNÇÃO PROCV() ................................................................................................... 27
✓ Função PROCV() ............................................................................................................... 27
✓ Exemplo 01: ....................................................................................................................... 27
✓ Função SEERRO() ............................................................................................................. 30
✓ Exemplo 2 .......................................................................................................................... 31
✓ Anotações .......................................................................................................................... 31
✓ Revisão .............................................................................................................................. 32
LIÇÃO 06 - FUNÇÕES FINANCEIRAS ........................................................................................ 33
✓ Funções financeiras ........................................................................................................... 33
✓ Função VF() - Valor Futuro ................................................................................................. 33
✓ Exemplo 01 ........................................................................................................................ 33
4

✓ Revisão: ............................................................................................................................. 34
✓ Função VP() - Valor Presente ............................................................................................. 34
✓ Exemplo 02 ........................................................................................................................ 34
✓ Revisão: ............................................................................................................................. 35
✓ Função TAXA() .................................................................................................................. 35
✓ Exemplo 03 ........................................................................................................................ 35
✓ Revisão .............................................................................................................................. 36
✓ Função NPER() – Nº de Períodos ...................................................................................... 36
✓ Exemplo 04 ........................................................................................................................ 36
✓ Revisão: ............................................................................................................................. 37
✓ Função PGTO() - Pagamento............................................................................................. 37
✓ Exemplo 05 ........................................................................................................................ 37
✓ Revisão .............................................................................................................................. 38
LIÇÃO 07 - MACRO ..................................................................................................................... 39
✓ Oque são Macros ............................................................................................................... 39
✓ Habilitar Guia Desenvolvedor ............................................................................................. 39
✓ Referências Relativas ........................................................................................................ 40
✓ Exemplo 01........................................................................................................................ 40
✓ Referências Absolutas........................................................................................................ 42
✓ Exemplo 02 ........................................................................................................................ 42
✓ Revisão .............................................................................................................................. 46
LIÇÃO 08 - INTRODUÇÃO VBA .................................................................................................. 47
✓ Visual Basic Application (VBA) ........................................................................................... 47
✓ Iniciando a criação do código ............................................................................................. 47
✓ Criando uma caixa de aviso ............................................................................................... 48
✓ Criando uma caixa de confirmação .................................................................................... 48
✓ Formulário Automático ....................................................................................................... 49
✓ Criação de Formulário Simples .......................................................................................... 50
✓ Revisão: ............................................................................................................................. 54
LIÇÃO 09 - HIPERLINK E FUNÇÃO ÍNDICE ............................................................................... 55
✓ O que é Hiperlink? .............................................................................................................. 55
✓ Painel de navegação .......................................................................................................... 55
✓ Criando hiperlink ................................................................................................................ 57
✓ Revisão .............................................................................................................................. 58
LIÇÃO 10 - FUNÇÃO ÍNDICE ...................................................................................................... 59
✓ Função ÍNDICE() ................................................................................................................ 59
✓ Exemplo 1 .......................................................................................................................... 59
✓ Função CORRESP() .......................................................................................................... 60
✓ Exemplo 2 .......................................................................................................................... 61
LIÇÃO 10 - PROJETO LOJA ....................................................................................................... 62

LIÇÃO 01 - REVISÃO EXCEL BÁSICO

✓ Equações Matemáticas Básicas
Primeiramente devemos nos lembrar
das operações básicas de soma (+),
subtração (-), multiplicação (*) e divisão (/),
temos também o símbolo de
exponenciação (^).
Para relembrar esses símbolos faça a
tabela abaixo e resolva as equações:

Lembre-se também que o Excel
respeita as prioridades matemáticas em
uma fórmula como por exemplo:

• 2*4+4 = 12

• 2*(4+4) = 16

✓ FUNÇÕES BÁSICAS:

Entre as funções básicas mais
utilizadas nós encontramos as funções:
• =SOMA()
• =MÉDIA()
• =MÁXIMO()
• =MÍNIMO()
• CONT.NÚM()
Essas funções podem ser encontradas na
guia “Página Inicial” dentro da ferramenta
Auto Soma.

Para relembrar, crie a tabela abaixo e
responda os campos em branco utilizando
as funções correspondentes:

Caso apareça alguma dúvida pergunte ao
seu instrutor.

✓ Revisão função SE:
A função SE, a partir dos valores nela
adicionados, avalia os dados da célula e
retorna a expressão escolhida caso o
resultado for falso ou verdadeiro. Muito
utilizada como complemento em outras
funções, ela apresenta, em seu quadro de
argumentos a sequência:
=SE(teste_lógico;[valor_se_verdadeiro];
[valor_se_falso])
Para relembrar de como utilizar a função se
siga os exemplos apresentados:
✓ Exemplo 01:

Em uma empresa os funcionários recebem
comissão de 5% apenas sobre as vendas
acima de R$1.000,00.

1) Digite a tabela a seguir:

2) Agora clique na célula de comissão da
funcionária Karen e digite a seguinte
função:
=SE(C3>1000;C3*5%;C3*0%)

Para você entender melhor a função
SE é importante você saber “ler” ou melhor
interpretar o que está escrito, como no
exemplo anterior:
“SE a Venda (C3) for Maior (>) do que
R$1.000,00 Então terei 5% de comissão em
cima da venda (C3*5%) Se não eu terei 0%
de comissão (C3*0%). ”
Lembre-se também os símbolos de
comparação que são utilizados dentro do
Teste Lógico da função SE/ E/ OU são:
• > (Maior) ou >= (Maior igual)
• < (Menor) ou <= (Menor igual)
• = (igual) ou <> (Diferente)

Também devemos lembrar que todo texto
que se encontra dentro de uma função
SEMPRE estará entre aspas duplas ( “ ”).

✓ Exemplo 02:
Digite a tabela abaixo e em seguida crie
uma função se que satisfaça o enunciado a
seguir:
O valor do INSS sobre o salário na folha de
pagamento é de 9% para quem ganha acima
de R$1.500,00 e de 6% para quem ganha até
R$1.500,00.

3) Os resultados devem ser os mesmos que
os mostrados abaixo:

Resposta: =SE(C3>1500;C3*9%;C3*6%)

✓ Exemplo 03:
Lembre-se também que dentro do teste
lógico, valor se verdadeiro e valor se falso
você pode inserir outras funções para fazer
análises e até mesmo outras funções SE
4) Digite a tabela abaixo:

5) Agora clique na situação do aluno Nicolas
e digite a seguinte Função:
=SE(Média(C3:D3)>70;“Aprovado”;“Reprovado”)

6) O Resultado deve estar com mostra a
tabela abaixo:

7) Salve sua tabela.
✓ Revisão Função E e OU

Função E
Devolve VERDADEIRO se todos os
argumentos devolverem VERDADEIRO
devolve FALSO se um ou mais argumentos
devolverem FALSO.
=E(lógico1; [lógico2]; ...)

Função OU
Devolve VERDADEIRO se qualquer um
dos argumentos devolver VERDADEIRO,
devolve FALSO se todos os argumentos
devolverem FALSO.
=OU(lógico1; [lógico2]; ...)
Tanto a função E quanto a função OU são
utilizadas em sua grande maioria para
complementar a função SE possibilitando
assim analisar mais do que uma célula ao
mesmo tempo.
✓ Exemplo 01:
Em um Campeonato o time para se
classificar precisa de mais que 33 pontos ou
mais que 8 Vitórias. Sendo assim monte uma
função na coluna de resultado dizendo se o
time está Aprovado ou Reprovado.
8) Escreva a tabela abaixo:

9) Agora na coluna de resultado do time a
escreva a seguinte Função:
=SE(OU(C5>33;D5>8);"Aprovado";"Reprovado")

Da mesma maneira que na função SE
saber interpretar a função é essencial. Neste
caso podemos “Ler” a função da seguinte
maneira:
“SE a quantidade de pontos (C5) for
maior (>) do que 33 OU a quantidade de
vitórias (D5) for maior (>) do que 8 Então
aprovado Se não reprovado. ”
10) Faça a função para os outros times.

✓ Exemplo 02:
O funcionário só deverá ter o aumento
salarial se ele tiver o tempo de empresa maior
que 5 anos e a avaliação do supervisor maior
igual a 70%. Crie uma função na coluna de
aumento e diga se o funcionário vai ou não ter
aumento.
11) Digite a tabela abaixo:

12) Na célula de Aumento do Nicolas digite a
seguinte função:
=SE(E(C3>5;D3>=70%);"Sim";"Não")

13) Agora faça a mesma função para os outros
funcionários.

14) Salve sua planilha.

✓ Exemplo 03:
Escreva asplanilhas abaixo e em seguida
crie uma função que satisfaça o enunciado.

15) O Aluno para estar aprovado deve ter a
média igual ou superior a 70 e não pode ter
uma quantidade superior a 20 faltas, caso
contrário está reprovado.

Resposta:
=SE(E(C3>=70;D3<20);”Aprovado”;”Reprovado”)

✓ Anotações:

LIÇÃO 02 - FUNÇÕES DE DATA, HORA

✓ Funções de Data:
O Excel pode trabalhar com diversas
funções que se baseiam na data e hora do seu
computador. Veja a seguir as principais
funções do Excel para data e hora:
=HOJE() Retorna a data atual. É
amplamente utilizado para inserir
automaticamente a data atual na planilha.
=AGORA() Retorna a Data e hora
atual.
=DIA () Retorna o dia do mês da data em
questão, um número de 1 a 31.
=MÊS() Retorna o mês de uma data
em questão.
=ANO() Retorna o ano de uma data
em questão.
=DIATRABALHO() Retorna o número da
série da data antes ou depois de um número
especificado de dias úteis.
=DIATRABALHOTOTAL() Retorna à
quantidade de dias úteis entre duas datas.
=TEXTO() Converte um número em um
texto com o formato de número especifico.

✓ Exemplo 01:
1) Digite os dados mostrados a seguir.

2) Na célula B1 digite a seguinte função:
=AGORA()

3) Repare que nesta célula deverá aparecer
a sua data e hora. Ambas essas
informações sempre estarão atualizadas.

4) Agora na célula D1 escreva a seguinte
fórmula.
=TEXTO(B1; “DDDD”)

A função texto converte um número em um
texto a partir da formatação especificada que
neste caso foi de dia “dddd”, se você colocar
apenas “ddd” irá aparecer uma abreviação do
10

seu dia. Você também pode utilizar “mmmm”
ou “mmm” para aparecer por escrito o mês da
data em questão. A função texto pode ser
utilizada com outras especificações de
formatos, porém os mais utilizados são esses.
5) Clique na célula C4 e digite a seguinte
fórmula:
=A4+C4

6) Faça a mesma formula nas células abaixo
tendo o seguinte resultado:

7) Selecione de C4 até C10.

8) Clique com o botão direito dentro da
seleção e selecione a ferramenta
Formatar Células.

9) Na janela que apareceu clique na opção
de categoria Personalizado:

10) Agora no campo de tipo digite DDDD.

11) Clique em Ok

12) Agora repare que todas as suas datas de
vencimento estão mostrando apenas o dia
da semana.

13) Repare que alguns dias de vencimento
estão caindo em um domingo, para evitar
isso podemos utilizar a função texto()
junto com a função se().

14) Para isso vamos incrementar a seguinte
lógica na célula C4:
=SE(TEXTO(A4+B4;”dddd”)= “Domingo”; A4+B4+1;
A4+B4)

15) Faça a mesma função nas células abaixo
e veja que os dias de vencimento que
eram no domingo, foram para segunda
feira (A4+B4+1) e o restante ficou como
era antes (A4+B4).

16) Agora clique na célula D4 e digite a
seguinte formula:
=HOJE()-C4

17) Selecione a célula D4 e altere a
formatação do número para Geral.

18) Faça a mesma função nas células abaixo:

Obs: os valores são respectivos da data em
que este exercício foi desenvolvido, para você
estes valores serão maiores.
19) Neste nosso exemplo veja que tivemos
alguns valores negativos, o que significa
que a data ainda não aconteceu.

20) Para não aparecer valores negativos
utilizaremos novamente a função SE().
=SE(C4>HOJE();0;HOJE()-C4)

21) Faça a mesma função nas células abaixo
e repare que agora ao invés de valores
negativos foram substituídos por 0.

22) Continue a tabela digitando os seguintes
valores:

23) Agora na Célula E4 digite a seguinte
função:
=DIA(C4).

24) Repare que o resultado foi o dia do mês
do dia de vencimento.

25) Faça a mesma função nas células abaixo:

26) Na célula F4 digite a seguinte fórmula:
=MÊS(C4)

27) Faça a mesma função para as células
abaixo.

28) Na célula G4 digite a seguinte fórmula:
=ANO(C4)

29) Refaça a função nas células abaixo.

30) Sua planilha deve estar como mostra a
figura a seguir:

31) Se achar necessário faça algumas
alterações nas funções para melhor
entendimento e em seguida salve a
planilha em sua pasta.
✓ Exemplo 02:
A Função DIATRABALHO() retorna a data
final levando em consideração apenas os dias
úteis (de segunda a sexta), você também
pode ter uma tabela com dias de “feriados”
para que sejam descontados, sua sintaxe é:
=Diatrabalho(data_inicial;dias_uteis;[feriados])
Sendo opcional a inserção da parte de
feriados.
1) Abra uma nova planilha e digite os dados
a seguir:
Obs: lembre-se que o atalho para abrir um
novo arquivo em branco é CTRL+O.

2) Na célula C4 digite a seguinte fórmula:
13

=DIATRABALHO(A4;C4)

3) Repare que está célula por ainda estar
formatada como Geral aparecerá apenas
um número que representa aquela data.

4) Altere a formatação numérica da célula
para Data Abreviada.

5) Faça a mesma função nas células abaixo:

6) Esta data foi calculada contando apenas
os dias úteis (segunda a sexta), para ver a
quantidade de dias corridos (segunda a
domingo)

7) Na célula D4 digite a seguinte fórmula
para descobrir a quantidade de dias
corridos:
=C4-A4

8) Faça a mesma fórmula para as células
abaixo.

9) Salve sua planilha.
✓ Exemplo 03:

A Função DIATRABALHOTOTAL() retorna à
quantidade de dias úteis (segunda a sexta)
entre duas datas determinadas, você também
pode ter uma tabela com dias de “feriados”
para que sejam descontados. Sua sintaxe é:
=Diatrabalhototal (data_inicial; data_final;
[feriados])
Sendo opcional a inserção da parte de
feriados.

1) Abra uma nova planilha e digite os dados
abaixo:

2) Agora digite a seguinte fórmula na célula
C4:
=DIATRABALHOTOTAL(A4;B4)

3) Repare que a quantidade de dias úteis
entre as duas datas.

4) Faça a mesma função para as datas
seguintes.

5) Salve sua planilha.

✓ Revisão:
Digite as planilhas abaixo e resolva os
enunciados a partir do que foi visto nesta lição.
1) Calcule a data final e os dias de atraso a
partir da data em que você se encontra, se
não houver dias de atraso terá que
aparecer 0.

2) Defina a quantidade de dias uteis do
projeto.

3) Em que dia da semana será cada uma das
datas e utilizando as funções condicionais
revisadas na lição anterior mostre se é no
final de semana ou não:

LIÇÃO 03 - FUNÇÕES SOMASE, SOMASES

✓ Função SOMASE.
A função SOMASE soma os valores que
atendem aos critérios que você especificar.
Por exemplo, suponha que em uma coluna
que contém números, você deseja somar
apenas os valores maiores que 5, ou, que
você tenha produtos e seus determinados
preços e queira somar apenas um deles.
Sua sintaxe é a seguinte:
=SOMASE(intervalo; critérios;
[intervalo_soma] )
A sintaxe da função SOMASE tem os
seguintes argumentos:
Intervalo: O intervalo de células que se
deseja calcular por critérios. As células em
cada intervalo devem ser números ou nomes,
matrizes ou referências que contêm números.
Espaços em branco e valores de texto são
ignorados. O intervalo selecionado deve
conter datas no formato padrão do Excel
(exemplos abaixo).
Critérios: Os critérios na forma de um
número, expressão, referência de célula, texto
ou função que define quais células serão
adicionadas. Por exemplo, os critérios podem
ser expressos como 32, ">32", B5, "32",
"maçãs" ou HOJE().
Intervalo_soma: As células reais a serem
adicionadas, se você quiser adicionar células
diferentes das especificadas no argumento
intervalo.

1) Abra uma nova planilha do Excel 2016.

2) Digite os dados mostrados abaixo:

3) Clique na Célula B15 e digitea seguinte
função:
=SOMASE(A4:A12 ; B14 ; C4:C12)

Nesta função será somado apenas o valor do
produto sabão.
4) Clique na célula B14 e digite Detergente

5) Repare que o valor logo abaixo foi
alterado para a soma dos detergentes

6) Agora na célula na célula B18 crie uma
função que some os produtos da marca
OMO.

7) Caso tenha alguma dúvida a função tem
que ser a seguinte:
=SOMASE(B4:B12;B17;C4:C12)
8) Altera a marca e veja com os valores são
alterados também.

✓ Pesquisas diferenciadas
9) Em alguns casos podemos criar
pesquisas utilizando apenas parte da
palavra a ser pesquisada, por exemplo,
digamos que tempos sabão em pó e
sabão em pedra e você queira somar os
itens que contém a palavra “sabão”, em
casos como esses nós devemos utilizar o
asterisco (*) como símbolo chave de
pesquisa.

10) Clique na célula B14 e digite LUSTRA*

11) Repare que agora foi somado todos os
produtos que começam com a palavra
“Lustra”

12) Agora escreva SA* na célula B14

13) Agora foram somados todos os valores
que começam com SA (sabão e sabonete)

14) Você também pode colocar o asterisco no
começo da palavra para somar o que
termina com aquele termo como por
exemplo:

15) Neste caso foram somados todos os
valores que terminam com “gente”.

16) Outra maneira de se utilizar o asterisco é
colocar antes e depois da palavra e desta
maneira você consegue pesquisar um
valor que esteja em qualquer parte da
planilha.

Neste caso ele soma tudo que contenha “E”.
17) Quando essas buscas já são esperadas
na sua tabela você pode inserir o asterisco
dentro da função SOMASE.

18) Substitua a função da célula B15 pela
seguinte função:
=SOMASE(A4:A12; “ * ”&B14&“ * ” ;C4:C12)

Repare que o nesta função utilizamos o
símbolo & este símbolo se chama concatenar
ele junta duas ou mais operação ou texto na
mesma célula, iremos trabalhar mais a fundo
este símbolo nos exercícios seguintes.

19) Agora digite apenas “E” na pesquisa e
veja que o resultado é o mesmo pois os
asteriscos foram colocados dentro da
função.

20) Para suas pesquisas você não precisa
necessariamente pesquisar textos em
outras colunas, também é possível
pesquisar um critério numérico para se
somar.
Obs: lembre-se que os comparadores
numéricos são: >(maior), >=(maior igual),
<(menor), <=(menor igual), =(igual) e
<>(diferente).
21) Na sua planilha digite as seguintes
células:

22) Na célula B21 digite a seguinte função:

=SOMASE(C4:C12;B20)

23) Neste exemplo ele somou todos os
valores que são maiores do que 15.
✓ Revisão
Responda as seguintes questões:
Qual a soma dos produtos com a letra A?
_________________________________
Qual a soma das marcas que terminam com O?
_________________________________
Qual a soma dos produtos que terminam com
TE?
_________________________________
Qual a soma dos valores maior do que
R$7,00?
__________________________________

Anotações:
____________________________________
____________________________________
____________________________________
18

✓ Função SOMASES()
A função SOMASES é muito parecida com
a função SOMASE a única diferença é que, a
função SOMASES permite que você tenha
mais do que um critério, como no exemplo
anterior, somar todos os produtos de apenas
determinada marca e não apenas o produto
ou a marca.
Sua sintaxe é a seguinte:
=SOMASES(intervalo_soma; intervalo_critério1;
critério1; intervalo_criterio2; critério2; ......)
Para entender melhor vamos utilizar o
seguinte exemplo:
24) Digite a seguinte tabela:

25) Na célula B17 digite a seguinte função:
=SOMASES(D4:D12;A4:A12;B14;B4:B12;B15;
C4:C12;B16)

26) Com está função iremos somar os valores
considerando os 3 critérios

27) Se você quiser também pode utilizar o
asterisco em determinado critério para
não pesquisar exatamente ele. Como por
exemplo, apenas somar o Corsa Preto,
você deve colocar um asterisco no lugar
do combustível, desta maneira ele
ignorará este critério.

28) Como no exemplo a seguir:

29) E deverá fazer assim para qualquer outro
critério que você queira ignorar.

30) Outra opção é colocar o asterisco dentro
da função como visto na função
SOMASE().
=SOMASES (D4:D12; A4:A12; "*"&B14;
B4:B12; "*"&B15; C4:C12; "*"&B16)

31) Com a função desenvolvida acima, você
escrevendo apenas um dos critérios, o
valor já será somado, não dependendo
dos outros critérios.

✓ Revisão
01) Ache o valor total utilizando o vendedor e
o produto.

02) Desenvolva a planilha abaixo inserindo as funções utilizadas nesta lição

✓ Anotações:

Insira as funções aqui
20

LIÇÃO 04 - FUNÇÃO CONT.SE() E VALIDAÇÃO DE DADOS

✓ Função CONT.SE()

A função CONT.SE conta o número de
células dentro de um intervalo que atendem a
um único critério que você especifica. Por
exemplo, é possível contar todas as células
que começam com uma certa letra ou todas
as células que contêm um número maior do
que ou menor do que um número que você
especificar.
=CONT.SE(Intervalo; Critério)
Onde:
✓ Intervalo - É o intervalo de células no qual
se deseja contar células que não estão em
branco.
✓ Critério - É a condição, na forma de número,
expressão ou texto, que define quais células
serão contadas.
Obs: O campo de critério desta função é
similar ao campo de critério da função somase
e está sujeita as mesmas condições, podendo
utilizar o asterisco como símbolo coringa para
fazer suas pesquisas.

✓ Exemplo 01
Neste exemplo usaremos um estoque de loja
para demostrar um dos usas da função
cont.se, nomenclatura de células e validação
de dados

1) Digite a planilha abaixo:

✓ Nomenclatura de Células
A nomenclatura seria você dar um nome
a uma célula ou em um intervalo e desta
forma ao invés de você precisar selecionar o
intervalo dentro de uma função você apenas
precisará colocar o nome do intervalo.

2) Selecione da célula A4 até A11.

3) Clique na guia de Caixa de nome

4) Digite o Produto e pressione a tecla
Enter.

5) Clique em qualquer lugar da sua tabela
para tirar a seleção dos produtos.

6) Clique na “setinha” na caixa de nome e
repare que temos o nome Produto salvo.

7) Se você clicar em Produto seus produtos
serão automaticamente selecionados.

8) Caso você tenha selecionado algo errado
ou colocado o nome errado você deve
clicar na guia Fórmulas.

9) Em seguida clique em Gerenciador de
Nomes.

10) E na janela que apareceu você consegue
editar, excluir ou até mesmo criar novos
nomes de células.

11) Agora nomeie as células B4 até B11 com
o nome Marca.

12) Nomeie a seleção de C4 até C11 com o
nome Quantidade.
13) Crie uma nova planilha.

14) Na Planilha2 digite e formate a seguinte
tabela:

✓ Criando a Validação de Dados (Lista
Suspensa)
Você pode usar a validação de dados
para restringir o tipo de dados ou os valores
que os usuários inserem em uma célula. Um
dos usos mais comuns da validação de
dados é criar uma lista suspensa.
15) Ainda a planilha2 clique na célula B3

16) Clique na Guia DADOS

17) Em seguida clique na ferramenta
Validação de Dados.

18) Na janela que apareceu altere a opção de
Permissões para Lista.

19) Agora no campo de Fonte insira o texto:
=Produto

Obs: ao inserir este nome na fonte se equipara
a selecionar os nomes dos produtos na tabela
da Planilha1 que nomeamos anteriormente.

20) Clique em Ok.

21) Agora repare que ao clicar na célula em
que inserimos a validação e dados
aparecerá um menu suspenso com o
nome dos produtos.

Obs: É importante ressaltar que, você poderáescrever apenas nomes existentes nesta lista
caso contrário aparecerá uma janela de erro.
22) Escolha algum dos produtos.

23) Na Célula B4 digite a seguinte fórmula:
=CONT.SE(PRODUTO ; B3)

24) Como dito anteriormente a função cont.se
mostra quantas vezes tem a mesma
palavra em uma determinada seleção que
neste caso foi a seleção nomeada
anteriormente .

25) Agora na célula B5 digite uma função
SOMASE() que diga a quantidade do
produto.

Resposta:
=SOMASE (PRODUTO ; B3 ; QUANTIDADE)

26) Agora faça a mesma coisa na tabela
abaixo de marcas

✓ Função CONT.SES()
A função CONT.SES() Aplica critérios a
células em vários intervalos e conta o número
de vezes que todos os critérios são atendidos.
27) Abaixo da tabela de marca preenchida
anteriormente digite a seguinte tabela:

28) Na célula B12 digite a seguinte função:
=CONT.SES(PRODUTO; B3; MARCA; B7)

Obs: a função cont.ses() sempre seguirá a
ordem de “intervalo de Critério” e em seguida
“Critério”, ou seja, qual o intervalo que está, o
que você vai contar e o que você vai contar
neste intervalo.
29) Na célula B13 relembre a função
SOMASES() e faça a soma do produto e
marca selecionados.

Resposta:
=SOMASES(Quantidade;Produto;B3;Marca;B7)
30) Agora confira seus resultados e faça
alterações caso ache necessário.

31) Salve sua tabela.

✓ Outras Validações de dados:

1) Em uma nova planilha digite a seguinte
tabela:

2) Clique na célula B3 e clique na ferramenta
Validação de dados na guia de dados.

3) Selecione a opção de permitir escolha
lista, como feito no exercício anterior.

4) Clique no campo de fonte e selecione da
célula A12 até A17.

5) Sua janela de validação de dados deve
estar como mostrado a seguir:

6) Repare que na janela de validação de
dados temos mais duas guias que são a
Mensagem de entrada e Alerta de erro.

• A guia Mensagem de entrada você
pode escolher uma mensagem que
aparecera quando a célula em questão
for selecionada.
• A guia Alerta de erro permite que
você personalize a mensagem de erro
que acontece quando um dado que está
fora da validação é inserido.

7) Clique na guia Mensagem de Entrada.

8) Configure está guia como mostra a figura
a seguir:

9) Clique na guia Alerta de erro:

10) Agora configure a guia de alerta de erro
como mostra a figura a seguir:

11) Repare que ao lado de título se encontra
um campo chamado estilo:

Existem 3 tipos de estilos para os erros:
Parar: este estilo de erro não permitirá
que o usuário escreva algo que esteja fora dos
critérios da validação.
Aviso: este estilo de erro oferece a
opção para o usuário continuar com o erro ou
não, caso o usuário escolha prosseguir o valor
ficará fora dos critérios de validação.
Informações: este estilo de erro
apenas mostra ao usuário que há uma
inconformidade do valor inserido com os
valores da validação e permite que o usuário
continue.

12) Agora clique em Ok.

13) Agora clique na célula B3 e repare que
apareceu tanto o menu suspenso quando
a mensagem de entrada inserida
anteriormente.

14) Digite o nome Francisco e pressione
Enter.

15) Repare que apareceu a mensagem de
erro personalizada que criamos na guia de
Alerta de erro.

16) Cliquei agora na célula B4

17) Clique na ferramenta Validação de
dados.

18) Em permitir altere o campo para Número
Inteiro.

19) Nesta seleção, assim como a maioria das
outras, habilitou para a edição o campo de
Dados, logo abaixo do permitir.

20) No campo de Dados colocaremos qual
será o intervalo numérico da validação que
será feita, neste caso usaremos o está
entre que normalmente aparece como
padrão.

21) No valor de Mínimo insira o valor de 18 e
de Máximo insira o valor de 60

22) Configure agora uma mensagem de
entrada e um alerta de erro personalizado
como feito na célula anterior.

23) Após a configuração veja o resultado e
altere caso seja necessário.

24) Nas outras células também insira a
validação de dados utilizando as
ferramentas vistas nos passos anteriores.

25) Salve seu arquivo.
26

✓ Revisão
Desenvolva a Planilhas a seguir utilizando os recursos vistos nesta lição.

Antes de escrever os dados crie uma validação de dados para a coluna de quantidade (Nº
inteiro) e valor (Decimal).
Após digitar os dados dê um nome para a coluna de quantidade e para a coluna de Valor.

Em uma nova planilha do mesmo arquivo digite a tabela abaixo:

Na célula de Nome e Produto crie uma validação de
dados com os nomes já existentes.
Na célula de ocorrências utilize a função CONT.SE()
para descobrir quantas vezes apareceu o nome ou o produto
Na célula de Quantidade e Valor utilize uma função
para somar os dados correspondentes do nome ou produto
selecionado

✓ Anotações:

LIÇÃO 05 - FUNÇÃO PROCV()

✓ Função PROCV()
A função PROCV é uma das funções de
pesquisa e referência. Você a utilizara
quando precisar localizar algo dentro de sua
tabela, como por exemplo, pesquisar o preço
de uma peça pelo seu código de
identificação, ou os dados de um cliente pelo
seu nome.
Em sua forma mais simples, a função
PROCV() diz:
=PROCV( Valor_procurado; Matriz_tabela;
núm_índice_coluna; [procura_intervalo])
Valor_procurado: Valor que você deseja
pesquisar
Matriz_tabela: Intervalo no qual você deseja
pesquisar o valor, este intervalo tem que
começar na coluna onde está o valor
procurado e terminar no mínimo onde o valor
desejado está.
Núm_índice_coluna: O número da coluna no
intervalo contendo o valor de retorno.
Procura_intervalo: Correspondência Exata ou
Correspondência Aproximada – indicado
como 0/FALSO ou 1/VERDADEIRO).
✓ Exemplo 01:
1) Digite a tabela a seguir:

2) Selecione de A3 até C13 e dê o nome
de Produtos.

3) Insira uma nova planilha

4) Na Planilha2 digite a seguinte tabela.

5) Na célula C4 digite a seguinte função:
=PROCV( B4; Produtos; 2; 0)

Obs: a matriz da tabela sempre será toda a
tabela de pesquisa a partir da coluna onde
se encontra o valor procurado. O número da
coluna será a partir do início da matriz em
que coluna está o valor que queremos
procurar. Por fim o número zero referência a
correspondência exata, ou seja, exatamente
o valor 1 nada próximo disto.
6) Faça a mesma função para as linhas
anteriores até que você tenha domínio
da função.
28

7) Na célula E4 digite a seguinte função:
=PROCV( B4; Produtos; 3; 0)

8) Refaça a função nas células abaixo.

9) Na célula F4 faça a multiplicação da
quantidade pelo valor.
=D4*E4
10) E leve a fórmula para as células abaixo.

11) Selecione da célula A3 até H18 e
nomeie como BancoDeDados.

12) Esse é um bom momento para salvar
sua tabela.

13) Abra uma nova Planilha.
14) Renomeie todas as planilhas como
mostra a figura abaixo:
Planilha1  Produtos
Planilha2  Dados
Planilha3  Relatório

15) Na planilha de Relatório digite a tabela
a seguir:

16) Selecione da célula A6 até F7 e
utilizando a ferramenta de alça leve até a
linha 20.

17) Selecione a célula C3.

18) Selecione a ferramenta de validação de
dados na guia de Dados

19) Na janela de validação de dados formate
como mostra a seguir:

20) Confirmando a validação repare que irá
aparecer todas as empresas que
utilizamos na planilha de dados.

21) Na mesma célula C3 dê o nome de
Empresa.

22) Volte para a planilha de Dados.

23) Na célula H4 digite a seguinte função:
=SE( G4 = Empresa; “X”; “”)

Obs: esta função indicará para a tabela de
relatório quais são as linhas que tem a
mesma empresaselecionada
24) Leve está função para as outras linhas
Obs: se quiser tirar a prova se está
funcionando volte para a planilha de relatório
escolha uma empresa e veja se as linhas na
planilha de dados ficam com o “X”.
25) Clique na célula A4 e digite a seguinte
função:
=SE( H4=“X”; CONT.SE(H$3:H4; “X” ); “”)

Obs: quando você deseja que um valor seja
fixo mesmo que se use a alça, é utilizado o
símbolo $ este símbolo é colocado antes da
linha ou antes da coluna ou antes de ambos
fazendo com que este valor fique fixo.
26) Utilizando a ferramenta alça leve a
função para as outras células.

27) Volte para a planilha Relatório.

28) Selecione a empresa A.

29) Clique na célula B6 e digite a seguinte
função:
=PROCV(A6; BancoDeDados; 2; 0)

30) Utilizando a ferramenta de alça leve a
função para baixo.

31) Repare que a empresa A tem apenas 4
produtos então as outras células
apareceram com erro.

32) Para corrigir esse erro iremos utilizar a
função SEERRO()

✓ Função SEERRO()
Retorna um valor especificado se uma
fórmula gerar um erro; caso contrário, retorna
o resultado da fórmula. Use a função
SEERRO para capturar e controlar os erros
em uma fórmula. Sua sintaxe é:
=SEERRO(valor, valor_se_erro), onde:
• Valor: O argumento verificado quanto ao
erro.
• Valor_se_erro: O valor a ser retornado se
a fórmula gerar um erro.

33) Clique na célula B6 e altere a função
para:
=SEERRO( PROCV(A6;BancoDeDados;2;0); "")

34) Da mesma maneira leve a função para
baixo utilizando a ferramenta de alça e
veja que agora onde havia erro ficou
vazio ( “” ), que foi o valor_se_erro
inserido na função.

35) Agora faça a mesma função PROCV e
SEERRO nas colunas ao lado, porém
alterando o número da coluna
especificado na função Procv.

36) Altere a empresa procurada e veja o
funcionamento da tabela.

37) Salve seu arquivo.

✓ Exemplo 2
Neste exemplo utilizaremos o valor
VERDADEIRO(1) no final da função para
pesquisar valores aproximados.
1) Digite a tabela abaixo:

2) Selecione de A13 até B18 e nomeie
como IMC.

3) Agora na célula E4 digite a seguinte
fórmula:
=D4/C4^2

Obs: está é a fórmula do IMC sendo Peso
sobre altura ao quadrado.
4) Leve a função para as células abaixo.

5) Clique na célula F4 e digite a seguinte
função:
=PROCV( E4; IMC; 2; 1)

Obs: Repare que foi utilizado o valor 1 no final
da função, este 1 significa correspondência
aproximada, ou seja, ele irá selecionar o valor
abaixo mais próximo, como em nossa tabela
o valor 23,46 não se encontra na tabela,
porém o valor abaixo mais próximo é o 19,
logo aparecerá o resultado “Normal”.
6) Reescreva a função nas outras células
da coluna até que entenda o
funcionamento da função.

7) Salve sua planilha.
✓ Anotações

✓ Revisão
1) Folha de Pagamento:

✓ Valor IR – Procure a porcentagem correspondente ao salário e multiplique pelo Salário Bruto
✓ Valor INSS – Procure a porcentagem correspondente ao salário e multiplique pelo Salário Bruto
✓ Salário Família – Procure o acréscimo correspondente ao salário e multiplique pelo nº de filhos
✓ Plano de Saúde – Procure a porcentagem correspondente ao salário e multiplique pelo Salário
Bruto
✓ Desconto V.T – Se o funcionário deseja ter o V.T ele terá um desconto de 3% do salário Bruto.
✓ Salário líquido – Salário Bruto somado com o Salário Família menos os descontos.

2) Lista de Produtos:

✓ Faça a função Procv na tabela de pedidos de compra para descobrir a partir do código qual a
descrição, marca, vlr. Unitário.
✓ Multiplique o valor unitário pela quantidade para descobrir o valor total.
33

LIÇÃO 06 - FUNÇÕES FINANCEIRAS

✓ Funções financeiras
As funções financeiras são parte importante
dos cálculos feitos no mercado de trabalho,
possibilitando de forma simples a visualização
de taxas, prazos, condições de pagamento
entre outros.
Entre estas funções encontramos:

✓ Função VF() - Valor Futuro
A fórmula do valor futuro é utilizada
para retornar o valor de resgate de uma dada
aplicação. Sua sentença é apresentada da
seguinte forma:
= VF (taxa; nper; pgto; vp; tipo) onde:
Taxa: Taxa de juros por período
Nper: número de períodos de pagamentos.
Pgto: É o pagamento feito em cada período.
Vp: É o Valor Presente, ou seja , o valor inicial
aplicado.
Tipo: Sempre sendo o número 1 ou 0 este
argumento indica as datas de vencimento dos
pagamentos sendo 0 o final do período e 1 o
início do período (opcional).
✓ Exemplo 01
1) Em uma pasta de trabalho em branco
digite a seguinte tabela:

2) Leia o enunciado a seguir:
Você colocou um valor de R$5.000,00 em
uma poupança que tem uma taxa de juros de
3% ao mês, nos 24 meses seguintes você
depositou R$50,00 por mês, qual o valor que
será resgatado da poupança após 24 meses?

3) Seguindo o enunciado nossa tabela será
preenchida da seguinte forma:

4) Agora na célula B7 digite a seguinte
função:
=VF(B6; B4; B5; B3)

5) Repare que quando você confirma a
função o resultado é negativo, isso se
deve ao fato de que, toda entrada de
dinheiro terá sinal positivo e toda saída
de dinheiro sinal negativo.

6) A função VF retorna o valor que você
deseja resgatar ou seja tirar de uma
aplicação com isso iremos colocar um
negativo no começo da função VF com
isso o resultado sairá positivo. Em uma
maneira geral sempre que tivermos a
variável VF colocaremos um sinal
negativo antes.
= - VF(B6; B4; B5; B3)
7) Agora seu valor deve estar em:
34

Obs: quando o valor do PGTO não
existir basta colocar o valor 0 (zero), e
lembre-se de que todas as unidades devem
ser as mesmas, ou seja, se a taxa de juros
for por mês a quantidade de períodos
também deve estar em meses.
8) Então após 24 meses nestas condições
você terá o valor de R$11.855,25.

9) Salve seu arquivo.
✓ Revisão:
Faça uma tabela similar a tabela criada
a pouco para cada enunciado de revisão, e
anote os resultados nesta apostila:
1) Qual o montante produzido com base na
da aplicação de um capital de
R$159.000,00, por um prazo de três
meses, à taxa de juros compostos de 5%
ao mês?
Resposta: __________________________
2) Uma aplicação financeira no valor de R$
40.000,00, à taxa de juros compostos de
1,80% ao mês, pelo prazo de 69 dias,
proporciona que valor de resgate?
Resposta: __________________________
3) Um empresário obteve um empréstimo
de R$ 60.000,00 em seu banco, à taxa de
juros compostos de 3% ao mês, por um
período de cinco meses. Calcular o valor
do montante final a ser pago.
Resposta: __________________________
4) Qual será o montante referente a uma
aplicação mensal de R$500,00, à taxa de
juros compostos de 2% ao mês, pelo
prazo de 36 meses?
Resposta: __________________________
✓ Função VP() - Valor Presente
Calcula o valor presente de um
empréstimo ou investimento com base em
uma taxa de juros constante. Você pode usar
VP com pagamentos periódicos e constantes
(como uma hipoteca ou outro empréstimo) ou
um valor futuro que é sua meta de
investimento. Sua sentença é apresentada da
seguinte forma:
=VP(taxa; nper; pgto; vf; tipo) onde:
As variáveis têm o mesmo significado do que
a função VF() só é alterado o:
VF: É o Valor futuro, ou seja, o valor da meta
que se deseja retirar.
✓ Exemplo 02
1) Em uma pasta de trabalho em branco
digite a seguinte tabela:

2) Leia o seguinte Enunciado:
Você deseja retirar de sua poupança
R$15.000,00 daqui 12 meses, depositando
todo o mês R$100,00 e aplicando um valor
inicial, a taxa de juros de sua poupança é de
1,80% ao mês, quanto você terá que aplicar
hoje para que isto aconteça?
3) Seguindoo enunciado a cima sua tabela
deverá ser preenchida da seguinte
maneira:

4) Na célula B7 digite a seguinte função:
=VP(B6; B4; B5; -B3)

Obs: repare que o valor do VF foi inserido
negativo para que o cálculo fosse feito
corretamente como explicado na função VF.
5) O resultado da sua função tem que ser R$
11.038,63 ou seja você teria que ter este
valor para que nas condições mostradas
ter o valor de R$15.000,00.

6) Salve seu arquivo.

✓ Revisão:
Faça uma tabela similar a tabela criada
a pouco para cada enunciado de revisão, e
anote os resultados nesta apostila:
5) Quanto devo aplicar hoje, para poder
resgatar daqui a seis meses a quantia de
R$ 4.000,00, à taxa de juros compostos de
3% ao mês?

Resposta: __________________________
6) Qual o valor à vista de um equipamento
cujo valor para pagamento a prazo é de
R$ 70.000,00 (pagamento em 45 dias),
sabendo-se que a taxa de juros cobrada
é de 5% ao mês?

Resposta: __________________________
7) Certo capital aplicado à taxa de juros
compostos de 3% ao mês, após 4 anos,
produziu um montante de R$ 60.000,00.
Determine o valor do capital inicial
aplicado.

Resposta: __________________________

✓ Função TAXA()
A função retorna a taxa de juros por
período de uma anuidade. TAXA é calculado
por iteração e pode ter zero ou mais soluções.
Sua sentença é apresentada da seguinte
forma:
=Taxa(nper; pgto; VP; [vf]; [tipo]; [estimativa])
Onde:
nper, pgto, VP, VF e tipo tem a mesma
definição que as funções mostradas
anteriormente.
Estimativa: A sua estimativa para a taxa. Se
você omitir estimativa, este argumento será
considerado 10%.
✓ Exemplo 03
1) Em uma pasta de trabalho em branco
digite a seguinte tabela:

2) Leia o seguinte Enunciado:
Você aplicou um valor de R$1.000,00
durante 12 meses, todo o mês era
depositado mais R$100,00, no final deste
período você resgatou R$3.000,00. Qual
foi a taxa de juros composto do banco?
3) Seguindo o enunciado iremos preencher
a tabela da seguinte maneira:

4) Na célula B7 digite a seguinte função:
=TAXA(B5; B6; B3; -B4)

Obs: lembre-se que o VF é negativo.
5) O seu resultado deve ser de 3,61% ao
mês.

6) Salve seu arquivo.

✓ Revisão
Faça uma tabela similar a tabela criada a
pouco para cada enunciado de revisão, e
anote os resultados nesta apostila:
8) A que taxa mensal de juros compostos se
aplica um capital de R$ 300.000,00, para
que após 89 dias seja possível resgatar a
quantia de R$ 325.324,00 ?
Resposta: __________________________
9) Qual a taxa mensal de juros compostos
cobrada em um empréstimo no valor de R$
50.000,00, pelo prazo de três meses, que
proporcionou a quantia de R$ 2.300,00, a
título de juros?
Resposta: __________________________
10) Qual a taxa mensal de juros compostos ao
qual durante 24 meses foi pago R$250,00
ao mês e ao final do período foi possível
resgatar R$8.000,00?
Resposta: __________________________
11) A que taxa anual de juros compostos devo
aplicar a quantia de R$ 100.000,00, de
modo a obter daqui a 89 dias o montante
de R$124.000,00?
Resposta: __________________________

✓ Função NPER() – Nº de Períodos
Retorna o número de períodos para
investimento de acordo com pagamentos
constantes e periódicos e uma taxa de juros
constante. Sua sentença é apresentada da
seguinte forma:
=NPER( taxa; pgto; vp; [vf]; [tipo]) onde:
Todos os argumentos têm a mesma
definição das funções anteriores.
✓ Exemplo 04
1) Em uma pasta de trabalho em branco
digite a seguinte tabela:

2) Leia o seguinte enunciado:
Você deseja resgatar R$10.000,00 de
uma poupança, depositando todo mês
R$450,00 e o seu banco tem uma taxa de
juros de 1,09% ao mês. Quanto tempo
você terá que depositar para alcançar o
valor desejado?
3) Seguindo o enunciado a tabela será
preenchida da seguinte maneira:

4) Na célula B6 digite a seguinte função:
=NPER( B4; B5; 0; -B3)

Obs: note que usamos o valor 0 para o campo
de VP já que nosso enunciado não disse
sobre um valor inicial aplicado, porém como
nas outras funções já vistas este campo tem a
mesma função.
5) O resultado deverá ser de
aproximadamente 20 meses, ou seja, em
um total de 20 meses para conseguir
juntar o valor de 10 mil em sua poupança.

6) Salve seu arquivo.

✓ Revisão:
Faça uma tabela similar a tabela criada a
pouco para cada enunciado de revisão, e
anote os resultados nesta apostila:
12) Em quantos meses um capital de R$
100.000,00 aplicado à taxa de juros
compostos de 5% ao mês, duplica de
valor?
Resposta: __________________________

13) Em quanto tempo um capital de R$
2.000,00, aplicado à taxa de juros
compostos de 3% ao mês, produz a
quantia de R$ 533,54, a título de juros?
Resposta: __________________________

14) Em quantos anos, aplicando R$100,00
por mês, com uma taxa de 5% ao mês
você atingira o valor de R$1.000.000,00?
Resposta: __________________________

15) Você tem um salário de R$1.200,00
reais e tem um aumento mensal de 3%
em quantos meses você recebera um
salário de R$5.000,00?
Resposta: __________________________

✓ Função PGTO() - Pagamento
Calcula o pagamento de um empréstimo
de acordo com pagamentos constantes e
com uma taxa de juros constante. Sua
sentença é apresentada da seguinte forma:
=PGTO(taxa; nper; vp; [vf]; [tipo]) onde
Todos os argumentos têm a mesma
definição das funções anteriores.
✓ Exemplo 05
1) Em uma pasta de trabalho em branco
digite a seguinte tabela:

2) Leia o enunciado a seguir:
Você fez um empréstimo de R$5.000,00
no banco que tem uma taxa de 3,00% ao mês,
você parcelou esse valor em 24 meses. Qual
o valor da mensalidade que o banco irá cobrar
de você e quanto é valor total que você irá
pagar?
3) Preenchendo a tabela seguindo o
enunciado fica da seguinte maneira:

4) Na célula B6 digite a seguinte função:
=PGTO( B5; B4; -B3)

Obs: Repare que foi colocado o campo de VP
como negativo, especialmente para a Função
PGTO será invertido os papéis de VP e VF ou
seja o VF será um valor que você irá receber
e o VP e valor que será pago.
5) O resultado deverá estar em R$295,24.

6) Na célula B7 digite uma fórmula de
multiplicação entre o Pagamento e a
quantidade de meses.
=B4*B6

7) Vendo o resultado podemos notar que
será pago R$2.085,69 de juros nesta
transação.

8) Salve seu arquivo.

✓ Revisão
Nesta revisão iremos trabalhar com todas
as funções trabalhadas nesta lição, então leia
os enunciados e anote os resultados nesta
apostila:
1) Qual o valor de uma fatura de cartão de
crédito de R$500,00 que foi atrasada por
6 meses, este cartão tem um juros de
26,45% ao mês.
Resposta: __________________________
2) Quanto você terá que depositar por mês
para que em 1 ano você tenha
R$5.000,00 em uma poupança com juros
de 3,25% ao mês.
Resposta: __________________________
3) Um carro de valor inicial de R$ 24.500 foi
financiado em 48 vezes, o valor final do
carro foi de R$38.900. Qual a taxa de
juros utilizada pela financiadora?
Resposta: __________________________
4) Você deseja ter em sua poupança
R$100.000,00 você deposita todo mês
R$350,00. Em quanto tempo você irá
conseguir o valor desejado?
Resposta: __________________________
5) Você deseja resgatar de uma aplicação
R$60.000,00 daqui 10 anos, a aplicação
tem uma taxa de juros de 4,56% ao mês.
Qual é o valor que você terá que aplicar
hoje para ter o valor desejado?
Resposta: __________________________

LIÇÃO 07 - MACRO

✓ Oque são Macros
Os Macro de uma maneira simples são
uma gravação de rotinas que acontecem no
Excel. Por exemplo, imagine que você tem
datas em formatos aleatórios e vocêdeseja
aplicar um único formato a todas elas, você
pode gravar um macro com o formato
desejado e aplicar esse macro em uma tecla
de atalho ou botão virtual desta forma sempre
que você executar ele fará o mesmo formato
gravado.
Quando você grava um macro, o gravador
de macro registra todas as etapas em código
de VBA (Visual Basic for Applications). Estas
etapas podem incluir, digitar texto ou
números, clicar em células ou comandos da
faixa de opções ou nos menus, a formatação
de células, linhas ou colunas, ou até mesmo
importando dados de uma fonte externa.
Visual Basic Application (VBA) é um
subconjunto da linguagem de programação
Visual Basic poderoso e está incluído na
maioria dos aplicativos do Office. Embora o
VBA oferece a capacidade de automatizar
processos dentro e entre os aplicativos do
Office, não é explicitamente necessário saber
o código VBA para se trabalhar com macros,
já que o gravador de Macro faz boa parte
daquilo que se deseja na parte programação.
É importante saber que você quando você
grava um macro, o gravador captura quase
todos os movimentos. Portanto se você
cometer um erro em sua sequência, por
exemplo, clicando em um botão que você não
pretenda clicar, o gravador de Macro gravará.
É por isso sempre que você gravar algo, é
melhor gravar um processo com o qual você
está familiarizado. Quanto mais
tranquilamente você gravar uma sequência,
mais eficiente a macro será executada
quando você o reproduzir.

✓ Habilitar Guia Desenvolvedor
Para começar a criação de macros é
recomendado habilitar a guia Desenvolvedor
está guia precisa ser habilitada apenas uma
vez em seu computador, então, caso já esteja
habilitado passe para o próximo tópico.
Caso não esteja habilitada siga os passos
abaixo:
1) Abra uma nova planilha do Excel

2) Clique com o botão direito em qualquer
uma das guias já habilitadas.

3) Selecione a opção Personalizar a Faixa
de Opção

4) Na janela que apareceu marque a opção
Desenvolvedor.

5) Clique em Ok.
40

6) Repare que agora a última guia será a
desenvolvedor.

✓ Referências Relativas
As referências relativas são utilizadas
quando há uma ação que tem que ser feita
aleatoriamente dentro de uma tabela.
✓ Exemplo 01
Neste exemplo iremos criar um macro que
altere a cor e formato das células
selecionadas.
1) Digite a seguinte tabela:

2) Clique na guia Desenvolvedor

3) Na guia desenvolvedor habilite a
ferramenta Usar Referências Relativas.

4) Agora clique na célula A4.

5) Clique na ferramenta Gravar Macro

6) Na janela que apareceu preencha da
seguinte maneira:

Obs: Para o nome de um macro você nunca
pode colocar espaço ou caracteres especiais
como “Ç” ou acentos, a única forma de
separação é alternando entre letras
maiúsculas e minúsculas ou utilizando o
símbolo de underline ( _ ).
Na tecla de atalho você pode utilizar
qualquer letra ou número. Já a descrição não
é obrigatória sua utilização, porém é
recomendado colocar quando outras pessoas
irão trabalhar com o macro ou também
quando em seu arquivo há uma grande
quantidade de macros.

7) Clique em Ok.

Lembre-se de que a partir deste momento
todos os seus movimentos estão sendo
gravados.

8) Selecione de A4 até B4.

9) Na guia de página inicial altere a cor de
preenchimento para amarelo e a cor da
fonte vermelha.

10) Clique novamente em A4

11) Na guia Desenvolvedor clique em Parar
gravação.

12) Agora para testar o macro que foi criado
clique em algum outro nome e reproduza
o atalho clicando em Ctrl + r.

13) Caso o resultado não foi como o
esperado, a linha em que você estava
não foi destacada da mesma maneira que
havia sido gravada. Clique me Macros.

14) Na janela que apareceu selecione o
macro que foi criado e clique em excluir.

15) Em seguida selecione Sim.

16) Caso o resultado esteja de acordo com o
esperado iremos salvar a planilha.

17) Para salvar uma planilha com macro
devemos mudar o tipo de arquivo para
Pasta de Trabalho Habilitada para
Macro do Excel

18) Feche seu arquivo.
42

✓ Referências Absolutas
As referências absolutas são utilizadas
como padrão pelo gravador de macros do
Excel. Este termo de referências absolutas se
deve ao fato de que o macro execute suas
ações sempre no mesmo local ou seja se você
gravar um clique na célula A1 toda vez que
executar a macro ele também clicara na célula
A1.
✓ Exemplo 02
1) Digite e formate a tabela a seguir:

2) Clique na guia Inserir.

3) Clique em Ilustrações e escolha a
ferramenta de Formas.

4) Escolha a forma de retângulo e crie ao
lado dos seus campos da tabela.

5) Com a sua forma geométrica
selecionada digite Salvar.

6) Na página inicial clique em Centralizar
e Alinhar ao Meio.

7) Repare que quando você está com a
forma selecionada aparece uma nova
guia chamada Formatar.

8) No painel de Estilo de Forma escolha um
formato que lhe agrade.

9) Crie mais uma forma geométrica e
escreva o nome de Limpar.

10) Em seguida altere o nome de sua tabela
para Cadastro.

11) Crie uma nova tabela e de o nome dê o
nome de Banco de Dados.

12) Na tabela de Banco de dados digite a
seguinte tabela.

13) Este é um bom momento para salvar
seu arquivo.
Obs: Lembre-se que iremos fazer macros
então teremos que salvar o arquivo como
Pasta de Trabalho Habilitada para Macro do
Excel.
14) Volte para a tabela de Cadastro.

15) Preencha o cadastro com os seguintes
dados

16) Iremos começar criando o macro para
fazer a limpeza do cadastro.

17) Certifique-se de que a opção de Usar
Referências Relativas esteja
desabilitada.

18) Em seguida clique em Gravar Macro.

19) Dê o nome “Limpar” para o macro

20) Clique em Ok.

21) Selecione de C4 até C10.

22) Pressione a tecla Delete (Del) em seu
teclado.

23) Clique na célula C4.

24) Clique em Parar gravação.

25) Clique com o botão direito do mouse
na forma geométrica Limpar

26) Selecione a opção Atribuir Macro...

27) Na janela que apareceu clique no macro
Limpar que foi criado e clique em Ok

28) Preencha novamente o cadastro e em
seguida clique na forma escrito Limpar.
Repare que ela virou um botão, sendo
assim, toda vez que ele é clicado, o macro
que acabamos de criar é executado.

29) Agora criaremos o macro que irá salvar
os dados na tabela de banco de dados.

30) Preencha novamente o cadastro com os
seguintes dados:

31) Selecione a célula C10.

32) Clique em Gravar Macro.

33) Dê o nome de Salvar e clique em Ok.

34) Clique na célula C4 e Pressione Ctrl + C
(copiar).

35) Vá para a planilha de Banco de Dados.

36) Clique com o botão direito na célula A2
e selecione a opção Inserir células
copiadas....

37) Deixe marcado a opção Deslocar para
baixo e clique em Ok.

38) Volte para a planilha de Cadastro.

39) Selecione a célula C6 e pressione as
teclas Ctrl + C (copiar).

40) Vá para a planilha de Banco de dados

41) Clique com o botão direito na célula B2
e selecione a opção Inserir células
copiadas....

42) Clique em Ok.

43) A sua tabela de banco de dados deve
estar parecida a tabela abaixo:

44) Faça a mesma ação de copiar e inserir
células copiadas no endereço e
telefone até que sua tabela fique da
seguinte forma:

45) Volte para a planilha de Cadastro e
pressione a tecla Esc (para tirar o
pontilhado em volta da célula)

46) Clique na célula C4.

47) Clique em Parar Gravação.

48) Clique com o botão direito na forma
escrito Salvar e clique em Atribuir
Macro...49) Clique no macro Salvar e clique em Ok.

50) Teste seu macro fazendo mais alguns
cadastros.

51) Por fim salve sua tabela.

✓ Revisão
Desenvolva as tabelas e crie o macro
descrito em seu enunciado.
1) Imagine a seguinte situação, você tem
uma tabela que deve analisar todas as
linhas e apenas algumas delas você
deve destacar visualmente e calcular o
atraso utilizando a função hoje. A tabela
é a seguinte:

Digamos que você irá que destacar a linha
que o Davi esteja, então você gravará um
macro e colocará em um atalho do teclado
que, selecione toda a linha dele, clique em
D3 insira a função hoje() menos a data do
vencimento.
Exemplo:

2) Crie uma tabela que tenha uma planilha
de cadastro que salve os dados em uma
planilha de banco de dados. A tabela
deve ter os seguintes campos para
salvar:

3) Crie um Macro que organize em ordem
alfabética ou do menor para o maior a
coluna da célula em que se está
selecionado.

✓ Anotações:

LIÇÃO 08 - INTRODUÇÃO VBA

✓ Visual Basic Application (VBA)
Como dito anteriormente o Visual Basic
Application (VBA) é um subconjunto da
linguagem de programação Visual Basic
poderoso e está incluído na maioria dos
aplicativos do Office ele oferece a capacidade
de automatizar processos dentro e entre os
aplicativos do Office. O VBA é sem dúvida
nenhuma uma ferramenta poderosíssima
dentro do Excel, com um conhecimento pleno
em VBA podemos fazer coisas realmente
incríveis no Excel, desde automatizar
pequenas tarefas como preenchimento de
formulários até desenvolver um sistema
robusto para cadastro de clientes por
exemplo.
Iremos trabalhar com alguns conceitos
básicos e ver como funciona para se escrever
essa linguagem de programação, além de
conhecer alguns códigos que são muito uteis
no dia a dia.
✓ Iniciando a criação do código
Para escrevermos os códigos de VBA
teremos que utilizar a ferramenta de Visual
Basic que se encontra na guia desenvolvedor
e habilitar o que é chamado de Módulo os
módulos serão os espaços para armazenar
códigos de procedimentos e códigos de
funções.
1) Clique na guia Desenvolvedor.

2) Clique na ferramenta Visual Basic.

Obs: Alt + F11 é o atalho para essa
ferramenta
3) A janela que apareceu é a que a partir
de agora trabalharemos para
desenvolver os códigos em VBA.

4) Clique na guia Inserir.

5) Clique em Módulo.

6) Repare que no painel ao lado apareceu
uma pasta chamada Módulo e um item
chamado Módulo 1 .

Obs: Quando você cria um macro o Excel cria
automaticamente esses módulos e escreve o
código VBA, caso queira conferir isso abra os
arquivos feitos no exercício anterior.
48

✓ Criando uma caixa de aviso
7) Na janela que apareceu digite o seguinte
código:
Sub oi()
MsgBox "Você criou uma caixa de texto!"
End Sub

Todas as funções que iremos criar aqui
irão começar com a palavra Sub (sub-rotina)
e terminará com End Sub estes códigos irão
se comportar como um macro, ou seja,
sempre que for executado ele irá ler o que
está escrito entre o início e fim do Sub.
8) Para executar o código clique em
Executar Sub ou pressione F5

9) Note que apareceu uma Caixa de
mensagem com o texto que foi escrito

10) Clique em Ok.
✓ Criando uma caixa de confirmação
11) A sentença de MsgBox também pode ser
utilizada para se confirmar uma ação.
Como por exemplo:
Sub Confirmacao()

If MsgBox("Continuar?", vbYesNo, "Etc") = vbNo Then
MsgBox "Você Cancelou"
Exit Sub
End If
MsgBox "Você Confirmou"

End Sub
Repare que agora foi utilizado um
termo chamado If, que tem a mesma
utilização da função Se. A função If precisa da
seguinte sintaxe:
If lógica a ser analisada Then
Linhas a serem executadas se verdade
End If
Como as funções do Excel as funções
de VBA também em sua grande maioria vão
precisar de um início e um fim como na função
If usando End If.
Outra coisa que foi usada neste código
foi a sentença Exit Sub este código é usado
para dar um em suas linhas de código
ignorando tudo o que vem depois desta
sentença.
Por fim como digo a sentença MsgBox
também pode ser usada com a seguinte
Sintaxe:
MsgBox (“mensagem”, Botões, “Título”)
Na mensagem como visto é o que
você deseja que o usuário da planilha veja,
pode se escrever qualquer coisa com o tanto
que esteja dentro das aspas duplas.
Os Botões sempre começam com o
termo VB que significa Virtual button (botão
virtual).
E o Título é o título da caixa de texto
que aparecerá, pode ser escrito qualquer
coisa com o tanto que esteja entre as aspas
duplas.

12) Execute a função e veja faça algumas
alterações na mensagem de
confirmação.

13) Salve seu arquivo.
Obs: lembre-se de salvar como Pasta de
trabalho habilitada para macro do Excel

✓ Formulário Automático
Esta ferramenta é uma forma rápida e
que não necessita de muito conhecimento
para se criar um formulário de consulta ou
preenchimento.
14) Digite a tabela a seguir:

15) Abra uma nova a janela de Visual Basic
(Alt + F11).

16) Crie um novo Módulo como visto
anteriormente.

17) Digite o seguinte código:
Sub formulario()
ActiveSheet.ShowDataForm
End Sub
Obs: A sentença ActiveSheet
seleciona tabela em que você se encontra, já
o modificador ShowDataForm faz com que a
ferramenta de formulário automático do Excel.
Está ferramenta só funcionará
adequadamente quando o cabeçalho da sua
tabela começa na célula A1.
18) Feche o editor de VBA

19) Ao lado da sua tabela insira uma forma
geométrica.

20) Clique com o botão direito na forma
geométrica e clique em atribuir macro.

21) Escolha o macro “formulario” e clique
em Ok.

22) Agora clique no botão que você atribuiu
o macro.
23) Na janela que apareceu clique em Ok

24) Note que apareceu um formulário com
os campos que foram criados.

25) Explore a ferramenta de formulário
preenchendo os seguintes dados:

26) Salve seu arquivo.
Obs: lembre-se de salvar como Pasta de
trabalho habilitada para macro do Excel.
✓ Anotações

✓ Criação de Formulário Simples
Utilizando o VBA também é possível criar
formulários personalizados que fazem
cadastros ou alterações de dados. Como no
exemplo a seguir que criaremos um cadastro
de clientes.
1) Abra um novo arquivo e insira um
botão escrito “Cadastrar novo cliente”

2) Insira uma nova planilha

3) Na planilha2 digite a seguinte tabela:

4) Renomeie como Início e Dados
respectivamente para as planilhas.

5) Abra o editor de VBA (Alt + F11).

6) Clique na guia Inserir em seguida
selecione UserForm.

7) Repare que apareceram duas janelas
para você, uma Caixa de ferramentas e
uma caixa de formulário em branco.
Formulário em branco:

Caixa de ferramentas:

8) Clique na ferramenta de Rótulo dentro
da caixa de ferramentas.

9) Em seguida crie um retângulo dentro do
formulário:

10) Altere o Texto para Nome.

11) Clique na ferramenta Caixa de texto

12) Em seguida crie uma caixa de texto logo
abaixo do rótulo Nome.

13) Selecione a caixa de texto que você
acabou de criar.

14) Repare que temos um painel de
propriedades no canto inferior esquerdo
da janela de VBA

15) Nesta janela de Propriedades iremos
indicar o nome para nossa caixa de
texto.
Obs: este nome é usado um pouco mais a
frente para identificarmos os textos
colocados dentro da caixa de texto, neste
nome você não poderá colocar espaços nem
acentuações.
16) No campo Name escreva NomeCliente

17) Repita a ação criando mais dois campos
um chamado Telefone e outro chamado
E-mail. Seu formuláriodeve estar como
mostrado a seguir

Obs: não se esqueça de dar o nome aos
campos de texto na janela de propriedades.

18) Clique na ferramenta Botão de
Comando.

19) Crie um botão com o texto “Salvar” ao
lado das caixas de texto:

20) No painel de propriedades dê o nome de
salvar.

21) Agora selecione todo o seu formulário e
no painel de propriedades altere a
configuração de Name e Caption para
Formulário:

22) Repare que agora o nome da caixa de
formulário é Formulário isso se deve a
configuração Caption.

23) Se você achar necessário altere o
tamanho da caixa de formulário para se
ajustar melhor aos dados inseridos:

Obs: Até agora foi trabalhado o layout
do formulário. Agora será tratado o código
para o formulário trabalhar.

24) Clique com o botão direito no formulário
e escolha a opção Exibir Código.

25) Apague qualquer coisa que esteja escrito
na caixa de código que apareceu
deixando-a em branco.

1
2
3
4
26) Digite o seguinte Código:

Sub Salvar_Click()

If NomeCliente.Value = Empty Or Telefone.Value = Empty Then
MsgBox "Você deve Preencher Nome e Telefone!"
Exit Sub
End If

Sheets("Dados").Select

Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

Cells(2, 1).Value = NomeCliente.Value
Cells(2, 2).Value = Telefone.Value
Cells(2, 3).Value = Email.Value

Sheets("Início").Select
Unload Me

End Sub

Seria a ação que fara com que o texto seja executado que no caso será clicar no botão
Salvar.
Nesta parte do código será feito uma verificação se os campos nome e telefone estão
preenchidos, caso um deles esteja vazio irá aparecer uma mensagem de texto avisando.
Está parte irá fazer com que seja criado uma nova linha logo abaixo do cabeçalho (linha 2)
e configurar para que seja escrito os novos valores.
Na última parte do código inserimos dentro da tabela os textos escritos no formulário
utilizando o código Cells, o cells pede que indiquemos a coordenada da célula colocando
primeiro a linha e em seguida a coluna.

27) Com o código feito clique em Executar
(F5) e confira se todas as partes do
código estão funcionando.

28) Ainda na janela de VBA insira um novo
Módulo

29) Digite o seguinte código dentro do
módulo:
Sub AbrirFormulario()

Formulario.Show

End Sub

30) Este macro irá fazer com que o
formulário seja aberto .

31) Feche a janela de VBA

1
2
3
4
54

32) Insira o macro no botão que foi criado na
planilha de Início.

Lembre-se que para inserir o macro no botão
basta clicar com o botão direito do mouse na
forma geométrica, clicar em atribuir macro e
selecionar o macro criado.
33) Agora clique no botão e preencha
cadastro alguns clientes.

34) Salve seu arquivo.
Obs: lembre-se de salvar como Pasta de
trabalho habilitada para macro do Excel.
✓ Revisão:
1) Crie um novo Formulário como mostrado
anteriormente utilizando os campos
mostrados abaixo:

✓ Anotações:

LIÇÃO 09 - HIPERLINK E FUNÇÃO ÍNDICE

✓ O que é Hiperlink?
Hiperlink é uma ferramenta que cria uma
ligação “link” com uma página da web, um
arquivo do seu computador ou ainda uma
ligação com alguma parte dentro do seu
próprio arquivo.
O hiperlink pode ser usado para criar
botões de navegação entre as planilhas do
Excel ou criar um botão que vá para um site
que seja dentro do assunto da planilha.
Você pode inserir o hiperlink em imagens,
formas geométricas ou textos em células.
✓ Painel de navegação
Neste exemplo será criado um menu de
navegação entre as planilhas.
1) Abra um novo arquivo do Excel e altere a
cor da linha 1 a 4 para Azul e a cor da
linha 5 para Amarelo como mostra a
figura a seguir:

2) Dentro das células em azul um retângulo:

3) Nas opções de estilo de forma escolha
um dos estilos em amarelo:

4) Escreva o Texto Cliente.

5) Na guia Página inicial deixe o texto
Centralizado e Alinhado ao meio,
Negrito, e com a cor Azul e tamanho do
texto 16.

6) Clique com o botão direito do mouse no
retângulo.

7) Clique na opção Tamanho e
Propriedades....

8) Repare que apareceu um painel à direita.
Neste painel clique em Propriedades.

9) Selecione a opção Mover, mas não
dimensionar com células.

Obs: essa opção permitirá que quando você
alterar a largura das colunas não altere o
painel de navegação.
10) Feche o painel de formatação de forma.

11) Com o retângulo selecionado Pressione
Ctrl + C (Copiar) e em seguida Ctrl + V
(Colar).

12) Crie mais 2 Cópias (4 no total).

13) Posicione elas uma no lado da outra
como mostrado abaixo:

14) Altere o nome das cópias para:
• Produtos
• Fornecedores
• Pagamentos

Obs: se for necessário faça a alteração do
tamanho do retângulo para o texto ser
ajustado.
15) Crie mais 3 planilhas.

16) Renomeie para os seguintes nomes:
• Clientes;
• Produtos;
• Fornecedores;
• Pagamentos;

✓ Criando hiperlink

17) Na planilha de cliente clique com o botão
Direito no retângulo escrito Cliente.

18) Clique na opção Link

19) Na janela que apareceu clique em
Colocar neste documento.

20) Repare que apareceu o nome das suas
planilhas e também apareceu em qual
célula da planilha você deseja que o link
ocorra

21) Como clicamos no retângulo de cliente
selecione a planilha de Clientes e digite
a célula A6.

22) Clique em Ok.

23) Faça a mesma coisa com os outros 3
botões, sempre colocando o link com a
planilha correspondente e colocando na
célula A6.

24) Agora selecione da linha 1 até a linha 5.

25) Pressione Ctrl + C.

26) Clique na planilha de Produtos.

27) Clique na célula A1.

28) Pressione Ctrl + V.

29) Repare que todos os botões e suas
formatações foram copiadas para a
planilha Produto.

30) Agora faça a mesma ação nas guias de
Fornecedores e de Pagamentos.

31) Volte para a guia Clientes.

32) Clique na célula A6.

33) Clique na guia Exibir.
58

34) Clique em Congelar Painéis e em
seguida em Congelar Painéis.

Obs: está ferramenta deixará sempre o
painel de navegação na parte superior da
sua tela mesmo que você desça a planilha.

35) Segure a tecla Ctrl e clique na forma
escrito Clientes.

Obs: é clicado segurando a tecla Ctrl para
seja possível selecionar a forma sem que
seja ativado o hiperlink.
36) Clique na guia Formatar

37) Altere a cor do preenchimento a seu
gosto.

38) Vá para a planilha Produto, Congele o
painel na célula A6 e altere para a
mesma cor que foi alterado o botão de
Clientes.

39) Faça a mesma coisa nas outras
planilhas.

40) Teste o funcionamento da planilha.

41) Salve seu arquivo.

✓ Revisão
1) Crie uma nova planilha e utilizando
hyperlink e a edição das células crie o
menu solicitado a baixo:

LIÇÃO 10 - FUNÇÃO ÍNDICE

✓ Função ÍNDICE()
A função índice é uma das funções de
pesquisa e referência, esta função pesquisa
valores de forma matricial, ou seja, faz a
pesquisa dentro de sua tabela a partir de um
número de linha e um número de coluna.
Sua Sintaxe pode ser escrita de duas
formas:
=ÍNDICE(matriz; núm_linha; [núm_coluna])
Ou
=ÍNDICE (referência, núm_linha, [núm_coluna],
[núm_área])
A primeira forma de se escrever a
função serve para se fazer pesquisa de
valores em uma única tabela utilizando
apenas uma matriz, já a segunda maneira de
se escrever permite que você tenha várias
áreas,