递增计算公式,excel逐年递增计算公式

作者：祝洪忠 转自：EXCEL之家ExcelHome

实际工作中，经常会有一些数据需要按照一定的规则进行拆分。以excel下图为例，A列是一些会计公式科目，现在需要将这些会计科目按一级科目、二级科目、三级科目这样分开。如BCD列中的样子。

对于数据的拆分，首先需要找出数据的规律。

咱们看A列的数据中，不同的科目之间都是用“/”隔开的。对于这种很有规律的数据源，用分列的方法可以快速的将数据拆分开。

接下来咱们说说具体的步骤：

首先选中A2:A17单元格区域，依次单击【数据】，【分列】。弹出【文本分列向导第1步共3步】对话框，保留默认选项，单击【下一步】按钮。

在弹出的【文本分列向导第2步共3步】对话框中，分割符号复选框中，勾选“其他”，在文本框中输入数据源中的间隔符号“/”，单击【下一步】按钮。

在弹出的【文本分列向导第3步共3步】对话框中，目标区逐年域文本框中输入=$B$2 ，单计算击【完成】按钮，这样就快速完成了数据的拆分。

有时候咱们的数据源是不断变化的，如果每次都要用分列来完成就显得比较麻烦了。

除了使用基础操作的方法，还可以使用公式的方法来完成数据分列。

仍然以上面这个数据为例，在B2单元格输入以下公式，向右、向下复制：

=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMN(A1)*99-98,99))

完成后的效果如图：

下面咱们简单说说这个公式的意思递增：

1、REPT函数的作用是按照给定的次数重复显示文本，REPT(" ",99)部分也就是将" "（空格）重复99次。

2、SUBSTITUTE函数的作用是在文本字符串中用新字符串替代旧字符串。

SUBSTITUTE($A2,"/", REPT(" ",99))的意思是将$A2单元格中的"/"替换成99个空格。

3、COLUMN(A1)*99-98部分，由于COLUMN函数的参数A1使用了相对引用。在公式向右复制时，公式会变成：

COLUMN(B1)*99-98

COLUMN(C1)*99-98….

得到的结果是1,100,199…..这样的一个递增序列，作为MID函数的参数。

4、MID 函数用于返回文本字符串中从指定位置开始的特定数目的字符。

MID(SUBSTITUTE($A2,"/", REPT("",99)),COLUMN(A1)*99-98,99)

也就是对SUBSTITUTE($A2,"/", REPT("",99))返回的结果分别从第1位、第100位、第199位开始截取，截取的长度为99个字符长度。

5、TRIM函数用于清除文本中所有的空格，得到相应的科目内容。

公式的运算过程讲完了，有小伙伴可能说：在2013及以上版本中，还有快速填充功能呢。老祝说，快速填充功能虽然便捷，但是在数据比较复杂的情况下，无法保证百分百的准确，还需要我们目测检验，所以咱们今天就不说这个方法啦。

好了，今天的内容就是这些吧，祝小伙伴们一天好心情！

图文制作：祝洪忠