EXCEL在会计中应用公式及几个常用函数。

邢台现代职业学校薛中年（工业学校）《Excel在会计中的应用》—函数的使用首页引入案例—如何进行工资处理？引入案例—工资计算公式本讲内容一、EXCEL公式的构成与单元格的引用二、EXCEL函数的构成三、EXCEL函数的使用四、SUM、AND、IF、SUMIF四个常用函数五、公式与函数使用实例—工资处理一、Excel公式的构成1、公式必须以“=”等号开始2、公式是由单元格引用、运算符、数值、字符、函数等组成的能进行计算的式子3、公式中使用的均为英文状态下的符号如：+非＋*非×,非，()非（）非“”举例：C6公式“=C3+C4+C5-2+sum(b3:b6)”B6公式“=sum(B2:B5)”一、Excel公式的构成1.单元格引用是指公式中输入单元格地址时，表示该单元格中的内容参加运算。当其中的数据发生变化时，公式则自动重新计算。2.运算符有算术运算符（+-*/%^()）、字符运算符（&表示连接合并）、比较运算符（===）三类。3.函数函数式一个预先定义好的特定计算公式，对一个或多个参数进行计算并得出一个或多个计算结果，此结果叫做函数值。一、Excel公式的构成—举例：K3公式：=P3*50J3公式：=SUM(E3:G3)-H3A5公式：=10^2结果：1001.算术运算如果A2=10，则公式“=A220”的返回值为FALSE,而公式“=A220”的返回值为TRUE3.比较运算公式：=河北省&邢台市结果：河北省邢台市如果B2中有字符串工资，C2=计算表则公式：=B2&的&C2结果为？2.字符运算一、Excel公式—单元格的引用公式中单元格或单元格区域的引用相对于包含公式的单元格的相对位置例：在C1单元格公式=A1+B1，把公式复制到C2单元格公式将自动变为=A2+B2，因为C2相对与C1只是行号增加了1，单元公式也这样变化相对引用绝对引用引用单元格的行标和列标前都加上$符号，相对引用就变为绝对引用。当复制公式时希望引用位置不变，那么可以使用绝对引用。例：在C1单元格公式=$A$1+$B$1，把公式复制到C2单元格公式将仍为=$A$1+$B$1，因为公式单元引用为绝对引用，复制不会变化。混合引用只在引用单元格的行标或列标前加$符号，即为混合引用。当复制公式时，相对引用部分随公式位置的变而变化，绝对引用部分不随公式位置的变化而变化。如：在C1单元格公式=$A1+B$1，把公式复制到D2单元格公式将自动变为=$A2+C$1(较难理解）小技巧：在编辑公式时选中单元格地址，按F4功能键，可改变单元格引用类型如：$J$15→J$15→$J15→J15→$J$15二、Excel函数的构成1、函数由函数名及参数构成，其表达式如下：函数名（参数1，参数2，参数3，…）2、EXCEL提供了十类，数百个函数，用户可在公式中使用函数有财务函数、日期与时间、数学与三角、统计、查找与引用、数据库、文本、逻辑、信息、工程函数共十类，还可自定义函数函数举例：=SUM(C2:C11,10,20)表示对C2:C11中的数值以及10、20求和二、Excel函数—参数的约定函数参数约定1、函数参数要用()括起来，且()前后都不能有空格2、函数中参数多于一个时，必须用英文，号隔开3,、参数除引用单元格位置外，还可以是数、文本、逻辑值、数值、公式、或其它函数等4、给定的参数必须能产生一个有效值举例：=SUM(C2:C11,10,A1)三、Excel函数的使用1、函数单独使用单击粘贴函数按钮fx，用函数向导输入单一函数。2、公式中引用函数可将函数放在其他函数或公式中使用，形成带函数的公式。三、Excel函数的使用—输入函数1、函数向导按钮fx2、公式编辑框直接输入四、常用的四个函数格式：SUM(Number1,Number2,…)功能：将指定为参数的所有数字相加SUM求和函数格式：IF(logical_test,[value_if_true],[value_if_false])功能：当指定条件的计算结果为TRUE，将返回某个值；否则返回另一个值。IF判断函数格式：SUMIF(range,criteria,[sum_range])功能：可以对区域中符合指定条件的值求和。SUMIF判断求和格式：AND(logical1,logical2,...)功能：所有参数的逻辑值为真时，返回TRUE；否则返回FALSE。AND逻辑函数1、Sum求和函数语法：格式：SUM(number1,[number2],...])例如：=SUM(F4:I4)-J4参数：1、number1必需。想要相加的第一个数值参数。2、number2,,...可选。想要相加的2到255个数值参数。说明：1、如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。2、如果任意参数为错误值或为不能转换为数字的文本，Excel将会显示错误。1、Sum求和函数—输入1、Sum求和函数—举例1、=SUM(A2:A4)表示将a2:a4区域各数相加5、=sum(A2,A3,A4)表示将A2、A3、A4三个单元格中数相加4、=SUM(A2:A4,15)表示将A2:A4区域各数与15相加3、=SUM(3，2)表示将3和2相加2、=SUM(G:G)表示将G列的所有数相加2、AND逻辑与函数语法：格式：AND(logical1,logical2,...)例如：AND(P41500,P4=4500)参数：Logical1,logical2,...表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。说明：1、参数必须是逻辑值TRUE或FALSE,或者包含逻辑值的数组或引用。2、如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。3、如果指定的单元格区域内包括非逻辑值，则AND将返回错误值#VALUE!。3、IF判断函数语法：格式：IF(logical_test,[value_if_true],[value_if_false])IF(逻辑条件,[条件为真时的值],[为假时的值])举例：A1=8000，则=IF(A110000,10%,15%)返回10%参数：1、logical_test必需。计算结果可能为TRUE或FALSE的任意值或表达式。可使用任何比较运算符。2、value_if_true可选。logical_test参数的计算结果为TRUE时所要返回的值。3、value_if_false可选。logical_test参数的计算结果为FALSE时所要返回的值。说明：最多可以使用64个IF函数作为value_if_true和value_if_false参数进行嵌套，以构造更详尽的测试。小技巧：在公式编辑框中按atl+回车可输入换行符4、SUMIF条件求和函数语法：格式：SUMIF(range,criteria,[sum_range])例如：=SUMIF(B2:B5,俊元,C2:C5)参数：1、range必需。用于条件计算的单元格区域。2、criteria必需。用于确定对哪些单元格求和的条件，例如，条件可以表示为32、“32”、B5、32、“苹果”。3、sum_range可选。要求和的实际单元格。如果省略，Excel会对在范围参数中指定的单元格求和。说明：可以在criteria参数中使用通配符（包括问号?和星号*）。问号匹配任意单个字符；星号匹配任意一串字符。如：“1???”“1002*”五、本讲实例：工资表五、本讲实例：工资计算公式五、本讲实例：工资计算J4公式=Q4*50K4公式=SUM(F4:I4)-J4P4公式=IF((K4-L4)3500,K4-L4-3500,0)五、工资计算(公式举例找问题？）J4公式：=5*50=Q4×50K4公式：SUM（F4,G4，H4，I4)-J4五、工资计算—代扣税公式=IF(P4=1500,P4*3%,IF(P4=4500,P4*10%-105,IF(P4=9000,P4*20%-555,IF(P4=35000,P4*25%-1005,IF(P4=55000,P4*30%-2755,IF(P4=80000,P4*35%-5505,P4*45%-13505))))))是否应改为?IF(AND(P4=4500,P41500),P4*10%-105,五、知识拓展—自定义函数五、应发工资按部门汇总（SUMIF函数)五、借助Excel帮助文件总结：正确输入EXCEL公式公式必须以=开始。不管是单纯的公式还是更高级的函数使用，都需要以=为开始标记，否则，所有的公式只是字符，而不能完成计算功能1、以=开始正确使用函数。所有公式中的函数都是以英文小括号()成对出现，不能少了左边或者右边，使用函数时可以自己输入也可使用插入函数的方法，但不可拼写错误2、正确使用函数公式以键盘enter键的输入结束。以=开始，以enter键输入结束是公式最基本的要求，千万不能在公式输入完毕后没有按enter键的情况下点击鼠标，公式将会遭到破坏3、以回车键结束课后作业1、独立上机完成训练7：定义工资结算计算公式，写出实训小结2、完成“工资费用分配表”各单元格计算公式的定义，写出小结3、学习委员将本次课教学屏幕录像拷贝到MP4上供同学参考谢谢聆听！欢迎联系我薛中年电话：[phone]结束