引用 Excel 函数中的最后一个工作表以对多个工作表中的相同范围求和 [英] Reference to last worksheet in Excel Function to Sum same range across multiple sheets
问题描述
我已经尝试了一些公式选项，但没有一个能像我希望的那样工作.
我正在使用一个非常简单的 SUM 公式来计算我工作簿中所有工作表的总和.目前它是这样设置的:
=SUM('1:10'!D3)
但是，我可能会添加更多工作表，并且希望将它们包含在此总数中.我尝试了几个不同版本的wshNameLast"或getlastwsname"，我在不同的文章中找到了这些，但它们都给我一个#REF 错误.有没有我可以在不使用 VBA 的情况下输入的公式?
感谢您的帮助！
有趣的问题.
@BigBen 对问题的评论 >>>最后有一个隐藏的空白工作表，只需引用它">>> 非常实用，也是最简单的解决方案.
但只是为了让它动态
参考..
然后很容易用这个公式对所有表格(包括第一个)的B5中的值求和
=SUMPRODUCT(SUM(INDIRECT("'"&SheetNames&"'!B5")))
但是，如果必须在 B5 的第一个工作表 (Sheet5) 中输入一个公式来总结所有剩余工作表的 B5 呢?上面的公式返回循环错误.
可以在第一个Sheet B5 中输入下面的公式，以对从Second 到last 的所有sheet 求和.请注意，它是一个数组公式.(不带大括号复制并使用 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 函数 替换为工作表编号数组.例如，只求和第二张和第五张
{=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(SheetNames,N(IF((1),{2,5})))&"'!B5")))}
此外，&"'!B5" 可以替换为 &"'!"&ADDRESS(ROW(),COLUMN()) 求和跨多个工作表的当前公式单元格地址.
I've tried a few formula options but nothing is quite working the way I would like it to.
I'm using a very simple SUM formula to total across all the worksheets in my workbook. Currently it's set up like this:
=SUM('1:10'!D3)
However, I'm likely going to add more worksheets and I want them included in this total. I've tried a few different versions of "wshNameLast" or "getlastwsname" that I've found in different articles here but they are all giving me a #REF error. Is there a formula I'm able to put in here without turning to VBA?
Thanks for your help!
Interesting question.
@BigBen's comment to the question >>> "Have a hidden blank worksheet at the end and just reference that" >>> is really practical and the easiest solution.
But just to make it dynamic
Referred to .. Jeffrey Weir's answer and Return array with Index Function
and as suggested by @Michal Rosa in comment on this question How to generate list of Sheet Names
and as suggested by @ashleedawg in comment on this question using indirect function to sum across multiple sheets
Created workbook with 5 worksheets.
First Sheet is Sheet5 and so on the last is Sheet1.
Each Sheet except the first has value in B5.
First defined the name for SheetNames array =GET.WORKBOOK(1)
Then it was easy to sum Values in B5 of all the the Sheets(Including the first) with this formula
=SUMPRODUCT(SUM(INDIRECT("'"&SheetNames&"'!B5")))
But what if one has to enter a formula in the First Sheet(Sheet5) in B5 to sum all remaining Sheets' B5. The formula above returns circulating error.
The formula below can be entered in the first Sheet B5 to Sum all the sheets from Second to last. Note that, its an array formula. (Copy without curly bracket and enter with ctrl+shift+enter)
{=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(SheetNames,N(IF((1),ROW(INDIRECT(2&":"&SHEETS())))))&"'!B5")))}
INDIRECT(2&":"&SHEETS()) starts the array with Second Sheet and Ends with last sheet (number calculated with Sheets() function)
So, if one wants to start array/ sum from Sheet number 3 use INDIRECT(3&":"&SHEETS()) with Ctrl+Shift+Enter
One can replace ROW function with an array of Sheet Numbers. For example, to sum only Second and Fifth Sheets
{=SUMPRODUCT(SUM(INDIRECT("'"&INDEX(SheetNames,N(IF((1),{2,5})))&"'!B5")))}
Also, &"'!B5" can be replaced with &"'!"&ADDRESS(ROW(),COLUMN()) to sum current formula cell address across multiple sheets.
这篇关于引用 Excel 函数中的最后一个工作表以对多个工作表中的相同范围求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
