基于单元格值重排Excel数据的技术问题求助

嘿，我来帮你搞定这个Excel数据重排的麻烦！看你的示例数据，每个单元格里都是用竖线分隔的客户信息，但每个客户的字段还不统一——有的有Location，有的只有Mobile，要把这些乱糟糟的信息规整成标准的列（比如姓名、Customer ID、Location这些）对吧？

先把你的原始数据示例整理出来，方便对照：

原始单元格内容示例：

Mr. Ajith | Customer ID: 119982928 | Location: Mumbai, India | Profession: Businessman | Birth Date: 12 July, 1989 | Type: Regular | Acc No: Not Known | Tel. No: Not Available
Mr. Sumon | Customer ID: 119934534 | Profession: Businessman | Type: Regular | Acc No: Not Known | Mobile: 1234567819
Mr. Arafat | Customer ID: 119886140 | Mobile: 678868 | Qualification: Graduate

Power Query是Excel自带的工具，专门对付这种非结构化的海量数据，比手动公式或者VBA高效太多，而且操作直观，步骤如下：

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

• 选中你的数据列（假设所有客户信息都在A列）
• 点击顶部菜单栏的数据选项卡 → 选择从表格/区域（旧版Excel可能叫“从表格”）
• 弹出的对话框里，如果你的A列第一行是标题（比如“客户信息”）就勾选“我的表格有标题”，如果第一行就是客户数据，就不勾，之后再调整表头。

步骤2：把单元格内容拆成单独的键值对行 #

• 在Power Query编辑器里，选中数据列 → 点击转换选项卡 → 拆分列 → 按分隔符
• 分隔符选自定义，输入英文竖线|，然后一定要选拆分为行（划重点！不是拆分成列，因为每个客户的字段数量不一样，拆成行才能统一处理）

步骤3：清理文本并拆分键和值 #

现在每行是一个单独的条目，比如Mr. Ajith、Customer ID: 119982928，接下来要把内容理干净：

• 先选中拆分后的列 → 转换 → 格式 → 修剪（去掉每个文本前后的多余空格，比如 Location: Mumbai...前面的空格）
• 再次拆分列：选中列 → 拆分列 → 按分隔符 → 自定义输入英文冒号:，选择拆分为列，然后选最右侧的分隔符（避免像Birth Date: 12 July, 1989里的日期冒号被误拆）

步骤4：把行转成规范的列（核心步骤） #

现在你有两列：一列是键（比如Customer ID、Location），一列是对应的值。接下来要把同一个客户的所有信息归到一行：

1. 标记每个客户的姓名：
   • 点击添加列 → 自定义列，输入这个公式：

     if not Text.Contains([拆分的列1], ":") then [拆分的列1] else null
     

     这个公式会把没有冒号的行（也就是客户姓名行）标记出来，其他行留空。
   • 选中这个新的自定义列 → 转换 → 填充 → 向下，这样每个客户的所有行都会带上对应的姓名，相当于给每条信息打了“归属标签”。
2. 透视列生成规范表格：
   • 选中姓名列（刚才填充的列）和键列（拆分的列1），然后点击转换 → 透视列
   • 在弹出的对话框里，值列选择“拆分的列2”，聚合函数选“不要聚合”（因为每个客户的每个字段只有一个值）

步骤5：整理并加载回Excel #

• 现在你已经得到了规整的表格：每一行是一个客户，每一列是一个字段（姓名、Customer ID、Location等）
• 可以按需调整列的顺序，或者删除不需要的列
• 点击主页 → 关闭并上载，把处理好的数据加载回Excel工作表就搞定了！

备选方案：公式提取（适合小量数据） #

如果你的数据量不大，也可以用Excel公式逐个提取字段，但海量数据不推荐（会很卡）。比如提取Customer ID的公式：

=IFERROR(TRIM(MID(SUBSTITUTE(A1,"|",REPT(" ",1000)),SEARCH("Customer ID:",A1)+12,1000)),"")

原理是把竖线换成大量空格，定位到“Customer ID:”的位置，提取后面的内容。每个字段都要写对应公式，比较繁琐，所以还是优先用Power Query。

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