如何在$C$5="Y"时仅提取Excel中非对角线的最小10个值？

现有Excel公式在$C$5="Y"时会包含对角线值并返回最小10个值，需修改公式实现：当$C$5="Y"时仅获取非对角线值，排除所有对角线单元格后取最小10个值。

原公式 #

=VSTACK({"V1","V2","V3","V4"},
    HSTACK({1;2;3;4;5;6;7;8;9;10},TAKE(SORT(--TEXTSPLIT(TEXTAFTER("|"&
    TOCOL(IFS(ISNUMBER(ABS(IF(ISREF(IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5")))), IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5")),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5"))), 0)-
    IF($C$5="N",INDIRECT("'"&$C$9&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$9&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$9&"'!B2:E5")),0,INDIRECT("'"&$C$9&"'!B2:E5"))))),ABS(IF(ISREF(IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5")))), IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5")),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),0,INDIRECT("'"&$C$10&"'!B2:E5"))), 0)-IF($C$5="N",INDIRECT("'"&$C$9&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$9&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$9&"'!B2:E5")),0,INDIRECT("'"&$C$9&"'!B2:E5"))))&"|"&INDIRECT("'"&$C$9&"'!A2:A5")&"|"&INDIRECT("'"&$C$9&"'!B1:E1")),3),"|",{1,2,3}),"|"),,1),10)))

参考数据 #

Sheet 2数据 #

Sheet 3数据 #

修改后的公式 #

=VSTACK({"V1","V2","V3","V4"},
    HSTACK({1;2;3;4;5;6;7;8;9;10},TAKE(SORT(--TEXTSPLIT(TEXTAFTER("|"&
    TOCOL(IFS(ISNUMBER(ABS(IF(ISREF(IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),NA(),INDIRECT("'"&$C$10&"'!B2:E5")))), IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5")),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),NA(),INDIRECT("'"&$C$10&"'!B2:E5"))), 0)-
    IF($C$5="N",INDIRECT("'"&$C$9&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$9&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$9&"'!B2:E5")),NA(),INDIRECT("'"&$C$9&"'!B2:E5"))))),ABS(IF(ISREF(IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),NA(),INDIRECT("'"&$C$10&"'!B2:E5")))), IF($C$5="N",INDIRECT("'"&$C$10&"'!B2:E5")),IF(ROW(INDIRECT("'"&$C$10&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$10&"'!B2:E5")),NA(),INDIRECT("'"&$C$10&"'!B2:E5"))), 0)-IF($C$5="N",INDIRECT("'"&$C$9&"'!B2:E5"),IF(ROW(INDIRECT("'"&$C$9&"'!B2:E5"))=COLUMN(INDIRECT("'"&$C$9&"'!B2:E5")),NA(),INDIRECT("'"&$C$9&"'!B2:E5"))))&"|"&INDIRECT("'"&$C$9&"'!A2:A5")&"|"&INDIRECT("'"&$C$9&"'!B1:E1")),3),"|",{1,2,3}),"|"),,1),10)))

修改说明 #

原公式中，当$C$5="Y"时，将对角线值设为0，但0会被视为有效数值参与排序，导致对角线相关的差值被选为最小值。修改方案为：

• 将所有判断对角线的IF(ROW(...)=COLUMN(...),0,INDIRECT(...))替换为IF(ROW(...)=COLUMN(...),NA(),INDIRECT(...))
• NA()会被TOCOL函数自动忽略（默认ignore_errors=True），从而彻底排除对角线单元格的数据，确保仅对非对角线的差值进行排序和取最小值操作。

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