Как рассчитать оценки в Excel
Обновлено: 25.06.2024
Когда оценки описываются в числовом виде, Excel предоставляет множество средств для просмотра их характеристик и их изменения.
Статистика оценок
Обычно в журналах оценок хочется видеть простую статистику оценок.
-
Окончательная оценка обычно определяется средневзвешенным или средним значением тестовых оценок и т. д.:
Одной из характеристик функции "Среднее" является то, что она игнорирует пустые ячейки, отличая их от ячейки, данные которой равны нулю.
Часто можно оставить пустую ячейку для учащегося, который не прошел тест, но при подсчете вы хотите, чтобы он считался равным нулю.
= Sqrt((B2^2 + C2^2 + D2^2 + 2 * E2^2)/5)
Это автоматически придаст больше значения более высоким оценкам, которые получает учащийся.
- Подсчет: подсчитывает количество непустых ячеек в списке аргументов.
- Макс.: находит максимальное значение среди своих аргументов.
- Min: находит минимальное значение в своих аргументах.
- Медиана: находит значение, для которого половина аргументов больше, а половина меньше.
- Режим: находит наиболее распространенное значение в своем аргументе.
Иногда может потребоваться округлить результат, например. если оценка выдается учащимся в виде целого числа, но впоследствии используется в другом расчете, который учащийся может проверить.
Тогда можно использовать функцию округления:
Уровни искривления
Если вы выберете кривые оценки, любой численный метод может быть реализован в Excel.
-
Общая идея кривой заключается в том, чтобы добавить баллы к необработанным оценкам, чтобы привести их к заранее определенной шкале значений, согласованной для нескольких тестов или других оценок.
По одной общепринятой шкале оценка в 90-х — "А", в 80-х – "В", в 70 – "С", в 60 – "D" и во всех остальных оценках – "F".< /p>
Затем можно согнуть каждый тест так, чтобы его медиана равнялась 80 (например), и объединить результаты для окончательной оценки.
Если медиана необработанных оценок больше целевого медианы 80 для кривой, кривая обычно вычитает баллы для многих учащихся.
Если кто-то хочет избежать этого, вместо этого можно установить медиану кривой:
Среднее значение кривой задано заранее, и оно выделено жирным шрифтом.
C = T + (медиана(C) - медиана(T))
С добавлением функции округления это выражение выглядит в Excel следующим образом:
Поскольку медиана одинакова для всех оценок, мы не хотим, чтобы Excel обновлял эту ссылку, когда формула кривой повторяется в других ячейках.
Символ $ перед цифрой 4 предотвращает обновление этой ссылки на строку.
В этом случае можно использовать линейную кривую, фиксирующую максимум при Max(C) = 100:
C = (Макс.(C) - Медиана(C))/(Max(T) - Медиана(T)) * (T - Медиана(T)) + Медиана(C)
Это выражение гарантирует, что если T = Max(T), то C = Max(C).
Как всегда, если T = Median(T), то C = Median(C).
В некоторых случаях, особенно если Max(T) увеличивается больше, чем Median(T), эта функция может снизить оценки учащихся, которые ниже медианы.
Поэтому вы можете ограничить дополнительные баллы, добавляемые выше медианы:
Макс.(C) = Мин.(100, Макс.(T) + (медиана(C) - медиана(T)))
С этим ограничением кривая автоматически становится постоянной, когда это необходимо.
С добавлением функции округления в Excel это выглядит так:
Если вам это не нужно, вы можете попробовать нелинейную кривую, отображаемую на графике:
C = T*Median(C)*(Max(T) - Median(T))/
(T*(Median(C)*Max(T)/Max(C) - Median(T) )) + (1 - медиана(C)/макс.(C))*макс.(T)*медиана(T))
(Эта функция кривой взята из Американского журнала физики за 1972 год.)
Обратите внимание, что кривая автоматически дает ноль баллов тем, кто не прошел тест.
С добавлением функции округления в Excel это выглядит так:
Связь между оценками
Результаты учащихся можно сравнивать от теста к тесту с помощью функции корреляции Excel.
-
Одна из причин кривых тестов по отдельности (в отличие от одной большой кривой в конце) заключается в том, что это упрощает сравнение производительности от теста к тесту.
Даже если кривая не выбрана, функция Correl позволяет сравнивать два набора оценок путем расчета статистической корреляции:
Эта функция основана на отклонениях от среднего, поэтому для нее не требуется общая шкала.
Коррел выглядит так в Excel:
Положительная корреляция, близкая к 1, указывает на то, что результаты тестов как группы очень близки друг к другу.
Если корреляция отрицательна, это указывает на то, что между хорошими и плохими учащимися наблюдается резкий скачок.
Вернуть оценки в Blackboard
В этот момент оценки могут быть снова введены в Blackboard.
-
В конце концов оценки должны быть снова введены в Blackboard, чтобы учащиеся могли их видеть.
Excel может экспортировать журнал оценок в совместимый с Blackboard формат под названием "CSV" ("значения, разделенные запятыми").
Blackboard может импортировать файлы CSV по столбцам.
Основные принципы графического представления расчетов, описанных здесь, можно найти в разделе Графики в Excel.
Формула для оценки – это вложенная формула ЕСЛИ, которая проверяет определенные критерии и возвращает конкретную оценку, если они соблюдены. Поиск оценки ученика, вероятно, является основным примером, который все руководства по Excel дают своим ученикам, чтобы объяснить логику логических функций в Excel. ЕСЛИ, И, ИЛИ, ЛОЖЬ, ИСТИНА и ЕСЛИОШИБКА — одни из самых популярных логических функций в Excel.
Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.
В этой статье объясняются различные способы определения оценок по баллам учащихся, уровня эффективности сотрудников, расчета премий и многих других вещей с помощью формулы оценки в Excel.
Как использовать формулу для расчета оценок в Excel?
Давайте разберемся, как использовать формулу для расчета оценок на нескольких примерах.
В каждом примере рассматривается отдельный вариант использования, реализованный с помощью формулы оценки в Excel.
Поиск оценок учащихся: вложенные условия ЕСЛИ
Поиск оценок учащегося — один из лучших примеров для понимания логичности условия ЕСЛИ в Excel. Обратите внимание на приведенную ниже оценку учащихся для этого примера.
Ниже приведены критерии определения оценки.
- Если оценка выше 550, оценка A+.
- Если оценка выше 500, оценка A.
- Если оценка выше 450, оценка B+.
- Если оценка выше 400, оценка B
- Если оценка выше 300, оценка C
- ЕСЛИ оценка не соответствует ни одному из вышеперечисленных условий, оценка будет НЕ ПРОЙДЕНА.
Чтобы найти индивидуальную оценку учащегося, нам нужно проверить все эти условия, и на основе выполнения условий; нам нужно объявить оценки.
Обучение Excel (21 курс, более 9 проектов) 21 онлайн-курс | 9 практических проектов | 110+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,9 (10 153 оценки)
Используя условие ЕСЛИ, мы можем найти оценки здесь. Прежде чем я расскажу вам, как найти оценки, позвольте мне сначала объяснить условие ЕСЛИ.
Логический тест: это аргумент для проверки логики значения.
Значение, если оно истинно: если логический тест верен, каким должен быть желаемый результат.
Значение, если ложно: если логическая проверка ложна, каким должен быть желаемый результат.
Теперь взглянем на простой пример.
Если день равен ВОСКРЕСЕНЬЮ, то цена должна быть 35, иначе нам нужна цена 30.
Шаг 1. Откройте условие ЕСЛИ в ячейке B2.
Шаг 2. Теперь нам нужно выполнить логический тест, т. е. определить, совпадает ли день с ВОСКРЕСЕНЬЕМ или нет. Выберите ячейку A2 и проверьте, соответствует ли она значению ВОСКРЕСЕНЬЕ или нет.
Шаг 3. Если логический тест верен, результат равен 35.
Шаг 4. Если логический тест ложный, результат должен быть 30.
Шаг 5. Закройте скобку и нажмите клавишу Enter.У нас есть результат.
Шаг 6. Теперь мы перетащим эту формулу из ячейки B2 в ячейку B3. Таким образом, мы получим результат, показанный ниже.
Теперь давайте применим эту логику, чтобы найти оценки учащихся с помощью формулы для оценки в Excel.
Шаг 1. Откройте условие ЕСЛИ в ячейке C2. Первый логический тест — если оценка выше 550. Таким образом, проверьте, является ли оценка> 550 или нет, если это значение ИСТИННОГО идентификатора теста должно быть «A».
Шаг 2: Если тест неверен, значение должно быть. Здесь нам нужно проверить еще несколько критериев. Итак, откройте новое условие ЕСЛИ.
Шаг 3. Теперь проверьте второй критерий, т. е. значение > 500. Если этот логический тест верен, результат должен быть «B+».
Шаг 4. Теперь, если оба условия НЕВЕРНЫ, нам нужно проверить третий критерий. Итак, откройте еще одно условие ЕСЛИ и проверьте, равно ли значение> 400 или нет. Если тест ИСТИНЕН, результат должен быть «B».
Шаг 5. Таким же образом примените условия ЕСЛИ ко всем критериям и закройте скобки. Я уже применил все условия ЕСЛИ и получил результаты.
Шаг 6. Теперь мы перетащим эту формулу из ячейки C2 в ячейку C9. Таким образом, мы получим результат, показанный ниже.
Поиск категорий сотрудников: комбинация условий IF и AND
Мы увидели, как использовать условие ЕСЛИ для определения оценок учащихся. Теперь давайте посмотрим на комбинацию условий IF и AND.
У меня есть таблица данных о сотрудниках с указанием их отдела и года работы в компании.
Чтобы найти класс, необходимо использовать следующие критерии: "Если сотрудник работает в отделе продаж и его стаж работы >=5 лет, то сотрудник должен иметь право на продвижение по службе".
Кроме отдела продаж никто не имеет права на продвижение.
Здесь нам нужно проверить два условия одновременно, в отличие от предыдущего, где нам нужно проверить множество критериев по отдельности. Здесь нам может помочь комбинация условий IF и AND.
Функция И проверяет несколько условий одновременно, и если все переданные условия верны, мы получим результат ИСТИНА или ЛОЖЬ.
Шаг 1. Откройте условие ЕСЛИ в ячейке D2.
Шаг 2. Логический тест здесь не единственный; скорее, нам нужно проверить два условия для одного сотрудника. Итак, откройте функцию И внутри условия ЕСЛИ.
Шаг 3. Первый критерий – соответствует ли отдел отделу продаж или нет. И второй тест: составляет ли год службы >=5 лет или нет.
Шаг 4. Поскольку у нас есть только два условия для проверки, закройте квадратную скобку функции И. Если логическая проверка с помощью функции И имеет значение ИСТИНА, то результатом должно быть право на повышение, а если логическая проверка с помощью функции И имеет значение ЛОЖЬ, то результатом должно быть несоответствие требованиям к повышению. Закройте скобку и нажмите клавишу Enter, чтобы завершить формулу.
Шаг 5. Теперь мы перетащим эту формулу из ячейки D2 в ячейку D9. Таким образом, мы получим результат, показанный ниже.
Что нужно помнить
- Если вы определяете оценку, проверяя одновременно несколько условий, и если все условия должны быть истинными, то для получения точных результатов необходимо использовать условия ЕСЛИ и И.
- Если вы определяете оценку, проверяя несколько условий одновременно, и если хотя бы одно из условий верно, то для получения точных результатов необходимо использовать условия ЕСЛИ и ИЛИ. Условие ИЛИ возвращает значение ИСТИНА, если выполняется любое из указанных условий, и возвращает ЛОЖЬ, если ни одно из условий не выполняется.
- Когда вы находите оценку на основе числовых значений, нам нужно сначала передать самое высокое значение. Он должен быть в порядке убывания.
- Все текстовые значения должны быть заключены в двойные кавычки, а числовые значения не должны заключаться в двойные кавычки.
Рекомендуемые статьи
Это руководство по формуле оценки в Excel. Здесь мы обсудили, как использовать формулу для расчета оценок в Excel, а также практические примеры и загружаемый шаблон Excel. Вы также можете ознакомиться с другими нашими рекомендуемыми статьями –
Microsoft Excel предоставляет ряд полезных и простых методов для расчета процента оценок. Здесь я собираюсь показать вам методы с надлежащими иллюстрациями, с помощью которых вы сможете рассчитать проценты оценок на основе набора определенных данных, а затем назначить их в текстовые строки на основе некоторых фиксированных критериев.
Загрузить практическую рабочую тетрадь
Чтобы попрактиковаться, вы можете загрузить нашу рабочую тетрадь, которую мы использовали при подготовке этой статьи. Вы также можете использовать эту книгу в качестве калькулятора в Sheet2, где вам нужно вводить данные только в соответствии с вашими требованиями, и вы сразу получите результаты.
3 подходящих метода для расчета процента оценки в Excel
1. Подсчет буквенной оценки и процента по каждому предмету отдельно в Excel
Допустим, студент получил оценки на выпускном экзамене по 5 предметам. Ниже представлен набор данных, в котором представлены полученные учащимся оценки по сравнению с общими оценками по всем предметам.
На нижней диаграмме есть буквенная система оценивания, упомянутая во всех диапазонах процентов оценок. Нам нужно найти две вещи-
- Процент оценок по всем предметам
- Буквенные оценки по всем предметам
Шаг 1:
⇒ Выберите ячейку D2.
⇒ Введите =B2/C2 и нажмите Enter.
Полученные оценки будут разделены на общие оценки по математике.
Вы получите десятичное значение, которое нужно преобразовать в проценты.
⇒ На вкладке «Главная» выберите значок «%» (процентиль) из группы команд «Число».
Итак, вы только что определили процент оценок по математике.
Шаг 2:
⇒ Теперь наведите курсор мыши на правый нижний угол ячейки D2.
Вы увидите значок «+», известный как маркер заполнения.
⇒ Выберите этот маркер заполнения, перетащите его в ячейку D6 и отпустите кнопку мыши.
Теперь вы получите процент оценок по всем предметам.
Перейдем ко второй части. Теперь нам нужно найти буквенные оценки по каждому предмету.
Шаг 3:
⇒ Нажмите на ячейку E2 и введите =ВПР(D2,$C$9:$D$15,2,ИСТИНА)
Здесь мы используем функцию ВПР, с помощью которой мы можем найти диапазон определенной информации из массива для определенных данных или ячейки.
Во-первых, нам нужно узнать буквенную оценку только по математике.
Итак, в панели функций и внутри скобок
D2 означает, что вы выбираете процент оценок по математике, который необходимо найти в массиве диаграмм системы оценок.
$C$9 : $D$15 указывает массив, в который вписаны проценты оценок, а также соответствующие буквенные оценки.
Здесь вам нужно заблокировать весь массив, используя символ «$» перед каждым номером строки и именем столбца. Это называется абсолютными ссылками на ячейки, и если вы не заблокируете здесь ссылки на ячейки, расчет не будет возвращаться к этому конкретному массиву каждый раз в процессе поиска, и для некоторых данных будут отображаться сообщения об ошибках, а также неверно истолкованные результаты. Вы можете получить подробное представление об абсолютной ссылке на ячейку здесь.
«2» обозначает второй столбец в этом массиве, который необходимо распечатать как буквенную оценку для определенного диапазона процентов.
И наконец, ИСТИНА означает приблизительное совпадение, которое вы собираетесь найти, в противном случае конкретный процент оценок, полученный по предмету, не будет включен в определенный процентный диапазон, если точное совпадение не будет получено.
⇒ Нажмите Enter, и вы увидите буквенную оценку по математике.
Шаг 4:
⇒ В ячейке E2 снова используйте маркер заполнения, чтобы заполнить ячейку E6.
Буквенные оценки по всем предметам будут показаны сразу же.
2. Вычислить среднюю оценку в процентах и среднюю буквенную оценку в Excel
Теперь давайте определим средний процент оценок и среднюю буквенную оценку по всем предметам.
Шаг 1:
⇒ Добавьте к предыдущему набору данных два дополнительных столбца с названиями «Средняя оценка в процентах» и «Средняя буквенная оценка».
Шаг 2:
⇒ Выберите ячейку F2, введите =СРЗНАЧ(D2:D6) и нажмите Enter.
Там вы получите средний процент оценок.
Шаг 3:
⇒ Теперь выберите ячейку G2 и введите =VLOOKUP(F2, C9:D15, 2, TRUE)
Здесь мы снова используем функцию ВПР, чтобы найти буквенную оценку, присвоенную среднему проценту оценки.
⇒ Нажмите Enter, и вы получите среднюю буквенную оценку в ячейке G2.
3. Вставьте вложенную формулу ЕСЛИ, чтобы узнать процент оценки в Excel
Мы можем получить аналогичные результаты, используя формулу "Вложенные ЕСЛИ", если функция ВПР кажется вам немного сложной. Итак, вот шаги, чтобы использовать вложенную функцию ЕСЛИ, чтобы найти буквенные оценки после выяснения процентных оценок.
Шаг 1:
⇒ Выберите ячейку E2.
⇒ Введите =ЕСЛИ(D2
⇒ Нажмите Enter
Вы только что определили буквенную оценку по математике в ячейке E2.
Так что же именно здесь происходит?
Мы используем функцию "Вложенные ЕСЛИ", чтобы добавить несколько условий в соответствии с нашими критериями.
Если значение в ячейке D2 не соответствует первому условию, оно будет колебаться вокруг всех условий, пока не будет соответствовать точным критериям. Как только этот процесс выполнит условие D2, ему будет присвоена фиксированная буквенная оценка из ячеек (D9:D15).
Шаг 2:
Теперь используйте маркер заполнения в ячейке E2, чтобы заполнить до E6, и вы сразу же получите ожидаемые результаты.
Заключение
Это одни из наиболее эффективных методов расчета процентных оценок и последующего преобразования их в буквенные оценки в Excel, которые я нашел. Я надеюсь, что эта статья помогла вам с правильными инструкциями. Если у вас есть какие-либо вопросы, мысли или отзывы, вы можете оставить комментарий здесь. Вы также можете ознакомиться с другими полезными статьями, связанными с Excel, на нашем сайте.
Многие школьные системы начали предоставлять своим учителям приложения для записи оценок. Если ваша школьная система не предоставляет вам такое программное обеспечение, возможно, вы могли бы создать свой собственный журнал оценок с помощью Excel.
Ниже вы видите изображение начала журнала оценок. Некоторые элементы этого изображения будут обсуждаться. один за раз.Ваш журнал оценок может быть более простым, чем в приведенном ниже примере. Мы специально использовали довольно сложную систему взвешенных оценок, чтобы проиллюстрировать возможности Excel.
Отправьте электронное письмо любому из соучредителей Internet4Classrooms, если вам нужна конкретная помощь в создании или изменении оценки. книга для вашего собственного управления классом.
Наши адреса электронной почты можно найти в нижней части любой из наших страниц.
Это не показано, чтобы вы могли скопировать мою информацию в электронную таблицу . Это показано только в качестве примера.
При экспериментировании с использованием рабочей тетради Excel в качестве журнала оценок старайтесь не усложнять ее.
Начните с 4–5 имен и не более двух типов оценок.
Легенда
Легенда. Перечислите каждый пункт, за который вы поставили оценку. Будьте конкретны, у вас достаточно места, и позже вы, возможно, пожелаете, чтобы вы записали больше информации о том, за какое задание была выставлена оценка.
Политика оценок
Политика выставления оценок. Необходимо четко указать, как вы будете использовать оценки для определения итоговой оценки учащегося. Рабочий лист Excel предоставляет достаточно места для включения этой информации на каждую страницу журнала оценок.
Функция
- Оценки за тест
- Добавьте три оценки (D7+E7+F7)
- Разделить на количество оценок (среднее)
- Умножить на 0,4 (40 %)
- Добавьте две оценки (G7+H7)
- Разделить на количество оценок (среднее)
- Умножить на 0,5 (50 %)
- Добавить четыре класса (I7+J7+K7+L7)
- Разделить на количество оценок (среднее)
- Умножить на 0,1 (10 %)
Форматирование данных
- Оценка 75,49 будет округлена до 75 без десятичных знаков. Однако с одним десятичным знаком оценка округляется до 75,5, и учащиеся ожидают, что оценка будет округлена до 76. Использование нулевого десятичного знака позволит Excel выполнять округление без путаницы для некоторых учащихся.
Как форматировать
<Цитата>1. Выделите столбец, который необходимо отформатировать, нажав на букву в верхней части столбца. <р>2. В меню «Формат» выберите «Ячейки»3. В окне «Формат ячеек» выберите «Число», а затем выберите количество десятичных разрядов, которое вы хотите использовать.
Читайте также: