如何在字符串中引用不同工作表的单元格值实现跨表批量内容拼接

需求说明 #

• 固定文本模板：The <SourceWorksheetCell:StoryNamename> of the story has three main characters: <SourceWorksheetCell:name1>, <SourceWorksheetCell:name2>, and <SourceWorksheetCell:name3>
• 场景约束：
  • 所有数据源存储在当前工作簿的其他工作表中
  • 源工作表每一行对应一条独立业务记录
  • 无法通过手动逐行循环、逐行拖拽的方式处理
  • 要求实现源工作表单元格内容自动嵌入目标段落模板，同时自动遍历源表全量记录生成对应内容

高版本Excel（365/2021及以上）无代码实现 #

• 操作步骤：
  1. 先确认源工作表的实际名称（比如为「StorySource」），以及模板占位符对应的数据列位置：StoryNamename对应A列，name1对应B列，name2对应C列，name3对应D列
  2. 在目标表的第一个输出行位置直接输入动态拼接公式，单格输入即可自动生成全量行结果：

     ="The "&INDEX('StorySource'!A:A,SEQUENCE(COUNTA('StorySource'!A:A)))&" of the story has three main characters: "&INDEX('StorySource'!B:B,SEQUENCE(COUNTA('StorySource'!A:A)))&", "&INDEX('StorySource'!C:C,SEQUENCE(COUNTA('StorySource'!A:A)))&", and "&INDEX('StorySource'!D:D,SEQUENCE(COUNTA('StorySource'!A:A)))
     

• 实现逻辑：
  • COUNTA('StorySource'!A:A)自动统计源表内有效数据行的总数，不需要手动计数行数
  • SEQUENCE(统计得到的行数)自动生成从1开始的连续行号序列，一一对应源表的每一条记录位置，实现全量自动遍历
  • 配合INDEX函数按生成的行号抓取对应列的单元格值，直接嵌入固定模板的文本结构中
  • 公式支持动态溢出，不需要手动拖拽填充，单格输入按回车后会自动向下扩展覆盖所有输出位置；后续源表新增数据行时，公式会自动识别有效数据范围，同步更新生成对应拼接内容

低版本Excel（2019及更早）批量实现 #

• 完全不需要逐行写公式，通过Power Query即可完成全量处理：
  1. 通过「数据」选项卡的「从表格/区域」功能，把源工作表的全量数据导入Power Query编辑器
  2. 在编辑器内添加自定义列，按照固定模板写入拼接逻辑，直接引用源表对应列的字段值，自定义列公式参考：

     "The "&[StoryNamename]&" of the story has three main characters: "&[name1]&", "&[name2]&", and "&[name3]
     

  3. 把处理完成的查询结果加载回目标工作表，后续源表更新数据时，只需要点击「刷新」按钮，就会自动遍历源表全量记录，重新生成拼接好的段落内容，全程不需要手动调整行级内容

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