Excel按组计算差值：基于STUDENT和CLASS组合生成WANT1/WANT2
2026-7-8
实现Excel按组差值计算需求
原始数据
| STUDENT | CLASS | TIME | GRADE |
|---|---|---|---|
| 1 | A | 1 | 5 |
| 1 | A | 2 | 8 |
| 1 | A | 3 | 6 |
| 1 | B | 1 | 2 |
| 1 | B | 2 | 9 |
| 2 | A | 1 | 2 |
| 2 | B | 1 | 6 |
| 2 | C | 1 | 3 |
| 2 | C | 2 | 0 |
目标结果
| STUDENT | CLASS | TIME | GRADE | WANT1 | WANT2 |
|---|---|---|---|---|---|
| 1 | A | 1 | 5 | 3 | -2 |
| 1 | A | 2 | 8 | 3 | -2 |
| 1 | A | 3 | 6 | 3 | -2 |
| 1 | B | 1 | 2 | 7 | |
| 1 | B | 2 | 9 | 7 | |
| 2 | A | 1 | 2 | ||
| 2 | B | 1 | 6 | ||
| 2 | C | 1 | 3 | -3 | |
| 2 | C | 2 | 0 | -3 |
计算规则
- WANT1：每个
STUDENT和CLASS组合下，TIME=2对应的GRADE减去TIME=1对应的GRADE - WANT2：每个
STUDENT和CLASS组合下，TIME=3对应的GRADE减去TIME=2对应的GRADE
Excel实现方法
假设数据从A1单元格开始（A1为STUDENT表头），WANT1对应E列，WANT2对应F列。
方法1：使用XLOOKUP公式（适合Excel 365/2021及以上版本）
- 在E2单元格输入WANT1的公式：
=IFERROR(XLOOKUP($A2&$B2&2,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,$D$2:$D$10)-XLOOKUP($A2&$B2&1,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,$D$2:$D$10),"")
回车后下拉填充至所有行。
- 在F2单元格输入WANT2的公式：
=IFERROR(XLOOKUP($A2&$B2&3,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,$D$2:$D$10)-XLOOKUP($A2&$B2&2,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,$D$2:$D$10),"")
回车后下拉填充至所有行。
方法2：使用SUMPRODUCT公式（兼容所有Excel版本）
- 在E2单元格输入WANT1的公式：
=IFERROR(SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10=$B2)*($C$2:$C$10=2)*$D$2:$D$10)-SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10=$B2)*($C$2:$C$10=1)*$D$2:$D$10),"")
回车后下拉填充至所有行。
- 在F2单元格输入WANT2的公式：
=IFERROR(SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10=$B2)*($C$2:$C$10=3)*$D$2:$D$10)-SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10=$B2)*($C$2:$C$10=2)*$D$2:$D$10),"")
回车后下拉填充至所有行。
公式说明
- 两种方法都会自动匹配当前行的
STUDENT和CLASS组合，查找对应TIME的GRADE值并计算差值 IFERROR函数用于处理缺少对应TIME记录的情况，返回空值以匹配目标结果格式
内容的提问来源于stack exchange，提问作者bvowe
相关产品推荐
相关产品推荐
