Para calcular as notas de meus alunos de Laboratório, geralmente é necessário de fazer umas operações um pouco mais complexas que uma simples média, de forma que uma simples função do Calc nem sempre resolve a questão.

O problema que proponho aqui consiste em fazer a média das “N” maiores notas de um conjunto de “M” notas de relatórios, utilizando uma planilha Calc, onde “M” deve ser maior ou igual a “N”.

Tome como exemplo as notas dos três alunos hipotéticos, apresentados nas células D5 a I7 da planilha abaixo.

calc-01

O Fulano fez todos os relatórios, tirando as notas (3,5; 6,7; 8,8; 6,4; 7,8; 5,5), Beltrano fez apenas dois relatórios com as notas (7,8; 5,3), enquanto que Sicrano não fez nenhum dos trabalhos. Desta forma as médias destes alunos seriam:

Fulano = \frac{(8,8 + 7,8 + 6,7)}{3} = 7,8

Beltrano = \frac{(7,8 + 5,3)}{3} = 4,4

Sicrano = 0,0

Arredondados com 1 algarismo após a vírgula.

Para fazer isto vou usar a função SOMASE(LISTA; CONDIÇÃO). A lista é o intervalo de dados, neste exemplo de D5 até I5, a condição seria os dados maiores ou igual a 6,7, que é a terceira maior nota dos relatórios. O comando para a nota do Fulano ficaria assim:

=SOMASE(D5:I5; ">=6,7")/3

Isto deve resultar 7,7666666…, que corresponde ao 7,8, quando arredondado. O arredondamento ficará para o final.

O problema ai é que provavelmente em um outro aluno a terceira maior nota não seria, necessariamente, 6,7. Portanto é necessário colar, no lugar do 6,7, o valor da terceira maior nota do aluno em questão. Isto pode ser determinado pela função MAIOR(LISTA; POSIÇÃO). A LISTA será e mesma anterior e a POSIÇÃO, neste caso, será 3. Desta forma, o comando SOMASE pode ser reescrito como:

=SOMASE(D5:I5; ">="&MAIOR(D5:I5; 3)/3)

Para concatenar é necessário usar o operador “&“, que irá unir o resultado da função MAIOR à string “>=”. Para automatizar um pouco mais o processo, troque o 3 por uma célula onde o número de relatórios desejados para a média possa ser alterado. Vou usar a célula B2 para manter o número de relatórios usados para a média. Neste caso a linha de comando deve ficar:

=SOMASE(D5:I5; ">="&MAIOR(D5:I5; B$2))/B$2

O “$” à frente do “2” é para travar a linha 2 da planilha, quanto este comando for replicado (arrastado) para as demais linhas. Esta linha funciona muito bem para os alunos que tiveram 3 ou mais relatórios entregues, mas irá gerar um erro para os que tiverem menos relatórios que o valor em B2, como acontece com o Beltrano.

Este erro ocorre na função MAIOR, pois não existe a terceira maior nota na lista, uma vez que o aluno Beltrano possui apenas duas notas.

Neste caso, será necessário mais duas funções para resolver o problema: CONT.NÚM(LISTA) e um SE(CONDIÇÃO; VALOR_VERDADEIRO; VALOR_FALSO). A primeira função será usada para contar o número de relatórios entregues por cada aluno. Para isto acrescente os comandos abaixo para as células C5, C6, …:

=CONT.NÚM(D5:I5)
=CONT.NÚM(D6:I6)
...

A função CONT.NÚM, na verdade conta as células que possuem números, o que neste caso são as colunas com notas dos relatórios.

Para terminar, aplique um SE(CONDIÇÃO; VALOR_VERDADEIRO; VALOR_FALSO) para determinar o parâmetro POSIÇÃO do comando MAIOR. O SE deve selecionar a contagem do número de relatórios (coluna C) como parâmetro POSIÇÃO, caso este seja menor que B2, ou o número em B2 em caso contrário. O SE para a linha 5 deve ficar assim:

=SE(C5

Utilize isto para substituir o B$2 do SOMASE:


=SOMASE(D5:I5; ">="&MAIOR(D5:I5; SE(C5

O Sicrano ainda vai gerar um erro, pois este não fez nenhum dos relatórios. Neste caso adicione mais um SE para verificar se o número de relatórios é igual a zero. Neste caso a nota será zero. A linha completa seria assim:


=SE(C5=0;0;SOMASE(D5:I5; ">="&MAIOR(D5:I5; SE(C5

Para terminar, utilize a função ARRED(VALOR, POSIÇÃO) para arredondar o resultado com um algarismo após a vírgula.


=ARRED(SE(C5=0;0;SOMASE(D5:I5; ">="&MAIOR(D5:I5; SE(C5

Replique isto para as demais células na coluna B e deve terminar com o planilha assim:


calc-02