如何用Power Query拆分姓名与大写缩写属性（含Jr./Sr.后缀）

需求场景 #

现有球员数据，需拆分出姓名和末尾的大写属性缩写：

• 属性为已知的20种大写缩写（如UER、RC、COR、ERR等），数量因人而异，可能单个或多个（用逗号分隔）
• 姓名后可能带有Jr.、Sr.这类后缀，需避免将其误识别为属性

示例输入 #

期望输出 #

Power Query 解决方案 #

核心思路：基于已知属性列表，从字符串末尾反向识别属性内容，保留前面的部分作为姓名。

步骤： #

1. 将数据导入Power Query（数据选项卡 → 自表格/区域）
2. 在Power Query编辑器中，进入高级编辑器，替换现有代码为以下内容（注意替换AttributeList中的属性为你实际的20种）：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 定义所有已知的属性列表
    AttributeList = {"UER", "RC", "COR", "ERR"},
    // 自定义函数：拆分姓名和属性
    SplitNameAndAttr = (inputText as text) as record =>
        let
            // 按空格拆分文本
            SplitParts = Text.Split(inputText, " "),
            // 反向遍历拆分后的部分，收集属于属性的内容
            ReverseParts = List.Reverse(SplitParts),
            AttrParts = List.Select(ReverseParts, each List.Contains(AttributeList, Text.Trim(_)) or (Text.Contains(_, ",") and List.AllTrue(List.Transform(Text.Split(Text.Trim(_), ","), each List.Contains(AttributeList, Text.Trim(_)))))),
            // 提取属性并拼接
            Attributes = if List.Count(AttrParts) > 0 then Text.Combine(List.Reverse(AttrParts), " ") else "",
            // 提取姓名：去掉属性部分后拼接
            NameParts = List.RemoveRange(SplitParts, List.Count(SplitParts)-List.Count(AttrParts)),
            Name = Text.Combine(NameParts, " ")
        in
            [Name=Name, Attributes=Attributes],
    // 应用自定义函数到Data列
    添加自定义列 = Table.AddColumn(源, "自定义", each SplitNameAndAttr([Data])),
    // 展开自定义列
    展开自定义列 = Table.ExpandRecordColumn(添加自定义列, "自定义", {"Name", "Attributes"}, {"Name", "Attributes"}),
    // 移除原Data列
    移除列 = Table.RemoveColumns(展开自定义列,{"Data"})
in
    移除列

3. 点击关闭并上载，即可得到拆分后的表格。

说明： #

• 代码会自动识别末尾的单个属性（如UER）或多个逗号分隔的属性（如COR, RC）
• 因为是从末尾反向匹配已知属性列表，所以Jr.、Sr.这类非属性后缀会被保留在姓名中

Excel 公式解决方案（适用于Excel 365/2021） #

利用动态数组和自定义LAMBDA函数实现：

步骤： #

1. 定义属性列表：在空白区域（比如Z1:Z4）输入所有已知属性，如UER、RC、COR、ERR
2. 定义LAMBDA函数（公式选项卡 → 定义名称）：
   • 名称：SplitPlayerData
   • 引用位置：

   =LAMBDA(input,attrList,LET(
       splitParts,TEXTSPLIT(input," "),
       reverseParts,TAKE(splitParts,-SEQUENCE(COUNTA(splitParts))),
       attrFilter,BYROW(reverseParts,LAMBDA(x,OR(ISNUMBER(MATCH(TRIM(x),attrList,0)),AND(ISNUMBER(MATCH(TRIM(TEXTSPLIT(x,",")),attrList,0)))))),
       attrCount,SUM(--attrFilter),
       attributes,IF(attrCount>0,TEXTJOIN(" ",,TAKE(splitParts,-attrCount)),""),
       name,TEXTJOIN(" ",,DROP(splitParts,-attrCount)),
       HSTACK(name,attributes)
   ))
   

3. 在B2单元格输入公式，下拉填充：

   =SplitPlayerData(A2,$Z$1:$Z$4)
   

4. 公式会自动拆分出姓名和属性，空属性会显示为空文本。

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