☰

选择前 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

如果我想编写一个公式来将每个队的前 3 名得分相加，我该怎么做？

例如，它将返回以下数据：

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

我曾考虑使用过滤器来表示“如果值大于团队成员的中间值”或其他内容，但如果成员少于 3 名，则不起作用。

我也考虑过sumif，但出于同样的原因，我认为这不合适。

我也想找到中间 3 个分数的总和，但我确信，如果我得到关于找到前 3 名的建议，我就能想出如何将其调整为中间 3 个。

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()将它们加起来。

编辑：为了获得中间 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

我的方法只是略有不同，即得到前 3 名和中间 3 名分数的总和。

在此处输入图片描述

  1. 我将源数据按球队名称作为主要字段按升序排列，按分数作为次要字段按降序排列。（以便手动计算前 3 名得分的总和）。
  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),"")

按升序排列各队伍前 3 名得分的总和：

{=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 名和中间 3 名得分。
  2. 由于球队的最大数量是 5，我假设中间的 3 个得分是数字 2、3 和 4。这样就可以从总分中剔除最高分，即使只有 2 或 3 个分数。如果你认为一支只得2或3分的球队应该全部分数计入总分，然后考虑上述答案之一。

相关内容