Desafio 1 continuação – parte 3

 

 

Construção da folha 1ºPERÍODO

 

Aula 5

 

Crie uma nova folha no seu livro com o nome 1º Período

 

                Passe à construção de uma folha de cálculo que guarde e ajude a calcular a nota do aluno para cada período. A folha vai ter o seguinte aspeto:

 

 

 

http://www.ticmania.net/excel/exer/excel/aula4/desafio1p2_arquivos/image009.png

 

 

 

Transferir os nomes dos alunos da GRELHA para a folha 1ºPERÍODO

 

Aula 5

 

                Como os nomes dos alunos da turma já estão na folha “pteste” ou “grelha”, não volte a escrever os nomes na folha 1ºPERÍODO. Vai transferir os nomes da “pteste” para a folha 1ºPERÍODO. Vai passar o nome do 1º aluno, nome que vai aparecer na célula C5. Se formos ver, o nome do primeiro aluno está também na célula C5 da folha a que chamou PTESTE, assim a fórmula poderia passar por =pteste!C5.

 

Assim, facilmente se entende que para passar um valor de uma folha para outra basta colocar a seguinte fórmula: =nome_da_folha!endereço_da_célula.

 

http://www.ticmania.net/excel/exer/excel/aula4/desafio1p2_arquivos/image009.png

 

 

 

Calcular o peso das Competências Essenciais

 

Aula 5

 

               

 

 

 

 

 

Como se constata, nesta folha deve lançar as notas que o aluno obteve nos testes (3 no máximo), sendo depois calculada a sua média Total Parcial, com base no peso das “Competências Essenciais”.

 

 

Na célula G5 calcula-se a média que o 1º aluno teve nos testes. Para isso vai usar a função Média, aplicada da seguinte forma: =MÉDIA(D5:F5).

Acontece que ao copiar esta fórmula para os restantes alunos (que no nosso caso pode ir até 25) observará que nas linhas onde não existem alunos vai ter #DIV/0. Então como resolver isto? Penso que concorda que só quer calcular a média caso existam notas atribuídas a “testes”! Assim, use a função CONT.NÚM e terá de usar algo como:

=SE(CONT.NÚM(D5:F5)>0;MÉDIA(D5:F5);""). Agora se copiar a fórmula terá o problema resolvido.

=SE(CONT.NÚM(D5:F5)>0;MÉDIA(D5:F5);"")

Office 2013

=SE(CONTAR(D5:F5)>0;MÉDIA(D5:F5);"")

Office 2010

 

No exemplo a média dos testes (a que chamamos de Competências Essenciais) terá um peso de 70%. Assim, na coluna Total1 quer calcular 70% da média dos testes.

A fórmula a utilizar será, numa primeira instância, =G5*$H$2. Para não variar, se copiar a fórmula terá problema nas linhas que não têm alunos/notas nos testes. Então vamos retocar a fórmula para:

=SE(CONT.NÚM(D5:F5)>0; G5*$H$2;"")

Office 2013

=SE(CONTAR(D5:F5)>0; G5*$H$2;"")

Office 2010

 

Nota: A fórmula tem o endereço H2 com dollar antes de cada coordenada ($H$2). Isto torna a referência “absoluta” o que faz que quando se copia a fórmula esta mantém sempre o endereço inalterável.

 

http://www.ticmania.net/excel/exer/excel/aula4/desafio1p2_arquivos/image009.png

 

 

 

Calcular o peso das Competências Transversais

 

Aula 5

                Agora que já tem a folha a calcular o peso das Competências Essenciais, falta avançar para o cálculo do peso das Competências Transversais, que se entende serem aspetos como Participação, Comportamento, TPC, …

 

 

                Como se pode observar na imagem anterior, reserve espaço para 5 competências transversais, usando no exemplo as competências TPC, Participação e Comportamento.

                No exemplo considere que o TPC tem peso de 5%, a participação 15% e o comportamento 10%, de modo a totalizar o valor percentual de 30% (100-70=30). Portanto na coluna do TPC pode, no exemplo, atribuir cotação máxima de 5 (validar).

               

A parte do cálculo referente às Competências Transversais é feita na coluna Total 2. Veja a fórmula a utilizar para o 1º aluno. Na célula O5 vamos colocar a fórmula: =SE(CONT.NÚM(J5:N5)>0;SOMA(J5:N5);""). Esta fórmula já está “trabalhada” para poder ser copiada para todas as linhas

=SE(CONT.NÚM(J5:N5)>0;SOMA(J5:N5);"")

Office 2013

=SE(CONTAR(J5:N5)>0;SOMA(J5:N5);"")

Office 2010

 

 

 

http://www.ticmania.net/excel/exer/excel/aula4/desafio1p2_arquivos/image009.png

 

 

 

Cálculo da Nota Final (com as Comp. Essenciais e Transversais)

 

Aula 5

Se já calculou os totais parciais resta agora somá-los. Na coluna TOTAL FINAL pretende-se obter a soma dos valores das duas competências. Assim, a fórmula para o 1º aluno será: =SE(H5<>"";(H5+O5)/100;""). Basta copiar até ao fim da grelha

 

 

http://www.ticmania.net/excel/exer/excel/aula4/desafio1p2_arquivos/image009.png

 

 

 

Atribuir o Nível (calculado com base nos pesos das Competências)

 

Aula 5

Agora que tem o cálculo da nota final dos alunos vai tentar obter o Nível para cada aluno (com base no que definiu na folha CONFIGURAR).

Se olhar para a imagem anterior facilmente concluí que o aluno terá Nível 1 se a sua nota for inferior ao nivel2, terá Nível 2 se a sua nota for inferior ao nivel3 …, ou seja precisa de usar a função SE (encadeada).

 

 

Pensando, como sempre, no 1º aluno da grelha, a sua NOTA FINAL está na célula Q5. Então a fórmula a colocar na célula S5 será: =SE(Q5="";"";SE(Q5<nivel2;1;SE(Q5<nivel3;2;SE(Q5<nivel4;3;SE(Q5<nivel5;4;5)))))

 

Na coluna Nível a atribuir e Autoavaliação não vamos colocar fórmulas. São colunas livres e para uso do docente.

 

http://www.ticmania.net/excel/exer/excel/aula4/desafio1p2_arquivos/image009.png

 

 

 

Cálculo das Estatísticas do 1ºPERÍODO

 

Aula 5

image018               

Para calcular as estatísticas do exemplo vai precisar essencialmente, de duas colunas da folha. As colunas a usar são a coluna do Nível Calculado (S) e a coluna da Média (dos testes) (G).

                Assim, o primeiro passo será atribuir nomes adequados a estes blocos.

Vamos atribuir o nome “media” ao bloco G5:G29 e o nome “nivelcalculado” ao bloco S5:S29.

 

 

 

Fórmulas a usar:

 

 

Office 2010

Office 2013

Nº alunos avaliados:

=CONTAR(media)

=CONT.NUM(media)

% de Níveis NEGATIVOS:

=(CONTAR.SE(nivelcalculado;"<3")*100)/M32

=(CONT.SE(nivelcalculado;"<3")*100)/M32

% de Níveis POSITIVOS:

=100-M33

=100-M33

% DE Níveis >= 4:

=(CONTAR.SE(nivelcalculado;">3")*100)/M32

=(CONT.SE(nivelcalculado;">3")*100)/M32

Média de Nível:

=MÉDIA(nivelcalculado)

=MÉDIA(nivelcalculado)

Mediana de Nível:

=MED(nivelcalculado)

=MED(nivelcalculado)

 

 

Notas:

A função CONT.SE permite fazer uma contagem de acordo com um critério.

Sintaxe: =CONT.SE(intervalo;critérios)

 

A função MED devolve a mediana de um bloco de valores

Sintaxe: =MED(intervalo)

 

http://www.ticmania.net/excel/exer/excel/aula4/desafio1p2_arquivos/image009.png