excel - Excel递增字母数字组合(Excel Increment Letter Number Combinations)

Question

I was wondering if there was a formulation in excel to increment a combination of letters and numbers.

(我想知道excel中是否有一种公式可以增加字母和数字的组合。)

The structure is: AA1AA1, AA1AA2, etc and continues to AA1AB1, AA1AB2, and obviously cycles through (like number plates on a car) until it reaches ZZ0ZZ0.

(结构为：AA1AA1，AA1AA2等，并延续到AA1AB1，AA1AB2，并且显然循环（就像汽车上的车牌一样）直到到达ZZ0ZZ0。)

Any assistance would be much appreciated.

(任何帮助将不胜感激。)

  ask by Aliqua translate from so

Answer 1

Yes, we can do by using this formula: =LEFT(ADDRESS(1,FLOOR(ROW(G1),26)/(26*26)+1,4))&ADDRESS(1,FLOOR(ROW(G1),26)/(26*26)+1,4)&","&LEFT(ADDRESS(1,FLOOR(ROW(G1),26)/(26*26)+1,4))&RIGHT(ADDRESS(1,FLOOR(ROW(H1),26)/(26)+1,4),2)

(是的，我们可以使用以下公式进行操作： =LEFT(ADDRESS(1,FLOOR(ROW(G1),26)/(26*26)+1,4))&ADDRESS(1,FLOOR(ROW(G1),26)/(26*26)+1,4)&","&LEFT(ADDRESS(1,FLOOR(ROW(G1),26)/(26*26)+1,4))&RIGHT(ADDRESS(1,FLOOR(ROW(H1),26)/(26)+1,4),2))

Break down:

(分解：)

To get the first half letters (AA1) 26*26 times: Step 1. =FLOOR(ROW(A1),26)/(26*26) Step 2. =LEFT(ADDRESS(1,FLOOR(ROW(A1),26)/(26*26)+1,4))&ADDRESS(1,FLOOR(ROW(A1),26)/(26*26)+1,4)

(要获得前半个字母（AA1）26 * 26次：步骤1. =FLOOR(ROW(A1),26)/(26*26)步骤2 =LEFT(ADDRESS(1,FLOOR(ROW(A1),26)/(26*26)+1,4))&ADDRESS(1,FLOOR(ROW(A1),26)/(26*26)+1,4))

To get the second half (AA1) 26 times: Combination of first half & second half

(26次获得下半场（AA1）：上半场和下半场的组合)

Step 3. FLOOR(ROW(A1),26)/(26) Step 4. LEFT(ADDRESS(1,FLOOR(ROW(A1),26)/(26*26)+1,4))&RIGHT(ADDRESS(1,FLOOR(ROW(B1),26)/(26)+1,4),2)

(步骤3. FLOOR(ROW(A1),26)/(26)步骤4. LEFT(ADDRESS(1,FLOOR(ROW(A1),26)/(26*26)+1,4))&RIGHT(ADDRESS(1,FLOOR(ROW(B1),26)/(26)+1,4),2))

Then, combine all together.

(然后，将所有组合在一起。)

If anybody can improve this answer, would be appreciated.

(如果有人可以改善这个答案，将不胜感激。)