财务工作常用Excel公式集锦及解析.docx

财务工作常用Excel公式集锦及解析第一季　科目余额表及明细账常用公式一、按科目级次筛选需求背景在财务日常工作中，经常需要将科目余额表或其他代码按层级进行筛选，比如筛选出总账科目、筛选出二级科目。解决方案由于科目代码的格式都是固定的，比如总账4个字符长度，二级7个字符长度。因而，这个需求实际上就是按字符个数筛选。我们通常是用LEN函数构造辅助列计算字符个数，再对辅助列进行筛选。实际上一个小技巧就可帮助我们轻松按字符个数筛选：选中表格，然后点击自动筛选，再在筛选搜索框中输入“????”（英文半角状态下输入），即可筛选出4个字符长度的记录。要筛选其他长度的记录，以此类推。具体操作详见操作演示知识点解释“?”是通配符，代表单个字符。所以在筛选搜索框里输入几个问号就代表筛选几个字符的记录，对字母、汉字、数字、字符均有效。我们将此知识点进一步拓展，可以按字符个数求和，比如对所有总账科目（字符长度为四个）进行求和的公式：=SUMIF(A2:A22,????,C2:C22)二、判断科目是否为最末级需求背景日常工作中我们将科目余额表导出，通常包含了第一级到最后一级，要分别筛选总账科目、二级科目等可以使用前文中的技巧，那如果要筛选或标注出最末级科目，该怎么办呢？解决方案可以使用辅助列法。辅助列可以化繁为简。先使用公式判断是否为最末级，然后筛选出该辅助列为“最末级“的记录行，公式如下：=IF(LEN(A2)=LEN(A3),最末级,“”)知识点解释在《“偷懒的技术：打造财务Excel达人》中说过“要设计一套功能强大的财务工作表，更需要的是表格设计过程中的逻辑思维和函数的拓展应用能力”，在编写公式前，先不要忙着琢磨用什么函数，而应该分析数据的规律，总结出规律后，再编写公式。粗一看，要判断科目是否为最末级，感觉无从下手，但是我们分析一下科目余额表就可发现，同一个总账科目下越是明细级的科目，其代码越长（废话，这个财务人都知道）。也就是说，如果用本行的科目代码字符数与下一行的相比，如果字符数与下一行相等（同级）或比它多（更明细级），它就是最末级的（前提条件是科目余额表要按科目先后顺序排列）。说明：本案例如果使用高级筛选，在F2单元格输入筛选条件公式=LEN(A2)=LEN(A3)，再以其为条件筛选，可筛选出大部分符合条件的记录。由于条件公式中的A3按要求应该为$A$3，但是如果写成这样，就与需求不符了，故写成A3，这样一来就不符合高级筛选“条件公式中除记录的第一行外的所有其他引用要求是绝对引用”这一条件，因而最后一行未筛选出，存在小小的瑕疵，因而不适合使用高级筛选。三、分离科目代码和科目名称需求背景：某些财务软件导出的科目余额表中是“1122.01.898偷懒的技术“这样的格式，需要将代码和名称分离，或者遇到不规范的数据，如人名与手机号“龙逸凡18，需要将数字和汉字分离为二列。解决方案如果科目代码长度一致，或者代码和名字中有某个固定分隔符，则可使用“分列”功能来分离科目代码和名称，如果没有，则需要使用下面的公式：取科目代码=LEFT(A2,2*LEN(A2)-LENB(A2))取科目名称=RIGHT(A2,LENB(A2)-LEN(A2))这个公式不太完善，当名称中有字母或数字时，则公式结果会出错，比如B16、B17单元格，这种情况下提取科目代码需使用下面的数组公式：=LEFT(A2,MIN(IF(MID(A2,ROW($1: $99),1)=a,ROW($1: $99),99))-1)输入完后需要同时按Ctrl+Shift+Enter三键输入如果电脑上安装了Excel2013以后的版本，也可使用快速填充，具体操作详见下面的操作演示：知识点解释第一个公式中的Len函数：计算字符数。LENB函数：计算字节数，一个汉字为二个字节。所以可以用LENB-LEN计算字符串中汉字的个数。由于公式是根据汉字个数来分离数字和汉字，所以，当名称中包含字母或数字，以及数字和汉字相互夹杂时，公式提取的结果就不符合要求。第二个公式的设计思路是逐个截取字符串中的每一个字符，判断其是否为字母或汉字，即是否为”a”之后的字符（在Excel里，如按升序排列，则字母在数字后，汉字在字母后，也就是1→9→a→z→吖（ā）→酢（zuò），这也是它们的大小顺序）。公式使用ROW($1: $99)生成1到99的常量数组，写成99只是为了保证大于等于字符串的长度，根据实际情况也可改成50，30等。MID(A2,ROW($1: $99),1)是依次截止A2单元格的第1个，第2个。。。第99个。截取出的字符如果是字母或汉字，则给其字符所在位置的顺序号，否则，给它的顺序号是99。再用MIN函数来提取第一个字母、汉字的位置。最后用LEFT来截取左边的数字和字符组成的代码。同理，如果要提