Excel 中无法解释的 #NUM 错误

Excel 中无法解释的 #NUM 错误

尝试在 Excel 中创建相关矩阵,我制作了以下公式:

=CORREL(OFFSET(A2:A1001,0,ROW()-ROW(G2)),OFFSET(A2:A1001,0,COLUMN()-COLUMN(G2)))

ROW()-ROW(G2)COLUMN()-COLUMN(G2)在单元格 G2 处求值时分别产生 0 和 0 的输出,并且CORREL(OFFSET(A2:A1001,0,0),OFFSET(A2:A1001,0,0))产生 1 的相关性,但在偏移公式中使用ROW()-ROW(G2)COLUMN()-COLUMN(G2)代替 0 和 0 会产生 #NUM 错误。这些表达式最终是否没有将 0 传递给偏移函数的列偏移参数?如果是,那么为什么会返回 #NUM 错误?

答案1

这似乎CORREL认为函数的内部最终结果OFFSET不是数字。

如果您的目标只是修复这个问题,那么将每个函数包装起来OFFSETVALUE可以解决。

如果要寻找原因,我什么也找不到,尽管我只找了大约 10 分钟。在内部,评估似乎会产生数组常量(因此{2}最终以作为列参数 vs.ROW(2)结束。但是,Excel 完全熟悉其自己的数组常量(!)并且形式是数字,而不是非数字(vs. ),因此应该不难区分数字和非数字。{0}0{0}{"0"}

进一步考虑数组常量,将文字版本放入公式中而不是计算中({0}而不是简单地0将计算放入公式中)会产生 #VALUE! 错误。这虽然不是明确的,但相当清楚的是,问题不在于 Excel 在内部创建数组常量来评估公式中的步骤。使用F9每个步骤手动跟踪流程也是如此,因为当这样做以 #DIV/0! 错误结尾时,它会很好地工作。

因此,可以肯定的是,Excel 通常识别为 0 的某个值确实在内部移动,并在整个过程中的每个阶段都恰当地用作数字,但最终,它还不足以“使用” CORREL。但是,它足以让人VALUE满意CORREL

对内部运作的有趣一瞥。没有启发性,但很有趣。

(这并不完全正确:有启发意义的是,其他函数可能会因为没有明显的原因而失败,记住这一点,并且在VALUE呈现给不满意的函数之前包装最终(或更早,可能发生)的中间结果可能会解决问题。(只是#NUM!问题,而不是其他问题。))

相关内容