Excel avançado

Prévia do material em texto

Excel 2016 – Avançado 
 
 
 
CAPÍTULO 1 
 FUNÇÕES 
MATEMÁTICAS, 
 
 ESTATÍSTICAS, LÓGICAS 
E 
 
 MANIPULAÇÃO DE 
DATAS 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Utilizar funções matemáticas do Excel. 
 Utilizar funções estatísticas do Excel. 
 Utilizar funções lógicas do Excel. 
 Utilizar funções de data e hora do Excel. 
 Formatar datas. 
 Efetuar cálculos com datas e horas. 
 
 
Excel 2016 – Avançado 
 
3 
 
 
FUNÇÕES MATEMÁTICAS 
O Excel 2016 oferece ferramentas avançadas que possibilitam a análise, o gerenciamento e o 
compartilhamento de informações de tal forma que a tomada de decisões para uma situação específica se 
torne mais eficiente. 
É nesse contexto que neste curso você aprenderá algumas dessas funções e ferramentas, para que possa 
utilizar em suas planilhas aprimorando sua capacidade de analisar conjuntos de dados. 
 
 
 
Objetivos : • Utilizar as funções matemáticas do Excel 2016. 
Tarefas : • Abrir a pasta Vendas trimestre 1.xlsx. 
 • Somar o número de itens vendidos. 
 • Calcular o preço total de cada produto. 
 • Somar o valor total de vendas. 
 • Somar o valor vendido por filial. 
 • Somar o valor vendido por filial e por mês. 
 
Função SOMA 
A função SOMA tem como objetivo somar os valores numéricos contidos em uma faixa de células. 
• Sintaxe: SOMA(núm1;núm2;.....) 
• Argumentos: os argumentos núm1, núm2,..... correspondem a valores, endereços de células 
ou faixa de células que se deseja somar. 
 
1. Abra a pasta “Vendas trimestre 1.xlsx”. 
Atividade 1 – Utilizando funções matemáticas 
Excel 2016 – Avançado 
 
4 
 
 
 
2. Você vai calcular o número de itens vendidos pelas filiais no 1º trimestre de 2016. Clique na célula D19 
e digite: 
= SOMA( 
 
3. Com o mouse clicado selecione a região D4:D17 . 
4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: 
= SOMA(D4:D17) 
 
 
 
 
Função MULT (PRODUTO) 
A função PRODUTO tem como objetivo efetuar o produto entre os valores numéricos contidos em uma faixa 
de células. 
• Sintaxe: MULT(núm1;núm2;.....) 
• Argumentos: os argumentos núm1, núm2,..... correspondem a valores, endereços de células ou 
faixa de células que desejamos multiplicar. 
 
1. Você vai calcular o valor total de cada item vendido pelas filiais no 1º trimestre de 2016. Clique na célula 
F4 e digite: 
= MULT( 
 
Uma outra forma de selecionar a função SOMA é através da ferramenta Ʃ que se encontra na 
guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de 
selecionar a função. 
Excel 2016 – Avançado 
 
5 
 
2. Clique na célula D4, digite ; (ponto e vírgula) e clique na célula E4. 
3. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: 
= MULT(D4:E4) 
 
No caso da atividade, o argumento D4:E4 refere-se ao intervalo de células D4 até E4, ou seja, serão 
considerados todos os valores entre essas duas células. Você pode, no entanto, substituir o argumento por 
D4;E4, uma vez que não existe nenhuma célula entre D4 e E4. 
 
4. Copie a fórmula para todos os itens da planilha. 
 
Função SOMARPRODUTO 
A função SOMARPRODUTO tem como objetivo multiplicar os componentes das faixas de dados fornecidas 
e retornar a soma destes produtos. 
• Sintaxe: SOMAPRODUTO(faixa1;faixa2;.....) 
• Argumentos: os argumentos faixa1, faixa2,..... correspondem a faixa de células que desejamos 
multiplicar e depois somar. 
 
1. Você vai calcular o valor total das vendas efetuadas pelas filiais no 1º trimestre de 2016. Na verdade 
terá de somar os resultados da multiplicação de quantidade por preço unitário de cada produto. Clique 
na célula D20 e digite: 
= SOMARPRODUTO( 
 
2. Com o mouse selecione a faixa D4:D17 
3. Digite ; (ponto e vírgula). 
4. Com o mouse selecione a faixa E4:E17. 
 
Você poderia indicar a fórmula selecionando a faixa D4:D17 e, pressionando a tecla CTRL, selecionar a faixa 
E4:E17. 
5. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: 
=SOMARPRODUTO(D4:D17;E4:E17) 
 
A operação realizada corresponde a somar a coluna Valor Total. 
 
Função SOMASE 
A função SOMASE tem como objetivo efetuar a soma dos valores indicados de acordo com um determinado 
critério ou condição. 
• Sintaxe: SOMASE(intervalo; critérios; intervalo_soma) 
• Argumentos: 
Excel 2016 – Avançado 
 
6 
 
o Intervalo: intervalo de células onde o critério será procurado; 
o Critério: condição para definir quais valores serão somados. Esses critérios podem ser número, 
expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”. O critério deve 
ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores 
relacionais (>, <, >=, <=, <>); 
o Intervalo_soma: intervalo de valores que serão somados. Se este argumento for omitido, serão 
somadas as células especificadas no argumento intervalo (as mesmas células às quais os critérios 
são aplicados). 
 
6. Você vai calcular o valor vendido pela filial de Vila Mariana. Clique na célula D21 e digite: 
= SOMASE( 
 
7. Com o mouse selecione a faixa A4:A17 
8. Digite ; (ponto e vírgula). 
9. Digite “Vila Mariana”. 
10. Digite ; (ponto e vírgula). 
11. Com o mouse selecione a faixa F4:F17. 
12. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: 
=SOMASE(A4:A17;"Vila Mariana";F4:F17) 
 
13. Na célula D22 calcule o valor vendido pela filial do Brooklin. Digite a fórmula: 
=SOMASE(A4:A17;"Brooklin";F4:F17) 
 
14. Na célula D23 calcule o valor das vendas no mês de Janeiro. Observe que o critério “Janeiro” deve ser 
procurado na faixa B4:B17. 
=SOMASE(B4:B17;"Janeiro";F4:F17) 
 
15. Nas células D24 e D25 calcule os valores vendidos para os meses de Fevereiro e Março. 
 
Função SOMASES 
A função SOMASES tem como objetivo efetuar a soma em um intervalo atendendo a vários critérios, que 
serão combinados. 
• Sintaxe: o SOMASES(intervalo_soma; intervalo_critério1; critério1; intervalo_critério2; 
critério2;......) 
• Argumentos: 
o Intervalo_soma: intervalo de valores que serão somados. 
Excel 2016 – Avançado 
 
7 
 
o Intervalo_critério1: intervalo de células onde o critério1 será procurado; o Critério1: 
condição para definir quais valores serão somados. Esses critérios podem ser número, 
expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”, F5. O critério 
deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar 
operadores relacionais (>, <, >=, <=, <>); 
o Intervalo_critério2: intervalo de células onde o critério2 será procurado; o Critério2: 
condição para definir quais valores serão somados. Esses critérios podem ser número, 
expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”, F5. O critério 
deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar 
operadores relacionais (>, <, >=, <=, <>). 
 
Você pode ter até 127 pares de intervalos/critérios. 
1. Você vai calcular o valor vendido pela filial de Vila Mariana no mês de Fevereiro. Clique na célula D26 e 
digite: 
= SOMASES( 
 
2. Com o mouse selecione a faixa F4:F17 
3. Digite ; (ponto e vírgula). 
4. Com o mouse selecione a faixa A4:A17. 
5. Digite ; (ponto e vírgula). 
6. Digite “Vila Mariana”. 
7. Digite ; (ponto e vírgula). 
8. Com o mouse selecione a faixa B4:B17. 
9. Digite ; (ponto e vírgula). 
10. Digite “Fevereiro”. 
11. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: 
=SOMASES(F4:F17;A4:A17;"Vila Mariana";B4:B17;"Fevereiro") 
 
12. Na célula D27 calcule o valor vendido pela filial do Brooklin no mês de Janeiro. 
Excel 2016 – Avançado 
 
8 
 
 
 
13. Feche a pasta salvando as modificações.Excel 2016 – Avançado 
 
9 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
10 
 
 
 
 
Objetivos : • Utilizar as funções estatísticas do Excel 2016. 
Tarefas : • Abrir a pasta Prêmio.xlsx. 
 • Calcular a maior e a menor venda entre os vendedores. 
 • Calcular a média de vendas dos vendedores. 
 • Calcular o total de vendedores. 
 • Calcular o número de vendedores que não efetuaram vendas. 
 • Calcular o número de vendedores que efetuaram vendas. 
 • Calcular o número de vendedores premiados com o primeiro lugar em vendas. 
 • Calcular o total de vendedores por departamento. 
 • Calcular o número de vendedores premiados com o primeiro lugar em vendas por 
departamento. 
 • Calcular a média de vendas por departamento. 
 • Calcular a média de vendas pelos vendedores não premiados com o primeiro 
lugar por departamento. 
 
Uma loja possui três departamentos de vendas: Informática, Eletrodomésticos e Livraria. Cada 
departamento tem a sua equipe de vendas. Com a finalidade de motivar os vendedores, a loja resolveu 
distribuir um prêmio no valor de R$ 450.000,00 para o(s) vendedor(es) que ficou(aram) em primeiro lugar 
no volume de vendas. 
O objetivo é efetuar os seguintes controles: 
• Mostrar o ranking de vendas, ou seja, calcular a colocação de cada um levando em consideração a 
venda individual em relação ao conjunto de vendas dos vendedores. 
• Mostrar a maior e a menor venda efetuada considerando todos os departamentos; 
• Calcular a média de vendas considerando todos os departamentos; 
• Mostrar o número de vendedores da loja, o número de vendedores que não efetuaram vendas, o 
número de vendedores que efetuaram vendas e quantos ficaram em primeiro lugar em vendas; 
• Por departamento, calcular o número de vendedores, quantos ficaram em primeiro lugar em 
vendas, calcular a média de vendas e a média de vendas dos vendedores não ganhadores do prêmio. 
 
Para resolver essa situação você vai trabalhar com a pasta Prêmio.xlsx. Nessa pasta você encontrará duas 
planilhas: VENDAS e RELATÓRIO. 
Atividade 2 – Utilizando Funções Estatísticas 
Excel 2016 – Avançado 
 
11 
 
 
Função MÁXIMO 
Essa função retorna o valor máximo entre um conjunto de valores numéricos informado. 
• Sintaxe: MÁXIMO(núm1;núm2;.....) 
• Argumentos: os argumentos núm1, núm2,..... correspondem aos valores numéricos cujo valor 
máximo queremos determinar. Os argumentos podem ser números, nomes, matrizes ou referências 
que contenham números. Pode-se informar de 1 até 255 valores numéricos. 
 
1. Abra a pasta Prêmio.xlsx. 
2. Selecione a planilha RELATÓRIO e clique na célula B2. Nessa célula você deve calcular o valor máximo 
das vendas que estão na faixa C3:C22 da planilha VENDAS. 
3. Digite: 
= MÁXIMO( 
 
4. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 
5. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: 
=MÁXIMO(VENDAS!C3:C22) 
 
 
 
Excel 2016 – Avançado 
 
12 
 
 
Observe que, utilizando a faixa C3:C22 da planilha VENDAS e como a fórmula está sendo colocada na 
planilha RELATÓRIO, é colocado o nome da planilha seguido pelo ponto de exclamação na identificação da 
região informada. 
 
 
 
 
Função MÍN 
Essa função retorna o valor mínimo entre um conjunto de valores numéricos informado. 
• Sintaxe: MÍN(núm1;núm2;.....) 
• Argumentos: os argumentos núm1, núm2,..... correspondem aos valores numéricos cujo valor 
mínimo queremos determinar. Os argumentos podem ser números, nomes, matrizes ou referências 
que contenham números. Pode-se informar de 1 até 255 valores numéricos. 
 
1. Selecione a planilha RELATÓRIO e clique na célula B3. Nessa célula você vai calcular o valor mínimo das 
vendas que estão na faixa C3:C22 da planilha VENDAS. 
2. Digite: 
= MÍN( 
 
3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 
4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: 
=MÍN(VENDAS!C3:C22) 
 
 
 
 
Função MÉDIA 
Essa função retorna a média aritmética entre um conjunto de valores numéricos informado. 
• Sintaxe: MÉDIA(núm1;núm2;.....) 
• Argumentos: os argumentos núm1, núm2,..... correspondem aos valores numéricos para os quais 
desejamos calcular a média aritmética. Os argumentos podem ser números, nomes, matrizes ou 
referências que contenham números. Pode-se informar de 1 até 255 valores numéricos. 
 
1. Selecione a planilha RELATÓRIO e clique na célula B4. Nessa célula você vai calcular a média aritmética 
das vendas que estão na faixa C3:C22 da planilha VENDAS. 
2. Digite: 
Outra forma de selecionar a função MÁXIMO é através da ferramenta Ʃ que se encontra na guia 
Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar 
a função. 
Outra forma de selecionar a função MÍN é através da ferramenta Ʃ que se encontra na guia 
Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar 
a função. 
Excel 2016 – Avançado 
 
13 
 
= MÉDIA( 
 
3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 
4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: 
=MÉDIA(VENDAS!C3:C22) 
 
 
 
 
Função ORDEM.EQ 
Essa função retorna a posição de um número em uma lista de números. • Sintaxe: ORDEM.EQ(número; 
referência; [ordem]) 
• Argumentos: 
o Número: número cuja posição se quer encontrar em uma lista de números; o Referência: 
lista de números onde o número informado será posicionado; o Ordem: argumento opcional. 
Se for 0 ou omitido, o Excel posicionará o número considerando a lista de números ordenada 
de forma descendente; se for qualquer valor diferente de zero, o Excel posicionará o número 
considerando a lista de números ordenada de forma ascendente. 
 
A presença de números com a mesma posição irá interferir na ordem dos números subsequentes Por 
exemplo, em uma lista de números inteiros classificados em ordem crescente, se o número 5 aparecer duas 
vezes e tiver uma ordem de 2, então 6 teria uma ordem de 4 e nenhum número teria a ordem de 3. 
5. Selecione a planilha VENDAS e clique na célula D3. 
6. Você vai utilizar a função ORDEM.EQ para posicionar o valor da venda da célula C3 dentro da faixa de 
vendas C3:C22. Digite a fórmula: 
= ORDEM.EQ(C3;$C$3:$C$22) 
 
Observe que a faixa de células C3:C22 foi fixada na fórmula, pois na cópia para as células de baixo essa faixa 
deve continuar sendo a mesma. 
7. Pressione a tecla ENTER e copie a fórmula até a célula D22. 
 
 
 
 
Função CONT.VALORES 
Essa função conta o número de células não vazias em um intervalo informado. O conjunto de células pode 
conter qualquer tipo de informação. 
Outra forma de selecionar a função MÉDIA é através da ferramenta Ʃ que se encontra na guia 
Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar 
a função. 
Não se preocupe com as informações de erro que aparecem nas células D8 e D17. Esse erro é 
devido a não existir um valor de venda nas células C8 e C17. Mais adiante, nesse mesmo 
capítulo, esse erro será tratado de forma que não apareça a mensagem. 
Excel 2016 – Avançado 
 
14 
 
• Sintaxe: CONT.VALORES(intervalo1; [intervalo2]; ....) 
• Argumentos: o Intervalo1;[intervalo2];....: faixas de células que devem ser 
contadas. 
 
1. Você vai contar o número total de vendedores da loja. Para isso conte quantas células contém os nomes 
dos vendedores. Selecione a planilha RELATÓRIO e clique na célula B7. 
2. Digite: 
=CONT.VALORES( 
 
3. Como os nomes dos vendedores estão na planilha VENDAS , selecione a planilha e, com o mouse clicado, 
selecione a faixa de células A3:A22. 
4. Pressionea tecla ENTER e você terá a fórmula: 
=CONT.VALORES(VENDAS!A3:A22) 
 
 
Função CONTAR.VAZIO 
Essa função conta o número de células vazias em um intervalo informado. Células que contenham 
valor nulo não serão contadas. 
• Sintaxe: CONTAR.VAZIO(intervalo) 
• Argumento: o argumento intervalo representa a faixa de células que devem ser contadas. 
 
1. Agora você vai contar quantos vendedores não efetuaram vendas, ou seja, quantas são as células da 
coluna VALOR DA VENDA que estão vazias. Selecione a planilha RELATÓRIO e clique na célula B8. 
2. Digite: 
=CONTAR.VAZIO( 
 
3. Como os valores das vendas estão na planilha VENDAS , selecione a planilha e, com o mouse clicado, 
selecione a faixa de células C3:C22. 
4. Pressione a tecla ENTER e você terá a fórmula: 
=CONTAR.VAZIO(VENDAS!C3:C22) 
 
 
Função CONT.NÚM 
Essa função conta o número de células que contêm números em um intervalo informado. Serão contadas 
as células que contenham números, datas ou números escritos entre aspas. • Sintaxe: 
CONT.NÚM(intervalo1; [intervalo2]; ....) 
• Argumentos: o Intervalo1;[intervalo2];....: faixas de células que devem ser 
contadas. 
Excel 2016 – Avançado 
 
15 
 
 
1. Agora você vai contar quantos vendedores efetuaram vendas, ou seja, quantas são as células da coluna 
VALOR DA VENDA que estão preenchidas com números. Selecione a planilha RELATÓRIO e clique na 
célula B9. 
2. Digite: 
=CONT.NÚM( 
 
3. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, 
selecione a faixa de células C3:C22. 
4. Pressione a tecla ENTER e você terá a fórmula: 
=CONT.NÚM(VENDAS!C3:C22) 
 
 
Função CONT.SE 
Essa função conta o número de ocorrências de uma determinada condição em um intervalo de células 
informado. 
• Sintaxe: CONT.SE(intervalo; critério) 
• Argumentos: 
o Intervalo: intervalo de células que será considerado; 
o Critério: condição que será procurada no intervalo de células; 
 
1. Você vai contar quantos são os vendedores ganhadores do prêmio, ou seja, aqueles que se encontram 
em primeiro lugar na colocação de vendas. Selecione a planilha RELATÓRIO e clique na célula B10. 
2. Digite: 
=CONT.SE( 
 
3. Como você quer verificar se o vendedor está na classificação 1, o intervalo de células considerado deve 
ser o da coluna COLOCAÇÃO da planilha VENDAS. Selecione a planilha VENDAS e, com o mouse clicado, 
selecione o intervalo D3:D22. 
4. Digite ; (ponto e vírgula). 
5. Digite 1 
6. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: 
= CONT.SE(VENDAS!D3:D22;1) 
 
7. Agora você vai contar quantos vendedores são do departamento de Informática. Para isso você deve 
contar o número de ocorrências da palavra Informática no intervalo de células B3:B22 da planilha 
VENDAS. Clique na célula E2 da planilha RELATÓRIO. 
8. Digite a fórmula: 
Excel 2016 – Avançado 
 
16 
 
= CONT.SE(VENDAS!B3:B22;"Informática") 
 
Como a palavra Informática, que é o critério para a função, está na célula D1 da planilha RELATÓRIO, a 
fórmula também poderia ser escrita da seguinte forma: 
= CONT.SE(VENDAS!B3:B22;RELATÓRIO!D1) 
 
 
Função CONT.SES 
Essa funçã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 verdadeiros. 
• Sintaxe: CONT.SES(intervalo1; critério1;intervalo2;critério2;....) 
• Argumentos: o Intervalo1: intervalo de células onde será procurado o critério1; 
o Critério1: condição que será procurada no intervalo de células intervalo1; o 
Intervalo2: intervalo de células onde será procurado o critério2; o Critério2: 
condição que será procurada no intervalo de células intervalo2; 
 
 
 
1. Você vai calcular quantos são os vendedores ganhadores do prêmio do departamento de Informática. 
Veja que agora você precisa considerar duas condições: o vendedor está classificado como primeiro e 
trabalha no departamento de Informática. Selecione a planilha RELATÓRIO e clique na célula E3. 
2. Digite: 
=CONT.SES( 
 
3. Você deve procurar pela palavra “Informática”. Selecione a planilha VENDAS e s elecione o intervalo 
B3:B22. Digite ; (ponto e vírgula) e, na planilha RELATÓRIO, clique na célula D1. Digite ; ( ponto e vírgula). 
4. Agora você vai procurar pelo número 1 no intervalo D3:D22 ( estamos procurando pelos primeiros 
colocados). Selecione a planilha VENDAS e o intervalo D3:D22. Digite ; (ponto e vírgula) e digite 1 . 
Pressione a tecla ENTER e teremos a seguinte fórmula: 
= CONT.SES(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;1) 
 
 
Função MÉDIASE 
A função MÉDIASE tem como objetivo calcular a média aritmética dos valores indicados de acordo com um 
determinado critério ou condição. 
• Sintaxe: MÉDIASE(intervalo; critérios; intervalo_média) 
• Argumentos: o Intervalo: intervalo de células onde o critério será procurado; o Critério: 
condição para definir quais valores serão considerados para o cálculo da média; o 
Intervalo_média: intervalo de células que será considerado para calcular a média. 
Pode-se ter até 127 pares de intervalos/critérios. 
Excel 2016 – Avançado 
 
17 
 
 
1. Calcule agora a média de vendas para o departamento de Informática. Selecione a célula E4 da planilha 
RELATÓRIO e digite a fórmula a seguir. Utilize o método de selecionar com o mouse, como foi feito para 
as fórmulas anteriores, para construir a expressão. 
= MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!C3:C22) 
 
Função MÉDIASES 
A função MÉDIASES tem como objetivo calcular a média aritmética em um intervalo atendendo a vários 
critérios. 
• Sintaxe: 
• MÉDIASES(intervalo_média; intervalo_critério1; critério1; intervalo_critério2; critério2;......) 
• Argumentos: 
o Intervalo_média: intervalo de valores que serão considerados para o cálculo da média. o 
Intervalo_critério1: intervalo de células onde o critério1 será procurado; o Critério1: 
condição para definir quais valores serão considerados para o cálculo da média; o 
Intervalo_critério2: intervalo de células onde o critério2 será procurado; o Critério2: 
condição para definir quais valores serão considerados para o cálculo da média. 
 
1. Calcular a média aritmética das vendas dos vendedores do departamento de Informática e que não são 
ganhadores do prêmio. Na célula E5 da planilha RELATÓRIO digite a fórmula: 
= MÉDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;"<>1") 
 
Os critérios informados nessa fórmula são os seguintes: 
• VENDAS!C3:C22 – intervalo de valores que serão considerados no cálculo da média; 
• VENDAS!B3:B22 – intervalo onde será procurado o primeiro critério (Departamento); 
• RELATÓRIO!D1 – endereço da célula que contém o primeiro critério (Informática); 
• VENDAS!D3:D22 – intervalo onde será procurado o segundo critério (Colocação); 
• “<>1” – segundo critério, vendedores que não estão na posição 1. 
 
2. Preencha, utilizando o mesmo raciocínio utilizado para as informações do departamento de 
Informática, os quadros relativos aos departamentos de Eletrodomésticos e Livraria da planilha 
RELATÓRIOS. 
3. No final você deverá ter o seguinte para as planilhas VENDAS e RELATÓRIO: 
Excel 2016 – Avançado 
 
18 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
19 
 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
20 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Objetivos : • Utilizar as funções lógicas do Excel 2016. 
 
Tarefas : 
• Calcular o valor do prêmio para cada um dos vendedores ganhadores. 
• Atribuir um conceito de acordo com o valor das vendas de cada vendedor. 
• Distribuir um prêmio extra entre os vendedores que não alcançaram o primeiro lugar de 
acordo com o valor de suas vendas. 
• Tratar adequadamenteos erros que possam ocorrer em planilhas. 
 
Para completar o preenchimento da planilha VENDAS da pasta Prêmio.xlsx, você ainda tem algumas tarefas 
a realizar: 
• Distribuir o prêmio que a empresa oferece aos vendedores colocados em primeiro lugar; 
• Atribuir um conceito a cada vendedor de acordo com o seu volume de vendas, comparando-
o com a meta a ser atingida individualmente; 
• Distribuir um prêmio extra aos vendedores que não se classificaram em primeiro lugar, mas 
que também se destacaram de acordo com alguns critérios estabelecidos. 
 
Atividade 3 – Utilizando Funções Lógicas 
Excel 2016 – Avançado 
 
21 
 
Função SE 
Essa função tem como objetivo efetuar testes condicionais com valores e fórmulas permitindo a escolha do 
que fazer de acordo com o resultado do teste, que pode ser Falso ou Verdadeiro. 
• Sintaxe: 
• o SE(teste_lógico; valor_se_verdadeiro; valor_se_falso) 
• Argumentos: 
o Teste_lógico: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; 
o Valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for Verdadeiro; 
o Valor_se_falso: o que fazer se o resultado do teste_lógico for Falso. 
 
1. Selecione a planilha VENDAS da pasta Prêmios.xlsx. 
 
Você deve verificar na célula E3 se o vendedor está em primeiro lugar na classificação e, se estiver, calcular 
e mostrar o seu prêmio. Se o vendedor não estiver em primeiro lugar, o valor que deve ser colocado na 
célula é zero. Lembre-se que o prêmio total deve ser dividido igualmente entre os vendedores ganhadores. 
O raciocínio que vamos seguir é o seguinte: 
a) SE classificação = 1 
b) ENTÃO dividir o valor do prêmio pelo número de ganhadores e mostrar na célula 
c) SENÃO mostrar o valor 0 na célula 
 
2. Clique na célula E3 e digite o seguinte: 
 = SE(D3=1;$B$1/RELATÓRIO!$B$10;0) 
 
Observe que na célula D3 está a classificação do vendedor, na célula B1 está valor do prêmio total e na 
célula B10 da planilha RELATÓRIO está o número de ganhadores. 
3. Copie a fórmula para todos os vendedores. Não se preocupe com os erros apontados nas células E8 e 
E17. Mais adiante você vai modificar as fórmulas para tratar adequadamente esses erros. 
4. Agora você vai colocar uma mensagem para cada vendedor de acordo com os seguintes critérios: 
CONCEITO CONDIÇÃO 
EXCELENTE Colocação do vendedor igual a 1 
ÓTIMO Valor das vendas do vendedor maior do que a meta 
BOM Valor das vendas do vendedor igual à meta 
PODE MELHORAR Nenhuma das condições anteriores 
 
Excel 2016 – Avançado 
 
22 
 
Observe que existem quatro condições a serem verificadas. Isso não será possível através de uma única 
função SE. Nesse caso, você terá que aninhar funções SE para resolver o problema. Até 64 funções SE podem 
ser aninhadas no Excel 2016. 
O raciocínio que você deve seguir é o seguinte: 
 SE colocação do vendedor = 1 
 ENTÃO atribuir conceito “EXCELENTE” 
 SENÃO SE valor das vendas maior do que a meta 
 ENTÃO atribuir conceito “ÓTIMO” 
 SENÃO SE valor das vendas igual à meta 
 ENTÃO atribuir conceito “BOM” 
 SENÃO atribuir conceito “PODE MELHORAR” 
 
5. Clique na célula F3 e digite a seguinte fórmula: 
 = SE(D3=1;"EXCELENTE";SE(C3>$D$1;"ÓTIMO";SE(C3=$D$1;"BOM";"PODE MELHORAR"))) 
 
6. Copie a fórmula para todos os vendedores. 
7. Feche a pasta Premio.xlsx. 
 
 
Função E 
Essa função retorna o valor Verdadeiro se todos os seus argumentos forem verdadeiros. 
• Sintaxe: 
o E(lógico1; lógico2;...) 
• Argumentos: 
o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o 
Lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado. 
Em uma função E você pode colocar até 255 argumentos, ou seja, pode-se ter até 255 condições lógicas 
para avaliar. 
 
 
Função OU 
Essa função retorna o valor Verdadeiro se pelo menos um de seus argumentos for verdadeiro. 
• Sintaxe: o OU(lógico1; 
lógico2;...) 
• Argumentos: 
o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado; o 
Lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado. 
Excel 2016 – Avançado 
 
23 
 
 
Em uma função OU você pode colocar até 255 argumentos, ou seja, pode-se ter até 255 condições lógicas 
para avaliar. 
As funções E e OU são normalmente utilizadas para dar mais flexibilidade a outras funções que executam 
testes lógicos, como por exemplo, em conjunto com a função SE. 
1. Abra a pasta Estado civil.xlsx. Você vai verificar a diferença dos resultados entre as funções E e OU. 
 
 
2. Clique na célula D3. Nela deve-se mostrar o valor VERDADEIRO se a pessoa for solteira e sua idade for 
maior ou igual a 22 anos. Caso contrário, deverá ser mostrado o valor FALSO. 
3. Digite a fórmula: 
= E(B3="Solteiro(a)";C3>=22) 
 
4. Copie a fórmula para todas as pessoas. 
5. Clique na célula E3. Nela deve-se mostrar o valor VERDADEIRO se a pessoa for solteira ou se sua idade 
for maior ou igual a 22 anos. Caso contrário, deverá ser mostrado o valor FALSO. 
6. Digite a fórmula: 
= OU(B3="Solteiro(a)";C3>=22) 
 
7. Copie a fórmula para todas as pessoas. 
Excel 2016 – Avançado 
 
24 
 
 
 
Note que no caso da função E, o resultado só é verdadeiro se as duas condições forem verdadeiras. No caso 
da função OU, o resultado é verdadeiro se pelo menos uma das condições for verdadeira, e somente será 
falso se as duas condições forem falsas. 
8. Feche a pasta Estado civil.xlsx e abra a pasta Prêmio.xlsx. 
9. A empresa resolveu premiar também os funcionários que obtiveram conceito ÓTIMO com uma viagem 
para Natal (RN) e os funcionários que venderam menos ou o valor da meta, mas que venderam mais do 
que R$ 300.000,00, com um jantar. Considere o seguinte raciocínio: 
 
SE conceito = ÓTIMO 
ENTÃO prêmio extra = Viagem para Natal 
SENÃO SE vendas <= meta E vendas > 300000 
 ENTÃO prêmio extra = Jantar 
 
10. Clique na célula G3 e digite o seguinte: 
 
= SE(F3="ÓTIMO";"Viagem para Natal";SE(E(C3<=$D$1;C3>300000);"Jantar";"")) 
 
Observe que se nenhuma das condições for verdadeira, o valor “” será colocado na célula, ou seja, a célula 
ficará vazia para evitar que seja mostrada a mensagem FALSO. 
11. Copie a fórmula para todos os vendedores. 
 
 
Função SEERRO 
Essa função tem como objetivo retornar um valor especificado se uma fórmula gerar um erro. Se não existir 
erro, a função retorna o resultado da fórmula. 
Excel 2016 – Avançado 
 
25 
 
• Sintaxe: o SEERRO(fórmula; 
valor_se_erro) 
• Argumentos: o Fórmula: fórmula que se 
deseja verificar quanto a erros; o 
Lógico2;...: valor a ser retornado se a 
fórmula gerar um erro. 
 
Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!. 
12. As fórmulas da coluna COLOCAÇÃO estão gerando erros nos casos em que a célula correspondente a 
venda está vazia. Como é um fato que pode acontecer, é aconselhável não mostrar a mensagem de erro 
gerada pelo Excel, mas sim armazenar na célula um valor mais adequado. Nesse caso, se existir o erro 
na célula, você deve deixá-la vazia, ou seja, armazenar “”. Clique na célula D3 e modifique a fórmula 
para: 
=SEERRO(ORDEM.EQ(C3;$C$3:$C$22);"") 
 
Observe que todos os erros da planilha foram solucionados. 
É necessário saber qual valor deve-se colocar na célula nos casos de erro para não ocasionar novos erros. 
 
13. Feche a pasta Prêmio.xlsx. 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
26 
 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
27 
 
 
 
Objetivos : • Formatar datas. 
 • Utilizar funções de datas e horas. 
 • Efetuar cálculos com datas e horas. 
Tarefas : • Formatar uma datapara que seja mostrado o dia da semana. 
 • Formatar uma data para que seja mostrado o mês. 
 • Formatar uma data para que seja mostrado o dia, mês e ano. 
 • Utilizar as funções de data para mostrar a data de hoje, o ano, dia e mês de uma 
data, o número do dia da semana. 
 • Projetar dias úteis a partir de uma data. 
 • Calcular o número de dias úteis entre duas datas. 
 • Projetar dias corridos a partir de uma data. 
 • Calcular o número de dias corridos entre duas datas. 
 • Calcular o número de horas e minutos entre dois horários. 
 • Aplicar formatação condicional nas planilhas. 
 
Em muitas planilhas é necessário o trabalho com datas e horas, efetuando cálculos e projeções. O Excel 
2016 oferece várias funções e opções de formatação que podem ser aplicadas para as mais variadas 
necessidades. 
 
Formatação de DATAS 
1. Abra a pasta “Datas Formato.xlsx “. 
 
 
Atividade 4 – Manipulando Datas e Horas 
Excel 2016 – Avançado 
 
28 
 
2. Copie o conteúdo da coluna A para as demais colunas. 
3. Na coluna B você vai mostrar o número serial que representa a data. Esse número é o número de dias 
transcorridos entre a data 01/01/1900 e a data considerada. Selecione o intervalo B2:B32. 
 
5. Selecione Geral. 
6. Na coluna C você vai mostrar o nome do dia da semana da data armazenada. Selecione o intervalo 
C2:C32. 
 
8. Na guia Número, selecione a categoria Personalizado. 
9. Apague o que está no campo Tipo e digite dddd. 
4. Na guia Página Inicial , grupo Número , clique na seta do comando Formato de número. 
 
 
7. Clique na seta do grupo Número da guia Página Inicial. 
 
 
Excel 2016 – Avançado 
 
29 
 
 
O que é informado nesse campo determina o formato do dado selecionado. Para o caso de datas, 
considerando o dia da data, temos os seguintes formatos personalizados: 
• D dias que são formados por um algarismo são exibidos com um algarismo. 
• DD dias que são formados por um algarismo são exibidos com dois algarismos, tendo o zero 
como inicial. 
• DDD exibe o nome do dia de forma abreviada, através das três primeiras letras. 
• DDDD exibe o nome do dia por extenso. 
 
10. Clique em OK. 
11. Agora você vai exibir na coluna D o nome do mês por extenso. Selecione o intervalo D2:D32. 
12. Clique na seta do grupo Número da guia Página Inicial. 
13. Na guia Número, selecione a categoria Personalizado. 
14. Apague o que está no campo Tipo e digite mmmm. 
 
Para o caso de datas, considerando o mês da data, temos os seguintes formatos personalizados: 
• M meses que são formados por um algarismo são exibidos com um algarismo. 
• MM meses que são formados por um algarismo são exibidos com dois algarismos, tendo o zero 
como inicial. 
• MMM exibe o nome do mês de forma abreviada, através das três primeiras letras. 
 
 
Excel 2016 – Avançado 
 
30 
 
• MMMM exibe o nome do mês por extenso. 
 
15. Clique em OK. 
 
Agora você vai mostrar a data por extenso. Por exemplo: para a data 01/01/2016, deverá ser mostrado 
sexta-feira, 01 de janeiro de 2016. 
 
16. No campo Tipo digite o seguinte: 
dddd", "dd" de "mmmm" de "aaa 
Onde: 
dddd nome do dia da semana 
“ , “ coloca um espaço, vírgula, espaço dd 
 número do dia 
“ de “ coloca espaço, a palavra “de”, espaço mmmm 
 nome do mês por extenso 
“ de “ coloca espaço, a palavra “de”, espaço aaa 
 número do ano 
 
No caso do ano, a representação aa formata a data para que apareçam os dois últimos dígitos do ano e a 
representação aaa formata a data para que apareçam os quatro dígitos do ano. 
 
17. Clique em OK. 
Excel 2016 – Avançado 
 
31 
 
 
 
 
 
 
 
FUNÇÕES DE DATA 
 
Função HOJE 
A função HOJE retorna a data de hoje formatada como data. 
• Sintaxe: HOJE() 
 
Essa função não possui argumentos. No entanto deve-se digitar os parênteses. 
 
Função ANO 
Essa função retorna o ano da data informada. O ano é retornado como um inteiro no intervalo de 1900 a 
9999. 
• Sintaxe: ANO(data_informada) 
• Argumentos: data_informada: data que se deseja retornar o ano. 
 
Função MÊS 
Você deve ter em mente a diferença entre conteúdo e formato. Conteúdo é o dado armazenado 
na célula, e formato é a forma como ele está sendo mostrado. Quando você efetuar cálculos 
com células formatadas, é o conteúdo que será trabalhado, e não o formato. 
Excel 2016 – Avançado 
 
32 
 
Essa função retorna o número do mês da data informada. O mês é retornado como um inteiro no intervalo 
de 1 a 12. 
• Sintaxe: MÊS(data_informada) 
• Argumentos: data_informada: data que se deseja retornar o mês. 
 
Função DIA 
Essa função retorna o dia da data informada. O dia é retornado como um inteiro no intervalo de 1 a 31. 
• Sintaxe: DIA(data_informada) 
• Argumentos: data_informada: data que se deseja retornar o dia. 
 
Função DIA.DA.SEMANA 
Essa função retorna o número do dia da semana da data informada. O dia é retornado como um inteiro, 
variando de 1 (domingo) a 7 (sábado), por padrão. 
• Sintaxe: 
o DIA.DA.SEMANA(data_informada;tipo) 
• Argumentos: 
o data_informada: data que se deseja retornar o número do dia da semana. 
o tipo: número que determina o tipo do valor retornado. Este argumento é opcional e pode 
ter os seguintes valores: 
TIPO VALOR RETORNADO 
1 ou omitido Números 1 (domingo) a 7 (sábado) 
2 Números 1 (segunda-feira) a 7 (domingo) 
3 Números 0 (segunda-feira) a 6 (domingo) 
11 Números 1 (segunda-feira) a 7 (domingo) 
12 Números 1 (terça-feira) a 7 (segunda-feira) 
13 Números 1 (quarta-feira) a 7 (terça-feira) 
14 Números 1 (quinta-feira) a 7 (quarta-feira) 
15 Números 1 (sexta-feira) a 7 (quinta-feira) 
16 Números 1 (sábado) a 7 (sexta-feira) 
17 Números 1 (domingo) a 7 (sábado) 
 
1. Abra a pasta “Datas Funções.xlsx”. 
Excel 2016 – Avançado 
 
33 
 
 
 
2. Clique na célula B1. 
3. Coloque nessa célula a função para mostrar a data de hoje. Digite: 
= HOJE() 
 
4. Clique na célula B2. 
5. Coloque nessa célula a função para mostrar o dia da data de hoje. Digite: 
= DIA(B1) 
 
6. Clique na célula B3. 
7. Coloque nessa célula a função para mostrar o número do mês da data de hoje. Digite: 
= MÊS(B1) 
 
8. Clique na célula B4. 
9. Coloque nessa célula a função para mostrar o ano da data de hoje. Digite: 
= ANO(B1) 
 
10. Clique na célula B5. 
11. Coloque nessa célula a função para mostrar o número do dia da semana da data de hoje, considerando 
1 como domingo e 7 como sábado. Digite: 
= DIA.DA.SEMANA(B1) 
12. Formate a célula com dia da semana para aparecer o dia escrito por extenso. 
 
Função DIATRABALHO.INTL 
Essa função retorna os dias úteis a projetar a partir de uma data informada, considerando como são 
calculados o fim de semana e os feriados que possam ocorrer no período. 
• Sintaxe: 
o DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados) 
• Argumentos: 
o data_inicial: data a partir da qual se deseja projetar dias úteis. 
Excel 2016 – Avançado 
 
34 
 
o dias: número de dias úteis antes ou depois da data inicial, isto é, quando ocorrerá o primeiro 
dia útil projetado a partir da data inicial. Um valor positivo gera uma data futura, um valor 
negativo gera uma data passada e um valor zero gera uma data inicial. 
o fim_de_semana: argumento opcional. Indica que dias da semana são considerados como 
pertencentes ao final de semana. Pode ter os seguintes valores: 
FIM_DE_SEMANA DIAS DE FIM DE SEMANA 
1 ou omitido Sábado, Domingo 
2 Domingo, Segunda 
3 Segunda-feira, Terça-feira 
4 Terça-feira, Quarta-feira 
5 Quarta-feira, Quinta-feira 
6 Quinta-feira, Sexta-feira 
7 Sexta-feira, Sábado 
11 Apenas Domingo 
12 Apenas Segunda-feira 
13 Apenas Terça-feira 
14 Apenas Quarta-feira 
15 Apenas Quinta-feira 
16 Apenas Sexta-feira 
17 Apenas Sábado• feriados: argumento opcional que é um conjunto de uma ou mais datas que serão excluídas do 
calendário de dias úteis. 
 
1. Abra a pasta Caixa.xlsx. 
2. Nela você encontra duas planilhas: Lançamentos e Feriados. Na planilha Lançamentos você fará a 
projeção dos dias úteis para o mês de Abril/2018. 
Excel 2016 – Avançado 
 
35 
 
 
 
3. Clique na célula B3 e digite a data 01/04/2018. 
4. Formate a célula B3 para que apareça somente o nome do mês. 
5. Agora você vai projetar o primeiro dia útil a partir do último dia do mês de março. Essa data é o dia 
anterior da data 01/04/2018 que está armazenada na célula B3, ou seja, é o conteúdo da célula B3 – 1. 
Clique na célula A6 e digite: 
= DIATRABALHO.INTL(B3-1; 
 
6. Como se deseja obter o primeiro dia útil a partir dessa data, digite 1; 
7. Nesta atividade o final de semana é composto por sábado e domingo. Logo, o parâmetro que deve ser 
informado para fim_de_semana é 1. Continue digitando 1; 
8. Na planilha Feriados, na coluna A, estão os feriados do mês de abril de 2018. Selecione a planilha 
Feriados e clique sobre a indicação da coluna A. Marque a coluna inteira para evitar refazer as fórmulas 
caso seja inserido algum feriado não previsto. Pressione ENTER e terá a seguinte fórmula na célula: 
= DIATRABALHO.INTL(B3-1;1;1;Feriados!A:A) 
 
9. Agora você vai calcular o próximo dia útil a partir da célula A6. Clique na célula A7 e digite a seguinte 
fórmula: 
= DIATRABALHO.INTL(A6;1;1;Feriados!A:A) 
 
Excel 2016 – Avançado 
 
36 
 
10. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de abril de 2016. 
 
 
11. Feche a pasta Caixa.xlsx. 
 
Função DIATRABALHOTOTAL.INTL 
Essa função retorna o número de dias úteis entre duas datas usando parâmetros para indicar quais e 
quantos dias são dias de fim de semana, bem como feriados. 
• Sintaxe: 
o DIATRABALHOTOTAL.INTL(data_inicial;data_final;fim_de_semana;feriados) 
• Argumentos: o data_inicial: data a partir da qual se deseja projetar 
dias úteis. 
o data_final: data até a qual se deseja projetar dias 
úteis. 
o fim_de_semana: argumento opcional. Indica que dias da semana 
são considerados como pertencentes ao final de semana. Pode ter 
os seguintes valores: 
 
FIM_DE_SEMANA DIAS DE FIM DE SEMANA 
1 ou omitido Sábado, Domingo 
2 Domingo, Segunda 
3 Segunda-feira, Terça-feira 
Excel 2016 – Avançado 
 
37 
 
4 Terça-feira, Quarta-feira 
5 Quarta-feira, Quinta-feira 
6 Quinta-feira, Sexta-feira 
7 Sexta-feira, Sábado 
11 Apenas Domingo 
12 Apenas Segunda-feira 
13 Apenas Terça-feira 
14 Apenas Quarta-feira 
15 Apenas Quinta-feira 
16 Apenas Sexta-feira 
17 Apenas Sábado 
 
o feriados: argumento opcional que é um conjunto de uma ou mais datas que serão 
excluídas do calendário de dias úteis. 
 
Seu trabalho neste momento é calcular o salário que deve ser pago por uma empresa aos seus estagiários. 
Esse controle é efetuado através da planilha Pagamento Estagiarios.xlsx. 
1. Abra a pasta Pagamento Estagiarios.xlsx. 
2. O primeiro passo é calcular quantos são os dias de vigência do contrato, ou seja, quantos dias corridos 
existem entre a data de início e a data de término do contrato. Para saber quantos dias existem entre 
duas datas, basta subtrair a data inicial da data final. Dessa forma, selecione a célula F4 e digite: 
= D4-C4 
 
3. Copie a fórmula para todos os estagiários. 
4. No entanto, o salário é pago considerando o número de dias a trabalhar no período, ou seja, o número 
de dias úteis do contrato, e não o número de dias corridos. Clique na célula G4 e digite a fórmula: 
= DIATRABALHOTOTAL.INTL(C4;D4;1;Feriados!A:A) 
 
Onde: 
C4 data inicial 
D4 data final 
1 final de semana formado por sábado e domingo 
Feriados!A:A lista de feriados no ano de 2016 
Excel 2016 – Avançado 
 
38 
 
 
5. Copie a fórmula para todos os estagiários. 
6. Agora calcule quanto cada estagiário deve receber, sabendo que o valor pago por hora está armazenado 
na célula C14. Clique na célula H4 e digite a fórmula: 
= MULT(E4;G4;C14) 
 
7. Copie a fórmula para todos os estagiários. 
 
 
8. A empresa percebeu que alguns contratos teriam que ser prorrogados. Foi então solicitado que uma 
nova coluna fosse inserida na planilha informando quantos dias corridos deveriam ser acrescidos aos 
contratos. Sua tarefa agora é inserir essa coluna e efetuar o cálculo das novas datas de término dos 
contratos para que o cálculo dos salários fique correto. Insira duas novas colunas na planilha antes da 
coluna Carga horária diária: 
 
 
9. Digite os valores mostrados na coluna Prorrogação (dias). Se quando digitar o valor aparecer uma data, 
formate os valores como números. 
10. Se você tiver uma data e somar um número a essa data, você terá a data correspondente à data inicial 
mais o número de dias somado. Portanto, para calcular a data efetiva de término do contrato, temos 
que adicionar o número de dias de prorrogação do contrato à data de término do contrato. Clique na 
célula F4 e digite a fórmula: 
= D4+E4 
Excel 2016 – Avançado 
 
39 
 
 
11. Copie a fórmula para todos os estagiários. 
12. Agora é necessário recalcular os dias de vigência do contrato e os dias a trabalhar no período, pois a 
data final do contrato é a nova data calculada, que se encontra na coluna Data de término efetiva. 
Refaça as fórmulas e confira o resultado: 
 
 
13. Feche e pasta Pagamento Estagiários.xlsx. 
14. Agora você vai efetuar cálculos com horas. Abra a pasta Atividades e horas.xlsx. Nessa pasta você 
encontra duas planilhas: Controle e Feriados. Na planilha Controle você vai calcular quanto o 
funcionário deve receber em função do tempo trabalhado nos dias úteis de fevereiro de 2018 e do valor 
que deve receber por hora. 
 
 
15. Clique na célula B2 e digite: 01/02/2018. 
16. Formate a célula para aparecer o nome do mês. 
17. A partir da célula A5 preencha até a célula A23 com os dias úteis de fevereiro. Utilize o mesmo raciocínio 
do exercício anterior. Os feriados de fevereiro estão na planilha Feriados. 
Excel 2016 – Avançado 
 
40 
 
18. Copie os dados do intervalo A5:A23 para o intervalo B5:B23. Tenha o cuidado de copiar os valores, e 
não as fórmulas. 
19. Formate o intervalo B5:B23 para que sejam mostrados os nomes dos dias da semana. 
20. Calcule agora o tempo trabalhado em cada dia. Esse tempo é calculado subtraindo o horário de entrada 
do horário de saída. Para isso, clique na célula E5 e digite: 
= D5-C5 
 
21. Copie a fórmula para todo o intervalo. 
 
 
Função HORA 
Essa função retorna a hora de um valor de tempo. A hora é retornada como um inteiro, variando de 0 (12:00 
A.M.) a 23 (11:00 P.M.). 
• Sintaxe: HORA(valor_tempo) 
• Argumento: valor_tempo: horário que contém a hora que desejamos encontrar. 
 
Função MINUTO 
Essa função retorna os minutos de um valor de tempo. O valor retornado é um número inteiro que varia de 
0 a 59. 
• Sintaxe: MINUTO(valor_tempo) 
• Argumento: valor_tempo: horário que contém os minutos que desejamos encontrar. 
 
1. Calcule quantas horas foram trabalhadas em cada dia. Clique na célula F5 e digite a seguinte fórmula: 
2. = HORA(E5) 
3. Copie a fórmula para todos os dias. Se o valor para número de horas estiver aparecendo como um valor 
do tipo hora, formate para que seja mostrado como número. 
4. Para calcular o número de minutos, além das horas cheias, clique na célula G5 e digite a seguinte 
fórmula: 
5. = MINUTO(E5) 
6. Copie a fórmula para todos os dias. Se o valor para número de minutos estiver aparecendo como um 
valor do tipo hora, formate para que seja mostrado como número. 
7. Para calcular o valor a pagar você deve multiplicar o número de horas pelo valor por hora e somar com 
a multiplicação do número de minutos pelo valor por hora dividido por 60. Clique na célula I5 e digite a 
fórmula:= F5*$I$2+G5*$I$2/60 
 
8. Copie a fórmula para todos os dias. 
9. Totalize o valor a pagar na célula I24. 
Excel 2016 – Avançado 
 
41 
 
 
 
 
Formatação CONDICIONAL 
A formatação condicional permite que os dados sejam mostrados na planilha de uma forma mais objetiva. 
Você pode, por exemplo, modificar a cor da fonte ou da célula para que os dados que satisfazem critérios 
estabelecidos sejam exibidos. 
1. Na coluna Dia da semana você vai mostrar em vermelho todos os dias que forem segunda-feira. No 
entanto você deve lembrar que o valor que está armazenado na célula é uma data, e não o nome do 
dia da semana. Para obter o número do dia da semana da data armazenada, você deve utilizar a função 
DIA.DA.SEMANA vista anteriormente. Selecione o intervalo B5:B23. 
2. Na guia Página Inicial, grupo Estilo, clique em Formatação Condicional e, no menu apresentado, 
 
3. Na janela exibida, selecione Usar uma fórmula para determinar quais células devem ser formatadas. 
selecione Nova regra. 
 
 
Excel 2016 – Avançado 
 
42 
 
4. No campo Formatar valores em que essa fórmula é verdadeira, digite a fórmula: 
=DIA.DA.SEMANA(B5;1)=2 
 
Lembre-se que a função DIA.DA.SEMANA traz como retorno o número do dia da semana, de acordo com o 
tipo de semana indicado na função. No caso o argumento para tipo foi passado como 1, ou seja, domingo 
é o dia 1, segunda-feira é o dia 2 e assim por diante. 
5. Clique no botão Formatar e escolha a cor vermelha e negrito. 
6. Clique em OK e, novamente em OK. 
 
 
7. Feche a pasta Atividades e horas.xlsx. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
43 
 
 
 
 
1. Abra a planilha Faculdade.xlsx. 
 
 
Siga os procedimentos descritos para efetuar os cálculos: 
a) Selecione a planilha Boletim. 
b) Calcule a média dos alunos. Utilize a função de tratamento de erros se for necessário em todos os 
cálculos que serão efetuados. 
c) Preencha a coluna MENSAGEM MÉDIA considerando a seguinte condição: se a média for maior ou 
igual a 5, a mensagem deve ser "Média OK", caso contrário deve ser "Média abaixo do limite". 
d) Preencha a coluna MENSAGEM FALTAS considerando a seguinte condição: se o número de faltas 
for menor ou igual a 25% das aulas dadas, a mensagem deve ser "Faltas OK", caso contrário deve 
ser "Faltas acima do limite". 
e) Calcule o RANKING DE MÉDIAS classificando os alunos por média. 
f) Preencha a coluna RESULTADO da seguinte forma: se a média está OK E faltas também OK, então 
colocar “Aprovado”, caso contrário colocar “Reprovado”. 
g) Formate condicionalmente a coluna RESULTADO colocando em vermelho os reprovados e em azul 
os aprovados. 
h) Preencha a planilha Resumo com as informações: número total de alunos, maior e menor média, 
número de aprovados e de reprovados, número de reprovados por média, por falta, por média e 
por falta, número de alunos que não entregaram o exercício, número de alunos que entregaram o 
exercício, média das médias dos alunos aprovados, média de faltas dos alunos com média OK e 
reprovados. 
Exercício Proposto 
Excel 2016 – Avançado 
 
44 
 
 
 
2. Abra a pasta Atendimento ao cliente.xlsx. 
 
 
Nessa pasta você encontrará as planilhas ATENDIMENTO e Feriados. A primeira planilha deve ser 
preenchida da seguinte forma: 
a) Considere o mês de novembro. 
b) Preencha a coluna DATA com os dias úteis de novembro. 
c) Calcule o tempo de atendimento de cada cliente. 
d) Calcule a data para retorno considerando o dia do atendimento e o prazo (em dias) para retorno. 
e) Calcule o prazo para retorno em dias úteis, considerando a data do atendimento e a data para 
retorno. 
f) Utilize a planilha Feriados para informar os feriados do ano de 2017. 
Excel 2016 – Avançado 
 
45 
 
 
3. Abra a pasta Banco.xlsx. Siga os seguintes procedimentos para efetuar os cálculos na planilha: 
 
a) Calcular o Saldo Atual. 
b) A coluna Situação deve ser preenchida da seguinte forma: se o cliente está com saldo positivo e a 
média entre o saldo anterior e o saldo atual for superior ou igual a R$ 3.000,00, deve ser 
apresentada a mensagem “Cheque Especial”, caso contrário deve ser apresentado a mensagem 
“Cheque Comum”. 
c) A coluna Limite do Cheque Especial deve ser preenchida da seguinte forma: se o cliente não tiver 
cheque especial, a célula deve ser preenchida com zero; se o cliente tiver Cheque Especial e se o 
total de créditos for menor ou igual a R$ 3.000,00, o valor do cheque especial será de R$ 2.000,00, 
caso contrário (cliente com cheque especial e o total de créditos maior do que R$ 3.000,00) o valor 
do cheque especial será de R$ 5.000,00. 
d) Contar o número de clientes que receberam cheque especial. 
 
4. Abra a pasta Idades e cursos.xlsx. Nessa pasta temos duas planilhas: Dados e Relatório. 
Excel 2016 – Avançado 
 
46 
 
 
 
 
a) Calcular a idade de cada aluno. Para isso considere o ano da data de hoje e o ano da data de 
nascimento do aluno. 
b) Preencher a planilha Relatório contando o número de alunos do sexo feminino, masculino, 
matriculados em biologia, educação física, história, letras, matemática e química e o número de 
alunos com idade maior do que 50 anos, menor do que 25 anos, maior de 40 anos e maior do que 
30 anos. 
 
5. Abra a pasta Vendedores.xlxs. Calcular o valor da Comissão e do Salário Total devido aos vendedores. 
Excel 2016 – Avançado 
 
47 
 
 
a) A coluna Comissão deve ser preenchida como sendo o valor da comissão sobre o valor total das 
vendas. 
b) A coluna Salário Total deve ser preenchida com o valor da comissão mais o valor do salário mínimo 
se o vendedor atingiu um volume de vendas maior ou igual a R$ 10.000,00. Caso contrário, a célula 
deve ser preenchida com o valor do salário mínimo. 
c) Calcular o total de vendas por funcionário. 
d) Calcular o valor da maior e menor venda. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
48 
 
CAPÍTULO 2 
 FUNÇÕES DE PESQUISA E 
 
 AUDITORIA DE 
FÓRMULAS 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Utilizar as funções de pesquisa PROCV, PROCH, 
CORRESP e ÍNDICE. 
 Executar auditoria em fórmulas 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
49 
 
 
 
Funções de PESQUISA 
As funções de pesquisa do Excel são poderosas ferramentas que permitem a busca de dados de forma 
rápida, muitas vezes evitando o uso de várias funções SE aninhadas. 
 
 
Objetivos : • Utilizar as funções PROCV e PROCH. 
Tarefas : • Abrir a pasta Relatório de Vendas.xlsx. 
 • Através da utilização da função PROCV, preencher a planilha Relatório com os 
nomes dos funcionários, comissões, salário fixo e imposto. 
 • Calcular o valor a receber para cada funcionário. 
 • Através da utilização da função PROCH, preencher a coluna Classificação faixas 
com as devidas faixas. 
 
Função PROCV 
A função PROCV permite procurar por um valor na primeira coluna de um intervalo de células e trazer outro 
valor da mesma linha em outra coluna especificada. 
• Sintaxe: o PROCV(valor_procurado;intervalo_células;núm 
_coluna,[procurar_intervalo]) 
• Argumentos: 
o valor_procurado: valor a ser procurado na primeira coluna do intervalo de células. Pode ser 
um valor ou uma referência. 
o intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um 
intervalo ou um nome de intervalo. Os valores na primeira coluna de intervalo_células são 
os valores procurados por valor_procurado e podem ser texto, números ou valores lógicos. 
Não existe distinção entre maiúsculas e minúsculas. 
o núm_coluna: número da coluna do intervalo de células que contém a informação que deve 
ser retornada. 
o procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de 
que forma o valor será procurado na primeira coluna do intervalo,se a correspondência 
deverá ser exata ou aproximada. 
 
Se este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Neste 
caso, os valores da primeira coluna do intervalo deverão estar classificados em ordem ascendente, ou a 
função poderá retornar valores indevidos. 
Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Neste caso, os valores da 
primeira coluna do intervalo não precisam estar classificados e, se houver mais de um valor que coincida 
com o valor_procurado, será considerado o primeiro encontrado. 
 1. Abra a pasta Relatório de Vendas.xlsx. Selecione a planilha Relatório. 
Atividade 1 – Utilizando as funções PROCV e PROCH 
Excel 2016 – Avançado 
 
50 
 
2. Preencha a coluna NOME DO FUNCIONÁRIO com os nomes dos funcionários, procurando-os na planilha 
Suporte. Clique na célula B2 e digite: 
= PROCV(A2; 
 
Você iniciou a digitação da função PROCV informando o primeiro argumento, que é o que se deseja 
procurar. No caso, você deve procurar pelo código do funcionário na planilha Suporte. 
3. Selecione a planilha Suporte. Como o mouse clicado, selecione a região A2:B23. Essa região é a que 
contém o valor a ser procurado (o código do funcionário, que está na primeira coluna do intervalo) e o 
valor a ser retornado (o nome do funcionário, que está na segunda coluna do intervalo). A fórmula fica 
da seguinte forma: 
= PROCV(A2; Suporte!$A$2:$B$23; 
 
Esse argumento indica qual intervalo de células será considerado para a procura e retorno dos dados. 
Observe que o intervalo A2:B23 da planilha Suporte foi fixado, pois não pode ser modificado quando você 
copiar a fórmula para as células de baixo. Outra forma de evitar que os endereços sejam modificados na 
cópia é atribuir um nome ao intervalo. 
4. Digite 2 e pressione a tecla ENTER. O parâmetro 2 indica que o valor que se deseja que retorne é o que 
está na coluna 2 do intervalo de células indicado. 
= PROCV(A2;Suporte!$A$2:$B$23;2) 
 
5. Copie a fórmula para todos os funcionários. 
6. Agora calcule o valor da comissão, salário fixo e impostos para cada funcionário. Esses valores estão em 
Suporte e dependem do valor da venda efetuada pelo funcionário. Atribua um nome à região da planilha 
Suporte onde será procurado o valor da venda e retornados os valores desejados para facilitar a 
digitação das fórmulas. Selecione a planilha Suporte e selecione o intervalo E2:H7. 
7. Na guia Fórmulas, grupo Nomes definidos selecione Definir Nome. Na janela apresentada digite 
Valor_Vendas e clique em OK. 
 
 
8. Você vai procurar pelo valor da venda na região Valor_Vendas e trazer o percentual de comissão. Esse 
percentual é obtido pela procura do valor da venda na região Valor_Vendas e trazendo o conteúdo da 
célula que está na coluna 2. O valor da comissão deve ser calculado como sendo o percentual de 
Excel 2016 – Avançado 
 
51 
 
comissão multiplicado pelo valor da venda. Selecione a planilha Relatório. Clique na célula D2 e digite a 
seguinte fórmula: 
= PROCV(C2;Valor_Vendas;2) * C2 
 
9. Copie a fórmula para todos os vendedores. 
10. Agora você vai preencher a coluna Salário Fixo com o valor do salário de cada vendedor. Mais uma vez 
o valor da venda dever ser procurado no intervalo Valor_Vendas . A função PROCV deve retornar o valor 
do salário fixo que está na coluna 3 do intervalo. Clique na célula E2 e digite a fórmula: 
= PROCV(C2;Valor_Vendas;3) 
 
11. O cálculo do imposto, nessa empresa, é feito sobre o valor das comissões. Agora você vai calcular quanto 
de imposto cada funcionário deve pagar. Para isso você deve procurar pelo percentual de imposto de 
cada um no intervalo Valor_Vendas e multiplicar pelo valor da comissão. Clique na célula F2 e digite a 
fórmula: 
= PROCV(C2;Valor_Vendas;4) * D2 
 
12. Calcule o valor a receber para cada funcionário como sendo: comissão + salário fixo – imposto. 
 
 
Função PROCH 
A função PROCH permite procurar por um valor na primeira linha de um intervalo de células e trazer outro 
valor da mesma coluna em outra linha especificada. 
• Sintaxe: o PROCH(valor_procurado;intervalo_células;núm 
_linha,[procurar_intervalo]) 
• Argumentos: 
o valor_procurado: valor a ser procurado na primeira linha do intervalo de células. Pode ser 
um valor ou uma referência. 
o intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um 
intervalo ou um nome de intervalo. Os valores na primeira linha de intervalo_células são os 
valores procurados por valor_procurado e podem ser texto, números ou valores lógicos. 
Não existe distinção entre maiúsculas e minúsculas. 
o núm_linha: número da linha do intervalo de células que contém a informação que deve ser 
retornada. 
o procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de 
que forma o valor será procurado na primeira linha do intervalo, se a correspondência 
deverá ser exata ou aproximada. 
 
Se este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Neste 
caso, os valores da primeira linha do intervalo deverão estar classificados em ordem ascendente, ou a 
função poderá retornar valores indevidos. 
Excel 2016 – Avançado 
 
52 
 
Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Neste caso, os valores da 
primeira linha do intervalo não precisam estar classificados e, se houver mais de um valor que coincida com 
o valor_procurado, será considerado o primeiro encontrado. 
A diferença entre as funções PROCV e PROCH é que a primeira efetua a procura de forma vertical, e a 
segunda de forma horizontal. Você pode utilizar qualquer uma das duas, dependendo da disposição do 
intervalo de células adotada. 
1. Você vai preencher a coluna Classificação faixas de acordo com as vendas de cada funcionário. Os 
valores a serem colocados nessa coluna estão na planilha Suporte. Selecione a planilha Suporte e 
selecione o intervalo de células F10:I11. Nomeie esse intervalo como Faixas. 
2. Selecione a planilha Relatório. Clique na célula H2 e digite a seguinte fórmula: 
 
= PROCH(C2;Faixas;2) 
3. Copie a fórmula para todos os vendedores. 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
53 
 
 
 
Objetivo : 
 
• Deslocar e rotacionar um objeto pelo teclado. 
Objetivos : • Utilizar as funções CORRESP e ÍNDICE. 
Tarefas 
 : 
• Calcular os valores de vendas máximo e mínimo. 
 • Identificar a posição desses valores dentro da planilha de vendas. 
 • Identificar os nomes dos vendedores responsáveis pelos valores máximo e 
mínimo de vendas. 
 
Função CORRESP 
Essa função traz como resultado a posição em que o valor procurado encontra-se no intervalo de células 
indicado, que deve ser formado por uma linha de células ou por uma coluna de células. • Sintaxe: 
CORRESP(valor_procurado;intervalo;[correspondência]) 
• Argumentos: 
o valor_procurado: argumento obrigatório, sendo o valor que desejamos procurar no 
intervalo de células e obter sua posição relativa dentro dele. 
o intervalo: argumento obrigatório, sendo o intervalo de células que estão sendo 
pesquisadas. Esse intervalo deve ser uma linha de células ou uma coluna de células. 
o correspondência: argumento opcional que especifica como o valor será encontrado. Pode 
ser: 
 1 ou omitido: Localiza o maior valor que é menor ou igual a valor_procurado. Nesse 
caso, os dados no intervalo devem estar classificados em ordem crescente. 
 0 : Localiza o primeiro valor que é exatamente igual ao valor_procurado. Os dados 
no intervalo podem estar em qualquer ordem. 
 -1 : Localiza o menor valor que é maior ou igual a valor_procurado. Nesse caso, os 
dados no intervalo devem estar classificados em ordem decrescente. 
 
1. Agora você vai verificar em que local da planilha se encontram os valores para maior e menor venda. 
Selecione a planilha Controle. 
2.Calcule a maior e menor venda na coluna VALOR. Clique na célula E5 e digite: 
= CORRESP( 
 
3. Você vai procurar pelo valor da maior venda, que está na célula D5. Continue a fórmula digitando D5; 
= CORRESP(D5; 
 
4. Agora você vai informar o intervalo de células onde será procurado o valor da venda. Esse intervalo é 
C2:C23 da planilha Relatório. 
Atividade 2 – Utilizando as funções CORRESP e ÍNDICE 
Excel 2016 – Avançado 
 
54 
 
= CORRESP(D5; Relatório!C2:C23; 
 
5. Como desejamos que o Excel encontre exatamente o valor, o próximo parâmetro será 0. A fórmula final 
é a seguinte: 
= CORRESP(D5; Relatório!C2:C23;0) 
 
6. Repita os mesmos procedimentos para encontrar a posição da menor venda na planilha Relatório. 
 
 
Função ÍNDICE 
Essa função traz o conteúdo de uma célula através do fornecimento de sua posição dentro do intervalo de 
células. 
• Sintaxe: ÍNDICE(intervalo;linha;[coluna]) 
• Argumentos: 
o intervalo: argumento obrigatório, sendo o intervalo de células que contém o dado a ser 
retornado. 
o linha: argumento obrigatório, sendo o número da linha do intervalo onde está o dado a ser 
retornado. 
o coluna: argumento opcional, sendo o número da coluna do intervalo onde está o dado a ser 
retornado. 
 
7. Agora você deve retornar os nomes dos vendedores que efetuaram a maior e menor venda. Clique na 
célula F5 e digite: 
= ÍNDICE( 
 
8. Você deve informar o intervalo de células que contém o nome do vendedor que está sendo procurado. 
Para tanto digite na fórmula: 
= ÍNDICE(Relatório!B2:B23; 
 
9. Agora você deve informar em que linha desse intervalo encontra-se o dado desejado. Pela função 
CORRESP você determinou, anteriormente, o número da linha onde está a maior venda. Portanto, o 
parâmetro que você deve informar na função é exatamente o resultado da função CORRESP, que se 
encontra na célula E5 da planilha Controle. A fórmula completa fica então: 
= ÍNDICE(Relatório!B2:B23;Controle!E5) 
 
Excel 2016 – Avançado 
 
55 
 
10. Utilize os mesmos procedimentos para trazer o nome do vendedor que efetuou a menor venda. 
 
 
AUDITORIA DE FÓRMULAS 
Muitas vezes é necessário executar auditoria em fórmulas inseridas em suas planilhas. Muitas vezes as 
fórmulas podem gerar erros ou mesmo retornarem valores que não são os esperados. O Excel 2016 fornece 
ferramentas bastante úteis que você possa utilizar para localizar e investigar as causas dos erros e 
determinar soluções. 
Essas ferramentas incluem o rastreamento de precedentes e dependentes de uma célula, rastreamento de 
 
 
Objetivos : • Rastrear as células precedentes de uma célula 
Tarefas : • Abrir a pasta Faculdade.xlsx. 
 • Rastrear as células precedentes das células da planilha Boletim. 
 • Rastrear as células precedentes das células da planilha Resumo. 
 
A ferramenta Rastrear Precedentes permite verificar quais células afetam o valor da célula selecionada no 
momento, ou seja, de que células aquela que se encontra selecionada depende para o cálculo de seu valor. 
1. Abra a pasta Faculdade.xlsx. 
2. Selecione a planilha Boletim. 
3. Clique na célula I5. 
4. Clique na ferramenta Rastrear Precedentes, do grupo Auditoria de Fórmulas, da guia Fórmulas. 
5. Veja que uma seta vem da célula K2 e outra vem da célula H5, ou seja, o resultado da fórmula contida 
na célula I5 depende dos valores das células K2 e H5. Isso é mesmo verdade, pois a célula I5 contém a 
fórmula: 
=SE(H5<=25%*$K$2;"Faltas OK";"Faltas acima do limite") 
 
Atividade 3 – Rastreando os precedentes de uma célula 
erros, exibição e depuração das fórmulas. Todas essas ferramentas estão presentes no grupo Auditoria de 
Fórmulas da guia Fórmulas. 
 
 
 
Excel 2016 – Avançado 
 
56 
 
 
 
Se você clicar duas vezes em cima da seta, a célula precedente fica selecionada. 
6. Selecione a planilha Resumo. 
7. Clique na célula B14. 
8. Clique na ferramenta Rastrear Precedentes. 
 
 
A linha pontilhada indica que a célula ou células precedentes encontram-se em outra planilha. 
9. Clique duas vezes sobre a linha pontilhada. A janela mostra a origem das células precedentes, com a 
indicação da pasta, planilha e endereço de célula ou intervalo. Para acessar a origem desejada, basta 
selecioná-la e clicar no botão OK. 
Excel 2016 – Avançado 
 
57 
 
 
 
10. Remova as setas selecionando a ferramenta Remover Setas. Você deve remover as setas 
das duas planilhas. 
 
 
 
 
Objetivos : • Rastrear as células dependentes de uma célula 
Tarefas : • Rastrear as células dependentes das células da planilha Boletim. 
 • Rastrear as células dependentes das células da planilha Resumo. 
 
A ferramenta Rastrear Dependentes permite verificar quais células são afetadas pelo valor da 
célula selecionada no momento. 
1. Selecione a planilha Boletim e clique na célula I5. 
2. Clique na ferramenta Rastrear Dependentes, do grupo Auditoria de Fórmulas, da guia 
Fórmulas. 
3. Veja que uma seta vai para a célula K5 e uma linha pontilhada vai para outra planilha. 
 
 
A primeira seta, indicando a célula K5, mostra que a fórmula =SE(E(G5="Média OK";I5="Faltas 
OK");"Aprovado";"Reprovado") depende do valor da célula I5. 
A seta indicada pela linha pontilhada e apontando para outra planilha, mostra que as células B8, 
B9 e B10 da planilha Resumo dependem do valor da célula I5. Podemos verificar essas referências 
clicando duas vezes sobre a linha pontilhada. 
Atividade 4 – Rastreando os dependentes de uma célula 
Excel 2016 – Avançado 
 
58 
 
 
 
4. Remova as setas da planilha. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
59 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
60 
 
Atividade 5 Rastreando erros em fórmulas 
Objetivos : • Rastrear erros em fórmulas 
Tarefas : • Gerar um erro na fórmula que calcula a média de um aluno. 
 • Rastrear esse erro. 
 
A ferramenta Verificação de Erros permite localizar, rastrear e realizar as etapas de cálculo de uma célula 
que contenha um erro. 
1. Clique na célula F5 e modifique a sua fórmula para: 
= MÉDIA(B5:E5) 
 
2. Observe que muitas células agora apresentam erros, ocasionados pelo erro da célula F5. 
 
 
3. Clique na célula K5 e observe que no momento em que a célula foi selecionada, você tem a indicação 
que está ocorrendo. 
 
Excel 2016 – Avançado 
 
61 
 
 
4. Clique na setinha ao lado do botão mostrado e selecione Rastrear erro e observe as setas 
mostradas: 
 
 
Existe uma seta vinda da célula G5 que, por sua vez, contém uma seta vinda da célula F5 que, 
por sua vez, contém uma seta vinda do intervalo de células B5:E5. Ou seja, o erro apontado na 
célula K5 é decorrente dos erros apontados nas células G5, F5 e do intervalo B5:E5. Na verdade, 
esse erro é causado pela tentativa de efetuar o cálculo da média aritmética com conteúdos de 
células vazias. 
5. Retorne a fórmula da célula F5 ao seu valor original: 
 =SEERRO(MÉDIA(B5:E5);0) 
 
 
Atividade 6 Avaliando fórmulas 
Objetivos : • Depurar fórmulas 
Tarefas • Verificar as etapas de cálculo de uma fórmula da planilha Boletim. 
 
A ferramenta Avaliar Fórmula permite depurar uma fórmula, analisando individualmente cada 
uma de suas partes. É bastante útil quando o resultado retornado não é o esperado. 
1. Clique na célula I5 e selecione a ferramenta Avaliar Fórmula. 
2. Na janela exibida você pode efetuar, passo a passo, os cálculos que estão envolvidos na 
fórmula. 
Excel 2016 – Avançado 
 
62 
 
 
 
Para mostrar o resultado da expressão que está sublinhada, clique em Avaliar. Cada vez que 
você clica nesse botão, a expressão sublinhada é calculada mostrando o resultado recente. 
Dessa forma você pode verificartodas as etapas de cálculo da fórmula. 
3. Clique em Avaliar. Perceba que, a cada vez que clicarmos nesse botão, os cálculos são 
efetuados, até que a expressão chegue ao seu resultado final. 
 
 
Se quiser reiniciar a avaliação, clique no botão Reiniciar. 
4. Clique em Fechar. Selecione a célula K5 e selecione a ferramenta Avaliar 
Fórmula. 
5. Clique no botão Depuração total. 
Excel 2016 – Avançado 
 
63 
 
 
 
Observe que foi aberto um quadro contendo a fórmula da célula precedente à 
célula K5 para que seja avaliada. 
6. Clique novamente em Depuração total. 
 
 
Observe que foi aberto um quadro contendo a fórmula da célula precedente à célula G5 para 
que seja avaliada. 
Uma vez que se tenha abertas todas as fórmulas envolvidas no cálculo da célula K5, você pode 
avaliar cada uma delas individualmente. Basta clicar dentro do quadro correspondente e efetuar 
a avaliação até o resultado final. 
7. Efetue a avaliação de cada fórmula. 
8. Para finalizar, clique em Fechar. 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
64 
 
 
Atividade 7 Mostrando fórmulas 
Objetivos : • Mostrar as fórmulas das planilhas 
Tarefas: • Exibir as fórmulas das planilhas Boletim e Resumo. 
 
A ferramenta Mostrar Fórmulas permite a visualização das fórmulas nas células das 
planilhas ao invés de seus resultados. 
1. Selecione a planilha Resumo. 
2. Clique na ferramenta Mostrar Fórmulas. 
 
 
Observe que, ao invés dos resultados, você visualiza as fórmulas. 
3. Selecione a planilha Boletim. 
4. Mostre as fórmulas nas células. Para mostrar novamente os resultados nas células, clique 
na ferramenta Mostrar Fórmulas. 
5. Feche a pasta Faculdade.xlsx. 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
65 
 
ANOTAÇÕES 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
66 
 
 Exercício Proposto 
 
1. Abra a pasta IMC.xlsx. 
a) Calcule o índice de massa corpórea (IMC) de cada pessoa como sendo peso/altura2. Lembre-se que 
para o cálculo de exponenciação utiliza-se o caractere ^. 
b) Preencha a coluna Resultado com a mensagem adequada de acordo com a classificação do IMC. 
c) Preencha a coluna Regime com a mensagem adequada de acordo com a classificação do IMC. 
 
2. Abra a pasta Escola de Idiomas.xlsx. 
a) Preencha a coluna CURSO com o curso correspondente a seu código. 
b) Preencha a coluna MENSALIDADE com a mensalidade correspondente ao código do curso. 
 
3. Abra a pasta Escritório.xls. 
a) Na planilha Atraso calcule o total de atrasos para cada funcionário. 
b) Preencha a coluna Atrasos da planilha Salários de acordo com a tabela constante da planilha Atraso. 
c) O valor dos atrasos deve ser calculado da seguinte forma: se o número de atrasos for maior do que 
5, o valor será o valor hora (salário fixo / 240) acrescido de 20%; caso contrário, será o próprio valor 
hora. 
d) Calcular o valor da coluna Total de Atrasos em função das colunas Atrasos e Valor dos Atrasos. 
e) Calcule o número total de horas extras para cada funcionário na planilha Hora Extra. Nessa mesma 
planilha, calcule o número de pessoas que têm horas extras em cada semana e no total. 
f) Traga para a coluna Hora Extra da planilha Salários o total de horas extras de cada funcionário. 
g) O valor da hora extra deve ser calculado da seguinte forma: se o número de atrasos no mês for 
maior do que a média das horas de atraso de todos os funcionários, o valor da hora extra deve ser 
de 20% a mais sobre o valor da hora normal (salário fixo / 240); caso contrário, o valor deverá ser 
35% a mais sobre o valor da hora normal. 
h) Calcule a coluna Total da Hora Extra. 
i) Calcule o Salário Total como sendo o Salário Fixo + Total da Hora Extra – Total dos Atrasos. 
j) A coluna Condição deve ser preenchida com a mensagem “Destaque” para os funcionários que 
tiveram um número de horas de atraso menor ou igual a 1 e fizeram mais de 7 horas extras no mês; 
caso contrário a coluna deve ficar em branco. 
k) O funcionário deve receber um recado no holerite de acordo com a seguinte regra: se o número de 
horas de atraso for maior do que 5 ou se o número de horas extras for menor do que 2, a mensagem 
será “Precisa melhorar”; caso contrário, a mensagem será “Bom Trabalho!”. 
 
4. Abra a planilha Impostos.xlsx. Calcule o Imposto de Renda e o INSS a pagar para cada um dos salários 
de acordo com as tabelas a seguir: 
 
a) Imposto de Renda 
Excel 2016 – Avançado 
 
67 
 
Salário Alíquota Parcela a deduzir 
Até 1.499,15 0% 0 
De 1.499,16 até 2.246,75 7,5% 112,43 
De 2.246,76 até 2.995,70 15,0% 280,94 
De 2.995,71 até 3.743,19 22,5% 505,62 
Acima de 3.743,19 27,5% 692,78 
 
O cálculo do imposto devido é feito multiplicando-se o valor do salário pela alíquota correspondente e 
subtraindo-se a parcela a deduzir. 
 
b) INSS 
Salário Alíquota 
Até 1.040,22 8% 
De 1.040,23 até 1.733,70 9% 
De 1.733,71 até 3.467,40 11% 
 
O cálculo da contribuição devida é feito multiplicando-se o valor do salário pela alíquota correspondente. 
Se o salário for maior do que R$ 3.467,40, o desconto será de R$ 381,41, independente do valor do salário. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
68 
 
CAPÍTULO 
3 
 FUNÇÕES DE TEXTO 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Utilizar as funções de texto ARRUMAR, DIREITA, 
ESQUERDA, EXT.TEXTO, MAIÚSCULA, MINÚSCULA e 
PRI.MAIÚSCULA. 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
69 
 
 
 
Objetivos : • Utilizar a função ARRUMAR. 
Tarefas : • Abrir a pasta Rodízio.xlsx. 
• Eliminar os espaços em branco no início das células da coluna 
MARCA da planilha Dados. 
 
Desde 1997 está em vigor, na cidade de São Paulo – SP, o rodízio municipal de veículos, com o propósito de 
melhorar as condições ambientais e também de reduzir os congestionamentos nas principais vias da cidade 
nos horários de maior movimento. Os automóveis e caminhões não podem circular em uma determinada 
região, denominada Centro Expandido, nos períodos de 07:00 – 10:00 horas e 17:00 – 20:00 horas todos os 
dias, de segunda a sexta-feira, seguindo uma tabela de acordo com o final da chapa do veículo. Assim: 
Veículos com finais de chapa 1 e 2 Não circulam às segundas-feiras 
Veículos com finais de chapa 3 e 4 Não circulam às terças-feiras 
Veículos com finais de chapa 5 e 6 Não circulam às quartas-feiras 
Veículos com finais de chapa 7 e 8 Não circulam às quintas-feiras 
Veículos com finais de chapa 9 e 0 Não circulam às sextas-feiras 
Recebemos uma pasta contendo os dados de vários veículos e devemos, então, verificar na data corrente 
quais veículos podem circular livremente e quais devem obedecer ao rodízio. Além disso, os dados chegaram 
de forma não muito adequada, com espaços em branco indevidos no início dos campos, letras minúsculas 
ao invés de maiúsculas e outros acertos que devem ser feitos. 
Para essa tarefa vamos utilizar as funções de texto do Excel, além de funções já estudadas, como SE e PROCV. 
 
Função ARRUMAR 
Essa função tem como objetivo eliminar os espaços em branco à esquerda e à direita do texto. 
• Sintaxe: ARRUMAR(texto) 
• Argumentos: texto: texto do qual se deseja remover espaços à direita ou à esquerda. 
 
1. Abra a pasta Rodízio.xlsx. 
2. Selecione a planilha DADOS. 
Atividade 1 – Utilizando a função ARRUMAR 
Excel 2016 – Avançado 
 
70 
 
 
 
3. Você vai arrumar a coluna referente a MARCA para eliminar os espaços em branco no 
início de cada célula. Esse ajuste será efetuado na planilha INTERMEDIÁRIA. Selecione a 
planilha INTERMEDIÁRIA e clique na célula A2. 
 
 
4. O texto onde se deseja eliminar os espaços indevidos está na célula A2 da planilha DADOS. 
Portanto, digite a seguinte fórmula: 
=ARRUMAR(DADOS!A2) 
 
5. Copie a fórmula até a célula A18.Excel 2016 – Avançado 
 
71 
 
Atividade 2 – Utilizando as funções MAIÚSCULA, MINÚSCULA e 
 PRI.MAIÚSCULA 
 
Objetivos : • Utilizar as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA. 
Tarefas : • Converter para letras maiúsculas os conteúdos das células referentes à coluna 
PLACA da planilha DADOS. 
 • Converter para letras minúsculas os conteúdos das células referentes à coluna 
COMENTÁRIOS da planilha DADOS. 
Função MAIÚSCULA 
Essa função tem como objetivo converter o texto para caracteres maiúsculos. 
• Sintaxe: MAIÚSCULA(texto) 
• Argumentos: texto: texto que se deseja converter para maiúsculas. 
 
Função MINÚSCULA 
Essa função tem como objetivo converter o texto para caracteres minúsculos. 
• Sintaxe: MINÚSCULA(texto) 
• Argumentos: texto: texto que se deseja converter para minúsculas. 
 
Função PRI.MAIÚSCULA 
Essa função tem como objetivo colocar em maiúsculas todas as primeiras letras das palavras de uma cadeia 
de texto. 
• Sintaxe: PRI.MAIÚSCULA(texto) 
• Argumentos: texto: texto que se deseja converter parcialmente para maiúsculas. 
 
1. A coluna PLACA da planilha DADOS está mostrando as placas dos automóveis com a identificação das 
letras em caracteres minúsculos. Você vai modificar para que apareçam em letras maiúsculas. Selecione 
a planilha INTERMEDIÁRIA, clique na célula B2 e digite a fórmula: 
= MAIÚSCULA(DADOS!B2) 
 
2. Copie a fórmula para todos os automóveis. 
3. Agora você vai converter os valores da coluna COMENTÁRIOS da planilha DADOS para minúsculas. Ainda 
na planilha INTERMEDIÁRIA clique na célula C2 e digite: 
= MINÚSCULA(DADOS!C2) 
4. Copie a fórmula para todos os automóveis. 
5. Os nomes dos proprietários dos automóveis estão escritos em letras minúsculas. Vamos 
corrigir esse erro, colocando todas as iniciais em maiúsculas. Ainda na planilha 
INTERMEDIÁRIA clique na célula D2 e digite: 
=PRI.MAIÚSCULA(DADOS!D2) 
6. Copie a fórmula para todos os proprietários. 
Excel 2016 – Avançado 
 
72 
 
 Atividade 3 – Utilizando as f unções ESQUERDA, DIREITA e 
EXT.TEXTO 
 
 
Objetivos : • Utilizar as funções ESQUERDA, DIREITA e EXT.TEXTO. 
Tarefas : • Retornar as letras das chapas dos automóveis. 
 • Retornar as duas últimas letras das chapas dos automóveis. 
 • Retornar o último número das chapas dos automóveis. 
 
Função ESQUERDA 
Essa função tem como objetivo retornar os primeiros n caracteres à esquerda de uma cadeia de texto. 
• Sintaxe: ESQUERDA(texto;[número_caracteres]) 
• Argumentos: 
o texto: texto de onde se deseja extrair os caracteres à esquerda. 
o número_caracteres: argumento opcional que indica o número de caracteres à esquerda que 
se deseja extrair do texto. Se omitido, trará o primeiro caractere à esquerda. 
 
Função DIREITA 
Essa função tem como objetivo retornar os últimos n caracteres à direita de uma cadeia de texto. 
• Sintaxe: DIREITA(texto;[número_caracteres]) 
• Argumentos: 
o texto: texto de onde se deseja extrair os caracteres à direita. 
o número_caracteres: argumento opcional que indica o número de caracteres à direita que se 
deseja extrair do texto. Se omitido, trará o último caractere à esquerda. 
 
Função EXT.TEXTO 
Essa função tem como objetivo retornar os n caracteres à partir de uma determinada posição em uma cadeia 
de texto. 
• Sintaxe: EXT.TEXTO(texto;posição;número_caracteres) 
• Argumentos: 
o texto: texto de onde se deseja extrair os caracteres. 
o posição: posição do primeiro caractere a ser extraído do texto. 
o número_caracteres: número de caracteres que devem ser extraídos a partir da posição do 
primeiro caractere considerado. 
 
1. Você vai extrair as letras das chapas dos automóveis. Para isso, clique na célula E2 da planilha 
INTERMEDIÁRIA, e digite: 
= ESQUERDA(B2;3) 
Excel 2016 – Avançado 
 
73 
 
2. Copie a fórmula para todos os automóveis. 
3. Agora você vai extrair as duas últimas letras das placas dos automóveis, considerando que se deseja 
trazer, a partir da posição 2, os dois caracteres do conteúdo das células que estão na coluna PLACA da 
planilha INTERMEDIÁRIA. Clique na célula F2 e digite: 
= EXT.TEXTO(E2;2;2) 
 
4. Copie a fórmula para todos os automóveis. 
5. Extraia o último número das chapas dos automóveis. Para isso, clique na célula G2 da planilha 
INTERMEDIÁRIA, e digite: 
= DIREITA(B2;1) 
 
6. Copie a fórmula para todos os automóveis. 
7. Para completar a tarefa você deve preencher a planilha RODÍZIO. Nessa planilha você vai ter a indicação 
dos automóveis que estão ou não no rodízio na data informada. Copie somente os valores das colunas 
PLACA e ÚLTIMO NÚMERO DA PLACA da planilha INTERMEDIÁRIA para as colunas PLACA e FINAL DA 
PLACA da planilha RODÍZIO. Copie somente os valores, e não as fórmulas. 
8. Na planilha RODÍZIO clique na célula B2 e digite a fórmula para mostrar o dia de hoje. 
= HOJE() 
 
9. Você vai colocar o nome do dia da semana correspondente à data de hoje na célula D2. Para isso você 
precisa procurar pelo número do dia da semana na tabela Dia da Semana através das funções 
DIA.DA.SEMANA e PROCV. Clique na célula D2 e digite: 
= PROCV(DIA.DA.SEMANA(B2;2);L3:M9;2) 
 
10. Preencha a coluna DIA DO RODÍZIO com o dia da semana encontrado na tabela Rodízio a partir do final 
da chapa do automóvel. Clique na célula C5 e digite a fórmula: 
= PROCV(B5;$I$3:$J$8;2) 
11. Copie a fórmula para todos os automóveis. 
12. Agora você vai preencher a coluna MENSAGEM informando se o veículo está ou não no rodízio na data 
considerada. Clique na célula D5 e digite a fórmula: 
=SE(C5=$D$2;"Carro no rodízio";"Livre para circular") 
 
13. Formate condicionalmente essa coluna para que mostre a mensagem “Carro no rodízio” em vermelho. 
Excel 2016 – Avançado 
 
74 
 
 
 
 
 
 
1. Abra a pasta Livraria Libélula.xlsx. Na planilha Aplicação faça os seguintes ajustes: 
a) A coluna Nome do Livro deve conter o conteúdo da coluna Nome do Livro da planilha Original sem 
os espaços em branco iniciais. 
b) A coluna Autor deve conter os nomes dos autores da planilha Original escritos com as iniciais em 
letras maiúsculas. 
c) A coluna Editora deve ter os nomes das editoras da planilha Original escritos em letras maiúsculas. 
d) A coluna Pré Venda deve ter o conteúdo da coluna Pré Venda da planilha Original escrito em letras 
minúsculas. 
e) A coluna Código 1º par deve conter os dois primeiros caracteres dos códigos constantes da coluna 
Código da planilha Original. 
f) A coluna Código 2º par deve conter os dois caracteres do meio dos códigos constantes da coluna 
Código da planilha Original. 
g) A coluna Código 3º par deve conter os dois últimos caracteres dos códigos constantes da coluna 
Código da planilha Original. 
 
2. Abra a pasta Codigos Produtos.xlsx. 
 
Na planilha Estoque cada produto é identificado por um código de formato XXXXXX-XXX que é formado 
pelos seguintes caracteres: 
a) O primeiro caractere informa o tipo de alimento de acordo com a tabela Tipo de Produto na planilha 
Tabelas; 
b) Do segundo ao quarto caractere temos a informação se o produto pertence à cesta básica ou não. 
Se esse conjunto for CBS, indica que o produto pertence à cesta básica; se for CBN, indica que o 
produto não pertence à cesta básica; 
Exercício Proposto 
Excel 2016 – Avançado 
 
75 
 
c) O quinto e sexto caracteres informam a distribuidora de acordo com a tabela Distribuidoras na 
planilha Tabelas. 
 
Preencha as colunas da planilha Estoque da seguinte forma: 
a) Coluna Tipo do Produto: deve conter o tipo do produto. 
b) Coluna Cesta Básica: deve conter “Sim” se o produto pertence à cesta básica ou “Não” se não 
pertence. 
 
Coluna Distribuidora: deve conter o nome da distribuidora. 
 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
76 
 
CAPÍTULO 4 
 FUNÇÕES DE BANCO 
 
 DE DADOSOBJETIVOS 
 Utilizar as funções de banco de dados: BDCONTARA, 
BDEXTRAIR, BDMÁX, BDMÍN, BDMÉDIA e BDSOMA. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
77 
 
Funções de BANCO DE DADOS 
Nesse capítulo você vai aprender a utilizar funções de banco de dados para manipular informações em 
planilhas com características de banco de dados. 
 
 
Objetivos : • Utilizar as funções BDSOMA e BDMÉDIA. 
Tarefas : • Abrir a pasta Relatório Produtos.xlsx. 
 • Preencher um relatório com o valor total de vendas efetuadas por fornecedor. 
 • Preencher um relatório com o valor total de vendas efetuadas por fabricante. 
 • Preencher um relatório com o valor da média de vendas efetuadas por 
fornecedor. 
 • Preencher um relatório com o valor da média de vendas efetuadas por 
fabricante. 
 
A pasta Relatório Produtos.xlsx contém informações sobre itens fornecidos por vários fornecedores. Seu 
trabalho será gerar relatórios para resumir e consolidar informações sobre os fornecedores e fabricantes 
dos produtos. 
 
Função BDSOMA 
Essa função tem como objetivo somar os valores de uma coluna de acordo com critérios estabelecidos. 
• Sintaxe: BDSOMA(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados que serão somados. Pode ser indicada 
pelo nome do campo entre aspas, o endereço da célula que contém o nome da coluna ou 
pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Abra a pasta Relatório Produtos.xlsx. 
Atividade 1 – Utilizando as funções BDSOMA e BDMÉDIA 
Excel 2016 – Avançado 
 
78 
 
 
 
2. O primeiro passo é somar os valores de venda por fornecedor, para isso você vai criar seus critérios para 
utilizá-los com a função BDSOMA. Selecione a planilha Critérios. 
 
 
 
 
3. Como se deseja efetuar a soma por fornecedor, os critérios devem conter a identificação do nome do 
campo (FORNECEDOR) e o conteúdo que se deseja procurar no campo. Digite os valores mostrados 
CUIDADO! Tanto o nome do campo quanto o conteúdo a ser procurado devem estar digitados 
exatamente como na planilha onde se fará a procura. É muito comum, durante a digitação, 
inserir um espaço em branco a mais ou mesmo pressionar alguma tecla que não gera caractere 
imprimível e, na hora da procura, não se obter nenhum resultado. Se isso acontecer, digite 
novamente as informações dos critérios. 
a seguir: 
 
 
 
Excel 2016 – Avançado 
 
79 
 
 
4. Selecione a planilha Relatório e clique na célula B4. Você vai calcular a soma de vendas para o 
fornecedor 3 Manos Hipermercados. 
5. Digite: 
= BDSOMA( 
 
6. O primeiro argumento referese à região da planilha que será considerada como o banco de dados. 
Selecione a planilha Controle de Produtos e selecione a região A1:D24. 
= BDSOMA('Controle de Produtos'!A1:D24; 
 
7. O próximo argumento é o nome do campo que será somado. No caso, é o campo Total e que está na 
célula D1 da planilha Controle de Produtos. 
= BDSOMA('Controle de Produtos'!A1:D24;'Controle de Produtos'!D1; 
 
8. O último parâmetro é o critério para efetuar a soma. Nesse caso o critério é: Fornecedor igual a 3 Manos 
Hipermercados. Esse critério está indicado na planilha Critérios na região A4:A5. 
= BDSOMA('Controle de Produtos'!A1:D24;'Controle de Produtos'!D1;Critérios!A4:A5) 
 
9. Preencha a coluna SOMA do relatório por fornecedor calculando a soma para os outros fornecedores. 
 
 
Função BDMÉDIA 
Essa função tem como objetivo calcular a média aritmética entre os valores de uma coluna de acordo com 
critérios estabelecidos. 
• Sintaxe: BDMÉDIA(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para os quais a média será calculada. 
Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que contém o 
nome da coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Agora você vai calcular a média de vendas para cada fornecedor. Para facilitar, você vai atribuir um nome 
à região da planilha Controle de Produtos que corresponde ao banco de dados, um nome à célula que 
contém a indicação do campo onde será efetuado o cálculo e nomes às regiões que contêm os critérios. 
Selecione a região A1:D24 da planilha Controle de Produtos e atribua o nome Geral. 
2. Selecione a célula D1 da planilha Controle de Produtos e atribua o nome Coltotal. 
3. Selecione a região A1:A2 da planilha Critérios e atribua o nome Carretel. 
Excel 2016 – Avançado 
 
80 
 
4. Selecione a região A4:A5 da planilha Critérios e atribua o nome Manos. 
5. Selecione a região A7:A8 da planilha Critérios e atribua o nome Doces. 
6. Selecione a região A10:A11 da planilha Critérios e atribua o nome Flores. 
7. Selecione a planilha Relatório e clique na célula C4. Você vai calcular a média de vendas para o 
fornecedor 3 Manos Hipermercado. Digite a fórmula: 
= BDMÉDIA(Geral;Coltotal;Manos) 
 
Onde: 
 
Geral identificação do banco de dados. 
Coltotal célula que contém o título da coluna onde será calculada a média. 
Manos critério. 
 
8. Calcule a média de vendas para os outros fornecedores. 
9. Agora você vai calcular a soma por fabricante. Na planilha Critérios digite os critérios para os 
fabricantes: 
 
 
10. Nomeie os critérios: garoto, granja, maeterra, nestle, parmalat, renata, royal e união. 
11. Selecione a planilha Relatório e clique na célula B13. 
12. Digite a fórmula: 
=BDSOMA(Geral;Coltotal;garoto) 
 
13. Calcule a soma para os outros fabricantes. 
Excel 2016 – Avançado 
 
81 
 
14. Seguindo o mesmo raciocínio utilizado para os fornecedores, calcule a média de vendas para os 
fabricantes. 
 
 
 
 
 
Objetivo : 
 
• Deslocar e rotacionar um objeto pelo teclado. 
Objetivos : • Utilizar as funções BDMÁX e BDMÍN. 
Tarefas 
 : 
• Preencher um relatório com o valor máximo de vendas efetuadas por 
fornecedor. 
 • Preencher um relatório com o valor máximo de vendas efetuadas por fabricante. 
 • Preencher um relatório com o valor mínimo de vendas efetuadas por 
fornecedor. 
 • Preencher um relatório com o valor mínimo de vendas efetuadas por fabricante. 
 
Função BDMÁX 
Essa função tem como objetivo retornar o valor máximo dentro de uma lista de valores de acordo com as 
condições especificadas. 
• Sintaxe: BDMÁX(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para os quais o valor máximo será 
calculado. Pode ser indicada pelo nome do campo entre aspas, o endereço da célula que 
contém o nome da coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
Função BDMÍN 
Essa função tem como objetivo retornar o valor mínimo dentro de uma lista de valores de acordo com as 
condições especificadas. 
• Sintaxe: BDMÍN(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para os quais o valor mínimo será 
calculado. Pode ser indicada pelo nome do campo entre aspas, o endereço da célulaque 
contém o nome da coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
Atividade 2 – Utilizando as funções BDMÁX e BDMÍN 
Excel 2016 – Avançado 
 
82 
 
1. Selecione a planilha Relatório e clique na célula D4. 
2. Os parâmetros da função BDMÁX são os mesmos das funções BDSOMA e BDMÉDIA. Digite a fórmula: 
= BDMÁX(Geral;Coltotal;Manos) 
 
3. Calcule o valor máximo de vendas para os outros fornecedores. 
4. Calcule o valor máximo de vendas para os fabricantes. 
5. Agora calcule o valor mínimo de vendas para os fornecedores. Clique na célula F4 e digite a fórmula: 
= BDMÍN(Geral;Coltotal;Manos) 
 
6. Calcule o valor mínimo de vendas para os outros fornecedores. 
7. Calcule o valor mínimo de vendas para os fabricantes. 
 
 
 
 
 
Objetivos : • Utilizar a função BDEXTRAIR. 
Tarefas : • Preencher um relatório com o nome dos produtos que apresentam o valor 
máximo de vendas efetuadas por fornecedor. 
 • Preencher um relatório com o nome dos produtos que apresentam o valor 
máximo de vendas efetuadas por fabricante. 
 • Preencher um relatório com o nome dos produtos que apresentam o valor mínimo 
de vendas efetuadas por fornecedor. 
 • Preencher um relatório com o nome dos produtos que apresentam o valor 
mínimo de vendas efetuadas por fabricante. 
 
Sua planilha Relatório mostra os valores máximo e mínimo de vendas por fornecedor e por fabricante. No 
entanto você precisa também saber quais são os produtos responsáveis por essas vendas. Para isso você 
utilizará a função BDEXTRAIR. 
 
Função BDEXTRAIR 
Essa função tem como objetivo retornar um único valor em uma coluna de uma lista que coincida com as 
condições especificadas. 
• Sintaxe: BDEXTRAIR(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados para a extração do valor desejado. 
Pode ser indicado pelo nome do campo entre aspas, o endereço da célula que contém o 
nome da coluna ou pelo número da coluna dentro da lista. 
Atividade 3 – Utilizando a função BDEXTRAIR 
Excel 2016 – Avançado 
 
83 
 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Você vai procurar no banco de dados pelos produtos responsáveis pelas maiores e menores vendas por 
fornecedor. O seu critério agora é selecionar o fornecedor e o valor da sua maior venda. Para isso você 
deve modificar sua planilha de critérios. Selecione a planilha Critérios e clique na célula B1. 
2. Nessa célula você vai digitar o nome do campo que contém o valor da maior venda do fornecedor 
Carretel Hipermercados na planilha Controle de Produtos. Esse campo é o campo de nome Total. 
Portanto, na célula B1 digite: 
Total 
 
3. Selecione a célula B2. Nessa célula você vai indicar o valor da maior venda efetuada pelo fornecedor 
Carretel Hipermercados. Esse valor está sendo calculado na planilha Relatório na célula D5 . Portanto, 
na célula B2 da planilha Critérios, digite: 
= Relatório!D5 
 
4. Modifique os critérios para os outros fornecedores. 
 
 
5. Selecione a planilha Relatório e clique na célula E4. 
6. Digite a seguinte fórmula: 
= BDEXTRAIR(Geral;'Controle de Produtos'!A1;Critérios!A4:B5) 
 
Note que a coluna a ser considerada nessa função é a que contém o nome do produto na planilha Controle 
de Produtos (célula A1), e a região que indica o critério agora contém o campo Valor. 
7. Mostre os nomes dos produtos de maior valor para todos os fornecedores. 
8. Modifique a planilha Critérios para que você possa mostrar os nomes dos produtos de menor valor para 
todos os fornecedores: 
Excel 2016 – Avançado 
 
84 
 
 
 
9. Preencha a planilha Relatório procurando pelos produtos de menor valor. 
10. Utilize o mesmo raciocínio para extrair os nomes dos produtos de maior e menor valor para cada 
fabricante. Construa os critérios na planilha Critérios. 
 
 
 
Se a função BDEXTRAIR retornar o erro #Valor!, significa que o valor procurado não foi 
encontrado; se retornar o erro #Num!, significa que existe mais de um valor que atende o 
critério. 
Excel 2016 – Avançado 
 
85 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
86 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
87 
 
 
 
 
Objetivos : • Utilizar a função BDCONTARA. 
Tarefas : • Preencher um relatório com o número de produtos por fornecedor. 
 • Preencher um relatório com o número de produtos por fabricante. 
 
Função BDCONTARA 
Essa função tem como objetivo contar as células não vazias em uma coluna de uma lista de acordo com as 
condições especificadas 
• Sintaxe: BDCONTARA(banco de dados;campo;critérios) 
• Argumentos: 
o banco de dados: intervalo de células compondo uma lista com os dados dispostos em linhas 
e colunas. A primeira linha dessa lista contém os rótulos (nomes) de cada coluna. 
o campo: identificação da coluna que contém os dados que devem ser contados. Pode ser 
indicada pelo nome do campo entre aspas, o endereço da célula que contém o nome da 
coluna ou pelo número da coluna dentro da lista. 
o critérios: intervalo de células que contém a condição especificada. 
 
1. Selecione a planilha Relatório. 
2. Clique na célula H4. 
3. Os parâmetros da função BDCONTARA são os mesmos das funções anteriores. Logo, digite na célula H4 
a fórmula: 
= BDCONTARA(Geral;'Controle de Produtos'!$B$1;Manos) 
 
4. Preencha o relatório contando o número de produtos para os demais fornecedores e fabricantes. 
 
Atividade 4 – Utilizando a função BDCONTARA 
Excel 2016 – Avançado 
 
88 
 
 
 
 
Uma empresa vende produtos para diversos estados. De acordo com a localidade, os valores do frete e 
ICMS variam. Na pasta Bebidas e Alimentos.xlsx temos três planilhas: 
a) Nota: deve ser preenchida com os cálculos dos valores dos produtos e a forma de pagamento; 
b) Critérios: planilha que contém os critérios para o cálculo das funções no Relatório; 
c) Relatório: planilha que deve ser preenchida, por estado, com os cálculos da soma, média, produtos 
de 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Exercício Proposto 
Excel 2016 – Avançado 
 
89 
 
CAPÍTULO 5 
 FILTRO, CLASSIFICAÇÃO 
 
 E FILTRO AVANÇADO 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Selecionar dados aplicando AutoFiltro. 
 Classificar dados por uma ou mais chaves. 
 Aplicar o Filtro Avançado. 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
90 
 
Filtrando e classificando no Excel 
Neste capítulo você destacará dados de interesse através da aplicação do recurso filtro e 
classificará o conteúdo das planilhas por uma chave e por mais de uma chave. Você criará planilhas 
que apresentarão resultados desejados. Com base em uma lista de produtos você criará planilhas 
que apresentarão os produtos separados por departamento. 
 
 
 
Objetivo : • Conhecer o recurso AutoFiltro 
Tarefas : • Aplicar o AutoFiltro no arquivo Filtro Avançado.xlsx 
 
Nesta atividade você utilizará o arquivo Filtro Avançado.xlsx e aplicará o AutoFiltro para destacar 
um grupo de registros que correspondem aos produtos de determinado Departamento. Abra o 
arquivo Automação.xlsx e acesse a guia Pesquisa. 
1. Abra o arquivo Filtro Avançado.xlsx. 
2. Exiba o AutoFiltro. Na guia Dados, no grupo Classificar e Filtrar clique no comando Filtro. 
 
 
Botões para aplicação do filtro aparecem ao lado do rótulo de cada coluna. 
 
3. Clique no botão da coluna Departamento e selecione a opção SelecionarTudo para desmarcar 
todas as opções. 
4. Em seguida, marque apenas a opção Telefones e Celulares. 
 
 
5. Clique no botão OK. 
Atividade 1 – Aplicar o AutoFiltro 
Excel 2016 – Avançado 
 
91 
 
6. O resultado é a exibição das linhas onde constam o departamento escolhido. As linhas que 
correspondem a outros departamentos ficam ocultas. 
 
 
7. Para mostrar todas as linhas novamente clique no botão ao lado do nome do campo 
Departamento, escolha Selecionar Tudo e clique em OK. 
 
 
 
 
 
 
 
 
 
 
 
 
 
Atividade 2 Aplicar o filtro a mais de um campo 
Objetivo : • Apresentar dados que atendam a critérios combinados. 
Tarefa : • Aplicar um filtro que exiba os produtos do departamento Telefones e Celulares 
cujo preço seja inferior a R$ 600,00. 
 
1. Aplique o AutoFiltro que apresenta os produtos do departamento Telefones e Celulares. 
2. Clique no botão da coluna Preço. 
3. Escolha a opção Filtros de número e escolha a opção É Menor do Que... 
Podemos reexibir todas as linhas clicando na opção Limpar do grupo Classificar e Filtrar da 
guia Dados. 
 
A coluna onde o filtro foi aplicado ex ibe uma imagem representando a aplicação do filtro. 
 
 
Excel 2016 – Avançado 
 
92 
 
 
 
4. Digite o limite superior - 600. 
 
Atividade 3 Aplicar filtro alternativo 
Objetivo : • Aplicar filtro quando parte da informação é conhecida. 
Tarefa : • Aplicar filtro para produtos iniciados pela letra N e filtro para produtos que 
contenham como parte de sua descrição a informação - 14”. 
 
1. Clique no botão de filtro do campo Produto. 
2. Escolha em Filtros de Texto e a opção Começa com... 
 
5. Clique em OK . 
 
 
 
Clique em Limpar do grupo Classificar e Filtrar quando desejar exibir todas as linhas. 
 
Excel 2016 – Avançado 
 
93 
 
 
 
 
4. Clique em Limpar para restaurar todos os produtos. 
5. Clique no botão de filtro da coluna Descrição. 
3. Preencha com a parte conhecida da informação. Pressione OK . 
 
 
 
Excel 2016 – Avançado 
 
94 
 
 
6. Escolha Filtros de Texto e opção Contém... 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
95 
 
Atividade 4 Classificar com o AutoFiltro 
Objetivo : • Apresentar o AutoFiltro como alternativa de classificação. 
Tarefa : • Classificar por ordem alfabética de produtos. 
 
1. Clique em Limpar para retirar os filtros aplicados. 
2. Clique no botão de filtro da coluna Produto. 
3. Escolha a opção Classificar de A a Z para colocar os dados da coluna em ordem crescente ou Classificar 
de Z a A para colocar os dados em ordem decrescente. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Quando a coluna estiver preenchida com números as opções serão Classificar do Menor para o 
Maior, Classificar do Maior para o Menor . Quando a coluna estiver preenchida com datas, as 
opções serão Classificar do Mais Antigo para o Mais Novo e Classificar do Mais Novo para o 
Mais Antigo. 
Clique no botão Filtro do grupo Classificar e Filtrar quando quis er retirar os botões do 
AutoFiltro. 
Excel 2016 – Avançado 
 
96 
 
Atividade 5 Classificação Avançada 
Objetivo : • Apresentar o recurso de classificação por mais de uma chave. 
Tarefa : • Classificar os funcionários da planilha Nome.xlsx por Estado Civil e Nome. 
 
1. Abra o arquivo Nome.xlsx. 
2. Na planilha Nome, selecione qualquer célula dentro da lista. 
3. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 
 
 
4. Escolha na caixa de diálogo a Coluna Estado Civil. 
 
 
5. Clique em Adicionar Nível para definir uma segunda chave de classificação. 
 
 
6. Escolha a coluna Nomes na linha que surgiu e clique em OK. 
 
 
 
Excel 2016 – Avançado 
 
97 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
98 
 
Atividade 6 Classificação por lista personalizada 
Objetivo : • Classificar os dados por uma ordem estabelecida pelo usuário. 
Tarefa : • Classificar os funcionários por Cargo em ordem hierárquica. 
 
Você criará uma lista personalizada para poder classificar os cargos em uma ordem de importância 
arbitrária. Para isso você digitará os cargos na lista personalizada na seguinte ordem: Presidente, Diretor(a), 
Gerente, Subgerente, Analista, Contador(a), Secretária, Escriturário, Comprador, Vendedor, Conferente e 
Estagiário. 
1. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 
2. Escolha a coluna Cargo e escolha Lista Personalizada na caixa de combinação Ordem. 
 
 
3. Digite os cargos na ordem especificada pressionando ENTER após cada cargo ou separando por vírgulas. 
 
 
4. Clique em Adicionar e a lista será criada. 
Excel 2016 – Avançado 
 
99 
 
 
 
5. Clique em OK e OK novamente para classificar pela lista personalizada. 
 
 
Atividade 7 Classificação por cores 
Objetivo : • Utilizar cores como critério de classificação. 
Tarefa : • Classificar os produtos mais vendidos através de codificação por cores. 
 
Você vai classificar o arquivo Produtos Mais Vendidos.xlsx onde a cor verde representa os produtos mais 
vendidos, a cor vermelha os produtos de venda mediana e a cor azul os produtos com pouca venda. 
1. Abra o arquivo Produtos mais Vendidos.xlsx. 
2. Clique no comando Classificar do grupo Classificar e Filtrar da guia Dados. 
Excel 2016 – Avançado 
 
100 
 
 
 
 
 
 
 
4. Estabeleça a ordem de importância das cores, adicionando nível a cada cor definida. 
 
 
5. Clique em OK para efetuar a classificação. 
 
 
Atividade 8 Filtro avançado 
Objetivo : • Aplicar filtro utilizando critérios avançados. 
Tarefas : • Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha para cada 
departamento. 
 
Existem as opções Cor da Fonte e Ícone da Célula, neste último caso quando for aplicada a 
Formatação Condicional. 
3. Escolha a coluna Produto e Cor da Célula na caixa de combinação Classificar em . 
 
 
 
 
Excel 2016 – Avançado 
 
101 
 
1. Abra o arquivo Filtro Avançado.xlsx. Você vai criar uma lista dos departamentos sem 
repetição. 
2. Selecione a coluna Departamento. 
3. Clique com o botão direito e a opção Copiar ou use qualquer outro método de cópia. 
 
 
4. Selecione a planilha Critérios e a célula A1. 
5. Cole o conteúdo da área de transferência. 
6. Mantendo a coluna selecionada, clique no botão Remover Duplicata do grupo Ferramenta de Dados, da 
guia Dados. 
 
 
7. Certifique-se que a caixa de verificação Meus dados contêm cabeçalhos esteja marcada. Caso não esteja 
marque-a. clique em OK. 
 
Excel 2016 – Avançado 
 
102 
 
 
8. Clique em OK para concluir. 
 
 
Você vai criar as áreas de critérios necessárias para o uso do filtro avançado. Um critério é constituído 
de uma célula com o nome da coluna que contém o dado a ser pesquisado tendo logo abaixo a célula 
que contém o dado pesquisado. Ela é constituída, portanto, de um intervalo de pelo menos duas 
células. 
9. Construa os critérios como abaixo: 
 
 
10. Insira e renomeie uma planilha para os departamentos de Informática, Telefones e Celulares, 
Câmeras e Filmadoras, Eletrônicos e uma planilha para os produtos dos outros departamentos. 
 
 
 
 
 
11. Você vai iniciar agora a construção das planilhas utilizando o Filtro Avançado. Selecione todas as 
linhas da planilha Geral e renomeie este intervalo como Geral. 
12. Vá para a planilha Informática. 
Excel 2016 – Avançado 
 
103 
 
13. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 
 
 
14. No quadro que surge marque a opção Copiar para outro local. 
15. Preencha o Intervalo da lista com Geral. 
16. Preencha o intervalo de critérios clicando na aba da planilha Critérios e selecionando ascélulas A1 
e A2. 
17. Vá para Copiar para e clique na célula A1 da planilha Informática. 
 
 
18. Clique em OK. 
 
 
19. Repita os passos 12 a 17 iniciando o processo nas planilhas Telefones e celulares, Câmeras 
e Filmadoras, Eletrônicos utilizando os intervalos de critérios correspondentes. 
20. Vá para a planilha OUTROS. 
21. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 
22. Marque a opção Copiar para outro local. 
23. Preencha Intervalo da lista com Geral. 
Excel 2016 – Avançado 
 
104 
 
24. Preencha Intervalo de critérios selecionando o intervalo A14:A18 da planilha Critérios. 
 
25. Vá para Copiar para e clique na célula A1 da planilha OUTROS. 
 
 
26. Clique em OK. 
 
 
 
 
 
 
 
Objetivo : • Aplicar filtro utilizando critérios avançados combinados. 
Tarefas : • Separar os dados do arquivo Filtro Avançado.xlsx gerando uma planilha com os 
produtos do departamento Informática com preço inferior a R$ 1.200,00. 
 
1. Vá à planilha Critérios, digite Preço na célula B1 e <1200 na célula B2. 
Quando colocamos o cabeçalho do campo e várias linhas de dados a pesquisar, o filtro 
avançado considera que um ou outro nome relacionado atenderá o critério. 
Atividade 9 – Filtro avançado com critérios múltiplos 
 
 
 
 
Excel 2016 – Avançado 
 
105 
 
 
 
O intervalo A1:B2 da planilha Critérios formará o critério combinado a ser usado no filtro. Este critério fará 
com que o filtro avançado retorne os produtos de informática e com preço inferior a 1200. 
 
 
 
2. Insira uma nova planilha. 
3. Renomeie como Informática - menor que 1200. 
4. Clique no botão Avançado do grupo Classificar e Filtrar da guia Dados. 
5. Marque a opção Copiar para outro local. 
6. Preencha Intervalo da lista com Geral. 
7. Preencha Intervalo de critérios selecionando o intervalo A1:B2 da planilha Critérios. 
 
 
8. Clique em OK. 
 
 
 
1. Abra a pasta de trabalho Nome-exercício.xlsx e crie uma nova planilha separando as informações por 
cidade usando o filtro avançado. 
2. Ainda na mesma pasta e usando o filtro avançado, crie uma planilha com os dados das pessoas 
casadas e outra com os dados das pessoas solteiras. 
 
 
 
 
Só os produtos que atenderem a estas duas condições aparecerão no resultado. 
Exercício Proposto 
Excel 2016 – Avançado 
 
106 
 
CAPÍTULO 6 
 VISUALIZAÇÃO DE 
DADOS 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Apresentar recursos para exibição e interpretação de dados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
107 
 
 
Atividade 1 Criação de Minigráficos 
Objetivo : • Apresentar dados através de minigráficos. 
Tarefa : • Exibir dados da evolução das vendas de cada vendedor. 
 
Você criará gráficos que demonstrarão a evolução das vendas de cada vendedor na coluna Evolução. 
1. Abra o arquivo Vendas.xlsx. 
2. No grupo Minigráficos da guia Inserir escolha o tipo de minigráfico que deseja para representar os 
dados. Use Linha. 
 
 
3. Preencha Intervalo de dados com os dados das vendas mensais do primeiro vendedor D2:I2. 
4. Preencha Intervalo de Locais com J2, onde será criado o minigráfico. 
 
 
5. Clique em OK. 
6. Copie o minigráfico gerado para as linhas de baixo como você faz com fórmulas 
. 
Excel 2016 – Avançado 
 
108 
 
 
 
Atividade 2 Alteração e formatação de Minigráficos 
Objetivo : • Apresentar os recursos para formatação e alteração dos minigráficos. 
Tarefas : • Configurar a exibição de valores nulos. 
 • Editar dados. 
 • Alterar o tipo e o estilo. 
 
Ao se construir o minigráfico uma nova guia Ferramentas de Minigráfico é apresentada. 
 
 
Para verificar como o minigráfico apresenta valores nulos ou células contendo zero, coloque zero em 
alguns valores da planilha. 
1. Digite zero na célula G6. 
2. Apague o conteúdo da célula E3 e I4. 
3. Assinale Marcadores no grupo Mostrar da guia Design para destacar os pontos de dados. 
4. Clique na parte inferior do botão Editar Dados do grupo Minigráfico da guia Design. 
5. Clique na opção Células Ocultas e Vazias... 
6. Marque a opção Zero no quadro que aparece para alterar a representação das células vazias no 
 
 
minigráfico. 
 
 
 
Excel 2016 – Avançado 
 
109 
 
 
7. Clique em OK para executar a alteração. 
 
No grupo Tipo da guia Design você pode escolher entre gráfico de Linhas, Barras ou Ganha/Perde, que 
apresentam os seguintes estilos, respectivamente: 
 
 
 
 
 
8. Defina a cor do minigráfico e as características dos marcadores no grupo Estilo da guia Design. 
 
 
Os Minigráficos são tratados pelo Excel como células agrupadas e, portanto, a alteração da 
formatação e escolha de estilos é geral. Para formatarmos um minigráfico isoladamente 
devemos desagrupar. 
9. Selecione uma célula que contenha o Minigráfico, clique no botão Desagrupar do grupo Agrupar 
da guia Design. 
 
 
10. Selecione o minigráfico a ser modificado e aplique outra cor no minigráfico e outros formatos 
desejados. 
 
 
As células vazias aparecem como Lacunas por padrão. Ao alterarmos a opção para Zero a linha 
do gráfico muda sua representação. Se escolhermos a opção Conectar pontos de dados com a 
linha, a linha ligará o ponto anterior diretamente com o ponto posterior. 
Excel 2016 – Avançado 
 
110 
 
 
 
Objetivo : • Capturar tela utilizando o recurso Instantâneo. 
Tarefa : • Capturar parte do texto do arquivo Programa de Metas.docx 
 
1. Abra o arquivo Programa de Metas.docx. 
2. Abra o arquivo Vendas.xlsx se ele ainda não estiver aberto. 
3. Clique no botão Instantâneo do grupo Ilustrações da guia Inserir. 
 
 
4. Clique na opção Recorte de Tela. 
5. Com a cruz que aparece selecione o trecho a copiar. 
 
 
 
 
 
 
6. Após o aparecimento da imagem no arquivo de destino, utilize os recursos de tratamento de imagem 
normais usados no Excel. 
 
 
 
 
 
Abra a pasta Bebidas.xlsx e crie minigráficos para representar a evolução do consumo das marcas de 
cervejas. Formate os minigráficos para melhorar sua apresentação. 
 
 
Para copiar a tela inteira clique na miniatura listada em Janelas Disponíveis. Para copiar parte 
da tela use a opção Recorte de Tela. Esta opção atuará na tela mais recentemente aberta 
anterior ao arquivo que recebe a cópia. 
Exercício Proposto 
Atividade 3 Capturar telas 
Excel 2016 – Avançado 
 
111 
 
 
CAPÍTULO 7 
 TABELA DINÂMICA 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Resumir dados de planilhas utilizando a Tabela Dinâmica. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
112 
 
Atividade 1 Criação da Tabela Dinâmica 
Objetivo : • Resumir dados usando a Tabela Dinâmica. 
Tarefas : • Construir Tabela Dinâmica que apresente dados agrupados por projeto. 
 
Utilizando os dados do arquivo Movimento Financeiro.xlsx você criará uma apresentação da soma 
dos valores agrupados por projeto. 
1. Abra o arquivo Movimento Financeiro.xlsx. 
2. Clique na metade superior do botão Tabela Dinâmica do grupo Tabelas da guia Inserir. 
3. Para preencher o campo Tabela/Intervalo do quadro Criar Tabela Dinâmica, selecione o 
intervalo A3:G56 da planilha Dados. 
4. Selecione Nova Planilha para criar a Tabela Dinâmica em uma planilha independente. 
Se você clicar na metade inferior do botão, as sub-opções Tabela Dinâmica e Gráfico 
Dinâmico aparecem. Escolha, então, Tabela Dinâmica. 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
113 
 
 
 
5. Clique em OK. Surge uma nova guia Ferramentas de tabela Dinâmica com duas subguias: 
Opções e Design. 
 
 
À direita surge o Painel de Tarefas Lista de Campos da Tabela Dinâmica 
. 
 
Na lista de campos você escolherá os campos (colunas) que utilizará no relatóriode tabela dinâmica. 
• Filtro de Relatório – permite que você escolha um ou mais campos para filtrar dados no seu 
relatório. 
• Rótulos de Linha – são os campos onde itens iguais são agrupados. 
Excel 2016 – Avançado 
 
114 
 
• Rótulos de Coluna – são os campos que serão indicados quando necessitarmos do 
cruzamento de duas informações. 
• Valores – corresponde a uma ou mais colunas que terão suas informações consolidadas. 
 
6. Marque o campo Projeto e o campo Valor. Será construído o relatório agrupando os valores 
por Projeto. 
 
 
 
 
Atividade 2 Atualização e Inclusão de Dados 
Objetivo : • Modificar os dados existentes e acrescentar novos dados tornando-os visíveis na 
Tabela Dinâmica. 
Tarefas : • Alterar dados já existentes na guia Dados e atualizar a Tabela Dinâmica. 
 • Inserir uma nova linha entre linhas já existentes e nova linha após o último projeto e 
torná-los visíveis na Tabela Dinâmica. 
 
1. Torne a célula A13 como célula ativa na planilha Dados. 
2. Insira uma nova linha. 
3. Acrescente os dados: 
• Data: 23/10/2016 
• Projeto: Hidroelétrica Torrente 
• Item: Venda de Ações 
• Categoria: Dinheiro dos Sócios 
• E/S: Entrada • Valor: 10.000.000,00 
• Estado: Paraná. 
 
4. Volte à Tabela Dinâmica. Este novo projeto não foi incluído. 
5. Clique no botão Atualizar do grupo Dados da guia Opções e o novo projeto será relacionado no final. 
Excel 2016 – Avançado 
 
115 
 
 
 
6. Clique no botão classificar para ordenar os projetos. 
 
 
7. Inclua mais uma entrada na última linha vazia da planilha Dados. Digite: 
• Data: 20/06/2011 
• Projeto: Aeroporto Perus 
• Item: Duplicatas – Desconto 
• Categoria: Contas a Receber 
• E/S: Entrada 
• Valor: 500.000,00 
• Estado: Goiás. 
 
A linha acrescentada está fora do intervalo de dados reconhecido pela Tabela Dinâmica. Portanto, o recurso 
Atualizar não terá efeito neste caso. É necessário redefinir o intervalo. 
8. Volte à Tabela Dinâmica. 
9. Clique em Alterar Fonte de Dados do grupo Dados da guia Opções. 
 
 
10. Digite ou redefina com o mouse o novo intervalo que inclua a linha acrescentada. Após a definição do 
novo intervalo um novo quadro surge. 
Excel 2016 – Avançado 
 
116 
 
 
 
11. Clique em OK. 
 
Atividade 3 Criação de outros níveis 
Objetivo : • Acrescentar subníveis à Tabela Dinâmica. 
Tarefa : • Subdividir os valores de cada projeto por Entrada e Saída. Cada um desses níveis 
será, também subdividido por Categoria. 
 
1. Marque E/S e Categoria no painel da lista de campos da tabela dinâmica e os dados são apresentados 
com as subdivisões desejadas. 
 
 
2. Selecione a célula de qualquer subtotal e clique em Configurações do Campo do grupo Campo Ativo da 
guia Opções. 
3. Marque a opção Nenhum para ocultar os subtotais. 
Excel 2016 – Avançado 
 
117 
 
 
 
4. Clique em OK. 
5. Na coluna Soma de Valor, dê um duplo clique na linha correspondente a um subtotal. Será incluída 
uma nova planilha na pasta de trabalho contendo os itens que formam o referido subtotal. 
 
 
 
Para alterar o layout de forma a cruzar as informações você pode arrastar os campos E/S para o 
diagrama na posição que representa os Rótulos das Colunas. 
6. Na parte inferior do painel de tarefas, arraste os campos E/S da área Rótulos de Linha para a área 
Rótulos de Colunas. 
Quando criamos uma Tabela Dinâmica com a opção Nova Planilha, uma nova planilha é 
acrescentada. É aconselhável renomearmos as planilhas que vão sendo incluídas com 
nomes que indiquem o conteúdo da planilha. 
Excel 2016 – Avançado 
 
118 
 
 
 
 
 
 
Atividade 4 Formatar a Tabela Dinâmica 
Objetivo : • Alterar a aparência da tabela dinâmica para facilitar a visualização. 
Tarefa : • Aplicar formato monetário à Tabela Dinâmica. 
 • Aplicar Bordas à Tabela Dinâmica e alterar as funções agregadas utilizadas. 
 
1. Escolha e selecione o Estilo desejado na guia Design, no grupo Estilos de Tabela Dinâmica. 
 
 
2. Na guia Opções, no grupo Campo Ativo, clique em Configurações do Campo. 
Excel 2016 – Avançado 
 
119 
 
 
 
No quadro que surge, analise as várias opções de funções que estão disponíveis para resumir o campo. Além 
de somar, você pode contar, calcular a média aritmética, obter o valor máximo ou mínimo, etc. 
3. Clique em Formato do Número e escolha Moeda. 
 
 
 
4. Clique em OK. Este será o resultado. 
Excel 2016 – Avançado 
 
120 
 
 
 
 
Atividade 5 Agrupar Itens 
Objetivo : • Formar grupos de itens. 
Tarefa : • Agrupar os projetos pelas regiões. 
 
1. Crie uma nova Tabela Dinâmica. Volte à planilha Dados, clique em Tabela Dinâmica do grupo Tabelas da 
guia Inserir. 
2. Selecione os campos Estado, Projeto e Valor. 
 
 
Os Estados correspondem às regiões Sul (Santa Catarina) e Sudeste (São Paulo, Rio de Janeiro e Minas 
Gerais). 
3. Selecione o grupo Minas Gerais, mantenha a tecla CTRL pressionada e selecione os demais Estados da 
região Sudeste (Rio de Janeiro e São Paulo). 
4. Clique em Agrupar Seleção do grupo Agrupar da guia Opções. 
Excel 2016 – Avançado 
 
121 
 
 
 
 
5. Digite o nome da região, Sudeste, sobre Agrupar1. 
6. Selecione Paraná e Santa Catarina. 
7. Clique em Agrupar Seleção do grupo Agrupar da guia Opções. 
8. Digite Sul onde aparece Agrupar2. 
9. Digite Centro-Oeste no grupo Goiás. 
 
 
 
 
Atividade 6 Aplicar Filtro de Relatório 
Objetivo : • Selecionar os dados a serem visualizados. 
Tarefa : • Filtrar para visualizar os projetos de um só Estado. 
 
1. Crie outra Tabela Dinâmica em uma nova planilha. 
2. Escolha os campos Projeto, Estado e Valor. 
3. Arraste o campo Estado para a área de Filtro de Relatório. 
Clique em Desagrupar para desfazer os grupos. 
Excel 2016 – Avançado 
 
122 
 
 
 
 
 
 
 
4. Nas células A1 e B1 surge o Filtro de Relatório. 
 
6. Clique no Estado que quer exibir. Os projetos e respectivos valores dos Estados não selecionados ficarão 
ocultos. 
 
 
 
 
 
 
 
 
 
 
Marque a caixa de verificação Selecionar Vários Itens para exibir mais de um 
item por vez. 
5. Clique na caixa de combinação da célula B1. 
 
 
Excel 2016 – Avançado 
 
123 
 
Atividade 7 Agrupar campos por períodos 
Objetivo : • Agrupar informações por períodos de tempo com base num campo de Data. 
Tarefa : • Criar Tabela Dinâmica que relaciona os dados dos projetos agrupados por 
categoria de itens e por período de tempo. 
 
1. Crie uma nova Tabela Dinâmica em uma nova planilha. 
2. Escolha os campos E/S, Categoria, Valor e Data. 
 
 
3. Arraste o campo Data para a área Rótulos de Colunas. 
 
 
Surge uma coluna para cada data relacionada no campo Data. Você vai agrupar estas datas em períodos que 
tenham significado para quem analisa. 
4. Selecione a primeira data. Clique em Agrupar Campo do grupo Agrupar da guia Opções. 
Excel 2016 – Avançado 
 
124 
 
 
 
5. Clique em Anos. O item Meses já estava marcado e você pode acrescentar vários níveis de agrupamento 
de datas. Cada grupo em destaque formará um grupo. Caso deseje desmarcá-lo clique novamente sobre 
o período. 
6. Clique em OK. 
 
Atividade 8 Segmentação de Dados 
Objetivo : • Aplicar filtros aprimorados em tabelas dinâmicas. 
Tarefa : • Utilizar o recurso de segmentação de dados para filtrar dados por Projeto e por 
Estado. 
 
1. Ainda na tabela dinâmica da atividade anterior, clique no botão Segmentação de Dados, do 
grupo Filtro da guia Inserir. 
Excel 2016 – Avançado 
 
125 
 
 
 
2. Marque os campos Projeto e Estado e clique em OK. 
3. Posicione as Segmentações de Dados. Todos os itens aparecem com cor destacada 
indicando que nenhum filtro foi aplicado ainda. 
4. Clique no Estado do Rio de Janeiro. Somente este Estado fica destacado. No outro quadro 
aparecem destacados osprojetos deste Estado. Os dados na tabela dinâmica correspondem 
somente aos dados dos projetos do Rio de Janeiro. 
 
 
 
5. Clique em um destes projetos. O resultado é a apresentação dos valores deste projeto. 
Neste caso, usamos um filtro combinando os dois campos, Estado e Projeto. 
Você pode fazer a seleção de vários Estados mantendo a tecla CTRL pressionada 
enquanto clique com o mouse nos Estados escolhidos. 
Excel 2016 – Avançado 
 
126 
 
 
Atividade 9 Gráfico Dinâmico 
 
Objetivo : • Demonstrar a construção e o uso do Gráfico Dinâmico. 
Tarefa : • Construir gráfico dinâmico referente à Tabela Dinâmica. 
 
1. Em uma célula da Tabela Dinâmica, clique no botão Gráfico Dinâmico do grupo Ferramentas da guia 
 
 
 
Para desaplicar o(s) filtro(s), clique no botão Limpar Filtro de cada Segmentação de 
Dados. 
 
Excel 2016 – Avançado 
 
127 
 
 
2. Escolha o tipo e clique em OK. 
 
Use os recursos de gráfico do Excel. A única diferença entre um gráfico normal do Excel e do 
gráfico dinâmico é que este possui caixas de combinação para 
 aplicar 
filtro. 
 
3. Clique no botão Alternar Linha/Coluna para que o eixo X apresente os meses e os anos. 
 
4. Use as caixas de combinação para filtrar pelo campo desejado. 
 
 
Opções . 
 
 
 
 
Excel 2016 – Avançado 
 
128 
 
 
Atividade 10 – Mostrar Páginas de Relatório 
 
Objetivo : • Utilizar recurso para criar uma cópia da Tabela Dinâmica para item do Filtro de 
Relatório. 
Tarefa : • Criar Páginas de Relatório para a Tabela Dinâmica dos projetos. 
 
1. Acrescente o campo Estado na área Filtro de Relatório. 
 
 
2. Clique no botão Tabela Dinâmica, na opção Opções e, em seguida, em Mostrar Páginas do 
Filtro de Relatório. 
 
Excel 2016 – Avançado 
 
129 
 
 
3. Escolha Estado e clique em OK. 
 
 
Será criada uma cópia da Tabela Dinâmica para cada Estado constante na planilha Dados. 
 
 
 
 
1. Abra o arquivo Movimento-ex-din.xlsx e construa uma tabela dinâmica que mostre os valores das 
Entradas e Saídas por Estado. 
2. Use os recursos para formatar a tabela dinâmica. 
3. Crie um gráfico dinâmico baseada nesta tabela. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Exercício Proposto 
Excel 2016 – Avançado 
 
130 
 
 
CAPÍTULO 8 
 VALIDAÇÃO DE DADOS 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Restringir a introdução de dados nas planilhas. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
131 
 
 
 
Objetivo : • Determinar quais dados pode ser introduzido nas planilhas. 
Tarefa : • Definir as regras para aceitação dos dados introduzidos na planilha. 
 
Algumas planilhas podem conter restrições quanto às informações que podem ser introduzidas 
em determinadas colunas. Neste caso precisamos estabelecer quais serão os critérios para 
aceitação ou não dos dados introduzidos. Para esse fim utilizamos o recurso Validação de Dados. 
1. Abra o arquivo Validação.xlsx. 
2. Selecione a coluna A clicando no botão seletor. 
 
 
3. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 
4. Na caixa de combinação Permitir: escolha Data. 
 
5. Na caixa de combinação Dados: escolha é maior do que. 
6. Em Data de inicio: digite 30/09/10. Somente datas posteriores à data de início serão aceitas. 
Atividade 1 – Criar regras de validação 
Excel 2016 – Avançado 
 
132 
 
 
 
7. Clique em OK. Vá para o final da planilha e introduzida dados na primeira linha disponível. 
8. Informe a data de 10/05/10. A Validação de Dados está restringindo os dados desta coluna para datas 
posteriores a 30/09/10. Portanto uma mensagem de erro surge informando que a regra foi violada. 
 
 
9. O botão Repetir permite que você volte à célula e digite uma data válida. O botão Cancelar desconsidera 
a digitação e retorna o valor anterior da célula. Clique em Repetir e digite uma data válida. 
 
Os dados da coluna Projeto devem pertencer a uma lista de projetos já definidos na planilha Projetos. 
10. Clique na planilha Projetos. A lista desta planilha é a que deve ser considerada a digitação da coluna 
Projeto. 
11. Selecione a coluna A. 
12. Clique na caixa de nome para definir um nome para o intervalo selecionado. 
13. Digite Projetos e finalize com ENTER. 
14. Volte à planilha Dados. 
15. Selecione a coluna B. 
16. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 
17. No campo Permitir escolha Lista. 
18. No campo Fonte: digite =Projetos. 
19. Desmarque a opção Ignorar em branco. 
Excel 2016 – Avançado 
 
133 
 
 
 
20. Você pode associar uma Mensagem de entrada às células que recebem a Validação de Dados como 
forma de auxílio ao digitador. Clique na guia Mensagem de entrada. 
21. Preencha os campos Título e Mensagem de entrada. 
 
 
Esta mensagem ficará visível quando a célula for ativada. 
 
22. Você também pode substituir o alerta de erro padrão por um alerta de erro personalizado. Clique na 
guia Alerta de erro. 
23. Preencha o Título e a Mensagem de erro. Tecle ENTER para continuar o texto em outra linha. 
24. Escolha o Estilo do quadro de mensagem. 
Excel 2016 – Avançado 
 
134 
 
 
 
25. Escolha Aviso ou Informações. 
26. Clique em OK. 
 
Agora você aplicará a validação de dados à coluna E/S para aceitar os valores Entrada ou Saída. 
 
 
 
 
 
 
 
 
Excel 2016 – Avançado 
 
135 
 
27. Selecione a coluna E. 
28. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 
29. Em Permitir: escolha Lista. 
30. No campo Fonte: digite Entrada e Saída separados por ponto e vírgula (;). 
31. Escolha o estilo de alerta de erro e preencha os campos Título e Mensagem de erro. 
32. Clique em OK. 
33. Os valores a serem introduzidos em Valor – coluna F – não podem ser menores ou iguais a zero e nem 
maiores que 10 milhões. Você aplicará a validação de dados para que este critério seja respeitado. 
Selecione a coluna F. 
34. Clique no botão Validação de Dados do grupo Ferramentas de Dados da guia Dados. 
35. Em Permitir: escolha Decimal. 
36. Preencha o campo Mínimo: com 1 e o campo Máximo: com 10000000. 
37. Defina o Alerta de erro e clique em OK. 
 
 
38. Complete as informações das colunas restantes. 
 
 
 
1. Abra a pasta validaexercício.xlsx . 
2. Coloque restrições para que a coluna Estado civil apresente uma lista com Casado(a), Solteiro(a), 
Viúvo(a), Separado(a). 
3. Na coluna Sexo deve aparecer uma lista com Masculino e Feminino. 
4. Na coluna Nascimento somente devem ser aceitas pessoas nascidas antes de 1996. 
5. Na coluna Salário o limite superior é 18.000. 
 
 
 
Exercício Proposto