Суммировать до N наивысшего значения из серии?

Question

У меня есть таблица, в которой каждая строка представляет собой набор числовых результатов соревнований. Каждый ряд можно рассматривать как одного участника в общем соревновании, а каждый столбец является событием соревнования. Ничего действительно необычного там нет.

Теперь я хочу взять сумму из N баллов наибольшего значения из каждой строки, так что:

• Если предоставлено меньше или равно N баллам, суммируйте все перечисленные баллы
• Если указано более N баллов, суммируйте N наивысших баллов из ряда

Как это сделать?

В моем конкретном случае N = 5, но я надеюсь, что существует общее решение.

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

Answer 1

Используйте LARGE функцию.

=LARGE(Range;1)+LARGE(Range;2)+…+LARGE(Range;N)

или же

=SUM(LARGE(Range;1); LARGE(Range;2); …; LARGE(Range;N))

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

Answer 2

Хорошо, если ваши числовые данные находятся в строках на Sheet1 , введите значение N в Sheet2!N1 и введите

=SUM(LARGE(Sheet1!1:1, ROW(INDIRECT("1:"&MIN(COUNT(Sheet1!1:1),N$1)))))

в Sheet2!A1 .  (Если ваши данные находятся, например, только в столбцах с G по Z , измените Sheet1!1:1 до листа 1 Sheet1!G1:Z1 .)  Нажмите Ctrl+Shift+Enter, чтобы создать формулу массива.  Перетащите столько строк, сколько вам нужно, и все готово.  (Обратите внимание, что в Sheet2!A17 , например, Sheet1!1:1 автоматически изменится на Sheet1!17:17 , т. Е. 17-ая строка в Sheet1 , потому что номер строки относительный - перед ним не стоит $ .)

Объяснение:

• COUNT(Sheet1!1:1) - это число чисел (баллов) в ссылочной строке (которое, как указано выше, может быть строкой 1 , строкой 17 или любой строкой, которую вы просматриваете на Sheet2).  Я делаю это потому, что вы сказали:«Не все участники принимают участие в каждом событии»; Я предполагаю, что неучастия являются пустыми или, возможно, нечисловой строкой.
• N$1 - указанное вами значение N ; количество баллов, которые вы хотите добавить.
• MIN() - это, конечно, минимум.  Если N равно 5, а конкурент участвовал только в трех событиях, мы хотим добавить все три.  Если участник участвовал в семи соревнованиях, мы хотим подвести итоги пяти самых высоких.
• & - это оператор конкатенации строк в Excel (и, как я слышал, Libre Office Calc очень похож на Excel), поэтому, если количество добавляемых нами баллов (MIN(…)), скажем, 5, то "1:"&MIN(…) становится строковым значением "1:5" .
• INDIRECT("1:5") является областью, включающей строки от 1 до 5 , и
• ROW() этого - массив {1,2,3,4,5} .
  Это хитрость для создания значения массива, которое определяется данными времени выполнения.
• LARGE(Sheet1!1:1, {1,2,3,4,5}) - это массив { LARGE(Sheet1!1:1,1), LARGE(Sheet1!1:1,2), LARGE(Sheet1!1:1,3), LARGE(Sheet1!1:1,4), LARGE(Sheet1!1:1,5) } , который является самым большим (самым высоким) показателем в диапазоне, вторым по величине,… и так далее, вплоть до 5-го.

Я предположил, что вы находитесь в локали, которая использует запятые для разделителей.  Если вы находитесь в стране точек с запятой, делайте так, как это делают точки с запятой.

Answer 3

Я хотел бы подойти к этому, составив баллы в виде таблицы, а затем отсортировать таблицу по баллам. Тогда вы можете посмотреть только первые n столбцов ...

Если вам нужно изменить n "на лету", вы можете сделать косвенную ссылку на ячейки, основываясь на заданном вами значении.