如何仅用Excel公式动态填充并扩展锁定仪表板表格列？

问题分析 #

你之前用的两个公式出错原因：

• =Datasheet[@ID]：属于表格内部的单行结构化引用，仅能在数据源表格自身内使用，跨表格引用时会因行匹配逻辑冲突返回#VALUE!
• =Datasheet[[#Data];[ID]:[ID]]：引用数据源ID列全量数据，但因目标区域有非空内容、表格未开启自动扩展或保护限制，导致溢出错误#Spill!

方案1：动态数组公式（Excel 365/2021 适用） #

1. 先解除仪表板工作表保护，选中黑色表格，在「表格设计」选项卡勾选「调整表格大小」，确保表格允许自动扩展；重新保护时，在设置中勾选「允许用户编辑区域」，将公式所在单元格加入可编辑范围。
2. 在黑色表格ID列的第一个数据单元格（如BlackTable[ID]首行）输入公式：

   =Datasheet[ID]
   

   该公式会自动溢出数据源ID列的所有数据，且数据源新增行时，仪表板表格会自动同步扩展（需确保溢出路径无其他内容遮挡）。

方案2：INDEX+SEQUENCE 兼容旧版本（Excel 2019及以前） #

如果你的Excel不支持动态数组，用以下公式替代：

=INDEX(Datasheet[ID],SEQUENCE(COUNTA(Datasheet[ID])))

• COUNTA(Datasheet[ID])：统计数据源ID列非空行数
• SEQUENCE：生成对应行数的序列索引
• INDEX：按索引提取数据源ID值
  此公式会自动适配数据源行数变化，需确保黑色表格下方预留足够空行，或开启表格自动调整大小。

关键注意事项 #

• 若数据源ID列存在空白行，将COUNTA(Datasheet[ID])替换为ROWS(Datasheet[ID])（结构化表格的ROWS会返回表格总行数）
• 保护工作表时，必须允许公式单元格的编辑权限，否则无法触发动态更新

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