动态，多级，编号列表使用Excel公式 [英] Dynamic, multi-level, numbered list using Excel formula

问题描述

Folks，

我已经设法创建一个动态的，多层次的编号列表，只使用excel公式。这工作正常，我以为我会分享结果。我唯一的问题是如果有人想要这样做，并尝试简化它。我似乎无法上传示例表（第一次发布）。

I've managed to create a dynamic, multi-level, numbered list only using excel formula. This working correctly and I thought I'd share the result. My only question is if someone wants to take this and try to simplify it. I can't seem to upload an example sheet (first time posting).

编辑我使用的是一个名为tbOOA的表，其中2列，选择级别和结果。要使用公式，请复制以下编码部分的每行不以>开头，并将其复制到一个（长）的论坛。

Edit I'm using a table called tbOOA, with 2 columns, Choose Level and Result. To use the formula, copy each line of the coded section below that does not start with > and copy it into one single (long) forumula.

所需结果：
结果1

The desired result: Outcome 1

输出1.1

活动1.1.1

活动1.1.2

输出1.2

活动1.2.1

结果2

输出2.1

活动2.1.1等等等

>The formula explained:
>First choose if it is an Outcome, Output or Activity. If blank, then nothing
=IF([@[Choose Level]]="","",

>If it is an Outcome, put the word "Outcome" into column C
IF([@[Choose Level]]="Outcome", "Outcome " &

>and concatenate it with a count of the number of "Outcome" already chosen, limited from the header of the table, until this row
COUNTIF(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]], [@[Choose Level]]),

>Else, if "Output" is chosen, put the word "Output"
IF([@[Choose Level]]="Output", "Output "&

>and concatenate it with the number of "Outcome" from the table header to this line
COUNTIF(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]],"Outcome")

>and concatentate it with a dot and then
&"."&

>(This was the difficult part of the formula)
>count how many instances of "Output" there are between the last (most recent) instance of "Outcome" and the current row
COUNTIF(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]])*(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]]="Outcome"))),COLUMN([@[Choose Level]]))&":"&ADDRESS(ROW([@[Choose Level]]),COLUMN(([@[Choose Level]]))),TRUE),"Output"),

>Else, it must be an "Activity", so put "Activity" 
"Activity " &

>and concatenate with the number of "Outcome" from table header to this row, and dot
COUNTIF(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]],"Outcome")&"."&

>and count how many instances of "Output" between the last "Outcome" and this line
COUNTIF(INDIRECT(ADDRESS(SUMPRODUCT(MAX(ROW(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]])*(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]]="Outcome"))),COLUMN([@[Choose Level]]))&":"&ADDRESS(ROW([@[Choose Level]]),COLUMN(([@[Choose Level]]))),TRUE),"Output")&"."&

>and finally, count how many instances of "Activity" since the last "Output"
ROW([@[Choose Level]])-SUMPRODUCT(MAX(ROW(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]])*(tbOOA[[#Headers],[Choose Level]]:[@[Choose Level]]="Output"))))))

推荐答案

我可以把它缩小到大约一半的大小

I can get it down to about half the size of yours.

数组公式**：

=选择（MIN（ROWS（INDEX（[ChooseLevel]，1）：[@ ChooseLevel]），4）， Outcome1， Output1.1， Activity1.1.1，[@ ChooseLevel]&安培; COUNTIF（ INDEX（[ChooseLevel]，1）：[@ ChooseLevel]， 结果）及IF（[@ ChooseLevel] = 结果， ，替代（ 与 & COUNTIF（[@ ChooseLevel]：INDEX（ [ChooseLevel]，MATCH（1,0 /（INDEX（[ChooseLevel]，1）：[@ ChooseLevel] = 结果）））， 输出）&安培;&安培; COUNTIF（[@ ChooseLevel] ： INDEX（[ChooseLevel]，MATCH（1,0 /（INDEX（[ChooseLevel]，1）：[@ ChooseLevel] = 输出））） 0， 活动）， ）））

关心

**数组mulas不以与standard公式相同的方式输入。而不是按ENTER键，您首先按住CTRL和SHIFT，然后只按ENTER。如果您已经正确完成，您会注意到Excel在公式周围放置大括号{}（尽管不要手动插入这些）。

这篇关于动态，多级，编号列表使用Excel公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！