Excel动态生成关联Staff与Courses的课时分配表格问题求助

第一步：将源表转为结构化表格（超级表格） #

这是实现动态更新的核心基础，操作简单：

• 选中Staff表的所有内容（包含表头），按下Ctrl+T，勾选「我的表格有标题」后点击确定。右键表格→「表格名称」，将其重命名为Staff。
• 用同样的方法处理Courses表，重命名为Courses。
  后续无论在这两个表的中间或末尾新增员工/课程，表格都会自动纳入新内容，所有引用它的公式也会同步更新。

第二步：搭建动态课时分配表 #

1. 自动生成员工列 #

在新工作表的A2单元格输入公式：

=Staff[姓名]

（仅支持Excel 365/2021及以后版本，输入后会自动「溢出」所有员工姓名到A列下方；旧版Excel请输入=INDEX(Staff[姓名],ROW()-1)，下拉填充至足够行数，新增员工后继续下拉即可）

2. 自动生成课程表头 #

在新工作表的B1单元格输入公式：

=Courses[课程代码]

（365版本会自动溢出所有课程代码到右侧列；旧版输入=INDEX(Courses[课程代码],COLUMN()-1)，右拉填充即可）

3. 核心：让课时内容与员工/课程绑定匹配，避免错位 #

在B2单元格输入以下公式，随后向右、向下填充至所有单元格：

=IFERROR(INDEX(你的课时存储区,MATCH($A2,Staff[姓名],0),MATCH(B$1,Courses[课程代码],0)),"")

• 把你的课时存储区替换为你原本记录员工-课程对应课时的区域；如果之前没有专门存储区，这个新表格就是存储区，可先留空直接输入课时，后续调整课程位置时，公式会自动定位到对应值。
• 原理：无论课程在Courses表的位置如何变动，MATCH(B$1,Courses[课程代码],0)都会找到该课程代码在源表的准确位置，结合员工姓名的匹配，就能精准对应课时值，不会因新增课程出现错位。

若使用Excel 365，可使用更简洁的公式：

=IFERROR(XLOOKUP(B$1,Courses[课程代码],XLOOKUP($A2,Staff[姓名],你的课时存储区)),"")

额外优化：把课时分配表也转为结构化表格 #

完成上述步骤后，选中课时分配表的全部区域（含A列员工和B列起的课程表头），按下Ctrl+T转成结构化表格，命名为CourseAssignments。后续新增员工/课程后，只需拖动表格右下角边框，即可自动扩展区域并应用公式，无需手动下拉右拉。

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