Excel中的分层自动编号（含三个级别） [英] Hierarchical auto-numbering (with three levels) in Excel

问题描述

如果我们有一个三级层次结构，只需枚举B（根据模式），但是某些C-则会干扰B。

问题：从列中获取列 B 结果 A 。

  A + 
 B 1 
 B 2 
С-
 B 3 
 A + 
 B 1 
С-
 B 2 
 A + 
С-
 B 1 
 B 2 
 B 3 
  

PS 此任务源于需要枚举Excel中复杂层次结构

想象一下， A - 1级; B - 第2级; C - 3级（在上面的例子中有一些滥用逻辑 C - code> A ，实际上通常不是这样）。

两级层次结构的简单情况显示为< a href =https://stackoverflow.com/questions/32313313/hierarchical-auto-numbering-in-excel> here 。

解决方案

我也去了一个帮助列（就像我憎恶他们一样）来显示每个A + p>

将其放在C1中： = ROW（A1）

将它放在C2中： = IF（A2 =A +，ROW（A2），C1）

它使用扩展的范围与可重复的起点。把它放在B2中：

将它放在B2中： = IF（OR（A2 =C - ，A2 = A +），，IF（A1 =A +，1，MAX（INDIRECT（B& C2&B& ROW（A1）））+ 1））

尽可能向下拖动。

希望有所帮助。以下是我收到的结果：

  A + 1 
 B 1 1 
 B 2 1 
 C- 1 
 B 3 1 
 A + 6 
 B 1 6 
 C-6 
 B 2 6 
 A + 10 
 C- 10 
 B 1 10 
 B 2 10 
 B 3 10 
  

What if we have a three-level hierarchy and need to enumerate only "B"s (according to the pattern), but some "C-"s interfere with "B"s.

Problem: to get column B result from a given column A.

A+  
B   1
B   2
С-  
B   3
A+  
B   1
С-  
B   2
A+  
С-  
B   1
B   2
B   3

P.S. The task arose from the need to enumerate complex hierarchy in Excel.

Imagine that A - level 1; B - level 2; C - level 3. (with some abuse of logic in the example above as C- in the pattern goes after A, which in practice is usually not the case).

The simple case of two-level hierarchy is shown here.

解决方案

I also went with a helper column (as much as I detest them personally) to show the row of each A+

Put this in C1: =ROW(A1)

Put this in C2: =IF(A2="A+",ROW(A2),C1)

It uses an expanding range with a rebasable starting point. Drag down as far as your data goes.

Put this in B2: =IF(OR(A2="C-",A2="A+"),"",IF(A1="A+",1,MAX(INDIRECT("B" & C2 & ":B" & ROW(A1)))+1))

Drag down as far as your data goes.

Hope that helps. Here are the results I received:

A+      1
B   1   1
B   2   1
C-      1
B   3   1
A+      6
B   1   6
C-      6
B   2   6
A+      10
C-      10
B   1   10
B   2   10
B   3   10

这篇关于Excel中的分层自动编号（含三个级别）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！