《财务工作常用Excel公式集锦及解析》由会员分享，可在线阅读，更多相关《财务工作常用Excel公式集锦及解析（11页珍藏版）》请在金锄头文库上搜索。
1、财务工作常用Excel公式集锦及解析第一季科目余额表及明细账常用公式一、按科目级次筛选需求背景在财务日常工作中，经常需要将科目余额表或其他代码按层级进行筛选，比如筛选出总账科目、筛选出二级科目。秫目代码H科目名称金響V伽现金7,%3,193.001001.01人民币2皿備.（*1001.02V1,10SM3122应曲账款阳网了443勺 122.01蚕点客户25,&42,743.001122.01.0207,910,63.001122.01.020.09寸谅华童公司b.OJ.TSS.OT1122.01.020.2121220,400.(1122.01.021上悔4,&57,2&8.0011220
2、1 021.01上海凱公司4.&57,2&S.(1122.fl1.5329,272,767.001122.01.53203公司9.272,767.(1122.01.7603,039,130.001122.01.760.01南岸区3,039,130.OT1122.01 .m愉攧的技术&62颗.(1122.01 .S9S.01fr62.-0.00*1122.03具他S.257,m.(1122.03.020杂七耿3,257,96.001122.03.020.013.257,W.(1122.03.020.01.011.&S1,473.001122.03.020.01.02解决方案由于科目代码的格式都是
3、固定的，比如总账4个字符长度，二级7个字符长度。因而， 这个需求实际上就是按字符个数筛选。我们通常是用LEN函数构造辅助列计算字符个数， 再对辅助列进行筛选。实际上一个小技巧就可帮助我们轻松按字符个数筛选：选中表格，然后点击自动筛选，再在筛选搜索框中输入“（英文半角状态下输入），即可筛选出4个字符长度的记录。要筛选其他长度的记录，以此类推。具体操作详见操作演示P 3右5TWflibd a t曲=xan iGE舅RMrtrtf ffTOl音 啼?停蟹耐1- iis16UjAQl rdm i i | i-1tiSS-QI 1 01途Jt.出吕4.E57JS.KnS -H 也恥它静H11?L：J15
4、12X0fliTgr.xUttt; ai ?tai.J.1X：1 T昭|1137时呻E:iZ”匐工刊-1IK H-BKwadT 十LM&I:TiW悴Sim裁賞W.Miin “征X M .沁工ii盖的做忻WWr；!itK HIM1 EMMn-r- ii ： - : i :.：napiT詞 r诫吨亠廿左活科事歸主勺革瑞宅砂官名摒|里也来卿B| 阿吉駆M .cj rsn昌m -Br知识点解释“?”是通配符，代表单个字符。所以在筛选搜索框里输入几个问号就代表筛选几个字 符的记录，对字母、汉字、数字、字符均有效。我们将此知识点进一步拓展，可以按字符个 数求和，比如对所有总账科目（字符长度为四个）进行求和
5、的公式：二SUMIF(A2:A22,C2:C22)二判断科目是否为最末级需求背景日常工作中我们将科目余额表导出，通常包含了第一级到最后一级，要分别筛选总账科目、二级科目等可以使用前文中的技巧j那如果要筛选或标注出最末级科目，亥怎么办呢？在阿,睡末總，”)全薇2 1W13 血 1 D1丄 IfrJI .025 M2Z5 J122.011 J 122.01 .320现全A Kt 美元 应I攵临 重点宫口7W3.iga.OT202.066.3 &5.761,102.00 规胁V44M 25.&4.749.30? .910,669.J 122.01 .300.21钱弟2,220,400.0010J 1
6、22.01 .321上灣l.&?,26Q.0l111122.01/321.01上響勢1公司1.&7,253.121122.01.532江费9,272, F&F.OT131122.01. &320S江匹也几亡可141122.017 W3130.001122.01.7S0.01秦岸区3*3 曾 130.H22C1.B9e662.gaa.oo1122G1.B9S01662,93I.W北京i JJ 22.01其他&25了腳.OflB57,995.OflII22CSI? 03.0(20 iif?03.K001131920;r22E Li Jk112JO3.O2OC1 1裁11? 03.020 01.02
7、匡 4t& K7.99S.0&1.C31.473.005,57,518.0&另末飯F_|_ 3HI寻求：FALSE侯用必翊断本科日是芒解决方案可以使用辅助列法。辅助列可以化繁为简。先使用公式判断是否为最末级，然后筛选出该辅助列为最末级的记录行，公式如下:=IF(LEN(A2) = LEN(A3),最末级，“”)知识点解释在偷懒的技术：打造财务Exce I达人中说过“要设计一套功能强大的财务工作表，更需要的是表格设计过程中的逻辑思维和函数的拓展应用能力”在编写公式前，先不 要忙着琢磨用什么函数，而应该分析数据的规律，总结出规律后，再编写公式。粗一看，要 判断科目是否为最末级，感觉无从下手，但是我
8、们分析一下科目余额表就可发现，同一个总 账科目下越是明细级的科目，其代码越长（废话，这个财务人都知道）。也就是说，如果用 本行的科目代码字符数与下一行的相比，如果字符数与下一行相等（同级）或比它多（更明 细级），它就是最末级的（前提条件是科目余额表要按科目先后顺序排列）。说明：本案例如果使用高级筛选，在F2单元格输入筛选条件公式二LEN（A2） = LEN（A3），再以其为条件筛选，可筛选出大部分符合条件的记录。由于 条件公式中的A3按要求应该为$A$3，但是如果写成这样，就与需求不符了，故写成A3， 这样一来就不符合高级筛选“条件公式中除记录的第一行外的所有其他引用要求是绝对引 用”这一条件
9、，因而最后一行未筛选出，存在小小的瑕疵，因而不适合使用高级筛选。三、分离科目代码和科目名称需求背景：某些财务软件导出的科目余额表中是“1122.01.898 偷懒的技术“这样的格式，需要将代码和名称分离，或者遇到不规范的数据，如人名与手机号“龙逸凡18X12345678”， 需要将数字和汉字分离为二列。解决方案C2:x=LEFT(A2lMIN(IFiMID(A2,RM($1：SW)J=,aFMt$1：Sl丄A1科目代码及名称公式咯|公血21001现金10011OT131001JD1人民币1001.011OT1.011001磋美兀1DD1.021001 jOQ&1122应收!悖11221122&
10、1122制重点客户1122.011122.0171122.01 450 北京112201.0201152.01.020S1122.01北京华章兹司1122.01.020 091122.01.020.0991122.01.020.211122.01.020.211122.01 .fl20.21101122.A1.021 上悔1122.01.0211152.01 .-021111122.01.021亦上海逸凡公司1122 01.021.011122.01.021.01121122.01 .&32 江西1122.01.5321122.01 .-5321311沦劝.S32.闊江西BR-ii-司1122
11、.01.532.031122.01.532.M141122.01 .两0重庆1122.01.7601122.01.7S0151122.01 .恥0.们南岸区1122.01700.011122.01.730.01161122副月貂偷懒的技术21122.01.S9BiI1122.01.98171122.C1创昭1刁造财务匚恶达人1122D1-呢2.制刁造财务匚1122.01.01181122/03具他1122.031122.03191122.03v(l2杂七杂八1122.03.0201122.M.-Q20201122朋.(12001销售部1122.03.020.011122.0G.-Q20.01211122.03.020.01.011122.03.020.01.0122nn1122.03-.Q20.01 .-02 团体1122.03.Q2Q.Q1.021122.03.02Q.Q1.Q2如果科目代码长度一致，或者代码和名字中有某个固定分隔符，则可使用分列”功 能来分离科目弋码和名称，如果没有，则需要使用下面的公式：取科目代码二LEFT(A2,2*LEN(A2)-LENB(A2)取科目名称二RIGHT(A2,LENB(A
