引用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 ，该怎么办?上面的公式返回循环误差.
可以在第一张工作表 B5 中输入以下公式，以汇总第二到最后的所有工作表.请注意，它是一个 array公式.(不带花括号的副本，然后使用 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函数替换为 Sheet Numbers数组.例如，仅汇总第二张和第五张纸
{= 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屋！
