如何用数组公式实现表格错位散乱数据的自动规整 无需脚本

需求可以实现，不需要安装脚本，通过BYROW+LET+XLOOKUP的组合即可实现单格输入、整列自动生效的效果。

核心优化思路 #

你原公式的核心逻辑是先匹配到对应原始数据行，再在该行内匹配对应列的内容，问题出在INDIRECT不支持数组运算，我们直接用行迭代函数逐行处理即可避开这个限制。

可用数组公式 #

将以下公式输入到Clean Data工作表的B2单元格（如果需要适配表头可以自行调整行号参数），即可实现整列自动计算：

=ARRAYFORMULA(
  BYROW($A2:$A,LAMBDA(cur_id,
    IF(cur_id="",,
      LET(
        # 匹配当前标识对应的原始数据行号
        target_row,XMATCH("*"&cur_id&"*",'Raw Data'!$A:$A,2),
        # 提取原始数据对应行的所有内容
        target_row_data,INDEX('Raw Data'!$A:$E,target_row,),
        # 在该行内匹配包含当前列表头的内容
        XLOOKUP("*"&B$1&"*",target_row_data,target_row_data,"未匹配",2)
      )
    )
  ))
)

补充说明 #

• 如果需要一次性生成所有列的规整数据，只需要把公式里的列引用范围调整为对应表头区域，用MAKEARRAY即可实现整个表格的自动生成，不用每列单独输入公式
• 公式里的未匹配是找不到对应内容时的默认返回值，你可以根据需要修改为空白""或者其他提示内容

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