Excel Avançado

1
Sumário
Questões Práticas de Nivelamento ............................................................................................................................3
Ferramentas de Banco de dados .............................................................................................................................. 22
Funções ................................................................................................................................................................... 14
Funções Lógicas ....................................................................................................................................................... 26
Funções de Pesquisa e Referência ............................................................................................................................ 27
Funções Matemática e Trigonométrica..................................................................................................................... 32
Funções de Texto........................................................................................................................................................34
Tabela Dinâmica.........................................................................................................................................................36
Gráfico Dinâmico........................................................................................................................................................38
Cenário.......................................................................................................................................................................39
Atingir Metas..............................................................................................................................................................41
Subtotal......................................................................................................................................................................42
Validação de Dados....................................................................................................................................................43
Proteger Planilha........................................................................................................................................................48
Macro no Aplicativo Excel...........................................................................................................................................81

2
1. QUESTÕES PRÁTICAS DE NIVELAMENTO

QUESTÃO 1
▪ Clique sobre a primeira célula que corresponde a A1 da planilha
apresentada;
▪ Nas células seguintes digite as informações a baixo:

▪ Na linha que separa a coluna A e a coluna B de um duplo clique para

alterar o tamanho de acordo com o texto;
▪ Clique no menu Arquivo;
▪ Clique sobre a opção Salvar Como;
▪ Salve seu exercício na sua pasta com o nome Ex01;

QUESTÃO 2

▪ Renomeie a guia da planilha plan1 para Atividade1, a plan2 para

Atividade 2 e a plan3 para Atividade 3;
▪ Insira uma nova planilha após a plan3 e renomeie para Atividade 4;
▪ Exclua as planilhas: Atividade 2 e Atividade 3;
▪ Trabalhe na planilha Atividade 4 e digite os dados abaixo, iniciar na linha 1
coluna A:

1090 1020 1040 1030 1050 1060 1000 960 1030 1010
1011 1015 960 1030 976 1050 1010 1010 970 1005
1090 1020 1010 1020 1050 980 1000 1040 1030 1015
1040 1015 1050 1035 1060 1021 1045 1020 1070 1005
1030 960 1011 1020 1040 1015 980 1000 1020 1010

a) Exclua as linhas 2 e 3;
3
b) Exclua a coluna B;
c) Insira uma linha antes da linha 1.
d) Insira uma coluna, entre as colunas E e F.
e) Altere a altura da linha 2 para 30;
f) Altere a largura da coluna C para 40;
g) Clique no menu Arquivo;
h) Clique sobre a opção Salvar Como;
i) Salve seu exercício na sua pasta com o nome Ex02;

QUESTÃO 3

Digite a planilha abaixo, obedecendo as linhas e as colunas:

▪ Renomeie a guia da planilha para Balanço Semestral e adicione uma cor;

▪ No título: Mesclar e centralizar a célula, alterar a Fonte para Comic Sans
MS, Tamanho 16 Negrito.
▪ Altere a fonte da tabela para Tahoma, Tamanho 14;
▪ Faça as bordas e coloque cores em sua tabela;
▪ Salve seu exercício na sua pasta com o nome Ex03;

QUESTÃO 4
▪ Utilize seu exercício 03 para realizar o exercício 4;
▪ Formate as células que contém valores de dinheiro para Moeda com 2
casas decimais e com o símbolo.
▪ Faça as formatações de borda conforme o modelo abaixo.
▪ Salve seu exercício na sua pasta com o nome Ex04;

4
QUESTÃO 5
Elabore a planilha abaixo considerando todos os critérios de formatação descritos
a seguir:
OBSERVAÇÃO Digite todos os dados na planilha e depois faça a formatação
de sua planilha.

FEIRA DO MÊS
PRODUTO QUANTIDADE PREÇO R$ TOTAL PAGO R$
Arroz 5 1,38
Feijão 2,5 1,24
Açúcar 2,5 1,05
Farinha 1,5 0,90
Café 1 1,50
Leite 5 1,49
Queijo 0,5 8,00
Carne 8 5,00
Macarrão 3 0,70

▪ Use Fonte: Arial, tamanho 12;

▪ Use negrito no título da planilha e nos títulos das colunas.
▪ Aplique as bordas a sua planilha de acordo com o modelo.
▪ Aplique o formato de número de contabilização nas células das colunas preço
e total pago;
▪ Renomeie a planilha para Feira;
▪ Clique na Plan2 e renomeie para Estilo;
▪ Copie os dados da planilha Feira e cole na planilha Estilo;
▪ Salve seu exercício na sua pasta com o nome Ex05;

5
 QUESTÃO 06

Digite os dados abaixo:

▪ Formate a coluna C para formato de número de contabilização para que os

valores possuam duas casas decimais, sem o símbolo.
▪ Alinhe os títulos Item e Valor para 45º, Horizontal: centro, Vertical: Centro.
▪ Utilize nos títulos Fonte: Arial, Estilo da fonte: Negrito Itálico, Tamanho: 10.
▪ Altere a largura das linhas e colunas de modo que apareçam os itens por
inteiro.
▪ Formate as células em relação à borda conforme abaixo.

Faça as últimas formatações para que sua planilha fique como abaixo, para isso
insira uma linha entre as linhas de alojamento e telefone.

▪ Salve seu exercício na sua pasta com o nome Ex06;

6
 QUESTÃO 07
Digite os dados abaixo em uma nova pasta de trabalho.

▪ Utilize o formato de número de contabilização com 2 casas decimais e

símbolo para as coluna Preço/Unidade e Subtotal e para a célula referente
ao valor total de compras;
▪ Complete as colunas Preço/Unidade e Quantidade.
▪ Calcule a coluna subtotal fazendo a multiplicação do Preço/Unidade pela
Quantidade.
▪ Calcule o Total utilizando o botão Soma.
▪ Salve seu exercício na sua pasta com o nome Ex07;

7
QUESTÃO 8

Faça a planilha conforme modelo abaixo:

Cód. Produto Abr Mai Jun

1 Porca 6.265,00 6.954,00 7.858,00
2 Parafuso 8.701,00 9.658,00 10.197,00
3 Arruela 4.569,00 5.099,00 5.769,00
4 Prego 12.341,00 12.365,00 13.969,00
5 Alicate 6.344,00 7.042,00 7.957,00
6 Martelo 4.525,00 5.022,00 5.671,00
SOMA

▪ Utilize o botão Soma, e encontre o total de vendas dos meses de Abril,

Maio e Junho;
▪ Insira uma coluna após o mês de junho, com o nome total;
▪ Faça o somatório de vendas por produto de todos os meses na coluna
total;
▪ Insira uma linha abaixo de soma, com o nome de porca e prego;
▪ Faça o somatório de vendas de porca e prego mensal e total;
▪ Salve seu exercício na sua pasta com o nome Ex08;

QUESTÃO 9

Faça a planilha conforme modelo abaixo:

▪ Utilize a função soma, e encontre o total de vendas por região e

por mês.
▪ Salve seu exercício na sua pasta com o nome Ex09;

8
QUESTÃO 10

Digite o banco de dados abaixo:

Nome Endereço Bairro Cidade Estado

Lucimara Av. dos Jequitibas, 11 Jd. Paulista Florianópolis SC
Eduardo R. Antônio de Castro, 362 São Benedito Araras SP
Gabriela Maria de L. Poyares Mata Praia Vitória ES
Katiane R. 5, 78 Jd. Europa Rio Claro SP
Lilian R. Lambarildo Peixe, 812 Vila Tubarão Ribeirão Preto SP
Érica R. Tiradentes, 123 Centro Vitória ES
Helena R. Júlio Mesquita, 66 Centro Vila Velha ES
Tatiane R. Minas Gerais, 67 Parque Industrial P. de Caldas MG
Roberto Av. Limeira, 98 Belvedere Araras SP
Fernanda Av. Orozimbo Maia, 987 Jd. Limoeiro Serra ES
Maria Av. Ipiranga, 568 Ibirapuera Manaus AM
Pedro R. Sergipe, 765 Botafogo Campinas SP
Rubens Al. dos Laranjais, 99 Centro Rio de Janeiro RJ
Ana Rodovia Norte Sul Jd. Camburi Vitória ES

▪ Formate a tabela de acordo com sua vontade e gosto;

▪ Classifique a tabela por ordem alfabética de nome;
▪ Utilize o recurso localizar, para substituir o nome Gabriela, por Rafael.
▪ Aplique o comando Filtro e classifique conforme abaixo:

Nome Selecione somente 5 nomes para aparecer na lista.

Estado Mostre apenas o Estado do ES.

▪ Salve seu exercício na sua pasta com o nome Ex10.

QUESTÃO 11

▪ Abra seu exercício 10, retire todos os filtros e faça uma nova filtragem
que mostre apenas o bairro Centro e em seguida apenas a cidade Vila
Velha e em seguida salve-o em sua pasta com o nome Ex11.

9
QUESTÃO 12

Digite a planilha abaixo:

A planilha acima representa o gasto mensal com alimentação de uma família de

baixa renda; Elabore essa planilha calculando na coluna resultado o saldo do mês
(total recebido – total gasto); após implementação da planilha, esquematize um
gráfico que a represente apresentando um paralelo entre os valores gastos e os
recebidos em cada mês.

▪ Formatar os números com o símbolo monetário (R$);

▪ Salve em sua pasta com o nome EX12.

QUESTÃO 13

De posse da tabela abaixo, construa um gráfico em pizza, que mostre a relação

entre a espécie e estabelecimentos públicos e outro que mostre a relação entre a
espécie e estabelecimentos particulares. Insira no primeiro gráfico o título
ESTABELECIMENTOS PÚBLICOS e no segundo ESTABELECIMENTOS
PARTICULARES. Em ambos os gráficos, mostre a legenda abaixo do gráfico e
mostre os percentuais.
Estabelecimentos
Espécie
Públicos Particulares

Hospital 1002 5132

Pronto-socorro 150 156
Policlínicas 1531 6136
Outros 14393 472

▪ Salve o exercício em sua pasta com o nome EX13.

10
QUESTÃO 14

Elaborar a planilha abaixo, fazendo-se o que se pede:

CONTAS A PAGAR
JANEIRO FEV. MARÇO ABRIL MAIO JUNHO
SALÁRIO 500,00 750,00 800,00 700,00 654,00 700,00
CONTAS
ÁGUA 10,00 15,00 15,00 12,00 12,00 11,00
LUZ 50,00 60,00 54,00 55,00 54,00 56,00
ESCOLA 300,00 250,00 300,00 300,00 200,00 200,00
IPTU 40,00 40,00 40,00 40,00 40,00 40,00
IPVA 10,00 15,00 14,00 15,00 20,00 31,00
SHOPPING 120,00 150,00 130,00 200,00 150,00 190,00
COMBUSTÍVEL 50,00 60,00 65,00 70,00 65,00 85,00
ACADEMIA 145,00 145,00 145,00 145,00 100,00 145,00
TOTAL CONTAS
SALDO

▪ Salve seu exercício com o nome EX14.

QUESTÃO 15

Elabore a planilha abaixo, fazendo o que se pede:

Produto Estoque Compra Total Estoque Venda Total Estoque Atual

Borracha 500 50 350
Caderno 100 fls 200 57 160
Caderno 200 fls 300 20 260
Caneta Azul 1000 150 560
Caneta Vermelha 1000 230 680
Lapiseira 200 60 220
Régua 15 cm 500 98 545
Régua 30 cm 500 55 440
Giz de Cera 50 30 60
Cola 100 40 120
Compasso 100 180 220
Totais

DADOS:
Total Estoque: Estoque + Compra.
Total Estoque Atual: Total Estoque - Venda
Totais: somar os totais de todas as colunas.
▪ Salve seu exercício com o nome EX15.
11
QUESTÃO 16
Elabore a planilha com o formato especificado abaixo aplicando as funções
SOMA, MÉDIA e SE.

▪ Classifique a coluna Aluno(a) em ordem alfabética.

▪ Faça um gráfico em colunas que demonstre as notas da prova 01 e da prova 02
por alunos.
▪ Salve o exercício em sua pasta com o nome EX16.

QUESTÃO 17

Elaborar as planilhas abaixo, fazendo-se o que se pede:

Valor do Dólar R$ 2,94

Papelaria Papel Branco
Produtos Qtde Preço Unit. Total R$ Total US$
Caneta Azul 500 R$ 0,15
Caneta Preta 750 R$ 0,15
Caderno 250 R$ 10,00
Régua 310 R$ 0,50
Lápis 500 R$ 0,10
Papel Sulfite 1500 R$ 2,50
Tinta Nanquim 190 R$ 6,00

FÓRMULAS:
Total R$: multiplicar Qtde por Preço Unitário
Total US$: dividir Total R$ por Valor do Dólar – usar $ nas fórmulas
Alterar as colunas de acordo com a necessidade.
▪ Salvar o exercício em sua pasta com o nome EX17.

12
QUESTÃO 18

Elaborar a planilha abaixo, fazendo-se o que se pede:

Total
Cód. Produto Jan Fev Mar Máximo Mínimo Média
1º Trim.
1 Porca 4.500,00 5.040,00 5.696,00
2 Parafuso 6.250,00 7.000,00 7.910,00
3 Arruela 3.300,00 3.696,00 4.176,00
4 Prego 8.000,00 690,00 10.125,00
5 Alicate 4.557,00 5.104,00 5.676,00
6 Martelo 3.260,00 3.640,00 4.113,00
Totais
Total
Cód. Produto Abr Mai Jun Máximo Mínimo Média
2º Trim.
1 Porca 6.265,00 6.954,00 7.858,00
2 Parafuso 8.701,00 9.658,00 10.197,00
3 Arruela 4.569,00 5.099,00 5.769,00
4 Prego 12.341,00 12.365,00 13.969,00
5 Alicate 6.344,00 7.042,00 7.957,00
6 Martelo 4.525,00 5.022,00 5.671,00
Totais

Total do
Semestre

DADOS:

Total 1º Trimestre: soma das vendas dos meses de Jan / Fev / Mar.
Máximo: calcular o maior valor entre os meses de Jan / Fev / Mar.
Mínimo: calcular o menor valor entre os meses de Jan / Fev / Mar.
Média: calcular a média dos valores entre os meses de Jan / Fev / Mar.
Total 2º Trimestre: soma das vendas dos meses de Abr / Mai / Jun.
Máximo: calcular o maior valor entre os meses de Abr / Mai / Jun.
Mínimo: calcular o menor valor entre os meses de Abr / Mai / Jun.
Média: calcular a média dos valores entre os meses de Abr / Mai / Jun.
Total: soma das colunas de cada mês (1ª e 2ª tabela).
Total do Semestre: soma dos totais de cada trimestre.

▪ Salvar o exercício em sua pasta com o nome EX18.

18
QUESTÃO 19
Digite a tabela abaixo:

▪ Calcule a coluna Melhor preço utilizando a função MÍNIMO.

▪ Calcule a coluna Valor da Compra fazendo a multiplicação entre a Qtde e o
melhor preço.
▪ Formate as colunas A, B, C , Melhor preço e Valor da compra no formato
de número de contabilização sem símbolo e com 2 casas
▪ Salve o exercício em sua pasta com o nome EX19.

QUESTÃO 20

Elaborar as planilhas abaixo, fazendo-se o que se pede:

Escola de Serviço Público do Espírito Santo - ESESP

Vitória/ES

INSS Gratificação Salário

Nº NOME Salário Bruto INSS Gratificação
R$ R$ Líquido
1 Eduardo R$ 880,00 10,00% 9,00%
2 Maria R$ 951,00 9,99% 8,00%
3 Helena R$ 1.456,00 8,64% 6,00%
4 Gabriela R$ 13.500,00 8,50% 6,00%
5 Edson R$ 18.850,00 8,99% 7,00%
6 Elisangela R$ 5.459,00 6,25% 5,00%
7 Regina R$ 3.478,00 7,12% 5,00%
8 Paulo R$ 2.658,00 5,99% 4,00%

Dados
INSS R$: multiplicar Salário Bruto por INSS.
Gratificação R$: multiplicar Salário Bruto por Gratificação.
Salário Líquido: Salário Bruto mais Gratificação R$ menos INSS R$.
Formatar os números para que eles apareçam de acordo com a planilha dada.
Salve o exercício em sua pasta com o nome EX20.

19
QUESTÃO 21
Digite a tabela abaixo:

▪ Calcule o valor do lucro com cada material e formate as células para

moeda, 2 casas decimais e com símbolo.
▪ Insira um gráfico que represente o material e valor do lucro.
▪ Salve o exercício em sua pasta com o nome EX21.

QUESTÃO 22

Digite a tabela abaixo e formate-a.

▪ Coloque a coluna Nomes em ordem alfabética.

▪ Caso a pessoa tenha de 18 anos acima escreva em situação: "Maior de
Idade", caso contrário "Menor de Idade"; usando função SE.
▪ Salve o exercício em sua pasta com o nome EX22.

20
QUESTÃO 23

Digite a tabela abaixo:

▪ Calcule a coluna Lucro/Prejuízo fazendo a subtração do valor de compras

com o valor de vendas.
▪ Calcule a coluna Situação do Mês utilizando a função SE, indicando se é
lucro ou prejuízo.
▪ Calcule a célula maior lucro utilizando a função máximo.
▪ Calcule a célula maior prejuízo utilizando a função mínimo.
▪ Salve seu exercício em sua pasta com o nome EX23.

QUESTÃO 24

• Aplique o formato de número de contabilização com símbolo e 2 casas

decimais nas colunas V. Unitário e V. Final.
• Calcule o valor final.
• Aplique as funções solicitadas.
• Formate sua tabela com cores.
• Salve o exercício em sua pasta com o nome EX24.

21
2. FERRAMENTAS DE BANCO DE DADOS

2.1. CONGELAMENTO DE PAINÉIS

Para manter uma área de uma planilha visível enquanto você rola para outra área da planilha,
é possível bloquear linhas ou colunas específicas em uma área congelando painéis (painel: uma
parte da janela do documento ligada e separada de outras partes por barras verticais ou
horizontais.) ou é possível criar várias áreas de planilha que podem rolar separadamente entre
si dividindo painéis.

Quando você congela painéis, o Microsoft Excel mantém linhas ou colunas específicas visíveis
durante a rolagem na planilha. Por exemplo, se a primeira linha da planilha contiver

rótulos, será possível congelá-la para garantir que os rótulos das colunas permaneçam visíveis
enquanto você rola para baixo na planilha.

Na guia exibição, no grupo Janela, clique na seta abaixo de Congelar Painéis.

Para bloquear somente uma linha, clique em Congelar Linha Superior.

Para bloquear somente uma coluna, clique em Congelar Primeira Coluna.
Para bloquear mais de uma linha ou coluna, ou bloquear tanto linhas quanto colunas ao
mesmo tempo, clique em Congelar Painéis.

2.2. CRIAR FILTROS

O Excel pode ser um grande aliado na hora de organizar informações. É possível criar filtros
que ajudem a organizar a informação de acordo com diferentes critérios. Para entendimento,
insira os valores abaixo na planilha.

22
Selecione a linha 1 e na guia Dados, grupo Classificar e Filtrar clique na opção Filtro

Ao lado de cada título, foi criada uma caixa de combinação, indicada por uma setinha ao lado
do título, onde podemos filtrar os campos de cada coluna. Para isso, é só clicar sobre a seta.

2.3. GRÁFICOS
Expressar números em forma de gráficos é uma das características mais atraentes das planilhas
eletrônicas. Em muitos casos, um gráfico pode sintetizar todo um conceito ou dar uma idéia
precisa e instantânea sobre um determinado assunto que possivelmente exigiria a leitura atenta
de muitas linhas e colunas de números da planilha.

O EXCEL possui uma grande variedade de tipos de gráficos que podem representar os dados
por meio de dezenas de formatos em duas e três dimensões.

2.3.1. CRIANDO GRAFICOS

O EXCEL permite a criação de gráficos na mesma página da planilha atual ou então em outra
página da pasta.

O EXCEL possui vários tipos de gráficos. Cada gráfico possui subtipos ou variações. A
quantidade de subtipos vária de tipo para tipo de gráfico.

23
Veremos agora a criação de um gráfico na mesma página da planilha. Para criar um gráfico,
você deve selecionar previamente a área de dados da planilha que será representada pelo
gráfico. Em nosso exemplo, a série que será representada está na faixa B3:E7. Após selecionar

a faixa, selecione a guia Inserir, grupo Gráficos, opção . Inicialmente escolha o

gráfico da coluna 2D.

Em seguida, um gráfico será inserido em sua planilha.

24
 2.3.2. A BARRA DE BOTÕES DE GRÁFICO

Note que após a inserção do gráfico passamos a utilizar as Ferramentas de Gráfico: guia
Design, Layout e Formatar.

2.3.3. ALTERANDO TIPO DE GRÁFICOS

Na maioria dos gráficos, é possível alterar o tipo do gráfico inteiro para proporcionar uma
aparência diferente, ou selecionar um tipo de gráfico diferente para uma única série de dados,
o que torna o gráfico uma combinação.

Para alterar o tipo do gráfico inteiro, clique na área do gráfico para exibir as ferramentas de
gráfico adicionando as guias Design, Layout e Formatar.

Na guia Design, grupo Tipo selecione a opção Alterar Tipo de Gráfico. Na caixa de diálogo
Alterar Tipo de Gráfico, clique em um tipo de gráfico que você deseja usar. Outra maneira
rápida de abrir a caixa de diálogo Alterar Tipo de Gráfico é clicar com o botão direito do mouse
na área do gráfico e em seguida clicar em Alterar Tipo de Gráfico.

25
Na caixa de diálogo Alterar Tipo de Gráfico, clique em um tipo de gráfico que você deseja usar.
A janela da esquerda mostra as categorias de tipo de gráfico; a janela da direita mostra os tipos
de gráficos disponíveis para cada categoria.

2.3.4. ALTERAR O LAYOUT OU O ESTILO DE UM GRÁFICO

Depois de criar um gráfico, é possível alterar sua aparência. Para evitar muita formatação
26
manual, o EXCEL oferece uma série de layouts e estilos rápidos, úteis e predefinidos que talvez
você deseje aplicar ao gráfico. Você pode então personalizar ainda mais o gráfico, alterando
manualmente o layout e o estilo de formatação de elementos individuais do gráfico.

Para alterar o layout de um gráfico, clique no gráfico que você deseja formatar. Isso exibe as
Ferramentas de Gráfico, incluindo as guias Design, Layout e Formatar.

Na guia Design, no grupo Layouts de Gráfico, clique no layout de gráfico que deseja usar.

Para alterar um estilo de gráfico, clique no gráfico que você deseja formatar. Isso exibe as
Ferramentas de Gráfico, incluindo as guias Design, Layout e Formatar.

Na guia Design, no grupo Estilos de Gráfico, clique no estilo de gráfico que deseja usar.

27
2.3.5. TITULO DO GRÁFICO

2.3.6. TITULO DOS EIXOS

28
 2.3.7. RÓTULO DOS DADOS

Dependendo do tipo de gráfico, é interessante colocar rótulos de dados junto às barras ou

fatias do gráfico, para explicitar o seu valor ou percentual.

Para entendimento, transforme o gráfico em uma torta 3D (ver item 7.1.2)

29
Em seguida, na guia Layout, Grupo Rótulos, opção Rótulos de dados, selecione a opção
que melhor adequar.

30
 31

2.4. NOMEANDO INTERVALO DE CÉLULAS OU A CÉLULA.

Para que nomear uma célula ou intervalo de célula? Parafacilitar a criação das fórmulas como
a compreensão da mesma. Nas planilhas grandes, temos grandes dificuldades de ir e voltar
para criação de fórmulas assim usando a célula nomeada fica fácil de lembrar.

Para nomear um intervalo, selecione o intervalo de célula desejado e vá para caixa de nome
e escreva o nome do intervalo.

Caixa de nome

Ou Selecione o intervalo de célula desejado e vá para o menu fórmulas> o ícone definir

nome> e escreva o nome do intervalo e OK.

Nomear célula basta selecionar a célula desejada ir para a caixa de nome digitar o nome da
célula ou ir ao menu fórmula e clicar no ícone definir nome e digitar o nome da célula e OK.

Caso precise apagar ou renomear o nome do intervalo ou da célula click no

menu>fórmula>gerenciador de nomes

31
 32

Exemplo:

As células que estão cinza vamos nomeá-las. A célula (B2) vai se chamar de DESCONTO e a
célula (D2) de ACRÉSCIMO, na célula D9 nomear de subtotal
Seleciona de( B4 até B8) e nomeamos de qtde e de (C4 até C8) e nomeamos de preçounitário,
e de D4 até D8 nomeamos de total
Na célula D4, fazemos a seguinte fórmula =qtde*preçounitário
No subtotal, célula D9 digite =soma(total)
Desconto da compra, na célula D10, fazemos a seguinte fórmula =subtotal*desconto
Acréscimo da compra, na célula D11, fazemos a seguinte fórmula =subtotal*acréscimo
Total da compra: =subtotal-d10+d11

32
 33

2.5. USANDO AS REFERÊNCIAS RELATIVA E ABSOLUTA

No aplicativo Excel é utilizado à referência relativa em toda construção de fórmulas, porque
todas as células no Excel já são referência relativa.

Referência absoluta

Já para a célula ser totalmente referência absoluta tem que colocar o cifrão antes da letra
que identifica a coluna e antes do número que identifica a linha. Para não precisar digitar o
cifrão basta apertar a tecla de função F4 que irá aparecer os cifrões antes da letra (coluna)
e antes do número (linha). Se apertar pela segunda vez só irá aparecer o cifrão antes do
número (linha). Se apertar pela terceira fez só irá aparecer o cifrão antes da letra (coluna).

Exemplo 1: Totalmente referência absoluta

No campo INSS: =b3*$B$11 e no campo IMPOSTO SINDICAL: =b3*$B$12

Ficando assim:

Exemplo 2:Para ser parcialmente referência absoluta (linha).

33
 34

INSS; =b3*b$15 e IMPOSTO SINDICAL: =b3*b$16

Ficando assim:

34
 35

2.6. AUDITORIA DE CÉLULAS

A auditoria de Fórmulas do EXCEL é um grupo de ferramentas responsável pelo

rastreamento precedente e dependente de células que estão sendo utilizadas em fórmulas
criadas nas planilhas. Esta funcionalidade é importante para localizar as células envolventes
em cálculos realizados em uma tabela qualquer, um recurso muito útil a ser utilizado em
tabelas complexas e com muitas fórmulas com a finalidade de não deixar o usuário confuso.

2.7. RASTREAR PRECEDENTES

A função para rastrear células Precedentes é utilizada quando você tem a localização exata
da fórmula e necessita localizar todas as células envolventes utilizadas na fórmula
selecionada. Você deve selecionar a célula que possui o resultado da fórmula e clica na
função Rastrear Precedentes, localizado na Guia Fórmulas, grupo Auditoria de
Fórmulas.

Para visualizar, preencha a planilha conforme abaixo, clique na celular que possui o resultado
da fórmula e clique em Rastrear Precedentes. Então todas as células precedentes do
resultado serão selecionadas na cor Azul e apontarão com uma seta para a fórmula do
resultado.

2.8. RASTREAR DEPENDENTES

A função para rastrear células Dependentes é inversa a anterior, pois é utilizada quando
você tem a localização exata das células e necessita localizar a fórmula que depende da
célula selecionada para gerar o resultado.

No exemplo você seleciona uma célula, clica na função Rastrear Dependentes, localizado
na Guia Fórmulas, grupo Auditoria de Fórmulas.e automaticamente a fórmula que

35
 36

depende da célula selecionada para gerar um resultado será localizada através da seta na
cor Azul.

2.9. REMOVER SETAS

Para retirar as setas apenas clique em Remover Setas localizado na Guia Fórmulas,
grupo Auditoria de Fórmulas, e selecione as setas desejadas a serem removidas.

2.10. MOSTRAR FÓRMULAS

Se você optar por visualizar todas as fórmulas e células envolvidas, basta clicar em
Mostrar Fórmulas localizado na Guia Fórmulas, grupo Auditoria de Fórmulas, e
automaticamente todas aparecerão em destaque.

36
2.11. VERIFICAÇÃO DE ERROS

Esta função é responsável por buscar erros nas tabelas. Útil ao final de qualquer trabalho no
EXCEL. Se não houver erros a mensagem será mostrada. Para executa-la basta acionar a
função Verificação de Erros, localizado na Guia Fórmulas, grupo Auditoria de Fórmulas.

2.12. AVALIAR FÓRMULA

10
 Para utilizá-la selecione uma fórmula de sua tabela e clique na função Avaliar Fórmula
localizada na Guia Fórmulas, grupo Auditoria de Fórmulas. Como no exemplo abaixo
você poderá clicar em Avaliar para confirmar o resultado.

Resultado:

2.13. FILTRO AVANÇADO

Quando o filtro não atender a sua necessidade, como, por exemplo, havendo duas ou mais
condições em um campo, você pode aplicar o filtro avançado localizado na guia Dados,
grupo Classificar e Filtrar, opção Avançado.

Exemplo: Plan1: será considerada a base de dados

11
Adicione duas planilhas: plan2 e plan3

Na plan2, será a planilha de critério, onde serão criados os critérios para o filtro avançado;

Por exemplo, na região, possui a região Sudeste.

A plan3, será a planilha para onde vai ser copiado o conteúdo filtrado; para fazer o filtro
avançado tem que começar nessa planilha, então selecione a plan3 e em seguida acesse a
12
guia Dados, grupo Classificar e Filtrar, opção Avançado. Será exibida a janela como filtro
avançado.

Marque a Ação Copiar para outro local. Em seguida, selecione no campo Intervalo da lista
o intervalo d a planilha que possui todos os dados, neste caso, a plan1.

No campo Intervalo de critérios selecione as células da plan2 que possui os dados do

critério. No campo Copiar para, marque a plan3 a célula a1 e em seguida botão OK.

O resultado na plan3 será com os registros onde a região for igual a Sudeste.

13
3. FUNÇÕES

3.1. RESUMO DE FUNÇÕES

O grupo de ferramentas Biblioteca de Funções, localizada na guia Fórmulas do
EXCEL, apresenta algumas categorias de funções como Financeira, Lógica, Texto e
etc. Porém nem todas as categorias estão expostas no grupo de ferramentas
mencionado. Aproveito para listar todas as categorias com suas respectivas descrições,
veja:

Categoria de Funções
• Cubo: Possui cerca de 7 funções que manipulam dados de procedimentos
armazenados em banco de dados existente.
• Banco de Dados: Possui cerca de 12 funções que auxiliam na manipulação de
dados de uma base existente na planilha, permitindo realizar análises em registros,
tais como média, desvio padrão e outros.
• Engenharia: Possui cerca de 39 funções que permitem realizar conversões e
cálculos com números decimais, hexadecimais e logaritmos.
• Financeiras: Possui cerca de 53 funções que manipulam taxas, valores de
depreciação, pagamento e outras funções similares à calculadora científica e
financeira.
• Informação: Possui cerca de 17 funções que manipulam e retornam expressões de
informações referentes aos dados manipulados em uma ou mais células, como tipo
de erro, conteúdo e tipo de célula.
• Lógicas: Possui cerca de 6 funções que retornam valores lógicos ou booleanos
(verdadeiro/falso) e outros tipos.
• Procura e Referência: Possui cerca de 18 funções que permitem pesquisar dados e
referências em um conjunto de células ou listas.
• Matemáticas e Trigonométricas: Possui cerca de 60 funções que permitem
arredondar valores exibidos em cálculos, seno e co-senos e cálculo raiz quadrada.
• Estatísticas: Possui cerca de 82 funções que permitem realizar análise de dados,
como médias, desvios, variância, etc.
• Texto: Possui cerca de 28 funções que permitem manipular células com conteúdo de
texto, tais como procura e substituição de dados por outro e conversões para
maiúsculas, minúsculas e valores.

14
 • Data e Hora: Possui cerca de 20 funções que manipulam datas e horas, realizando
conversões e localização de ano, mês, dia da semana e minutos.
• Suplemento e Automação: Possui cerca de 5 funções que visam atualizar vínculos,
bibliotecas e procedimentos de consulta a banco de dados.

3.2. EXEMPLOS DE FUNÇÕES

3.2.1. PROCV
Esta função procura um valor na primeira coluna à esquerda de uma tabela e retorna um
valor na mesma linha de uma coluna especificada. Como padrão a tabela deve estar
classificada em ordem crescente.

Sintaxe: =procv(valor procurado;matriz_tabela;núm_indice_coluna;...)

Exemplo: A planilha abaixo foi criada na plan1. A plan1 foi nomeada para de “Vendas”.

Na plan2, vamos criar a seguinte planilha:

Onde o resultado seria:

15
 3.2.2. SE
A função SE retornará um valor se uma condição que você especificou for avaliada como
VERDADEIRO e outro valor se essa condição for avaliada como FALSO.

Na planilha abaixo, devemos criar uma fórmula que analise a média de cada aluno para
retornar o valor “Aprovado” ou “Reprovado” no campo “Situação”. Para que o aluno seja
aprovado, a média deverá ser maior ou igual a 7.

Neste caso, criamos a coluna situação e inserimos o comando SE conforme abaixo:

Teremos o seguinte resultado:

16
 17

3.2.3. SOMASE

Esta função é utilizada para somar as células especificadas por um determinado critério ou
condição.

Sintaxe: =somase(intervalo; critérios; intervalo_soma)

Em seguida, vamos acrescentar as seguintes informações na planilha:

Neste caso, o resultado, será o valor total das vendas de Ana e Jão conforme abaixo:

17
 18

3.2.4. CONT.SE
Esta função calcula o número de células não vazias em um intervalo que corresponde a
uma determinada condição.

Sintaxe: =cont.se(intervalo;”critério”)

Teremos o seguinte resultado:

18
 19

3.2.5. CONT.NUM

Esta função, conta quantas células contêm números e também os números na lista de
argumentos. Use CONT.NÚM para obter o número de entradas em um campo de número
que estão em um intervalo ou matriz de números.

Sintaxe

CONT.NÚM(valor1;valor2;...)

Para visualizar, preencha a planilha conforme abaixo:

Na coluna C2 insira a formula.

19
 20

O resultado será o número de células que contêm números na lista acima (Coluna A)

Outro exemplo é o comando abaixo que exibe o número de células que contêm números
na lista e o valor 2

3.2.6. CONTAR.VAZIO

Conta o número de células vazias no intervalo especificado.

Sintaxe

CONTAR.VAZIO(intervalo)

Para visualização utilizamos os mesmos dados da planilha anterior. Alteraremos apenas a

função para CONTAR.VAZIO.

20
 21

3.2.7. CONT.VALORES

Esta função calcula o número de células não vazias e os valores na lista de argumentos.
Use CONT.VALORES para calcular o número de células com dados em um intervalo ou
matriz.

Sintaxe

CONT.VALORES(valor1;valor2;...)

Para visualização iremos utilizar os mesmos dados da planilha acima. Alteraremos apenas
a função para CONT.VALORES.

A função irá contar o número de células não vazias na lista acima

21
 22

Alteramos a função para agora contar o número de células não vazias na lista acima e o
valor "2"

3.2.8. DATA E HORA

Uma data ou hora atualizada quando a planilha é recalculada ou a pasta de trabalho é aberta
é considerada “dinâmica”, e não estática. Em uma planilha, a maneira mais comum de
retornar uma data ou hora dinâmica em uma célula é usar uma função de planilha.

Lembre-se, antes de digitar uma função em uma célula, você tem que digitar o sinal de igual
(=) para que o Excel entenda que irá digitar uma fórmula.

Funções de Data e Hora para uso no Excel:

=>DATA - retorna uma data a partir da composição de três valores separados (ano, mês e
dia). Veja a figura exemplo:

Fórmula: =DATA(D2;C2;B2)

Se a célula estiver no formato GERAL (ver a opção Formatar Células), será exibido um valor
numérico para a data.

IMPORTANTE: é recomendado usar essa função para passar valores datas para as
demais funções, ou pode ocorrer de sua fórmula não funcionar.

=>AGORA – retorna a data e hora atuais.

Exemplo: AGORA()

=>ANO – retorna o ano de uma data.

Exemplo: ANO(DATA(2008;4;6))
Retorna 2008
=>MÊS – retorna o mês de uma data.
22
 23

Exemplo: MÊS(DATA(2008;4;6))
Retorna 04

=>DIA – retorna o dia de uma data.

Exemplo: DIA(DATA(2008;4;6))
Retorna 06

=>HORA – retorna a hora de uma data com horas.

Exemplo: HORA(“04/06/2008 10:19:23″)
Retorna 10

=>MINUTO – retorna o minuto de uma data com horas.

Exemplo: MINUTO(“04/06/2008 10:19:23″)
Retorna 19

=>SEGUNDO – retorna os segundos de uma data com hora.

Exemplo: SEGUNDO(“04/06/2008 10:19:23″)
Retorna 23

=>DIA.DA.SEMANA – retorna o dia da semana em formato numérico, começando pelo

domingo (1) e terminando sábado (7).
Exemplo: DIA.DA.SEMANA(DATA(2012;5;23))
Retorna 4

=>HOJE – retorna a data atual, sem as horas.

Exemplo: HOJE()
Retorna 23/05/2012

3.2.9. ARRED

A função ARRED arredonda um número para um número especificado de dígitos.

Sintaxe:

ARRED(número, núm_dígitos)

Por exemplo, se a célula A1 contiver 23,7825 e você quiser arredondar esse valor para
duas casas decimais, poderá usar a seguinte fórmula:

=ARRED(A1, 2)

O resultado dessa função é 23,78

Observações:

• Se núm_dígitos for maior do que 0 (zero), o número será arredondado para o

número especificado de casas decimais.
• Se núm_dígitos for 0, o número será arredondado para o inteiro mais próximo.

23
 24

• Se núm_dígitos for menor do que 0, o número será arredondado para a esquerda

da vírgula decimal.
• Para sempre arredondar para cima (longe de zero), use a função
ARREDONDAR.PARA.CIMA.
• Para sempre arredondar para baixo (na direção de zero), use a função
ARREDONDAR.PARA.BAIXO.

• Para arredondar um número para um múltiplo específico (por exemplo, para o mais
próximo de 0,5), use a função MARRED.

Outros exemplos:

3.2.10. ARREDONDAR PARA CIMA

Esta função arredonda um número para cima afastando-o de zero.

Sintaxe

ARREDONDAR.PARA.CIMA(núm, núm_dígitos)

Observações:

• ARREDONDAR.PARA.CIMA funciona como ARRED, com a diferença de sempre

arredondar um número para cima.
• Se núm_dígitos for maior do que 0, então o número será arredondado para cima
pelo número de casas decimais especificado.
• Se núm_dígitos for 0, núm será arredondado para cima até o inteiro mais próximo.

• Se núm_dígitos for menor do que 0, então o número será arredondado para cima à
esquerda da vírgula decimal.

Exemplos:

24
 25

3.2.11. ARREDONDAR PARA BAIXO

Esta função arredonda um número para baixo até zero.

Sintaxe:

ARREDONDAR.PARA.BAIXO(núm, núm_dígitos)

Observações:

• ARREDONDAR.PARA.BAIXO funciona como ARRED, com a diferença de sempre

arredondar um número para baixo.
• Se núm_dígitos for maior do que 0, então o número será arredondado para baixo
pelo número de casas decimais especificado.
• Se núm_dígitos for 0, núm será arredondado para baixo até o inteiro mais próximo.
• Se núm_dígitos for menor do que 0, então o número será arredondado para baixo à
esquerda da vírgula decimal.

Exemplos:

25
 26

3.2.12. ALEATÓRIO
Esta função retorna um número aleatório real maior ou igual a 0 e menor que 1 distribuído
uniformemente. Um novo número aleatório real é retornado toda vez que a planilha é
calculada.

Sintaxe:

ALEATÓRIO()

Observação:

Para gerar um número real aleatório entre a e b, use:

RAND()*(b-a)+a

Exemplo:

4. FUNÇÕES LÓGICAS

4.1. E
– é utilizado para verificar se todos os argumentos (critérios) são verdadeiros, assim
26
 27

retorna o valor verdadeiro se todos forem verdadeiro caso contrário retorna falso.

Sintaxe: =e(1ª lógica;2ª lógica;3ª lógica; ...)

4.2. Ou
- é utilizado para verificar se pelo menos um argumento (critérios) é verdadeiro, assim retorna
o valor verdadeiro caso contrário retorna o valor falso.

Sintaxe:=ou(1ª lógica;2ª lógica;3ª lógica; ...)

4.3. SE
– é utilizado para verificar se a condição foi satisfatória, assim retorna o valor se for
verdadeiro, caso contrário retorna o valor falso.

Sintaxe:=se(teste lógico;se for verdadeiro;se for falso)

Exemplo: a planilha está com as colunas ocultas (b,c,d,e,f,g,h,i).

BOLETIM ESCOLAR
DISCIPLINAS FUNÇÃO E FUNÇÃO OU SITUAÇÃO
PORTUGUÊS =E(I3>=7;H3<=D9) =OU(I3>=7;H3<=D9) =SE(E(I3>=7;H3<=D9);"APROVADO";"REPROVADO")
MATEMÁTICA =E(I4>=7;H4<=D10) =OU(I4>=7;H4<=D10) =SE(E(I4>=7;H4<=D10);"APROVADO";"REPROVADO")
HISTÓRIA =E(I5>=7;H5<=D11) =OU(I5>=7;H5<=D11) =SE(E(I5>=7;H5<=D11);"APROVADO";"REPROVADO")
GEOGRAFIA =E(I6>=7;H6<=D12) =OU(I6>=7;H6<=D12) =SE(E(I6>=7;H6<=D12);"APROVADO";"REPROVADO")

Ficando assim:

CARGA MÁXIMO
DISCIPLINAS
HORÁRIA DEFALTAS

PORTUGUÊS 180 45
MATEMÁTICA 180 45
HISTÓRIA 90 22,5
GEOGRAFIA 90 22,5

5. FUNÇÕES DE PESQUISA E REFERÊNCIA

27
 28

5.1. PROCV
– Procura um valor na primeira coluna a esquerda de uma tabela e retorna um valor na
mesma linha de uma coluna especificada.

Sintaxe: =procv(valor procurado;matriz;nº índice da coluna resposta;verdadeiro ou falso)

Obs.: verdadeiro ou 1 significa valor aproximado;

Falso ou 0 significa valor exato

Exemplo: está planilha foi criada na plan1, a planilha foi nomeada de vendas.

CONTROLE DE PAGAMENTO
IMPOSTO TOTAL DO
NOME SALÁRIO BRUTO INSS SALÁRIO LIQUIDO
código SINDICAL DESCONTO
1 ANA R$ 1.200,00 R$144,00 R$12,00 R$156,00 R$ 1.044,00
2 PEDRO R$ 1.500,00 R$180,00 R$15,00 R$195,00 R$ 1.305,00
3 CARLA R$ 2.000,00 R$240,00 R$20,00 R$260,00 R$ 1.740,00
4 MARIA R$ 600,00 R$72,00 R$6,00 R$78,00 R$ 522,00
SOMA R$ 5.300,00 R$636,00 R$53,00 R$689,00 R$ 4.611,00

Na plan2, vamos criar a seguinte planilha:

CÓDIGO NOME SALÁRIO LIQUIDO

1 =procv(a2;vendas;2;0) =procv(a2;vendas;7;falso)

3 =procv(a3;vendas;2;0) =procv(a3;vendas;7;falso)

Ficando assim.

CÓDIGO NOME SALÁRIO LIQUIDO

1 ANA R$ 1.044,00

3 CARLA R$ 1.740,00

5.2. INDÍCE
- Retorna um valor ou a referência da célula na interseção de uma linha ou coluna
especifica, em um dado intervalo.

Sintaxe:=índice(matriz;numlinha;num coluna)

Matriz – é um intervalo de células ou constante matriz

núm linha – seleciona a linha na matriz ou referência de onde um valor será retornado. Quando não
especificado, númcoluna é necessário.

núm coluna - seleciona a coluna na matriz ou referência de onde um valor será retornado. Quando

28
 29

não especificado, númlinha é necessário.

Exemplo:

Na célula a10 digito vendedor >célula a11 digito Ana> célula b10 digito quantidade>célula c10 digito
total.

Na célula b11 digito a função: =índice(a1:f8;2;5) e na célula c11 digito a função: =índice(a1:f8;2;6)

5.3. CORRESP
- Retorna a posição relativa de um item em uma matriz que corresponda a um valor especifico
em uma ordem especifico.

Sintaxe: =corresp(valor procurado;matrizprocurado;tipodecorrespondência)

Valor procurado – é o valor utilizado para encontrar o valor desejado na matriz

Matriz procurado – é um intervalo contiguo de células que contém valores possíveis de procura,
uma matriz de valores ou uma referência a uma matriz.

Tipodecorrespondência – é um número 0 ou 1, o nº (0) zero é correspondente exato e o nº (1)um

corresponde aproximado.

Exemplo:

29
 30

Na célula a10 digito vendedor >célula a11 digito Ana> célula b10 digito quantidade>célula c10 digito
total.

Na célula b12 digito a função: =corresp(a11;a1:f8;0) e na célula c12 digito a função:

=corresp(a11;a1:f8;0)

Exemplo com índice e corresp - Essa planilha foi construída na plan1:

MATRÍCULAS NOMES CIDADE ANO DESC MENSALIDADE
1 JOÃO SERRA 5 0% R$ 300,00
2 ANA BEATRIZ VILA VELHA 6 0% R$ 350,00
3 MARCIA SERRA 6 SIM R$ 350,00
4 ALINE VITÓRIA 5 0% R$ 300,00
5 SANDRA VITÓRIA 5 0% R$ 300,00
6 PAULA CARIACICA 5 SIM R$ 300,00
7 PATRICIA CARIACICA 6 0% R$ 350,00
8 PAMELA SERRA 6 0% R$ 350,00
9 CINTIA VILA VELHA 7 SIM R$ 400,00
10 ALEXSANDRA CARIACICA 7 0% R$ 400,00
11 ALEXANDRA CARIACICA 7 0% R$ 400,00
12 PEDRO SERRA 6 0% R$ 350,00
13 MARIA VILA VELHA 7 SIM R$ 400,00
14 SONIA VITÓRIA 7 0% R$ 400,00
15 JOSY VITÓRIA 6 0% R$ 350,00
16 ELIANA CARIACICA 5 0% R$ 300,00
17 ROSELY VITÓRIA 6 0% R$ 350,00
18 REGIANE SERRA 7 SIM R$ 400,00
19 LUCIA SERRA 8 0% R$ 450,00
20 LEUSLENE SERRA 9 SIM R$ 500,00
MARIA
21 EDUARDA VILA VELHA 9 0% R$ 500,00
22 CARLA VITÓRIA 9 SIM R$ 500,00
23 BEATRIZ VITÓRIA 8 SIM R$ 450,00

30
 31

24 MIGUEL VITÓRIA 8 0% R$ 450,00

MARIA
25 APARECIDA SERRA 9 0% R$ 500,00

1) Vamos construir a segunda planilha na plan2:

CÓDIGO NOME MENSALIDADE

2 =ÍNDICE(plan1!A1:F50;CORRESP(A2;pla =ÍNDICE(plan1!A1:F50;CORRESP(A2;pla
n1!A1:A50;0);CORRESP(B1;plan1!A1:F1; n1!A1:A50;0);CORRESP(c1;plan1!A1:F1;
0)) 0))

5 =ÍNDICE(plan1!A1:F50;CORRESP(A3;pla =ÍNDICE(plan1!A1:F50;CORRESP(A3;pla
n1!A1:A50;0);CORRESP(B1;plan1!A1:F1; n1!A1:A50;0);CORRESP(c1;plan1!A1:F1;
0)) 0))

Exemplo da função
Na célula B2

=ÍNDICE(plan1!A1:F50;CORRESP(A2;plan1!A1:A50;0);CORRESP(B1;plan1!A1:F1;0))

Na célula B3

=ÍNDICE(plan1!A1:F50;CORRESP(A3;plan1!A1:A50;0);CORRESP(B1;plan1!A1:F1;0))

Na célula C2

=ÍNDICE(plan1!A1:F50;CORRESP(A2;plan1!A1:A50;0);CORRESP(c1;plan1!A1:F1;0))

Na célula C3

=ÍNDICE(plan1!A1:F50;CORRESP(A3;plan1!A1:A50;0);CORRESP(c1;plan1!A1:F1;0))

Ficando assim.

MATRÍCULAS NOMES MENSALIDADE

2 ANA BEATRIZ R$ 350,00
5 SANDRA R$ 300,00

GEOGRAFIA 8,0 1 7,0 6 7,0 4 11 VERDADEIRO DIGITE O DIVISOR

DIVISOR

Éerros - verifica se um valor é um erro(#valor!, #div/0!, #ref!, #núm!, #nome?, #nulo! Ou

#N/D) e retorna o valor verdadeiro ou falso.

31
 32

Sintaxe: =éerros(célula ou valor)

Exemplo:Essa planilha está na plan1, a planilha tem o seguinte nome boletim.

BOLETIM ESCOLAR
TOTAL
1º 2º 3º
DISCIPLINAS FLT FLT FLT DAS MÉDIA
TRIM TRIM TRIM
FALTAS

PORTUGUÊS 8,0 2 7,0 7 6,0 8 17 7,0

MATEMÁTICA 7,5 3 6,0 8 5,5 6 17 6,3

HISTÓRiA 10,0 10 9,0 5 9,0 10 25 9,3

GEOGRAFIA 8,0 1 7,0 6 7,0 4 11 7,3

Façamos outra planilha na plan2:

DISCIPLINAS TOTAL DAS FALTAS MÉDIA

História =se(éerros(procv(a2;boletim;8;0));”Digite a =se(éerros(procv(a2;boletim;9;0));”Digite a

disciplina desejada”;procv(a2;boletim;8;0)) disciplina desejada”;procv(a2;boletim;9;0))

Fincando assim:

DISCIPLINAS TOTAL DAS FALTAS MÉDIA

História 25 9,3

6. FUNÇÕES MATEMÁTICA E TRIGONOMÉTRICA

6.1. SOMASE
– é utilizado para somar as células especificadas por um determinado critério ou condição.

32
 33

Sintaxe: =somase(intervalo;critérios;intervalo soma)

Na célula a11, vamos digitar a seguinte tabela:

Vendedor Total

Paulo =somase(b1:b8;a2;f1:f8)

Ana =somase(b1:b8;a3;f1:f8)

6.2. SOMASES
- é utilizado para somar as células especificadas por vários critérios ou condições.

Sintaxe:=somases(intervalo soma; 1º intervalo critério; 1º critério; 2º intervalo

critério;2º critério;...)

Exemplo:

33
 34

Na célula a15, vamos digitar a seguinte tabela:

VENDEDOR SUL SUDESTE

JOÃO =SOMASES(F1:F8;B1:B8;A2;C1:C8;B1) =SOMASES(F1:F8;B1:B8;A2;C1:C8;C1)

PAULO =SOMASES(F1:F8;B1:B8;A3;C1:C8;B1) =SOMASES(F1:F8;B1:B8;A3;C1:C8;C1)

7. FUNÇÕES DE TEXTO

7.1. &
(E comercial) – permite concatenar os caracteres.

De acordo com a planilha lista de compra o texto que vamos criar explicando o resultado
dela.

Exemplo: =“O subtotal é o valor de ” &subtotal&”, o valor do desconto é

“&descontonacompra&”, o o valor do acréscimo é “&acréscimonacompra&”, ficando assim o
total da compra “&”totaldacompra

Ficando assim o texto:

O subtotal é o valor de 60,8, o valor do desconto é 12,16, ovalor do acréscimo é 0, ficando assim o
total da compra 48,64

Podemos usar com a função se

=se(b2<>””;”O subtotal é o valor de “&subtotal&”, o valor do desconto é

“&descontonacompra&”, ficando assim o total da compra “&”totaldacompra; se(d2<>””;”O
subtotal é o valor de “&subtotal&”, o valor do acréscimo é “&acréscimonacompra&”, ficando
assim o total da compra “&”totaldacompra; ”O subtotal é o valor de “&subtotal&”, ficando
assim o total da compra “&”totaldacompra

Ficando assim o texto:

O subtotal é o valor de 60,8, o valor do desconto é 12,16,ficando assim o total da

compra 48,64

As funções de textos

Esquerda – a sua utilização é para extrair a quantidade de caracteres de um texto da

esquerda para a direita.

Sintaxe: =esquerda(texto;nºcaract)

34
 35

7.2. DIREITA
A sua utilização é para extrai a quantidade de caracteres de um texto da direita para
esquerda

Sintaxe:

=direita(texto;nºcaract)

7.3. EXT.TEXTO:
A sua utilização é para extrair a quantidade de caracteres do meio de um texto Sintaxe:

=ext.texto(texto;nºinicial;nºcaract)

7.4. NÚM.CARACT
É utilizado para retornar o nº de caracteres em uma sequencia de caracteres de texto.

Sintaxe:=núm.caract(texto)

7.5. MAIÚSCULA
Serve para colocar todos os caracteres da célula em maiúsculas Sintaxe:

=maiúscula(texto)

7.6. MINÚSCULA
Serve para colocar todos os caracteres da célula em minúsculas Sintaxe:

=minúscula(texto)

7.7. PRI.MAIÚSCULA
Serve para colocar a 1ª letra de cada palavra da célula em maiúscula Sintaxe:

=pri.maiúscula(texto)

35
 36

8. TABELA DINÂMICA

Um relatório de tabela dinâmica deve ser usado quando você deseja analisar totais
relacionados, especialmente quando tem uma longa lista de valores a serem somados e
deseja comparar vários itens sobre itens sobre cada um. Em uma tabela dinâmica, cada
coluna ou campo nos dados de origem torna-se um campo de tabela dinâmica que resume
várias linhas de informação.

8.1. CRIAR UM RELATÓRIO DE TABELA DINÂMICA

Um relatório de Tabela Dinâmica é um meio interativo de resumir rapidamente grandes

quantidades de dados. Use um relatório de Tabela Dinâmica para analisar detalhadamente
dados numéricos e responder perguntas inesperadas sobre seus dados. Um relatório de
Tabela Dinâmica é projetado especialmente para:

• Consultar grandes quantidades de dados de várias maneiras amigáveis.

• Subtotalizar e agregar dados numéricos, resumir dados por categorias e
subcategorias, bem como criar cálculos e fórmulas personalizados.
• Expandir e recolher níveis de dados para enfocar os resultados e fazer uma busca
detalhada dos dados de resumo das áreas de seu interesse.
• Mover linhas para colunas ou colunas para linhas (ou "dinamizar") para ver resumos
diferentes dos dados de origem.
• Filtrar, classificar, agrupar e formatar condicionalmente o subconjunto de dados mais
útil e interessante para permitir que você se concentre nas informações desejadas.
• Apresentar relatórios online ou impressos, concisos, atraentes e úteis.

Exemplo:

36
 37

Click em qualquer campo da planilha e click no menu inserir>tabela dinâmica, irá aparecer a janela
criar tabela dinâmica, e olhe o intervalo que apareceu se refere a planilha toda então click em OK

Nesse momento o Excel criou uma nova planilha para o gerenciamento da tabela dinâmica, repare
que apareceu nova janela, no lado direitoa janela que aparecerá será lista de campos da tabela
dinâmica e o espaço da construção da tabela dinâmica.

Nessa janela usamos para criar a tabela dinâmica

arrastamos os campos para as áreas abaixo de
acordo com a disposição que desejamos, por
exemplo: arraste a região para o rótulo de linha,
arraste o produto para rótulos de coluna e arraste o
total para valores e observe a lateral a esquerda a
planilha vai se desenhando. Caso deseje desfazer
click nos campos que estão marcados na área dos
campos e pode logo em seguida pode fazer outra
tabela só arrastar novamente. Observação a sua
planilha como base dados continua intacta no mesmo
lugar que foi feita.

Ficando assim:

37
 38

9. GRÁFICO DINÂMICO

Um Gráfico Dinâmico oferece uma representação gráfica dos dados em um relatório de

Tabela Dinâmica. Quando você cria um relatório de Gráfico Dinâmico, os filtros do relatório de
Gráfico Dinâmico são exibidos na área de gráfico para que você possa classificar e filtrar os
dados subjacentes do relatório de Gráfico Dinâmico. Um relatório de Gráfico Dinâmico exibe
série de dados, categorias, marcadores de dados e eixos da mesma forma que os gráficos
padrão. Você também pode alterar o tipo de gráfico e outras opções como os títulos, a
colocação da legenda, os rótulos de dados e o local do gráfico.

Para criar um gráfico dinâmico basta posicionar o cursor em qualquer campo da planilha
desejada e selecionar na guia Inserir, grupo Tabelas a opção Gráfico Dinâmico.

Agora siga os passos da tabela dinâmica. E no final terá o gráfico para ser manipulado
conforme desejar.

38
 39

10. CENÁRIO
Armazena valores de uma ou mais células para exibi-las na planilha, fazendo assim várias projeções
sobre o mesmo assunto. As células que são utilizadas para fazer o cenário necessariamente não
podem ter fórmulas e são chamadas de células variáveis.

Exemplo:

Digite a planilha, observação as fórmulas digitadas não irão aparecer porque o que aparece é o
resultado. Agora temos que analisar o vai variar no cenário, obs. não pode ter fórmula nenhuma
nesses campos que vai variar. Podemos dizer o que vai variar são os percentuais dos meses de
fevereiro a abril e o dólar então vamos clicar no menu dados>teste de hipóteses>gerenciador de
cenários

39
 40

Irá aparecer essa janela então click em adicionar

40
 41

De o nome ao cenário “real” para identificá-lo e click células variáveis e vá até planilha e
selecione os percentuais de fevereiro a abril e o Dólar como já vimos e de OK

Essa janela é do cenário “real” para adicionar outro cenário click em adicionar.

Repete a janela da figura x então de o nome do cenário “pessimista” e dê OK, altere os

valores contidos na janela para 1: 1%, 2: 2%, 3: 2% e 4: 1,20. Para adicionar outro cenário
click em adicionar.

Repete a janela da figura x então de o nome do cenário “otimista” e dê OK, altere os valores
contido na janela para 1: 10%, 2: 12%, 3: 8% e 4: 1,80.

11. ATINGIR METAS

Serve quando queremos saber qual é o valor inicial para se chegar o resultado definido,
sendo consideradas as variáveis.

Para usar essa opção deve-se clicar no menu>dados>teste hipóteses>atingir meta

41
 42

Exemplo:
O seu time deseja contratar um atleta, sendo que ele solicita o salário liquido de 8.000,00,
quanto vai ser o salário bruto para o seu time contratá-lo.

TABELA PARA CONTRATAÇÃO

RUBRICA VALORES
SALÁRIO BRUTO R$ 5.000,00
INSS R$ 600,00
IMPOSTO SINDICAL R$ 50,00
VALE TRANSPORTE R$ 112,30
IRRF R$ 1.350,00
TOTAL DOS DESCONTOS R$ 2.112,30
SALÁRIO LIQUIDO R$ 2.887,70
Obs.: é necessário fazer as fórmulas nos campos INSS, IMPOSTO SINDICAL, VALE
TRANSPORTE, IRRF, TOTAL DOS DESCONTOS E SALÁRIO LIQUIDO.

Definir célula: é célula que tem a fórmula para obter o resultado final, então nessa planilha
B9.

Para Valor: o valor desejado, que é 8000.

Alternando célula: é célula que devo mudar para que o salário liquido seja 8000, então
sabemos que é o salário bruto a célula B3.

12. SUBTOTAL
É totalizar a coluna que deseja em uma lista. Como o subtotal trabalha a seqüência de valor para
totalizar é necessário classificar a coluna da lista que se deseja fazer o subtotal para que a cada
alteração do valor ele façao subtotal.

Exemplo: Digite a planilha na plan1

42
 43

Observe a coluna da data está classificada então pode ser feito o subtotal pela data gerando totais
da quantidade e do total.

Click em qualquer parte da planilha e no menu dados, click o ícone subtotal.

Olhe que aparece uma janela subtotais, onde tem que escolher em cada alteração em: a data, pois
ela que está classificada>usar a função: soma, porque quero somar caso queira outra função
troque>adicionar subtotal a: quantidade e total, onde ele vai fazer os totais> e click no OK.

Para remover o subtotal é só clicar no Menu Dados>subtotal>remover todos

13. VALIDAÇÃO DE DADOS

Serve para definir a entrada de dados na célula ou faixa de células. Pode ser definida entrada
de dados de números inteiros, fracionários, data, hora, lista de dados, um texto com uma
quantidade de caracteres ou personalizar por meio de uma fórmula.

Para acessar o recurso validação de dados ir ao menu>dados e clicar no ícone validação de

dados.

43
 44

13.1. COMO CRIAR UMA LISTA SUSPENSA NO EXCEL

O Excel é uma ótima ferramenta para elaborar e organizar planilhas de controle. É muito
comum a criação de formulários, nos quais o usuário irá inserir os dados que irão compor a
planilha. Porém, ao digitar, existe uma grande possibilidade de informar um valor diferente
daquele que deveria ter sido digitado e comprometer a integridade das informações. Para
prevenir esta situação é necessário limitar ou restringir os valores que serão inseridos. Para
44
 45
isso, deve-se utilizar o recurso Validação de Dados do Excel.

13.2. COMO ADICIONAR UMA LISTA SUSPENSA

O Excel permite criar uma lista suspensa para controlar o tipo de dados ou os valores que os
usuários inserem em uma célula. Por exemplo, se você possui uma lista de funcionários e
pretende atribuir um departamento ao lado de cada nome dos empregados, pode-se utilizar
uma lista suspensa ao invés de digitar os departamentos.

Abaixo está um exemplo de como usar esta opção de validação de dados.

1. Selecione a guia “Plan2” da planilha e digite de acordo com a figura abaixo:

2. Selecione o intervalo A4: A10 e digite Departamento na caixa de nomes conforme a tela
acima.

3. Selecione a guia “Cadastro” da planilha e selecione as células na coluna Departamento

(D5: D15)

45
 46

4. Em seguida clique na guia Dados e selecione a opção Validação de Dados como na tela
abaixo:

5. A seguinte tela será exibida. Na aba Configuraçãoes selecione Lista no campo Permitir.
No campo Fonte insira o nome do intervalo que você criou no passo 1. Para inserir o nome
do intervalo (Departamento), você pode digitar =Departamento ou pressionar F3 e, em
seguida, selecionar Departamento na lista de intervalos nomeados.

46
 47

6. Insira as informações dos funcionários. Para escolher o departamento selecione o menu

drop down na coluna D. Uma lista de departamentos será mostrada. Basta selecionar para
adicionar automaticamente na sua planilha.

Se você tentar inserir um departamento que não faça parte da lista de Departamentos, o
sistema não aceitará e aparecerá a mensagem de erro abaixo:

47
 48

É possível personalizar a mensagem de erro e o tipo de alerta acessando a aba “Alerta de

Erro” na tela de Validação de dados.

Existem diversas situações em que se pode aplicar a validação de dados. Este é um de

uma série de posts em que este assunto será tratado.

14. PROTEGER PLANILHA

No Microsoft Office 2010, você pode usar senhas para ajudar a impedir que outras pessoas
abram ou modifiquem seus documentos, suas pastas de trabalho e suas apresentações. É
importante saber que, se você não se lembrar da senha, a Microsoft não poderá recuperá-
la.

Para proteger sua planilha do EXCEL, use as opções a seguir.

1. Em uma planilha aberta, clique na guia Arquivo. O modo de exibição Backstage é

aberto.
2. No modo de exibição Backstage, clique em Informações.
3. Em Permissões, clique em Proteger Planilha. As seguintes opções são exibidas:

A imagem a seguir é um exemplo das opções de Proteger Planilha.

48
 49

• Marcar como Final Torna o documento somente leitura. Quando uma planilha é
marcada como final, a digitação, a edição de comandos e as marcas de revisão de
texto são desabilitadas ou desativadas, e a planilha se torna somente leitura. O
comando Marcar como Final o ajuda a comunicar que você está compartilhando uma
versão concluída de uma planilha. Ele também ajuda a impedir que revisores ou
leitores façam alterações inadvertidas na planilha.

• Criptografar com Senha Define uma senha para o documento. Quando você
seleciona Criptografar com Senha, a caixa de diálogo Criptografar Documento é
exibida. Na caixa Senha, digite uma senha. Importante: a Microsoft não pode
recuperar senhas perdidas ou esquecidas, por isso, mantenha uma lista de suas
senhas e os nomes de arquivo correspondentes em um local seguro.

• Proteger Planilha Atual Protege a planilha e as células bloqueadas. Com o recurso

Proteger a Planilha Atual, você pode selecionar a proteção por senha e permitir ou
impedir outros usuários de selecionar, formatar, inserir, excluir, classificar ou editar
áreas da planilha.

• Proteger Estrutura da Pasta de Trabalho Protege a estrutura da planilha. Com o

recurso Proteger Estrutura da Pasta de Trabalho, você pode selecionar a proteção por
senha e opções para impedir que os usuários alterem, movam, excluam dados
importantes.

• Restringir Permissão por Pessoas Instala o Gerenciamento de Direitos do Windows

para restringir permissões. Use um Windows Live ID ou uma conta do Microsoft
Windows para restringir permissões. Você pode aplicar permissões por meio de um
modelo usado por sua organização ou adicionar permissões, clicando em Restringir
Acesso.

49
 80

• Adicionar uma Assinatura Digital Adiciona uma assinatura digital visível ou invisível.
As assinaturas digitais autenticam informações digitais, como documentos,
mensagens de email e macros, usando a criptografia do computador. As assinaturas
digitais são criadas digitando uma assinatura ou usando uma imagem de uma
assinatura para estabelecer a autenticidade, a integridade, e não o repúdio.

14.1. PARA PROTEGER AS FÓRMULAS

Serve para proteger as células que possui fórmulas contra a exclusão da mesma. Pode também
proteger a planilha inteira também assim o usuário não consegue usar a planilha.

Para acessar esse recurso ir ao menu>revisão

Exemplo:

Selecione b3 até f7, e click no MENU REVISÂO>permitir que os usuários editem intervalos.

80
 81

15. MACRO NO APLICATIVO EXCEL

O Excel vem para otimizar suas tarefas rotineiras por meio de programação. O macro vem para
automatizar a sua planilha.

Os macros podem ser feitos para formatar a planilha, construção de formulas, transferir dados e etc.

O Macro pode ser feito em duas formas: usando o gravador de macros ou digitando o código assim
usando a janela do VBA. Mas vamos aprender usando o gravador de macros porque não é preciso
saber os códigos da linguagem de programação.

COMO FAZER MACRO NO EXCEL

81
 82

Vamos usar a seguinte planilha:

Plan1: base dados,

Antes de mandar gravar a macro, vamos criar o roteiro da macro.

Roteiro: para retirar a linha de grade da planilha. Exemplo de macro com referência
absoluta.

1- Clique na A1
2- Use CTRL + SHIFT+END
3- Clique no menu exibição
4- Clique em linhas de grade, desmarcando
5- Clique para terminar a gravação da macro

GRAVANDO MACRO

Agora que sabemos o que vai ser feito então Clique no Menu Exibição que irá aparecer a barra de
ferramenta desse menu, escolher o ícone Macros.

Figura 1 - Barra de Ferramenta Menu Exibição

De acordo com a figura 1 Clique na opção gravar macro. Agora irá aparecer a seguinte janela,
como mostra a figura 2

82
 83

Nome da macro não pode ter espaço e deve

começar com letra. Exemplo classificar.
Tecla de atalho deve usar letra maiúscula para
não perde os atalhos do Excel já existente,
usando uma letra maiúscula aparecerá o SHIFT,
exemplo:C, então ficará CTRL+SHIFT+C.
Armazenar macro em pasta de trabalho pessoal
de macro para ser utilizado sempre no seu Excel
e não só no arquivo que foi criado a macro.
Descrição é necessário para que possamos
sempre lembrar o que faz a determinada macro.
Clique no botão OK.

Figura 2 - Gravar Macro

83
 84
Agora deverá seguir o roteiro que foi determinado na página anterior. Depois de fazer tudo
que diz no roteiro clique no botão parar que está na figura 3.

Figura 3 - Parar Gravação

Há só para lembrar esse macro foi usada a referência absoluta, ao executar a macro sempre
vai fazer na mesma posição que foi criada.

Obs.: repare que no ícone Macros tem três opções:

Exibir Macros – mostra todas as macros abertas

Gravar Macro – para construir uma macro.

Usar referências Relativas – essa opção ela varia em duas situações: referência absoluta que já se
encontra e referência relativa quando clicado na opção ela fica com uma borda laranja.

Se for criar uma macro usando a referência absoluta, deverá lembrar que ao executar a macro
sempre vai fazer na mesma posição que foi criado. No caso usando a referência relativa ao executar
a macro sempre vai fazer na posição que se encontra.

O macro está pronto acrescente mais duas vendas e mande executar a macro, pode verificar que
não vai classificar o que foi acrescentado, porque foi utilizada a referência absoluta.

Agora vamos usar a referência relativa e verá que as vendas acrescentadas será classificado.

PARA GRAVAR UM ARQUIVO COM MACRO

Clique no menu arquivo>salvar como, clique em salvar como tipo e escolha a opção pasta
de trabalho habilitada para macro do excel, e digite o nome do arquivo.

84
 85

COMO ATIVAR MACRO

Caso a macro não esteja ativada deverá

clicar no Menu arquivo>opções

85
 Clique no menu central de
confiabilidade, configurações
da central de confiabilidade

Irá aparecer a seguinte

janela e clique no menu
configurações de
macro e na opção
habilitar todas as
macros e clique no
botão OK

CRIANDO MACRO PARA TRANSFERIR DADOS DE UMA PLANILHA PARA OUTRA.

1- Crie a seguinte estrutura da planilha.

Na plan1 iremos criar a seguinte planilha.

NOME CIDADE TELEFONE

Olavo Vila Velha (27) 3200-4356

Na plan2 iremos criar a seguinte planilha.

NOME CIDADE TELEFONE

Vamos transferir da plan2 para plan1.

1º criar o roteiro.

86
 1- Clique na plan2
2- Clique na célula A2
3- SHIFT+ →+→
4- CTRL+C
5- Clique na Plan1
6- Clique no MENU Exibição, Macros, Usar Referências Relativas – para que os dados transferidos
não saia em cima do outro dado contido na plan1
7- Clique na célula A1
8- Aperte END + ↓
9- Aperte END + ↑
10- Aperte ↓
11- CTRL+V
12- HOME
13- Clique na plan2
14- Clique na célula A2
15- SHIFT+ →+→
16- Aperte DEL
17- HOME
18- PARAR A GRAVAÇÃO

Agora é só tentar.

Lembre-se: toda vez que tentar fazer macro olhe a referência relativa se está ativada ou não.

COLOCANDO UM OBJETO COMO BOTÃO PARA ATIVAR A MACRO SEM O ATALHO.

Clique no MENU Inserir, clique no ícone formas e escolha a forma que desejar.

Clique com o botão direito do mouse em cima da imagem e clique em atribuir macro e agora escolha
a macro que deseja quando clicar no objeto ele será executado.

COMO EXCLUIR MACRO

Aperte ALT+F11, que irá aparecer a janela do VBA. Clique no Menu Ferramentas Macros

87
Escolha a macro desejada e clique no botão Excluir, ai é só fechar a janela do VBA que irá
voltar para Microsoft Excel.

88