☰

上位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が、同じ理由でそれは適切ではないと思います。

また、MIDDLE 3 のスコアの合計も求めていますが、上位 3 つを見つけるためのアドバイスが得られれば、それを中間の 3 つに適用する方法もわかると思います。

これは Power Query が役立つことでしょうか? 少しだけ試したことはありますが、プロではありません。(どうしても必要な場合は、VBA で何かを作成することもできますが、最終的には Google スプレッドシートに入れて Web で共有したいので、できればその方法は避けたいと思います)。

答え1

データをチームとスコアの降順で並べ替えます。次に、各チームの最初のスコアを見つけて、次の 3 つのスコアを合計するか、そのチームのスコアが少ない場合はその数を合計する次の数式を使用します。

=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 つのスコアの合計を取得するために、リテラル配列と「area_num」を選択する数式を使用した INDEX() の参照形式を使用しようとしました。

=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. 最後に、上記の回答の 1 つから得た式を使用して、上位 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点しか得点していないチームが全てスコアが合計に加算される場合は、上記の回答のいずれかを検討してください。

関連情報