Excel 2016二维数据扁平化需求（非Unpivot方案）

用Python这类编程语言处理起来很简单，但Excel内置函数无法满足需求，现求助解决以下问题：

输入数据 #

示例为动态生成表头的二维表格，因表头动态生成无法转为表格使用Unpivot功能，部分门店数据列冗余，目前暂按此格式处理。

需求目标 #

需将核心数据区域扁平化为每行对应一个值的格式，适配数据库导入要求——每个门店、每个日期对应一行，且支持新增门店时自动适配。

限制条件 #

仅能使用Excel 2016，无法使用2019及以后版本的函数（如LET、溢出功能等）。

现有思路 #

计划通过按钮触发VBA宏实现，已编写伪代码但存在多处疑问，期望代码简洁易维护，可通过「控制面板」工作表的命名单元格自动适配数据范围变化。控制面板公式如下：

伪代码需完善为可运行的宏，实现将数据转为Code、Date、Value三列输出到指定工作表，且不覆盖D列及以后的手动公式列。

Sub makeOutput()
    Dim srcSheet As Worksheet
    Dim ctrlSheet As Worksheet
    Dim outputSheet As Worksheet
    Dim i As Long, j As Long
    Dim dataHeight As Long, dataWidth As Long
    Dim dataStartCell As Range
    Dim outputArr() As Variant
    Dim ccRange As Range
    Dim dateRange As Range
    Dim srcDataRange As Range
    Dim counter As Long
    
    ' 初始化工作表对象
    Set srcSheet = ThisWorkbook.Sheets("Daily Sales Forecast")
    Set outputSheet = ThisWorkbook.Sheets("Daily Sales Output")
    Set ctrlSheet = ThisWorkbook.Sheets("Control Panel")
    
    ' 从控制面板读取数据范围参数
    With ctrlSheet
        dataHeight = .Range("Data_Height").Value
        dataWidth = .Range("Data_Width").Value ' 修复伪代码中的赋值错误
        Set dataStartCell = srcSheet.Cells(.Range("Start_Row").Value, .Range("Start_Col").Value)
    End With
    
    ' 定义核心数据范围、门店Code范围、日期范围
    With srcSheet
        Set srcDataRange = dataStartCell.Resize(dataHeight, dataWidth)
        Set ccRange = .Range("B4:B7") ' 门店Code列，可根据实际情况调整
        Set dateRange = dataStartCell.Offset(-1).Resize(1, dataWidth) ' 日期行位于数据区域上方一行
    End With
    
    ' 初始化输出数组：行数为总数据点数量，列数固定3列(Code, Date, Value)
    ReDim outputArr(1 To dataHeight * dataWidth, 1 To 3)
    
    ' 填充输出数组
    counter = 0
    For i = 1 To dataWidth ' 遍历日期列
        For j = 1 To dataHeight ' 遍历门店行
            counter = counter + 1
            outputArr(counter, 1) = ccRange(j).Value ' 门店Code
            outputArr(counter, 2) = dateRange(i).Value ' 日期
            outputArr(counter, 3) = srcDataRange(j, i).Value ' 销售数值
        Next j
    Next i
    
    ' 清除输出表原有数据（仅前3列，保留D列及以后的手动内容）
    outputSheet.Range("A2:C" & outputSheet.Cells(outputSheet.Rows.Count, "A").End(xlUp).Row).ClearContents
    
    ' 将数组写入输出表，从A2开始（不覆盖表头）
    outputSheet.Range("A2").Resize(UBound(outputArr, 1), 3).Value = outputArr
    
    MsgBox "数据转换完成！", vbInformation
End Sub

代码说明 #

• 参数适配：通过控制面板的命名单元格自动获取数据范围，新增门店或调整数据区域位置时无需修改代码
• 数据保护：仅清除输出表A-C列的旧数据，保留D列及以后的手动公式列不被覆盖
• 效率优化：使用数组批量处理数据，比逐单元格写入更高效
• 可扩展性：门店Code列硬编码为B4:B7，后续若有变动可直接修改该行代码

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