资源描述：
《Excel根据导出的科目余额表 补全完整科目名称的公式.doc》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、Excel根据导出的科目余额表补全完整科目名称的公式 前段时间写的财务工作常用Excel公式集锦及解析(第一季）-Excel偷懒的技术-Excel根据导出的科目余额表补全完整科目名称的公式 前段时间写的财务工作常用Excel公式集锦及解析(第一季）-Excel偷懒的技术-知乎专栏，有朋友说看不懂那些复杂的公式，比如根据导出的科目余额表，补全完整科目名称的公式： =IFNA(VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)))),A$1:C7,3,)&“-”&B8,B8)现做一个详细解析： 公式思路： 我
2、们观察科目代码的规律可以发现，所有明细科目的代码都是在上一级科目代码后顺序添加“.01”、“.02”......明细代码，也就是说前面的部分都与上一级相同。 因而，我们只要截取前面相同部分的代码，然后用vlookup查找引用上级科目的完整科目名称，再添加上本级科目的名称即可，比如：“”科目代码，要补全其科目名称，只需先截取出其上一级科目代码“”，然后使用Vlookup函数，查找引用上一级科目名称“应收账款-重点客户-北京”,然后补上“-”和本级科目名称“北京华章公司”即可。 具体编制方法： 那如何截取出“”科目代码的“”呢？ 如果明细科目代码有规律的话，比如
3、都是两位，可以使用Left函数编制公式： =left(A8,len(A8)-3) 来截取，但是由于明细科目代码有二位的，也有三位的，所以不能使用此公式，只能先查找最后一个“.”的位置，然后根据其位置来截取上级科目代码。 那如何查找最后一个“.”的位置呢？ 可以使用公式下面的公式来查找 ＝FIND(“.”,A8,ROW($1:$20)) ROW($1:$20)会生成1到20的常量数组： {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20} 此公式的意思是：依次从A8单元格的第1位、第2位…第20位开始
4、查找“.”符号，查找到就返回其位置数，如果没有查找到，就会出错，返回#VALUE!。 因而，此公式将生成一个由错误值及“.”所在位置组成的序列： {5;5;5;5;5;8;8;8;12;12;12;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} 从上面的序列可以看出，12就是“”代码中最后“.”的位置数，那如何从上面序列中取到这个值呢？它在序列中的顺序号并不固定，但有一点是确定的，就是“除去错误值#VALUE!，它就是最后的值”了。所以我们可以利用Lookup的特点来取到这
5、个值。 LOOKUP有三个特点： 1、会忽略错误值 2、要求查找对象按升序排列，并且按二分法来查找3、如果LOOKUP找不到与“查找值”相等的数，它会使用“应查找区域”中小于或等于“查找值”的“最大值”因而可以编制下面的公式： ＝LOOKUP(0,1-FIND(“.”,A2,ROW($1:$20))) 这个公式为什么能刚好取得最后一个非错误值呢？ 这是由于 1-FIND(“.”,A2,ROW($1:$20))会生成下面的序列： {-4;-4;-4;-4;-4;-7;-7;-7;-11;-11;-11;-11;#VALUE!;#VALUE!;#VALU
6、E!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} 忽略掉错误值，相当于{-4;-4;-4;-4;-4;-7;-7;-7;-11;-11;-11;-11}，根据二分法，LOOKUP先取最中间那个值进行判断，－7小于查找对象零值，由于LOOKUP要求按升序排列，所以它很傻很天真地认为“右边的数比－7大，只有在右边才有可能找到0值”，LOOKUP就会按二分法在右边继续查找零值。 由于右边的数实际上都是小于零的，所以，LOOKUP会一直查找，直到查找到最后一个－11，根据规则“查找对象按升序排列”，这个－11它是最一个位置了，肯定就
7、是最大值了，所以LOOKUP最终返回－11。 然后，我们再在公式前加个负号将其转换为正数，然后使用Left函数截止左边的11位代码： ＝LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20))))就可得到上级科目代码“”了， 再使用VLOOKUP查找引用得到上级科目名称，公式： ＝VLOOKUP(“”,A$1:C7,3,)&“-”&B8也就是： ＝VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)))),A$1:C7,3,)&“-”&B8大家要注意本公式所在的单元格是C
