Exercícios de Trabalho -Parte II
1 - Função if. Cálculo com valores lógicos.
1. (Exemplos)
i) Na células B1 e B2 são inseridas duas notas. Na célula B3 calcula-se a nota final (média).
Na célula B4 aparece a indicação Aprovado ou Reprovado.
ii) Na célula B10 é dado um Valor. Na célula B11 á calculado o respectivo Desconto.
O desconto calcula-se aplicando a percentagam de B7ao valor, mas é limitado ao valor máximo indicado na célula B8.
iii) Na célula B16 é dado um valor e na célula C16 a respectiva moeda (ESC ou EURO). Nas célula B17 e C17 aparece o valor e a moeda convertidos.
iv) Nas células B20 e B21 é dado o resultado de um jogo de futebol entre as equipas A20 e A21. Na célula B23 aparece a indicação Empate de o jogo ficou empatado.
Na célula B24 aparece a indicação Empate ou o nome da equipa vencedora. Na células C20 e C21 aparece o número de pontos atribuído a cada equipa. v) Na célula B28 é indicado um número (entre 1 e 9).
Em baixo, aparece um X na posição correspondente.
2. (Pauta) 1. Preencha manualmente as colunas Nº, Nome, Turma, Teste e Trab. i) Na coluna Final calcule a nota final, com os pesos indicados em N1 e N2.
ii) Na célula N3 aparece a indicação Erro (a vermelho) caso a soma dos pesos não esteja certa. iii) Na coluna Resultado aparece a indicação Aprovado, Reprovado ou Oral (notas 8 ou 9). iv) Na coluna Qual aparece a indicação qualitativa sugerida pelo quadro C20:D24.
v) Na coluna Res_2 indicam-se os Aprovados se houvesse nota mínima 8 no teste e no trabalho. vi) Na coluna Final_2 aparece a nota dos aprovados nas mesmas circunstâncias.
3. (Exemplos) Indique 3 maneiras diferentes de preencher e célula E1. 4. Verifique o resultado das seguintes fórmulas:
i) =E2>E3 ii) =E2=E3 iii) =OR(E1;E2=0;E3<0) iv) =NOT(E1) v) =NOT(E2=E3) vi) =AND(E1;E2=0;E3<0) vii) =AND(E2>5;E3>5) viii) OR(E2>5;E3>5)
5. (Vendas) Preencha as colunas Distrito, V1997 e V1998. i) Na coluna Ind_A assinale o distrito com maiores vendas.
ii) Na coluna Ind_B assinale os distritos com vendas acima da média.
iii) Na coluna Ind_C assinale se o valor de vendas de 1997 para 1998 subiu, desceu ou ficou =. iv) Na coluna Ind_D assinale os distritos com 1º lugar de vendas em qualquer dos anos. v) Na coluna Ind_E assinale os que tenham perdido o 1º lugar de vendas, de 1997 para 1998. 6. Qual o resultado das seguintes fórmulas:
=if(1>2;A3;77) =if(A1>A2;F15+1;1+F15) =if(A1=A2;10;if(A2<>A1;10;20) 7. (Exemplos) Experimente as seguintes configurações de if (que, aliás, nunca usaria): i) =if(E3>7;"X";)
ii) =if(E3>7;"X")
8. Considere a seguinte fórmula: =IF(AND(A1>=8;A1<=9);"Oral";""). i) Escreva uma fórmula equivalente usando OR em vez de AND; ii) Escreva uma fórmula equivalente só com if´s.
2 - Funções de informação.
1. (Exemplos) Experimente as seguintes fórmulas para vários valores de H1: i) =isblank(H1) ii) =isnumber(H1) iii)=istext(H1) iv) =iserror(H1) v) =islogical(H1)
vi) =isnumber(H1+1) vii) =iserror(H1+1) Indique, para cada alínea, uma situação que dê TRUE.
2. Nas células G18 e G19 são indicadas duas notas. Na célula G20 calcula-se a média. Se o aluno não fez uma das provas, em vez da nota indica-se XXX (e vale como 0).
3 - Pesquisa em Tabelas. Função vlookup.
1. (Exemplos) Insira a fórmula F27: =VLOOKUP(F25;Pauta!A2:J16; F26;FALSE)
i) Insira 2002 na célula F25. Experimente a fórmula para vários valores de F26, designadamente:
3 5 12 -1 1 0
ii) Insira 3 na célula F26. Experimente a fórmula para vários valores de F25, designadamente: 1004 2002 2003
2. Insira a fórmula K8: =VLOOKUP(K7;J1:K5;2).
Experimente para vários valores de K7, designadamente:
34 250 251 1090 5
3. Insira a fórmula K18: =VLOOKUP(K17;J12:K15;2). Experimente para vários valores de K17, designadamente:
Pim Alô Zé pim 123
4. Insira a fórmula K26: = VLOOKUP(K25;J21:K23;2).
i) Indique um valor para K25 que faça com que o resultado da fórmula seja B. ii) idem para C.
5. Insira a fórmula F31: =VLOOKUP(F30;Pauta!A2:J16; 3) que nunca usaria. i) Experimente o resultado para valores de F25
1000 2002 1008 ii) "Nunca usaria" porquê ?
6. (Pauta) Na célula C28 é dado o número de um aluno.
Nas células C29 e C30 aparecem o respectivo nome e nota final.
7. Construa de novo a coluna Qual (coluna H da folha de cálculo), desta vez usando vlookup. (Sugestão: comece por construir a tabela F20:G24.)
8. (Exemplos) Nas células J35 e J36 é dado o número de unidades e preço de venda. Na célula J37 calcula-se o valor da venda e na célula J38 o desconto a aplicar.
A taxa de desconto depende o número de unidades, segundo as indicações do quadro H30:J33. 9. (Vendas) Na célula C24 é indicado o rendimento e na célula C25 o número de filhos.
Na célula C27 calcula-se o imposto aplicando a taxa da célula C26.
Esta taxa obtém-se da tabela A18:E22 em função do rendimento e do número de flhos. 10. Na célula H23 é dado o rendimento. Na célula H26 calcula-se o imposto.
As taxas são as indicadas no quadro G17:H21.
O imposto calcula-se da seguinte forma: divide-se o rendimento em duas partes, uma igual ao limite do escalão, outra com o restante; à segunda aplica-se a taxa do escalão do rendimento; à primeira aplica-se a taxa do escalão inferior.
Exemplo: se o rendimento for 25000 divide-se em 20000+5000. Aos "20000" aplica-se 20% e aos "5000" aplica-se 30%.
11. Nas células M23 e M24 são dados um valor e a respectiva moeda.
A moeda indicada pode estar na tabela "Taxas de Conversão" ou "Cotações". Na célula M25 aparece o respectivo valor em Escudos.
4 - Datas e aritmética de datas.
Coloque (ou veja se já tem) o "Regional Settings" em Português. 1. Preencha células com:
30/10/99 10/30/99 30.10.99 30/10
30-10-99 10/99 30/99 30-10
29-2-1999 29-2-2000
i) Quais são datas ?
ii) Indique uma maneira "indesmentível" de ver isso. 2. Suponha que insere uma data numa célua.
i) Com que formato fica a célula.
ii) O que aparece na célula se, depois disso, inserir o número 100. iii) Como faz para ver aparecer o número 100 na célula ?
3. i) A que data correspondem os seguintes números: 35000 200 -1
ii) A que números correspondem as seguintes datas: 1-1-1999 30-1-1900 1-1-2000 4. Suponha que o número 37500 corresponde à data 1-9-2002.
i) a que data correspondem os números 37501 37499 37510 ii) a que números corerspondem das datas 3-9-2002 28-8-2002 5. Suponha que A1 e A2 têm "datas" (ie valores formatados como datas).
i) Insira a fórmula =A1+1. Como fica formatada ? Está bem ? i) Insira a fórmula =A2-A1. Como fica formatada ? Está bem ? 6. (Facturas) Insira duas datas quaisquer nas células C25 e C26.
i) qual o resultado lógico da fórmula =C25+1 ? ii) e da fórmula C25+30 ?
iii) e da fórmula C25-1 ? iv) e da fórmula C26- C25 v) e da fórmula = C26= C25 vi) e da fórmula = C26> C25
7. Qual a diferença entre escrever a data de hoje numa célula e escrever =TODAY() ? 8. Calcule há quantos quantos dias nasceu.
9. (Facturas). Preencha as colunas Nº, Cliente, Emissão, Valor e Recebida. i) Calcule a data "Pagamento" (data de Emissão mais 30 dias).
ii) A coluna "A Receber" indica a data de pagamento das facturas ainda não recebidas. iii) A coluna "Atraso" indica as facturas já recebidas mas com atraso face à data prevista. 10. Das seguintes operações quais fazem sentido e quais não fazem:
- somar uma data e um número; - somar duas datas;
- diferença entre duas datas; - multiplicação de duas datas;
5 - Funções de datas.
1. (Facturas) Insira uma data na célula F22. Intreprete o resultado de:
i) =year(F22) ii) =month(F22) iii) = day(F22)
iv) =date(year(F22);12;25) v) =weekday(F22;2) vi) =weekday(F22) vii) =F22+8-weekday(F17)
3. Escreva uma fórmula que indique se o ano em que estamos é bissexto. 4. Na célula F35 insere-se uma data.
i) na célula F36 aparece o semestre respectivo (1ºS ou 2ºS). ii) na célula F37 aparece o trimestre respectivo.
5. A tabela H36:I39 divide um ano por trimestres.
i) Faça com que a tabela reflita o ano da data indicada em F35. ii) Use esta tabela para resolver a questão 4.ii).
6. Na célula H33 é dada a data de nascimento de uma pessoa.
Na célula H34 aparece a indicação Parabéns se hoje for dia de aniversário. 7. Interprete =ROUNDUP(MONTH(TODAY())/3) & "ºT".
8. No quadro das facturas acrescente uma coluna "FS" (coluna I da folha de cálculo) que avança as datas da coluna "Pagamento" para o próximo dia útil.
9. Nas células H19 e H20 são dados um mês e um ano.
i) No quadro J21:P22 aparece assinalado o primeiro dia do mês.
ii) No quadro J25:P26 aparece toda a primeira linha do calendário desse mês. 10. Na célula F19 é dada uma data. Na célula F20 aparece a respectiva estação do ano.
6 - Formatos numéricos personalizados.
1. (Facturas) Insira a mesma data 1-9-2002 em todas as células H25:H31. Formate para obter cada uma das representações indicadas.
2. Insira o número 120 em todas as células C36:C38.
Formate para obter cada uma das representações indicadas.
3. i) Compare os formatos # e 000. Experimente, por exemplo, com 3 33 e 0.
ii) Compare os formatos #.## e 0.00. Experimente, por exemplo, com 3 0.3 e 0.333 4. Experimente o formato [green]# ; [red]-# ; [blue].
Qual o efeito, por exemplo, para os números 33 0 e -33.
7 - Texto e funções de Texto.
1. (Pauta) Com base nos valores das células C28, C29 e C30 obtenha o texto da célula B32.
2. (Vendas) Acrescente a coluna Comentário (coluna I da folha) para os distritos onde houve subida das vendas de 1997 para 1998.
3. Insira na célula E30 o conteúdo '33 (iniciado com ' ). Mostre que se trata de um texto, apesar de só ter algarismos.
4. Insira a fórmula F28: =if(F27="X";33;"33"). Quais os resultados possíveis ? 5. Preencha as células K31:K33 com os textos indicados.
Verifique e comente o resultado das formulas: i) =SUM(K31:K33)
ii) =K31+K32+K33 6. Ordene os seguintes textos:
7. Insira um texto na célula A30. Verifique o resultado das seguintes fórmulas:
i) =LEN(A30) ii) =LEFT(A30;3) iii) =RIGHT(A30;3)
iv) =MID(A1;4;7) v) =MID(A30;12;7) 8. Na célula C30 é dada uma matrícula.
Na célula C31 aparece a mesma matrícula com a as letras trocadas de posição. 9. Insira a fórmula B37: =FIND(A37;A30).
Experimente colocando diferentes textos em A30.
i) qual o resultado se em A37 colocar um letra que faça parte do texto contido em A30 ? ii) e se for uma sequência de letras ?
iii) e se for uma letra ou sequência que não faça parte do conteúdo de A30 ? 10. Nas células E33:E36 aparecem uma série de números de telefone.
Na coluna do lado aparecem os indicativos (início do número, até ao primeiro ponto ).
8 - Funções SUMIF (revisão)
1. (Pauta)
i) Na célula M20 calcule o número de alunos da turma A1.
ii) Na célula M21 calcule o número de alunos da turma indicada em L21. iii) Em M22 calcule a média das notas da mesma turma.
iv) Construa o quadro K32:M35 que mostra o número de alunos e média por turma. 2. i) Na célula M25 calcule o número de alunos com nota maior que 12.
ii) Qual o resultado da fórmula =">"&L26?
iii) Na célula M26 calcule o número de alunos com nota maior que a indicada em L26.
3. i) Insira na coluna Aux_1 uma fórmula que dê o seguinte resultado: 1 para os alunos da turma A2 que ficaram aprovados; 0 para os outros alunos.
ii) Com base nesta coluna calcule, na célula M28, a % de aprovações na turma A2. 4. Na célula M28 calcule a % de alunos com nota do teste maior que a nota do trabalho.
(Sugestão: construa a coluna auxiliar Aux_2).
5. i) Calcule em I33 o número de alunos com nota >= à indicada em H33.
ii) Identicamente, em I34 calcule o número de alunos com nota >= à indicada em H34. iii) Em I35 calcule o número de alunos com notas no intervalo H33 a H34.
6. Construa o quadro E32:F37 com o número de notas em cada um dos intervalos indicados. 7. Qual o resultado de =COUNTIF(C2:C16;">C") ?
8. (Facturas)
i) Na célula I12 calcule o valor das facturas recebidas.
ii) Na célula I13 calcule o valor das facturas ainda não recebidas.
9. i) Na célula D12 calcule o valor da facturação no 1º trimestre (até 31-3-1999). ii) Na célula D13 calcule o valor da facturação em Março.
10. Na célula B15 á dado um mês.
Nas células A17 e D17 é apresentado o respectivo valor de facturação. 11. Na célula C19 é dado um Cliente e na célula C20 é dado um mês.
Na célula C21 aparece o valor de facturação do cliente nesse mês. (Sugestão: construa a coluna auxiliar Aux).
(II) Exemplos
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 A B C D E F G H I J K L M Teste 9 TRUE 01-01-1999 10 A Trabalho 10 5 20 BFinal 10 7 blank FALSE 250 C
Resultado Aprovado number TRUE 800 D
i) FALSE text FALSE 1001 E
Desconto ii) FALSE error FALSE
Taxa 2,50% iii) TRUE logical FALSE Valor 34
Máximo 250 iv) FALSE num H1+1 TRUE Resultado B
v) TRUE error H1+1 FALSE
Valor 12000 vi) FALSE
Desconto 250 vii) FALSE
Total 11750 viii) TRUE B 10
D 20
i) FALSE K 30
Taxa 200,482 ii) 0 X 40
Dado 1000 ESC
Conversão 4,99 EURO Valor Pim
Teste 16 16 Resultado 30 Trabalho XXX 0 Equipa_A 2 3 Final 8 Equipa_B 1 0 01-01-1999 A 03-03-1999 B 02-02-2000 C Equipa_A 5,1 Valor 2002 Valor
Coluna 3 Resultado #N/A
Resultado Carlos
Marca 3
1 Valor 1000 0-1000 unidades 2% 0 2,00%
2 Resultado #N/A 1000-5000 unidades 3% 1000 3,00%
3 X 5000-15000 unidades 3,5% 5000 3,50% 4 >15000 unidades 3,8% 15000 3,80% 5 6 Unidades 5000 7 Preço Unitário 0,25 8 Valor 1250 9 Desconto 43,75 0,035
(II) Pauta
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 A B C D E F G H I J K L M N ONº Turma Nome Teste Trab Final Resultado Qual Res_2 Final_2 Aux_1 Aux_2 Teste 40%
1009 A3 Alberto 12 6 8 Oral Med Rep 0 1 Trab 60%
1014 A1 Alberto 16 10 12 Ap Suf Ap 12 0 1
1004 A2 Alfredo 7 16 12 Ap Suf Rep 1 0
1001 A1 António 4 10 8 Oral Med Rep 0 0
3003 A1 António 7 6 6 Rep Med Rep 0 1
1005 A1 Bernardo 7 14 11 Ap Suf Rep 0 0
2002 A2 Carlos 17 8 12 Ap Suf Ap 12 1 1 1011 A3 Fernando 12 16 14 Ap Bom Ap 14 0 0 1012 A3 Francisco 14 14 14 Ap Bom Ap 14 0 0 1230 A1 Frederico 15 12 13 Ap Suf Ap 13 0 1 1006 A2 João 9 12 11 Ap Suf Ap 11 1 0 1007 A1 Joaquim 9 10 10 Ap Suf Ap 10 0 0
1008 A2 Jose 4 8 6 Rep Med Rep 0 0
1010 A3 Luis 12 18 16 Ap Bom Ap 16 0 0
1120 A2 Manuel 9 8 8 Oral Med Rep 0 1
3 6
0 a 4 Mau 0 Mau Número de alunos da turma A1 6
5 a 9 Med 5 Med Número de alunos da turma A1 6
10 a 13 Suf 10 Suf Média da nota final da mesma turma 10
14 a 17 Bom 14 Bom
18 a 20 MB 18 MB
Número de alunos com nota > 12 4
Número de alunos com nota > 12 4 >12
Aluno nº 1014 Percentagem de aprovações na turma A2 60%
Nome Alberto 9
Nota final 12 Percentagem nota teste > trabalho 40%
Intervalo Nº notas Turma Nº Alunos Média
0a 4 0 4 11 7 A1 6 10 5 a 9 5 9 15 1 A2 5 9,8 10 a 13 7 13 6 A3 4 11 14 a 16 3 16 18 a 20 0 20 O aluno nº 1014, Alberto, obteve a classificação final de Aprovado com 12 valores.
(II) Vendas
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 A B C D E F G H I J K L M NDistrito V 1997 Ind_A Ind_B V 1998 Ind_C Ind_D Ind_E Comentário
Lisboa 1 000 * 1 002 Subiu * Subiu 0,2% 0,20% EUR 200,482
Porto 350 450 Subiu Subiu 28,57% 28,57% ATS 14,57
Coimbra 890 * 890 = 0,00% BEF 4,9698
Faro 230 230 = 0,00% FIM 33,719
Beja 1 002 * * 1 000 Desceu * * -0,20% FRF 30,563
Viana do Castelo 300 200 Desceu -33,33% DEM 102,505
Guimarães 120 240 Subiu Subiu 100% 100,00% IEP 254,56
Braga 1 001 * 1 001 = 0,00% ITL 0,1 NLG 90,975 Média 612 627 ESP 1,205 Máximo 1 002 1 002 AUD 121,29 CAD 130,595 GRD 0,581 Rendimento 0 JPY 1,816 Rendimento 0 1 2 >2 0 10% 0 ZAR 30,813 0 10% 9% 8% 7% 10000 20% 0 GBP 309,475 10000 20% 19% 18% 17% 20000 30% 10000 USD 190,752 20000 30% 29% 28% 27% 50000 40% 50000 50000 40% 39% 38% 37%
Rendimento 25000 Moeda ITL
Rendimento 15000 Limite 20000 10000 0,2 Valor 20000
Número de filhos 4 3 Diferencial 5000 0,3 Escudos 2000
Taxa 17% Imposto 5500
Imposto 2550 y Cotação #N/A FALSE
33 Conversão 0,1 TRUE
Sim, meu mestre AB-03-05 33 TRUE
15 05-03-AB 33 10
Sim Telefone Indicativo 4 20
tre 96.111.11.1 96 3 Alo 30 , meu m 93.111.11.1 93 3 zeus stre 251.11.11.1 251 4 0 21.111.11.1 21 3 60 meu 6 Número de filhos Taxas de Conversão Cotações
(II) Facturas
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 A B C D E F G H I J K L M N O PNº Cliente Emissão Valor Pagamento Recebida A receber Atraso FS Aux 1 Janeiro C01 01-01-1999 800 31-01-1999 03-03-1999 X 01-02-1999 0 2 Fevereiro C02 01-01-1999 200 31-01-1999 03-03-1999 X 01-02-1999 0 3 Março C01 02-02-1999 1000 04-03-1999 03-03-1999 0 4 Abril C02 03-03-1999 300 02-04-1999 02-04-1999 0 5 Maio C02 01-04-1999 400 01-05-1999 01-05-1999 03-05-1999 400 6 Junho C01 02-04-1999 500 02-05-1999 02-05-1999 03-05-1999 0 7 Julho C02 03-04-1999 2100 03-05-1999 03-05-1999 2100 8 Agosto 9 Setembro 10 Outubro 11 Novembro Valor da facturação no 1º T 2300 Valor das facturas recebidas 2000 12 Dezembro Valor da facturação em Março 300 01-01-1960 Inverno Valor das facturas não recebid 3300
21-03-1960 Primavera Mês 3 01-03-1999 31-03-1999 21-06-1960 Verão
23-09-1960 Outono Facturação no mês de Março 300 21-12-1960 Inverno
Cliente C02 Data 01-06-2001 Mês 3 1 2 3 4 5 6 7
Mês 4 Estação Primavera Ano 2000
Facturação 2500 01-03-2000 2ª 3ª 4ª 5ª 6ª S D
27-03-2001 3 X
i) 2001
Data1 01-01-1999 ii) 3 01-09-2002 2ª 3ª 4ª 5ª 6ª S D
Data2 02-02-1999 iii) 27 Set-2002 1 2 3 4 5
iv) 25-12-2001 2002
i) 02-01-1999 v) 2 02
ii) 31-01-1999 vI) 3 Setembro
iii) 31-12-1998 vii) 01-04-2001 domingo
iv) 32
iv) FALSE 31-03-2000
v) TRUE
Data 25-12-2002
Número 120 Km Semestre 2ºS 01-01-2002 1º T
Escudos 120 Esc Trimestre 4ºT 01-04-2002 2º T
Escudos 120$00 Trimestre 4ºT 01-07-2002 3º T
Trimestre 4º T 01-10-2002 4º T
