В сегодняшнем выпуске я рассказал о простом приложении для журнала успеваемости в Excel. Хотя концепция проста, в журнале оценок есть несколько формул, которые могут вас сбить с толку. Журнал успеваемости выглядит так:
Вертикальные заголовки
Заголовки в строке 2 переворачиваются на бок с помощью команды Формат - Ячейки - Выравнивание. На вкладке «Выравнивание» измените ориентацию на 90 градусов.
Возможная строка очков
Важно иметь строку в таблице, в которой указывается общее количество баллов, возможных для каждого задания. Не указывайте возможные баллы за задание, пока не введете оценки за это задание. На первом рисунке выше возможные точки находятся в строке 17.
Итоговые формулы в столбце H
Введите функцию = СУММ в H4, чтобы суммировать баллы для первого учащегося. Скопируйте эту формулу в столбец H для каждого учащегося и для строки возможных баллов. Excel автоматически изменяет относительные ссылки в формуле, чтобы они указывали на следующую строку и т. Д.
Сложно: формулы среднего значения в столбце I
Формула для I4 может показаться простой. Вы можете попробовать войти =H4/H17, но это вызовет проблемы.
Когда вы копируете эту формулу на несколько строк, она перестает работать. Здесь, в строке 6, формула изменилась на =H6/H19. Верно, что Excel изменил H4 на H6, но вы хотите, чтобы Excel всегда абсолютно указывал на H17 в качестве делителя при вычислении.
Вернитесь и отредактируйте формулу в I4. Вы можете выбрать I4 и нажать F2 или просто дважды щелкнуть I4.
Когда точка вставки находится сразу после H17, нажмите клавишу F4. Excel вставляет знаки доллара, чтобы изменить H17 на 17 H $.
Знак доллара перед H означает, что независимо от того, в каком направлении вы копируете формулу, вы всегда хотите указывать на столбец H.
Знак доллара перед цифрой 17 говорит о том, что независимо от того, в каком направлении вы копируете формулу, вы всегда хотите указывать на строку 17.
Это называется абсолютной ссылкой.
Когда вы копируете эту формулу всем ученикам, Excel всегда правильно делит на общее количество баллов, возможное в строке 17.
Преобразование процентов в буквенные оценки
Вы будете использовать функцию ВПР для преобразования процентов в буквенную оценку. Есть два способа использовать функцию ВПР, и в 90% случаев на этом сайте я использую версию, в которой Excel должен найти точное совпадение в таблице поиска. Однако в этой версии используется версия ВПР, в которой таблица содержит диапазоны данных.
В этой версии справочная таблица должна быть отсортирована в порядке возрастания. Как вы можете видеть ниже, для этого необходимо взять шкалу оценок в школе и перевернуть ее. В таблице ниже в строке 4 указано, что любые оценки выше 0 получат F. Строка 5 берет верх и говорит, что любые оценки выше 65% получают D. В строке 6 все, что составляет 69% или выше, является D +. . Основная идея заключается в том, что Excel найдет число, равное или меньшее, чем результат ученика.
- Чтобы создать ВПР в J4, начните с
=VLOOKUP( - Первый аргумент - это баллы ученика
I4. - Следующий аргумент - это диапазон в вашей таблице поиска. В этом примере таблица находится в формате
L4:M12. - Вы хотите, чтобы формула всегда указывала на один и тот же диапазон, поэтому нажмите клавишу F4, чтобы изменить его на
$L$4:$M$12. - Последний аргумент - это номер столбца из вашей таблицы с буквенной оценкой. В этом примере это
2. - Формула такая
=VLOOKUP(I4,$L$4:$M$12,2).
- Скопируйте формулу для всех студентов.
Версия, которую я использовал в шоу, небольшая - с несколькими учениками и всего несколькими заданиями. Вы можете загрузить эту уменьшенную версию, чтобы увидеть, как работают формулы. Щелкните правой кнопкой мыши файл CFHGrades.zip .
