Майк — учитель, и ему часто приходится давать тесты своим ученикам. В одном случае у него было 20 результатов тестов, которые варьируются от 76 до 12. Майк хочет оставить высший результат на 76, но увеличить нижний результат на 5 (до 17)

а затем пропорционально скорректируйте все промежуточные оценки. Он задается вопросом, есть ли в Excel функция, которая выполнит настройку за него.

Нет функции, которая бы это делала, но есть любое количество формул, которые можно использовать для получения скорректированных результатов теста. На самом деле существует удивительно большое количество формул, которые вы можете использовать. Причина, по которой их так много, состоит в том, что слово «пропорционально» неоднозначно — как именно выглядит пропорциональная корректировка?

Например, это может означать часть разницы между самой низкой и самой высокой оценкой. Или это может означать, что более низкие оценки каким-то образом «взвешиваются», поэтому они получают больший прирост, чем более высокие оценки. Или это может означать, что вы просто подсчитываете, какой процент балла от максимального балла, а затем увеличиваете его на столько же.

Имея это в виду, давайте рассмотрим несколько способов корректировки оценок.

Вы захотите посмотреть результаты каждой формулы и определить, соответствуют ли они вашим ожиданиям в отношении того, что должно произойти с оценками. Эти три формулы производят точно такие же корректировки оценок, при этом более низкие оценки имеют больший вес, чем более высокие оценки:

=A1+(1-(A1-MIN(A:A))/(MAX(A:A)-MIN(A:A)))5 =A1+(5(MAX(A:A)-A1))/(MAX(A:A)-MIN(A:A))

=A1+(MAX(A:A)-A1)/(MAX(A:A)-MIN(A:A))*5

Эти формулы корректируют наивысший и самый низкий баллы по требованию Майка и все остальные баллы пропорционально. В результате, например, получается средний балл (между 12 и 76, что составляет 42 балла)

с поправкой на 2,5 пункта.

Следующая простая формула обращает внимание только на максимальное значение в диапазоне исходных оценок. Он подсчитывает, насколько далеко от максимального балла, а затем увеличивает его. В результате получается 17,33 с поправкой на нижнюю границу, но ее можно легко округлить, чтобы получить желаемую сумму.

=((MAX(A:A)-A1)/A1)+A1

Следующая формула проверяет ранжирование исходных 19 баллов по шкале от 0 до 19. Затем она определяет, какой процент из 5 представляет этот рейтинг, и затем добавляет это значение к исходной оценке:

=(RANK(A1,A:A)-1)*5/19+A1

Вы также можете использовать линейную регрессию для решения проблемы. В Excel есть две встроенные функции (НАКЛОН и ПЕРЕСЕЧЕНИЕ), которые можно использовать для поиска формулы для определения скорректированных оценок. В примере Майка диапазон оценок до корректировки составляет от 12 до 76. Их необходимо отрегулировать до диапазона от 17 до 76. Если вы поместите 12 в ячейку E1, 76 в ячейку E2, 17 в ячейку F1 и 76 в ячейку ячейка F2, то в конечном итоге вы получите минимумы и максимумы как в начальном, так и в конечном диапазоне. Затем вы можете использовать эти формулы, чтобы найти наклон и точку пересечения:

=SLOPE(F1:F2,E1:E2)

=INTERCEPT(F1:F2,E1:E2)

Это вычисляет наклон на уровне 0,921875 и точку пересечения на уровне 5,9375. Затем вы можете применить эти значения к исходным результатам теста следующим образом:

=0.921875 * A1 + 5.9375

Интересно, что этот подход линейной регрессии возвращает точно такие же значения, полученные по первым трем формулам, представленным в начале этого совета.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (1022) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Adjusting_Test_Scores_Proportionately [Пропорциональная корректировка результатов тестов].