Excel函数高级应用.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 表示 近似查找且首列升序排列进行查找 第二讲练习表 xls vlookup和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为待选参数的序号 取1 29的数字如取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 参数 函数特征参数为希望得到列标的单元格或者单元格区域