Problem Statement:
I'm creating a template for multi tiered complicated calculations in MS Excel that depend on a few input "n x 3" matrices.
我正在为MS Excel中的多层复杂计算创建一个模板,该模板依赖于一些输入“n x 3”矩阵。
It is really difficult to redesign the 15 sheets or so (200 ~ 300 lines each) every time I have a different "n" where "n" ranges from 3 to 900.
每次我有一个不同的“n”时,很难重新设计15张左右(每行200~300行),其中“n”的范围是3到900。
Goals:
- I'd like to input the number of "n" in a cell subsquently changing all the other sheets in the workbook accordingly.
- Avoid VBA as much as possible
我想在一个单元格中输入“n”的数量,从而相应地改变工作簿中的所有其他工作表。
尽可能避免使用VBA
How can I achieve these goals?
我怎样才能实现这些目标?
Note: I'm willing to answer any questions or comments concerning my issue
注意:我愿意回答有关我的问题的任何问题或意见
EDIT "n" represents the number of columns / rows, if n = 3, all calculations will be for a 3 x 3 matrix. If n = 500, all calculations will be for a 500 x 3 matrix. I want Excel to do the expansions / contractions of the rows automatically, so i do't have to do them myself accross hundreds of tables
EDIT“n”表示列数/行数,如果n = 3,则所有计算都将用于3 x 3矩阵。如果n = 500,则所有计算都将针对500 x 3矩阵。我希望Excel自动进行行的扩展/收缩,所以我不必自己做几百个表
3 个解决方案
#1
In Excel 2007 turn your data matrices into tables.
在Excel 2007中,将数据矩阵转换为表格。
This can be done by clicking on a matrix and then on the Insert tab select Table. The keyboard shortcut for this functionality is Ctrl-L or Ctrl-T. Multiple tables can exist on the same worksheet.
这可以通过单击矩阵然后在“插入”选项卡上选择“表”来完成。此功能的键盘快捷键是Ctrl-L或Ctrl-T。多个表可以存在于同一工作表中。
Once your data is marked as a table, the table will dynamically expand when new data is added.
将数据标记为表格后,表格将在添加新数据时动态扩展。
Each table is automatically given a name, starting with Table1. The name can be change via the Table tools - Design tab.
从表1开始,每个表都自动赋予一个名称。可以通过表格工具 - 设计选项卡更改名称。
In formulas each table can be referenced by it's name.
在公式中,每个表都可以通过它的名称引用。
=SUM(Table1)
Each column heading in the table is also usable in formulas.
表中的每个列标题也可用于公式。
=SUM(Table1[Column1])
In versions of Excel prior to 2007, 'Dynamic named ranges' can be used.
在2007年之前的Excel版本中,可以使用“动态命名范围”。
These can be created via the Insert - Name - Define menu.
这些可以通过“插入 - 名称 - 定义”菜单创建。
Give the 'Dynamic named range' a name (e.g. Table1) and enter a formula similar to the following assuming your matirx starts in cell A1:
给“动态命名范围”命名(例如表1)并输入类似于以下的公式,假设您的matirx在单元格A1中开始:
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!A:A)-1,3)
If your matrix starts in cell D10 the formula would look like this
如果矩阵在单元格D10中开始,则公式将如下所示
=OFFSET(Sheet1!$D$10,1,0,COUNTA(Sheet1!D:D)-1,3)
This formula excludes any column heading in the matrix. It selects data on a 'n x 3' basis.
此公式排除矩阵中的任何列标题。它以“n×3”为基础选择数据。
In formulas each 'Dynamic named range' can be referenced by it's name.
在公式中,每个“动态命名范围”都可以通过它的名称引用。
=SUM(Table1)
You will need to review the layout of your worksheet as the dynamic named range works out it's number of rows by counting all items that appear in the first column of data.
If you have cells populated above and/or below your matrix they will be included in the calculation and the 'Dynamic named range' will include rows below your data matrix.
您需要检查工作表的布局,因为动态命名范围通过计算出现在第一列数据中的所有项来计算出行数。如果在矩阵的上方和/或下方填充了单元格,则它们将包含在计算中,“动态命名范围”将包括数据矩阵下方的行。
To see which cells are included in a 'Dynamic named range' or table press F5 and type in it's name, then click on OK.
要查看“动态命名范围”或表中包含哪些单元格,请按F5键入其名称,然后单击“确定”。
#2
Create defined names (Insert - Name - Define) that use OFFSET and COUNTA to make dynamic ranges. Instead of
创建使用OFFSET和COUNTA创建动态范围的已定义名称(插入 - 名称 - 定义)。代替
=SUM(A1:A300)
use
=SUM(MyRange)
where MyRange is
MyRange在哪里
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
#3
Instead of calculating against A1:A300, you can calculate against A:A, which is the entire column.
而不是计算A1:A300,你可以计算A:A,这是整个列。
You may have to bear in mind that you don't want other stuff in that column when you design your layout.
您可能必须记住,在设计布局时,您不需要该列中的其他内容。
In more recent version of Excel, you can select sets of data and format them as a table, in which case you can use the table column, for example Table1[Column2].
在更新版本的Excel中,您可以选择数据集并将其格式化为表格,在这种情况下,您可以使用表格列,例如Table1 [Column2]。
#1
In Excel 2007 turn your data matrices into tables.
在Excel 2007中,将数据矩阵转换为表格。
This can be done by clicking on a matrix and then on the Insert tab select Table. The keyboard shortcut for this functionality is Ctrl-L or Ctrl-T. Multiple tables can exist on the same worksheet.
这可以通过单击矩阵然后在“插入”选项卡上选择“表”来完成。此功能的键盘快捷键是Ctrl-L或Ctrl-T。多个表可以存在于同一工作表中。
Once your data is marked as a table, the table will dynamically expand when new data is added.
将数据标记为表格后,表格将在添加新数据时动态扩展。
Each table is automatically given a name, starting with Table1. The name can be change via the Table tools - Design tab.
从表1开始,每个表都自动赋予一个名称。可以通过表格工具 - 设计选项卡更改名称。
In formulas each table can be referenced by it's name.
在公式中,每个表都可以通过它的名称引用。
=SUM(Table1)
Each column heading in the table is also usable in formulas.
表中的每个列标题也可用于公式。
=SUM(Table1[Column1])
In versions of Excel prior to 2007, 'Dynamic named ranges' can be used.
在2007年之前的Excel版本中,可以使用“动态命名范围”。
These can be created via the Insert - Name - Define menu.
这些可以通过“插入 - 名称 - 定义”菜单创建。
Give the 'Dynamic named range' a name (e.g. Table1) and enter a formula similar to the following assuming your matirx starts in cell A1:
给“动态命名范围”命名(例如表1)并输入类似于以下的公式,假设您的matirx在单元格A1中开始:
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!A:A)-1,3)
If your matrix starts in cell D10 the formula would look like this
如果矩阵在单元格D10中开始,则公式将如下所示
=OFFSET(Sheet1!$D$10,1,0,COUNTA(Sheet1!D:D)-1,3)
This formula excludes any column heading in the matrix. It selects data on a 'n x 3' basis.
此公式排除矩阵中的任何列标题。它以“n×3”为基础选择数据。
In formulas each 'Dynamic named range' can be referenced by it's name.
在公式中,每个“动态命名范围”都可以通过它的名称引用。
=SUM(Table1)
You will need to review the layout of your worksheet as the dynamic named range works out it's number of rows by counting all items that appear in the first column of data.
If you have cells populated above and/or below your matrix they will be included in the calculation and the 'Dynamic named range' will include rows below your data matrix.
您需要检查工作表的布局,因为动态命名范围通过计算出现在第一列数据中的所有项来计算出行数。如果在矩阵的上方和/或下方填充了单元格,则它们将包含在计算中,“动态命名范围”将包括数据矩阵下方的行。
To see which cells are included in a 'Dynamic named range' or table press F5 and type in it's name, then click on OK.
要查看“动态命名范围”或表中包含哪些单元格,请按F5键入其名称,然后单击“确定”。
#2
Create defined names (Insert - Name - Define) that use OFFSET and COUNTA to make dynamic ranges. Instead of
创建使用OFFSET和COUNTA创建动态范围的已定义名称(插入 - 名称 - 定义)。代替
=SUM(A1:A300)
use
=SUM(MyRange)
where MyRange is
MyRange在哪里
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
#3
Instead of calculating against A1:A300, you can calculate against A:A, which is the entire column.
而不是计算A1:A300,你可以计算A:A,这是整个列。
You may have to bear in mind that you don't want other stuff in that column when you design your layout.
您可能必须记住,在设计布局时,您不需要该列中的其他内容。
In more recent version of Excel, you can select sets of data and format them as a table, in which case you can use the table column, for example Table1[Column2].
在更新版本的Excel中,您可以选择数据集并将其格式化为表格,在这种情况下,您可以使用表格列,例如Table1 [Column2]。