У меня есть система оценок, в которой есть столбец с числами, соответствующими процентам.
Например, B2-B5 — это числа, зависящие от оценки. 1 соответствует 85%, 2 соответствует 95%, 3 соответствует 97% и 4 соответствует 100%. В левом верхнем углу A1 у меня есть поле для общего балла, которое должно брать эти числа, интерпретировать их как проценты, а затем усреднять их.
Моей целью было использовать ПОДСТАВИТЬ и СРЗНАЧ. Я пробовал использовать Подстановку, но, судя по всему, ее нельзя применить к диапазону ячеек в столбце одновременно, и, боже, я раздражен.
Возможно ли это в Excel? Я разработчик и мог бы легко сделать это буквально в чем угодно, но это запрос для коллеги, а Excel меня бесит до бесконечности. Я нахожу это полностью и абсолютно неинтуитивным.
решение1
ПытатьсяВЫБИРАТЬ(). В ячейку А1 поместите
=AVERAGE(CHOOSE(B2:B5,0.85,0.95,0.97,1))
Это формула массива, и ее необходимо вводить с помощью CTRLShiftEnter, а не просто Enter.
Если формула введена правильно, в строке формул она будет заключена в фигурные скобки {}.
решение2
Используйте эту формулу массива:
=AVERAGE(INDEX({0.85,0.95,0.97,1},N(IF(1,$B$2:$B$5*1))))
Так как это формула массива, ее необходимо подтвердить сочетанием клавиш Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.
Он создает массив чисел в диапазоне. Затем он передает этот диапазон в INDEX. Таким образом, при итерации массива он изменит числа для десятичного процента и передаст все это в AVERAGE:
решение3
Альтернативный вариант — если вы можете использовать несколько других ячеек для промежуточных вычислений (возможно, в скрытых столбцах или даже на другом листе):
- Используйте справочную таблицу для хранения взаимосвязи между баллами и процентами.
- ИспользоватьВПРпреобразовать каждую оценку в процент
- ИспользоватьСРЕДНИЙобъединить все проценты
Преимущество этого подхода в том, что он не требует, чтобы оценки были последовательными числами; он будет работать так же хорошо с буквенными или словесными оценками. Не требуется никаких заумных трюков или вовлеченных формул массива.
Скриншот с формулами:
Скриншот вывода:
Скриншот с алфавитным рейтингом:
