☰

挑战：非易失性公式可动态引用任意工作表任意单元格

tag-icon • tag-icon microsoft-excel
挑战：非易失性公式可动态引用任意工作表任意单元格

如何根据变量工作表名称或编号以及行号和列号选择（例如通过下拉列表）动态引用任何单元格？

我知道 INDIRECT 是标准方法（详见下文），但由于 INDIRECT 是易失性的（每次工作簿发生细微变化时都会重新计算），因此它会大大降低电子表格的速度。可以使用非易失性方法来实现吗？我还知道 3D 引用在某些情况下很有用，例如，您需要对多张工作表求和或求平均值，但它不适用于单个单元格寻址。

我还将使用 INDEX(CHOOSE(...)) 提出我自己对这个问题的答案，但也许有更好的方法？

答案1

首先，以简单/常用/不稳定的方式动态寻址任意工作表中的任意单元格

假设所需的工作表名称在 A1 ="JAN" 中，行号和列号在 B1 =1 和 C1 =1 中，那么：

=INDIRECT(ADDRESS(B1, C1,,,A1))

将返回 JAN!$A$1 中的引用/值。您还可以返回整个范围，如下所示：

=SUM(INDIRECT(ADDRESS(B1, C1,,,A1)&":"&ADDRESS(9,C1)))

这将返回 SUM(JAN!$A$1:$A$9) 的值

现在，对于非挥发性方法

您可以使用以下非易失性公式对任何工作表中的任何单元格进行索引，前提是工作表列表是静态的/或可以根据需要手动更新。CHOOSE 函数可以根据提供给 CHOOSE 的索引号返回对工作簿中每个工作表的引用。您必须将 CHOOSE 与 INDEX 一起使用，因为 INDEX 无法在多个工作表上正常工作。

=INDEX(CHOOSE(*sheetnumber*,Sheet1!$A:$ZFD,Sheet2!$A:$ZFD,Sheet3!$A:$ZFD),*row number*,*column number*).

您必须手动扩展此公式以包含工作簿中的每个工作表。您在 CHOOSE 函数列表中放置它们的顺序决定了 CHOOSE 索引工作表编号顺序（不是工作簿中工作表的排序顺序）。

最好将冗长的 INDEX(CHOOSE(...)) 函数定义为命名单元格或范围，这样您就不必在多个单元格公式中键入它（如果您必须编辑它/添加新工作表，则只需编辑一次定义的名称）。您可以使用相对定义名称将工作表、行和列号的变量添加到相邻单元格中的命名范围，或者在 Office 365 中使用 lambda 函数定义名称 SHEETINDEX =LAMBDA(sheetnum, r, c, INDEX(CHOOSE(sheetnum,Sheet1!$A:$ZFD,Sheet2!$A:$ZFD,Sheet3!$A:$ZFD),r,c)。这样，您就可以像函数一样使用命名范围=SHEETINDEX(1,1,1)来返回 Sheet1!$A$1。

相关内容