尝试在 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
不是数字。
如果您的目标只是修复这个问题,那么将每个函数包装起来OFFSET
就VALUE
可以解决。
如果要寻找原因,我什么也找不到,尽管我只找了大约 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!问题,而不是其他问题。))