Excel格式转换需求：将宽表转为系统友好型规范长表

一、动态数组公式方案（适用于Excel 365/2021及以上） #

假设原表结构：

• A列：ProductId（表头A1，数据从A2开始）
• B_{E列：`Client1`}Client4（表头B1_{E1，对应金额数据从B2}E2开始）

操作步骤： #

1. 在空白区域（比如G1:I1）输入表头：ClientID、ProductId、ProductAmount
2. 在G2单元格输入公式，按回车自动填充：

   =TOCOL(IF(B2:E100>0,B1:E1,""),3)
   

3. 在H2单元格输入公式：

   =TOCOL(IF(B2:E100>0,A2:A100,""),3)
   

4. 在I2单元格输入公式：

   =TOCOL(IF(B2:E100>0,B2:E100,""),3)
   

说明： #

• TOCOL函数将二维数据转换为一维数组，参数3表示忽略空值
• IF(B2:E100>0, ..., "")仅保留金额大于0的对应记录，不符合条件的返回空值
• 公式会自动动态更新，原表数据修改后长表同步变化
• 若原表数据范围变化，只需调整公式中的单元格区域（比如把B2:E100改成实际数据范围）

二、VBA宏方案（适用于所有Excel版本，月度一键复用） #

宏代码： #

Sub ConvertWideToLong()
    Dim wsRaw As Worksheet, wsLong As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long, k As Long
    
    ' 指定原数据工作表名称，可根据实际修改
    Set wsRaw = ThisWorkbook.Worksheets("宽表数据")
    
    ' 检查并创建长表工作表
    On Error Resume Next
    Set wsLong = ThisWorkbook.Worksheets("长表数据")
    On Error GoTo 0
    If wsLong Is Nothing Then
        Set wsLong = ThisWorkbook.Worksheets.Add
        wsLong.Name = "长表数据"
    End If
    
    ' 清空长表旧数据，保留表头
    wsLong.Cells.Clear
    wsLong.Range("A1:C1").Value = Array("ClientID", "ProductId", "ProductAmount")
    
    ' 获取原表数据边界
    lastRow = wsRaw.Cells(wsRaw.Rows.Count, "A").End(xlUp).Row
    lastCol = wsRaw.Cells(1, wsRaw.Columns.Count).End(xlToLeft).Column
    
    k = 2 ' 长表数据起始行
    
    ' 遍历所有产品和客户，筛选有效记录
    For i = 2 To lastRow
        For j = 2 To lastCol
            If wsRaw.Cells(i, j).Value > 0 Then
                wsLong.Cells(k, 1).Value = wsRaw.Cells(1, j).Value
                wsLong.Cells(k, 2).Value = wsRaw.Cells(i, 1).Value
                wsLong.Cells(k, 3).Value = wsRaw.Cells(i, j).Value
                k = k + 1
            End If
        Next j
    Next i
    
    ' 自动优化列宽
    wsLong.Columns("A:C").AutoFit
    MsgBox "转换完成！共生成" & k - 2 & "条有效记录。"
End Sub

使用方法： #

1. 打开Excel文件，按Alt+F11打开VBA编辑器
2. 右键点击左侧工程窗口中的当前工作簿，选择插入→模块
3. 将上述代码粘贴到模块窗口中，修改wsRaw = ThisWorkbook.Worksheets("宽表数据")中的工作表名称为你的原表名称
4. 关闭VBA编辑器，按Alt+F8，选择ConvertWideToLong并点击执行
5. 月度操作时直接重复步骤4即可，每次运行会自动清空旧长表数据并重新生成

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