Выберите лучшие 3 значения и суммируйте их

Question

Допустим, у меня есть некоторые данные, которые выглядят так:

PLAYER    |Team      |Points
Smith     |Suns      |25
Jones     |Suns      |15
Martin    |Suns      |23
Chen      |Suns      |3
Williams  |Suns      |17
Quill     |Marvel    |40
Banner    |Marvel    |1
Stark     |Marvel    |1
Odinson   |Marvel    |1
Parker    |Marvel    |3
Curly     |Spurs     |2
Franke    |Spurs     |5
Wayne     |Spurs     |23
Weasley   |Wizards   |21
Potter    |Wizards   |19
Granger   |Wizards   |15
Thompson  |Bobcats   |12
Boehme    |Bobcats   |13

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

Например, он вернул бы эти данные:

Team   |Points
Suns   |65
Marvel |44
Spurs  |30
Wizards|55
Bobcats|25

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

Я также рассмотрел sumif но я не думаю, что это будет уместно по той же причине.

Я также собираюсь найти сумму баллов по шкале MIDDLE 3, но я уверен, что если я получу совет по поиску топ-3, я смогу выяснить, как адаптировать его для середины-3.

Может ли это помочь Power Query? Я баловался, но я не профессионал. (Если бы мне это было абсолютно необходимо, я мог бы также кое-что сделать в VBA, но я бы предпочел не идти по этому пути, если это возможно, поскольку я хочу поместить его в Google Sheet, чтобы в конечном итоге поделиться им в Интернете).

Answer 1

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

=SUM(INDEX(C:C,MATCH(F2,B:B,0)):INDEX(C:C,MATCH(F2,B:B,0)+MIN(COUNTIF(B:B,F2)-1,2)))

Answer 2

Эта формула будет суммировать 3 лучших результата для каждой команды:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E2,C$2:C$19),{1,2,3}),""))

Это формула массива, поэтому ее нужно вводить с помощью CTRL Shift Enter, а не просто Enter.

Результаты показаны ниже:

Как это работает: IF() возвращает список очков для команды, указанной в столбце E. Тогда LARGE() берет только 3 лучших результата. IFERROR() обрабатывает случаи, когда набрано менее 3 баллов. Наконец, SUM() добавляет их.

РЕДАКТИРОВАТЬ: Чтобы получить сумму средних 3 баллов, я попытался использовать справочную форму INDEX() с литеральными массивами и формулой, чтобы выбрать "area_num":

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX(({1,2,3},{2,3,4}),,,INT(COUNTIF(B2:B19,E11)/2))),""))

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

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX((H$1:H$3,H$2:H$4),,,INT(COUNTIF(B2:B19,E11)/2))),""))

Answer 3

Мой подход только немного отличается, чтобы получить сумму баллов 3 и 3.

1. Я отсортировал исходные данные по названию команды в качестве основного поля в порядке возрастания и по точкам в качестве дополнительного поля в порядке убывания. (Чтобы можно было подсчитать сумму трех лучших баллов вручную).
2. Я использовал формулу для генерации списка команд как в порядке возрастания, так и в порядке убывания.
3. Наконец, я вычислил сумму 3-х и 3-х баллов, используя формулу из одного из приведенных выше ответов.

Вот формулы для:

Список команд в порядке возрастания:

{=INDEX($D$216:$D$233, MATCH(0, COUNTIF($J$215:J215, $D$216:$D$233), 0))}

Список команд в порядке убывания:

  =IFERROR(LOOKUP(2,1/(COUNTIF($G$215:G215,$D$216:$D$233)=0),$D$216:$D$233),"")

Сумма трех лучших результатов команд в порядке возрастания:

{=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{1,2,3}),0))}

Сумма средних 3 очков для команд в порядке возрастания:

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{2,3,4}),0))

См. Примечание 2 ниже для объяснения этой формулы.

Заметки:

1. Замените J216 на G216, чтобы получить высшие и средние 3 балла для команд в порядке убывания.
2. Поскольку максимальное количество команд составляет 5, я предположил, что средние 3 балла - это номера 2,3 и 4. Это исключает наивысшую оценку из общего числа, даже если есть только 2 или 3 балла. Если вы считаете , что команда только с 2 или 3 баллов должны иметь все оценки вклада в целом, а затем рассмотреть один из вышеуказанных ответов.