EXCEL常用函数培训教程.ppt

EXCEL常用函数培训 学习内容 1EXCEL快捷键操作2数学与统计函数3逻辑函数4文本函数5查找与引用函数6数据分类与汇总7数据透视表 1EXCEL快捷键操作 实操 1EXCEL快捷键操作 1EXCEL快捷键操作 2数学与统计函数 2 1ROUND函数 将数字四舍五入用途 按指定位数四舍五入某个数字 语法 ROUND number num digits 参数意义 Number是需要四舍五入的数字 Num digits为指定的位数 Number按此位数进行处理 注意 1 在单元格格式中可以设置小数位数 但数字本身并没有真正的四舍五入 只是以2位 如设置为2 的形式显示数据 如果财务中采用这种方式进行计算 经常会出现几分钱的误差的 2 如果num digits大于0 则四舍五入到指定的小数位 实例 如果A1 65 25 则公式 ROUND A1 1 返回65 3如果num digits等于0 则四舍五入到最接近的整数 实例 如果A1 65 25 则公式 ROUND A1 0 返回65如果num digits小于0 则在小数点左侧按指定位数四舍五入 实例 如果A1 65 25 则公式 ROUND A1 1 返回70 实例剖析 2数学与统计函数2 2RANK函数 统计排名 用途 返回一个数字在数字列表中的排位 数字的排位是其大小与列表中其他值的比值 如果列表已排过序 则数字的排位就是它当前的位置 语法 RANK number ref order 参数意义 Number为需要找到排位的数字 Ref为数字列表数组或对数字列表的引用 Order为一数字 指明排位的方式 如果order为0或省略 则按照降序排列 如果order不为零则按升序排列的列表 注意 相同数值用RANK函数计算得到的序数 名次 相同 但会导致后续数字的序数空缺 实例剖析 2 3最大值函数MAX 最小值函数MIN语法 MAX number1 number2 MIN number1 number2 参数意义 number1 number2 是需要找出最大值 最小值 的1至30个数值 数组或引用 2 4AVERAGE 平均值函数语法 AVERAGE number1 number2 参数意义 number1 number2 是需要计算平均值的1 30个参数 注意 函数中的参数可以是数字 空白单元格 逻辑值或数字的文本形式 如果参数是不能转换为数字的内容将导致错误 如果参数为数组或引用 则只有数组或引用中的数字参与计算 空白单元格 逻辑值或文本则被忽略 2数学与统计函数 功能 返回数据集中第K个最大值 语法 LARGE array k 参数 array为需要找到第k个最大值的数组或数字型数据区域 k为返回的数据在数组或数据区域里的位置 从大到小 实例 如果A1 24 A2 5 A3 7 A4 15 A5 0 A6 35 A7 2 A8 5 则公式 LARGE A1 A8 3 返回15 即数组中第3个小的数字是15 A4 数学与统计函数2 5LARGE函数 2 6SMALL函数用途 返回数据集中的第K个最小值 语法 SMALL array k 参数 array为需要找到第k个最小值的数组或数字型数据区域 k为返回的数据在数组或数据区域里的位置 从小到大 实例 如果A1 24 A2 5 A3 7 A4 15 A5 0 A6 9 A7 2 A8 5 则公式 SMALL A1 A8 5 返回7 即数组中第5个小的数字是7 实例剖析 数学与统计函数 3 1IF函数 进行条件检测功能 执行真假值判断 根据逻辑计算的真假值 返回不同结果 可以使用函数IF对数值和公式进行条件检测 语法 IF logical test value if true value if false 参数 Logical test表示计算结果为TRUE或FALSE的任意值或表达式 Value if truelogical test为TRUE时返回的值 Value if falselogical test为FALSE时返回的值 实例剖析 3逻辑函数 3逻辑函数 功能 所有参数的逻辑值为真时 返回TRUE 只要一个参数的逻辑值为假 即返回FLASE 语法 AND logical1 logical2 参数 Logical1 logical2 表示待检测的1到30个条件值 各条件值可为TRUE或FALSE 3 2AND函数 判断多个条件同时为真 3 3OR函数 判断任一参数为真 功能 在其参数组中 任何一个参数逻辑值为TRUE 即返回TRUE 所有参数的逻辑值为FALSE 才返回FALSE 语法 OR logical1 logical2 参数 Logical1 logical2 为需要进行检验的1到30个条件表达式 实例剖析 条件求和函数SUMIF 语法 SUMIF range criteria sum range 参数 range是用于条件判断的单元格区域 criteria是由数字 逻辑表达式等组成的判定条件 为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本sum range为需要求和的单元格 区域或引用 实例操作 4 1MID LEFT RIGHT函数 获取字符4 1 1MID函数功能 从文本字符串中指定的起始位置起返回指定长度的字符语法 Mid text start num num chars 参数 text准备从中提取字符串的文本字符串start num准备提取的第一个字符的位置num chars指定所要提取的字符串的长度4 1 2RIGHT函数功能 从一个文本字符串的最后一个字符开始返回指定个数字符语法 RIGHT text num chars 参数 text要提取字符的字符串num chars要提取的字符数 如果忽略 为14 1 3LEFT函数功能 从一个文本字符串的第一个字符开始返回指定个数字符语法 LEFT text num chars 参数 text要提取字符的字符串num chars要提取的字符数 如果忽略 为1实例剖析 4文本函数 4 2CONCATENATE函数 4文本函数 功能 将几个文本字符串合并为一个文本字符串 语法 CONCATENATE text1 text2 参数 Text1 text2 为1到30个将要合并成单个文本项的文本项 这些文本项可以为文本字符串 数字或对单个单元格的引用 补充说明 也可以用 和号 运算符代替函数CONCATENATE实现文本项的合并 实例剖析 4文本函数 4 3LOWER UPPER PROPER 大小写转换 4 3 1LOWER功能 将一个文本字符串中的所有大写字母转换为小写字母 语法 LOWER text 参数 Text是要转换为小写字母的文本 4 3 2UPPER功能 将文本转换为大写形式语法 UPPER text 参数 Text是要转换为大写形式的文本4 3 3PROPER功能 将文本字符串的首字母及任何非字母字符之后的首字母转换成大写 将其余的字母转换成小写 语法 PROPER text 参数 Text包括在一组双引号中的文本字符串 返回文本值的公式或是对包含文本的单元格的引用 实例剖析 功能 在表格数组的首列查找指定的值 并由此返回表格数组当前行中其他列的值 VLOOKUP中的V参数表示垂直方向 语法 VLOOKUP lookup value table array col index num range lookup 参数 Lookup value为需要在数据表首列进行搜索的值 Table array为两列或多列数据 使用对区域或区域名称的引用 table array第一列中的值是由lookup value搜索的值 这些值可以是文本 数字或逻辑值 文本不区分大小写 Col index num为table array中待返回的匹配值的列序号 Col index num为1时 返回table array第一列中的数值 为2 返回table array第二列中的数值 以此类推 Range lookup为逻辑值 指定希望VLOOKUP查找精确的匹配值还是近似匹配值 1 如果为FALSE VLOOKUP将只寻找精确匹配值 在此情况下 table array第一列的值不需要排序 如果table array第一列中有两个或多个值与lookup value匹配 则使用第一个找到的值 如果找不到精确匹配值 则返回错误值 N A 2 如果为TRUE或省略 则返回精确匹配值或近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 table array第一列中的值必须以升序排序 否则VLOOKUP可能无法返回正确的值 实例剖析 5查找与引用函数VLOOKUP 6数据分类与汇总 6 1概述分类汇总是对工作表中指定的行或列中的数据进行汇总统计 通过折叠或展开原工作表中的行 列数据及汇总结果 从汇总和明细两种角度显示数据 可快捷地创建各种汇总报告 分类汇总的数据折叠层次可达8层 6 2功能在数据表中显示一组或多组数据的分类汇总数据 在分组数据上完成不同的计算 如求和 统计个数 求平均数 求最大 最小值 等 6数据的分类与汇总6 3建立分类汇总准备工作 分类汇总表的每个数据列都有列标题 对要进行分类的列排序 说明 排序的列标题称为分类汇总关键字 在进行分类总时 只能指定排序后的列标题为汇总关键字 操作方法 选择 数据 分类汇总 菜单 打开分类汇总对话框 在对话框中完成操作 6数据的分类与汇总 汇总结果保存替换当前分类汇总 最后一次的汇总会取代以前的分类汇总 每组数据分页 各种不同的分类数据将被分页保存 汇总结果显示在数据下方 原数据的下方会显示汇总计算的结果 说明 以上3种方式可同时选中 Excel的默认选择是第1和第3项 6数据的分类与汇总 实例剖析 6数据的分类与汇总 分类汇总的删除对刚建立的分类汇总 若要恢复为原始数据表 则选择 编辑 中的 撤消 菜单项取消分类汇总 或单击撤消按钮 若建立分类汇总之后又进行了其他操作 可按如下方法删除分类汇总 1 单击分类汇总表中的任一单元格 2 选择 数据 分类汇总 菜单项 3 单击 分类汇总 对话框中的 全部删除 按钮 实例剖析 7数据透视表 7 1数据透视表的作用能够改变数据表的行 列布局 能够快速汇总大量数据 能够基于原数据表创建数据分组 并对分组进行汇总统计 7数据透视表 7 2数据透视表的建立选择 数据 数据透视表和数据透视图 按向导提示做完1 2步 在第3步中选择 布局 按钮 出现如图所示的对话框 7数据透视表 解释 行 拖放到行中的数据字段中的每个数据项将占据透视表的一行 列 拖放到列中的数据字段中的每个数据项将占据透视表的一列 行和列确定一个二维表格 页 拖放在页中的字段 Excel将按该字段的数据项对透视表进行分页 数据 进行计数或汇总的字段名称 7数据透视表 7 3修改数据透视表显示或隐藏行 列中的数据项在行或列字段的下拉列表条中选择或取消复选标志 重新组织数据透视表行 列置换 鼠标移至行字段上按住拖动到列字段上 列方法类似 在字段内移动个别项 鼠标按在要移动字段边框线上拖动到指定位置放开 在原有行列字段上增加或减少字段增加 单击数据透视表 在数据透视表工具栏上将所需字段拖入行或列的字段处 减少 选择数据透视表行或列的字段名 拖到表外 7数据透视表 修改数据透视表的概要函数概要函数 即数据透视表中使用的透视函数 如 求和 计数 平均 百分比 最大值 标准偏差等 操作方法 选择数据透视表中数据字段 在对象菜单或数据透视表工具栏中选择 字段设置 在数据透视表对话框的汇总方式列表中选择所需函数 7数据透视表 修改透视表数据的显示方式 见数据透视表 修改数据透视表的显示方式 操作方法 选择数据透视表中数据字段 在对象菜单或数据透视表工具栏中选择 字段设置 在数据透视表对话框中单击 选项 按钮 在 数据显示方式 列表中选择所需的显示方式 实例剖析 综合案例分析1 案例 如近期从系统导出SV 5700BOM表 发现与前期所导出的BOM表 相关物料及用量均有差异 如后期作了技术更改 现要找出有变化的物料 做具体分析 案例中应用到的函数 1 CONCATENATE函数 2 条件求和函数SUMIF 3 数据透视表 4 查找与引用函数VLOOKUP 案例2根据销售订单统计相关数据 谢谢