Google Sheets自动跨表取数：每日Staffing Sheet同步至Sort Sheet

问题背景 #

日常用Staffing Sheet模板做人员排班：扫描员工工牌自动填充员工ID，相邻单元格通过公式生成员工姓名，姓名右侧设隐藏单元格存储当日岗位（简化表格显示）。需要搭建Sort Sheet模板实现：

• 每日新建的Staffing Sheet数据自动同步到Sort Sheet并完成排序
• 无需手动修改Sort Sheet公式，能灵活切换数据源（新建Staffing Sheet后直接指定即可拉取数据）
  之前尝试在Sort Sheet的A1单元格输入目标Staffing Sheet名称，用=A1!(指定公式)的形式调用数据，但未生效。

解决方案 #

1. 核心：用INDIRECT函数实现动态引用 #

直接用=A1!XX的形式无法解析单元格里的文本为工作表引用，必须用INDIRECT函数完成文本到引用的转换：

• 在Sort Sheet的A1单元格输入目标Staffing Sheet的完整名称（比如「2024-05-20排班表」）
• 假设Staffing Sheet中，员工ID在B列、姓名在C列、岗位在D列，数据从第2行开始到第100行：
  • Sort Sheet的B2单元格输入公式：=INDIRECT("'"&$A$1&"'!B2")，下拉填充到B100，即可拉取对应表的ID列数据
  • C2单元格输入：=INDIRECT("'"&$A$1&"'!C2")，下拉填充拉取姓名
  • D2单元格输入：=INDIRECT("'"&$A$1&"'!D2")，下拉填充拉取隐藏的岗位数据

2. 自动排序实现 #

方法1：Excel 365/2021 动态数组（推荐）

直接用SORT函数生成自动排序且实时更新的结果：

• 在Sort Sheet的空白区域（比如F2单元格）输入公式：=SORT(B2:D100,3,1)
  • 参数说明：B2:D100是待排序的数据源区域，3代表按第3列（岗位）排序，1表示升序（改为0则是降序）
  • 公式会自动溢出填充所有排序后的结果，且原数据源更新时自动同步排序

方法2：传统手动排序+刷新

• 选中Sort Sheet中已同步的数据源区域（B2:D100）
• 点击「数据」选项卡→「排序」，设置好排序规则（比如按岗位、姓名排序）
• 后续数据源更新后，重新点击「排序」按钮即可刷新结果

3. 关键注意事项 #

• 如果Staffing Sheet的名称包含空格、特殊字符（比如括号、中文），必须用单引号将表名括起来，所以公式里的"'"&$A$1&"'!"是核心，避免引用报错
• 确保Sort Sheet和目标Staffing Sheet在同一个工作簿内，跨工作簿引用需要额外添加工作簿路径（比如INDIRECT("'[工作簿名.xlsx]"&$A$1&"'!B2")）
• 若要实现完全自动刷新，可结合VBA的Worksheet_Change事件，当A1单元格的表名改变时自动触发排序

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