如何在Excel中按前缀将列数据分组到行？大数据量公式失效求助

问题根源 #

原公式在大数据量下返回0，是因为旧数组公式采用全量循环计算模式，当数据量过大时，Excel计算资源被耗尽，无法完成运算返回错误值。

方案一：Power Query（推荐，适配十万级以上数据） #

• 选中存放文件名的列，点击「数据」选项卡→「从表格/区域」，确认数据包含表头（无表头可在弹出窗口勾选「我的表格没有标题」）
• 在Power Query编辑器中，添加自定义列，输入公式提取前缀：Text.BeforeDelimiter([你的列名], "_")
• 点击「转换」选项卡→「分组依据」，设置：
  • 分组依据：选择刚创建的前缀列
  • 新列名：比如「分组结果」
  • 操作：选择「所有行」
• 点击「分组结果」列右侧的展开箭头，选择「提取值」，设置分隔符（如逗号、空格等，按需选择）
• 点击「关闭并上载」，分组结果会自动生成在新工作表中

方案二：优化后的动态数组公式（适合不想用Power Query的场景） #

假设文件名存放在A列（从A2开始）：

1. 提取唯一前缀：在C2单元格输入公式

   =UNIQUE(TEXTBEFORE(A2:A100000,"_"))
   

   （可根据实际数据范围调整A2:A100000，不建议直接用A:A，会增加计算量）
2. 批量匹配分组：在D2单元格输入公式，下拉填充到所有前缀行

   =TEXTJOIN(", ",TRUE,FILTER(A:A,TEXTBEFORE(A:A,"_")=C2))
   

内容的提问来源于stack exchange，提问作者tryabp