求助：数组公式中忽略空白或#N/A单元格的实现方法

旧版Excel（需数组输入） #

如果你的Excel版本不支持动态数组（如Excel 2019及更早），可使用以下数组公式（输入后需按Ctrl+Alt+Shift三键确认，不能直接回车）：

=INDEX(A1:A6,MATCH(MIN(IF((NOT(ISNA(C1:C6)))*(NOT(ISNA(D1:D6)))*(NOT(ISBLANK(C1:C6)))*(NOT(ISBLANK(D1:D6))),ABS(C1:C6-D1:D6),9^99)),IF((NOT(ISNA(C1:C6)))*(NOT(ISNA(D1:D6)))*(NOT(ISBLANK(C1:C6)))*(NOT(ISBLANK(D1:D6))),ABS(C1:C6-D1:D6),9^99),0))

公式说明： #

• (NOT(ISNA(C1:C6)))*(NOT(ISNA(D1:D6)))*(NOT(ISBLANK(C1:C6)))*(NOT(ISBLANK(D1:D6)))：通过逻辑与（*）筛选出C、D列既不为#N/A也不为空白的行，符合条件的位置返回1，否则返回0。
• IF(...,ABS(C1:C6-D1:D6),9^99)：仅对符合条件的行计算两列差值的绝对值，不符合条件的行返回极大值9^99（确保不会被MIN选中）。
• MIN(...)：从有效差值中找出最小值。
• MATCH(...)：在处理后的差值数组中定位最小值的首次出现位置。
• INDEX(A1:A6,...)：根据位置返回A列对应的内容。

新版Excel（支持动态数组，如365/2021） #

如果使用支持动态数组的Excel版本，推荐用更简洁的公式，无需手动数组输入：

方案1：XLOOKUP直接实现 #

=XLOOKUP(MIN(IF((NOT(ISNA(C1:C6)))*(NOT(ISNA(D1:D6)))*(NOT(ISBLANK(C1:C6)))*(NOT(ISBLANK(D1:D6))),ABS(C1:C6-D1:D6),9^99)),IF((NOT(ISNA(C1:C6)))*(NOT(ISNA(D1:D6)))*(NOT(ISBLANK(C1:C6)))*(NOT(ISBLANK(D1:D6))),ABS(C1:C6-D1:D6),9^99),A1:A6)

方案2：用LET函数提升可读性 #

通过LET定义中间变量，让公式逻辑更清晰：

=LET(
    有效行数据, FILTER(A1:A6, (NOT(ISNA(C1:C6)))*(NOT(ISNA(D1:D6)))*(NOT(ISBLANK(C1:C6)))*(NOT(ISBLANK(D1:D6))), ""),
    有效差值, FILTER(ABS(C1:C6-D1:D6), (NOT(ISNA(C1:C6)))*(NOT(ISNA(D1:D6)))*(NOT(ISBLANK(C1:C6)))*(NOT(ISBLANK(D1:D6))), ""),
    XLOOKUP(MIN(有效差值), 有效差值, 有效行数据)
)

注意事项： #

• 如果存在多个行的差值同为最小值，公式会返回第一个出现的对应A列内容。
• 9^99可替换为其他足够大的数值，只要不会与实际业务中的差值冲突即可。

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