简单明了用Excel补全会计科目编码

很多公司在使用金蝶、用友等电算化会计软件,但这些软件导出的会计科目为层级显示,有些为左对齐,有些则为段首缩进。为了便于财务人员查看,现在需要将它们转换为“一级科目-二级科目-三级科目”的形式。对于类似结构的表格转换,手动操作效率很低,如果借助Excel 2019的内置函数就简单多了。

一、层级为左对齐的转换

比如在图1中A列为科目编码,B列为科目的中文名称,层级之间为左对齐,现在需要转换为F列、G列的样式(图1)。

1.需求分析

从上图中可以看出,转换后的完整科目是由一级科目(4位编码)、二级科目(假设有,共6位编码,添加2位部门编码)和三级科目(假设有,共8位编码,再添加2位员工编码)组成的,也就是根据B列的科目级别,依次将编码对应的中文科目名称使用“-”符号连接在一起,这可以使用TEXTJOIN函数来完成。

2.提取一级科目名称

在C2单元格中输入公式“=IFS(LEN(A2)=4,B2,LEN(A2)<>4,C1)”并下拉填充,即可完成一级科目名称的提取(图2)。

公式解释:

先使用LEN函数算出A2单元格中的文本字符串的字符数,然后使用IFS函数做出判断,如果字符数是4位(即对应一级科目),那么显示B2单元格中的数值(即对应的中文科目名称),否则就显示上一个单元格中的数值。

3.提取二级、三级科目名称

在D2单元格中输入公式“=IFS(LEN(A2)=4,"",LEN(A2)=7,B2,LEN(A2)<>7,D1)”、E2单元格中输入公式“=IFS(LEN(A2)=4,"",LEN(A2)=7,"",LEN(A2)=9,B2)”,下拉填充公式后即可完成二级、三级科目名称的提取(图3)。公式解释同上。

4.连接各级科目

在G2单元格中输入公式“=TEXTJOIN("-",TRUE,C2:E2)”,下拉填充公式后就可以完成各级科目的连接了(图4)。

公式解释:

“-”为各科目间的连接符号,参数“TRUE”表示忽略空值,“C2:E2”为连接区域,即上述提取的一级、二级、三级科目的中文名称。

最后将C~F列隐藏,以后只需将软件导出的数据分别粘贴在A列和B列中,在G列中就可以自动完成科目的转换了。

二、层级为段首缩进的转换

如果在图5的左侧,软件导出的层级科目采用了段首缩进的方式,现在需要将其转换为右侧的样式(图5)。

1.需求分析

本例和上例不同的是,这里从二级科目开始,每个级别的中文名称和上一级科目之间有4个空格缩进,因此需要删除缩进的空格,可以使用MID函数来完成。

2.提取一级科目名称

在C2单元格中输入公式“=IFS(LEN(A2)=4,B2,LEN(A2)<>4,C1)”并下拉填充,即可完成一级科目名称的提取。

3.提取二级科目名称

在D2单元格中输入公式“=IFS(LEN(A2)=4,"",LEN(A2)=7,MID(B2,5,15),LEN(A2)<>7,D1)”并下拉填充,即可完成二级科目名称的提取(图6)。

公式解释:

使用IFS函数判断A2单元格中的文本字符串的位数,如果是一级科目就显示为空,如果是二级科目,那么使用MID函数从B2单元格中的字符串的第5位开始(因为前面有两个中文空格,计四个字符),向右提取15个字符。向右提取的字符数请根据科目的最长字数来设置,假如最长科目的字数是20,那么字符数就设置为25。

4.提取三级科目名称

在E2单元格中输入公式“=IFS(LEN(A2)=4,"",LEN(A2)=7,"",LEN(A2)=9,MID(B2,9,15))”并下拉填充即可(图7)。公式解释同上,只不过从第9位开始提取字符(因为三级科目前有四个中文空格)。

5.完成科目组合

在F2单元格中输入公式“=TEXTJOIN("-",TRUE,C2:E2)”并下拉填充,最后将C~E列隐藏,即可完成最终的转换(图8)。