Excel中的分层自动编号（含三个级别）

本文介绍了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 ，实际上通常不是这样）。

两级层次结构的简单情况显示为 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