如何用Office 365公式或Power Query拆分Excel佣金列多分隔符数据

一、Office 365 动态数组公式方案 #

假设原始数据位于A2:C4（A=供应商，B=日期，C=佣金），在空白单元格（比如E2）输入以下动态数组公式，将自动生成符合要求的所有行：

=LET(
    原始数据, A2:C4,
    佣金文本, INDEX(原始数据,,3),
    清理前缀, SUBSTITUTE(佣金文本, "intre ", ""),
    统一分隔符, SUBSTITUTE(SUBSTITUTE(清理前缀, " - ", "|"), " => ", "|"),
    拆分结果, TEXTSPLIT(统一分隔符, "|",,TRUE),
    配对组数, COLUMNS(拆分结果)/2,
    重复供应商, INDEX(原始数据,,1)&SEQUENCE(ROWS(原始数据),配对组数),
    重复日期, INDEX(原始数据,,2)&SEQUENCE(ROWS(原始数据),配对组数),
    佣金区间, INDEX(拆分结果, SEQUENCE(ROWS(原始数据)), SEQUENCE(1,配对组数,1,2)),
    佣金百分比, INDEX(拆分结果, SEQUENCE(ROWS(原始数据)), SEQUENCE(1,配对组数,2,2)),
    合并结果, HSTACK(LEFT(重复供应商,LEN(INDEX(原始数据,,1))), LEFT(重复日期,LEN(INDEX(原始数据,,2))), 佣金区间, 佣金百分比),
    FILTER(合并结果, 佣金区间<>"")
)

公式说明： #

1. 清理文本：先移除佣金列开头的固定前缀intre ，再将两种分隔符-和=>统一替换为|，确保拆分规则一致。
2. 拆分与配对：用TEXTSPLIT拆分文本后，提取奇数位置的内容作为佣金区间，偶数位置作为对应百分比。
3. 重复基础数据：通过SEQUENCE重复供应商和日期，确保每一组佣金对应正确的原始行信息。
4. 过滤空行：最后过滤掉可能存在的空行，保证结果干净。

二、Power Query 方案 #

Power Query适合处理批量数据，步骤更直观：

步骤1：导入数据到Power Query #

选中原始数据区域 → 点击「数据」选项卡 → 「从表格/范围」 → 勾选「我的表格有标题」，进入Power Query编辑器。

步骤2：添加自定义列处理佣金文本 #

点击「添加列」选项卡 → 「自定义列」，输入以下公式：

let
    移除前缀 = Text.Replace([佣金], "intre ", ""),
    替换分隔符 = Text.Replace(Text.Replace(移除前缀, " - ", "|"), " => ", "|"),
    拆分为列表 = Text.Split(替换分隔符, "|"),
    按组配对 = List.Split(拆分为列表, 2)
in
    按组配对

点击「确定」，生成包含配对列表的自定义列。

步骤3：展开自定义列 #

点击自定义列右侧的展开箭头 → 选择「展开到新行」，将每组佣金拆分单独成行。

步骤4：拆分配对列表为两列 #

选中展开后的自定义列 → 点击「转换」选项卡 → 「拆分列」 → 「按分隔符」，选择「逗号」并勾选「拆分到列」，将列表拆分为「佣金区间」和「百分比」两列。

步骤5：整理列名与清理 #

• 重命名拆分后的两列为「佣金区间」和「百分比」。
• 删除原始的「佣金」列。

步骤6：导出结果 #

点击「关闭并上载」，将处理后的数据导出到Excel工作表。

注意事项 #

样本数据中Floris的佣金区间0.5-2 mil $在预期输出中被写成0-2 mil $，属于笔误。如果需要统一修正为0-2 mil $，可在清理文本步骤添加Text.Replace(xxx, "0.5", "0")。

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