☰

選擇前 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()。 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 名分數。
  2. 由於團隊的最大數量為 5，因此我假設中間 3 個分數為數字 2,3 和 4。這會從總分中消除最高分，即使只有 2 或 3 個分數。如果您認為只有 2 或 3 分的球隊應該全部分數佔總分，然後考慮上面的答案之一。

相關內容