基于指定条件将物料代码列转换为矩阵的技术求助

数据透视表的局限性 #

数据透视表擅长聚合统计，但要生成按首版本、末版本、行内版本数定义的自定义矩阵结构，它的布局灵活性不足，无法直接满足你的需求，不推荐作为核心方案。

最优方案分三类（按技术门槛选择） #

方案A：Excel公式组合（适合无编程基础） #

步骤1：标记分组与版本顺序

假设数据在A列（Mcode）、B列（版本号），新增两个辅助列：

• C列（分组ID）：输入=IF(A2=A1,C1,C1+1)后下拉，给每个Mcode组分配唯一ID
• D列（组内版本序号）：输入=COUNTIFS($A$2:A2,A2)后下拉，得到每个Mcode在组内的版本顺位

步骤2：生成目标矩阵

假设「行内版本数」存于E1单元格，目标矩阵起始于K2单元格（K1输入要生成矩阵的分组ID）：

• 矩阵单元格输入数组公式（Excel 365可直接回车，旧版本按Ctrl+Shift+Enter）：

  =INDEX($B:$B,SMALL(IF($C:$C=K1,ROW($C:$C)),(ROW()-ROW($K$2))*$E$1+COLUMN()-COLUMN($L$2)+1))
  

• 快速获取首/末版本验证范围：
  • 首版本：=XLOOKUP(K1,$C:$C,$B:$B,,0,1)
  • 末版本：=XLOOKUP(K1,$C:$C,$B:$B,,0,-1)

方案B：Power Query（适合批量处理，效率更高） #

步骤1：导入数据到Power Query

选中数据区域，点击「数据」选项卡→「从表格/区域」，进入PQ编辑器。

步骤2：分组生成矩阵

1. 按Mcode分组，分组操作选择「所有行」，得到每个Mcode对应的行集合
2. 添加自定义列提取版本列表：=Table.Column([Grouped Rows],"版本号")
3. 添加自定义列按「行内版本数」拆分列表：=List.Split([版本列表], [行内版本数])（需确保「行内版本数」已导入PQ）
4. 展开子列表为矩阵：点击自定义列的展开按钮，选择「展开到新行」，再将子列表展开为列，即可得到结构化矩阵

方案C：VBA脚本（适合复杂自定义需求） #

如果需要更灵活的格式控制，可使用以下VBA脚本（运行前调整参数）：

Sub GenerateMcodeMatrix()
    Dim ws As Worksheet, lastRow As Long, i As Long, groupID As Integer
    Dim mcode As String, startRow As Long, endRow As Long, rowPerMatrix As Integer
    Dim matrixStartCol As Integer, matrixRow As Integer, matrixCol As Integer
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    rowPerMatrix = ws.Range("E1").Value '行内版本数所在单元格
    matrixStartCol = 7 '矩阵从G列开始
    groupID = 0
    mcode = ws.Range("A2").Value
    startRow = 2
    
    For i = 3 To lastRow + 1
        If ws.Range("A" & i).Value <> mcode Or i = lastRow + 1 Then
            endRow = i - 1
            groupID = groupID + 1
            '写入组基础信息
            ws.Cells(groupID + 1, matrixStartCol).Value = mcode
            ws.Cells(groupID + 1, matrixStartCol + 1).Value = "首版本: " & ws.Range("B" & startRow).Value
            ws.Cells(groupID + 1, matrixStartCol + 2).Value = "末版本: " & ws.Range("B" & endRow).Value
            '生成矩阵
            matrixRow = groupID + 2
            matrixCol = matrixStartCol
            For j = startRow To endRow
                ws.Cells(matrixRow, matrixCol).Value = ws.Range("B" & j).Value
                matrixCol = matrixCol + 1
                If matrixCol > matrixStartCol + rowPerMatrix - 1 Then
                    matrixCol = matrixStartCol
                    matrixRow = matrixRow + 1
                End If
            Next j
            '更新下一组信息
            mcode = ws.Range("A" & i).Value
            startRow = i
        End If
    Next i
End Sub

选择建议 #

• 新手优先选方案A，公式组合快速实现，无需额外工具；
• 数据量可能增大时选方案B，Power Query批量处理效率更高；
• 有复杂格式需求时选方案C，VBA灵活性最强。

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