如何将Excel单行拆分为多行实现数据规范化？含商品数量拆分

原数据格式 #

目标格式 #

下面提供两种简便方法，解决文本分列无法实现的拆分需求：

方法1：Power Query（推荐，操作直观无复杂公式） #

这是Excel内置的高效数据处理工具，适合批量转换：

• 选中原数据区域，点击「数据」选项卡 → 「从表格/区域」（Excel 2016及以后版本，旧版本找「Power Query」选项卡），确认数据包含表头，进入Power Query编辑器。
• 选中「购买商品」列，点击「转换」→ 「拆分列」→ 「按分隔符」，选择逗号（,），勾选「拆分为行」，确定后每个商品会单独占一行。
• 再次选中拆分后的「购买商品」列，点击「转换」→ 「拆分列」→ 「按字符类别」→ 「非数字到数字」，系统会自动把商品名称（字母）和数量（数字）拆分成两列，右键重命名为「购买商品」和「QTY」。
• 点击「主页」→ 「关闭并上载」，选择结果存放位置，即可得到目标表格。

方法2：公式组合（适用于低版本Excel或偏好公式的场景） #

假设原数据在A1:C3区域（A1为表头「订单编号」）：

1. 拆分商品为单独行：用TEXTSPLIT函数（仅Excel 365/2021支持），在D2输入=TEXTSPLIT(C2, ", ")，下拉填充后，选中所有拆分出的商品值，复制后右键「选择性粘贴」→ 「转置」，整理成一列（比如放在E列）。
2. 匹配订单编号和客户：根据原订单的商品数量，重复对应订单信息，比如用INDEX函数关联，或者手动填充（数据量小时更快捷）。
3. 拆分商品名和数量：
   • 商品名（F2单元格）：=LEFT(E2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, E2&"0123456789"))-1)
   • 数量（G2单元格）：=RIGHT(E2, LEN(E2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, E2&"0123456789"))+1)
     下拉填充公式即可完成拆分。

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