Roteiro Aula 20 excel 2010-1

Prévia do material em texto

102 
 Roteiro Aula 20 
 
- Abrir arquivo Aula20.xlsx 
 
- Planilha 1 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
103 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
104 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
105 
- Planilha 2 
 
Guia Dados / Obter dados externos / Do Access 
 
 Selecionar arquivo Aula20_MDB.MDB 
 
 Onde deseja colocar os dados? Na planilha existente! Célula A3. OK. 
 
 Criar gráfico que mostre as vendas anuais de cada loja. 
 
 
 
 
106 
- Planilha 3 
Objetivo: importar dados de um arquivo TXT e criar um gráfico com o somatório dos 
valores totais de cada produto. (Utilizar a ferramenta SUBTOTAL) 
 
Guia Dados / Obter dados externos / de Texto 
 
 - Selecionar arquivo Aula20_TXT.txt 
 
 - Em seguida, na janela do Assistente de importação de texto, há 03 etapas para 
finalizar o processo de importação do conteúdo do arquivo. 
 
ETAPA 1 
 - Escolha o tipo de campo que melhor descreva os seus dados: 
 Delimitado 
 - Largura fixa 
 - Iniciar importação na linha: (01) 
 - Origem do arquivo: Western European ISO ou Latin 9 (ISO). 
 - Avançar. 
 
ETAPA 2 
 - Delimitadores: 
- Tabulação 
- Ponto e vírgula 
- Vírgula 
- Espaço 
- Outros 
 - Avançar. 
 
ETAPA 3 
 - Formato dos dados da coluna: 
 - Geral 
 - Texto 
 - Data 
 - Não Importar, Ignorar. 
 - Concluir. 
 
Onde você deseja colocar os dados? 
Resposta: Na planilha existente e na célula B2. (Clicar na célula B2) 
 
SubTotal 
Utilizar a ferramenta de Subtotal. 
 
Para que o comando seja acionado, uma célula válida deve estar selecionada, ou 
seja, uma célula abaixo de uma das colunas com dados. 
 
Dados / Estruturas de Tópicos / Subtotal. 
 
 A cada alteração em Ref_Produto 
 Usar função SOMA 
 Adicionar subtotal a PREÇO - OK 
 
 
107 
 
Após calcular os subtotais, minimizar os itens à esquerda da planilha para permitir criar 
gráfico com as colunas Ref_Produto e Preço 
 
Observação: não utilizar o valor Total Geral para a confecção do gráfico. 
 
Criar gráfico de colunas agrupadas 3D e posicionar o gráfico como nova planilha. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
0
500
1000
1500
2000
2500
00
.15
 To
tal
00
.95
 To
tal
01
.21
 To
tal
01
.25
 To
tal
01
.26
 To
tal
01
.27
 To
tal
01
.28
 To
tal
01
.40
 To
tal
01
.41
 To
tal
01
.42
 To
tal
01
.43
 To
tal
01
.44
 To
tal
01
.50
 To
tal
01
.71
 To
tal
01
.81
 To
tal
01
.90
 To
tal
01
.91
 To
tal
01
.95
 To
tal
01
.98
 To
tal
02
.24
 To
tal
02
.25
 To
tal
02
.26
 To
tal
02
.27
 To
tal
02
.35
 To
tal
02
.50
 To
tal
02
.51
 To
tal
02
.60
 To
tal
02
.80
 To
tal
02
.90
 To
tal
02
.91
 To
tal
02
.92
 To
tal
04
.01
 To
tal
04
.02
 To
tal
04
.03
 To
tal
04
.04
 To
tal
04
.05
 To
tal
04
.06
 To
tal
04
.07
 To
tal
04
.08
 To
tal
04
.10
 To
tal
04
.11
 To
tal
04
.12
 To
tal
Preco
Preco
 
 
108 
- Planilha 4 
 
Para encontrar o melhor (menor) preço de cada produto use a função MÍNIMO. 
 
Na coluna “comprar no” utilizar a função SE (IF) para selecionar o nome do supermercado 
de acordo o melhor preço encontrado. 
 
Na coluna “valor da compra” basta multiplicar o melhor preço do produto pela quantidade. 
 
 
 
 
- Planilha 5 
Usar a Função SE (IF). 
 
A coluna estoque é obtida subtraindo os valores da coluna de entrada pelos valores da 
coluna de saída. 
 
Coluna estoque final indica: 
 - Produto em Falta 
 - Produto em Excesso 
 - Estoque Normal 
 
 Se o valor em estoque for menor que o mínimo indicado para o produto, escreva 
Produto em Falta. 
 Se o valor em estoque for maior que o máximo indicado para o produto, escreva 
Produto em Excesso. 
 Se não ocorrer nenhuma das situações acima escreva Estoque Normal. 
 
Utilize a Formatação Condicional na coluna Estoque Final. 
 
PRODUTOS SENDAS CARREFOUR GUANABARA MUNDIAL PÃO DE AÇÚCAR melhor preço comprar no Quantidade valor da compra
ARROZ R$ 1,20 R$ 1,40 R$ 1,60 R$ 1,80 R$ 2,00 R$ 1,20 Sendas 5 R$ 6,00
FEIJAO R$ 2,00 R$ 1,20 R$ 1,60 R$ 1,00 R$ 2,40 R$ 1,00 Mundial 3 R$ 3,00
SAL R$ 1,90 R$ 1,10 R$ 1,20 R$ 0,90 R$ 2,00 R$ 0,90 Mundial 2 R$ 1,80
OVOS R$ 3,40 R$ 2,80 R$ 1,60 R$ 2,50 R$ 2,30 R$ 1,60 Guanabara 1 R$ 1,60
QUEIJO R$ 2,40 R$ 7,00 R$ 6,00 R$ 5,00 R$ 4,00 R$ 2,40 Sendas 3 R$ 7,20
FARINHA R$ 2,40 R$ 3,00 R$ 2,10 R$ 1,00 R$ 0,90 R$ 0,90 Pão de Açúcar 6 R$ 5,40
AÇUCAR R$ 2,30 R$ 1,20 R$ 1,60 R$ 1,00 R$ 2,40 R$ 1,00 Mundial 2 R$ 2,00
FRANGO R$ 10,00 R$ 12,00 R$ 11,00 R$ 11,00 R$ 13,00 R$ 10,00 Sendas 1 R$ 10,00
PEIXE R$ 40,00 R$ 39,00 R$ 41,00 R$ 42,00 R$ 44,00 R$ 39,00 Carrefour 4 R$ 156,00
CARNE R$ 41,00 R$ 54,00 R$ 55,00 R$ 61,00 R$ 36,00 R$ 36,00 Pão de Açúcar 3 R$ 108,00
TOTAIS R$ 106,60 R$ 122,70 R$ 122,70 R$ 127,20 R$ 109,00 R$ 94,00 R$ 301,00
 
 
109 
- Planilha 6 
Usar a Função SE (IF). 
 
- Estilo de Filme: 
Se filme locado for Felicidade, escrever mensagem "drama". 
Se filme locado for Aladin, escrever mensagem "infantil". 
 
- Série: 
Se filme locado for Felicidade, escrever mensagem "ouro". 
Se filme locado for Aladin, escrever mensagem "prata". 
 
- Preço da Diária: 
Se série for "ouro" preço da locação 7,00 (valor em Reais). 
Se série for "prata" preço da locação 6,00 (valor em Reais). 
 
- Multa: 
O número máximo de dias permitidos para locação sem pagar multa é 5 dias. 
 
A coluna MULTA indicará se o cliente terá de pagar multa (SIM) ou se não é o caso de cobrar 
multa (NÃO) de acordo com o número de dias locados. 
 
Se número de dias locados for maior ou igual a 5 dias escreva SIM, senão escreva NÃO. 
 
- Total a pagar: 
Considere a multa quando necessário. 
A multa será de 20% sobre o valor total a pagar. 
 
- Para a coluna ESTILO DO FILME: 
Utilize a Formatação Condicional com cores diferentes para o texto e preenchimento das 
células. 
 
 
 
 
 
 
 
 
 
110 
- Planilha 7 
 
- Considere a seguinte expressão: 
𝑑 = 𝑥2 − 𝑥1 2 + 𝑦2 − 𝑦1 2 
 
- Encontrar o valor d considerando os valores das duas tabelas. 
 
 
- Planilha 8 
 
- Use a função média para calcular a média das notas dos alunos. 
 
- Faça um gráfico de colunas que compare as notas dos alunos, mostradas na tabela.