☰

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

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007 power-query
Выберите 3 лучших значения и просуммируйте их.

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

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, но не думаю, что это будет уместно по той же причине.

Я также хочу найти сумму СРЕДНИХ трех баллов, но я уверен, что если я получу совет по поиску верхних трех, я смогу придумать, как адаптировать ее для средних трех.

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

решение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)))

введите описание изображения здесь

решение2

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

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

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

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

введите описание изображения здесь

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

EDIT: Чтобы получить сумму средних 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))),""))

решение3

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

введите описание изображения здесь

  1. Я отсортировал исходные данные по названию команды как основному полю в порядке возрастания и по очкам как вторичному полю в порядке убывания. (Чтобы можно было вручную рассчитать сумму трех лучших очков).
  2. Я использовал формулу для создания списка команд как в порядке возрастания, так и в порядке убывания.
  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, я предположил, что средние три результата — это числа 2, 3 и 4.Это исключает наивысшую оценку из общей суммы, даже если их всего 2 или 3.Если вы считаете, что команда, набравшая всего 2 или 3 очка, должна иметьвсебаллы учитываются в общем итоге, затем рассмотрите один из ответов выше.

Связанный контент