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

362
phroureo

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

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, чтобы в конечном итоге поделиться им в Интернете).

0
Хотели бы вы отсортировать по команде и по убыванию очков? Scott Craner 6 лет назад 0
Да, я могу отсортировать все, что нам нужно. phroureo 6 лет назад 0

3 ответа на вопрос

2
Scott Craner

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

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

enter image description here

Я отмечаю правильность Скотта, потому что это тот, который я в итоге использовал (я не так хорошо знаком с массивами, поэтому я не был уверен, что смогу изменить его, чтобы найти середину 3). Однако оба ответа были великолепны. :) phroureo 6 лет назад 0
2
Bandersnatch

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

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

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

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

enter image description here

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

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

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX((,),,,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))),""))
Я делал SUMIF больше, чем третий по величине, но продолжал получать слишком много для воинов. Это отличный подход. Scott Craner 6 лет назад 0
Спасибо, Скотт. Очень приятно получить от тебя дополнение. У меня были проблемы с получением средней 3, но я добавлю ее к своему ответу через минуту. Bandersnatch 6 лет назад 0
0
Rajesh S

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

enter image description here

  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),),0))}

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

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),),0))

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

Заметки:

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

Похожие вопросы