Я пытаюсь ранжировать своих студентов по 2 критериям. Сначала из RESULT а затем POINT .
В этом примере результирующий порядок должен быть NUNU, NENE, NONO, NANA и NINI.
Это невозможно сделать, сложив RESULT и POINT . Я искал в Интернете и не могу понять, как сделать рейтинг.
Я хотел бы предложить вам решение с другим подходом:
Запишите эту формулу в ячейку J2(Rank Col) и заполните ее, наконец, отсортируйте данные в порядке возрастания по столбцу Rank:
=COUNTIF($G$2:$G$6,">"&G2)+1+SUMPRODUCT(--($G$2:$G$6=G2),--($H$2:$H$6>H2))
Решение довольно простое:
Введите следующую формулу в I2 и ctrl-enter/copy-paste/fill-down в столбце:
=G2*10^3+H2
Обратите внимание, что это будет работать правильно, только если максимально допустимое значение в столбце POINT равно 999.
Для больших значений необходимо изменить 10^3 в формуле. Например, если максимально допустимое значение равно 9999, то требуется 10^4 .
Если вам нужна более надежная формула, которая будет работать независимо от значений в столбце H , используйте это:
=G2*10^CEILING(LOG10(MAX($H$2:$H$6)),1)+H2
Если вам нужен "фактический" ранг, к сожалению, нет возможности использовать вышеуказанные формулы с функцией RANK() , поскольку она допускает ссылки только на второй аргумент, а не на массивы. Требуется совершенно другая формула:
Введите следующую формулу в I2 и ctrl-enter/copy-paste/fill-down в столбце:
=1+SUMPRODUCT(($G$2:$G$6>G2)+($G$2:$G$6=G2)*($H$2:$H$6>H2))
Введенный эквивалентный массив (Ctrl+Shift+Enter):
{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}
Конечно, если вам необходимо отсортировать таблицу, самое простое решение - выполнить сортировку по нескольким столбцам:
Итак, глядя на ваш предыдущий вопрос, а также на этот, становится очевидным, что вы хотите ранжировать студентов по количеству A, затем по B и т.д., И разбить эти ранги на основе сумм оценок. для каждого предмета.
Имея это в виду, вот формула столбца no helper, чтобы сделать именно это:
Массив введите (Ctrl+Shift+Enter) следующую формулу в M5 и скопируйте-вставьте / заполните вниз в оставшуюся часть столбца (не забудьте удалить { и the }):
{=
SUM(
1,
--(
MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))
>INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))
),
(
MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))
=INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))
)
*(
MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))
>INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))
)
)}
Ниже приведен эквивалентный сокращенный вариант приведенной выше формулы. Я настоятельно советую не использовать его, а вместо этого использовать предварительно проверенную версию формулы. Это сделает формулу намного проще в обслуживании.
{=SUM(1,--(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))),(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))=INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)))*(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))))}
Объяснение:
Глядя на структуру предварительно подтвержденной формулы, становится ясно, что она по сути совпадает с "правильной" формулой, введенной в ранг ранга из моего предыдущего ответа.
{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}
с RESULT и POINT вспомогательные столбцы заменяются с MMULT(…) функции, а также ссылки одной ячейки в этих столбцах заменены INDEX(MMULT(…),1+ROW()-ROW($B$5:$L$9))
Если вы хотите понять, как здесь используется функция MMULT() , вы можете начать с изучения более простого использования в моем ответе на другой вопрос.
Должно быть достаточно очевидно, как настроить формулу для большего количества оценок. Например, чтобы добавить D , добавьте +($B$5:$L$9="D")*6^0 к концу остальных и увеличьте остальные степени.
Как упоминалось в ответе на предыдущий вопрос, если таблица будет расширена за счет большего количества предметов, число 6 должно быть увеличено, чтобы оно было как минимум на один больше, чем новое количество предметов в таблице.