如何用Excel函数将单列条码数据按规则转换为多列表格？

需求说明 #

• 原始数据：A列存储扫描的条码数据，示例：

XXID0081、45011、654000、2、654001、3、654002、4、XXID0082、45012、785902、2、3、XXID0083、45013、888981、2、888982、3

• 目标格式：转换为4列表格，相同XXID、5位编码、6位编码对应的数量需求和，示例：

XXID0081 45011 654000 2
XXID0081 45011 654001 3
XXID0081 45011 654002 4
XXID0082 45012 785902 5
XXID0083 45013 888981 2
XXID0083 45013 888982 3

已尝试WRAPROWS(A2:A300,4)但无效，且已用Power Query实现（附代码），现需纯Excel函数方案。

函数解决方案 #

假设原始数据范围为A2:A20（可根据实际调整），分步骤实现：

1. 提取并填充XXID（结果列A） #

在目标单元格（如D2）输入公式，下拉填充：

=LOOKUP(1,0/(LEFT($A$2:$A$20,4)="XXID"),$A$2:$A$20)

该公式会自动匹配当前行上方最近的XXID并填充。

2. 提取并填充5位编码（结果列B） #

在E2输入公式，下拉填充：

=LOOKUP(1,0/(LEN($A$2:$A$20)=5),$A$2:$A$20)

匹配并填充最近的5位长度编码。

3. 提取并填充6位编码（结果列C） #

在F2输入公式，下拉填充：

=LOOKUP(1,0/(LEN($A$2:$A$20)=6),$A$2:$A$20)

匹配并填充最近的6位长度编码。

4. 提取数量值（结果列D） #

在G2输入公式，下拉填充：

=IF(LEN($A2)<4,$A2,"")

筛选出长度小于4的数量值，空行后续过滤。

5. 过滤有效行并求和 #

方法1：使用GROUPBY（Excel 365+推荐）

直接生成去重求和后的结果：

=GROUPBY(FILTER(D2:G20,G2:G20<>""),[[Column1]:[Column3]],FILTER(D2:G20,G2:G20<>""),[[Column4]],SUM,0,0)

方法2：UNIQUE+SUMIFS（兼容低版本）

• 先提取唯一的前三列组合：

  =UNIQUE(FILTER(D2:F20,G2:G20<>""))
  

• 对每个组合求和（假设唯一组合在H2:J2开始的区域）：

  =SUMIFS(G:G,D:D,H2,E:E,I2,F:F,J2)
  

附原Power Query代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([A], "XXID") then [A] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([A], "UID")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if Text.Length([A]) = 5 then [A] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom",{"Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down1", "Custom.2", each if Text.Length([A]) = 6 then [A] else null),
    #"Filled Down2" = Table.FillDown(#"Added Custom1",{"Custom.2"}),
    #"Filled Up" = Table.FillUp(#"Filled Down2",{"Custom.2"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Up", "Custom.3", each if Text.Length([A]) < 4 then [A] else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each [Custom.3] <> null and [Custom.3] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Custom.1", "Custom.2", "A"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"A", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Custom", "Custom.1", "Custom.2"}, {{"A", each List.Sum([A]), type nullable text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Custom", type text}, {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"A", type number}})
in
    #"Changed Type2"

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