如何提取数据区域中非空条目对应的内容、列标题与行标题

核心需求 #

从带有行列标题的数据区域中，提取三列内容：

• 非空数据内容（排除公式返回的空值""）
• 对应列标题
• 对应行标题

解决方案（假设数据区域为B2:E10，列标题B1:E1，行标题A2:A10） #

1. 提取非空数据（G2单元格输入）

=FILTER(B2:E10, B2:E10<>"")

注：若出现#SPILL错误，检查输出列（如G列）下方是否有非空单元格占用溢出区域，或确认数据区域引用范围正确。

2. 提取对应列标题（H2单元格输入）

=LET(
    data, B2:E10,
    col_headers, B1:E1,
    non_empty, FILTER(data, data<>""),
    col_pos, XLOOKUP(non_empty, data, COLUMN(data)-COLUMN(col_headers)+1),
    INDEX(col_headers, col_pos)
)

3. 提取对应行标题（I2单元格输入）

=LET(
    data, B2:E10,
    row_headers, A2:A10,
    non_empty, FILTER(data, data<>""),
    row_pos, XLOOKUP(non_empty, data, ROW(data)-ROW(row_headers)+1),
    INDEX(row_headers, row_pos)
)

问题排查说明 #

• 之前用CONCAT(REPT)获取列标题的方法可替换为上述INDEX+XLOOKUP方案，更适配动态数据场景。
• INDEX MATCH未成功的原因通常是二维数据区域中直接匹配会返回首个匹配项，需结合行列号定位，上述LET封装的公式已解决该问题。

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