Excel中的分层自动编号 [英] Hierarchical auto-numbering in Excel

问题描述

有没有办法在VBA列表中给出的格式的列B中获取自动编号？ （A和B的密度是任意的;唯一的规则是大炮是连续两个和更多的A，A总是先行。）

  A 
 B 1 
 B 2 
 B 3 
 A 
 B 1 
 B 2 
 A 
 B 1 
 B 2 
 B 3 
  

如果需要，一些辅助计算（列）是正常的。

PS任务的扩展是

Is there a way to get auto-numbering in column "B" of the form given in the pattern below without VBA? (Densities of "A" and "B" are arbitrary; the only rule is that it cannon be two and more A's 'in a row', and "A" always goes first.)

A   
B   1
B   2
B   3
A   
B   1
B   2
A   
B   1
B   2
B   3

Some auxiliary calculations (columns) if needed are fine.

P.S. The extension of the task is here.

解决方案

Hi Garej: Based on the example you provided you could accomplish this with an "IF-THEN" formula in excel. Just put your letters in a blank excel document, paste the formula in cell B2, and you should be good. You need hardcode ="A" in the formula to be whatever "A" is. Here is the formula:

=IF(A2="A","",IF(A2=A1,B1+1,1))

If you don't want to hard code the ="A", you could also change the beginning from:

  A2="A" 

to

 A2=$A$1

which would allow you to have a dynamic value instead of "A" so the full code would be....

 =IF(A2=$A$1,"",IF(A2=A1,B1+1,1))

The logic behind this code is as follows: Look at the letter in the current row... IF the letter in the current row is "A" [or matches the contents of $a$1], then leave the number blank. IF the letter in the current row is the same as the letter in the previous row, then add one to the number in the row above. IF the letter in the current row is different than the row above [and is not "A" or $A$1], then start over at 1.

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