在带有层次结构的Excel中自动编号 [英] Automatic numbering in Excel with hierarchy

问题描述

我想进行自动摘要编号.我们唯一可以做的就是给A(标题)编号，但是字幕应该自动编号.如果标题= 1，副标题1.1，则低于1.1.1，依此类推.

I would like to do an automatic summary numbering. The only thing we could do is to number the A's (the titles) but the subtitles should be numbered automatically. If the title = 1, subtitle 1.1, below 1.1.1 and so on.

比方说标题是A，B和C都是字幕.

Let's say the titles are A, B and C are subtibles.

图案应该是这样

1.A

1.A

1.1 B

1.2 B

2.A

2.1 B

2.1.1 C

所以我尝试了这个: https://stackoverflow.com/a/32321112/7968011

我能得到什么

我们想要的东西

我们想要的东西

推荐答案

如果级别标记在A列中为"A"/"B"/"C"，而在B列中为标题，则可以使用以下(复杂的)代码:

If you have your Level Marker as "A" / "B" / "C" in Column A, and the heading in Column B, then you can use the following (convoluted) code:

=REPT(CHAR(9), CODE(A1)-65) & SUMPRODUCT(--(A:A="A")*--(ROW(A:A)<=ROW(A1))) & "." & IF(CODE(A1)>65,SUMPRODUCT(--(A:A="B")*--(ROW(A:A)<=ROW(A1))*--(ROW(A:A)>=MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1))))) & ".","") & IF(CODE(A1)>66,SUMPRODUCT(--(A:A="C")*--(ROW(A:A)<=ROW(A1))*--(ROW(A:A)>=MAX(--ROW(A:A)*--(A:A="B")*--(ROW(A:A)<=ROW(A1))))) & ".","") & CHAR(9) & B1

我们将其分解为以下步骤:

Let's break it down into steps:

1. 从制表符开始缩进标题(0表示"A"，1表示"B"，2表示"C"):REPT(CHAR(9), CODE(A1)-65)其中Char(9)是制表符.

2. 接下来，我们要计算我们拥有多少个"A".我们可以使用SUMPRODUCT作为数组公式来运行它，查找值是"A"且行是< =当前行的单元格:SUMPRODUCT(--(A:A="A")*--(ROW(A:A)<=ROW(A1))).之后点一个点，便会得到您的标题编号.

1. Start with Tabs to indent the heading (0 for "A", 1 for "B", 2 for "C"): REPT(CHAR(9), CODE(A1)-65) where Char(9) is a Tab.

2. Next, we want to count how many "A"s have we had. We can use SUMPRODUCT to run this as an Array Formula, looking for cells where the value is "A" and the Row is <= current row: SUMPRODUCT(--(A:A="A")*--(ROW(A:A)<=ROW(A1))). Shove a dot after that, and you have your heading number.

接下来，IF列A在字母中是"B"或更高版本(IF(CODE(A1)>65，因为CODE("A") = 65，CODE("B") = 66等)，那么我们要计算多少个自上一个"A"以来为B.这与我们的上一个查询非常相似，但是我们需要一个ROW(A:A)>=LAST_A.但是，LAST_A是什么?好吧，我们想要MAX行，其中A列="A"，而< =行是当前行.因此，MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1))).

Next, IF Column A is "B" or later in the alphabet (IF(CODE(A1)>65, since CODE("A")=65, CODE("B")=66, etc) then we want to count how many "B"s since the last "A". This is very similar to our last query, but we need a ROW(A:A)>=LAST_A. But, what is LAST_A? Well, we want the MAX Row where Column A = "A" and Row <= current row. So, MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1))).

现在，我们需要添加IF和句号以获取

Now, we need to add the IF and the full-stop, to get

If(Code(A1)>65,SUMPRODUCT(--(A:A="B")*--(ROW(A:A)<=ROW(A1))*--(Row(A:A)>=MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1))))) & ".","")

对上一个"B"之后的所有"C"重复相同的操作，然后最后添加一个制表符(CHAR(9))和列B中的值.

Repeat the same for all "C"s since the last "B", and then finally add a Tab (CHAR(9)) and the value in Column B.

(例如，如果要在行的开头或数字与图块之间使用4个空格或6个连字符或7个点而不是制表符，则只需替换第一个或最后一个CHAR(9))

(If you want, for example, 4 spaces or 6 hyphens or 7 dots instead of Tabs at the start of the row or between the number and the tile, just replace the first or last CHAR(9))

{EDIT} 示例:

这篇关于在带有层次结构的Excel中自动编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！