简单明了用Excel补全会计科目编码 - 青浦海洋数码电脑城

[db:作者]

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