☰

他のセルに基づいてセルを合計する

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2019
他のセルに基づいてセルを合計する

たとえば、次のような Excel ファイルがあります。

| USER ID | SCORE | TOTAL SCORE | TOTAL BONUS | NOTE  |
| ------- | ----- | ----------- | ----------- | ----- |
| 18      | 10    |             |             |       |
| 18      | 25    |             |             |       |
| 18      | 15    |             |             |       |
| 18      | 5     |             |             | bonus |
| 18      | 30    |             |             |       |
| 24      | 2     |             |             |       |
| 24      | 7     |             |             |       |
| 24      | 21    |             |             |       |
| 32      | 6     |             |             |       |
| 32      | 9     |             |             | bonus |
| 32      | 18    |             |             |       |
| 32      | 40    |             |             |       |

各ユーザー ID について、最初の行には、ボーナスを除いたスコアの合計 (列 NOTE で識別可能) と、ボーナスの合計 (存在する場合、そうでない場合は 0) が必要です。

例の結果は次のようになります。

| USER ID | SCORE | TOTAL SCORE | TOTAL BONUS | NOTE  |
| ------- | ----- | ----------- | ----------- | ----- |
| 18      | 10    | 80          | 5           |       |
| 18      | 25    |             |             |       |
| 18      | 15    |             |             |       |
| 18      | 5     |             |             | bonus |
| 18      | 30    |             |             |       |
| 24      | 2     | 30          | 0           |       |
| 24      | 7     |             |             |       |
| 24      | 21    |             |             |       |
| 32      | 6     | 64          | 9           |       |
| 32      | 9     |             |             | bonus |
| 32      | 18    |             |             |       |
| 32      | 40    |             |             |       |

関数を使用して合計を自動化することは可能ですか? おそらくSUMIF()?

答え1

表の開始位置A1

Total Score:  C2: =IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13)  -SUMIFS($B$2:$B$13,$A$2:$A$13,A2,$E$2:$E$13,"bonus"))

Total Bonus:  D2: =IF(A2=A1,"",SUMIFS($B$2:$B$13,$A$2:$A$13,A2,$E$2:$E$13,"bonus"))

そして埋めます。

ここに画像の説明を入力してください

答え2

助けを借りてこの記事:

追加の列をいくつか作成する必要があります。最初に学生 ID の合計スコア (ボーナスを含む) を計算し、次に合計ボーナスを計算し、最後に合計スコアから合計ボーナスを減算して、目的の結果を得ました。

エクセルの結果

  • 合計スコアと合計ボーナスは、次の関数を使用して計算しますSUMPRODUCT。 SUMPRODUCT((A2:A13=A2)*B2:B13) 最初の行列はユーザ​​ー ID であり、これによって結果がグループ化され、合計されます。2 番目の行列はユーザ​​ー スコアです。
  • IF 関数を使用して合計ボーナスを計算する行を作成します。IF(F2="bonus",B2,0)
  • 最初の関数と非常によく似た合計ボーナスを計算します。 SUMPRODUCT((A2:A13=A2)*D2:D13)
  • 合計スコアから合計ボーナスを差し引きます。C2-E2

この後、残っているのは手動でテーブルをクリーンアップすることだけです。

セルの値が 0 の場合にセルを空になるように書式設定するオプションもあります (これを「ボーナス」列に適用できます)。

答え3

次の式を試してください:

合計スコアカラム：

=IF(A2=A1,"",SUMPRODUCT(($A$2:$A$13=A2)*$B$2:$B$13))

ここに画像の説明を入力してください

合計ボーナスカラム：

=IF(A2=A1,"",SUMPRODUCT(N($A$2:$A$13=A2)))

ここに画像の説明を入力してください

答え4

ここに画像の説明を入力してください

使い方：

  • セルD2の数式:

    =IF(A2=A1,"",SUMPRODUCT(($A$2:$A$13=A2)*($E$2:$E$13="bonus"),$B$2:$B$13))

  • セル C2 の数式:

    =IFERROR(IF(A2=A1,"",SUMIF(A$2:A$13,A2,B$2:B$13))-D2,"")

必要に応じて、数式内のセル参照を調整できます。

関連情報