时间戳

首页 > 解决方案 > 我可以创建一个间接数组吗？

excel - 我可以创建一个间接数组吗？

问题描述

我有一些每周的股票回报数据，如下所示： 在此处输入图像描述

显然，我在那里隐藏了很多行。但基本上有 200 种不同的股票和它们在 10 年内的每周回报。它们与标准普尔 500 指数同期 E 列中的回报配对。我想用这些来计算贝塔值Covariance(Stock, S&P 500)/Variance(S&P 500)。我现在正在努力创建协方差部分：

我的新工作表中的 A 列是所有股票代码的唯一列表，我的公式如下：

=COVARIANCE.P(INDIRECT("'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!G:G,0)),INDIRECT("'Weekly Data'!E$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!E$"&MATCH(A2,'Weekly Data'!G:G,0)))

收到 #REF 错误。

标签： excelexcel-formula

解决方案

你的串联

"'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!F:F,0)&":'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!G:G,0)

导致单元格范围地址的错误字符串表示。

它导致'Weekly Data'!D$2:'Weekly Data'!D$528在 ORCL 的情况下。但在Excel它必须是'Weekly Data'!D$2:D$528。注意，工作表名称只有一次。

所以正确的公式是

=COVARIANCE.P(INDIRECT("'Weekly Data'!D$"&MATCH(A2,'Weekly Data'!F:F,0)&":D$"&MATCH(A2,'Weekly Data'!G:G,0)),INDIRECT("'Weekly Data'!E$"&MATCH(A2,'Weekly Data'!F:F,0)&":E$"&MATCH(A2,'Weekly Data'!G:G,0)))

但通常INDIRECT可以替换为INDEX. 这是更好的方法，因为 的易失性行为INDIRECT会在工作表的每次更改时重新计算，而不仅仅是函数参数中单元格的更改。

INDEX公式为：

=COVARIANCE.P(INDEX('Weekly Data'!$D:$D,MATCH(A2,'Weekly Data'!$F:$F,0)):INDEX('Weekly Data'!$D:$D,MATCH(A2,'Weekly Data'!$G:$G,0)),INDEX('Weekly Data'!$E:$E,MATCH(A2,'Weekly Data'!$F:$F,0)):INDEX('Weekly Data'!$E:$E,MATCH(A2,'Weekly Data'!$G:$G,0)))

推荐阅读