Excel数组场景下IFERROR仅返回1的问题排查与解决咨询

我来帮你拆解并解决这个Excel数组公式的异常问题，咱们一步步理清楚核心矛盾和可行方案：

核心问题根源 #

你的核心困扰是数组公式里的IFERROR没有按数组逻辑返回结果，只输出单个1，同时还存在F9分步计算和「公式求值」结果不一致、INDEX/OFFSET作为数组公式无法返回数组的情况，本质原因是旧版Excel（非365/2021动态数组版本）的CSE数组公式限制：

1. IFERROR的单值特性：旧版Excel中IFERROR是单值函数，哪怕用Ctrl+Shift+Enter（CSE）执行，它也只会处理数组的第一个元素，返回单个结果，不会遍历整个数组替换错误值。
2. INDIRECT的数组兼容性差：你用INDIRECT+ADDRESS拼接的范围引用，在数组运算中容易出现维度不匹配，导致「公式求值」工具显示全错；而F9只是临时计算片段，会忽略数组上下文限制，所以结果不一致。
3. INDEX/OFFSET的CSE数组限制：旧版Excel的CSE模式下，INDEX要返回数组需要严格匹配连续区域+数组参数的格式，OFFSET也无法直接返回多单元格数组，这就是你的测试公式失效的原因。

针对性解决方案 #

方案1：用IF+ISERROR替代IFERROR（兼容CSE数组） #

把原公式的IFERROR替换为IF(ISERROR(...),1,...)，因为IF和ISERROR可以在CSE数组公式中逐元素处理：

=IF(ISERROR(INDIRECT(ADDRESS((IF(Array>0;1;-1)*COLUMN(Array)-31)*51+$CJ3+1;COLUMN(CP3))));1;INDIRECT(ADDRESS((IF(Array>0;1;-1)*COLUMN(Array)-31)*51+$CJ3+1;COLUMN(CP3)))

输入后按Ctrl+Shift+Enter执行，就能逐元素捕获错误并替换为1，得到你期望的数组结果。

方案2：优化Array命名公式的引用方式 #

你的Array用INDIRECT+ADDRESS拼接范围的写法兼容性很差，建议改用INDEX直接定位连续范围，避免字符串拼接：

=INDEX(List!$A$1:$ZZ$1000;'Tab1'!$BT3;31):INDEX(List!$A$1:$ZZ$1000;'Tab1'!$BT3;43)

（注意：这里假设List工作表的最大数据范围是$A$1:$ZZ$1000，你可以根据实际数据范围调整）
这种写法比INDIRECT更稳定，能减少数组运算中的#VALUE!错误。

方案3：升级到Excel 365/2021（彻底解决） #

如果条件允许，升级到支持动态数组的Excel版本，IFERROR、INDEX、OFFSET都会自动支持数组输入，不需要按Ctrl+Shift+Enter，直接输入公式就能返回数组结果，从根源上解决旧版CSE数组的兼容性问题。

额外测试验证 #

你提到的=SUM(1*OFFSET($A$1;{1};{1}))失败，同样是旧版CSE的限制，换成INDEX的数组写法就能正常运行：

=SUM(1*INDEX($A:$A;{2};{2}))

按Ctrl+Shift+Enter执行即可。

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