如何使用VBA将多列月度数据转换为堆叠式报表并实现自动联动更新

适用Excel 365/2021（动态数组版本，一键生成无空行） #

预设Sheet1结构：A列为员工姓名，B列起每3列依次为对应月份的「日期、日、小时」，共36列月度数据，第1行为表头，第2行起为实际数据。

1. 在Sheet2的A1:D1区域依次输入表头：员工姓名、日期、日、小时
2. 在Sheet2的A2单元格输入如下公式，按回车即可自动生成所有符合要求的堆叠数据，无需下拉：

=LET(
  raw,Sheet1!A2:DK1000,
  name_col,INDEX(raw,,1),
  month_blocks,WRAPCOLS(DROP(raw,,1),3),
  stack_data,FILTER(TOCOL(IF(SEQUENCE(ROWS(month_blocks,,0))=0,name_col,month_blocks),,TRUE),TOCOL(INDEX(month_blocks,3,0),,TRUE)<>""),
  WRAPROWS(stack_data,4)
)

公式说明 #

• 仅需调整raw参数后的Sheet1!A2:DK1000为你Sheet1的实际数据范围即可
• 自动按每3列拆分月度数据块，匹配对应行的员工姓名完成堆叠
• 内置空值过滤逻辑，自动跳过各月份天数不足产生的空行，结果无冗余
• 所有数据直接引用Sheet1单元格，修改Sheet1的小时数据会自动同步到Sheet2，完全符合引用关联要求

适用旧版Excel（无动态数组函数版本） #

通过基础函数+辅助列实现：

1. Sheet2表头设置同上述版本，新增E列为辅助列（后续可隐藏）
2. E2单元格输入公式=IFERROR(IF(D1<>"",E1,E1+1),1)，下拉到超过「员工数*366」的行数（覆盖全年最大可能数据量）
3. A2单元格输入公式：=INDEX(Sheet1!A:A,INT((ROW(A2)-2)/(31*12))+2)
4. B2单元格输入公式：=INDEX(Sheet1!$2:$1000,INT((ROW(A2)-2)/(31*12))+2,CEILING(MOD(ROW(A2)-2,31*12)/31+1,1)*3+MOD(ROW(A2)-2,31)+1)
5. C2单元格输入公式：=INDEX(Sheet1!$2:$1000,INT((ROW(A2)-2)/(31*12))+2,CEILING(MOD(ROW(A2)-2,31*12)/31+1,1)*3+MOD(ROW(A2)-2,31)+2)
6. D2单元格输入公式：=INDEX(Sheet1!$2:$1000,INT((ROW(A2)-2)/(31*12))+2,CEILING(MOD(ROW(A2)-2,31*12)/31+1,1)*3+MOD(ROW(A2)-2,31)+3)
7. 选中A2:D2下拉填充到和E列相同行数，筛选D列（小时列）去掉空值即可

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