Excel常见问题:如何用公式实现单元格内容递增?

2015-04-13 17:58 阅读 77 次 评论关闭

Excel 常见问题:在excel中如何用公式实现单元格内容递增
如:    AB12
AB13
AB14
.......
AB100
条件是无法确定储存格中的内容的前面有多少个字符,也就是,可能是2个,也可能是3个,或者更多。

解答:為什麼要用公式呢?

如 A1 = AB12 ,只要你向下拉的複制就可以。

公式可參考 (條件是 AB12 不可以是 AB02, 處理 0 為首的數字 有困難,亦不可以只有英文字)

A1 = AB12
A2 = LEFT(A1,LEN(A1)-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},"")))) & RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))+1
(A1 = AB12

公式

=LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))
答案看到的是 4 ,但其實它回傳一個數組 {4,3,3,4,4,4,4,4,4,4}

公式

=LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))
答案看到的是 0 ,但其實它回傳一個數組 {0,1,1,0,0,0,0,0,0,0}

公式
=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))) 是將 {0,1,1,0,0,0,0,0,0,0} 加總
= 2)

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:Excel常见问题:如何用公式实现单元格内容递增? | 猎微网
分类:Excel 标签:, , , ,

评论已关闭!