无需VBA实现CSV按单元格多值拆分单行至多行的方法

可以用Excel内置的**Power Query（获取和转换数据）**实现，完全不需要VBA，操作简单且适合团队复用，步骤如下：

方法一：Power Query（推荐，适合任意数据量） #

1. 导入CSV到Power Query
   打开Excel，点击「数据」选项卡 → 「自文本/CSV」，选择目标CSV文件。在导入预览窗口中，点击「加载」旁的下拉箭头，选择「加载到」→ 勾选「只有创建连接」并勾选「将此数据添加到数据模型」，点击确定；之后在「数据」选项卡点击「查询和连接」，右键点击刚创建的连接，选择「编辑」进入Power Query编辑器。

2. 拆分Friends列并整理

   • 在编辑器中选中Friends列，点击「转换」选项卡 → 「拆分列」→ 「按分隔符」。
   • 分隔符选「逗号」，拆分方式选「拆分为行」，点击确定。
   • 将Friends列重命名为Friends and/or family，此时已得到Name与对应朋友的每行一组关系。
   • 点击「主页」→ 「关闭并上载至」，选择「仅创建连接」，保存为Friends拆分结果。

3. 拆分Family列并整理

   • 回到「查询和连接」面板，右键点击原始CSV连接选择「复制」，粘贴后重命名为「Family拆分处理」并编辑。
   • 选中Family列，重复步骤2的拆分操作，将Family列重命名为Friends and/or family。
   • 关闭并上载为连接Family拆分结果。

4. 合并两个拆分结果

   • 在Power Query编辑器中，点击「主页」→ 「追加查询」→ 「追加查询作为新查询」。
   • 在弹出窗口选择「两个或更多表」，添加Friends拆分结果和Family拆分结果，点击确定。
   • 若存在空白行，选中Friends and/or family列，点击「转换」→ 「删除行」→ 「删除空白行」。
   • 点击「主页」→ 「关闭并上载」，即可得到每行仅一组Name与亲友对应关系的表格。

方法二：数组公式（适合小数据量） #

如果数据量不大，也可以用数组公式实现，假设原始数据在Sheet1的A2:C100区域（A=Name，B=Friends，C=Family）：

1. 在新工作表的A2单元格输入以下公式，按Ctrl+Shift+Enter完成数组输入后下拉填充：

   =INDEX(Sheet1!$A$2:$A$100,INT((ROW(A1)-1)/(1+(LEN(Sheet1!$B$2:$B$100)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$100,",","")))+(LEN(Sheet1!$C$2:$C$100)-LEN(SUBSTITUTE(Sheet1!$C$2:$C$100,",",""))))+1)
   

2. 在新工作表的B2单元格输入以下公式，同样按Ctrl+Shift+Enter完成数组输入后下拉填充：

   =IFERROR(TRIM(MID(SUBSTITUTE(INDEX(Sheet1!$B:$C,INT((ROW(A1)-1)/(1+(LEN(Sheet1!$B$2:$B$100)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$100,",","")))+(LEN(Sheet1!$C$2:$C$100)-LEN(SUBSTITUTE(Sheet1!$C$2:$C$100,",",""))))+1,MOD(ROW(A1)-1,1+(LEN(Sheet1!$B$2:$B$100)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$100,",","")))+(LEN(Sheet1!$C$2:$C$100)-LEN(SUBSTITUTE(Sheet1!$C$2:$C$100,",",""))))+1)),",",REPT(" ",99)),(MOD(ROW(A1)-1,1+(LEN(Sheet1!$B$2:$B$100)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$100,",","")))+(LEN(Sheet1!$C$2:$C$100)-LEN(SUBSTITUTE(Sheet1!$C$2:$C$100,",","")))))*99+1,99)),""
   

3. 下拉填充到公式返回空白为止，即可得到目标表格。

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