1、EXCLE 培训课件洋紫荆油墨（中山）有限公司 培培 训训 大大 纲纲 Excel基础 常用图表的制作 Excel常用函数用法及运用实例洋紫荆油墨（中山）有限公司 培培 训训 大大 纲纲 Excel基础 常用图表的制作 Excel常用函数用法及运用实例洋紫荆油墨（中山）有限公司Excel 基基 础础-数数 据据 录录 入入1、分数的输入 如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。2、序列“001”的输入 如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“”（西文单引号），然后输入“001”。3、日
2、期的输入 如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；”键。洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 录录 入入4、银行卡号、身份证号的输入 1）把输卡号的列设置为文本格式。 在输入之前，先选中要输入的那列，然后依次进入“格式单元格数字”，在“分类”中选择“文本”即可。 注意：如果发现输入数据格式有问题，则必须清空单元格，重新设置格式方可，重新输入 2）在输入之前先输入“ ”在数字前面。洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 排排 序序v 定义：数据排序是指按一定规则对数据进行整理、排列。定义
3、：数据排序是指按一定规则对数据进行整理、排列。可以按升序或降序也可以进行自定义进行排序。可以按升序或降序也可以进行自定义进行排序。 v 分类：分类：v 1 1、简单排序简单排序v 对数据清单进行排序时，如果只是按照单列的内容进行简对数据清单进行排序时，如果只是按照单列的内容进行简单排序，可以直接使用工具栏中的单排序，可以直接使用工具栏中的“升序排序升序排序”按钮按钮 或或“降序排序降序排序”按钮按钮数据数据排序排序1.1.简单排序简单排序2.2.高级排序高级排序洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 排排 序序v 1 1、简单排序、简单排序2016年1季度KPI统计表1
4、月2月3月李四93.10 95.45 85张三86.36 62.94 90王麻子82.77 83.93 82提示：对提示：对1 1月使用降序排序月使用降序排序洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 排排 序序v 2 2、高级排序、高级排序v 高级排序是指按照多个条件对数据清单进行高级排序是指按照多个条件对数据清单进行排序。排序。操作：操作：【排序和筛选排序和筛选】菜单菜单【自定义排序自定义排序】命令命令洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 排排 序序提示：打开提示：打开“高级排序高级排序”工作簿，将工作簿，将1 1月按由月按由高到低排序，将高到
5、低排序，将“2 2月月”按由低到高排序，将按由低到高排序，将“3 3月月”按颜色排序。按颜色排序。洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 筛筛 选选数据筛选数据筛选1.1.自动筛选自动筛选2.2.高级筛选高级筛选 1 1、定义：筛选是指从数据清单中查找和分析具备特定、定义：筛选是指从数据清单中查找和分析具备特定条件记录数据的快捷方式。条件记录数据的快捷方式。 2 2、分类：、分类：简单条件的筛选使用自动筛选。简单条件的筛选使用自动筛选。条件复杂的筛选使用高级筛选。条件复杂的筛选使用高级筛选。洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 筛筛 选选1 1
6、、自动筛选、自动筛选操作：操作：【数据数据】菜单菜单【筛选筛选】命令命令【自动筛选自动筛选】取消筛选操作：取消筛选操作：【数据数据】菜单菜单【筛选筛选】命令命令【自动筛选自动筛选】练习：打开练习：打开“自动筛选自动筛选”工作簿筛选出工作簿筛选出项目组名称为项目组名称为“PVCPVC墨墨(1)(1)”的所有数据的所有数据洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 筛筛 选选v 2 2、高级筛选、高级筛选v 对数据清单中的字段比较多，筛选条件也比较多，用自定对数据清单中的字段比较多，筛选条件也比较多，用自定义筛选就很麻烦，对筛选条件较多的情况，可用高级筛选义筛选就很麻烦，对筛选
7、条件较多的情况，可用高级筛选来处理来处理操作：操作：【数据数据】菜单菜单【筛选筛选】命令命令【高级筛选高级筛选】首先要建立条件区域：在数据清单以外的空白区域输入首先要建立条件区域：在数据清单以外的空白区域输入筛选条件。筛选条件。取消筛选操作：取消筛选操作：【数据数据】菜单菜单【筛选筛选】命令命令【全部全部显示显示】洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 筛筛 选选练习：打开练习：打开“高级筛选高级筛选”工作簿筛选出项工作簿筛选出项目组名称为目组名称为“PVCPVC墨墨(1)(1)”，并且数量为，并且数量为“大于等于大于等于1000”的所有数据的所有数据项目组名称项目组名
8、称数量数量PVC墨墨(1)=1000洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 分分 类类 汇汇 总总v ExcelExcel可自动计算数据清单中的分类汇总和总计值。可自动计算数据清单中的分类汇总和总计值。先按分类的字段对数先按分类的字段对数据清单排序，使同类据清单排序，使同类的排在一起（即先的排在一起（即先“分类分类”后后“汇总汇总”）操作：操作：【数据数据】菜单菜单【分类汇总分类汇总】命令命令洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 分分 类类 汇汇 总总打开打开“分类汇总分类汇总”工作簿中将数据按工作簿中将数据按“项项目名称目名称”分类，并汇总项
9、目名称的数量。分类，并汇总项目名称的数量。提示：使用分类汇总提示：使用分类汇总洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 透透 视视 表表v 数据透视表是一种对大量数据快速汇总和建立交叉列表的数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格。交互式表格。操作：操作：【数据数据】菜单菜单【数据透视表和透视图数据透视表和透视图】命令命令洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 透透 视视 表表打开打开“分类汇总分类汇总”工作簿中将数据按工作簿中将数据按“项项目名称目名称”分类，并汇总项目名称的数量。分类，并汇总项目名称的数量。提示：使用数据透视
10、表汇总提示：使用数据透视表汇总1)、选择数据清单或数据清单中任一单元格，按【数据】菜单-【数据透视表和数据透视图】洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 透透 视视 表表2)、对话框中确定数据区域，单击“确定”洋紫荆油墨（中山）有限公司 Excel 基基 础础-数数 据据 透透 视视 表表v3)、在弹出来的工作表中右侧区域选择需要的“行标签”、“数值”，左边区域则显示汇总数据。将上面所需的字段直接将上面所需的字段直接拖至下面相应位置。拖至下面相应位置。洋紫荆油墨（中山）有限公司 培培 训训 大大 纲纲 Excel基础 常用图表的制作 Excel常用函数用法及运用实例洋紫
11、荆油墨（中山）有限公司函 数 中 的 “引 用”v单元格引用单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们把引用分为相对引用相对引用、绝对引用绝对引用和混混合引用合引用三种类型。以存放在F2 单元格中的公式“=SUM(A2:E2)”为例，当公式由F2 单元格复制到F3 单元格以后，公式中的引用也会变化为“=SUM(A3:E3)”。若
12、公式自F 列向下继续复制，“行标”每增加1 行，公式中的行标也自动加1。如果上述公式改为“=SUM($A $3:$E 3)”，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。F4键可以在绝对引用和相对引用之键可以在绝对引用和相对引用之间进行转换。间进行转换。v混合引用混合引用有“绝对列和相对行绝对列和相对行”，或是“绝对行和相对列绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。想让谁不动就在谁前面加想让谁不动就在谁前面加“$”即可。即可。v三维引用三维引用：上面所讲的都是同一工作表中的数据，如果要分析同一工作簿中多张工作表上的数
13、据，就要使用三维引用。三维引用中不仅包含单元格或区域引用，还要在前面加上带“!”的工作表名称。 假如你要引用的数据来自另一个工作簿，如工作簿Book1中的SUM 函数要绝对引用工作簿Book2 中的数据，其公式为“=SUM(Book2Sheet1! SA S1: SA S8,Book2Sheet2! SB S1:SB S9)”，也就是在原来单元格引用的前面加上“Book2Sheet1!”。放在中括号里面的是工作簿名称，带“!”的则是其中的工作表名称。即是跨工作簿引用单元格或区域时，引用对象的前面必须用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。洋紫荆油墨（中山）有限公司函函 数数 的的
14、 结结 构：构：函数的结构以函数名称开始，后面是左圆括号、以逗函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（现，请在函数名称前面键入等号（= =）。）。 （，：，）（，：，） 等等 号号函数名称函数名称参参 数数注意：各参数用逗号隔开，参数用括号括起注意：各参数用逗号隔开，参数用括号括起洋紫荆油墨（中山）有限公司IF 函函 数数功能：执行真假值判断，根据逻辑计算的真假值，返功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。回不同结果。IFIF函数格式函数格式IF(l
15、ogical_test,value_if_true,value_if_false) 测试条件结果1结果2IF函数说明：如果满足“测试条件”则显示“结果1”，如果不满足“测试条件”则显示“结果2”。洋紫荆油墨（中山）有限公司IF 函函 数数打开打开“IF IF函数函数”工作表，分别计算下列公式的返回值工作表，分别计算下列公式的返回值1 1、=IF(A2B2,“=IF(A2B2,“超出预算超出预算”,“”,“正常正常”) ”) 2 2、=IF(A2B3,=IF(A2B3,超出预算超出预算,正常正常) ) 3 3、IF(A4=500,B4-A4,) IF(A4=500,B4-A4,) 4 4、IF(
16、A2B3,IF(A2B3,超出预算超出预算,正常正常)洋紫荆油墨（中山）有限公司SUM 函函 数数功能功能：计算一组选定数据的和。：计算一组选定数据的和。SUMSUM函数函数格式格式 S U M ( n u m b e r 1 , n u m b e r 2 , . . . ) 参数1SUM函数说明：参数可以是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将初忽略；但包含零值的单元格将计算在内。 参数2参数洋紫荆油墨（中山）有限公司SUM 函函 数数打开打开“SUMSUM函数函数”工作表，分别计算下列公式工作表，分别计算下列公式的值的值1 1、
17、=SUM(3, 2)=SUM(3, 2)2 2、=SUM(5, 15, TRUE)=SUM(5, 15, TRUE)3 3、=SUM(A2:A4)=SUM(A2:A4)4 4、=SUM(A2:A4, 15)=SUM(A2:A4, 15)洋紫荆油墨（中山）有限公司AVERAGE 函函 数数功能功能：计算一组选定数据的算术平均值。：计算一组选定数据的算术平均值。AVERAGEAVERAGE函数函数格式格式AVERAGE函数说明：参数可以是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将初忽略；但包含零值的单元格将计算在内。 A V E R A G
18、E( n u m b e r 1 , n u m b e r 2 , . . . )参数1参数2参数洋紫荆油墨（中山）有限公司RANK 函函 数数功能功能：返回一个数字在数字列表中的排位。数字的排位是其：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。排位就是它当前的位置）。RANKRANK函数函数格式格式= R A N K ( n u m b e r , r e f , o r d e r ) 排 序 数 字数字列表数组排 序 方 式RANK函数说明：1 1、如果、如
19、果 order order 为为 0 0（零）或省略，按由低到高排序；（零）或省略，按由低到高排序； 2 2、如果、如果 order order 不为零，按由高到低排序。不为零，按由高到低排序。洋紫荆油墨（中山）有限公司打开打开“RANKRANK函数函数”工作表工作表，对，对KPIKPI分数进行排位。分数进行排位。RANK 函函 数数洋紫荆油墨（中山）有限公司VLOOKUP 函函 数数功能功能：搜索某个单元格区域：搜索某个单元格区域 （区域：工作表上的两个或多个（区域：工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）的第一列，单元格。区域中的单元格可以相邻或不相邻。）的第一列，然
20、后返回该区域相同行上任何单元格中的值。然后返回该区域相同行上任何单元格中的值。VLOOKUPVLOOKUP函数函数格式格式=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) 搜 索 的 值搜 索 的 值搜 索 区 域匹配值 的列号VLOOKUP函数说明：如果如果 range_lookuprange_lookup 为为 TRUE TRUE 或被省略，则返回或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于 lookup_valuelookup
21、_value 的最大值。的最大值。 匹配值 的精确度洋紫荆油墨（中山）有限公司VLOOKUP 函函 数数VLOOKUP函数说明：1 1、如果、如果 range_lookuprange_lookup 为为 TRUE TRUE 或被省略，则返回精确匹配值或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于或近似匹配值。如果找不到精确匹配值，则返回小于 lookup_valuelookup_value 的最大值。的最大值。 2 2、如果、如果 range_lookuprange_lookup 参数为参数为 FALSEFALSE，VLOOKUP VLOOKUP 将只查找精将只查找精
22、确匹配值。如果确匹配值。如果 table_arraytable_array 的第一列中有两个或更多值与的第一列中有两个或更多值与 lookup_valuelookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值匹配值，则返回错误值 #N/A#N/A。洋紫荆油墨（中山）有限公司VLOOKUP 函函 数数打开打开“VLOOKUPVLOOKUP函数函数”工作表工作表，查找相对应的值。，查找相对应的值。如何更快速核对系如何更快速核对系统内盘点数据是否统内盘点数据是否录入正确？录入正确？洋紫荆油墨（中山）有限公司Excel 函函
23、数数洋紫荆油墨（中山）有限公司 培培 训训 大大 纲纲 Excel基础 常用图表的制作 Excel常用函数用法及运用实例洋紫荆油墨（中山）有限公司柏柏 拉拉 图图 Pareto chart，译作柏拉图，也称柏拉分布图，帕累托图，是一个垂直条形统计图，图中显示的相对频率数值从左至右以递减方式排列。由于图中表示频率的较高条形能清晰显示某一特定体系中具有最大累积效应的变量，因此柏拉图可有效运用于分析首要关注问题。 柏拉图一名源于意大利经济学家Vilfredo Pareto。1906年，Pareto注意到：在意大利，20%的人拥有80%的财富。他指出，这一比率在物质世界随处可见，并推理该现象或可成为一
24、条自然法则，即80%的结果由20%的投入来决定。20世纪40年代，享有品质管理之父美誉的美国电气工程师Joseph Juran 引入了Pareto理论。正是Juran决定将这一80/20比值称为“柏拉法则”（The Pareto Principle），将柏拉法则运用于业务度量有助于从“有用多数”（useful many，即其它80%）中分离出“重要少数”（vital few，具有最重要影响的20%）。柏拉图通过标示频率阐明柏拉法则频率出现越高的内容对结果影响越大。柏拉图是品质管理七大基础手段之一。图中横轴显示自变量，因变量由条形高度表示。表示累积相对频率的点对点图可附加至该条形图上。由于统计变
25、量值按相对频率顺序排列，图表可清晰显示哪些因素具有最大影响力，以及关注哪些方面可能会产生最大利益。洋紫荆油墨（中山）有限公司柏柏 拉拉 图图 Pareto分析，分析， 先比较先比较影响影响 不良率程度的高低，不良率程度的高低， 然后处理影响不良率然后处理影响不良率 较严重的项目，较严重的项目， 依次采取措施依次采取措施 叫做叫做Pareto分析法分析法。洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v打开一个打开一个EXCELEXCEL文档，建立数据统计表（可以利用排文档，建立数据统计表（可以利用排序功能自动将数据按照降序排列，其它项除外），选序功能自动将数据按照降序排列，其它项除外），选中中D4
26、D4单元格单元格. .洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v在函数编辑栏中输入在函数编辑栏中输入“=C4/SUM($C$4:$C$9)，然后回车，复制，然后回车，复制D4单元格，并粘贴到单元格，并粘贴到D5到到D9，选，选中中E4。洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v在函数编辑栏中输入在函数编辑栏中输入“=D4”，然后回车，选中，然后回车，选中E5，在函数编辑栏中输入在函数编辑栏中输入“=E4+D5”,然后回车然后回车,复制复制E5单单元格，并粘贴到元格，并粘贴到E6到到E9.洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v选中选中C3：B9区域，单击区域，单击“插入插入”，选择，选
27、择“柱形图柱形图”，单，单击击“簇状柱形图簇状柱形图”。洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v选中绘图区域，右键单击，单击选择选中绘图区域，右键单击，单击选择“选择数据选择数据(E)”洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v单击对话框中单击对话框中“添加（添加（A）”,进入对话空后，选择系进入对话空后，选择系列名称为列名称为“=分析报告分析报告!$E$3”，系列值为，系列值为“=分析报告分析报告!$E$4:$E$9”后，单击后，单击“确定确定”（两次）。（两次）。洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v选择第二个系列，就是红色那个柱状图，右键选择选择第二个系列，就是红色那个柱状图
28、，右键选择设置数据系列格式，选择次要坐标轴，就是我涂了黄设置数据系列格式，选择次要坐标轴，就是我涂了黄色的那块，然后再选择红色柱状图，右键选择更改系色的那块，然后再选择红色柱状图，右键选择更改系列图标类型，选择带数据标记的折线图。列图标类型，选择带数据标记的折线图。洋紫荆油墨（中山）有限公司柏柏 拉拉 图图v选择红色折线，添加数据标签选择红色折线，添加数据标签洋紫荆油墨（中山）有限公司柏柏 拉拉 图图洋紫荆油墨（中山）有限公司甘甘 特特 图图v 甘特图（Gantt Chart）由亨利甘特于1910年开发的，通过条状图来显示项目、进度和其他时间相关的系统进展的内在关系随着时间进展的情况。甘特图的
29、横轴表示项目的时间跨度，多以月、周或日为时间单位，纵轴表示项目涉及的各项任务，长短不一的条状图形则表示在项目周期内单项活动的时间跨度及进度。可以直观地表明任务计划在什么时候进行，及实际进展与计划要求的对比。管理者由此可以非常便利地弄清每一项任务（项目）还剩下哪些工作要做，并可评估工作是提前还是滞后，亦或正常进行。除此以外，甘特图还有简单、醒目和便于编制等特点，在项目管理中有着广泛的应用。 洋紫荆油墨（中山）有限公司甘甘 特特 图图洋紫荆油墨（中山）有限公司甘甘 特特 图图v1 1、选中表格数据（红色框区域）、选中表格数据（红色框区域）点击插入点击插入条形图条形图条形堆积图（图条形堆积图（图1
30、1）；）；洋紫荆油墨（中山）有限公司甘甘 特特 图图v2 2、右键单击蓝色条状上方，依次单击、右键单击蓝色条状上方，依次单击“选择数据选择数据”“添加添加”, ,序列名称选择序列名称选择“=Sheet1!$D$3”=Sheet1!$D$3”，序列选择，序列选择“=Sheet1!$D$4:$D$7”=Sheet1!$D$4:$D$7”（图（图2 2）；）；洋紫荆油墨（中山）有限公司甘甘 特特 图图v3 3、选择、选择Y Y轴坐标，右键单击，选择轴坐标，右键单击，选择“设置坐标轴格式设置坐标轴格式”，进入后选择进入后选择“逆序类别逆序类别”（图（图3 3）；）；洋紫荆油墨（中山）有限公司甘甘 特特 图图v4 4、右键单击蓝色区域，选择、右键单击蓝色区域，选择“设置序列格式设置序列格式”，单击填充，单击填充，选择，选择“无填充无填充”（图（图4 4）洋紫荆油墨（中山）有限公司THE ENDTHE END洋紫荆油墨（中山）有限公司
