首页 文章

需要在一系列单元格中复制的动态范围内运行公式

提问于
浏览 次
-1

我正在创建一个相关值网格，如距离网格 . 我有一系列的单元格，每个单元格都包含一个公式，如果你知道第一个单元格的偏移量，那么它的范围很容易描述，而且我无法弄清楚如何指定它 .

  • 在左上角的单元格（R10）中，公式为 CORREL(C2:C21,C2:C21) - 当然是1 .

  • 在（S10）的下一栏中，公式为 CORREL(D2:D21,C2:C21) .

  • 在下一行（R11）中，公式为 CORREL(C2:C21,D2:D21) .

  • 当然，S11将包含 CORREL(D2:D21,D2:D21) ，也是1.依此类推，大约15x15网格 .

以下是所涉及范围的图形表示：

C2:C21,C2:C21  C2:C21,D2:D21  C2:C21,E2:E21
D2:D21,C2:C21  D2:D21,D2:D21  D2:D21,E2:E21
E2:E21,C2:C21  E2:E21,D2:D21  E2:E21,E2:E21

每当我添加新数据行时，我都必须手动更新多个公式 . 所以，我想要动态确定最后一个非空白列号（在本例中为21），例如使用 COUNTA(C:C) . 理想情况下，我也希望计算行偏移的公式，以便我可以在整个范围内拖动一个公式 .

实现这一目标的最佳方法是什么？我认为OFFSET可能是解决方案中的一个组件，但我没有成功地将它们全部合作 .

excel excel-formula excel-indirect

2 回答

  • 0

    使用corr矩阵的每个元素的这个简单设置也有助于：

    = CORREL（间接（“'风险因素'！”和“T”和G6＆“：T”和H6）;间接（“'风险因素'！”和“U”＆G6＆“：U”和H6））

    使用此功能，我可以参考另一个表格中的数据，风险因素，将行T和U相互关联 . 我希望数据的范围是动态的，所以我将当前表中的G6和H6引用到列的长度（行数），我当然在这些G6和H6单元格中指定了这些列 .

    希望这可以帮助！

    回复于
  • 1

    我发现这个公式虽然罗嗦，却达到了预期的效果 . 在此示例中，数据位于C2：O19中 . 我想要构建的表计算了列对的所有排列的相关值 . 由于有11列，因此相关对表为11x11，从R10开始 . 每个单元格具有以下公式：

    =CORREL(INDIRECT(ADDRESS(2,2+(ROWS($R$10:R10)),4)&":"&ADDRESS(COUNTA($C:$C),
2+(ROWS($R$10:R10)),4)),INDIRECT(ADDRESS(2,2+(COLUMNS($R$10:R10)),4)&":"&
ADDRESS(COUNTA($C:$C),2+(COLUMNS($R$10:R10)),4)))

    正如我发现的那样， INDIRECT() 解析了一个单元格引用并获得了它的值 .

    让我们拿一个单元格，比如U12，详细看一下范围公式 . 第一个 INDIRECT 是通过应用R10的行偏移给出的列 .

    由于第12行是第10行的2行， ADDRESS(2,2+(ROWS($R$10:U12)),4)&":"&ADDRESS(COUNTA($C:$C),2+(ROWS($R$10:U12)),4) 应该产生第C行右边2行的列，即E . 公式计算结果为 E2:E19 .

    第二个 INDIRECT 是通过应用R10的列偏移量给出的列 . 类似地，由于列U是列R右边的3列， ADDRESS(2,2+(COLUMNS($R$10:U12)),4)&":"&ADDRESS(COUNTA($C:$C),2+(COLUMNS($R$10:U12)),4) 应该产生行C右边3行的列，即F.第二个公式的计算结果为 F2:F19 .

    将这些范围参考值代入，单元格公式减少到 =CORREL(INDIRECT("E2:E19"),INDIRECT("F2:F19")) ，进一步减少到 =CORREL(E2:E19,F2:F19) ，这是我到目前为止所用的 .

    就像距离表一样，这个表沿对角线是对称的，因为 =CORREL(E2:E19,F2:F19) 等于 =CORREL(F2:F19,E2:E19) . 对角线上的每个值都是1，因为根据定义，相同范围的 CORREL 是100％相关 .

    回复于

相关问题