EXCEL函数应用教学课件ppt.ppt

《EXCEL函数应用教学课件ppt.ppt》由会员分享，可在线阅读，更多相关《EXCEL函数应用教学课件ppt.ppt（38页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益EXCEL函数应用课程大纲u课堂讲解篇:函数功能说明与案例讲解(共18个)为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益EXCEL函数教学目的通过功能说明与案例演示,了解函数的应用范围与功效,激发后续学习函数的兴趣掌握函数使用的方法与技巧充分发挥Excel的强大数据处理功能，提升工作效率 为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益EXCE

2、L函数的结构Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式特殊公式。Excel 函数结构:也有一些函数是没有参数的,如ROW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益函数参数常用符号或表示方法n函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入 东南汽车或“东南汽车”n连接符:&如：东南&汽车的值为 东南汽车n空值/空格的表示法:n 空值：n 空格：n相关数学符号:（不等于）;=（大于等于）;=3)；countif(I:I

3、,=3)-countif(I:I,=5)为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益COLUMN用途：返回给定引用的列标。语法：COLUMN(单元格引用)。参数：“单元格引用”为需要得到其列标的单元格，如果省略，则假定函数COLUMN 是对所在单元格的引用。实例：公式“=COLUMN(A3)”返回1，=COLUMN(D5)返回?。答案：答案：4为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Row用途：返回给定引用的行号。语法：ROW(单元格引用)。“单元

4、格引用”为需要得到其行号的单元格或单元格区域。实例:利用row建立序号:Go为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益MOD：取余用途：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(被除数，除数)参数：除数不能为零。为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Mod函数的案例n实例1：“=MOD(-5，-2)”返回？。n实例2:如何利用MOD函数，将整张EXCEL表的偶数行都标上底色 Go答案：答案：-1ROW()Mod(ROW()+1

5、,2)=1为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Vlookup在表格的在表格的首列首列查找指定的值查找指定的值,确定待检索单元格所在的区域中确定待检索单元格所在的区域中的行序号的行序号,并由此返回表格当前行中选定列的值。并由此返回表格当前行中选定列的值。语法：语法：VLOOKUP(VLOOKUP(索引值，数据区域，列序号，查找方式索引值，数据区域，列序号，查找方式)参数：参数：索引值索引值为需要在数据表第一列中查找的数值，它可以是数值、为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。引用或文字串。数据区

6、域数据区域为需要在其中查找数据的数据表，可以使用对区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。或区域名称的引用。列序号列序号 指数据区域指数据区域 中待返回的匹配值的列序号。它等于中待返回的匹配值的列序号。它等于1 1 时，返回数据区域时，返回数据区域 第一列中的数值；它等于第一列中的数值；它等于 2 2时，返回数据时，返回数据区域第二列中的数值，以此类推。区域第二列中的数值，以此类推。查找方式查找方式为一逻辑值为一逻辑值,指明函数指明函数VLOOKUP VLOOKUP 返回时是精确匹配返回时是精确匹配还是近似匹配。（可省略）还是近似匹配。（可省略）如果为如果为1 1或省

7、略，则返回近似匹配值或省略，则返回近似匹配值如果为如果为0 0，函数，函数VLOOKUP VLOOKUP 将返回精确匹配值。将返回精确匹配值。如果找不到，则返回错误值如果找不到，则返回错误值#N/A#N/A。为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益VLOOKUP函数的语法：为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Vlookup案例案例一:给定如下数据（如图所示），请设计下拉菜单，实现查询。首先为图2中D4单元格设置“数据”/”有效性”，数据源为

8、“序列”，区域为A4：A7。然后在E4输入公式=VLOOKUP(D4,A4:B7,2,)Go为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益match用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置(不是具体的单元格)。语法：MATCH(A，B，C)。参数：A:为需要在数据表中查找的数值或单元格引用。B:是可能包含所要查找的数值的连续单元格区域。C:它说明Excel 如何在B 中查找A。C 的常用值为0，表示函数MATCH 查找等于A 的第一个数值。注意：MATCH 函数返回B 中目标值的位置，而不位置，而不是数值本

9、身。是数值本身。为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Match函数的案例Go=MATCH(11，A1:A9，0)返回的值是?=match(420,A3:E3,0)返回的值是?为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Vlookup与match函数的综合运用Go利用利用VLOOKUPVLOOKUP和和MATCHMATCH函数查找出相应月份和产品的销售额。函数查找出相应月份和产品的销售额。=VLOOKUP(I5,$A$3:$G$14,MATCH(

10、J5,$A$2:$G$2,0),0)为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Left/Right：截取字符串函数用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。语法：LEFT(文本,返回的字符数)Right(文本,返回的字符数)参数：返回的字符数必须大于或等于0。实例：如果A1=电脑爱好者，则LEFT(A1，2)返回?;Right(A1,3)返回?电脑电脑爱好者爱好者为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Left

11、与right的综合运用 如何利用Left及Right从身份证号中提取出生年份。提示：假如提示：假如A2存放身份证号，提取年份的做法：存放身份证号，提取年份的做法：=RIGHT(LEFT(A2,10),4)身份证号共身份证号共18位，位，7到到10位位为出生年份为出生年份为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Mid：截取字符串函数用途：MID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(文本串,开始位置,返回字符的个数)参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开

12、始位置为1，以此类推。实例1：如果a1=东南(福建)汽车工业有限公司，则公式“=MID(A1，4，2)”返回“福建”。案例:利用MID从身份证号中提取出生年份。Go为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Replace：替换指定位置处的任意文本用途：REPLACE 使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。语法：REPLACE(需替换其部分字符的文本，被替换字符的起始位置，替换的字符个数，用于替换到原文本中去的字符)。为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用

13、制度，保障用人单位和职工的合法权益Replace的案例案例一：请分别说出下列两个函数公式的返回值：学习电脑学习电脑东南造船厂东南造船厂为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益LEN：计算字符长度。用途：LEN 返回文本串的字符数。语法：LEN(text)。参数：Text 待要查找其长度的文本。注意：此函数用于双字节字符，且空格也将作为字符进行统计。实例：如果A1=电脑 爱好者，则公式“=LEN(A1)”返回？答案：6为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职

14、工的合法权益If、Len及mid函数综合运用 Go如果如果A A列里列里CMCM后没有后没有“00”“00”的话，就把的话，就把“00“00“加到加到CMCM后后,但是如果但是如果CMCM后有后有”00“”00“话，就不加了（产生话，就不加了（产生的效果如的效果如B B列所示），怎么做？列所示），怎么做？公式提示：公式提示：=IF(MID(A1,3,2)00,CM00&=IF(MID(A1,3,2)00,CM00&MID(A1,3,LEN(A1)-2),MID(A1,3,LEN(A1)-2),A1)A1)为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保

15、障用人单位和职工的合法权益SUBSTITUTE：替换指定文本用途：在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定的文本，可以使用函数SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，就应当使用函数REPLACE。语法：SUBSTITUTE(需要替换其中字符的文本，需被替换的旧文本，用於替换的文本，第几次出现的旧文本)。参数：第几次出现的旧文本为一数值，用来指定以新的文本替换第几次出现的旧文本；如果指定了该参数，则只有满足要求的旧文本被替换；否则将用新文本替换所有出现的旧文本。实例：SUBSTITUTE(CM1100,1,2,2)?CM1200为了规范事业单位聘

16、用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Rept：补位函数用途：按照给定的次数重复显示文本。可以通过REPT 函数对单元格进行重复填充。语法：REPT(需重复显示的文本，重复显示的次数)。参数：重复显示的次数需为正数。注意：REPT 函数的结果不能多于255 个字符。实例：公式“=REPT(很好，2)”返回“很好很好”。为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Rept函数与len函数的综合运用新的到职编号为新的到职编号为6位，所以旧到职编号不足位，所以旧到职编号

17、不足6位的，位的，要在前面补要在前面补0为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益Index用途：返回表格或区域中的数值。语法：INDEX(单元格区域，行序号，列序号)返回数组中指定的单元格或单元格数组的数值。案例：题一：=INDEX（A1：C5，2，2）的返回值是？题二：=INDEX（A1：A5，5，1）的返回值是？256100为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益PERCENTRANK用途：返回某个数值在一个数据集合中的百分比排位，可用于查看数据在数据集中所处的位置。（即是求比此数据小的数据个数除以与此数据进行比较的数据个数总数。）语法：PERCENTRANK(数据集合，需要在数值集合中得到排位的值）为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益PERCENTRANK的案例案例一：计算出各汽车厂商相应销量的百分比排位 Go=PERCENTRANK(B2:B8,B2)为了规范事业单位聘用关系，建立和完善适应社会主义市场经济体制的事业单位工作人员聘用制度，保障用人单位和职工的合法权益