（excel 2013）根据另一张表中的项目数量，动态更改公式 [英] (excel 2013) Dynamic change of formula depending on number of items in another sheet

问题描述

我有一个具体的问题要解决。我不知道该怎么做，但是想做没有宏或更糟的没有VB脚本。

我会解释我有什么，然后我想做什么。
我有（计划）有许多工作表中的工作表。第一页是一些介绍页，其中我还有一列包含每个单元格中的工作表名称。我使用公式= MID（CELL（filename，A1），FIND（]，CELL（filename，A1））+ 1,256）所以我可以更改表的名称，它也会更改名称

所以为了想象力，我有列N，我有sheet1，在sheet2等...
每张表在相同的地方有相同的结构和相同的公式，所以我有每张商品的每张表。

现在我想要拥有。我想在介绍表中添加一个公式，其中我将总结SUM（添加）或类似的东西。每张纸上的每张纸都将在同一个地方的每个价值。所以公式看起来像这样：= sheet1！C4 + sheet2！C4 + sheet3！C4 + .... +'任何工作表'！C4，但我不知道我会有多少张，有时候会有数字更改，我不想编辑公式，因为我计划有更多的这种动态公式。

我将添加另一个工作表到列N和每个公式使用这个excel循环类型将使用此动态数组来计算公式。我希望你明白我想要什么我的问题要复杂得多，但是当我把它描述为更多的时候，你会放弃我的兴趣。

感谢任何建议

编辑：但是如果只有一个使用VBA脚本的解决方案，我将不得不使用它。我不想改变公式，每次我在工作表中添加另一张表

解决方案

你所描述的是一个3D公式。你可以做一些类似

  = SUM（Sheet1：Sheet4！A1）
  / pre> 
 
 
 Excel将从Sheet1到Sheet4的所有表格中的单元格A1和这两个之间的任何表格相加。您可以插入新的工作表，而不必将其命名为SheetX。
 
 
 
为了更有效地管理3D公式，您可以使用两张照片，如在您要包括在公式中的工作表之后：插入两张名为第一和最后的表格，并确保所有要合计的纸张安排在这两张纸之间。然后在您的介绍页上使用
  = SUM（First：Last！A1）
  
您可以隐藏第一和最后表格，只有当您需要检查您的公式的表格安排时，才能取消隐藏它们。 
 
 
 
您可以插入新的表格，并确保它们位于您的书本表之间。在书籍之间拖入和移出书页以将其从3D公式中包含或排除。 
 
i have a specific problem to solve. I dont know how to do it but want to do it without macro or worse without VB script.

I will explain what i have and then what i want to do.
I have (plan to have) many sheets in worksheet. first sheet is some intro sheet, where i have also one column containing name of sheets in every cell. i am using formula "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)" so i can change name of sheet and it will change also name of sheet in intro sheet cell.

so for imagination i have column N and there i have sheet1, under that sheet2 and so on...
Each of sheets have the same construction and same formulas on the same place, so i have each sheet for every goods.

And now what i would like to have. I want to add a formula into intro sheet where i would summarize SUM (add) or something like that. It would take every value form each sheet on the same place on each sheet. so the formula would look something like this: "=sheet1!C4 + sheet2!C4 + sheet3!C4 + .... + 'any sheet'!C4" but i dont know how many sheets i will have and the number will sometimes change and i dont want to edit formula because i plan to have more this kind of dynamic formulas.

i will just add another sheet into column N and every formula using this excel type for cycle would take and recount formula using this dynamic array. i hope you understand what i want. my problem is much more complicated but when i describe it more into detials you would loose the point of my interest.

thanks for any suggestions

edit: but if there is only a solution using VBA script, i will have to use it. i dont want to change formula everytime i add another sheet into worksheet
解决方案
What you describe is a 3D formula. You can do something like 
=SUM(Sheet1:Sheet4!A1)
Excel will sum cell A1 in all sheets from Sheet1 to Sheet4 and any sheets that are between these two. You can insert new sheets and they don't have to be named SheetX.

In order to manage the 3D formulas more efficiently, you could use two sheets like bookends before and after the sheets you want to include in the formula: Insert two sheets called "First" and "Last" and ensure that all sheets you want to sum are arranged between these two sheets. Then on your intro sheet use
=SUM(First:Last!A1)
You can hide the "First" and "Last" sheets and only unhide them when you need to check your sheet arrangements for your formulas. 

You can insert new sheets and make sure that they are located between your bookend sheets. Drag sheets in and out between the bookends to include or exclude them from the 3D formula. 

                        
这篇关于（excel 2013）根据另一张表中的项目数量，动态更改公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！