《会计报表函数公式.docx》由会员分享，可在线阅读，更多相关《会计报表函数公式.docx（29页珍藏版）》请在课桌文档上搜索。
1、会计实务会计报表函数公式1、文本与百分比连接公式如果直接连接，百分比会以数字显示，需要用Text函数 格式化后再连接用lookup函数可以划分账龄区间= LooKUP(D2,G$2:H$6)如果不用辅助区域，可以用常量数组= LoOKUP(D2,0J小于 30 天”;31J13 个月 ”;91,“36 个月”;181J6-1年”;36L”大于1年月E2二 L。KUP(D2,G$2:H$6)ABCD-* EFGH1客户借款日期金额借款天数区间范围区间2A2015-5-726,200639大于1年0小于30天3B2015-10-846,400485大于1年3113个月4A2015-12-1040,
2、800422大于1年9136 月5A2015-12-1134,600421大于1年1816-1年6B2016-3-1239,0003296-1年361大于1年7C2016-5-1474,4002666-1年8C2016-11-1528,8008113个月9A2016-12-1763,7004913个月10B2017-1-183,60017小于30天11D2017-2-146,4003小于30天123、屏蔽错误值公式把公式产生的错误值显示为空公式:C2=IFERROR(A2B2J,)说明：如果是错误值则显示为空，否则正常显示。4、完成率公式如下图所示，要求根据B的实际和C列的预算数，计算 完成率
3、。公示：E2=IF(C30zC2z-C2)ABCDE1项目本年累计去年同期累计同比增长1同比增长22E8560.00%60. 00%3A58-37.50%-37. 50%4B5-8-162.50%162. 50%5C-58-162.50%-162.50%6D-5-8-37.50%37.50%7E-8-560.00%-60. 00%896、金额大小写公式=TEXT(LEFT(RMB(A2),LEN(RMB(A2)-3)J0dbnu m2G通用格式元;0 dbnum2G/通用格史;元；0负dbnum2G/ 通由格式元；& TEXT (RIGHT (RME(A2),2), IdbnUm2耍。分整工小
4、写IA4c D IjZ12 112474.56大写4壹万怪仟肆佰柒拾肆元伍角陆分I567.多条件判断公式公式:C2=IF（AND（A2,zB3) + l) + 2z100)19 .多表同一位置汇总公式多个丁作表如果格式完全相同，可以用sum函数的多表 求和功能。如下图所示.尊求在汇总表里设置合计公式，汇总前19 个工作表B列的和。B2 = SUM(Sheetl:Sheetl9!B2)20、中国式排名公式公式:C12= SUMPRODUCT($D$4:$D$9 = D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)21.先进先出公式提醒：该公式屋貂高难度公式,不建议新丰停用和费力的
5、去理解，仅供excel中高水平用户参考使用。定义名称：Li = SUBToTAL(9QFFSET(!SES2 RoW()-1)-SUBTOT AL(9,OFFSET(!$B$2Z,/ROW(!$B$1:$B9)G3公式：USUMPRODUCT(IF(IiSB$3:B3,$B$3:B3Jj),IF(lj0,$C$3:C3)-SUM($G$2:G2)ABCDEFGHIJ1日期入库出库结余2数星单价金额数量单价金额数量单价32015-01-01428.0042.008.0042015-01-0210110.00141.2918.0052015-01-0332.006.00111.0912.00620
6、15-01-0441.255.0071.007.0072015-01-05891022文本、日期与百分比连接尊求：下面为FI期与文本进行连接。E2单元格输入公式 为(&表示连接)：=TEXT(A2, yyyy-mm-dd”)&B2&TEXT(C2,”0.00%”)E2 A =TEXT (A2, *yyyy-nM-dd*)&B2eLTEXT(C2, *0. 00%*)ABCDE1日期指标名称指标达成简单连接正确的连接22018/8/1同比达成56.00%43313同比0.562018-08-01 同比达成56.00%32018/8/2环比达成45.%43314环比 ).452018-08-02环
7、比 45.%42018/8/3销售达成82.00%43315销隹 ).822018-08-03睛隹 S82.00%52018/8/4利润达成32.00%43316利润达成0.322018-08-04利润达成32.00%62018/8/5CTS21.%43317预算进度0.212018-08-05Sg21.00%M Sheetl/Sheet2 J Sheet3 tJ/4 -iik-注：如果住用筒单的浑挎而不定义格式的话那么就像D 冽一样出现汶样的数字格式，日期与时间的本质是数值， 所以会出现这样的问题。23 IF条件判断例:在下面的题目中,如果性别为男则返回先生， 如果为女，则返回女士。1234
8、ABCDE序号部门姓名性别称呼1人资部男粒2财务部陈阳女处3市场部刘大民男粒f -F(D2=男，先生,女士)在E2单元格中输入公式=IF(D2 = 男/先生丁女士”)， 然后确定。说明：在Excel中引用文本的时候一定萝健用英文状态 下的半角双引号C以卜公式判断D2如果是男，则返回 先生，否则那一定就是女，返回女士。24合同到期计算计算合同到期是财务工作中一个最常见的用法。在D2单元格中输入公式：=EDATE(B2(2)然后确定。D2X fi=EDATE(B2,C2)ABCD1姓名日期逾期期限合同到期日期2弓KH2018/11/1622019/1/163李四2018/11/15362021/1
9、1/154Efi2018/11/1282019/7/125的2018/11/1122019/11/1注意:第二个参数一定是月份的数量，比如2年那么就 是24个月。25VLOoUP查找函数查找姓名对应的销售额。在F3单元格中输入公式二VLooKUP(E3,$A$2:$C$93。)，按 Enter 键完成。如 下图所ZF :F3f =VLoOKUP(E3,$A$2:$C $9,3,0)ABCDEF1姓名匿邕日期销瞥额2云秋2018/11/3020627姓名销售额3冯欢2018/12/181321尤明红748174尤明红2018/12/27481752018/12/3951846孙莎2018/12/
10、4766687卫傲文2018/12/5199308王文静2018/12/6814809施冰露2018/12/79793326条件求和例：求下面的1月的1组的数量总计.在E9单亓格中输 入公式:=SUMIFS(G2:G7,A2:A7,“1 月”,B2:B7,“1 组”)， 确定填充即可。E9个5 y f=SUMIFS(G2:G7,A2:A7J1 月，B2B7J1 组)ABCDEFG1月份组别姓名数量1数量2数量3t21月!组张三90977125832月2组李中89828225341月1组马七86768024251月1组杨成82719925262月2组赵四82929827273月3组9582992
11、7689计算1月1组的总计数量752注：以卜函数支持诵配符，同时对于条件要注意加上英 文状态下的半角单引号。27带有合格单元格的求和合并单元格的求和，一直是一个比较让新手头疼的问题。 诜中D2:D13单亓;格区域,然后在公式编辑栏里输入公 式：=SUM(C2:C13)-SUM(D3:D14),然后按 完成，如下图所示：D2f=SUM(C2:C13)-SUM(D3:D14)ABCDE1姓名小组提奖额酎2张成2513赵四第1组2956854陈小天1395李阳410r6马田第2组7352635.17凯文690.98约翰799.29宝强第3组907.51923.310田记1015.811王构1124.
12、1r 12赵约第4组1232.43697.213陈中1340.7注：一定叁注意第二个SUM函数的区域范围要错位， 不然就报错。28带有小计的单元格求和在表中带有小计呈许多领导的最寿的一个风格J日是对 干做表的人来说绝对一个星很难受的过程，那么带有小 计的单元格到底怎么样求和呢。在C9单元格里昂输i入公式：=SUM(C2:C8)/2按Enter 键完成。如下图所示：C9：Ix =SUM(C2:C8)/2ABCDE姓S小组提奖额提奖比例2皿第1组2512%3赵四第1组2952%4除小天第1组1392%5小计6856李阳第2组4105%7马田第2组7355%8小计11459 I合计1830注意：I文
13、里早自用了小计与求和的过程星重算计箕了上 面的数据.所以再除以2就可以得到不重复的结果，也 正是想要的结果。29VLOoKUP账龄分析在D2单元格中输入公式：=VLOOKUP(TODAY-B20.,0-30 天I3CT30-60 天 ”:6060-90 天”:9090 天以上)2 J),按 Enter 键后向 下填充。如下图所示：D2X = VLOOKUP(TODAY0-B2,0.0-30j30,30 60天60J60 90天-;90J90天以上)2,1)/ABCED1应收款日期金通IK龄分布2江SJ制造厂2018/7/1836190天以上3东风洗有Bl公司2018/12/1242340 3味
14、4Wt*洋保险有限公司2018/11/21603430-605IWJiSJiar2018/10/122262260 9块6百叫酒厂2018/4/12643490天以上7例铁建材投资有限公司2018/11/121419430-608美丽金融2018/12/12147780 3快9百年的2018/12/2338390-3俣10i4tiar2018/9/3511190天以上11LUiSiSjea2018/5/11883490天以上12ta2018/11/41326330-6 味132018/10/1113986090天Shet1 0-： 最后同上一个方法一样插入数据透视表即可。注：住用VLOOKUP
15、函数的最后一个参数为1时为模糊 查找的原理进行查询。结果。30多工作表求和下表中呈4个目的皿结统计.每个T作表的里面的张成的 位置都呈一样的,求张成的1-4月的提成统计。在F5单 元格中输入公式：= SUMCl月:4月!C2）按Enter键完成填充。如下图所示：F5f | SUMCl 月:4 月 YC2)VABC1姓名小组摞奖额2张成第1组2513赵四第2组2954陈小天第3组139求1-4月弓0W奖额5李阳第2组41010046马田第3组7357叔第4组690.9一 一SC6%,COIUMNS(DSN),)/ ASC WW EFG H I J K L M N O2付款凭证NO.OT23415
16、32243 4 QAHa2019 年月 20 日201 K 0M3 号借万科目金SI亿千万丽十万75干百+元分ItiXXX123.45D12345国温XXX901231319012313100如XXX124351243500合计9013S689.4S9013S6894SH玖仟物拾鲂伍仟.捌拾以秘角伍分1213 主 xtsdfOoOo “2M 9213123MBA U14在D6单元格中输入公式：=IF(JC6.LEFT(RIGHT(n ,&$C6/1%,COLUMNS(D:$N)二)按Enter键完成后，向右向下填充。33交叉杳找SI一44.E 3 3 3 5 1 4 8 4 5 4 5 1A
17、f- f4 f f6 F424233124CDE FGHI侑官日期俏售额员工螭号谓售额姓名2018/11/3020627*3245000尤明红2018/12/181321*52395184 文2018/12/250,4542419930卫做文2018/12/3951842018/12/4766682018/12/519930 I VLOOKUP($G2,$A$2:$D$9,MATCH(H$1,$A$1 :$D$1,0),0)-i2018/12/6800002018/12/797933B 一在H2单元格中输入公式：=VLOOKUP($G2.$A$2:$D$9,MATCH(H$1.$A$1:$D
18、$1,0),0),按Enter键完成后向下向右填充。注：一定叁锚定VLOOKUP函数的第一个参数的列号， MATCH函数的第一个参数的行号，这样才能得到正确 的结果。34屏蔽错误FERROR函数是屏蔽错误值的一个函数。E2- K UfX I =VLOOKUP(C2$3:$K$7,3,0)ABCDE1序号部门姓名性称呼21人资部张三男粒32财务部陈阳女女士43市场部刘大民男54新闻部吴有女#N/A65技术部张千女女士在E2单元格中输入的公式杳询的时候出现了一个错误， 此时根杷I文个公式屏蔽为空白，那么就可以在E2单元 格中输入公式：=IFERRoR(VLoe)KUP(C2,$I$3:$K$73。
19、),),确定后 向下填充。注意：该函数先判断第一参数桌否为错误值,如果为错 误值则: 反向为宗义的第二个参数，如果不是错误值，那 么继续地返回其本身。35四舍五入函数E2A =ROUHD (D2, 2) -AC11EFG1序号姓名品牌销售额结果1(保A位效为2)结果2(保席位数为0)结果3 (保.位数为-2)215K=海尔-123.45561-123.46I-123-10032李中三星4578.123564578. 1245784600435美的0.2125640.2100功能：将某个数字四舍五入为指定的位数语法:ROUND(number, num_digits)在E2、单元格中分别输入公式：
20、=RoUND(D2,2),在F2单元格中分别输入公式：=ROUND(D2,0),在G2单元格中分别输入公式：=ROUND(D2,-2)注：如果num diits大干0 (零)，则恪数字四金五 入至I脂镇的小数位：如果num diaits等干0 ,则将教 字四舍而入至Il最挎沂的整数：如果num.digits小于0 , 则在小数点左侧进行四舍五入。一.员工信息表公式姓名身份证号I蛭出生年月遢休日期籍贯丁状220223196903223411男1969-03-22482029/03/22 星期四吉林皙吉林市磐石县牍412823198005251216S1980-05-25372040/05/25
21、SffiE可南省驻马店地区遂平Z龚丽丽430225198003113537另1980-03-11372040/03/11 星期日湖南重株洲市炎隋县除美华r430225198511163527女1985-11-16312040/11/16 星期五湖南省株洲市炎陵县汪志魁r430225198008123527女1980-08-12362035/08/12 星期日湖南省株洲市炎陵县李丽君r320325197001017121女1970-01-01472025/01/01 星期三防招得陈美丽r4201171973021753711973-02-17442033/02/17 星期四湖北有武汉市新洲区F1
22、32801194705058361女1947-05-05702002/05/05 早期日河北肯施坊地区霖坊市段月r430225198001153597男1980-01-1572040/01/15 SXB湖南省株洲市炎陵县刘韵320325198001018124女1980-01-01372035/01/01肪官蟀节至眼1 .计算性别(F列)=IF(MoD(MlD(E3,17,1),2)男 7 女”)2 .出生年月(G列)=TEXT(MID(E3,7,8)JO-OO-O(T)3 .年龄公式(H列)= DATEDlF(G3,TODAY()/)4、退休日期(I列)=TEXT(EDATE(G3,12*(
23、5*(F3=”男) + 55)yyyymmdd aaaa)5、籍贯(M列)=VLooKUP(LEFT(E3,6)*L 地址库!E:F2)注：附带示例中有地址库代码表6 .社会工龄(T列)= DATEDlF(S3,NOW()y)7 .公司工龄(W列)= DATEDIF(V3,NoWn年,8iDATEDIF(V3.NOW.,vmn)8t,H“&DATEDIF(V3,N0W()JmCr)&天8 .合同续签日期(Y列)= DATE(YEAR(V3) +LEFTB(X3,2),MoNTH(V3),DAY(V3) )-19 .合同到期日期(Z列)=TEXT(EDATE(V3LEFTB(X3.2)*12)-
24、TODAY(),T0过期O天;30即将到期O天;还早“)10 .工龄工资(AA列)= MIN(700zDATEDIF($V3,NOW()zy)*50)11 .生肖(AB列)= MIDC1海鸡泊1猪鼠牛虎兔龙蛇马羊 n,MOD(MID(E3Jz4)zl2) + lll)二.员工考勤表公式DSTUVVXYZAAABL?姓含工作时冏入司时间I公司工龄合同谈筌工龄工SJ丁状2012-9-2242012-8-7碑11月2味1(W2022-8-6还早200燥20154222H2013-9-153年1。月1沃2年2015-9-14过期681天15095331992-9-8245H2005-8-311年11月24天3年2008-8 2CW32805509i陈美华1995-12-27212012-8-30族10月27天株2016-8-29E331 天200牛f1993-8-9232009-4-19阳月吃阵2014-4-18过期119S天400震李的君1992-3-172S2010-6-117年1月16天2年2012-6-10过期187沃3S0我)陈美丽2
