Excel函数高级应用(1).ppt

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、WEEKDAYHOUR、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函数能够进行两个条件的统计吗？例：如何统计C列数据中大于等于5而且小于10的数值个数？,输入：“=COUNTIF(C:C,=5)-COUNTIF(C:C,=10)”,二、Excel函数的应用3、Excel查询与引用函数相关知识：LOOKUP、HLOOKUP、VLOOKUPINDEXOFFSET,二、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的最终结果,第二讲练习表.xlsOffset函数练习,二、Excel函数的应用3、Excel查询与引用函数ROW函数被用来返回给定引用的行号ROW（参数）函数特征参数为希望得到行号的单元格或者单元格区域,二、Excel函数的应用3、Excel查询与引用函数COLUMN函数被用来返回给定引用的列标COLUMN（参数）函数特征参数为希望得到列标的单元格或者单元格区域,二、Excel函数的应用3、Excel查询与引用函数ROUNDUP函数向上舍入数字,远离零值ROUNDUP（数值,小数位数）函数特征将数值按给定的小数位数向上舍入如ROUNDUP(3.2,0)的结果为4,函数综合练习：生成工资条主要利用引用函数借助ROW函数产生循环序列的方式来实现,效果如下图.,第二讲练习表.xls生成工资条,