Суммировать ячейки на основе других ячеек

Question

У меня есть, например, следующий файл 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    |             |             |       |

Для каждого ИДЕНТИФИКАТОРА ПОЛЬЗОВАТЕЛЯ в первой строке должна быть указана общая сумма баллов, за исключением бонусов (их можно определить по столбцу ПРИМЕЧАНИЕ), и отдельно сумма бонусов (если они есть, в противном случае она будет равна 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()?

Answer 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"))

и залейте.

Answer 2

С некоторой помощьюЭта статья:

Вам нужно будет создать несколько дополнительных столбцов. Сначала я подсчитал общий балл за студенческий билет (включая бонус), затем общий бонус, и, наконец, я вычел общий бонус из общего балла, чтобы получить желаемый результат.

• Мы вычисляем общий балл и общий бонус с помощью SUMPRODUCTфункции: SUMPRODUCT((A2:A13=A2)*B2:B13) Первая матрица — это идентификаторы пользователей, по которым группируются и суммируются результаты. Вторая матрица — это баллы пользователей.
• Создаем строку, в которой общий бонус рассчитывается с помощью функции ЕСЛИ:IF(F2="bonus",B2,0)
• Мы рассчитываем общий бонус, очень похожий на первую функцию: SUMPRODUCT((A2:A13=A2)*D2:D13)
• Вычитаем общий бонус из общего балла:C2-E2

После этого останется только вручную убрать со стола.

Также есть возможность отформатировать ячейки так, чтобы они были пустыми, если их значение равно 0 (это можно применить к столбцу «бонус»).

Answer 3

Попробуйте эту формулу:

ОБЩИЙ СЧЕТстолбец:

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

ОБЩИЙ БОНУСстолбец:

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

Answer 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,"")
  

При необходимости вы можете скорректировать ссылки на ячейки в формуле.