Roteiro Aula 15 excel 2010-1

Prévia do material em texto

50 
 Roteiro Aula 15 
 Formatação Condicional 
 Função ARRED 
 Função INT 
 Planilhas 3D 
 
 
- Abrir arquivo Aula15.xlsx 
 
Funções 
Português Inglês 
SOMA SUM 
MÉDIA AVERAGE 
MIN MIN 
MAX MAX 
CONTAR NÚMEROS COUNT 
SOMASE SUMIF 
SE IF 
PROCV VLOOKUP 
PROCH HLOOKUP 
ARRED ROUND 
INT INT 
 
 
1- Formatação Condicional. 
- Aplica formatações automaticamente de acordo com o conteúdo das células. 
 
Guia Início / Estilo / Formatação Condicional 
 
 
 
 
51 
 Realçar Regras das Células 
 
 
 
 
 
 Regras de Primeiros/Últimos 
 
 
 
 
 
 
 
 
52 
 Barras de Dados 
 
 
 
 
 
 Escalas de Cor 
 
 
 
 
 
 
 
 
53 
 Conjuntos de Ícones 
 
- Planilha 1 
 
Sempre selecione o conjunto de dados que serão considerados na Formatação 
Condicional. (D9:D16) 
 
(CONDIÇÃO 1): 
- Formatar os tempos que estiverem acima do índice da seguinte maneira 
- Cor vermelha do texto e negrito. 
 
(CONDIÇÃO 2): 
- Formatar os tempos que estiverem abaixo do índice da seguinte maneira 
- Cor verde do texto e negrito. 
 
 
 
01:48:00 minutos
cinco barra escalas conjuntos 
Número do Atleta Raia Tempo últimos de dados de cor de ícones
6 01:48:56 1:48:56 1:48:56 1:48:56 1:48:56
7 01:58:34 1:58:34 1:58:34 1:58:34 1:58:34
2 01:46:00 1:46:00 1:46:00 1:46:00 1:46:00
3 01:47:45 1:47:45 1:47:45 1:47:45 1:47:45
1 01:46:29 1:46:29 1:46:29 1:46:29 1:46:29
8 01:47:38 1:47:38 1:47:38 1:47:38 1:47:38
4 01:48:21 1:48:21 1:48:21 1:48:21 1:48:21
5 01:49:15 1:49:15 1:49:15 1:49:15 1:49:158
Atletismo
Prova: 800m rasos
Índice a ser atingido:
1
2
3
4
5
6
7
 
 
54 
- Planilha 2 
- Na coluna IMC é utilizada a seguinte fórmula: 
 
2altura
peso
IMC 
 
 
- Na Coluna Resultado utilize a função PROCV (VLOOKUP) e busque o resultado 
na tabela auxiliar. Nessa coluna deve estar indicado resultado equivalente ao da Tabela 
Auxiliar. 
 
- Na Coluna Regime utilize a função SE (IF) para informar se a pessoa precisa SIM 
ou NÃO fazer regime de acordo com os valores da coluna IMC Indivíduo. 
 
- Se IMC >= 25 então SIM, caso contrário NÃO. 
 
- Na Coluna Regime utilize a Formatação Condicional de tal forma que o resultado 
que indica SIM seja formatado na cor vermelha e negrito e da mesma forma se o 
resultado for NÃO a formatação deve ser VERDE e negrito. 
 
 
 
 
 
 
 
Nome Idade Altura Peso
IMC
Individuo
Resultado
Regime
(Sim/Não)
Roberto 32 1,85 95,4 27,87 Sobrepeso SIM
Claudia 25 1,61 55,3 21,33 Normal NÃO SIM
Sonia 43 1,58 76,9 30,80 Obeso SIM NÃO
Sandra 12 1,52 46,2 20,00 Normal NÃO
Telma 29 1,74 75,1 24,81 Normal NÃO
Joaquim 18 1,73 55,7 18,61 Muito Magro NÃO
Alberto 38 1,68 89,6 31,75 Obeso SIM
Wilson 23 1,91 90,5 24,81 Normal NÃO
Bernardo 45 1,82 85,8 25,90 Sobrepeso SIM
Mauricio 56 1,65 110,6 40,62 Obesidade Grave SIM
peso
altura
2
Regime? IMC Resultado
0 Muito Magro
19 Normal
25 Sobrepeso
30 Obeso
40 Obesidade Grave
Cálculo de Índice de Massa Corpórea (IMC)
TABELA AUXILIAR
NÃO
SIM
Formatação 
Condicional
 
 
55 
 
- Planilha 3 
 
- Calcular com a função MÉDIA ( ou AVERAGE) as médias mensais de MAR, ABR, 
MAI, JUN. 
- Calcular a coluna MÉDIA SF de cada aluno com a função MÉDIA (AVERAGE). 
 
=MÉDIA(C5:F5) 
 
- Calcular a Coluna ARREDONDAR utilizando a função ARRED (ou ROUND) e 
arredondar para uma casa decimal os valores da coluna MÉDIA. 
 
=ARRED(G5;1) 
 
 
Função INT retorna o valor inteiro de um número. 
 
Coluna Ajuste 0,5 
Considerar a regra de ajuste 0,5 dos valores da coluna ARREDONDAR. 
 
 Se a parte fracionária da nota for maior que meio ponto (0,5) então o valor da nota 
será a parte inteira mais 1. 
 Se a parte fracionária for igual a (0,5), fica inalterada. 
 Se a parte fracionária for menor que meio ponto (0,5) o valor da nota fica sendo 
somente a parte inteira da nota. 
 
 
=SE ( H5- INT(H5)>0,5 ; 
 
INT(H5)+1 ; 
 
 SE ( H5-INT(H5)=0,5 ; 
 
 H5 ; 
 INT(H5) ) 
) 
ARREDONDAMENTO DE NOTAS
ALUNO MAR ABR MAI JUN MÉDIA ARREDONDAR AJUSTE 0,5 AJUSTE 0,25
João da Silva 5,8 7,9 8,0 9,0 7,67500 7,70000 8,00 7,50
José Alencar 5,1 8,3 7,5 8,5 7,35000 7,40000 7,00 7,50
Manuel de Andrade 6,5 6,5 9,0 8,0 7,50000 7,50000 7,50 7,50
Maria Oliveira 3,2 4,1 6,2 4,4 4,47500 4,50000 4,50 4,50
Rodolfo Carvalho 7,6 6,0 8,9 8,3 7,70000 7,70000 8,00 7,50
0 1 0,5 
 
 
56 
Coluna Ajuste 0,25 
Considerar a regra de ajuste 0,25 dos valores da coluna ARREDONDAR. 
 
 Se a parte fracionária da nota for menor que (0,25) então o valor da nota será 
somente a parte inteira. 
 Se a parte fracionária for menor que (0,75), a nota será a parte inteira mais meio 
ponto. 
 Se a parte fracionária for maior ou igual a (0,75) o valor da nota fica sendo a parte 
inteira da nota mais 1. 
 
 
 
 
 
 
 
=SE ( H5-INT(H5)<0,25 ; 
 
INT(H5) ; 
 
SE ( H5-INT(H5)<0,75 ; 
 
INT(H5)+0,5 ; 
 
 INT(H5)+1 ) ) 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
0 0,25 0,75 1 0,5 
 
 
57 
- Planilha TOTAL 
 
Objetivo: Executar cálculos envolvendo várias planilhas. 
 
 
 
 
 
Ex.: Somar todos os valores das Vendas Líquidas de janeiro. 
 
Há duas maneira de realizar essa tarefa: 
 
 Clicar igual (=) e clicar na aba SÃO PAULO clicar na célula (B3) e clicar mais 
(+), clicar na aba RIO DE JANEIRO clicar na célula (B3) e clicar mais (+), 
clicar na aba BELO HORIZONTE clicar na célula (B3) e clicar ENTER. 
 
 
 
 Acionar a função SOMA e clicar na aba SÃO PAULO clicar na célula (B3), 
pressionar a tecla SHIFT (e manter pressionada) 
em seguida clicar na aba BELO HORIZONTE 
soltar a tecla SHIFT e clicar ENTER. 
 
 
 
Cálculos Complementares 
 
 Lucro Bruto = Vendas líquidas - Custo dos produtos vendidos 
 
 Subtotal é o somatório de: 
Despesas administrativas, 
Despesas financeiras, 
Matérias-primas e 
Tributos. 
 
 Lucro/Prejuízo é obtido fazendo o Lucro bruto menos o Subtotal. 
Janeiro Fevereiro Março Abril Maio Junho
Vendas líquidas 12.050,00 13.702,67 18.776,08 22.310,85 25.816,82 21.948,45
Custo dos produtos vendidos 3.347,37 3.977,00 4.842,00 8.052,68 12.946,70 13.035,54
Lucro bruto 8.702,63 9.725,67 13.934,08 14.258,17 12.870,12 8.912,91
Despesas administrativas 1.160,00 1.898,57 2.377,67 2.737,34 3.906,70 4.046,02
Despesas financeiras 4.251,00 4.903,94 5.792,95 6.776,30 6.903,94 6.445,70
Matérias-primas 657,00 1.303,70 1.521,70 1.952,68 2.043,24 1.267,09
Tributos 75,00 129,00 199,94 393,50 671,97 1.154,02
Subtotal 6.143,00 8.235,21 9.892,26 11.859,82 13.525,85 12.912,83
Lucro/Prejuízo 2.559,63 1.490,46 4.041,82 2.398,35 -655,73 -3.999,92