qualquer modo ou meio, seja eletrônico, fotográfico ... · Planilhas Eletrônicas (Avançado) - Excel 2007 / Ismael Souza Araújo – 1. ed. – Brasília: NT Editora, 2014

Copyright © 2014 por NT Editora.Nenhuma parte desta publicação poderá ser reproduzida por

qualquer modo ou meio, seja eletrônico, fotográfico, mecânico ou outros, sem autorização prévia e escrita da NT Editora.

Araújo, Ismael Souza.

Planilhas Eletrônicas (Avançado) - Excel 2007 / Ismael Souza Araújo – 1. ed. – Brasília: NT Editora, 2014.

62 p. il. ; 21,0 X 29,7 cm.

ISBN 978-85-8416-259-8

1. Planilhas. 2. Excel.

I. Título

Design InstrucionalNT Editora

RevisãoNT Editora

Editoração EletrônicaNT Editora

Projeto GráficoNT Editora

CapaNT Editora

IlustraçãoNT Editora

NT Editora, uma empresa do Grupo NT SCS Quadra 2 – Bl. C – 4º andar – Ed. Cedro IICEP 70.302-914 – Brasília – DFFone: (61) [email protected] e www.grupont.com.br

AutorIsmael Souza AraújoGraduado em Tecnologia de Segurança da Informação e pós-graduado em Gerenciamento de Projetos pelas Faculdades Unicesp, Certificado pela ITIL® Foundation Certificate in IT Service Management. É professor de concursos da área de Informática, consultor em EAD e conteudista de cursos de educação a distância. Há mais de 20 anos atua como profissional de informática, período durante o qual atuou nas áreas de suporte, serviços, consultoria, treinamentos e cursos. É autor de livros nas áreas de montagem e configuração de computadores, redes de computadores, informática básica e avançada.

LEGENDA

ÍCONES

Prezado(a) aluno(a),Ao longo dos seus estudos, você encontrará alguns ícones na coluna lateral do materialdidático. A presença desses ícones o ajudará a compreender melhor o conteúdo abordado e a fazer os exercícios propostos. Conheça os ícones logo abaixo:

Saiba maisEsse ícone apontará para informações complementares sobre o assunto que você está estudando. Serão curiosidades, temas afins ou exemplos do cotidiano que o(a) ajudarão a fixar o conteúdo estudado.

ImportanteO conteúdo indicado com esse ícone tem bastante importância para seus estudos. Leia com atenção e, tendo dúvida, pergunte ao seu tutor.

DicasEsse ícone apresenta dicas de estudo.

Exercícios Toda vez que você vir o ícone de exercícios, responda às questões propostas.

Exercícios Ao final das lições, você deverá responder aos exercícios no seu livro.

Bons estudos!

4 NT Editora

Planilhas Eletrônicas (Avançado) - Excel 2007 5

Sumário

1. FUNÇÕES E FÓRMULAS AVANÇADAS ��7

1.1 Funções financeiras mais comuns ......................................................................................71.2 Funções para listas ...................................................................................................................91.3 Usando funções de pesquisa ............................................................................................ 14

2. CRIANDO E EDITANDO GRÁFICOS ..............................................................192.1 Funções financeiras mais comuns ................................................................................... 192.2 Alterando um gráfico pronto ............................................................................................ 212.3 Ferramentas de desenho .................................................................................................... 23

3. FORMATANDO PLANILHAS ..........................................................................283.1. Formatação de planilhas ................................................................................................... 283.2 Alterando o tipo de dados ................................................................................................. 303.3 Utilizando quebra de linhas ............................................................................................. 313.4 Usando a ferramenta Pincel .............................................................................................. 32

4. GERENCIAMENTO DE PLANILHAS ..............................................................354.1 Gerenciando planilhas ......................................................................................................... 35

5. GERENCIANDO LISTAS .................................................................................405.1 Visualizando listas ................................................................................................................. 405.2 Classificando e obtendo informações de dados ........................................................ 425.3 Filtrando dados em listas .................................................................................................... 44

6. O EXCEL E A WEB ...........................................................................................486.1 Criando hiperlinks ................................................................................................................. 486.2 Criando arquivos para a Web ............................................................................................ 49

7. ATINGINDO META E CRIANDO MACROS ....................................................537.1 Usando o recurso Atingir meta ........................................................................................ 537.2 Protegendo sua planilha ..................................................................................................... 567.3 Conhecendo macros ............................................................................................................ 58


Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

Objetivos• Utilizar funções financeiras mais comuns.

• Utilizar funções de pesquisa.

• Obter ajuda sobre uma função qualquer do Excel.

1.1 Funções financeiras mais comunsO Excel possui centenas de funções predefinidas, agora você vai aprender a inserir qualquer

função em suas fórmulas, dependendo de suas necessidades. Dentre as possibilidades de uso, tem-se: Um estatístico pode precisar da função CURT, um auxiliar de escritório pode usar a função PROCV, um engenheiro pode usar a função MATRIZ.MULT e um comerciante pode usar a função TAXA. Conheça agora como utilizar as fórmulas mais comuns entre os usuários do Excel.

Função PGTO:

• Clique no Botão Office e, em seguida, em Abrir.

• Dê um duplo clique no arquivo compras a prazo.

• Vamos supor que já exista uma pasta com arquivos preparados para esse capítulo. Dê um clique duplo no arquivo Compras a prazo

Imagine a seguinte situação: você quer comprar um televisor para a sua casa, mas não sabe se fechará negócio à vista ou a prazo. O uso de planilhas pode contribuir no processo de decisão. A fun-ção PGTO calculará o valor da prestação de uma compra a prazo, de forma que você possa comparar o que pagaria a prazo e à vista.

Os dados numéricos da planilha acima apresentam duas opções: levar a TV à vista por R$ 3.500,00 ou comprá-la a prazo em 12 parcelas com juros de 3,99% ao mês, isto significa que, devido

1. FUNÇÕES E FÓRMULAS AVANÇADAS

8 NT Editora

Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

aos juros, o preço final da TV a prazo ficará maior que o preço à vista. Esta planilha calculará o preço final da TV com os juros. Assim você terá um cenário claro para sua tomada de decisão.

• Clique na célula B4.

• Digite = (igual) para informar ao Excel o uso de fórmulas.

• Abra a lista de opções do campo Caixa de nome e clique em PGTO.

Dentre os argumentos da função PGTO, existem os itens obrigatórios, em negrito, e os opcio-nais. No caso, os itens Taxa, Nper e Vp são obrigatórios, enquanto Vf e Tipo são opcionais.

• Clique na célula B5 para selecionar o valor do campo Taxa e tecle <Tab>.

• Clique na célula B6 para selecionar os 12 meses, correspondente ao campo Nper, e tecle <Tab>.

Lembrando-se de que Vp, ou valor presente, é o preço à vista do televisor, ou seja, o valor que se pagaria agora, no presente.

• Clique na célula B7, que contém o valor do campo Vp.

Observe as áreas destacadas. Talvez você não tenha notado antes, mas nessas regiões são exibi-dos os resultados parciais de qualquer fórmula.

Observe que o número aproximado -372,72 indica o valor de cada prestação a ser paga. Ele é negativo porque você passará a dever, ficando com menos R$ 372,72 no bolso a cada mês. O valor negativo apareceu entre parênteses porque esta fórmula trabalha com dinheiro, e é prática comum entre contadores e outros profissionais que lidam com valores, usar parênteses nesses casos de modo a evitar mal-entendidos com o sinal negativo.


Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

• Não vamos precisar de Vf e Tipo. Tecle <Enter> para encerrar os cálculos.

• De posse deste número, -372,72, fica mais fácil calcular o preço final do produto com os juros: basta multiplicar B4 por B6, isto é, o número de parcelas vezes o valor de cada uma.

• Clique na célula B8 e digite =.

• Clique na célula B4 e digite *.

• Clique na célula B6 e tecle <Enter>.

Conclusão: se a TV custa R$ 3.500,00 à vista, ela custará R$ 4.472,60 quando comprada a pra-zo com juros de 3,99% ao mês, ao longo de 12 meses. Agora, cabe a você decidir: vale a pena pagar quase R$ 1 mil a mais e dividir o pagamento em 12 vezes ou pagar à vista?

Para testar a planilha:

• Clique na célula B6, digite 24 e tecle <Enter>.

• Veja o que aconteceu: Ao aumentar o prazo para 24 meses em vez de 12, o valor da prestação caiu para R$ 229,32, no entanto, o preço final do televisor ficou em R$ 5.503,65.

• Clique no botão Salvar.

• Feche a pasta de trabalho Compras a prazo.

A título de curiosidade, outra função financeira muito usada é VF, ou valor futuro, que é ótima para quem precisa saber quanto depositar na caderneta de poupança todo mês para atingir um de-terminado valor depois de certo tempo.

Com o uso desta fórmula, você pôde perceber que o ideal é juntar o dinheiro antes e só depois comprar um determinado bem; além de não pagar juros, você ainda pode negociar um bom

desconto, pois tem o dinheiro no bolso.

1�2 Funções para listas• Clique no Botão Office e, em seguida, clique no botão Abrir.

• Dê um clique duplo no arquivo História.

O cenário desta planilha é comum na vida de qualquer estudante e professor: cálculo das mé-dias finais, incluindo aprovação ou não na matéria.

Para começar, você vai calcular as médias finais de alguns alunos:

• Clique na célula E4.

• O intervalo C4:D4 contém as notas da aluna Patrícia.

• Digite = (igual) para indicar que será utilizada uma fórmula.

• Abra a lista de opções do campo Caixa de nome e clique em MÉDIA.

O Excel colocou uma sugestão de intervalo, que é exatamente o que contém as duas notas C4:D4.

• Tecle <Enter>.

• Dê um clique duplo na alça de preenchimento da célula E4 para copiar esta fórmula para as demais células desta coluna e calcular a média dos outros alunos.

10 NT Editora

Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

• Clique na célula F4.

Nesta planilha, o campo Status pode possuir um destes dois valores:

• Aprovado, se a média final for maior ou igual ao valor da célula C2.

• Reprovado, se a média final for menor que o valor da célula C2.

Como o conteúdo do campo Status vem de uma decisão, deve-se usar a função SE. Para inserir a função SE de forma alternativa:

• Clique no botão Inserir função (fx) de Barra de fórmulas.

• Clique na função lógica SE.

• Clique em OK.

A função SE possui três argumentos, sendo que o primeiro, Teste_lógico, é obrigatório. Tes-te_lógico é qualquer valor ou expressão que pode ser avaliada como VERDADEIRA ou FALSA.


• Digite >= (maior e igual).

• Clique na célula C2, cujo valor é 5, e depois pressione a tecla <Tab>.

Você inseriu a condição de aprovação: “se a média final for maior ou igual a 5...”. Em termos de células, se o conteúdo da célula E4 for maior ou igual ao conteúdo da célula C2.

E o que acontece se a média final for maior ou igual a C2? A resposta deve estar no campo Va-lor_se_verdadeiro.

• Digite Aprovado e tecle <Tab>.

• No campo Valor_se_falso, digite Reprovado e tecle <Enter>.


Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

• Dê um clique duplo na alça de preenchimento da célula F4 para copiar esta fórmula para as demais células abaixo desta e calcular o status dos outros alunos.

Será que deu certo? Observe a célula F5: a aluna com melhor média desta sala foi reprovada! O que está errado?

• Para entender o que houve, dê um clique na célula F5 e observe a barra de fórmula.

Observe que o teste original era E4>=C2. O que o Excel fez na célula F5? Recalculou a fórmula usando as células seguintes, E5 e C3. Porém, C3 contém um texto, e não a nota mínima de aprovação. Isso gerou o valor incorreto.

O correto seria que todas as fórmulas que calculam os status dos alunos usassem a célula C2 como referência. A solução está em fixar a célula C2 de modo que o Excel não tente ir para a célula C3, C4, etc.

• Tecle <Esc> para desfazer o erro.

• Agora, selecione o intervalo F5:F13 com o mouse.

• Tecle <Delete>.

• Tecle <Esc> para desfazer o erro. Depois selecione o intervalo F5:F13 com o mouse e tecle <Delete>.

• Dê um clique duplo na célula F4.

Para “fixar” a célula C2 nesta fórmula será necessário utilizar “pregos”. No Excel, estes “pregos” são representados pelo cifrão ($), que deve ser colocado antes da letra C e do número 2.

12 NT Editora

Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

• Altere o texto C2 para $C$2 e tecle <Enter>.

• Tecle seta para cima para ir à célula F4.

• Dê um clique duplo na alça de preenchimento da célula F4.

Agora o resultado está certo!

• Observando a Barra de fórmulas, tecle <Tab> três vezes e veja o que muda em cada fórmula.

Viu como a célula C2 ficou travada pelos cifrões? Apenas o valor da célula E muda de uma célula para a de baixo.

Agora você vai inserir outras fórmulas nesta planilha.


Para que o Excel conte o número de alunos desta turma, você será utilizada a função CONT.NÚM.

• Clique no botão Inserir Função (fx) da Barra de fórmulas, caixa Inserir função, na lista Sele-cione uma função, clique em CONT. NÚM e clique em OK.

• Para aumentar sua área de trabalho, clique no botão indicado.

• Selecione o intervalo A4:A13.

• Clique novamente no botão indicado e clique em OK.

• Tecle seta para baixo.

A função CONT. NÚM mostrou que existem 10 matrículas no intervalo A4:A13. Para que o Ex-cel conte o número de alunos aprovados desta turma, será usada a função CONT.SE. Se o Status for “Aprovado”, conta, caso contrário, não conta.

• Digite = (igual) para indicar o uso de uma função.

• Abra a lista de opções do campo Caixa de nome.

E agora? Onde está a função CONT.SE? Este é o momento de descobrir como encontrar todas as outras funções do Excel.

• Clique no item Mais funções. A caixa de diálogo Inserir função mostra, por padrão, as fun-ções mais utilizadas.

• No campo Ou selecione um categoria, escolha Estatística.

• No campo Selecione uma função, clique em CONT.SE e tecle <Enter>.


Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

• Digite F4:F13 e tecle <Tab>.

O critério de contagem é: conte se o status for “Aprovado”.

• Digite “Aprovado” e clique no botão OK.

Observe que a função CONT.SE mostra que 7 alunos foram aprovados.

• Tecle seta para baixo.

• Digite =E15-E16 e tecle <Enter> para calcular o número de reprovados rapidamente, isto é, o número total de alunos menos o número de aprovados.

Da mesma maneira que foi inserida a função CONT.SE, podem ser inseridas as funções MÁXI-MO e MÍNIMO, com o objetivo de conhecer a média máxima e a média mínima da turma, porém, para ganhar tempo, insira estas fórmulas diretamente.

• Digite =máximo(.

• Selecione o intervalo E4:E13.

• Feche o parênteses digitando: ) e tecle <Enter>.

• Agora digite =mínimo(.

• Selecione novamente o intervalo E4:E13, prossiga fechando o parênteses digitando: ) e tecle <Enter>.

Observe que a média máxima foi 10 e a mínima foi 1,5.

• Clique no botão SALVAR.

14 NT Editora

Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

1�3 Usando funções de pesquisaAgora você conhecerá uma função destinada à pesquisa de informações em uma lista: a função

PROCV. O problema a ser resolvido é: “dado um número de matrícula numa lista em ordem crescente, mostre-me o Nome do aluno, a Média final alcançada e se o aluno passou ou não, ou seja, o seu Status”.

Como esta lista é muito pequena, é fácil obter qualquer informação. Para listas maiores, com mais de 100 linhas, o trabalho de encontrar dados da lista torna-se mais complexo. Para facilitar o seu entendimento, continue trabalhando com esta tabela restrita.

• Clique na célula B15, digite 3012 e tecle <Enter>.

O desafio é fazer com que o Excel leia o código de matrícula da célula B15 e retorne o nome, a média final e o status do respectivo aluno. Para resolver este problema, basta usar a função PROCV, que é uma das várias funções de procura.

• Clique no botão Inserir função (fx) da Barra de fórmulas.

• Na caixa Procure por uma função, digite PROCV e clique no botão Ir.

• Para pesquisar uma função no Excel, selecione a função PROCV.

Sempre que tiver dúvida sobre os argumentos de uma função ou desejar saber mais informa-ções sobre ela, utilize a Ajuda do Excel.

• Clique em Ajuda sobre esta função.

Eis as explicações sobre esta função, incluindo exemplos.

• Feche a janela Ajuda do Excel.

• Clique no botão OK.

O elemento Valor_procurado representa a informação de referência para encontrar outras informações. No caso, o conteúdo da célula B15 é a base para encontrar o nome, a média final e o status do aluno.

• Clique na célula B15 e tecle <Tab.


Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

Neste exemplo, a Matriz_tabela é composta pelas linhas 4 a 13 da planilha, desde a coluna A até a coluna F.

• Digite A4:F13 e tecle <Tab> para indicar a seleção de toda a tabela.

Muito bem! Agora falta apenas uma informação para concluir esta fórmula: Núm_índice_co-luna. A tabela que foi selecionada no intervalo A4:F13 tem seis colunas. Assim, para cada coluna há uma informação diferente.

• Como queremos o nome do aluno, digite 2 para indicar a coluna que contém o nome e tecle <Enter>.

Veja que a função retornou o nome da aluna Ana: matrícula é 3012.

A mesma função foi inserida nas células para obtenção da média final e do status do aluno, naturalmente com argumentos diferentes.

• Observando a barra de fórmulas, tecle <seta para baixo> duas vezes.

• Para testar esta planilha. Clique na célula B15, digite 4456 e tecle <Enter>.

• Salve e feche esta pasta de trabalho.

16 NT Editora

Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

Exercícios

Questão 01 – A opção que melhor define o conceito de funções no Excel é:

a) fórmulas matemáticas pré-gravadas no Excel;

b) mecanismos de busca de valores textuais;

c) estruturas que facilitam o trabalho apenas com valores numéricos;

d) fórmulas pré-definidas que efetuam cálculos usando valores específicos, denomina-dos argumentos, em uma determinada ordem ou estrutura.

Questão 02 – Uma das finalidades da função PGTO é:

a) calcular o pagamento de um empréstimo, baseado em pagamentos e uma taxa de juros fixos;

b) fazer o cálculo da taxa de juros mensal com base em um pagamento específico;

c) calcular a taxa de endividamento de uma determinada aplicação;

d) realizar a amortização financeira de uma dívida, expressa em reais.

Questão 03 – A aplicação prática do cifrão ($), quando usado em coordenadas de células, é:

a) atribuir um valor aleatório a uma célula;

b) cálculo de valores “curingas”, apenas usado para definição do valor da coluna;

c) fixar o valor da coluna, linha ou ambos (célula), para que ela não varie em uma deter-minada fórmula ou função;

d) estabelecer o valor monetário do dólar.

Parabéns, você finalizou esta lição!

Agora responda às questões ao lado.

Excelente! Você passou por este rigoroso capítulo! Viu um pouco sobre fórmulas financeiras para listas de pesquisas,

além de aprender como fixar o valor de uma célula em uma fórmula e obter ajuda sobre uma função qualquer. Considere-se um usuário de Excel bem acima da média!


Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

Questão 04 – Em relação à Ajuda online do Excel, é correto afirmar que:

a) ela pode ser acessada a partir de uma janela de uma função;

b) ela não é apresentada no Painel de Tarefas;

c) as pesquisas sobre dúvidas não podem ser realizadas por meio de perguntas, mas somente com valores pré-definidos;

d) mesmo que o computador esteja conectado à Internet não se pode obter esse tipo de ajuda.

Questão 05 – A partir da caixa de diálogo Inserir função, é possível:

a) inserir valores em determinadas células;

b) gravar arquivos especiais do Office;

c) programar o processo de instalação do Excel;

d) inserir a fórmula e os argumentos necessários para o cálculo que se deseja fazer.

Questão 06 – A caixa Argumentos da função PGTO NÃO contém:

a) a taxa de juros;

b) o número de períodos para o pagamento;

c) o valor presente, isto é, o valor à vista;

d) a multa por atraso no pagamento.

Questão 07 – A função CONT. NÚM. tem por finalidade:

a) procurar um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha;

b) somar todos os números em um intervalo de células;

c) calcular o número de células em um intervalo que contém números;

d) retornar o valor máximo de um conjunto de argumentos. Valores lógicos e texto são ignorados.

Questão 08 – A função CONT.SE se aplica:

a) na soma de todos os intervalos de célula;

b) na divisão de duas células;

c) no cálculo de células que contenham apenas números;

d) no cálculo do número de células não-vazias.

18 NT Editora

Repr

oduç

ão p

roib

ida.

Cop

yrig

ht ©

NT

Edito

ra. T

odos

os

dire

itos

rese

rvad

os.

Questão 09 – Na opção Mais funções, é possível acessar:

a) uma função e uma categoria;

b) opções de cores e formatação;

c) o layout da página inicial;

d) a estrutura do gráfico em 3D.

Questão 10 – A caixa de diálogo Inserir função mostra, por padrão:

a) categorias de funções mais recentemente usadas;

b) os estilos de formatação;

c) as funções mais utilizadas;

d) os gráficos mais utilizados.

Documents

qualquer modo ou meio, seja eletrônico, fotográfico ... · Planilhas Eletrônicas (Avançado) - Excel 2007 / Ismael Souza Araújo – 1. ed. – Brasília: NT Editora, 2014