如何在无VBA的单行公式中获取二维矩阵非零值的行列坐标？

错误原因分析 #

你合并公式时出现报错，核心问题有三点：

1. 引用不一致：合并公式中一处用了$L$5，另一处用了L5，导致计算出的数组维度不匹配
2. 行号引用错误：ROW(ABS(...))返回的是工作表的绝对行号，而非原动态数组的相对行号序列，和原公式中ROW(Sheet2!N5#)的逻辑不符
3. 重复代码冗余：大量重复的数组计算导致公式嵌套过深，极易出现括号不匹配或维度冲突

修正后的合并公式（兼容向下复制，非动态友好） #

方案1：用LET简化计算（推荐，减少重复代码） #

LET函数可以将重复计算封装为变量，避免冗余，同时保证公式清晰，支持向下复制：

=LET(
    pad_calc, CHOOSECOLS(FILTER(BDCpadgauges[#All], BDCpadgauges[detail]=Sheet1!$E$5, "no found"), 2)*2*PI(),
    target_val, Sheet1!L5 - pad_calc,
    diff_data, TOROW(Table4[OC '[mm']]) - TOCOL(target_val),
    abs_matrix, ABS(WRAPROWS(TOCOL(diff_data), COUNTA(Table4[MOLD]))),
    min_abs, MIN(abs_matrix),
    match_row, SUMPRODUCT((abs_matrix=min_abs)*SEQUENCE(ROWS(abs_matrix))),
    INDEX(Table4[MOLD], match_row)
)

公式说明：

• pad_calc：一次性计算BDCpadgauges对应detail的圆周值，$E$5用绝对引用保证向下复制时不偏移
• target_val：计算目标差值的基准值
• abs_matrix：生成和原Sheet2!N5#完全一致的绝对值矩阵
• SEQUENCE(ROWS(abs_matrix))：模拟原ROW(Sheet2!N5#)的相对行号序列
• match_row：通过SUMPRODUCT匹配最小值所在的行号，最终用INDEX返回对应MOLD值

方案2：纯重复计算版本（兼容旧版Excel，无动态函数依赖） #

如果需要完全不依赖动态数组函数，可使用以下公式，统一所有引用并修正行号逻辑：

=INDEX(Table4[MOLD],SUMPRODUCT((ABS(WRAPROWS(TOCOL(TOROW(Table4[OC '[mm']])-TOCOL(Sheet1!L5-(CHOOSECOLS(FILTER(BDCpadgauges[#All],BDCpadgauges[detail]=Sheet1!$E$5,"no found"),2)*2*PI()))),COUNTA(Table4[MOLD])))=MIN(ABS(WRAPROWS(TOCOL(TOROW(Table4[OC '[mm']])-TOCOL(Sheet1!L5-(CHOOSECOLS(FILTER(BDCpadgauges[#All],BDCpadgauges[detail]=Sheet1!$E$5,"no found"),2)*2*PI()))),COUNTA(Table4[MOLD]))))*ROW(INDIRECT("1:"&COUNTA(Table4[MOLD]))))

关键修正：

• 统一所有引用为Sheet1!L5和Sheet1!$E$5，避免维度不匹配
• 用ROW(INDIRECT("1:"&COUNTA(Table4[MOLD])))生成1到MOLD行数的连续序列，替代原ROW(Sheet2!N5#)的行号逻辑
• 移除了原公式末尾多余的-ROW(N5)+1，因为现在使用的是数组的相对行号

非动态公式获取非零值的行/列 #

获取非零值所在行（对应Table4的行号） #

=SUMPRODUCT((Table4[OC '[mm']]<>0)*ROW(Table4[OC '[mm']]))-ROW(Table4[#Headers])+1

获取非零值所在列（对应Table4的列标题） #

=INDEX(Table4[#Headers],SUMPRODUCT((Table4<>0)*COLUMN(Table4))-COLUMN(Table4[#Headers])+1)

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