Вопрос или проблема
У меня есть лист с границами оценок. Например, первая строка гласит «A* | 100 | 90», следующая строка — «A | 90 | 80», затем «B | 80 | 70» и так далее. Другой лист содержит список студентов в первом столбце и общую оценку, которую они получили, во втором столбце.
Какие формулы мне следует использовать, чтобы преобразовать общие баллы студентов в оценки?
Наверняка существуют более “лучшие” решения, которые можно реализовать с помощью VLOOKUP или INDEX/MATCH. Но для довольно небольшого списка, такого как шкала оценок, простая серия вложенных IF должна сработать вполне нормально.
Здесь я начал с “F” и двигался к “A”. Формула просто работает по уровням оценок, чтобы проверить, меньше ли оценка студента максимума для каждого уровня. Как только она находит правильный уровень, она останавливается. С этой формулой минимумы и спецификация диапазона для “A” технически ненужны. Она также автоматически скорректирует буквенные оценки студентов, когда максимумы изменяются (хотя могут быть некоторые ошибочные результаты, если вы случайно оставили некоторое перекрытие).
=IF(A2<$F$6,$D$6,IF(A2<$F$5,$D$5,IF(A2<$F$4,$D$4,IF(A2<$F$3,$D$3,$D$2))))
Имейте в виду, что каждая ссылка на проверяемую оценку должна быть относительной (например: A2), в то время как все ссылки на таблицу оценок должны быть абсолютными (например: $D$2). Таким образом, формула автоматически адаптируется к той оценке, которая находится в строке, в которой она находится, всегда указывая на правильные ячейки, из которых нужно брать буквенное значение.
Если таблица оценок находится на отдельном листе, формула остается практически такой же, но выглядит немного сложнее. Для каждой ссылки на таблицу оценок вам нужно будет включить имя листа, вот так: 'Grading Chart'!$A$2. Смотрите формулу ниже.
=IF(A2<'Grading Chart'!$C$6,'Grading Chart'!$A$6,IF(A2<'Grading Chart'!$C$5,'Grading Chart'!$A$5,IF(A2<'Grading Chart'!$C$4,'Grading Chart'!$A$4,IF(A2<'Grading Chart'!$C$3,'Grading Chart'!$A$3,'Grading Chart'!$A$2))))
Простой способ заставить Excel помочь вам конструктивно сформировать это — если вы можете начать с таблицы оценок на том же листе, что и список оценок. Затем, после того как вы написали формулу и она заработала, вырежьте и вставьте таблицу оценок на другой лист. Excel автоматически скорректирует формулу, чтобы она указывала на таблицу в ее новом месте. Это работает даже если вы переместите таблицу в другой файл, при условии, что файл со списком оценок открыт, когда вы это делаете.
Ответ или решение
Для преобразования оценок студентов в оценки с использованием Excel существует несколько подходов, включая использование формул, таких как VLOOKUP или INDEX/MATCH. Однако для небольшого списка, такого как градационные шкалы, можно использовать простую цепочку вложенных IF. Рассмотрим этот процесс более подробно.
1. Структура данных
Существует два листа в вашей книге Excel:
-
Лист "Градации" (или другое название), содержащий границы оценок. Строки на этом листе могут выглядеть следующим образом:
- A* | 100 | 90
- A | 90 | 80
- B | 80 | 70
- и так далее.
-
Лист "Студенты", содержащий список студентов в первой колонке и их достижения в баллах во второй колонке.
2. Формулы для присвоения оценок
2.1 Вложенные IF
Формула, которую вы можете использовать, выглядит следующим образом:
=IF(B2<'Градация'!$C$6,'Градация'!$A$6,IF(B2<'Градация'!$C$5,'Градация'!$A$5,IF(B2<'Градация'!$C$4,'Градация'!$A$4,IF(B2<'Градация'!$C$3,'Градация'!$A$3,'Градация'!$A$2))))Где:
B2– это ячейка с баллом студента.'Градация'!$C$6и т. д. – это абсолютные ссылки на границы оценок в шкале.'Градация'!$A$6и т. д. – это абсолютные ссылки на буквы оценок.
2.2 Применение формулы
- Разместите формулу в первой строке списка студентов (например, в C2).
- Протяните формулу вниз по колонке, чтобы присвоить оценки всем студентам на основе их баллов.
3. Советы по использованию формул
-
Относительные и абсолютные ссылки: Обратите внимание, что ссылки на баллы студентов (например,
B2) должны быть относительными, чтобы они менялись при копировании формулы вниз, в то время как ссылки на шкалу оценок должны быть абсолютными ($A$2), чтобы оставались неизменными. -
Перемещение шкалы оценок: Если вы начнете с того, что шкала оценок находится на том же листе, а после этого переместите ее на другой лист, Excel автоматически скорректирует ссылки на данные. Это удобно для организации ваших данных.
4. Альтернативный метод: VLOOKUP
Если количество границ оценок значительно увеличивается, вы можете рассмотреть использование функции VLOOKUP, которая оптимизирует процесс поиска, поскольку она позволяет автоматически выводить оценки, основываясь на значениях баллов. Пример формулы VLOOKUP может выглядеть следующим образом:
=VLOOKUP(B2,'Градация'!$C$2:$A$6,1,TRUE)В этой формуле:
B2– это балл студента.'Градация'!$C$2:$A$6– диапазон, содержащий границы оценок и соответствующие буквы оценок.1указывает, что нужно вернуть значение из первого столбца в диапазоне.
Заключение
Использование Excel для автоматизации процесса преобразования оценок в буквы значительно упрощает работу с данными. Вы вправе выбирать между простотой вложенных IF и более гибким VLOOKUP, в зависимости от размеров ваших наборов данных и предпочтений в обработке. Эта методология помогает не только избежать ошибок при ручном вводе, но и сохраняет время, позволяя сосредоточиться на других важных аспектах вашей работы.