【会计实操经验】财务常用Excel公式解释：补全完整科目名称.pdf

只分享有价值的知识点，本文由梁博会计学堂精心收编，大家可以下载下来好好看看！【会计实操经验】财务常用【会计实操经验】财务常用 ExcelExcel 公式解释：补全完整科目名称公式解释：补全完整科目名称 前言 前段时间写的 财务工作常用 Excel 公式集锦及解析(第一季）-Excel 偷懒的技术-知乎专栏，有朋友说看不懂那些复杂的公式，比如根据导出的科目余额表，补全完整科目名称的公式：=IFNA(VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20),A$1:C7,3,)&“-”&B8,B8)现做一个详细解析：公式思路：我们观察科目代码的规律可以发现，所有明细科目的代码都是在上一级科目代码后顺序添加“.01”、“.02”.明细代码，也就是说前面的部分都与上一级相同。因而，我们只要截取前面相同部分的代码，然后用 vlookup 查找引用上级科目的完整科目名称（C 列），再添加上本级科目的名称即可（C 列），比如：“1122.01.020.09”科目代码，要补全其科目名称，只需先截取出其上一级科目代码“1122.01.020”，然后使用Vlookup 函数，查找引用上一级科目名称“应收账款-重点客户-北京”,然后补上“-”和本级科目名称“北京华章公司”即可。具体编制方法：那如何截取出“1122.01.020.09”科目代码的“1122.01.020”呢？如果明细科目代码有规律的话，比如都是两位，可以使用 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 位开始查找“.”符号，查找到就返回其位置数，如果没有查找到，就会出错，返回#VALUE!。因而，此公式将生成一个由错误值及“.”所在位置组成的序列：5;5;5;5;5;8;8;8;12;12;12;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!从上面的序列可以看出，12 就是“1122.01.020.09”代码中最后“.”的位置数，那如何从上面序列中取到这个值呢？它在序列中的顺序号并不固定，但有一点是确定的，就是“除去错误值#VALUE!，它就是最后的值”了。所以我们可以利用 Lookup 的特点来取到这个值。只分享有价值的知识点，本文由梁博会计学堂精心收编，大家可以下载下来好好看看！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!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!忽略掉错误值，相当于-4;-4;-4;-4;-4;-7;-7;-7;-11;-11;-11;-11，根据二分法，LOOKUP 先取最中间那个值（7）进行判断，7 小于查找对象零值（0），由于 LOOKUP 要求按升序排列，所以它很傻很天真地认为“右边的数比7 大，只有在右边才有可能找到 0 值”，LOOKUP 就会按二分法在右边继续查找零值。由于右边的数实际上都是小于零的，所以，LOOKUP 会一直查找，直到查找到最后一个11，根据规则“查找对象按升序排列”，这个11 它是最一个位置了，肯定就是最大值了，所以 LOOKUP 最终返回11。然后，我们再在公式前加个负号将其转换为正数，然后使用 Left只分享有价值的知识点，本文由梁博会计学堂精心收编，大家可以下载下来好好看看！函数截止左边的 11 位代码：LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)就可得到上级科目代码“1122.01.020”了，再使用 VLOOKUP 查找引用得到上级科目名称，公式：VLOOKUP(“1122.01.020”,A$1:C7,3,)&“-”&B8 也就是：VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20),A$1:C7,3,)&“-”&B8 大家要注意本公式所在的单元格是 C8，公式的查找范围是 A$1:C7,这是混合引用，查找范围的起始行是固定的（A$1)，查找范围的最末行不是固定的（C7），会随着下拉填充公式时，自动变为 C8、C9、C10.，也就是随着公式下拉填充，会自动将前面已经补充完整的会计科目名称包含进来，这个借用前面公式计算结果的公式的编制思路值得大家学习借鉴。如果没查找到，那就是新科目，新科目直接引用其科目名称即可=IFNA(VLOOKUP(“1122.01.020”,A$1:C7,3,)&“-”&B8,B8)即最终公式：=IFNA(VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20),A$1:C7,3,)&“-”&B8,B8)总结：本公式有二点值得仔细揣摸学习：1、找到最后一个特定字符的公式：-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)2、借用前面公式的计算结果。只分享有价值的知识点，本文由梁博会计学堂精心收编，大家可以下载下来好好看看！会计是一门很实用的学科，从古至今经历漫长时间的发展，从原来单一的以记账和核账为主要工作，发展到现在衍生出来包括预测、决策、管理等功能。会计是一门很基础的学科，无论你是企业老板还是投资者，无论你是税务局还是银行，任何涉及到资金决策（有些可能不涉及资金）的部门都至少要懂得些会计知识。而我们作为专业人员不仅仅是把会计当作“敲门砖”也就是说，不仅仅是获得了资格或者能力就结束了，社会是不断向前进步的，具体到我们的工作中也是会不断发展的，我们学到的东西不可能会一直有用，对于已经舍弃的东西需要我们学习新的知识来替换它，这就是专业能力的保持。因此，那些只把会计当门砖的人，到最后是很难在岗位上立足的。希望大家多多不断的补充自己的专业知识。