excel - 引用 Excel 函数中的最后一个工作表以在多个工作表中求和相同的范围

Question

我已经尝试了一些公式选项，但没有什么能像我想要的那样工作。

我正在使用一个非常简单的 SUM 公式来汇总我工作簿中的所有工作表。目前它是这样设置的:
=SUM('1:10'!D3)
但是，我可能会添加更多工作表，并且希望将它们包含在总数中。我已经尝试了几个不同版本的“wshNameLast”或“getlastwsname”，我在这里的不同文章中找到了它们，但它们都给了我一个#REF 错误。有没有我可以在不求助于 VBA 的情况下在这里输入的公式？

谢谢你的帮助!

Answer 1

有趣的问题。

@BigBen 对问题的评论>>>“最后有一个隐藏的空白工作表，只需引用它”>>> 非常实用且最简单的解决方案。

但只是为了让它充满活力

引用.. Jeffrey Weir's answer
和
Return array with Index Function

正如@Michal Rosa 在评论这个问题时所建议的那样
How to generate list of Sheet Names

正如@ashleedawg 在评论这个问题时所建议的那样
using indirect function to sum across multiple sheets

创建了包含 5 个工作表的工作簿。

第一张是 Sheet5依此类推最后是Sheet1 .

除了第一个工作表之外，每个工作表都具有 B5 中的值.

首先为 SheetNames 定义名称数组 =GET.WORKBOOK(1)


然后很容易将 B5 中的值相加使用此公式的所有表格(包括第一张)
=SUMPRODUCT(SUM(INDIRECT("'"&SheetNames&"'!B5")))
但是，如果必须在 Sheet5 的第一张表 (B5) 中输入公式怎么办？总结所有剩余的表格'B5 .上面的公式返回循环误差。

下面的公式可以在第一张表中输入B5将所有工作表从第二个到最后一个相加。请注意，它是 array formula . (不带大括号的复制并输入 ctrl+shift+enter )
{=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(SheetNames,N(IF((1),ROW(INDIRECT(2&":"&SHEETS())))))&"'!B5")))}INDIRECT(2&":"&SHEETS())以第二张表开始数组并以最后一张表结束(使用 Sheets() 函数计算的数字)

因此，如果想从第 3 表开始数组/求和，请使用 INDIRECT(3&":"&SHEETS())与 Ctrl+Shift+Enter
一个可以代替ROW function与 array of Sheet Numbers .例如，仅对第二张和第五张表求和
{=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(SheetNames,N(IF((1),{2,5})))&"'!B5")))}
另外，&"'!B5"可以替换为 &"'!"&ADDRESS(ROW(),COLUMN())在多个工作表中求和当前公式单元格地址。