No episódio de hoje, falei sobre um aplicativo simples de lançamento de notas no Excel. Embora o conceito seja simples, existem várias fórmulas no diário de classe que podem enganá-lo. O diário de classe se parece com isto:
Títulos verticais
Os títulos na linha 2 são virados de lado usando Formatar - Células - Alinhamento. Na guia Alinhamento, altere a orientação para 90 graus.
Pontos Possíveis Linha
É importante ter uma linha na planilha listando o total de pontos possíveis para cada tarefa. Não preencha os pontos possíveis para uma tarefa antes de inserir as notas para essa tarefa. Na primeira figura acima, os pontos possíveis estão na linha 17.
Total de fórmulas na coluna H
Insira uma função = SUM em H4 para totalizar as pontuações do primeiro aluno. Copie esta fórmula para baixo na coluna H para cada aluno e para a linha possível de pontos. O Excel altera automaticamente as referências relativas na fórmula para apontar para a próxima linha, etc.
Complicado: Fórmulas Médias na Coluna I
A fórmula para I4 pode parecer simples. Você pode tentar entrar =H4/H17, mas isso causará um problema.
Quando você copia essa fórmula algumas linhas, ela para de funcionar. Aqui, na linha 6, a fórmula mudou para =H6/H19. É correto que o Excel mudou H4 para H6, mas você deseja que o Excel sempre aponte absolutamente para H17 como o divisor no cálculo.
Volte e edite a fórmula em I4. Você pode selecionar I4 e pressionar F2 ou simplesmente clicar duas vezes em I4.
Quando o ponto de inserção estiver logo após o H17, pressione a tecla F4. O Excel insere cifrões para alterar H17 para $ H $ 17.
O cifrão antes do H indica que, independentemente da direção em que copie a fórmula, você sempre deseja apontar para a coluna H.
O cifrão antes do 17 indica que, independentemente da direção em que copie a fórmula, você sempre deseja apontar para a linha 17.
Isso é chamado de referência absoluta.
Quando você copia esta fórmula para todos os alunos, o Excel sempre divide corretamente pelo total de pontos possíveis na linha 17.
Conversão de porcentagens em notas de letras
Você usará a função PROCV para converter porcentagens em uma nota de letra. Existem duas maneiras de usar a função VLOOKUP, e 90% do tempo neste site, eu uso a versão onde o Excel tem que encontrar uma correspondência exata na tabela de pesquisa. Esta versão, no entanto, usa a versão de VLOOKUP em que a tabela contém intervalos de dados.
Nesta versão, a tabela de pesquisa deve ser classificada em ordem decrescente. Como você pode ver abaixo, isso exige que você pegue a escala de notas da escola e a vire de cabeça para baixo. Na tabela abaixo, a linha 4 está dizendo que qualquer pontuação acima de 0 receberá um F. A linha 5 assume e diz que qualquer pontuação acima de 65% receberá um D. Na linha 6, qualquer coisa em 69% ou acima é um D + . A ideia básica é que o Excel encontre o número igual ou inferior à pontuação do aluno.
- Para construir o VLOOKUP em J4, comece com
=VLOOKUP( - O primeiro argumento é a pontuação do aluno em
I4. - O próximo argumento é o intervalo com sua tabela de pesquisa. Neste exemplo, a tabela está em
L4:M12. - Você deseja que a fórmula sempre aponte para o mesmo intervalo, então pressione a tecla F4 para alterá-la para
$L$4:$M$12. - O argumento final é o número da coluna de sua tabela com a letra da nota. Neste exemplo, é a
2. - A fórmula é
=VLOOKUP(I4,$L$4:$M$12,2).
- Copie a fórmula para todos os alunos.
A versão que usei no programa é pequena - com poucos alunos e apenas algumas tarefas. Você pode baixar esta versão menor para ver como as fórmulas funcionam. Clique com o botão direito em CFHGrades.zip .
