如何将多组Mass-数值列对合并为含缺失值补0的表格？

核心思路 #

先提取所有Mass列的唯一值作为统一索引，再针对每组数值列，用匹配函数填充对应Mass的数值，缺失项自动返回0。

步骤1：生成唯一Mass索引列 #

假设你的Mass列分别为A列(Mass-10)、C列(Mass-11)、E列(Mass-12)...，在空白列（比如G列）输入公式：

=UNIQUE(TOCOL(A:A,C:C,E:E,TRUE))

• TOCOL(...,TRUE)：合并指定列并忽略空单元格，生成一维数组
• UNIQUE：提取数组中的唯一值，作为最终的统一Mass索引

步骤2：填充各组数值列（补0） #

针对每组数值列（比如B列(Mass-10数值)、D列(Mass-11数值)...），在对应空白列输入匹配公式：
以Mass-10的数值列为例（H列）：

=XLOOKUP($G2,A:A,B:B,0,0)

下拉填充即可，其中：

• $G2：唯一Mass索引列的当前行值
• A:A：对应组的Mass列
• B:B：对应组的数值列
• 第一个0：匹配失败时返回0（缺失项补0）
• 最后一个0：启用精确匹配模式

批量处理优化（多组列时） #

如果Mass-数值列对数量较多，可使用LET+BYCOL批量生成结果，假设所有数据在A:F区域（3组Mass-数值对），输入公式：

=LET(
    data,A:F,
    mass_cols,INDEX(data,,SEQUENCE(1,COLUMNS(data)/2,1,2)),
    value_cols,INDEX(data,,SEQUENCE(1,COLUMNS(data)/2,2,2)),
    unique_masses,UNIQUE(TOCOL(mass_cols,TRUE)),
    filled_values,HSTACK(unique_masses,BYCOL(value_cols,LAMBDA(col,XLOOKUP(unique_masses,INDEX(data,,MATCH(col,value_cols,0)*2-1),col,0,0)))),
    filled_values
)

• 自动识别区域内的Mass列（奇数列）和数值列（偶数列）
• 一次性生成包含唯一Mass索引+所有补0后数值列的合并数组

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