第二讲 Excel函数高级应用

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