如何在Excel/Sheets中用单函数合并多组带对应名称的引用区域？

问题描述 #

现有两组一一对应的数据：一组是指向其他工作表区域的字符串引用（引用区域固定4列，行数可变），另一组是对应名称。需要将这些数据合并为一个完整区域，每个引用区域的右侧自动添加对应的名称，优先用单个单元格函数实现自动溢出效果，不使用Google Apps Script/VBA脚本。

场景示例 #

主工作表（MainPage） #

工作表FirstPage #

工作表SecondPage #

期望输出 #

已尝试的方法 #

1. 单个区域处理公式（可正常运行，但无法合并多区域）

lambda(id, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))(1)

2. 尝试用VSTACK批量处理（报错：“结果应位于单行中”）

VSTACK(makearray(counta(MainSheet!A2:A999), 1, lambda(id, _, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))))

3. 尝试用REDUCE合并（报错：“引用错误”，无法将数组作为初始值）

reduce({},makearray(counta(MainSheet!A2:A999), 1, lambda(id, _, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))), LAMBDA(array, array2, array&array2))

解决方案 #

使用REDUCE结合VSTACK、HSTACK和INDIRECT的组合公式，可实现单个单元格自动溢出效果：

=REDUCE("", MainPage!A2:B, LAMBDA(acc, curr, VSTACK(acc, HSTACK(INDIRECT(INDEX(curr,1)), INDEX(curr,2)))))

公式说明 #

• MainPage!A2:B：指定主表中包含引用区域和对应名称的数据源范围，会自动识别非空行
• REDUCE("", ...)：以空字符串为初始累积值，遍历每一行数据
• HSTACK(INDIRECT(INDEX(curr,1)), INDEX(curr,2))：对当前行，先将引用字符串转为实际单元格区域，再和对应名称横向合并
• VSTACK(acc, ...)：将当前合并后的区域与之前累积的结果纵向堆叠，最终输出完整的合并表格

该公式支持自动溢出，当主表中的引用或名称更新时，结果会自动同步更新。

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