2023年月度业绩Excel动态表格创建技术求助

核心技术选型 #

优先使用Excel 365/2021支持的动态数组函数（UNIQUE、XLOOKUP、SUMIFS）搭配结构化表格，这是目前最稳定高效的方案——相比OFFSET这类易失性函数，动态数组不会额外消耗计算资源，且自动更新逻辑更直观。

分步实现（附示例公式） #

假设你的原始数据已转为结构化表格（选中数据区域→Ctrl+T→勾选「我的表格有标题」），表格命名为Table_Sales，表头包含日期、员工姓名、业绩金额三列。

1. 生成动态月份表头 #

在目标表格的表头起始单元格（如B1）输入以下公式，自动提取并排序数据源中所有不重复的月份：

• 显示数字月份：

=SORT(UNIQUE(MONTH(Table_Sales[日期])),1,1)

• 显示中文月份名称：

=TEXT(SORT(UNIQUE(MONTH(Table_Sales[日期])),1,1),"[$-zh-CN]mmmm")

新增包含新月份的数据时，表头会自动扩展更新。

2. 提取/汇总员工月度业绩 #

假设目标表格A列为固定的员工姓名（如A2为「张三」），在B2单元格输入对应公式：

• 单条业绩匹配（员工每月仅一条数据）：

=XLOOKUP($A2&"-"&B$1, Table_Sales[员工姓名]&"-"&MONTH(Table_Sales[日期]), Table_Sales[业绩金额], 0)

• 多条业绩汇总（员工每月有多条数据）：

=SUMIFS(Table_Sales[业绩金额], Table_Sales[员工姓名], $A2, MONTH(Table_Sales[日期]), MONTH(B$1))

选中B2单元格后向右、向下拖动填充（Excel 365中公式会自动溢出填充），新增员工或业绩数据时，表格会自动同步更新。

3. 确保自动更新的关键设置 #

• 始终使用结构化表格存储原始数据：新增数据到表格下方时，表格会自动扩展范围，函数会自动引用新数据。
• Excel 365用户开启「自动溢出」功能（默认已开启），无需手动拖动公式。

旧版Excel兼容方案（2019及更早版本） #

若无法使用动态数组函数，可通过动态名称定义+数组公式实现：

1. 定义动态数据源：点击「公式」→「名称管理器」→「新建」，名称设为Dynamic_Sales，引用位置：

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

2. 生成动态表头（需按Ctrl+Shift+Enter作为数组公式输入）：

=INDEX(MONTH(INDEX(Dynamic_Sales,,1)),SMALL(IF(MATCH(MONTH(INDEX(Dynamic_Sales,,1)),MONTH(INDEX(Dynamic_Sales,,1)),0)=ROW(INDEX(Dynamic_Sales,,1))-ROW(Sheet1!$A$1),ROW(INDEX(Dynamic_Sales,,1))-ROW(Sheet1!$A$1)),COLUMN(A1)))

3. 提取业绩仍用INDEX+MATCH组合，新增数据后需手动刷新公式。

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