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