EXCEL在会计中的应用第04章.ppt

《EXCEL在会计中的应用第04章.ppt》由会员分享，可在线阅读，更多相关《EXCEL在会计中的应用第04章.ppt（40页珍藏版）》请在三一办公上搜索。

1、【本章知识点】,函数的功能与类型函数的输入与调用常用函数及其应用主要数学函数应用逻辑判断函数应用日期时间函数应用文本函数及其应用,本章目录,4.1函数的基本知识4.2常用函数4.3数学函数4.4逻辑判断函数4.5日期和时间函数4.6文本函数小结习题,4.1函数的基本知识,4.1.1函数的功能与类型划分函数具有比公式更加强大的数据处理功能，具体功能如下：（1）使用函数可以大大简化公式。（2）使用函数可以实现用公式无法进行的某些运算。（3）使用函数可以减少手工编辑，提高编辑速度。（4）使用函数可以实现判断功能，进行有条件的运算。在Excel中，有300多个内部函数，它们涉及到许多应用领域，例如财务

2、、工程、统计、数据库、时间处理、数学运算等，可分为数学与三角函数、日期与时间函数、文本函数、逻辑函数、信息函数、统计函数、财务函数、数据库函数、查找与引用函数和工程函数等10大类。,4.1.2函数的结构,在Excel中，函数结构一般由三部分构成，分别是函数名、括号和参数表。如下所示：函数名（参数1，参数2，参数3，）（1）函数名：确定了函数的功能和运算规则，在形式上它一般采用大写字母，但是在具体使用过程中，用户也可以采用小写形式，系统会自动将其转换为大写字母的状态。（2）括号：在Excel函数中，圆括号“()”不能省略，必须紧跟在函数名称后面，里面用来书写参数。（3）参数表：参数表规定了函数的

3、运算对象、顺序或结构等。,4.1.3函数的调用,函数的调用就是在公式或者表达式中应用函数，包括如下三种形式。（）在公式中直接调用函数。如果函数以公式的形式出现，只需要在函数前面加上等号“=”即可，如：“=SUM(A1:A100)”。（）在表达式中调用函数。如果函数作为表达式的组成部分来使用，则需要在公式的相应位置输入函数，如：“C9+SUM(A1:A100)”，此时不用在函数前面加“=”。（）函数的嵌套调用。函数也可以嵌套调用，即一个函数作为另一个函数的参数，如“=IF(RIGHT(A2,1)=1,男,女)”。其中公式中的IF函数中嵌套了RIGHT函数，而且将RIGHT函数返回的结果作为I函数

4、的逻辑判断依据。对于函数的嵌套调用，作为参数的函数前面不能添加“=”，如上面公式中内部的RIGHT函数前面就不能添加“=”。,4.1.4函数的输入,在Excel的公式和表达式中调用函数，第一个要解决的问题就是函数的输入。按照函数的来源，Excel函数可以分为内置函数、扩展函数和自定义函数三类。对于内置函数，只要启动Excel，用户就可以使用它们；而对于扩展函数，必须通过单击“工具”/“加载宏”菜单命令加载，然后才能像内置函数那样使用；而自定义函数，需要用户先通自动录制宏或者手工编写VBA代码的方法，先定义好函数，然后就能像内置函数一样进行调用。函数输入有借助函数向导输入和手工直接输入两种方法。

5、1借助函数向导输入2手工直接输入函数,4.1.5使用Excel帮助理解函数,多数情况下，用户对一些不熟悉的函数常常采用插入函数的方法，其实在使用“插入函数”对话框或“函数参数”对话框的时候，用户都可以通过单击对话框下方的“有关该函数的帮助”超链接或按【F1】键，进入Excel的帮助系统，以便得到该函数的帮助。说明：Excel的帮助系统具有极其强大的功能，它比许多参考书内容更全面，讲述更清楚，实例更丰富。用户利用它能够解决在使用Excel过程中所遇到的各种问题，如Excel的新技术、疑难问题解答、专用名词术语、函数说明、函数应用实例等。一个Excel的真正用户应该会运用帮助系统，从中获取有用的资

6、料，培养自学能力，养成“终身学习”习惯。,4.2常用函数,4.2.1自动求和按钮下对应的常用函数求和是最为常用的运算，所以Excel以前一直将求和做成了一个命令按钮。而从Excel 2003开始，Excel自动求和按钮又提供了一个下拉菜单，其中包括了求和、求平均值、计数、求最大值、求最小值等常用函数以及可以用来打开“插入函数”的“其他函数（F）”超级链接。1.SUM函数2.AVERAGE函数3.COUNT函数4MAX函数和MIN函数,4.2.2其他常用计数函数介绍,1COUNTA函数COUNTA返回参数列表中非空值单元格的个数。2COUNTBLANK函数COUNTBLANK返回指定参数中空白单

7、元格的个数。3COUNTIF函数COUNTIF函数的功能是计算指定参数中满足给定条件的单元格的个数，语法格式为：COUNTIF(range,criteria)其中，参数Range为需要计算其中满足条件的单元格数目的单元格区域；Criteria是指定的条件，其形式可以为数字、表达式或文本。如果是文本或表达式，则Criteria参数必须加上半角状态下的双引号。,4.2.3常用函数应用实例：学生成绩处理,4.3数学函数,4.3.1 常用数学函数的功能介绍ABS 返回数字的绝对值INT 将数字向下舍入到最接近的整数TRUNC 将数字的小数部分截去，返回整数ROUND 按指定位数四舍五入某个数字SIGN

8、 返回数字符号。PRODUCT将参数数字相乘，返回乘积值MOD返回两数相除的余数，其结果符号与除数相同SQRT返回某一正数算术平方根FACT返回数的阶乘POWER返回给定数字的乘幂（与“”运算符相同）RAND 返回一个大于等于0 小于1 的随机数RANDBETWEEN产生位于两个指定数值之间的一个随机数,4.3.2数学函数应用实例一：利用随机函数快速获取虚拟数据,4.3.3数学函数应用实例二：按实发工资总额计算每种面额钞票的张数,4.4逻辑判断函数,4.4.1逻辑函数的功能与用法介绍EXCEL 中的逻辑函数共有6个，分别是IF、AND、OR、NOT、TRUE和FALSE，另外，IS类函数作为信

9、息判断的函数，也有逻辑判断的功能。1IF函数IF函数也称为条件函数，其功能是对指定的条件判断真假，根据参数的真假值，返回不同结果。数据处理中，经常利用IF函数对公式和数值进行条件检测。其语法格式如下：IF(logical_test,value_if_true,value_if_false)其中，参数logical_test是一个条件表达式，可以是比较表达式和逻辑判断表达式，其结果返回值为TRUE和FALSE；Value_if_true表示logical_test为TRUE时的显示内容；Value_if_false表示当logical_test为FALSE时的显示内容。,4.4.1逻辑函数功能与

10、用法介绍,2 AND、OR、NOT函数Excel不同于其它一些计算机编程语言，它没有AND、OR、NOT等逻辑运算符，也就是不能进行“A AND B”、“英语85”OR“数学85”、“NOT 超出计划”等运算，其逻辑运算只能由逻辑函数AND、OR、NOT来实现。这三个函数的语法格式分别为：AND(logical1,logical2,)OR(logical1,logical2,)NOT(logical)其中，AND和OR函数中的参数Logical1,logical2,表示待测试的条件值或表达式，其结果可为 TRUE 或 FALSE，最多不超过30个。当使用AND函数时，只有当运算对象都为TRUE

11、的情况下，运算结果才为TRUE，否则为FALSE；当执行OR运算时，只有当运算对象都为FALSE的情况下，运算结果才为FALSE，否则为TRUE。NOT函数中的Logical为可以计算出 TRUE 或 FALSE 的逻辑值或逻辑表达式。NOT函数的功能是对该表达式运算结果取相反值。,4.4.1逻辑函数功能与用法介绍,3.TRUE和FALSE函数TRUE函数和FALSE函数都是无参函数，其结果分别是返回逻辑值“TRUE”和“FALSE”。其实，可以直接在单元格或公式中键入“TRUE”或“FALSE”值，而不使用此这两个函数。引入这两个函数主要目的是用于与其它电子表格程序兼容。4.IS类函数IS类

12、函数属于信息判断函数，其功能是检验数值的类型，并且根据参数的值返回TRUE或FALSE，从而也可以起到一定的逻辑判断和条件检测功能。IS类函数总共包括用来检验数值或引用类型的九个工作表函数,分别为：ISBLANK函数、ISERR函数、ISERROR函数、ISLOGICAL函数、ISNA函数、ISNONTEXT函数、ISNUMBER函数、ISREF函数和ISTEXT函数，它们具有以下相同的语法格式：IS函数名(value)其中，IS函数名代表任一IS类函数名称，value是需要进行检验的参数，可以是“”(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。,4.4.2

13、逻辑函数应用实例一：销售提成计算,问题描述：如图4-13所示为某公司销售部5月份各地办事处的销售额及其销售费用情况，请根据右边的奖金提成政策判别各个办事处的奖金提成比例，并计算奖金提成金额。图4-13 销售提成数据与政策表格,4.4.3 逻辑函数应用实例二：考试成绩评判,4.4.4 逻辑函数应用实例三：制作跳水比赛计分表模板,4.5日期和时间函数,4.5.1 常用日期和时间函数介绍1.TODAY函数TODAY函数的功能是返回当前日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果为日期格式。其语法格式为：TODAY（）。示例：在任意一个单元格中输入公式“=TODAY（）”，都得到电脑

14、系统的当前日期。说明：TODAY函数是一个无参函数，但是在应用时候后面的左右括号不能漏掉。2.YEAR函数YEAR函数的功能是返回某日期对应的年份，返回值为 1900 到 9999 之间的整数。其语法格式为：YEAR(serial_number)。其中的参数Serial_number为一个日期值，其中包含要查找年份的日期。示例：公式“=YEAR(“2008/5/15”)”返回2008。,4.5.1 常用日期和时间函数介绍,3.MONTH函数MONTH函数的功能是返回指定日期中的月份，返回值是介于 1到 12之间的整数。其语法格式为：MONTH(serial_number)，参数同YEAR函数中

15、的参数意义一样。示例：公式“=MONTH(2008/5/15)”返回5。4.DAY函数DAY函数的功能是返回指定日期的天数，返回值用整数 1 到 31 表示。其语法格式为：DAY(serial_number)，参数同YEAR函数中的参数意义一样。示例：公式“=DAY(2008/5/15)”返回15。,4.5.1 常用日期和时间函数介绍,5.DATE函数DATE函数返回代表特定日期的序列号。其语法格式为：DATE(year,month,day)其中的三个参数中：Year为年份，也可以为年份的序列号，用一到四位数字表示。Month为月份，如果所输入的月份大于12，将从指定年份下一年的一月份开始往上

16、加算。如：“DATE(2008,14,2)”返回代表 2009年2月2日的序列号。Day代表在该月份中的天数，如果 day 大于该月份的最大天数，则将从指定月份的下一月的第一天开始往上累加。如：“DATE(2008,5,50)”将返回2008 年6月19日。示例：在有些数据库中，日期的年、月、日数字可能分为三个字段来保存，当导入到Excel工作表后，就可以利用DATE函数将年、月、日合并成一个具体的日期数据。假设单元格A1是年份数字，B1是月份数字，C1是日数，则公式“=DATE（A1，B1，C1）”就将A1的年份数字，B1的月份数字和C1的日数，合并到一起组成了一个具体的日期数据。说明：上面

17、实例中，读者可能会想到利用连接符“&”将三个数字连接起来,利用公式“=A1&-&B1&-&C1”,单是这种方式得到的结果不是一个时间序列号，而是文本。,4.5.1 常用日期和时间函数介绍,6.WEEKDAY函数WEEKDAY函数的功能是返回某日期为星期几，默认情况下，其值为 1（星期天）到 7（星期六）之间的整数。其语法格式为：WEEKDAY(serial_number，return_type)其中参数Serial_number为日期序列号,可以是日期数据或是对日期数据单元格的引用。参数Return_type用来确定返回值类型的数字，具体如下表4-3所示：表4-3 参数Return_type含

18、义说明参数Serial_number的值返回结果数字及其含义说明1或省略返回数字 1到7，其中：1表示星期日，2表示星期一，7表示星期六2返回数字 1到7，其中：1表示星期一，2表示星期二，7表示星期日3返回数字 0到6，其中：0表示星期一，1表示星期二，6表示星期日示例：2008年5月15日是星期四，则：“=WEEKDAY(2008/5/15)”返回“5”；“=WEEKDAY(2008/5/15,2)”返回“4”；“=WEEKDAY(2008/5/15,3)”返回“3”。,4.5.1 常用日期和时间函数介绍,7.NETWORKDAYS函数NETWORKDAYS函数的功能是返回两个日期之间完整

19、的工作日数值(不包括周末和专门指定的假期)。其语法格式为：NETWORKDAYS(start_date，end_date，holidays)其中的参数：Start_date表示开始日期；End_date表示终止日期；holidays为节假日，可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。示例：公式“=NETWORKDAYS(2008/5/1,2008/5/10,2008/5/1)”返回6。8.NOW函数NOW函数的功能是返回当前日期和时间的序列号。如果在输入函数前，单元格的格式为“常规”，则结果为日期格式。其语法格式为：NOW（）。示例：在任意一个单元格中输入公式“=

20、NOW（）”，都得到电脑系统的当前时间。说明：NOW函数同TODAY函数一样，也是一个无参函数，但应用时后面括号不能漏掉。9.TIME函数TIME函数的功能是返回返回某一特定时间的小数值,小数值为 0（零）到 0.99999999 之间的数值，代表从 0:00:00(12:00:00 AM)到 23:59:59(11:59:59 PM)之间的时间。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。其语法格式为：TIME（hour,minute,second）,4.5.2 日期和时间函数应用实例一：生日提醒,4.5.3 日期和时间函数应用实例二：会议日程安排,4.5.4 日期和时间

21、函数应用实例三：加班工资计算,4.5.5日期和时间函数应用实例四：制作万年历,4.6文本函数,4.6.1 常用文本函数介绍1TEXT函数TEXT函数的功能是将数值转换为按指定数字格式表示的文本。其语法格式为：TEXT（value,format_text）其中包括两个参数，意义分别为：Value为数值、计算结果是数值的公式或对数值单元格的引用；format_text为所要选用的文本型数字格式，即“单元格格式”对话框“数字”选项卡的“分类”列表框中显示的文本形式的数字格式，它不能包含星号“*”。示例：如果工作表中A1单元格的数值为2986.638，则公式“=TEXT(A5,#，#0.00)”返回字

22、符串“2，986.64”。说明：使用“单元格格式”对话框的“数字”选项卡设置单元格格式，只会改变单元格的格式而不会影响其中的数值。而使用TEXT函数可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。,4.6.1 常用文本函数介绍,2VALUE函数VALUE函数的功能是将代表数字的文本字符串转换成数字。其语法格式为：VALUE（text）其中参数text为带引号的文本，或对需要进行文本转换的单元格的引用，还可以是Excel 中可识别的日期或时间格式。如果text不是上述格式，则VALUE函数返回错误值“#VALUE!”。示例：公式“=VALUE(￥1,000)”返回1000。3LOW

23、ER函数LOWER功能是将一个文本字符串中的所有大写字母转换为小写字母。其语法格式为：LOWER（text）其中参数text是要转换为小写字母的文本，它可以是引用或文字串。示例：如果A1中有字符串“EXCEL”，则公式“=LOWER(A1)”返回字符串“excel”。说明：LOWER函数不改变文本中的非字母字符。与LOWER函数类似，Excel还有UPPER函数和PROPER函数，其中：UPPER函数将一个文本字符串中所有小写字母转换为大写字母的，而PROPER函数将文本字符串的首字母和任何非字母字符之后的首字母转换成大写、其余字母转换成小写，它们的语法格式和LOWER函数相同。,4.6.1

24、常用文本函数介绍,4TRIM函数TRIM函数清除文本中的所有空格（除了单词之间的单个空格外）。其语法格式为：TRIM(text)其中参数text是需要清除其中空格的文本。示例：如果工作表A1单元格的数据为字符串“First Quarter Earnings”，则公式“=TRIM(A1)”返回字符串“First Quarter Earnings”。5LEN函数LEN函数的功能是返回文本字符串中的字符数。其语法格式为：LEN(text)其中参数text为要查找其长度的文本，空格也将作为字符进行计数。示例：如果A1单元格中字符串为“电脑 爱好者”，则公式“=LEN(A1)”返回6。6CONCATEN

25、ATE函数CONCATENATE函数的功能是将若干字符串合并成一个字符串，其功能与文本连接运算符“&”相同。其语法格式为：CONCATENATE(text1,text2,)其中的参数text1,text2,为1到30个将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。示例：公式“=CONCATENATE(97,千米)”返回“97千米”。,4.6.1 常用文本函数介绍,7LEFT函数LEFT函数的功能是根据所指定的字符数从文本字符串左边取出第一个或前几个字符。也就是说，LEFT函数对字符串进行“左截取”，其语法格式为：LEFT（text,num_chars）其中参数T

26、ext是要提取字符的文本字符串；Num_chars是指定提取的字符个数，它必须大于或等于0。如果省略参数 num_chars，默认值为 1。如果参数 num_chars 大于文本长度，则LEFT函数返回所有文本。示例：公式“=LEFT(“电脑爱好者”,2)”返回字符串“电脑”。8MID函数MID函数的功能是返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。其语法格式为：MID(text,start_num,num_chars)其中参数text为要提取字符的文本字符串；start_num为要提取的第一个字符的位置（文本中第一个字符的位置为 1，其余依此类推）；num_chars为

27、指定提取的字符个数。示例：公式“=MID(“电脑爱好者”,3,2)”返回字符串“爱好”。,4.6.1 常用文本函数介绍,9REPLACE函数REPLACE函数的功能是根据所指定的字符数，使用其它文本字符串替换当前文本字符串中的部分文本。其语法格式为：REPLACE(old_text,start_num,num_chars,new_text)其中参数Old_text为要替换其部分字符的文本；Start_num为要用new_text替换的old_text中字符的位置；Num_chars是用new_text替换old_text中字符的个数；New_text是用于替换old_text中字符的文本。示例

28、：如果工作表中A1单元格的数据为字符串“电脑爱好者”、A2单元格的数据为字符串“计算机”，则公式“=REPLACE(A1,1,2,A2)”返回字符串“计算机爱好者”。,4.6.1 常用文本函数介绍,10SUBSTITUTE函数SUBSTITUTE函数在文本字符串中用 new_text代替old_text。其语法格式为：SUBSTITUTE(text,old_text,new_text,instance_num)其中参数Text为需要替换其中字符的文本，或对含有文本的单元格的引用；Old_text是需要替换的旧文本；New_text是用于替换old_text的文本；Instance_num为一数

29、值，用来指定以new_text替换第几次出现的old_text，如果指定了instance_num，则只有满足要求的old_text被替换，否则将用new_text替换Text中出现的所有old_text。示例：如果工作表中A1单元格的数据为字符串“电脑爱好者”、A2单元格的数据为字符串“计算机”，则公式“SUBSTITUTE(A1,电脑,A2)”返回字符串“计算机爱好者”。说明：如果需要在某一文本字符串中替换指定的文本，请使用函数SUBSTITUTE函数；如果需要在某一文本字符串中替换指定位置处的任意文本，请使用REPLACE函数。,4.6.2 文本函数应用实例一：通讯地址拆分,4.6.3

30、文本函数应用实例二：电话号码升位,4.6.4 文本函数应用实例三：根据身份证获取个人信息,小 结,Excel具有强大的函数库，熟练使用这些函数，可以方便、快捷地对数据进行各种处理，进而高效地解决日常管理工作中的一些数据运算问题。在使用Excel函数时要注意函数的书写形式、输入方式和调用方法，对于每个函数，除了要掌握其功能以外，还要熟悉其参数数量、意义和顺序，以及函数返回值的类型。本章主要讲述了工作表常用函数的使用，以及主要的数学函数、逻辑函数、日期与时间函数、文本函数的语法格式与使用方法，而对于其他一些具有特殊用途的统计函数、数据库函数、查找与引用函数、财务运算函数等，将在后续章节需要时进行介绍。总之，通过本章学习，读者应能正确、熟练、高效地选择函数进行相关数据运算。,习 题,1Excel 函数与公式的区别是什么，函数的组成部分都有哪些？2使用Excel函数时的注意事项有哪些？3Excel 中，函数的调用方式有哪些？分别如何进行操作？4请说明如何在单元格区域A1：J10产生100个100010000之间的随机数？5上机自行将本章的所有实例操作一遍。69 上机练习习题 具体要求详见教材和本书素材,