Excel命名范围以增加标题和子标题 [英] Excel named ranges to make incrementing headers and sub headers

问题描述

我正在创建一个电子表格应用程序，它将要求用户填写输入表的不同部分。要将这些部分分成逻辑手段，我使用的头文件和子标题如下所示：

I am creating a spreadsheet application which will require a user to fill in different sections of an input sheet. To separate out these sections into logical means, I am using headers and sub headers as seen below:

如果我有很多标题，那么编号标题将会有大量的手动工作。我已经尝试通过创建一个 Header1 命名范围来自动化该过程，这相当于下图中的灰色标题。所以在 A2 和 A11 单元格中，公式为 = Header1 。命名范围 Header1 中的公式为：

If I have a lot of headers, there will be a significant amount of manual work in numbering the headers. I have made some attempt at automating the process by creating a Header1 named range which equates to the grey headers in the picture below. So within the A2 and A11 cells, the formula is =Header1. The formula in the Header1 named range is:

IF（COUNTA（INDIRECT（CONCATENATE （ $ A $ 1， ：，ADDRESS（ROW（） - 1，COLUMN（）））））= 0,0，INDEX（INDIRECT（CONCATENATE（ $ A $ 1， ：，ADDRESS（ROW （） - 1，COLUMN（）））），MATCH（ROW（INDIRECT（ADDRESS（ROW（） - 1，COLUMN（）））），INDIRECT（CONCATENATE（ $ A $ 1， ：，ADDRESS（ROW （）-1，COLUMN（）））），TRUE）））+ 1

基本上该公式计算列中的所有值A并加1。换句话说，无论您在哪个工作表中，您每次在单元格中调用 Header1 时，会很好地递增（因此使用 INDIRECT ）。唯一的硬编码是起始单元格，它是 A1 ，我已经把一个0，因此它检测到从1开始。

Basically the formula counts all of the values in column A and adds 1 on. In other words it nicely increments every time you call Header1 in your cell, regardless of what worksheet you are in (hence the use of INDIRECT). The only piece of hard coding is the starting cell which is A1 and I have put a 0 in that so it detects to start from 1.

我的问题是 - 以类似的方式，我如何使用命名范围实现增量头，我如何为子标题做这个？我上图显示了我试图实现的效果（即 2.1 ， 2.2 ）但是我希望这是自动的只需在单元格中放一个 = Header2 的公式。

My question is - In a similar manner to how I have achieved incrementing headers using named ranges, how can I do this for sub headers? My picture above shows the effect I am trying to achieve (i.e. 2.1, 2.2) however I want this to be automatic by simply putting a formula of =Header2 in the cell for example.

编辑 - 我已经得到这个公式：

EDIT - I have gotten as far as this formula:

= SUM（INDIRECT（CONCATENATE（$ A $ 1，：，ADDRESS ROW（） - 1，COLUMN（） - 1））））& &安培; MAX（1，COUNTA（INDEX（INDIRECT（CONCATENATE（ $ B $ 1， ：，ADDRESS（ROW（） - 1，COLUMN（）））），MATCH（SUM（INDIRECT（CONCATENATE（ $ A $ 1 ： ADDRESS（ROW（） - 1，COLUMN（））））） - 1，INDIRECT（CONCATENATE（ $ A $ 1， ：，ADDRESS（ROW（） - 1，COLUMN（） - 1 ））），FALSE））：INDIRECT（ADDRESS（ROW（） - 1，COLUMN（））））））

当它作为公式放置在单元格中时可以工作，但在命名范围中使用时不起作用。奇怪的是，

...which works when placed as a formula in a cell, but doesn't work when used in a named range. Odd!

推荐答案

使用定义的名称来保存公式的好主意。

Good idea using Defined Names to hold the formula. However your formula is highly volatile.

建议在工作簿级别（范围）中创建两个定义的名称，如下所示：

Suggest to create two defined names at workbook level (scope) as follows:

1. 使用以下公式命名为 _Hdr （根据需要更改） ：

=IF( COLUMN() <> 1, "", 1 
+ MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ) )

使用以下公式命名为 _Sub （根据需要更改）

=IF( COLUMN() <> 2, "", 0.01 
+ IF( MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ) 
> MAX( INDEX(!$B:$B, 1 ) : INDEX(!$B:$B, - 1 + ROW() ) ), 
MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ), 
MAX( INDEX(!$B:$B, 1 ) : INDEX(!$B:$B, - 1 + ROW() ) ) ) )

/ li>

如果子项小于10，则使用 0.1 但少于100使用 0.01 （根据需要调整）

Use 0.1 if sub items are less than 10, if higher but less than 100 use 0.01 (adjust as required)

编辑：将！添加到公式确保引用将自动更新到使用公式的相应的 Sheet 。

Add ! to the column ranges in the formulas to ensure that the references will automatically update to the corresponding Sheet where the formula is used.

上面的公式被分为几行，以便阅读和理解，然后在创建名称时输入一行。

还建议隐藏定义名称 （即 Visible = False ）为了避免公式被意外改变。

以上名称：

• 没有必要在第1行输入零。


• 公式与忽略任何标签的数字一起工作。


• 他们首先检查公式是否在预期的列中输入（即 _Hdr 和 _Sub 列 1 和 2 / em>
  
• 由于公式与数字一起工作，他们可以获得每列的 MAX 号码，并添加 1 或 0.1 以生成下一个数字。


• 公式不是挥发性的（不使用他们使用 INDEXECT ）。


• 他们使用 INDEX 生成所需的范围。

• There is no need to enter zero at row 1.
• The formulas work with numbers ignoring any labels.
• They check first that the formula is entered in the expected column (i.e. _Hdr and _Sub columns 1 and 2 respectively).
• As the formulas work with numbers they get the MAX number for each column and add 1 or 0.1 to generate the next number.
• Formulas are no volatile (no use of INDIRECT).
• They use the INDEX function to generate the required ranges.

这篇关于Excel命名范围以增加标题和子标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！