Как извлечь максимальные значения из нескольких строк и посчитать, сколько из них соответствует определенным критериям, в функции с одной ячейкой

Question

У меня есть лист в Excel, который показывает успехи студентов в определенных частях экзамена (тесты, презентации, итоговый экзамен и т.д.). Мне нужно вычислить некоторые статистические данные, и проблема в том, что для некоторых тестов у них есть три попытки, учитывается только наибольшее количество баллов, а у меня нет столбца "max". Мне нужно посчитать, сколько из них набрало более 15 баллов в лучшем случае, сколько между 10 и 14,9 и т.д. Также мне нужно увидеть, сколько учеников выполнили хотя бы одну попытку (на основе непустого поля или> = 0).

Таблица выглядит так (одна часть):
Ученик....Att1 ....ATT2 ....ATT3
Anna ......... 10 ...... 14 ..... 13,5
Belle ......... 7 ......... 15 ...... 15,5
Клара ........ 17 ...... 18 ..... 16,5

Итак, из этого мне нужно извлечь в одной ячейке, что максимальное количество значений в строке> = 15 равно 2 (Белл в 15,5 и Клара в 18), а общее количество учеников, которые когда-либо пытались хотя бы один раз, равно 3.

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

Я попробовал несколько промежуточных и смещенных комбинаций, но без особого успеха. Я использую MS Excel 2013, другие коллеги используют 2007 и 2010.

Answer 1

Эта формула может быть легко расширена для любого количества строк или столбцов

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>15)+0)

OFFSET возвращает каждую строку в качестве отдельного диапазона, SUBTOTAL находит максимальное значение [4] в каждой строке, а SUMPRODUCT считает, сколько из этих значений> 15.

Также можно сделать то же самое с COUNTIF вместо SUBTOTAL, т.е.

=SUMPRODUCT((COUNTIF(OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0),">15")>0)+0)

Для диапазона, например> 10 и <= 15, вы можете настроить SUBTOTAL версию следующим образом

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>{10,15})*{1,-1})

Answer 2

для подсчета больше или равно 15 для строки (скажем, в столбцах E, поэтому E2 здесь)

=COUNTIF(B2:D2;">=15")

для вас это максимум (в E5 здесь)

=countif(E2:E4;">0")

образец

student at1 at2 at3     up15
anna    10  14  13,5    0
Belle   7   15  15,5    2
clara   17  18  16,5    3
                 Max    2

Можно добавить тест как минимум для 3 попыток на строку, но не ясно, где вы хотите (на строку или на максимум)