Google Sheets 如何按ISO周数高效跨表导入匹配员工工时数据

核心逻辑 #

• 每个员工工作表仅调用1次IMPORTRANGE全量拉取ISO周数与对应工时的映射关系，无需逐单元格重复调用
• 通过匹配函数关联汇总表预设的ISO周数与导入的映射数据，项目启动前的周数自动返回空值，无需提前筛选数据

具体操作步骤 #

步骤1：搭建原始数据暂存区（可隐藏） #

在汇总表中新增一个单独的工作表作为暂存区，按行存储每个员工的导入数据：

• A列填员工姓名，B列填对应员工工作表的URL
• C列输入全量导入公式，单员工仅需写1次：
  =IMPORTRANGE(B2,"'PROJECT PLAN'!E30:AH33")
  公式执行后会自动返回4行数据，其中第1行是对应列的ISO周数，第4行是对应周的工时数据。

步骤2：汇总表主区域自动匹配 #

假设汇总表主区域的第1行C列起已预先填好所有需要统计的ISO周数，在首个需要填充工时的单元格（如C2）输入匹配公式：
=IFERROR(XLOOKUP(C$1, 暂存区!C$1:AH$1, 暂存区!C$4:AH$4, ""), "")

• 该公式会自动用当前列的ISO周数匹配暂存区的周数，匹配成功返回对应工时，匹配失败（项目未启动的周数）直接返回空值
• 公式仅需写1次，向右、向下拖拽即可完成所有员工所有周数的工时填充

可选优化：无需暂存区的数组公式方案 #

如果不想单独设置暂存区，可在每个员工行的首个工时单元格输入数组公式，单员工仅需输入1次即可自动生成整行工时：
=BYCOL(C$1:AH$1, LAMBDA(week, IFERROR(XLOOKUP(week, INDEX(IMPORTRANGE("对应员工工作表URL","'PROJECT PLAN'!E30:AH30"),1,0), INDEX(IMPORTRANGE("对应员工工作表URL","'PROJECT PLAN'!E33:AH33"),1,0), ""), "")))

内容的提问来源于stack exchange，提问作者hunter-gatherers