使用Excel公式重新格式化数据的技术求助

核心需求 #

• 对原始数据进行合并：当同一人员所属区域内，两行的B、C、D、E列内容完全相同时，仅保留一行
• 将匹配行对应的**各月份cost（成本）和discount price（折扣价）**整合到保留的行中
• 需支持处理匹配行被其他不同值行隔开的场景（例如John的数据中，B6-E6与B8-E8匹配但中间隔了一行）

解决方案：使用Power Query实现（推荐） #

Power Query能高效处理这类跨行匹配合并的需求，步骤如下：

1. 导入数据到Power Query：
   选中原始数据区域，点击「数据」选项卡 → 「从表格/区域」，勾选「我的表格有标题」后确认导入。

2. 分组合并数据：
   在Power Query编辑器中，点击「转换」选项卡 → 「分组依据」：

   • 分组列选择人员列（对应原始数据的A列）、B列、C列、D列、E列
   • 新列名设置为「合并数据」，操作选择「所有行」

3. 提取并整合月份数据：
   添加自定义列，用公式提取并合并各月份的cost和discount price：

   = let
       rows = [合并数据],
       mergeCost = List.Combine(List.Transform(rows, each {_[一月cost],_[二月cost],_[三月cost]})) // 根据实际月份列扩展
       mergeDiscount = List.Combine(List.Transform(rows, each {_[一月discount price],_[二月discount price],_[三月discount price]}))
       in Record.FromList(mergeCost & mergeDiscount, List.Combine(List.Transform({"一月","二月","三月"}, each {_ & "cost", _ & "discount price"})))
   

   （注：需根据实际存在的月份列调整公式中的月份名称）

4. 展开自定义列并整理结构：
   点击自定义列右侧的展开按钮，选择所有生成的月份字段，然后删除「合并数据」列，调整列顺序至需求的布局。

5. 加载回Excel：
   点击「主页」选项卡 → 「关闭并上载」，将处理后的数据加载到新工作表，即可得到符合要求的Desired Output布局。

备选方案：数组公式（适合小规模数据） #

如果数据量不大，可使用数组公式实现（以一月cost为例，假设原始数据在A1:X100区域）：

=IFERROR(INDEX($F$1:$F$100, MATCH($B2&$C2&$D2&$E2&$A2, $A$1:$A$100&$B$1:$B$100&$C$1:$C$100&$D$1:$D$100&$E$1:$E$100, 0)), "")

（需按Ctrl+Shift+Enter作为数组公式输入，其他月份字段同理修改列引用）

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