Excel/Power Query无需公式或自定义唯一ID,按A-D列重复值聚合求和多列的实现方法
方法一:Excel原生分类汇总功能
适合一次性处理的静态数据,全程不需要写任何公式,也不用单独生成自定义唯一ID列,就算匹配列、求和列扩展到数百个也能正常使用,操作步骤:
- 第一步:选中整个数据源区域,点击「数据」选项卡下的「排序」,把所有需要判断重复的匹配列(原需求的A-D列)依次加进排序条件,确认后完成排序。必须把所有匹配列都加入排序规则,不然同组行不会连续,会导致汇总出错。
- 第二步:保持数据区域选中,点击「数据」选项卡下的「分类汇总」按钮。
- 第三步:在弹出的设置窗口里:
- 「分类字段」选排序条件里的最后一个匹配列——因为已经提前按所有匹配列排好序了,内容完全一致的行已经挨在一起,选最后一个列就能正确识别同组。
- 「汇总方式」选「求和」。
- 「选定汇总项」列表里,勾选所有需要求和的目标列(原需求的E-K列,列数多的时候按住
Ctrl批量选就行)。 - 确认「替换当前分类汇总」「汇总结果显示在数据下方」两个选项打勾,点确定。
- 第四步:导出纯聚合结果:点工作表左侧分级显示的数字「2」,此时页面只会留每个匹配组的汇总行和总计行,按
Alt+;选中所有可见单元格,复制到新工作表,就是最终的逐列求和结果,没有多余的明细行。
方法二:Power Query实现(适合大数据量、频繁更新的场景)
适合需要重复计算、列数特别多的情况,后续原始数据更新了一键刷新就行,同样全程不用公式、不用生成自定义唯一ID,操作步骤:
- 第一步:选中整个数据源区域,点击「数据」选项卡下的「从表格/区域」,弹窗里确认「我的表格有标题」打勾,点确定进入Power Query编辑器。
- 第二步:在编辑器顶部的列名栏,批量选中所有用来判断重复的匹配列(原需求的A-D列,列数多的时候按住
Ctrl点选;如果匹配列是连续的前N列,点第一列后按住Shift点第N列就能快速全选)。 - 第三步:点编辑器上方「转换」选项卡下的「分组依据」按钮,弹窗里选高级模式:
- 弹窗上方的「分组依据」区域会自动带出你刚才选的所有匹配列,不用额外改——系统会自动按这些列的内容完全一致作为分组规则,不需要额外拼接唯一ID。
- 弹窗下方的「聚合」区域,点「添加聚合」,依次给所有需要求和的目标列设规则:「列」选对应目标列,「新列名」直接用原列名就行,「操作」下拉选「求和」。就算有几百个目标列,也是重复点选就行,不需要写任何自定义公式。
- 所有规则设完点确定,就能看到初步的分组求和结果。
- 第四步:点编辑器上方的「关闭并加载」,结果会自动输出到新的Excel工作表里。后续原始数据有变动,只要右键结果表选「刷新」,就会自动重新计算,不用从头再操作一遍。
选择提示:临时处理一次静态数据,用分类汇总最快;如果数据源要经常更新、列数到几百的规模,优先用Power Query,长期操作省时间。
内容的提问来源于stack exchange,提问作者Ranga
相关产品推荐
相关产品推荐

