Excel：批量拆分单元格数据并按数字分类整理Test项需求

针对2000-3000行的Excel表格，需要完成两项操作：拆分B列中用-分隔的数字，再按数字分类，在新工作表中把所有包含该数字的Test项列到对应数字的下方。下面提供两种实用方法：

方法一：Power Query（高效适配大行数） #

Power Query是Excel自带的批量数据处理工具，处理几千行数据毫无压力，步骤清晰：

1. 把原数据导入Power Query
   选中原表格里的任意单元格，点击顶部「数据」选项卡，选择「从表格/区域」，弹出的窗口里确认数据有没有表头（原表如果没有表头，就勾选「我的表格有标题」），点击确定进入编辑器。

2. 拆分B列的数字为单独行
   在编辑器里选中B列，点击「转换」选项卡，选「拆分列」→「按分隔符」，自定义分隔符填-，拆分方式选「拆分为行」。这一步会把每个Test项对应的所有数字拆成单独行，比如Test1会拆成5行，分别对应1、2、3、4、5。

3. 整理出数字对应的Test列表
   选中拆分后的数字列，点击「转换」→「数据类型」改成「整数」，避免后续分类时出现文本格式的问题。
   点击「开始」选项卡的「分组依据」，分组依据选「数字」，新列名设为「Test列表」，操作选「所有行」，确定后每个数字会对应包含它的所有Test行。
   选中「Test列表」列，点击「添加列」→「自定义列」，输入公式 =Table.Column([Test列表], "A")，提取每个数字对应的Test项列表，然后删掉原来的「Test列表」列，此时表格就是数字和对应Test项的一一配对。

4. 转置成目标格式导出
   选中所有列，点击「转换」→「转置」，此时第一行就是所有不重复的数字，下面的行就是对应位置的Test项。
   点击「开始」→「关闭并上载」，选「关闭并上载至」，指定新工作表作为输出位置，调整下列宽和格式就得到你要的结果了。

方法二：公式+辅助列（适合熟悉函数的用户） #

如果习惯用公式，也可以这么操作，不过大行数下可能会有点慢，建议缩小引用范围：

1. 生成不重复数字表头
   在新工作表的A1单元格输入公式：

   =UNIQUE(TEXTSPLIT(TEXTJOIN("-",TRUE,原表!$A$1:$B$3000),"-"))
   

   按回车后会自动生成所有不重复的数字作为表头，注意把原表!$A$1:$B$3000改成你实际的原表范围。

2. 填充每个数字对应的Test项
   在新工作表A2单元格输入公式：

   =IFERROR(INDEX(原表!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A$1,原表!$B:$B)),ROW(原表!$A:$A)),ROW(A1))),"")
   

   旧版Excel按Ctrl+Shift+回车执行数组公式，新版直接回车就行，然后下拉填充到出现空值为止。
   把A2的公式复制到B2，把里面的$A$1改成$B$1，以此类推修改其他列的表头引用，下拉填充就完成了。

小提示 #

• 几千行数据优先用Power Query，速度快还不容易出错；
• 如果原表B列有多余空格，先清理掉——可以用Power Query的「清理」功能，或者在原表加辅助列用TRIM(B1)处理；
• 公式方法里别用整列引用（比如B:B），改成实际的行范围（比如B1:B3000），能大幅提升计算速度。

内容的提问来源于stack exchange，提问作者tasos.kou