Revisão NT Editora e Figuramundo. Projeto Gráfico NT Editora. Editoração Eletrônica NT Editora e Figuramundo. Capa NT Editora e Figuramundo

Autor

Ismael Souza Araujo

Pós-graduado em Gerência de Projetos PMBOK – UNICESP, Graduado em Tecnologia em Segurança da Informação - UNICESP. Professor de concurso da área de informática e con-sultor em EAD. Conteudista de cursos de Educação a Distância.

Revisão

NT Editora e Figuramundo

Projeto Gráfico

NT Editora

Editoração Eletrônica

NT Editora e Figuramundo

Capa

NT Editora e Figuramundo

NT Editora, uma empresa do Grupo NT

SCS Q2 - Bl. D - Salas 307 e 308 - Ed. Oscar Niemeyer

CEP 70316-900 - Brasília - DF

Fone: (61) 3421-9200

sac@grupont.com.br

www.nteditora.com.br e www.grupont.com.br

Planilhas Eletrônicas - Avançado. / NT Editora.

-- Brasília: 2014. 62p. : il. ; 21,0 X 29,7 cm.

ISBN - 978-85-68004-35-7

1. Excel 2007. 2. Planilhas Eletrônicas. 3. Células. 4. Gráficos.

5. Funções. 6. Fórmulas. 7. Listas. 8. Gerenciando planilhas.

9. Formatando planilhas. 10. Planilhas para Web. 11. Alcance

de metas. 12. Macros.

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

LEGENDA

ÍCONES

Prezado(a) aluno(a),

Ao longo dos seus estudos, você encontrará alguns ícones na coluna lateral do material

didático. A presença desses ícones o ajudará a compreender melhor o conteúdo

abor-dado e também como fazer os exercícios propostos. Conheça os ícones logo abaixo:

Saiba Mais

Este ícone apontará para informações complementares sobre o assunto que

você está estudando. Serão curiosidades, temas afins ou exemplos do

cotidi-ano que o ajudarão a fixar o conteúdo estudado.

Importante

O conteúdo indicado com este ícone tem bastante importância para seus

es-tudos. Leia com atenção e, tendo dúvida, pergunte ao seu tutor.

Dicas

Este í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.

4

NT Editora

Sumário

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

Objetivos ...7

1.1 Funções financeiras mais comuns ...7

1.2 Funções para listas ...9

1.3 Usando funções de pesquisa ...14

2. CRIANDO E EDITANDO GRÁFICOS ...19

Objetivos ... 19

2.1 Funções financeiras mais comuns ...19

2.2 Alterando um gráfico pronto ...21

2.3 Ferramentas de desenho ... 23

3. FORMATANDO PLANILHAS ...28

Objetivos ... 28

3.1. Formatação de planilhas ... 28

3.2 Alterando o tipo de dados ... 30

3.3 Utilizando quebra de linhas ...31

3.4 Usando a ferramenta Pincel ...32

4. GERENCIAMENTO DE PLANILHAS ...35

Objetivos ... 35

Planilhas Eletrônicas - Avançado

5

5. GERENCIANDO LISTAS ... 40

Objetivos ...40

5.1 Visualizando listas ...40

5.2 Classificando e obtendo informações de dados ...42

5.3 Filtrando dados em listas ...44

6. O EXCEL E A WEB ... 48

Objetivos ...48

6.1 Criando hiperlinks ...48

6.2 Criando arquivos para a Web ...49

7. ATINGINDO META E CRIANDO MACROS ... 53

Objetivos ...53

7.1 Usando o recurso Atingir meta ...53

7.2 Protegendo sua planilha ...56

Planilhas Eletrônicas - Avançado

7

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados .

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 comuns

O 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

8

NT Editora

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados . 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.

Planilhas Eletrônicas - Avançado

9

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados . • 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

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados . • 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. • Clique na célula E4. • 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>.

Planilhas Eletrônicas - Avançado

11

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados . • 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

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados . • 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. • Clique na célula E15. 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.

Planilhas Eletrônicas - Avançado

13

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados . • 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

SUMÁRIO

Repr odução pr oibida. C op yr igh t © NT E dit or a. Todos os dir eit os r eser vados .

1.3 Usando funções de pesquisa

Agora 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.