当Unique ID与列名匹配时的计数求和问题求助
2026-7-29
问题描述
- Unique ID对应人员，列名包含多类信息，另有单独工作表存储列键信息。
- 需要在最终表格中实现当Unique ID与列名匹配时的Count（计数）和Sum（求和），最终需生成196行（Unique ID×列数）的结果。
- 尝试Index Match Match时空白被识别为0（但此处0为有效数据），Countifs和Sumifs报错，求解决建议。
数据示例
| Location | Year | UNIQUE ID | 1-2019-01-1 | 1-2019-01-2 | 1-2019-01-3 | 2-2019-01-1 | 2-2019-01-2 | 2-2019-01-3 | 1-2020-01-1 | 1-2020-01-2 | 1-2020-01-3 | 2-2020-01-1 | 2-2020-01-2 | 2-2020-01-3 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2019 | A | 0 | 1 | 1 | |||||||||
| 1 | 2019 | B | 1 | 0 | 1 | |||||||||
| 1 | 2019 | C | 1 | 0 | 1 | |||||||||
| 1 | 2019 | D | 1 | 1 | 1 | |||||||||
| 1 | 2020 | E | 1 | 5 | 0 | |||||||||
| 1 | 2020 | F | 1 | 5 | 0 | |||||||||
| 1 | 2020 | G | 1 | 3 | 1 | |||||||||
| 1 | 2020 | H | 1 | 2 | 1 | |||||||||
| 2 | 2019 | I | 2 | 2 | 3 | |||||||||
| 2 | 2019 | J | 1 | 5 | 2 | |||||||||
| 2 | 2019 | K | 3 | 4 | 2 | |||||||||
| 2 | 2019 | L | 1 | 1 | 0 | |||||||||
| 2 | 2020 | M | 4 | 1 | 3 | |||||||||
| 2 | 2020 | N | 1 | 0 | 3 | |||||||||
| 2 | 2020 | O | 2 | 2 | 2 | |||||||||
| 2 | 2020 | P | 1 | 1 | 3 |
解决建议
1. 区分空白与有效0（修复Index Match Match问题）
原公式会将空白单元格返回0，用IF+ISBLANK组合判断即可区分：
=IF(ISBLANK(INDEX(数据区域,MATCH(目标ID,UNIQUE ID列,0),MATCH(目标列名,表头行,0))),"",INDEX(数据区域,MATCH(目标ID,UNIQUE ID列,0),MATCH(目标列名,表头行,0)))
执行后，空白单元格会返回空文本，有效0则正常显示。
2. 修复Countifs/Sumifs报错
报错核心原因是当前数据为宽表结构，而Countifs/Sumifs更适配长表。先转成长表格式：
- 用Power Query的「逆透视列」功能，将Location、Year、UNIQUE ID之外的所有列转成「属性（原列名）」和「值」两列
- 转换完成后，直接用以下公式：
- 求和：
=SUMIFS(值列,UNIQUE ID列,目标ID,属性列,目标列名) - 计数：
=COUNTIFS(UNIQUE ID列,目标ID,属性列,目标列名,值列,"<>")（若要统计所有匹配行含空白，移除值列,"<>"条件）
- 求和：
3. 批量生成196行结果
用Power Query转长表后，自动生成所有ID+列名的组合，直接加载到新工作表即可；若手动生成，先列出所有Unique ID和列名，通过交叉引用生成全组合，再用上述公式填充对应值。
内容的提问来源于stack exchange，提问作者Ari Monger
相关产品推荐
相关产品推荐
