在Excel中创建标题，子标题和子标题的列表 [英] Creating a list of headings, sub-headings and sub-subheadings in Excel

问题描述

我正在寻找一个标题类型1，子标题类型2和子子标题类型3的列表，并且每个随后的标题实例在excel中都会递增.例如

I am looking to make a list of heading type 1, sub-heading type 2 and sub-sub-heading type 3, and each subsequent instance of a heading increments in excel. e.g.

Outcome 1
Output 1.1
Activity 1.1.1
Activity 1.1.2
Output 1.2
Activity 1.2.1
Activity 1.2.2
Activity 1.2.3
Outcome 2
Output 2.1
Activity 2.1.1
etc

这是我的公式-成为一个复杂的嵌套IF语句:

Here is my formula - getting to be a complicated nested IF statement:

IF([@Column1]="","",
IF([@Column1]="Outcome", "Outcome " & COUNTIF(tbOOA[[#Headers],[Column1]]:[@Column1], [@Column1]), 
IF([@Column1]="Output","Output "& COUNTIF(tbOOA[[#Headers],[Column1]]:[@Column1],"Outcome") ***&"."&*** COUNTIF(tbOOA[[#Headers],[Column1]]:[@Column1],[@Column1]), 
"Activity " & "serious help")))

在第1列中，从结果"，输出"或活动"列表中进行选择.

In Column 1, choose from a list of 'Outcome', 'Output', or 'Activity'.

在第2列中，计算适当的数字，例如输出1.2

In column 2, calculate the appropriate number, e.g. Output 1.2

如果该行为空，则为空. -很好

If the row is empty, then nothing. - Fine

If it is "Outcome", count from the header until current row for the number of instances of "Outcome". - Fine    
Else if it is "Output", count the number of "Outcome"s there are. - Fine    
This is where it falls apart. Trying to calculate the number after the "." (bold and italic)

我需要计算输出"的实例数，但是每当有一个新的结果"时，它就必须重置为1.

I need to count the # of instances of "Output", but then this has to reset to 1 each time there is a new 'Outcome'.

我要遵循的逻辑是:

(# of "Outputs" from the table header until the current row) minus 
(# of "Outputs" from the table header until the last instance of "Outcome")

我已经尝试过几次尝试计算行号，但是一切都出现了问题.

I've tried several attempts at calculating row number, but everything has been problematic.

活动的逻辑是相同的，尽管会使公式变得更加复杂，而且直到我获得第2级排序后，我才开始着手进行这一工作.

The logic is the same for activities, though will complicate the formula even more and I haven't bothered to start on that until I can get level 2 sorted.

有人知道类似的问题/解决方案吗?

Does anyone know of a similar problem/solution?

推荐答案

如果您愿意使用隐藏的帮助程序列，则公式将变得更易于管理.使用A列保存结果"，输出"和活动"数据.

If you are open to using hidden helper columns, the formulas become much more manageable. Use Column A to hold your "Outcome", "Output", and "Activity" data.

然后使用B列处理Outcome数字，C列处理Output数字，D列处理Activity数字.将最终结果合并到E列中.

Then use column B to deal with Outcome numbers, column C to deal with Output numbers, and column D to deal with Activity numbers. Merge the final results together in Column E.

在B1，C1和D1中，手动写入第一个值(1，0和0).

In B1, C1, and D1, manually write in the first values (1, 0, and 0).

然后，从B2开始，填写以下内容:

Then, fill down starting from B2 with the following:

=IF(A2="Outcome",B1+1,B1)

仅当找到下一个结果时，此方法才递增.

This works by incrementing only if you have found your next Outcome.

使用以下内容从C2进行填充:

Fill down from C2 with the following:

=IF(A2="Outcome",0,IF(A2="Output",C1+1,C1)) 

仅当找到下一个输出时，此选项才递增.如果您有新的结果，它将重置为0.

This works by incrementing only if you have found your next Output. It resets to 0 if you have a new Outcome.

然后从D2填充

=IF(OR(A3="Outcome",A3="Output"),0,IF(A3="Activity",D2+1,D2))

它与先前的公式非常相似，但是会在结果"或输出"上重置.

It's very similar to the prior formula, but resets on an Outcome or an Output.

最后，在D4中，将所有内容与

Finally, in D4, merge it all together with

=B1&IF(C1>0,"."&C1&IF(D1>0,"."&D1,""),"")

&是字符串连接操作.通过检查内部值是否为0，如果下一个数字不为零，我们仅将.和下一个数字连接起来.

& is a string concatenate operation. By checking if the inner values are 0, we only concatenate . and the next number if the next number is non-zero.

这篇关于在Excel中创建标题，子标题和子标题的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！