EXCEL常用函数培训教程名师优质课获奖市赛课一等奖课件.ppt

EXCEL 惯用函数培训惯用函数培训第1页学习内容学习内容 1 EXCEL快捷键操作快捷键操作 2 数学与统计函数数学与统计函数 3 逻辑函数逻辑函数 4 文本函数文本函数 5 查找与引用函数查找与引用函数 6 数据分类与汇总数据分类与汇总 7 数据透视表数据透视表第2页1 EXCEL快捷键操作打开工作簿Ctrl+O保留工作簿Ctrl+S增加一个新工作薄Ctrl+N关闭当前工作薄Ctrl+W恢复选定工作簿窗口大小Ctrl+F5最大化选定工作簿窗口Ctrl+F10最小化选定工作簿窗口Ctrl+F9切换到上一个工作簿窗口Ctrl+F6切换到下一个工作簿窗口Ctrl+Shift+F6实操第3页1 EXCEL快捷键操作 相对引用与绝对引用转换F4重新计算工作簿全部工作表F9输入当前日期Ctrl+；输入当前时间Ctrl+Shift+：向下填充输入Ctrl+D向右填充输入Ctrl+R打开“单元格格式”对话框Ctrl+1第4页 1 EXCEL快捷键操作撤消上步操作Ctrl+Z恢复上步操作Ctrl+Y重复上步操作F4剪切Ctrl+X复制Ctrl+C粘贴Ctrl+V查找Ctrl+F替换Ctrl+H定位Ctrl+G插入批注Shift+F2打开“插入函数”对话框Shift+F3定义名称Ctrl+F3第5页2 数学与统计函数数学与统计函数2.1 ROUND函数函数将数字四舍五入将数字四舍五入用用 途途：按指定位数四舍五入某个数字。语语 法法：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；实例剖析实例剖析第6页 2 数学与统计函数数学与统计函数2.2 RANK 函数函数统计排名统计排名用用 途途：返回一个数字在数字列表中排位。数字排位是其大小与列表中其它值比值（假如列表已 排过序，则数字排位就是它当前位置）。语语 法法：RANK(number,ref,order)参数意义参数意义：Number 为需要找到排位数字；Ref 为数字列表数组或对数字列表引用；Order 为一数字，指明排位方式。（假如 order 为 0或 省略，则按照降序排列。假如 order 不为零则按升序排列 列表。注注 意意：相同数值用RANK函数计算得到序数（名次）相同，但 会造成后续数字序数空缺。实例剖析 第7页2.3 2.3 最大值函数最大值函数MAXMAX、最小值函数、最小值函数MINMIN 语 法：MAX（number1，number2，）MIN（number1，number2，）参数意义：number1，number2是需要找出最大值 （最小值）1至30个数值、数组或引用。2.4 AVERAGE2.4 AVERAGE平均值函数平均值函数 语 法：AVERAGE（number1，number2，）参数意义：number1、number2是需要计算平均值130个参数。注意：函数中参数能够是数字、空白单元格、逻辑值或数字文本形式，假如参数是不能转换为数字内容将造成错误。假如参数为数组或引用，则只有数组或引用中数字参加计算，空白单元格、逻辑值或文本则被忽略。2 数学与统计函数数学与统计函数第8页功功 能能:返回数据集中第返回数据集中第K K个最大值。个最大值。语语 法：法：LARGE(array,k)LARGE(array,k)参参 数：数：array array 为需要找到第为需要找到第 k k 个最大值数组或数字型数据区域。个最大值数组或数字型数据区域。k k 为返回数据在数组或数据区域里位置为返回数据在数组或数据区域里位置(从大到小从大到小)。实实 例：例：假如假如A1=24A1=24，A2=5A2=5，A3=7A3=7，A4=15A4=15，A5=0A5=0，A6=35A6=35，A7=2A7=2，A8=5A8=5，则公式则公式=LARGE(A1:A8,3)=LARGE(A1:A8,3)返回返回1515，即数组中第，即数组中第3 3个小数字是个小数字是1515（A4A4）。）。2数学与统计函数数学与统计函数2.5 LARGE函数函数 第9页2.6 SMALL函数函数 用用 途：途：返回数据集中第K个最小值。语语 法：法：SMALLSMALL（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。实例剖析2数学与统计函数数学与统计函数第10页3.1 IF函数-进行条件检测功 能：执行真假值判断，依据逻辑计算真假值，返回不一样结果。能够使用函数 IF 对数值和公式进行条件检测。语 法：IF(logical_test,value_if_true,value_if_false)参 数：Logical_test 表示计算结果为 TRUE 或 FALSE 任意值或表示式。Value_if_true logical_test 为 TRUE 时返回值。Value_if_false logical_test 为 FALSE 时返回值。实例剖析 3 3 逻辑函数逻辑函数第11页3 逻辑函数逻辑函数功功 能：能：全部参数逻辑值为真时，返回 TRUE；只要一个参数 逻辑值为假，即返回 FLASE。语语 法：法：AND(logical1,logical2,.)参参 数数：Logical1,logical2,.表示待检测 1 到 30 个条件值，各条件 值可为 TRUE 或 FALSE。3.2 AND函数判断多个条件同时为真 3.3 OR函数判断任一参数为真功功 能能：在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；所 有参数逻辑值为 FALSE，才返回 FALSE。语语 法：法：OR(logical1,logical2,.)OR(logical1,logical2,.)参参 数数：Logical1,logical2.Logical1,logical2.为需要进行检验1到 30 个条件表示式。实例剖析第12页 条件求和函数条件求和函数SUMIF 语语 法法：SUMIF（range，criteria，sum_range）。参参 数：数：range是用于条件判断单元格区域，criteria是由数字、逻辑表示式等组成判定条件，为确定哪些单元格将被相加求和条件，其形式可 认为数字、表示式或文本 sum_range为需要求和单元格、区域或引用。实例操作第13页4.1 MID、LEFT、RIGHT 函数函数-获取字符获取字符4.1.1 MID 4.1.1 MID 函数函数 功功 能：从文本字符串中能：从文本字符串中指定起始位置指定起始位置起返回起返回指定长度指定长度字符字符 语语 法：法：Mid(text,start_num,num_chars)参参 数：数：text 准备从中提取字符串文本字符串准备从中提取字符串文本字符串 start_num准备提取第一个字符位置准备提取第一个字符位置 num_chars指定所要提取字符串长度指定所要提取字符串长度4.1.2 RIGHT 4.1.2 RIGHT 函数函数功功 能：从一个文本字符串能：从一个文本字符串最终一个字符最终一个字符开始返回指定个数字符开始返回指定个数字符语语 法：法：RIGHT(text,num_chars)参参 数：数：text 要提取字符字符串要提取字符字符串 num_chars要提取字符数，假如忽略，为要提取字符数，假如忽略，为14.1.3 LEFT 函数功功 能：从一个文本字符串能：从一个文本字符串第一个字符第一个字符开始返回指定个数字符开始返回指定个数字符语语 法：法：LEFT(text,num_chars)参参 数：数：text 要提取字符字符串要提取字符字符串 num_chars要提取字符数，假如忽略，为要提取字符数，假如忽略，为1 实例剖析实例剖析4 文本函数文本函数第14页 4.2 CONCATENATE函数函数 4 文本函数文本函数功 能：将几个文本字符串合并为一个文本字符串。语 法：CONCATENATE(text1,text2,.)参 数：Text1,text2,.为 1 到 30 个将要合并成单个文本项文本项。这些文本项能够为文本字符串、数字或对单个单元格引用。补充说明：也能够用&（和号）运算符代替函数 CONCATENATE 实现文本 项合并。实例剖析第15页4 文本函数文本函数4.3 LOWER/UPPER/PROPER大小写转换 4.3.1 LOWER 功 能：将一个文本字符串中全部大写字母转换为小写字母。语 法：LOWER(text)参 数：Text 是要转换为小写字母文本。4.3.2 UPPER 功 能：将文本转换为大写形式 语 法：UPPER(text)参 数：Text 是要转换为大写形式文本 4.3.3 PROPER 功 能：将文本字符串首字母及任何非字母字符之后首字母转换成大写，将其余字母转换成小写。语 法：PROPER(text)参 数：Text 包含在一组双引号中文本字符串、返回文本值公式或是对包含文 本单元格引用。实例剖析第16页功功 能能：在表格数组首列查找指定值，并由此返回表格数组当前行中其 他列值。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 第17页6 数据分类与汇总数据分类与汇总6.1 概述概述 分类汇总是对工作表中分类汇总是对工作表中指定行或列中指定行或列中数据进行汇数据进行汇总统计。经过折叠或展开原工作表中行、列数据及汇总统计。经过折叠或展开原工作表中行、列数据及汇总结果，从汇总和明细两种角度显示数据，可快捷地总结果，从汇总和明细两种角度显示数据，可快捷地创建各种汇总汇报。分类汇总数据折叠层次可达创建各种汇总汇报。分类汇总数据折叠层次可达8层。层。6.2 功效功效 在数据表中显示一组或多组数据分类汇总数据，在数据表中显示一组或多组数据分类汇总数据，在分组数据上完成不一样计算，如在分组数据上完成不一样计算，如求和、统计个数、求和、统计个数、求平均数、求最大（最小值）求平均数、求最大（最小值）等。等。第18页6 数据分类与汇总 6.3 建立分类汇总建立分类汇总准备工作：分类汇总表每个数据列都有列标题；对要进行分类列排序对要进行分类列排序;说明:排序列标题称为分类汇总关键字，在进行分类总时，只能指定排序后列标题为汇总关键字。操作方法:选择“数据”“分类汇总”菜单，打开分类汇总对话框,在对话框中完成操作。第19页6 数据分类与汇总汇总结果保留替换当前分类汇总:最终一次汇总会取代以前分类汇总。每组数据分页:各种不一样分类数据将被分页保留。汇总结果显示在数据下方:原数据下方会显示汇总计算结果。说明：以上3种方式可同时选中；Excel默认选择是第1和第3项。第20页6 数据分类与汇总1、指定分类字段、指定分类字段2、指定汇总方式、指定汇总方式3、指定汇总字段、指定汇总字段4、指定汇总结果、指定汇总结果保留方式保留方式实例剖析第21页6 数据分类与汇总分类汇总删除 对刚建立分类汇总，若要恢复为原始数据表，则选择“编辑”中“撤消”菜单项取消分类汇总，或单击撤消按钮。若建立分类汇总之后又进行了其它操作，可按以下方法删除分类汇总：（1）单击分类汇总表中任一单元格；（2）选择“数据”|“分类汇总”菜单项；（3）单击“分类汇总”对话框中“全部删除”按钮。实例剖析第22页7 数据透视表7.1 数据透视表作用能够改变数据表行、列布局;能够快速汇总大量数据;能够基于原数据表创建数据分组，并对分组进行汇总统计。第23页7 数据透视表7.2 数据透视表建立选择“数据”“数据透视表和数据透视图”;按向导提醒做完1，2步;在第3步中选择“布局”按钮，出现如图所表示对话框。第24页7 数据透视表解释:行：拖放到行中数据字段中每个数据项将占据透视表一行。列：拖放到列中数据字段中每个数据项将占据透视表一列。行和列确定一个二维表格.页：拖放在页中字段，Excel将按该字段数据项对透视表进行分页。数据：进行计数或汇总字段名称。第25页7 数据透视表7.3 修改数据透视表显示或隐藏行、列中数据项 在行或列字段下拉列表条中选择或取消复选标志。重新组织数据透视表 行、列置换：鼠标移至行字段上按住拖动到列字段上，列方法类似。在字段内移动个别项：鼠标按在要移动字段边框线上拖动到指定位置放开。在原有行列字段上增加或降低字段增加：单击数据透视表,在数据透视表工具栏上将所需字段拖入行或列字段处.降低：选择数据透视表行或列字段名，拖到表外 第26页7 数据透视表修改数据透视表概要函数 概要函数：即数据透视表中使用透视函数。如：求和、计数、平均、百分比、最大值、标准偏差等。操作方法：选择数据透视表中数据字段；在对象菜单或数据透视表工具栏中选择字段设置；在数据透视表对话框汇总方式列表中选择所需函数。第27页7 数据透视表修改透视表数据显示方式 见数据透视表修改数据透视表显示方式操作方法:选择数据透视表中数据字段；在对象菜单或数据透视表工具栏中选择字段设置在数据透视表对话框中单击选项按钮；在数据显示方式列表中选择所需显示方式。实例剖析第28页综合案例分析1：案例：如近期从系统导出SV-5700BOM表，发觉与前期所导出BOM表，相关物料及用量都有差异（如后期作了技术更改），现要找出有改变物料，做详细分析。案例中应用到函数：（1）CONCATENATE函数（2）条件求和函数SUMIF（3）数据透视表（4）查找与引用函数 VLOOKUP 案例2依据销售订单统计相关数据第29页 谢 谢 ！第30页