我正在创建一个电子表格应用程序,它将要求用户填写输入表的不同部分。为了将这些部分分离为逻辑手段,我使用标题和子标题,如下所示:
如果我有很多页眉,在对页眉编号时将需要大量的手工工作。我通过创建一个Header1
命名范围(相当于下图中的灰色标题)来使过程自动化。因此,在A2
和A11
单元格中,公式为=Header1
。 Header1
命名范围内的公式为: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开始。
我的问题是-以与我如何使用命名范围实现增加标头的方式类似的方式,如何为子标头实现此目的?上面的图片显示了我想要达到的效果(即2.1
,2.2
),但是我希望通过简单地将=Header2
公式放在单元格中来实现这种效果。
编辑-我已经达到了这个公式:=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()))))
...当作为公式放置在单元格中时起作用,但是在命名范围中使用时不起作用。奇!
最佳答案
使用Defined Names
保存公式的好主意。但是,您的配方易挥发。
建议在工作簿级别(作用域)创建两个定义的名称,如下所示:
用以下公式命名为_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() ) ) ) )
如果子项小于10,则使用
0.1
;如果子项小于或等于100,则使用0.01
(根据需要进行调整)编辑:将
!
添加到公式中的列范围,以确保引用将自动更新为使用该公式的相应Sheet
。上面的公式分为几行,以方便阅读和理解,在创建名称时将其输入为一行。
还建议隐藏
Define Names
(即Visible = False
),以避免意外更改公式。具有以上名称:
无需在第一行输入零。
公式使用忽略任何标签的数字。
他们首先检查是否在期望的列中输入了公式(即
_Hdr
和_Sub
列分别为1
和2
)。当公式与数字一起使用时,它们将为每列获取
MAX
数字,并添加1
或0.1
以生成下一个数字。公式不易变(不使用
INDIRECT
)。他们使用
INDEX
函数生成所需的范围。关于excel - Excel命名范围使递增的标题和子标题,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/34112138/