推荐-Excel函数高级应用-课件

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第二讲,Excel,函数高级应用,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel,高级应用,_,办公应用教程,Excel 高级应用_办公应用教程,Excel,高级应用,Excel,高级应用基础,Excel,函数高级应用,Excel,图表高级应用,数据透视表与数据透视图,Excel,与数据库应用,Excel 高级应用Excel高级应用基础,Excel,高级函数入门介绍,Excel,函数的应用,Excel高级函数入门介绍,一、,Excel,高级函数入门介绍,1,、函数及其结构,2,、输入公式与插入函数,3,、巧用,Excel,帮助理解函数,一、Excel高级函数入门介绍,一、,Excel,高级函数入门介绍,1,、函数及其结构,相关知识：,函数的定义,参数,:,常量、公式、函数、数组、单元格引用等,嵌套,函数结构：函数名（参数列表）,一、Excel高级函数入门介绍,函数,逗号,等号,左括号,常数,变量,嵌套函数,引用位置,右括号,函数结构说明,函数逗号等号左括号常数变量嵌套函数引用位置右括号函数结构说明,一、,Excel,高级函数入门介绍,第二讲练习表,函数结构说明,.xls,一、Excel高级函数入门介绍第二讲练习表函数结构说明.,一、,Excel,高级函数入门介绍,2,、输入公式与插入函数,相关知识：,自动计算功能及公式的输入方法,单元格引用：相对应用、绝对引用、混合引用,函数向导,一、Excel高级函数入门介绍,一、,Excel,高级函数入门介绍,2,、输入公式与插入函数,自动计算功能及公式的输入方法,Excel,中公式以,“,=,”,开头,.,一、Excel高级函数入门介绍Excel中公式以“=”开头,一、,Excel,高级函数入门介绍,2,、输入公式与插入函数,单元格引用：相对引用、绝对引用、混合引用,引用方式和引用实例,使用,“,$,”,符号来锁定引用。,相对引用：,A1,、,B5,绝对引用：,$A$5,、,$C$2,快捷键为,F4,混合引用：,$A2,、,D$1,一、Excel高级函数入门介绍,实例练习,2-1,：在第二讲练习表,.xls,中，对,“,2-1,”,表中数据进行计算：,要求,计算进货数量和销货数量的小计值,计算出每种产品的存货数量和销货金额,统计表中产品的种类数,实例练习2-1：在第二讲练习表.xls中，对“2-1”表中数,实例练习,2-2,：在第二讲练习表,.xls,中，对,“,2-2,”,表中数据进行计算：,要求,:,该表为一个对外投资收益表,按所给公式计算,填表,其中,对外投资比重,、,投资收益率,数据格式设置为百分比形式，且保留两位小数,将合计值添入,实例练习2-2：在第二讲练习表.xls中，对“2-2”表中数,实例练习,2-3,：在第二讲练习表,.xls,中，在,“,2-3,”,中构造一个次幂表：,要求：例：,B4,单元格对应,2.0,的,0,次幂,C4,对应,2.0,的,1,次幂,D4,单元格对应的是,2.0,的平方值,D8,单元格对应,2.4,平方值,E6,对应,2.2,的立方值,以此类推,利用公式计算，结果保留一位小数,实例练习2-3：在第二讲练习表.xls中，在“2-3”中构造,实例练习,2-4,：在,“,2-1,”,表中，利用插入函数的方法插入打印日期。,实例练习2-4：在“2-1”表中，利用插入函数的方法插入打印,一、,Excel,高级函数入门介绍,3,、巧用,Excel,帮助理解函数,相关知识：,F1,键获取帮助,Office,助手,搜索方式打开,“,帮助,”,系统,一、Excel高级函数入门介绍,二、,Excel,函数的应用,1,、日期时间函数,2,、逻辑函数,3,、查找与引用函数,二、Excel函数的应用,二、,Excel,函数的应用,1,、日期与时间函数,相关知识：,DAY,、,MONTH,、,NOW,、,TODAY,、,YEAR,、,WEEKDAY,HOUR,、,MINUTE,、,SECOND,、,TIME,二、Excel函数的应用,实例练习,2-5,：在,2-5,表中，插入星期的数据。,公式为：“,=today,（）”,实例练习2-5：在2-5表中，插入星期的数据。公式为：“=t,实例练习,2-6,：在,2-5,表中，计算时间总计数据。,编辑栏中输入,“,*,60+,”,确定,实例练习2-6：在2-5表中，计算时间总计数据。编辑栏中输入,确定,此例最简单的方法是：,1,）单击,D4,单元格，,2,）在编辑栏中输入公式：“,=HOUR(C4-B4)*60+MINUTE(C4-B4)”,3,）将,D4,单元格的数字格式设置为“常规”,确定此例最简单的方法是：,确定,确定,二、,Excel,函数的应用,2,、逻辑函数,相关知识：,逻辑判断,IF,函数,COUNTIF,函数,SUMIF,函数,二、Excel函数的应用,二、,Excel,函数的应用,2,、逻辑函数,IF,函数,IF,函数有三个参数，,if,（关系或逻辑表达式，值,1,，值,2,）,当表达式为真时，结果为值,1,，当表达式为假时，结果为值,2,。,二、Excel函数的应用,实例练习,2-7,：在,2-7.xls,中，判断月指标完成情况。,要求,:,当实产量的和大于计划产量和时,标明,”,已完成,”,当实产量的和小于计划产量和时,标明,”,未完成,”,实例练习2-7：在2-7.xls中，判断月指标完成情况。,二、,Excel,函数的应用,2,、逻辑求和函数,SUMIF,函数,计算符合条件的单元格的求和,语法,:,SUMIF(,),二、Excel函数的应用,实例练习,2-8,：在第二讲练习表,.xls,的,2-8,表中,计算男女生的奖学金总计数值。,Range:,作为条件判断的单元格区域,Criteria:,输入条件数据,Sum_range:,要计算合计的单元格范围,编辑栏公式为,:,实例练习2-8：在第二讲练习表.xls的2-8表中,计算男女,二、,Excel,函数的应用,2,、条件统计函数,COUNTIF,函数,用于单个条件的统计,COUNTIF(,条件范围,统计条件,),功能,:,对区域中满足单个指定条件的单元格进行计数。,说明,条件范围只能是单元格引用。,统计条件可以使用引用、字符串表达式或数组，同时支持？和*的统计。,二、Excel函数的应用2、条件统计函数,实例练习,2-9,：,在,”,第二讲练习表,.xls,”,的,2-9countif,练习表中,按要求进行统计。,=COUNTIF(B2:B10,A*),=COUNTIF(C:C,=5)-COUNTIF(C:C,=10),”,问题：输入：“=COUNTIF(C:C,=5)-COU,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,相关知识：,LOOKUP,、,HLOOKUP,、,VLOOKUP,INDEX,OFFSET,二、Excel函数的应用,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,查找函数,-LOOKUP,函数,LOOKUP,（查找值,查找范围,目标范围,）,通常情况下查找范围的,首列或首行,要求,升序,排列,二、Excel函数的应用,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,查找函数,-VLOOKUP,函数,VLOOKUP,（查找值,数据表,列序号,匹配条件,）,匹配条件,:,0:,表示,”,精确查找,”,且支持无序查找,;,1:,表示,”,近似查找且首列升序排列进行查找,;,第二讲练习表,.xlsvlookup,和,lookup,函数表,二、Excel函数的应用,实例练习,2-10,：为,“,第二讲练习表,.xls,”,中,2-10,表制作一个简单的成绩查询器。,要求：在查询器的考号栏内输入职工考号，该职工的姓名、职称、成绩就能够自动显示出来。,原表,在（,A19,：,D20,）构造查询表,实例练习2-10：为“第二讲练习表.xls”中2-10表制作,方法,1,：利用数组,形式,B20,输入：,C20,输入：,D20,输入：,方法,2,：利用向量,形式,B20,输入：,C20,输入：,D20,输入：,方法1：利用数组形式B20输入：C20输入：D20输入：方法,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,查找函数,-HLOOKUP,函数,HLOOKUP,（查找值,查找范围,行序号）,通常用于水平方向查询,二、Excel函数的应用,实例练习,2-11,：,为,“,第二讲练习表,.xls,”,的,2-11,表按其下方的,“,评语对照表,”,为,“,评语,”,列填入评语。,原表,E3,输入：,实例练习2-11：为“第二讲练习表.xls”的2-11表按其,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,引用函数,-INDEX,函数,功能：返回数组或单元格区域中指定单元格或单元格数组的数值。如引用的区域互不相连，需用括号括起来,Array,数组,Reference,单元格区域,二、Excel函数的应用Array 数组,实例练习,2-12,：,“,第二讲练习表,.xls,”,的,2-12,表。,要求：在,F3,中显示数据表第四行第二列的内容，即,“,李四,”,实例练习2-12：“第二讲练习表.xls”的2-12 表。,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,CHOOSE,语法,:,choose(index_num,value1,value2),其中,:index_num,为待选参数的序号,取,129,的数字,如取,1,，则,choose,返回,value1,，取,2,，则,choose,返回,value2,二、Excel函数的应用,实例练习,2-13,：为,“,第二讲练习表,.xls,”,的,2-13,表的,“,是否合格,”,列填入数据。,在,E3,单元格输入：,实例练习2-13：为“第二讲练习表.xls”的2-13表的,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,引用函数,-OFFSET,函数,将源引用根据制定的行、列偏移来产生新的,单元格引用,OFFSET,（基点,行数,列数,高度,宽度,）,函数特征,高度,宽度,用于制定目标的单元格区域大小,高度,宽度,允许用户使用负数作为参数,二、Excel函数的应用,公式,=OFFSET(A5,2,2,5,3)=$C$7:$E$11,),下图展示将,A5,区域进行偏移后产生的新引用,C7:E11,公式,=OFFSET(A5,2,2,5,3)=$C$7:$E$11,5,行,3,列,OFFSET,的最终结果,第二讲练习表,.xlsOffset,函数练习,公式=OFFSET(A5,2,2,5,3)=$C$7:$E$,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,ROW,函数,被用来返回给定引用的行号,ROW,（参数）,函数特征,参数为希望得到行号的单元格或者单元格区域,二、Excel函数的应用,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,COLUMN,函数,被用来返回给定引用的列标,COLUMN,（参数）,函数特征,参数为希望得到列标的单元格或者单元格区域,二、Excel函数的应用,二、,Excel,函数的应用,3,、,Excel,查询与引用函数,ROUNDUP,函数,向上舍入数字,远离零值,ROUNDUP,（数值,小数位数）,函数特征,将数值按给定的小数位数向上舍入,如,ROUNDUP(3.2,0),的结果为,4,二、Excel函数的应用,函数综合练习,：生成工资条,主要利用引用函数借助,ROW,函数产生循环序列的方式来实现,效果如下图,.,第二讲练习表,.xls,生成工资条,函数综合练习：生成工资条第二讲练习表.xls生成工资条,