EXCEL_AVANÇADO

Prévia do material em texto

CURSO APERFEIÇOAMENTO / 
ESPECIALIZAÇÃO PROFISSIONAL 
MS EXCEL AVANÇADO 
 
 
 
 
 
 
 
 
 
 
Identificação do participante 
Nome: 
Telefone: 
E-mail: 
N de identificação: 
Instrutor (a): 
Data do curso: 
 
 
CURSO APERFEIÇOAMENTO / 
ESPECIALIZAÇÃO PROFISSIONAL 
MS EXCEL AVANÇADO 
 
 
 
Professor Aldo R. Mendes 
Todos os direitos autorais reservados. Este manual não pode ser 
copiado, fotocopiado, reproduzido, traduzido ou convertido em 
qualquer forma eletrônica, ou legível por qualquer meio, em 
parte ou no todo, sem aprovação prévia, por escrito do autor, 
estando contra fator sujeito a responder por crime de violação de 
Direito Autoral, conforme o art. 184 do código penal Brasileiro, 
além de responder por perdas e danos. Todos os logotipos e 
marcas utilizados neste material pertencem as suas respectivas 
empresas. 
 
 MS EXCEL AVANÇADO 
Coordenação Geral 
Aldo Rodrigues Mendes 
Elaboração 
Aldo Rodrigues Mendes 
Equipe de colaboradores 
Aldo Rodrigues Mendes 
Samuel Pinho Carvalho 
Elaboração de exemplos e exercícios. 
Aldo Rodrigues Mendes 
Alexandra Vieira Alves 
Edição nº5 
Julho/2020 
Prof. Aldo Mendes 
 
 
SUMÁRIO 
1 INTRODUÇÃO 1-2 
2 REVISÃO DAS FUNÇÕES 2-2 
3 TRABALHANDO COM DATAS 3-2 
4 FUNÇÕES DE DECISÃO 4-2 
5 FUNÇÕES FINANCEIRAS 5-2 
6 FORMATAÇÃO CONDICIONAL 6-2 
7 ANÁLISE DE DADOS 7-2 
8 TABELA E GRÁFICO DINÂMICO 8-2 
9 FUNÇÕES DE BANCO DE DADOS 9-2 
10 TESTE DE HIPÓTESES 10-2 
11 SEGURANÇA 11-2 
12 MACROS 12-2 
13 INTRODUÇÃO VBA 13-2 
14 TECLAS DE ATALHO 14-2 
15 IMPORTAÇÃO DE DADOS 15-2 
16 TRABALHANDO EM EQUIPE 16-2 
 
 
 
 
 
 
 
 
 
 
 
1 INTRODUÇÃO 1-2 
REFERÊNCIAS 1-2 
 
 
 Introdução 
 
1 INTRODUÇÃO 
 
Excel é um programa de planilhas do sistema Microsoft Office. Você pode usar o Excel 
para criar e formatar pastas de trabalho (um conjunto de planilhas) para analisar dados e 
tomar decisões de negócios mais bem informadas. Especificamente, você pode usar o 
Excel para acompanhar dados, criar modelos de análise de dados, criar fórmulas para 
fazer cálculos desses dados, organizar dinamicamente os dados de várias maneiras e 
apresentá-los em diversos tipos de gráficos profissionais.1 
 
 
 
 
REFERÊNCIAS 
 
1 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Introdução ao Excel 2013). 
 
 
 
 
2 REVISÃO DAS FUNÇÕES 2 
2.1 REVISÃO DAS FUNÇÕES 2 
2.1.1 REVISÃO DAS FUNÇÕES 3 
2.1.2 EXERCÍCIO SOMA, MÁXIMO, MÍNIMO E MÉDIA. 4 
2.2 FUNÇÃO SE SIMPLES 6 
2.2.1 FUNÇÃO SE SIMPLES- EXEMPLO 7 
EXERCÍCIOS FUNÇÃO SE SIMPLES 8 
2.3 FUNÇÃO SE ANINHADO 9 
2.3.1 FUNÇÃO SE ANINHADO – EXEMPLO 10 
2.4 REFERÊNCIAS – RELATIVA E ABSOLUTA 11 
2.4.1 EXERCÍCIO SE 12 
2.5 FUNÇÃO PROCV 13 
2.5.1 EXEMPLO 1- PROCV 15 
2.5.2 EXEMPLO 2- PROCV 16 
2.5.3 EXEMPLO 3- PROCV 17 
2.5.4 EXERCÍCIO PROCV 18 
2.6 NOMEAR INTERVALO 21 
2.6.1 EDITAR O NOME DO INTERVALO 22 
2.7 AUDITORIA 23 
2.8 ÉRROS 24 
8.1 TIPOS DE ERROS 25 
EXERCÍCIOS ÉERROS 26 
REFERÊNCIAS 28 
Prof. Aldo Mendes 
 
 
2 - 2 
2 REVISÃO DAS FUNÇÕES 
Como ponto de partida para o estudo, será feita uma rápida revisão e demonstração 
da aplicação das funções de cálculo mais comuns e de maior probabilidade de uso no 
programa de planilha eletrônica Excel tais como: SOMA, MÍNIMO, MÁXIMO, MÉDIA, 
SE, PROCV.1 
2.1 Revisão das funções 
SOMA
Sintaxe:
SOMA(núm1;núm2; ...) argumentos de número de 2 a 255 que
você deseja somar.
MÉDIA
Retorna a média entre os intervalos
Sintaxe:
=MÉDIA(núm1;núm2; ...) números adicionais, referências de
célula ou intervalos para os quais você deseja a média, até no
máximo 255.
REVISÃO DAS FUNÇÕES
5
 
SOMA 
Descrição: A função SOMA, soma todos os números que você especifica como 
argumentos. Cada argumento pode ser um intervalo, uma referência de célula, uma 
matriz, uma constante, uma fórmula ou o resultado de outra função. 2 
MÉDIA 
Esta função por sua vez faz os cálculos entre os intervalos e retorna o valor médio. 
Média, que é a média aritmética e é calculada por meio da adição de um grupo de 
números e, em seguida, da divisão pela contagem desses números3 
javascript:AppendPopup(this,'ofArgument_2_2')
javascript:AppendPopup(this,'xldefRange_3_3')
javascript:AppendPopup(this,'xldefCellReference_4_4')
javascript:AppendPopup(this,'xldefArray_5_5')
javascript:AppendPopup(this,'IDH_qudefConstant_6_6')
javascript:AppendPopup(this,'xldefFormula_7_7')
Prof. Aldo Mendes 
 
 
 
2 - 3 
Revisão das Funções 
2.1.1 Revisão das funções 
MÍNIMO
Retorna o valor mínimo de um conjunto de valores.
Sintaxe:
MÍN(núm1;núm2; ...) de 1 a 255 números cujo valor mínimo você
deseja saber.
MÁXIMO
Retorna o valor máximo de um conjunto de valores.
Sintaxe:
MÁXIMO(núm1;núm2; ...) de 1 a 255 números cujo valor máximo você
deseja saber.
REVISÃO DAS FUNÇÕES
6
 
MÍNIMO 
A função MÍNIMO retorna o valor mínimo de um conjuto de valores, na versão MS 
Excel 2013 existe também a função MÌNIMOA, pois se você deseja incluir valores 
lógicos e representações de texto dos números em uma referência como parte do 
cálculo, utilize esta função. 4 
MÁXIMO 
A função MÁXIMO é uma fórmula pré-desenvolvida que assume um valor ou vários 
valores, executa uma operação e retorna um valor ou vários valores. Use as funções 
para simplificar e reduzir fórmulas em uma planilha, especialmente aquelas que 
executam cálculos longos e complexos.) no Microsoft Excel. Exemplo:5 
javascript:AppendPopup(this,'IDH_xldefFunction_1_1')
javascript:AppendPopup(this,'IDH_xldefFunction_1_1')
javascript:AppendPopup(this,'IDH_xldefFunction_1_1')
javascript:AppendPopup(this,'IDH_xldefFunction_1_1')
javascript:AppendPopup(this,'IDH_xldefFunction_1_1')
Prof. Aldo Mendes 
 
 
2 - 4 
2.1.2 Exercício Soma, Máximo, Mínimo e Média. 
EXERCÍCIO
Soma, Máximo, 
Mínimo e Média
7
 
1) Nas figuras 1, 2 e 3 elaborar 3 planilhas com as seguintes informações : 
 
Planilha 1 
 
1. Total 1º Trimestre: soma das vendas dos meses de Jan / Fev / Mar. 
2. Máximo: calcular o maior valor entre os meses de Jan / Fev / Mar. 
3. Mínimo: calcular o menor valor entre os meses de Jan / Fev / Mar. 
4. Média: calcular a média dos valores entre os meses de Jan / Fev / Mar 
5. Totais: Calcular os totais de: Total 1º Trimestre, Máximo, Mínimo e Média. 
Prof. Aldo Mendes 
 
 
 
2 - 5 
Revisão das Funções 
 
Figura 1: Exercício Soma, Média, Mínimo, Máximo 
 
Planilha 2: 
 
6. Total 2º Trimestre: soma das vendas dos meses de Abr / Mai / Jun. 
7. Máximo: calcular o maior valor entre os meses de Abr / Mai / Jun. 
8. Mínimo: calcular o menor valor entre os meses de Abr / Mai / Jun. 
9. Média: calcular a média dos valores entre os meses de Abr / Mai / Jun. 
10. Totais: soma das colunas de cada mês (1ª e 2ª tabela). 
11. Total do Semestre: soma dos totais de cada trimestre. 
 
Figura 2: Exercício Soma, Média, Mínimo, Máximo 
 
Prof. Aldo Mendes 
 
 
2 - 6 
Planilha 3 
12. Calcular os totais do 1ª trimestre e do 2ª trimestre. 
 
Figura 3: Exercício Soma, Média, Mínimo, Máximo 
Salvar como: Exercício Soma, Média, Mínimo e Máximo 
2.2 Função SE simples 
FUNÇÃO SE
Condição
A B
Verdadeiro Falso
Retorna um valor se uma condição que você especificou avaliar 
Como VERDADEIRO e um outro valor se for avaliado como FALSO.
Sintaxe:
=se(condição;verdadeiro;falso)
Exemplo:
=se(B2>=5;”SIM”;”NÃO”)
10
 
Retorna um valor se uma condição que você especificou avaliaromo 
VERDADEIRO e um outro valor se for avaliado como FALSO. Onde: 
 
Sintaxe: 
=SE (teste_lógico, [valor_se_verdadeiro], [valor_se_falso]) 
Prof. Aldo Mendes 
 
 
 
2 - 7 
Revisão das Funções 
2.2.1 Função SE Simples- exemplo 
FUNÇÃO SE – SIMPLES
Nota 1 Nota 2 Nota 3 Nota 4 Média Final Situação 1
10,00 5,50 7,502,00 6,25 
5,00 10,00 10,00 10,00 8,75 
2,00 2,00 6,00 4,00 3,50 
10,00 8,00 9,50 7,50 8,75 
Nota de Corte 7,5
Exemplo 1
11
 
Exemplo1 
No primeiro momento um aluno será considerado “APROVADO” se tiver sua média 
final maior ou igual a 7,5. 
 
Prof. Aldo Mendes 
 
 
2 - 8 
Exercícios Função SE simples 
EXERCÍCIO
12
SE simples
 
 
Figura 4 Exercícios de Função SE Simples 
 
 
Prof. Aldo Mendes 
 
 
 
2 - 9 
Revisão das Funções 
2.3 Função SE aninhado 
FUNÇÃO SE - ANINHADO
As funções aninhadas usam uma função como um dos argumentos de outra função.
Sintaxe 
=se(condição;verdadeiro;se(condição;verdadeiro;falso))
Exemplo
=se(Média aluno>=7,5;”aprovado”;se(Média aluno <= 5;”reprovado”;”Exame”))
Condição
B...
Verdadeiro Falso
Condição
A
Falso
Verdadeiro Falso
13
 
ANINHADO 
A função SE é usada para testar condições valor_se_verdadeiro e valor_se_falso se 
cair na condição falso dentro da própria função poderá entrar em outro teste lógico. 
Até 64 funções SE podem ser aninhadas como argumentos valor_se_verdadeiro e 
valor_se_falso para criar testes mais elaborados. Você também pode usar outras 
funções como argumentos.6 
 
 
Prof. Aldo Mendes 
 
 
2 - 10 
2.3.1 Função SE Aninhado – Exemplo 
FUNÇÃO SE – ANINHADO
Nota 1 Nota 2 Nota 3 Nota 4 Média Final Situação 1 Situação 2
10,00 5,50 7,50 2,00 6,25 
5,00 10,00 10,00 10,00 8,75 
2,00 2,00 6,00 4,00 3,50 
10,00 8,00 9,50 7,50 8,75 
Nota de Corte 7,5
Exame 5
Exemplo 2
14
 
Exemplo 2: 
Um aluno será considerado “APROVADO” se tiver sua média final maior ou igual a 
7,5 se ficar entre 7,5 e 5 está de “EXAME”, se for menor que 5 “REPROVADO. 
 
 
 
Prof. Aldo Mendes 
 
 
 
2 - 11 
Revisão das Funções 
2.4 Referências – Relativa e Absoluta 
REFERÊNCIAS – RELATIVA E ABSOLUTA
Referência Absoluta – Quando a célula, linha ou coluna NÃO deve 
ser trocada.
Referência Relativa – Quando a célula deve ser trocada.
Para fixar uma referência de célula, é preciso usar o cifrão ($), e a 
referência que estiver após o $ estará fixa.
Exemplo:
A1: Não “travada”
$A1: Somente a coluna A está travada”
A$1: Somente a linha 1 está “travada”
$A$1: A célula está “travada” (linha e coluna)
15
 
Ao copiar ou arrastar quaisquer funções ou fórmulas e, o Excel utiliza o conceito de 
Referência Absoluta e Referência Relativa na alteração dos endereços das células. 
 
Prof. Aldo Mendes 
 
 
2 - 12 
2.4.1 Exercício SE 
EXERCÍCIO
Função SE Aninhado
16
 
2) Com as informações da figura 5 aplique a função SE para apurar os resultados 
da planilha “Gol de Letra”. O resultado deverá ser : Vitória, Derrota ou Empate. 
 
Figura 5 Tabela de exercício Função SE 
Salvar como Exercício SE 
Prof. Aldo Mendes 
 
 
 
2 - 13 
Revisão das Funções 
2.5 Função PROCV 
Localiza um valor na primeira coluna de uma matriz da tabela e 
retorna um valor na mesma linha de outra coluna na matriz da 
tabela. 
Sintaxe:
=PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;[pr
ocurar_intervalo])
PROCV
18
 
A Função PROCV é usada para pesquisar no banco de dados uma informação 
baseada em uma chave de pesquisa. Por exemplo, qual o preço de um determinado 
produto identificado por uma referência ou modelo. 
Em primeiro lugar, devemos identificar a base de dados definidos um nome. 
Sintaxe: 
PROCV(valor_procurado,matriz_tabela, núm_índice_coluna, [procurar_intervalo]) 
 
Descrição: 
VALOR PROCURADO: Obrigatório. O valor a ser procurado na primeira coluna 
da tabela ou intervalo. O argumento valor_procurado pode ser um valor ou uma 
referência. Se o valor que você fornecer para o argumento valor_procurado for 
menor que o menor valor da primeira coluna do argumento matriz_tabela, PROCV 
retornará o valor de erro #N/D. 
Prof. Aldo Mendes 
 
 
2 - 14 
 
MATRIZ TABELA: Obrigatório. O intervalo de células que contém os dados. Você 
pode usar uma referência a um intervalo (por exemplo, A2:D8) ou um nome de 
intervalo. Os valores na primeira coluna de matriz_tabela são os valores 
procurados por valor_procurado. Os valores podem ser texto, números ou valores 
lógicos. Textos em maiúsculas e minúsculas são equivalentes. 
núm_índice_coluna Obrigatório. O número da coluna no argumento matriz_tabela 
do qual o valor correspondente deve ser retornado. Um argumento 
núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; 
um argumento núm_índice_coluna de 2 retornará o valor na segunda coluna em 
matriz_tabela e assim por diante.
7 
PROCURAR INTERVALO: Opcional. Um valor lógico que especifica se você quer 
que PROCV localize uma correspondência exata ou aproximada. Onde: 
 
VERDADEIRO: valores aproximados 
 
FALSO: valores exatos 
 
 
 
Prof. Aldo Mendes 
 
 
 
2 - 15 
Revisão das Funções 
2.5.1 Exemplo 1- PROCV 
FUNÇÃO PROCV
Exemplo 1
19
 
Exemplo 1 
Utilize a função PROCV para buscar na tabela acima, Origem, Preço e Estoque.O 
produto será informado na célula A13 
Responda: 
Qual foi o valor lógico utilizado neste exemplo? 
( ) Verdadeiro 
( ) Falso 
 
 
Prof. Aldo Mendes 
 
 
2 - 16 
2.5.2 Exemplo 2- PROCV 
FUNÇÃO PROCV
Exemplo 2
20
 
Exemplo 2 
Utilize a função PROCV para buscar na tabela o prêmio em função da produção 
 
Responda: 
Qual foi o valor lógico utilizado neste exemplo? 
( ) Verdadeiro 
( ) Falso 
 
 
Prof. Aldo Mendes 
 
 
 
2 - 17 
Revisão das Funções 
2.5.3 Exemplo 3- PROCV 
Exemplo 3
FUNÇÃO PROCV
21
 
Exemplo 3 
Utilize a função PROCV para buscar na tabela correspondente a Origem, Preço e 
Estoque.O produto será informado na célula A2. 
Responda: 
Qual foi o valor lógico utilizado neste exemplo? 
( ) Verdadeiro 
( ) Falso 
 
 
Prof. Aldo Mendes 
 
 
2 - 18 
2.5.4 Exercício PROCV 
EXERCÍCIO
Função PROCV
22
 
3) Os dados apresentados na planilha procv será uitlizado para o 
desenvolvimento do exercício. 
Planilha1 
A figura 7 mostra a tabela matriz, onde será a origem do valor procurado 
 
Figura 6 Tabela1 do exercício da Função PROCV 
Prof. Aldo Mendes 
 
 
 
2 - 19 
Revisão das Funções 
 
Planilha 2 
Utilize a função PROCV para buscar na tabela correspondente a Cidade e Faturado. 
O código será informado na célula A2. 
 
Figura 7 Tabela 2 do exercício da Função PROCV 
 
Planilha 3 
Inserir a função para mostrar a taxa de comissão do vendedor conforme o total de 
vendas na célula D2. 
 
Figura 8 Tabela 3 do exercício da Função PROCV 
 
Prof. Aldo Mendes 
 
 
2 - 20 
Planilha 4 
Na coluna C utilize a função PROCV para buscar na tabela de origem a porcentagem 
referente a comissão de cada venda, na coluna D calcular a comissão do vendedor. 
 
Figura 9 Tabela 4 do exercício da Função PROCV 
 
Salvar como Exercício PROCV 
 
Prof. Aldo Mendes 
 
 
 
2 - 21 
Revisão das Funções 
2.6 Nomear intervalo 
NOMEAR INTERVALO
23
 
Definir um nome para o intervalo 
1- Selecione o intervalo que deseja nomear. 
2- Na Guia Fórmulas, clique na caixa definir nome 
3- Digite o nome desejado. O nome não poderá conter espaços ou acentuação 
4- Clique em Ok 
 
 
 
Prof. Aldo Mendes 
 
 
2 - 22 
2.6.1 Editar o nome do Intervalo 
NOMEAR INTERVALO
Podemos criar um novo nome, editar ou excluir o intervalo.
24
 
Gerenciador de nomes 
O nome do intervalo poderá ser editado, excluído ou poderá criar um novo nome. 
Quando nomea-se um intervalo ele poderá servir como referência absoluta, por se 
tratar de células travadas. 
 
Prof.Aldo Mendes 
 
 
 
2 - 23 
Revisão das Funções 
2.7 Auditoria 
Existem alguns erros propositalmente inseridos nela para que se
possa usar o recurso de rastreamento de erros do Excel.
Com ele você localizará falhas em qualquer lugar da planilha,
mesmo os que você não esteja percebendo. Para localizar algum
erro, na guia Fórmulas, na parte Auditoria de Fórmulas, clique no
botão Verificação de Erros.
AUDITORIA
8
 
O auditor é uma ferramenta que ajuda a analisar a estrutura de uma planilha, 
possibilitando localizar fórmulas e erros que possam existir em seu trabalho, 
facilitando a operação que, quando feita manualmente, certamente demanda um 
tempo bastante grande, destacando fórmulas, ou a relação existente entre os valores 
e fórmulas de uma planilha. 
A figura 4 mostra a execução do comando apresenta cinco recursos. 
 
Figura 10 Cinco recursos para auditoria de fórmulas 
1- Rastrear Precedentes 
Esta opção permite que sejam identificadas todas as células ou faixas de células que 
fornecem informações para uma fórmula. Uma célula. 
2- Rastrear Dependentes 
Prof. Aldo Mendes 
 
 
2 - 24 
Esta opção permite que sejam identificadas as fórmulas que fornecem informações 
para alguma célula. Uma fórmula tem dependentes quando possui uma informação 
referenciada em uma célula ou faixa de células. 
3- Remover Setas 
Esse comando permite excluir da planilha todas as setas rastreadoras, que foram 
inseridas na planilha por meio dos comandos Rastrear dependentes e Precedentes 
4- Mostrar fórmulas 
Esta opção permite a exibição das fórmulas utilizadas na planilha. 
5- Rastrear Erro 
Esta opção que sejam desenhadas setas do valor de erro na célula ativa para as 
células que podem ter causado erro. A célula ativa deve conter um valor de erro; caso 
contrário, este comando não tem efeito. Caso apareçam setas vermelhas ou 
pontilhadas, elas apontarão para a primeira fórmula precedente que contem erro. No 
caso de setas azuis ou cheias apontarão para as células que possuem os valores 
precedentes da primeira fórmula que contém um erro.
8 
2.8 Érros 
É uma função que verifica se um determinado valor, seja ele uma célula ou fórmula 
contém algum tipo de erro. O resultado da função é um tipo lógico, podendo ser 
VERDADEIRO ou FALSO. Quando o valor analisado contiver algum tipo de erro, a 
fórmula retornará VERDADEIRO, caso contrário, retornará FALSO, o que significa 
que o valor não possui nenhuma inconsistência. 
Prof. Aldo Mendes 
 
 
 
2 - 25 
Revisão das Funções 
2.8.1 Tipos de erros 
A função ÉERROS estende a função EÉRRO, permitindo qualquer 
tipo de erro daqueles que são disponibilizados pelo Excel.
(#N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!)
TIPOS DE ERROS
26
 
 Veja o exemplo na figura 11. 
Exemplo: 
 
Figura 11 Exemplo de Função éerros 
 
Prof. Aldo Mendes 
 
 
2 - 26 
Exercícios éerros 
EXERCÍCIO
28
Função éerros 
 
 
AS Funções Utilizadas neste exercício de aplicação são: ÉERROS, PROCV, SE, 
SOMA, MÉDIA, MÍNIMO E MÁXIMO. 
 
 
Figura 12 Exercício de aplicação 
Prof. Aldo Mendes 
 
 
 
2 - 27 
Revisão das Funções 
Enunciado: 
Locadora JHUSTOS 
1 
Na planilha Filmes célula D204, D205 e D206 utilize as funções 
apropriadas para calcular o valor máximo, médio e mínimo dos valores 
de aluguéis dos vídeos. 
2 
Na célula B6 procure pelo nome do filme, correspondente ao código da 
célula A6, na planilha Filmes. Essa função deve servir para toda as 
demais células entre B7 e B30.(Inserir função para trabalhar erro) 
3 Faça o mesmo na célula C6 para a categoria do Filme. 
4 Na Célula A1 inserir 4 é o número de dias de permanência 
5 Na célula E6 (Devolução em:) use a função que retorna a data do dia. 
6 
Na Célula D6 insira datas aleatórias entre o primeiro e último dia do 
mês da célula E6 
7 
Na coluna Dias em Atraso (F) calcule a quantidade de dias em atraso 
da entrega do filme. 
8 
Se a quantidade de dias em atraso for menor que 10, calcule cada dia 
de atraso ao valor de R$ 2,50. Caso contrário utilize o valor de R$ 
2,85. 
9 
Na coluna H calcule o Total à Pagar sendo: o valor normal do aluguel 
+ a multa pelos dias em atraso. 
 
Prof. Aldo Mendes 
 
 
2 - 28 
REFERÊNCIAS 
 
1 MANZANO, A. L., & MANZANO, J. A. (2007). Estudo dirigido - Excel Avançado (7ª 
ed.). São Paulo: Érica Ltda. (introdução). 
2 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Soma). 
3 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Média). 
4 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Função Mínimoa). 
5 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Função Máxima). 
6 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Função SE). 
7 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Função PROCV). 
8 MANZANO, A. L., & MANZANO, J. A. (2007). Estudo dirigido - Excel Avançado (7ª 
ed.). São Paulo: Érica Ltda.(Auditor de células). 
 
 
 
 
 
3 TRABALHANDO COM DATAS 3-2 
3.1 FUNÇÕES HOJE E AGORA 3-2 
3.2 PRINCIPAIS FUNÇÕES DE DATAS 3-4 
3.3 DIA / MÊS / ANO 3-5 
3.4 DIA DE TRABALHO 3-7 
3.5 DIA DE TRABALHO TOTAL 3-8 
EXERCÍCIOS DE DADOS 3-9 
REFERÊNCIA 3-9 
 
 
Prof. Aldo Mendes 
 
 
 
3 - 2 
Trabalhando com datas 
3 Trabalhando com datas 
 
Uma das mais poderosas características do Excel é a sua habilidade de trabalhar com 
datas. Fazemos uso desta característica no módulo 3 sobre cálculos de dias, mês e 
ano, e cálculos de como por exemplo dias de trabalho. 
3.1 Funções Hoje e Agora 
√Hoje
A função Hoje() retorna a data atual do sistema. A data é inserida
no formato dd/mm/aaaa. Por exemplo, para inserir a data atual
em uma célula, basta digitar a seguinte fórmula: =Hoje ()
√Agora
Mas porque utilizar a função Agora() e não digitar a data e hora
diretamente? A vantagem da função Agora() é que ela atualiza o
valor da data e da hora, toda vez que a planilha for aberta. Com o
uso da função Agora() teremos sempre a data e hora atualizadas.
FUNÇÕES HOJE E AGORA
32
 
Função Hoje() 
Sintaxe : Hoje() 
Argumentos : Nenhum 
A função Hoje() retorna a data atual do sistema. A data é inserida no formato 
dd/mm/aaaa. Por exemplo, para inserir a data atual em uma célula, basta digitar a 
seguinte fórmula: =Hoje () 
Prof. Aldo Mendes 
 
 
 
3 - 3 
Trabalhando com datas 
Mas porque utilizar a função Hoje() e não digitar a data diretamente? A vantagem da 
função Hoje() é que ela atualiza o valor da data na célula, toda vez que a planilha for 
aberta no Excel. Com o uso da função Hoje() teremos sempre a data atualizada. 
Função Agora() 
Sintaxe : Agora() 
Argumentos : Nenhum 
A função Agora() retorna a data e hora do sistema. A data é inserida no formato 
dd/mm/aaaa e a hora no formato hh:mm . Por exemplo, para inserir a data e hora 
atual em uma célula, basta digitar a seguinte fórmula: 
=Agora() 
Mas porque utilizar a função Agora() e não digitar a data e hora diretamente? A 
vantagem da função Agora() é que ela atualiza o valor da data e da hora, toda vez 
que a planilha for aberta. Com o uso da função Agora() teremos sempre a data e hora 
atualizadas.i 
 
Prof. Aldo Mendes 
 
 
 
3 - 4 
Trabalhando com datas 
3.2 Principais funções de datas 
As principais funções de datas são:
√Hoje
√Agora
√Dia
√Mês
√Ano
√Diatrabalho
√Diatrabalhototal
FUNÇÕES
30
 
O Excel armazena todas as datas como integrais e todas as horas como frações 
decimais. Com esse sistema, ele pode adicionar, subtrair ou comparar datas e horas 
como qualquer outro número e todas as datas são manipuladas pelo uso desse 
sistema.Os valores retornados pelas funções ANO, MÊS e DIA serão valores 
gregorianos, independentemente do formato de exibição do valor de data fornecido. 
Por exemplo, se o formato de exibição da data fornecida for, os valores retornados 
para as funções ANO, MÊS e DIA serão valores associados à data gregoriana 
equivalente.
2 
 
 
Prof. Aldo Mendes 
 
 
 
3 - 5 
Trabalhando com datas 
3.3 Dia / Mês / Ano 
Dia
Mostrar tudo Ocultar tudo Retorna o dia de uma data representado
por um número de série. O dia é dado como um inteiro que varia de 1
a 31.
Mês
Mostrar tudo Ocultar tudo Retorna o mês de uma data representado
por um número de série. O mês é fornecido como um inteiro, variando
de 1 (janeiro) a 12 (dezembro).
Ano
Mostrar tudo Ocultar tudo Retorna o ano correspondente a uma data.
O ano é retornado como um inteiro no intervalo de 1900-9999.
DIA / MÊS /ANO
31
 
Dia 
Retorna o dia de uma data representado por um número de série. O dia é dado como 
um inteiro que varia de 1 a 31. 
Sintaxe DIA(núm_série) 
Núm_série: é a data do dia que você está tentando encontrar. As datas devem ser 
inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Por 
exemplo, use DATA(2008,5,23) para 23 de maio de 2008. 
 
 
 
Figura 1 Função data 
 
Prof. Aldo Mendes 
 
 
 
3 - 6 
Trabalhando com datas 
Mês 
Retorna o mês de uma data representado por um número de série. O mês é fornecido 
como um inteiro, variando de 1 (janeiro) a 12 (dezembro). 
Sintaxe MES(núm_série) 
Núm_série: é a data do mês que você está tentando encontrar. As datas devem ser 
inseridas com a função DATA ou como resultado de outras fórmulas ou funções. Por 
exemplo, use DATA(2008,5,23) para 23 de maio de 2008. 
 EXEMPLO: 
 
Figura 2 Função mês 
Ano 
Retorna o ano correspondente a uma data. O ano é retornado como um inteiro no 
intervalo de 1900-9999. 
Sintaxe ANO(núm_série) 
Núm_série é a data do ano que você deseja localizar. 
As datas são armazenadas como números de série sequenciais de forma que possam 
ser usadas em cálculos. Por padrão, 31 de dezembro, 1899 é o número de série 1, e 
1 de janeiro, 2008 é o número de série 39448 porque é 39.448 dias após 1 de janeiro, 
1900. 
EXEMPLO: 
 
 
Figura 3 Função Ano 
Prof. Aldo Mendes 
 
 
 
3 - 7 
Trabalhando com datas 
3.4 Dia de Trabalho 
 
Retorna um número que representa uma data que é o número indicado de dias úteis 
antes ou após uma data (a data inicial). Os dias úteis excluem fins de semana e 
quaisquer datas identificadas como feriados. Use a função para excluir os fins de 
semana ou feriados ao calcular as datas de vencimento de fatura, horas de entrega 
esperadas ou o número de dias de trabalho executado. 
Neste exemplo existe uma data onde se inicia uma compra (07/07/2013), e com 28 
dias úteis para o pagamento, não há feriados, utilizando a função DIATRABALHO 
podemos saber qual será o dia exato de recebimento do pagamento. será no dia 
16/08/2013. 
EXEMPLO: 
 
Figura 4 Função DIATRABALHO 
Prof. Aldo Mendes 
 
 
 
3 - 8 
Trabalhando com datas 
 
3.5 Dia de Trabalho Total 
 
Retorna o número de dias úteis inteiros entre data_inicial e data_final. Os dias úteis 
excluem os fins de semana e quaisquer datas identificadas em feriados. Use 
DIATRABALHOTOTAL para calcular os benefícios aos empregados que recebem 
com base no número de dias trabalhados durante um período específico. 
Sintaxe: DIATRABALHOTOTAL(data_inicial;data_final;feriados) 
As datas devem ser inseridas com a função DATA ou como resultado de outras 
fórmulas ou funções. 
 
Prof. Aldo Mendes 
 
 
 
3 - 9 
Trabalhando com datas 
Exercícios de dados 
EXERCÍCIO
35
Trabalhando com 
datas
 
Use a função necessária e descubra qual será dia da entrega. 
 
Figura 5 Exercício de datas 
Referência 
 
i Julio Battisti, Disponível em: http://www.juliobattisti.com.br/cursos/excelbasico/modulo4/licao7.asp, 
Acesso em 08 de julho de 2011. 
 
2 MICROSOFT, Disponível em: http://support.microsoft.com/kb/214094/pt-br, Acesso 07 de Julho de 
2011. 
 
 
http://www.juliobattisti.com.br/cursos/excelbasico/modulo4/licao7.asp
http://support.microsoft.com/kb/214094/pt-br
 
 
 
 
 
4 FUNÇÕES DE DECISÃO 4-2 
4.1 FUNÇÃO E 4-2 
4.2 FUNÇÃO OU 4-3 
4.3 FUNÇÕES CONT.SE E SOMASE 4-4 
4.3.1 FUNÇÃO CONT.SE 4-4 
4.3.2 FUNÇÃO SOMASE 4-6 
4.3.3 EXEMPLO- CONT.SE E SOMASE 4-8 
4.3.4 EXERCÍCIO -CONT.SE 4-9 
4.3.5 EXERCÍCIO - SOMASE 4-10 
REFERÊNCIAS 4-11 
 
 
Prof. Aldo Mendes 
 
 
 
 4 - 2 
Funções de Decisão 
 
4 FUNÇÕES DE DECISÃO 
O Excel pode oferecer vários tipos de funções de decisão tais como: Função E e OU 
como função de lógica, Função CONT.SE e SOMASE. Este módulo irá demostrar cada 
uma destas funções. 
4.1 Função E 
FUNÇÃO E
Retornará VERDADEIRO se todos argumentos forem
VERDADEIROS; retornará FALSO se apenas um argumento for
FALSO.
Sintaxe
=E(lógico1;[lógico2];...)
Lógico1,lógico2,...são de uma a 255 condições que você deseja
testar e que podem resultar em VERDADEIRO ou FALSO.
38
 
Como Utilizar a Função “E” 
A função E retorna VERDADEIRO se todos os seus argumentos forem VERDADEIROS. 
Um uso comum para a função E é expandir a utilidade de outras funções que realizam 
testes lógicos. 
Por exemplo, a função SE realiza um teste lógico e, em seguida, retornará um valor se o 
teste for avaliado como VERDADEIRO e outro valor se o teste for avaliado como 
FALSO. Usando a função E como argumento teste_lógico da função SE, você pode 
testar várias condições diferentes em vez de apenas uma. 
Prof. Aldo Mendes 
 
 
 
 4 - 3 
Funções de Decisão 
Sintaxe: 
=E(lógico1, [lógico2];...) 
A sintaxe da função E tem os seguintes argumentos: 
• lógico1 Obrigatório. A primeira condição que você deseja testar que pode ser 
avaliada como VERDADEIRO ou FALSO. 
• lógico2, Opcional. Condições adicionais que você deseja testar que pode ser 
avaliada como VERDADEIRO ou FALSO, até um máximo de 255 condições.1 
4.2 Função OU 
FUNÇÃO OU
Retornará VERDADEIRO se qualquer argumento for
VERDADEIRO; retornará FALSO se todos os argumentos forem
FALSOS.
Sintaxe
=OU(lógico1;lógico2;...)
Lógico1,lógico2,... são de uma a 255 condições que você deseja 
testar e que podem resultar em VERDADEIRO ou FALSO.
39
 
Como Utilizar a Função “OU” 
A função OU retorna VERDADEIRO se um dos argumentos for VERDADEIRO 
A sintaxe da função OU tem os seguintes argumentos: 
• Lógico1; lógico2;... Lógico1 é necessário, valores lógicos subsequentes são 
opcionais. Condições de 1 a 255 que você deseja testar e que podem resultar 
em VERDADEIRO ou FALSO.2 
Prof. Aldo Mendes 
 
 
 
 4 - 4 
Funções de Decisão 
4.3 Funções CONT.SE e SOMASE 
 
A seguir falaremos sobre as funções CONT.SE E SOMASE, onde trata-se de funções 
para análise de dados. 
Por exemplo, podemos usar a função CONT.SE para detectar quantas células não 
vazias, dentro de uma faixa de células, atendem a um determinado critério. A função 
CONT.SE retorna um número o qual indica quantas células contém um valor que 
corresponde ao critério especificado. 
Podemos usar a função SOMASE para efetuar uma soma condicional, onde os valores 
de uma coluna são ou não acrescentados a soma, com base nos valores de uma outra 
coluna.3 
4.3.1 Função CONT.SE 
Use o CONT.SE para contar o número de células dentro de um
intervalo que atendem a um único critério que você especifica.
Sintaxe 1:
=CONT.SE(Intervalo;critérios)
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. 
Sintaxe 2:
=CONT.SES(Intervalo_critérios1;critérios1;[intervalo_critério2;critério2...)
CONT.SE
41
 
CONT.SE 
DESCRIÇÃO: 
Afunçã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 
Prof. Aldo Mendes 
 
 
 
 4 - 5 
Funções de Decisão 
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. Por exemplo, suponha uma planilha 
que contenha uma lista de tarefas na coluna A e o nome da pessoa atribuída a cada 
tarefa na coluna B. Você pode usar a função CONT.SE para contar quantas vezes o 
nome de uma pessoa aparece na coluna B e, dessa maneira, determinar quantas tarefas 
são atribuídas a essa pessoa. 
SINTAXE: 
CONT.SE(intervalo; critérios) 
A sintaxe da função CONT.SE tem os seguintes argumentos: 
• intervalo Obrigatório. Uma ou mais células a serem contadas, incluindo 
números ou nomes, matrizes ou referências que contêm números. Campos 
em branco e valores de texto são ignorados. 
• critérios Obrigatório. Um número, uma expressão, uma referência de célula 
ou uma cadeia de texto que define quais células serão contadas. 
 
CONT.SES 
DESCRIÇÃO: 
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. 
Fique atento as novas regras 
Cada intervalo adicional deve ter o mesmo número de linhas e colunas que o argumento 
intervalo_critérios1. Os intervalos não precisam ser adjacentes entre si. 
SINTAXE: 
A sintaxe da função CONT.SES tem os seguintes argumentos: 
• intervalo_critérios1 Obrigatório. O primeiro intervalo no qual avaliar os 
critérios associados. 
Prof. Aldo Mendes 
 
 
 
 4 - 6 
Funções de Decisão 
• critérios1 Obrigatório. Os critérios no formato de um número, uma 
expressão, uma referência de célula ou um texto que define quais células 
serão contadas. 
• intervalo_critérios2, critérios2, ... Opcional. Intervalos adicionais e seus 
critérios associados. Até 127 intervalo/critérios pares são permitidos.4 
4.3.2 Função SOMASE 
Use a função SOMASE para somar os valores em um
intervalo que atendem aos critérios que você especificar.
Sintaxe 1:
=SOAMSE(intervalo;critérios;[intervalo_soma])
A função SOMASES adiciona as células em um intervalo que 
atendem a vários critérios. 
Sintaxe 2:
=SOMASES(intervalo_soma;intervalo_critérios1;critério1;...)
SOMASE
42
 
SOMASE 
DESCRIÇÃO 
Use a função SOMASE para somar os valores em um 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. É possível usar a seguinte fórmula: 
SINTAXE: 
=SOAMSE(intervalo;critérios;[intervalo_soma]) 
A sintaxe da função SOMASE tem os seguintes argumentos: 
Prof. Aldo Mendes 
 
 
 
 4 - 7 
Funções de Decisão 
• intervalo Obrigatório. O intervalo de células que se deseja calcular por 
critérios. As células em cada intervalo devem ser números e nomes, matrizes 
ou referências que contêm números. Espaços em branco e valores de texto 
são ignorados. 
• Critérios Obrigatório. 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. 
 
SOMASES 
DESCRIÇÃO: 
Adiciona as células em um intervalo que atendem a vários critérios. 
Fique atento as novas regras 
A ordem dos argumentos é diferente entre as funções SOMASES e SOMASE. Em 
particular, o argumento intervalo_soma é o primeiro em SOMASES, mas é o terceiro em 
SOMASE. Se você estiver copiando e editando essas funções semelhantes, coloque os 
argumentos na ordem correta. 
SINTAXE: 
A sintaxe da função SOMASES tem os seguintes argumentos: 
=SOMASES(intervalo_soma;intervalo_critérios1;critério1;...) 
• intervalo_soma Necessário. Uma ou mais células para somar, incluindo 
números ou nomes, intervalos ou referências de célula que contêm números. 
Valores em branco e de texto são ignorados. 
• intervalo_critérios1 Necessário. O primeiro intervalo no qual avaliar os 
critérios associados. 
• critérios1 Necessário. Os critérios no formato de um número, uma 
expressão, uma referência de célula ou um texto que define quais células no 
argumento intervalo_critérios1 serão adicionadas. Por exemplo, os critérios 
podem ser expressos como 32, ">32", B4, "maças" ou "32." 
Prof. Aldo Mendes 
 
 
 
 4 - 8 
Funções de Decisão 
• intervalo_critérios2, critérios2, … Opcional. Intervalos adicionais e seus 
critérios associados. Até 127 intervalo/critérios pares são permitidos.5 
4.3.3 Exemplo- CONT.SE e SOMASE 
FUNÇÃO CONT.SE E SOMASE
Exemplo 1
43
 
Neste exemplo usando as funções CONT.SE e SOMASE cria-se uma situação onde: 
1. Na célula B18 conte quantos salários são menores que R$ 500, e no C18 some 
estes salários menores que R$ 500. 
2. Na célula B19 conte quantos salários estão entre R$ 500 e R$ 1000, C19 some 
os salários encontrados entre o valor de R$ 500 e R$ 1000. 
3. Na célula B20 conte quantos salários são maiores que R$ 1000, e no C20 some 
estes salários maiores que R$ 1000. 
 
 
Prof. Aldo Mendes 
 
 
 
 4 - 9 
Funções de Decisão 
4.3.4 Exercício -CONT.SE 
EXERCÍCIO
Cont.se e Somase
44
 
Na figura 10 exibe o controle de faltas no mês de setembro no qual F significa faltas, e 
as células em branco simboliza que o aluno não tem falta neste dia. Para tanto pede-se: 
1. Utilize a função apropriada para calcular o total de faltas. 
2. Utilize a função apropriada para calcular o Bônus ou deixar o campo em branco a 
célula correspndente. 
 
Figura 1 Tabela de controle de faltas 
Observações: 
✓ Para Faltas utilize F 
Prof. Aldo Mendes 
 
 
 
 4 - 10 
Funções de Decisão 
✓ Limites de falatas no mês 2 
✓ O aluno que não exceder o limite ganhará de bônus 1 ponto na média,os 
alunos que excederem as faltas não ganhará o bônus. 
Salve como Exercício CONT.SE. 
4.3.5 Exercício - SOMASE 
Planilha1 
A figura 11 mostra a tabela matriz, onde será a origem do valor para realização deste 
exercício. 
 
Figura 2 tabela matriz 
Planilha 2 
Observe a figura 12 e utilize a função aproprida para calcular o total de vendas por 
categoria. O total de vendas será informado nas células F2 até F14, na célula F17 somar 
o valor total das vendas de totas as categorias. 
Prof. Aldo Mendes 
 
 
 
 4 - 11 
Funções de Decisão 
 
Figura 3 Tabela Exercício SOMASE 
Salve como Exercício SOMASE. 
 
 
REFERÊNCIAS 
 
1 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. (Função 
E) 
2 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. (Função 
OU) 
3 BATTISTI, J. C. (2002). Curso de Excel Avançado em 120 lições. Santa Cruz do Sul. 
4 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
CONT.SE (Função CONT.SE), (Função CONT.SES).CONT.SE (Função CONT.SE), 
(Função CONT.SES). 
5 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. ( (Função 
SOMASE), (Função SOMASES).SOMASE (Função SOMASE), (Função SOMASES). 
 
 
 
 
 
 
 
5 FUNÇÕES FINANCEIRAS 5-2 
5.1 ARGUMENTOS DA FUNÇÃO FINANCEIRA 5-2 
5.2 VF – VALOR FUTURO 5-3 
5.3 VP – VALOR PRESENTE 5-4 
5.4 PGTO – PAGAMENTO 5-5 
5.5 NPER – PERÍODOS 5-6 
5.6 TAXA 5-7 
EXERCÍCIOS – FUNÇÕES FINANCEIRAS 5-8 
REFERÊNCIAS 5-9 
 
 
 
Prof. Aldo Mendes 
 
 
 
5 - 2 
Funções Financeiras 
 
5 FUNÇÕES FINANCEIRAS 
As funções financeira, para cálculo do retorno sobre os investimentos, aceita 
argumentos similares. A seguir temos uma descrição dos argumentos que aparecem 
na nestas funções. 
5.1 Argumentos da Função Financeira 
ARGUMENTOS
46
√ Taxa: Taxa de juros por período. (O Excel sempre calcula a taxa
composta, que é a que acumulade um período para outro)
√ Nper: Número de períodos. (Meses, Ano, Dia)
√ Pgto: Valor desembolsado a cada período. (Prestação)
√ VF: Valor Futuro, (É o valor a ser obtido no final do último
período)
√ VP: Valor Presente. (É o inverso do Valor Futuro, é o valor
atual, à vista, de uma série de pagamentos que serão feitos
futuramente.
√ Tipo: Será 0 ou 1. (Zero indica que o pagamento será no final do
primeiro período e 1 no início)
 
Temos uma descrição dos argumentos que aparecem na nestas funções. 
A sintaxe das funções tem os seguintes argumentos: 
• Taxa Obrigatório. A taxa de juros por período. 
• Nper Obrigatório. O número total de períodos de pagamento em uma 
anuidade. 
• Pgto Obrigatório. O pagamento feito a cada período; não pode mudar 
durante a vigência da anuidade. Geralmente, pgto contém o capital e os 
juros e nenhuma outra tarifa ou taxas. Se pgto for omitido, você deverá 
incluir o argumento vp. 
javascript:AppendPopup(this,'ofArgument_2_2')
Prof. Aldo Mendes 
 
 
 
5 - 3 
Funções Financeiras 
• Vf Opcional. O valor futuro, ou o saldo, que você deseja obter depois do 
último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de 
um empréstimo, por exemplo, é 0). Por exemplo, se você deseja 
economizar R$ 50.000 para pagar um projeto especial em 18 anos, então 
o valor futuro será de R$ 50.000. Você poderia então fazer uma 
estimativa conservadora na taxa de juros e concluir quanto economizaria 
por mês. Se vf for omitido, você deverá incluir o argumento pgto. 
• Vp Opcional. O valor presente ou a soma total correspondente ao valor 
presente de uma série de pagamentos futuros. Se vp for omitido, será 
considerado 0 (zero) e a inclusão do argumento pgto será obrigatória. 
• Tipo Opcional. O número 0 ou 1 e indica as datas de vencimento dos 
pagamentos. Se tipo for omitido, será considerado 0.1 
5.2 VF – Valor Futuro 
VALOR FUTURO
✓VF: Valor Futuro, (É o valor a ser obtido no final do último 
período) 
Sintaxe:
=VF(taxa;nper;pgto;[vp];[tipo])
Exemplo 1
Qual o montante final de uma aplicação na qual serão depositados 
R$ 5000,00, durante três anos, com uma taxa de 1,04% ao mês?
47
 
A função VF retorna o valor futuro de um investimento de acordo com os 
pagamentos periódicos e constantes e com uma taxa de juros constante.2 
Prof. Aldo Mendes 
 
 
 
5 - 4 
Funções Financeiras 
Sintaxe: 
=VF(taxa;nper;pgto;[vp];[tipo]) 
VAMOS RESOLVER ? 
Iremos resolver este exemplo de VF da apresentação 
Exemplo 1 
Qual o montante final de uma aplicação na qual serão depositados R$ 5000,00, 
durante três anos, com uma taxa de 1,04% ao mês? 
 
5.3 VP – Valor Presente 
✓VP: Valor Presente. (É o inverso do Valor Futuro, é o valor atual, à 
vista, de uma série de pagamentos que serão feitos futuramente)
Sintaxe:
=VP(taxa;nper;pgto;vf;tipo)
Exemplo 2
Quanto é necessário aplicar hoje para ter 10 mil reais daqui a um 
ano, considerando uma taxa de 1,76% ao mês?
VALOR PRESENTE
48
 
Retorna o valor presente de um investimento. O valor presente é o valor total 
correspondente ao valor atual de uma série de pagamentos futuros. Por exemplo, 
quando você toma uma quantia de dinheiro emprestada, a quantia do empréstimo é 
o valor presente para o concessor do empréstimo.3 
Sintaxe: 
VP(taxa, nper, pgto, [vf], [tipo]) 
Prof. Aldo Mendes 
 
 
 
5 - 5 
Funções Financeiras 
VAMOS RESOLVER ? 
Iremos resolver este exemplo de VP da apresentação 
Exemplo 2 
Quanto é necessário aplicar hoje para ter 10 mil reais daqui a um ano, considerando 
uma taxa de 1,76% ao mês? 
5.4 Pgto – Pagamento 
✓ Pgto: Valor desembolsado a cada período. (Pagamento)
Sintaxe:
=PGTO(taxa;nper;vp;vf;tipo)
Exemplo 3
Se você comprar um carro à vista por R$ 12.200,00, quanto pagará 
de prestação se financiar por 36 meses com uma taxa de 1,95%?
PAGAMENTO
49
 
Retorna o pagamento periódico de uma anuidade de acordo com pagamentos 
constantes e com uma taxa de juros constante.4 
Sintaxe: 
PGTO(taxa, nper, pv, [fv], [tipo]) 
 
VAMOS RESOLVER ? 
Iremos resolver este exemplo de PGTO da apresentação 35 
Prof. Aldo Mendes 
 
 
 
5 - 6 
Funções Financeiras 
Exemplo 3 
Se você comprar um carro à vista por R$ 12.200,00, quanto pagará de prestação se 
financiar por 36 meses com uma taxa de 1,95%? 
5.5 NPER – Períodos 
√Nper: Número de períodos. (Meses, Ano, Dia)
Sintaxe:
=NPER(taxa;pgto;vp;vf;tipo)
Exemplo 4
Em quantos meses eu terei R$ 10.000,00 se aplicar R$ 500,00 
todo mês a uma taxa de 1% ao mês ?
PERÍODOS
50
 
Retorna o número de períodos para investimento de acordo com pagamentos 
constantes e periódicos e uma taxa de juros constante.5 
Sintaxe: 
NPER(taxa,pgto,vp,[vf],[tipo]) 
 
VAMOS RESOLVER ? 
Iremos resolver este exemplo de NPER da apresentação 
Exemplo 4 
Em quantos meses eu terei R$ 10.000,00 se aplicar R$ 500,00 todo mês a uma taxa 
de 1% ao mês ? 
Prof. Aldo Mendes 
 
 
 
5 - 7 
Funções Financeiras 
5.6 TAXA 
✓ Taxa: Taxa de juros por período. (O Excel sempre calcula a taxa 
composta, que é a que acumula de um período para outro)
Sintaxe:
=TAXA(nper;pgto;vp;vf;tipo;estimativa)
Exemplo 5
Qual a taxa de juros de um empréstimo de R$ 15.000,00 a ser pago em 
10 parcelas de R$ 1.580,00?
TAXA
51
 
O argumento estimativa deve ser especificado caso você tenha uma idéia da taxa, 
pois a função TAXA faz o cálculo com até 20 tentativas. Se não for possível 
encontrar um valor retorna o erro #NUM!. Especifique a estimativa entre e 1.6 
Sintaxe: 
TAXA(nper, pgto, vp, [vf], [tipo], [estimativa]) 
Exemplo 5 
Qual a taxa de juros de um empréstimo de R$ 15.000,00 a ser pago em 10 parcelas 
de R$ 1.580,00 
 
Prof. Aldo Mendes 
 
 
 
5 - 8 
Funções Financeiras 
 Exercícios – Funções Financeiras 
EXERCÍCIO
52
Funções Financeiras
 
 
1) Um capital de R$560,00 ficou aplicado durante um ano e três meses à taxa de 
5%a.m. de juros compostos. Qual o montante final? 
 
2) Determinar o valor aplicado, por 10 meses, a uma taxa de juros de 3,75% 
a.m., gerando um montante de R$100.000,00. 
 
3) Qual o montante produzido por um capital de R$150.000 que ficou aplicado 
durante dois anos e sete meses à taxa de 8,2%a.m. de juros compostos? 
 
4) Quanto devo investir para ter uma retirada de R$ 2.500,00 ao término de 6 
meses a uma taxa de 2,5% a.m.? 
 
5) Um amigo vai comprar um apartamento financiado em 120 meses, a uma taxa 
de juros de 3% a.m.. Qual o valor de cada parcela, se o imóvel for adquirido 
sem entrada a um valor de R$ 155.000,00? 
 
 
 
Prof. Aldo Mendes 
 
 
 
5 - 9 
Funções Financeiras 
REFERÊNCIAS 
 
1 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(funções financeiras). 
2 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Funções financeiras, Função VF). 
3 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Funções financeiras, Função VP). 
4 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(funções financeiras, Função PGTO). 
5 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Funções financeiras, Função NPER). 
6 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Funções financeiras, Função TAXA) 
 
 
 
 
 
 
6 FORMATAÇÃO CONDICIONAL 6-2 
6.1 FORMATAÇÃO CONDICIONAL NA FAIXA DE OPÇÕES 6-2 
6.2 EXECUTANDO UMA FORMATAÇÃO CONDICIONAL 6-3 
6.3 BARRAS DE DADOS 6-4 
6.4 ESCALA DE COR 6-5 
6.5 CONJUNTO DE ÍCONES 6-6 
6.6 GERENCIADOR DE REGRAS 6-7 
REFERÊNCIAS 6-9 
 
Prof. Aldo Mendes 
 
 
 
6 - 2 
Formatação Condicional 
6 FORMATAÇÃO CONDICIONAL 
 
A formatação condicional diferencia a célula quando a condição for diferente daestabelecida. Podemos definir uma formação para uma ou mais células. Nesta versão 
não possui limites para formatar condicionalmente, mas atenção, não abuse do 
número de formatações aplicadas, desde que compreenda como ela funciona. 
6.1 Formatação Condicional na faixa de opções 
FORMATAÇÃO CONDICIONAL
A formatação Condicional diferencia a célula quando a condição
for diferente da estabelecida.
55
 
Aplicando a formatação condicional aos seus dados, você identifica rapidamente 
variações em uma faixa de valores com uma visão rápida. 
Também pode-se dizer que a formatação Condicional ajuda a responder visualmente 
a questões específicas sobre seus dados. Há diferenças importantes que devem ser 
entendidas ao usar formatação condicional em um relatório de Tabela Dinâmica. 
Siga o passo a passo de uma formatação condicional: 
1. No menu Página inicial 
Prof. Aldo Mendes 
 
 
 
6 - 3 
Formatação Condicional 
2. Formatação Condicional. 
3. Nova regra 
4. Formatar apenas células que contenham 
5. Edite a descrição da regra 
6.2 Executando uma formatação Condicional 
FORMATAÇÃO CONDICIONAL
56
 
Editando regras para a formatação condicional com valores opcionais, como mostra 
na figura 13: 
 
Figura 1 Regras de formatação 
Prof. Aldo Mendes 
 
 
 
6 - 4 
Formatação Condicional 
6.3 Barras de dados 
BARRAS DE DADOS
1
25
35
15
1
8
20
10
11
A barras de dados formata condicionalmente do maior para o menor valor
57
 
Formatar todas as células usando barras de dados 
Uma barra de dados ajuda você a ver o valor de uma célula com relação a outras 
células. O comprimento da barra de dados representa o valor na célula. Uma barra 
mais longa representa um valor mais alto e uma barra mais curta representa um valor 
mais baixo. Barras de dados são úteis para indicar números mais altos e mais baixos, 
especialmente com grandes quantidades de dados, como brinquedos mais e menos 
vendidos em um relatório de vendas de fim de ano. 
1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de 
Tabela Dinâmica. 
2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação 
Condicional, clique em Barras de Dados e, em seguida, selecione um ícone de 
barra de dados.1 
 
Prof. Aldo Mendes 
 
 
 
6 - 5 
Formatação Condicional 
6.4 Escala de Cor 
1
25
35
15
1
8
20
10
11
ESCALA DE COR
Selecione o intervalo e formate 
condicionalmente com escalas de cores
Números <50% e >50% as cores 
variam entre Rosa, Amarelo e 
verde.
58
 
As escalas de cores são guias visuais que ajudam a entender a distribuição e a 
variação de dados. Uma escala de três cores ajuda a comparar um intervalo de 
células usando uma gradação de três cores. O tom da cor representa valores 
maiores, médios ou menores. Por exemplo, em uma escala de cores verde, amarela e 
vermelha, é possível especificar que células de valores mais altos tenham a cor 
verde, valores médios tenham a cor amarela e células de valores mais baixos tenham 
a cor vermelha.2 
Etapas para formatar com escalas de cores 
1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de 
Tabela Dinâmica. 
2. Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação 
Condicional e, em seguida, clique em Escalas de Cor. 
3. Selecione uma escala de três cores. A cor da parte superior representa valores 
maiores, a cor do centro representa valores médios e a cor da parte inferior 
representa valores menores. 
Prof. Aldo Mendes 
 
 
 
6 - 6 
Formatação Condicional 
6.5 Conjunto de ícones 
Critérios utilizado com 
porcentagem do maior valor
CONJUNTO DE ÍCONES
59
 
Use um conjunto de ícones para anotar e classificar dados em três a cinco categorias 
separadas por um valor limite. Cada ícone representa um intervalo de valores. Por 
exemplo, no conjunto de ícones de 3 Setas, a seta verde para cima representa 
valores mais altos, a seta amarela lateral representa valores médios e a seta 
vermelha para baixo representa valores mais baixos. 
Você pode optar por exibir ícones somente para células que atendem a uma 
condição; por exemplo, a exibição de um ícone de aviso para células que estão 
abaixo de um valor crítico e nenhum ícone para aquelas que o ultrapassam. Para 
isso, oculte os ícones selecionando Sem Ícone de Célula na lista suspensa de ícones 
ao lado do ícone ao definir as condições. Você também pode criar sua própria 
combinação de conjuntos de ícones; por exemplo, uma marca de seleção de 
"símbolo" verde, uma "luz de tráfego" amarela e um "sinalizador" vermelho. 
Problema: a formatação condicional não é exibida em nenhuma célula do 
intervalo. 
Prof. Aldo Mendes 
 
 
 
6 - 7 
Formatação Condicional 
1. Selecione uma ou mais células em um intervalo, uma tabela ou um relatório de 
Tabela Dinâmica. 
2. Na guia Página Inicial, no grupo Estilo, clique na seta ao lado de Formatação 
Condicional, clique em Conjunto de Ícones e selecione um conjunto de ícones. 
Dica É possível alterar o método de escopo de campos na área Valores de um 
relatório de Tabela Dinâmica usando o botão de opção Aplicar regra de formatação.3 
6.6 Gerenciador de regras 
GERENCIADOR DE REGRAS
60
 
Se suas necessidades de formatação condicional forem mais complexas, você pode 
usar uma fórmula lógica para especificar os critérios de formatação. Por exemplo, 
talvez você queira comparar valores com um resultado retornado por uma função ou 
avaliar dados em células fora do intervalo selecionado. 
Na guia Página Inicial, no grupo Estilos, clique na seta ao lado de Formatação 
Condicional e, em seguida, clique em Gerenciar Regras. 
A caixa de diálogo Gerenciador de Regras de Formatação Condicional será 
exibida. 
Prof. Aldo Mendes 
 
 
 
6 - 8 
Formatação Condicional 
Siga um destes procedimentos: 
• Para adicionar um formato condicional, clique em Nova Regra. 
• A caixa de diálogo Nova Regra de Formatação será exibida. 
• Para alterar um formato condicional, siga este procedimento: 
Verifique se a planilha, a tabela ou o relatório de Tabela Dinâmica apropriado está 
selecionado na caixa de listagem Mostrar regras de formatação para. 
Como opção, altere o intervalo de células clicando em Recolher Caixa de Diálogo 
na caixa Aplica-se a para ocultar temporariamente a caixa de diálogo, 
selecionando o novo intervalo de células na planilha ou nas outras planilhas e, em 
seguida, selecionando Expandir Caixa de Diálogo . 
Selecione a regra e, em seguida, clique em Editar regra. 
A caixa de diálogo Editar Regra de Formatação será exibida. 
1. Em Aplicar Regra a, altere opcionalmente o escopo dos campos na área Valores 
de um relatório de Tabela Dinâmica por: 
▪ Seleção, clique em Apenas estas células. 
▪ Campo de correspondência, clique em Todas as células de <campo de valor> 
com os mesmos campos. 
▪ Campo de valor, clique em Todas as células de <campo de valor>. 
2. Em Selecione um Tipo de Regra, clique em Usar uma fórmula para 
determinar as células a formatar. 
1. Em Edite a Descrição da Regra, na caixa de listagem Formatar valores nos 
quais esta fórmula é verdadeira, digite uma fórmula. 
Você deve iniciar a fórmula com um sinal de igual (=) e a fórmula deve 
retornar um valor lógico VERDADEIRO (1) ou FALSO (0). 
Exemplo 1: use um formato condicional com vários critérios e referências 
de células fora do intervalo de células 
Prof. Aldo Mendes 
 
 
 
6 - 9 
Formatação Condicional 
Nesta fórmula, um formato condicional com vários critérios aplicados ao intervalo 
A1:A5 formata as células em verde quando o valor médio para todas as células do 
intervalo é maior que o valor na célula F1 e alguma célula no intervalo tem um valormínimo maior ou igual ao valor em G1.4 
 
 
 
REFERÊNCIAS 
 
1 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. (Barra 
de dados) 
2 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Escala de cor) 
3 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Conjunto de ícones) 
4 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Gerenciador de regras). 
 
 
 
 
7 ANÁLISE DE DADOS 7-2 
7.1 FILTRO 7-2 
7.1.1 CLASSIFICAÇÃO DE REGISTROS 7-3 
7.1.2 FILTRO POR DATA 7-4 
7.1.3 FILTRAR POR COR 7-6 
7.1.4 FILTROS DE TEXTO 7-8 
7.1.5 FILTRO DE NÚMEROS 7-9 
7.1.6 FILTRO POR SELEÇÃO 7-10 
7.2 FILTRO AVANÇADO 7-11 
7.2.1 EXECUTANDO UM FILTRO AVANÇADO 7-11 
7.3 SUBTOTAL 7-14 
7.3.1 O USO DE SUBTOTAIS 7-14 
7.4 OPERAÇÕES DE BUSCA E TROCA 7-17 
7.4.1 EXERCÍCIO –FILTRO, FILTRO AVANÇADO E SUBTOTAL 7-18 
REFERÊNCIAS 7-20 
 
Prof. Aldo Mendes 
 
 
 
7 - 2 
Análise de Dados 
7 ANÁLISE DE DADOS 
Para extrair dados de uma base podemos utilizar algumas ferramentas do Excel. 
Neste módulo aprenderemos 3 tipos diferentes. FILTRO, FILTRO AVANÇADO e 
SUBTOTAL. O Filtro nos permite extrair informações a partir de critérios pré-
estabelecidos pelo Excel. Com o Filtro Avançado podemos personalizadar os 
critérios criando uma tabela de critérios. Veremos ainda a ferramenta Subtotal, que 
através da função subtotal nos dá a possibilidade de subtotalizar os dados pela 
coluna desejada. 
7.1 Filtro 
√Classificar
√Personalizar classificação
√Filtrar
FILTRO
63
 
A filtragem de informações em uma planilha possibilita encontrar valores 
rapidamente. Você pode filtrar uma ou mais colunas de dados. Com a filtragem, é 
possível controlar não apenas o que ver, mas também o que excluir. Você pode 
filtrar com base nas opções escolhidas em uma lista, ou criar filtros específicos 
focados exatamente nos dados desejados.1 
Prof. Aldo Mendes 
 
 
 
7 - 3 
Análise de Dados 
7.1.1 Classificação de registros 
√Por data
√Por cor
√Por texto
√Por Número
√Por seleção
CLASSIFICAÇÃO DE REGISTROS
64
 
Você pode pesquisar texto e números ao filtrar, usando a caixa de diálogo 
Pesquisar na interface de filtro. Durante a filtragem de dados, linhas inteiras serão 
ocultadas se valores de uma ou mais colunas não atenderem aos critérios de 
filtragem. Você pode filtrar valores numéricos ou texto, filtrar por cor, para células 
que tenham formatação de cores aplicada ao plano de fundo ou ao texto dessas 
células. 
 
 
Prof. Aldo Mendes 
 
 
 
7 - 4 
Análise de Dados 
7.1.2 Filtro por data 
FILTROS POR DATA
65
 
1. Efectue um dos seguintes procedimentos: 
Intervalo de células 
A. Selecione um intervalo de células que contenha dados numéricos. 
B. No separador Base, no grupo Edição, clique em Classificar e Filtrar e, em 
seguida, clique em Filtrar. 
 
Figura 1 filtrar e classificar 
Tabela: 
Certifique-se de que a célula activa está presente numa coluna da tabela que 
contenha datas ou horas. 
Prof. Aldo Mendes 
 
 
 
7 - 5 
Análise de Dados 
C. Clique na seta no cabeçalho de coluna. 
D. Cliique na opção filtros por data (como mostra a figura acima) 
2. Efectue um dos seguintes procedimentos: 
Selecionar a partir de uma lista de datas 
• Na lista de datas ou horas, selecione ou desmarque uma ou mais datas ou 
horas pelas quais pretende filtrar.2 
Exemplo: 
 
Figura 2 Exemplo de Filtragem 
Prof. Aldo Mendes 
 
 
 
7 - 6 
Análise de Dados 
7.1.3 Filtrar por cor 
FILTRAR POR COR
66
 
Se tiver formatado manual ou condicionalmente um intervalo de células com cores 
de célula ou cores de tipo de letra, também poderá filtrar por essas cores. Poderá 
também filtrar por um conjunto de ícones criado através de um formato condicional. 
1. Efectue um dos seguintes procedimentos: 
A. Selecione um intervalo de células que contenha formatação por cor de célula, 
cor de tipo de letra ou um conjunto de ícones. 
B. No separador Base, no grupo Editar, clique em Ordenar e Filtrar e, em 
seguida, clique em Filtrar. 
 
Figura 3 classificar e filtrar 
 
Prof. Aldo Mendes 
 
 
 
7 - 7 
Análise de Dados 
Tabela 
C. Certifique-se de que a coluna da tabela contém dados formatados por cor de 
célula, cor de tipo de letra ou um conjunto de ícones (não é necessário efectuar 
qualquer seleção). 
2. Clique na seta no cabeçalho de coluna. 
3. Selecione Filtrar por Cor e, em seguida, dependendo do tipo de formatação, 
selecione Filtrar por Cor da Célula, Filtrar por Cor do Tipo de Letra ou Filtrar por 
Ícone da Célula. 
4. Dependendo do tipo de formatação, selecione uma cor, cor de tipo de letra ou ícone 
da célula.3 
Exemplo: 
 
Figura 4 Exemplo de filtro por cor 
Prof. Aldo Mendes 
 
 
 
7 - 8 
Análise de Dados 
7.1.4 Filtros de texto 
FILTROS DE TEXTO
67
 
Na lista de valores de texto, selecione ou desmarque um ou mais valores de texto 
pelos quais pretende filtrar. 
A lista de valores de texto pode chegar a 10,000. Se a lista for grande, desmarque 
(Selecionar Tudo) na parte superior e, em seguida, selecione os valores de texto 
específicos pelos quais pretende filtrar. 
Criar critérios 
1. Aponte para Filtros de Texto e, em seguida, clique num dos comandos de ou 
clique em Filtro Personalizado. 
Por exemplo, para filtrar texto que comece por um determinado carácter, 
selecione Começa com ou, para filtrar texto que contenha determinados 
caracteres independentemente da sua posição, selecione o vendedor. 
2. Na caixa de diálogo Personalizar AutoFiltro, na caixa à direita, introduza texto ou 
selecione o valor de texto a partir da lista. 
Prof. Aldo Mendes 
 
 
 
7 - 9 
Análise de Dados 
Por exemplo, para filtrar por texto que comece pela letra "J", introduza J ou, 
para filtrar por texto que contenha a sequência de caracteres "bola" em 
qualquer parte do texto. 
Se necessitar de localizar texto que partilhe alguns caracteres mas não 
outros, utilize um carácter universal. 
3. Opcionalmente, filtre com base em mais um critério.4 
7.1.5 Filtro de números 
FILTROS DE NÚMEROS
68
1
3
2
4
 
1. Use a caixa de diálogo pesquisar para inserir texto ou números a serem 
pesquisados. 
2. Marque e desmarque as caixas de seleção para mostrar os valores 
encontrados na coluna de dados. 
3. Use critérios avançados para encontrar valores que atendam a condições 
específicas. 
Prof. Aldo Mendes 
 
 
 
7 - 10 
Análise de Dados 
4. Você ainda poderá personalizar sua pesquisa de acordo com o seu filtro. 
7.1.6 Filtro por seleção 
FILTRO POR SELEÇÃO
69
 
Pode filtrar rapidamente dados com critérios iguais ao conteúdo da célula activa. 
Num intervalo de células ou coluna de tabela, clique com o botão direito do rato 
numa célula que contenha o valor, cor, cor de tipo de letra ou ícone pelo qual 
pretende filtrar. 
Clique em Filtrar e, em seguida, efetue um ou mais dos seguintes procedimentos: 
• Para filtrar por texto, número ou data ou hora, clique em Filtrar por Valor da 
Célula Selecionada. 
• Para filtrar pela cor da célula, clique em Filtrar por Cor da Célula Selecionada. 
• Para filtrar pela cor do tipo de letra, clique em Filtrar por Cor do Tipo de Letra 
da Célula Selecionada. 
• Para filtrar por ícone, clique em Filtrar por Ícone da Célula Selecionada.5 
 
Prof. Aldo Mendes 
 
 
 
7 - 11 
Análise de Dados 
7.2 Filtro Avançado 
 
O filtro avançado é de extrema importância na hora de trabalhar com grande 
quantidade de dados. Vamos aprender na versão do MS. Excel 2013. 
 
7.2.1 Executando um filtro avançado 
FILTRO AVANÇADO
O Filtro Avançadoé utilizado para filtrar os dados extrair para uma
outra área da planilha ou para uma nova planilha.
71
 
Agora, a pesquisa será efetuada com a finalidade de extrair os dados para uma 
outra área da planilha. É preciso criar uma área de critério e uma área de extração 
que devem possuir os mesmos campos da lista. Na figura 18 mostra como devemos 
criar estas duas condições. 
Prof. Aldo Mendes 
 
 
 
7 - 12 
Análise de Dados 
 
Figura 5 Apresentação da área de critérios e extração. 
A área de Critério será a busca de todas as informações dos instrumentos musicais 
dos países Austrália e Bélgica. 
Após definir as duas áreas: a de critérios e a de extração, execute p comando, 
Dados > Avançado, como mostra a figura 19: 
 
Figura 6 Localização do Filtro Avançado 
Será então apresentada a caixa de diálogo Filtro avançado, indicando a seleção da 
área da base de dados. Nesse momento selecione a opção: 
• Copiar para outro local, no campo Intervalo de critério digite o endereço da 
área de critério 
• Para o campo Copiar para, digite o endereço da área de extração 
• Finalize clicando no botão Ok. 
 
Prof. Aldo Mendes 
 
 
 
7 - 13 
Análise de Dados 
 
Figura 7 Caixa de diálogo do filtro avançado. 
 
Após a finalização, é apresentado o resultado da extração como mostra a figura 21: 
 
Figura 8 Apresentação de uma extração de dados. 
Perceba que para área de critério foram definidas três linhas, sendo uma com o 
nome dos campos e a outras duas dos dados a serem pesquisados. 
Prof. Aldo Mendes 
 
 
 
7 - 14 
Análise de Dados 
Como esta linha está com os países Austrália e Bélgica a extração ocorreu somente 
as informações dos instrumentos musicais destes dois países. 
Observação: 
Quando for necessário usar condições do tipo “OU”, é preciso estabelecer mais 
uma linha para alternar entre elas os elementos que serão pesquisados. Para 
executar o conceito do operador “E”, os dados devem ser digitados na mesma 
linha.6 
7.3 Subtotal 
 
Retorna um subtotal em uma lista ou em um banco de dados. É geralmente mais 
fácil criar uma lista com subtotais usando o comando Subtotais, grupo Contorno, 
na guia Dados. Assim que a lista de subtotais for criada, você poderá modificá-la 
editando a função SUBTOTAL. 
 
7.3.1 O Uso de Subtotais 
SUBTOTAIS
73
 
 Para Trabalhar com o recurso de Subtotais é necessário manter a base de dados 
segundo a ordem em que se deseja obter o subtotal 
Prof. Aldo Mendes 
 
 
 
7 - 15 
Análise de Dados 
Após a ordenação alfabética pelo campo que será o mais importante, mantenha o 
cursor posicionado dentro da área de base de dados e em seguida execute o 
comando do menu : 
Dados > Subtotais... 
 
Figura 9 Localização do Subtotal 
Então será apresentada a caixa de diálogo Subtotais, indicada na figura 23: 
 
Figura 10 Caixa de diálogo do Subtotal 
Esta caixa apresenta as seguintes opções abaixo: 
• A cada alteração em é o local em que se determina por qual será feito o 
subtotal 
• Usar função é a utilização da função estatística que será usada em cada 
intervalo de subtotal. O padrão é Soma, mas pode ser também Média, Máximo, 
etc. 
Prof. Aldo Mendes 
 
 
 
7 - 16 
Análise de Dados 
• Adicionar subtotal a nesse local é(são) determinado(s) o (s) campo(s) que terá 
(ão) abaixo um subtotal. Normalmente é dado à última coluna por ele tratar de 
valores totais. 
• Substituir subtotais atuais quando ligado, serve para substituir subtotais que 
já tenham sido anteriormente. 
• Quebra de páginas entre grupos é muito interessante esse recurso, pois a 
cada mudança de dados de um mesmo campo, ele automaticamente quebra a 
página, facilitando a impressão de um relatório por grupo de dados, ou seja, a 
cada campo 
• Resumir abaixo dos dados é a colocação do total geral de todos os grupos 
abaixo destes; caso contrário, o total geral será colocado como primeira 
informação da base de dados, abaixo somente dos nomes dos campos. 
• Remover todos quando se deseja desligar com o recurso aplicado na base. 
 
Neste caso, a base de dados deve estar ordenada pelos nomes dos países, como 
mostra o exemplo abaixo, nesse momento, acione o botão OK e observe como ficará 
a apresentação das informações da base de dados : 
 
Figura 11 Apresentação do Subtotal 
Observe a esquerda apareceram três novos botões numerados como 1, 2 e 3. 
Clique sobre cada um deles para obter a seguinte relação de visão: 
• Botão 1 – Visão Executiva, pois nela obtém-se o total geral da listagem com a 
qual se está trabalhando. 
Prof. Aldo Mendes 
 
 
 
7 - 17 
Análise de Dados 
• Botão 2 – Visão gerencial, pois mostra somente os totais dos elementos 
existentes na listagem, como mostra o exemplo acima. 
• Botão 3 – Visão operacional, pois mostra a listagem dos elementos e 
simultaneamente os seus respectivos totais. 
Experimente extrair subtotal com médias, desvio padrão, com maiores e menores 
valores, etc.7 
7.4 Operações de busca e troca 
Localizar permite determinar a direção da
pesquisa
Substituir permite efetuar a substituição
dos caracteres pesquisados nas células
ativa
OPERAÇÕES DE BUSCA E TROCA
74
1 2
74
3 4 5
 
Outro recurso a ser executado em uma base de dados são os operações de busca e 
troca de informações. 
1. Substituir tudo permite efetuar a localização e substituição de todas as 
ocorrências dos caracteres especificados no campo “Localizar” pelos 
caracteres especificados no campo “substituir por” uma única vez. 
2. Substituir permite efetuar a substituição dos caracteres pesquisados nas 
células ativa 
Prof. Aldo Mendes 
 
 
 
7 - 18 
Análise de Dados 
3. Localizar tudo permite determinar a direção da pesquisa. Se ela vai ocorrer 
por linha ou por coluna. 
4. Localizar próximo permite localizar a próxima ocorrência dos caracteres 
informados no campo Localizar . 
5. Fechar permite efetuar o encerramento da caixa de diálogo, mas não interfere nas 
substituições já efetuadas.8 
7.4.1 Exercício –Filtro, Filtro Avançado e Subtotal 
EXERCÍCIO
Filtro, 
Filtro Avançado 
e Subtotal
75
 
Exercício 1 Auto Filtro 
Observe a figura 25 e siga as seguintes instruções: 
Prof. Aldo Mendes 
 
 
 
7 - 19 
Análise de Dados 
 
Figura 12 Exercício de Análise de Dados 
Planilha 1 
1. Classifique os registros: 
• Por data 
Deverão conter somente as datas de março à outubro 
• Por número 
Na coluna unidades pesquise todos valores que contenha 5 unidades 
• Por texto 
Na coluna País, pesquise somente “Austrália”. 
• Por cor 
Cada categoria deverá conter as seguintes cores: 
Cordas (azul) 
Eletrônicos (verde) 
Metais (marrom) 
Sopros (amarelo) 
Logo após filtre somente a categoria de cor azul. 
 
Prof. Aldo Mendes 
 
 
 
7 - 20 
Análise de Dados 
Planilha 2 
1. Crie os seguintes critérios: 
• Busque as informações do País Brasil 
• Na categoria eletrônico e 5 unidades 
• Na categoria Corda e o total >200 
• A data deverá estar entre 01/01/2008 e 31/03/2008 
• A data deverá estar entre 01/01/2008 e 31/03/2008 e com os países Japão, 
França, Brasil, e por a categoria Eletrônico, Sopro e Metal. 
Planilha 3 
2. Renomeie como: SUBTOTAL 
Crie um subtotal nesta planilha onde o critério será entre os países somado as 
unidades. 
 
REFERÊNCIAS 
 
1 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Filtrar dados usando um filtro automático). 
2 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(.filtrar datas ou horas) 
3 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Filtrar por cor de célula, cor de fonte). 
4 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos:Microsoft, 2013. 
(Filtrar texto). 
5 MICROSOFT Corporation, Ajuda do Excel - Estados Unidos: Microsoft, 2013. 
(Filtrar por seleção). 
6 MANZANO, A. L., & MANZANO, J. A. (2007). Estudo dirigido - Excel Avançado (7ª 
ed.). São Paulo: Érica Ltda. (Filtro Avançado). 
7 MANZANO, A. L., & MANZANO, J. A. (2007). Estudo dirigido - Excel Avançado (7ª 
ed.). São Paulo: Érica Ltda.(Subtotais) 
8 MANZANO, A. L., & MANZANO, J. A. (2007). Estudo dirigido - Excel Avançado (7ª 
ed.). São Paulo: Érica Ltda.(operação de busca e troca). 
 
 
8 TABELA E GRÁFICO DINÂMICO 8-2 
8.1 TABELA DINÂMICA 8-2 
8.1.3 LAYOUT – TABELA DINÂMICA 8-3 
8.1.1 EXEMPLO 2 – TABELA 8-4 
8.1.2 DESIGN – TABELA DINÂMICA 8-6 
8.2 GRÁFICO DINÂMICO 8-7 
8.2.1 GRÁFICO DINÂMICO - CAMPOS DE EXIBIÇÃO 8-10 
8.2.2 GRÁFICO DINÂMICO- DESIGN 8-11 
8.2.3 GRÁFICO DINÂMICO – LAYOUT 8-11 
8.2.4 GRÁFICO DINÂMICO – LEGENDA 8-12 
8.2.5 GRÁFICO DINÂMICO – RÓTULO DE DADOS 8-13 
8.2.6 GRÁFICO DINÂMICO – TABELA DE DADOS 8-13 
8.2.7 GRÁFICO DINÂMICO – FORMATAR 8-14 
8.2.8 GRÁFICO DINÂMICO – ANALISAR 8-15 
EXERCÍCIOS TABELA E GRÁFICO DINÂMICO 8-16 
REFERÊNCIAS 8-17 
 
 
Prof. Aldo Mendes 
 
 
 
8 - 2 
Tabela e Gráfico Dinâmico 
8 TABELA E GRÁFICO DINÂMICO 
 
Tabela e gráfico dinâmico os dois relatórios permitem que você tome decisões 
informadas sobre dados críticos de sua empresa. As seções a seguir fornecem uma 
visão geral dos relatórios de Tabela Dinâmica e de Gráfico Dinâmico.1 
 
8.1 Tabela Dinâmica 
Tabela e Gráfico Dinâmico são usados para a obtenção de 
cruzamento de informações em uma base de dados.
TABELA DINÂMICA
77
 
Um relatório de tabela dinâmica é um meio interativo de resumir rapidamente 
grandes quantidades de dados. Use um relatório de tabela dinâmica para analisar 
detalhadamente dados numéricos e responder perguntas inesperadas sobre seus 
dados.2 
Prof. Aldo Mendes 
 
 
 
8 - 3 
Tabela e Gráfico Dinâmico 
8.1.3 Layout – Tabela Dinâmica 
LAYOUT - TABELA DINÂMICA
1
2
3
78
 
Passo a passo 
Imagine a necessidade de obter o resultado total de vendas separado por 
departamento e dividido por seus vendedores. Isto pode até ser conseguido 
utilizando o conceito de ordenação, porém a visualização dos dados não será 
adequada.3 
1 – sendo assim, a partir de uma planilha existente, selecione o intervalo no qual 
você deseja analisar, na guia inserir na abas tabelas, clique em tabela dinâmica será 
apresentado a seguinte caixa de diálogo que é apresentada na figura 26: 
Prof. Aldo Mendes 
 
 
 
8 - 4 
Tabela e Gráfico Dinâmico 
 
 
Figura 1 Caixa de diálogo de tabela dinâmica 
2 - Dados de origem, neste caso, a partir de uma planilha. 
3- Os campos valores de origem para o resumo. 
4- O relatório de Tabela Dinâmica completo. 
8.1.1 Exemplo 2 – Tabela 
Abaixo mostra o resultado deste tipo de Seleção
EXEMPLO- TABELA DINÂMICA
79
 
Prof. Aldo Mendes 
 
 
 
8 - 5 
Tabela e Gráfico Dinâmico 
Quando você cria uma Tabela Dinâmica, o Excel exibe a Lista de Campos da Tabela 
Dinâmica de forma que você possa adicionar campos à Tabela Dinâmica, 
reorganizar e reposicioná-los conforme o necessário ou removê-los da Tabela 
Dinâmica. Por padrão, a Lista de Campos da Tabela Dinâmica exibe duas funções: 
• Uma seção de campo na parte superior para adicionar e remover campos 
da Tabela Dinâmica 
• Uma seção de layout na parte inferior para reorganizar e reposicionar os 
campos 
 
 
 
 
 
Figura 2 Lista de campos da tabela dinâmica 
 
Um relatório de tabela dinâmica são projetados especialmente para: 
• Consultar grandes quantidades de dados de várias maneiras amigáveis. 
• Subtotalizar e agregar dados numéricos, resumir dados por categorias e 
subcategorias, bem como criar cálculos e fórmulas personalizados. 
• Expandir e recolher níveis de dados para enfocar os resultados e fazer uma 
busca detalhada dos dados de resumo das áreas de seu interesse. 
• Mover linhas para colunas ou colunas para linhas (ou "dinamizar") para ver 
resumos diferentes dos dados de origem. 
• Filtrar, classificar, agrupar e formatar condicionalmente o subconjunto de 
dados mais útil e interessante para permitir que você se concentre nas 
informações desejadas. 
• Apresentar relatórios online ou impressos, concisos, atraentes e úteis.4 
 
 
Prof. Aldo Mendes 
 
 
 
8 - 6 
Tabela e Gráfico Dinâmico 
8.1.2 Design – Tabela Dinâmica 
TABELA DINÂMICA - DESIGN
80
 
No MS Excel 2013 você poderá personalizar sua tabela dinâmica, na guia das 
ferramentas da tabela dinâmica clique em design, e escolha uma tabela com cores e 
modelos diferentes. 
 
 
Prof. Aldo Mendes 
 
 
 
8 - 7 
Tabela e Gráfico Dinâmico 
8.2 Gráfico Dinâmico 
GRÁFICO DINÂMICO
81
 
Inserindo gráfico dinâmico passo a passo 
1- Selecione o intervalo para elaboração do gráfico dinâmico 
2- Clique na guia Inserir e na aba tabela, e então irá apresentar: 
Tabela Dinâmica e gráfico dinâmico, como mostra a figura 28: 
 
Figura 3 Localização do Gráfico Dinâmico 
3- Automaticamente abrirá uma janela para criar a tabela dinâmica com gráfico 
dinâmico, marque a opção selecionar uma tabela de intervalo, neste exemplo já 
selecionamos anteriormente e marque a opção Nova Planilha. Clique no botão 
OK. 
Prof. Aldo Mendes 
 
 
 
8 - 8 
Tabela e Gráfico Dinâmico 
 
Figura 4 Caixa de diálogo do gráfico dinâmico 
4- Logo após aparecerá uma caixa de diálogo para inserir os campos, a partir das 
informações da lista de campos 
 
Figura 5 Caixa de diálogo para inserir os campos no gráfico dinâmico 
 
 
 
Prof. Aldo Mendes 
 
 
 
8 - 9 
Tabela e Gráfico Dinâmico 
5- Lista de campos como mostra a figura 31: 
 
Figura 6 Apresentação da lista de campos 
A partir dos campos marcados aparecerá no gráfico 
Prof. Aldo Mendes 
 
 
 
8 - 10 
Tabela e Gráfico Dinâmico 
8.2.1 Gráfico Dinâmico - Campos de exibição 
GRÁFICO DINÂMICO
Para construir um Gráfico dinâmico, escolha os campos na lista de 
campos da Tabela Dinâmica.
82
 
Este exemplo mostra um gráfico onde no eixo x mostra os vendedores, podendo 
variar a pesquisa deste banco de dados: 
 
Figura 7 Exemplo de gráfico dinâmico 
Além de oferecer uma análise rápida e eficiente dos dados, o gráfico dinâmico 
permite trocar os campos de lugares. Sendo assim arrastamos o campo vendedor 
para nosso gráfico, onde você poderá mudar esta análise para o campo fornecedor. 
 
Prof. Aldo Mendes 
 
 
 
8 - 11 
Tabela e Gráfico Dinâmico 
8.2.2 Gráfico dinâmico- Design 
GRÁFICO DINÂMICO - DESIGN
83
 
O gráfico dinâmico poderá ser personalizado da forma de acordo com as 
necessidades do seu dia a dia. Neste exemplo acima você poderá incluir um design 
para seu gráfico. 
8.2.3 Gráfico Dinâmico – Layout 
GRÁFICO DINÂMICO - DESIGN
83
 
Prof. Aldo Mendes 
 
 
 
8 - 12 
Tabela e Gráfico Dinâmico 
Ao clicar no botão LAYOUT, você poderá distribuir algumas personalizações como 
no primeiro exemplo de inserindo Linhas no gráfico, ajuda na indicação dos valores. 
8.2.4 Gráfico Dinâmico – Legenda 
GRÁFICO DINÂMICO - LAYOUT
85
 
A Legenda em um gráfico é essencial, pois é nela que poderá ser baseada uma 
consulta, ou localização dos dados, lembre-se que a legenda e outros aplicativos do 
Layout é opcional. 
Prof. Aldo Mendes 
 
 
 
8 - 13 
Tabela e Gráfico Dinâmico 
8.2.5 Gráfico Dinâmico – Rótulo de dados 
GRÁFICO DINÂMICO - LAYOUT
86
 
Clique em Rótulo de Dados do campo a ser adicionado na tabela, determinando o 
seu posicionamento. 
8.2.6 Gráfico Dinâmico – Tabela de dados 
GRÁFICO DINÂMICO - LAYOUT