☰

简化从多张工作表收集数据的公式

tag-icon • tag-icon microsoft-excel worksheet-function
简化从多张工作表收集数据的公式

我有一个电子表格，其中一年中每个周数都有一个电子表格，分别名为 1、2、...、53。

现在，我想在汇总表中汇总过去 10 周的一些数据。为此，我=ISOWEEKNUM(TODAY())在单元格 B1 中输入

=IFERROR(INDIRECT(B1&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-1)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-2)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-3)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-4)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-5)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-6)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-7)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-8)&"!B4";TRUE);0)+IFERROR(INDIRECT((B1-9)&"!B4";TRUE);0)

在不同的单元格中。

现在，您可以看出这是一个又长又笨重的公式。有没有更优雅的方法可以循环过去 10 个电子表格而无需使用 VBA？

答案1

使用以下数组公式

=SUM(N(INDIRECT("Sheet"&ROW(INDIRECT(B1&":"&(B1-9)))&"!B4")))

在单元格中输入该代码，然后在旧版 Excel 中按 CTRL+SHIFT+ENTER (CSE)。Excel 365 具有动态数组和溢出功能，因此无需使用 CSE。

解释

首先，我们创建一个表示行引用的字符串，例如，如果 B1 包含数字 29，则为“29:20”。将该字符串传递给 INDIRECT() 会将其转换为对根据所需工作表编号的行的引用：

=INDIRECT(B1&":"&(B1-9))

使用 ROW() 我们提取引用中每行的行号，该行号以数组形式返回，例如 {20,21,22,23,24,25,26,27,28,29}。接下来，我们将编号数组扩展为表示对每个工作表单元格 B4 的引用的字符串：

="Sheet"&ROW(...above formula...)&"!B4"

这将返回一个字符串数组，例如 {"Sheet20!B4","Sheet21!B4","Sheet22!B4",...,"Sheet29!B4",}，我们现在可以将其传递给 INDIRECT()。在某些版本的 Excel 中或对于某些作用于间接引用数组的函数，我们需要使用 N() 强制将引用评估为数字：

=N(INDIRECT(...above formula...))

这将返回一个数组，其中包含每个指定编号工作表中 B4 的值。最后，您可以根据需要对数字进行求和、求平均值等。

仅供参考，另一种解决方案

您可以使用这样的 3D 参考：

=SUM(Sheet1:Sheet10!B4)

但遗憾的是，您不能将 INDIRECT 与 3D 引用一起使用。但它仍然是一种快速手动汇总工作表的好方法。注意 3D 引用不关心工作表的编号，而是您在工作簿中对它们进行排序的顺序。

最后，可能可以使用命名范围内的 EVALUATE 函数（谷歌搜索“Excel 宏 4.0 EVALUATE 函数”）来评估构造为类似于“SUM(Sheet1:Sheet10!B4)”的字符串作为 Excel 公式。EVALUATE 有点像整个公式的 INDIRECT，而不仅仅是引用。由于您不能将 3D 引用与 INDIRECT 一起使用，因此您可以将整个 3D 引用公式构造为字符串并使用 EVALUATE 调用它。

相关内容