У меня есть лист, содержащий границы оценок. Например, в первой строке написано «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). Таким образом, формула автоматически адаптируется к тому, какой класс находится в строке, в которой она находится, и всегда указывает на нужные ячейки, необходимые для поиска значения буквы.
Если таблица оценок находится на отдельном листе, формула почти такая же, но выглядит немного более грязной. Для каждой ссылки на оценочную таблицу вам необходимо указать название листа, например: « Оценочная таблица »!$ 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 автоматически настроит формулу так, чтобы она указывала на график в новом месте. Это работает даже в том случае, если вы перемещаете диаграмму в другой файл, если при этом открыт тот, который содержит список оценок.