无VBA与Power Query的自动刷新预算工作簿实现及复刻方法求教

核心逻辑：靠Excel动态数组+结构化表实现，无代码无Power Query #

原工作簿没用到宏或Power Query，核心是用Excel 365/2021自带的动态数组函数，配合结构化表实现自动扩展和刷新。

第一步：把人员源数据转成结构化表 #

• 选中你的人员信息区域（要包含表头），按Ctrl+T，勾选“表包含标题”，给表起个好记的名字比如tbl_Staff（在表格工具的“设计”标签里修改）
• 结构化表的好处是你新增行时，表会自动扩大，后面的公式能自动识别新数据

第二步：用动态数组拆分出按在职月数的预算行 #

假设源表有这些字段：姓名、入职日期、离职日期（或预算截止月）、岗位、每月预算额

1. 先加辅助列计算在职月数：在源表空白列表头写“在职月数”，下面单元格用公式DATEDIF([@入职日期], [@离职日期], "M")+1（+1是算上入职当月）
2. 到预算表的A2单元格，粘贴下面的动态数组公式，回车后会自动生成所有拆分好的预算行：

=LET(
    源数据, tbl_Staff[#All],
    人员行数, ROWS(源数据)-1,
    在职月数, tbl_Staff[在职月数],
    总预算行数, SUM(在职月数),
    人员索引, TOCOL(SEQUENCE(人员行数,1,1,1)/1^0,1),
    月份索引, TOCOL(SEQUENCE(1,MAX(在职月数),1,1),1),
    筛选条件, 月份索引 <= INDEX(在职月数,人员索引),
    姓名列, INDEX(tbl_Staff[姓名],FILTER(人员索引,筛选条件)),
    对应月份, INDEX(EDATE(tbl_Staff[入职日期],SEQUENCE(MAX(在职月数),1,0)),FILTER(月份索引,筛选条件)),
    预算额列, INDEX(tbl_Staff[每月预算额],FILTER(人员索引,筛选条件)),
    HSTACK(姓名列, 对应月份, 预算额列)
)

这个公式的核心是：生成每个人员重复对应在职月数的索引，再把姓名、对应月份、预算额拼接起来，动态数组会自动展开所有行。

第三步：设置“刷新”触发方式 #

原工作簿的“数据向导刷新”其实就是Excel自带的全部刷新功能，动态数组一般会自动更新，若遇延迟可按以下操作：

• 点击顶部“数据”标签，选择“全部刷新”，或直接按Alt+A+R快捷键
• 嫌找按钮麻烦的话，可把“全部刷新”加到快速访问工具栏：点快速访问栏的下拉箭头→选“其他命令”→从“所有命令”中找到“全部刷新”添加，以后点这个按钮就能触发更新

优化卡顿问题 #

原工作簿卡顿大概率是因为用了整列引用或非结构化数据，优化点：

• 源表和预算表都用结构化表，缩小Excel的计算范围
• 把自动计算改成手动：文件→选项→公式→选择“手动重算”，需要刷新时按F9或点“全部刷新”
• 所有公式都用结构化表的列引用（比如tbl_Staff[姓名]），避免使用A:A这类整列引用

内容的提问来源于stack exchange，提问作者Ana Clara Miranda Castro