Excel在日常工作中的高效应用PPT课件

Excel在日常工作中的高效应用,Office软件中高级培训之,目录,1,操作技巧,2,公式函数,3,数据透视表,4,图表与VBA,快捷键运用技巧,1.高效管理数据技巧,通用快捷键口复制ctrl+c口剪切ctrl+x口全选ctrl+a口粘贴ctrl+v口撤销ctrl+z口重做ctrl+y口查找ctrl+f口打印ctrl+p口替换ctrl+H口返回桌面Windows+P,特有快捷键口设置单元格格式ctrl+1口转换单元格引用方式f4口数组公式ctrl+shift+enter口扩展选定区域ctrl+shift+方向口单元格内换行alt+enter口添加批注shift+f2口移至表格开始位置ctrl+home口多单元格填入相同内容：ctrl+enter,自定义快捷键可使用自定义宏加设置快捷键方式,1.高效管理数据技巧,路径：数据-数据有效性,创建下拉菜单,1.高效管理数据技巧,实现表格某些部分别人不能编辑，但是某些数据区需要别人填数据,关键步骤:1.全部单元格变成未锁定2.需要保护的单元格变成锁定3.审阅保护工作表，只勾选：选定未锁定的单元格,根据单元格数据内容有条件设置单元格格式,使用条件格式按单元格数值大小改变单元格格式,1.高效管理数据技巧,根据单元格数据内容有条件设置单元格格式,使用公式做为条件设置复杂的单元格格式：到期提醒,条件格式的拓展应用：1.表格样式自动生成,1.高效管理数据技巧,重复数据与缺失数据的处理,1.高效管理数据技巧,数据维护：共享工作簿,1.高效管理数据技巧,照相机的功能,Excel中的照相功能，可以将被选择的数据区域“拍照”成为图片。换句话说，使用Excel中的照相功能，可以把选中的数据区域，转换成为图片。,1.高效管理数据技巧,1.什么是函数Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,.)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们；而后者必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。2.什么是公式函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。,函数和公式,2.函数公式在日常工作中的运用,函数的参数,参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1)，甚至可以是另一个或几个函数等。常量这里就不多叙述，大家应该见得很多，比如数字“012”、文本“星愿宝”等，都是常量，可以作为参数；1.逻辑值：逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,A2/A3)”中，“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算结果。2.数组：数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在“”(按下Ctrl+Shift+Enter组合键自动生成)内部，而且内部各列的数值要用逗号“,”隔开，各行的数值要用分号“;”隔开。假如你要表示第1行中的56、78、89和第2行中的90、76、80，就应该建立一个2行3列的常量数组“56,78,89;90,76,80。区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。3.错误值：使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”，如果其中的参数是#NUM!，则返回数值“6”。,2.函数公式在日常工作中的运用,函数的参数,5.单元格引用单元格引用是函数中最常见的参数，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2单元格中的公式“=SUM(A2:E2)”为例，当公式由F2单元格复制到F3单元格以后，公式中的引用也会变化为“=SUM(A3:E3)”。如果上述公式改为“=SUM($A$3:$E3)”，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。混合引用有“绝对列和相对行”，或是“绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。提示：上面介绍的是Excel默认的引用方式，称为“A1引用样式”。如果你要计算处在“宏”内的行和列，必须使用“R1C1引用样式”。在这种引用样式中，Excel使用“R”加“行标”和“C”加“列标”的方法指示单元格位置。6.嵌套函数除了上面介绍的情况外，函数也可以是嵌套的，即一个函数是另一个函数的参数，例如“=IF(RIGHT(E2,1)=”1“，男”,“女”)”。7.名称和标志为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用。由于Excel工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=AVERAGE(物理)”。,2.函数公式在日常工作中的运用,2.函数公式在日常工作中的运用,SumAverageCountMaxMinSumifSumifsCountifCountifsCountaAverageifAverageifsSumproduct,YearMonthDayDatetodayDatedifNetworkdaysWeekdayWeeknumHourMinuteSecondNow,LeftRightMidFindTextLen,用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务；语法：IF(logical_test，value_if_true，value_if_false)；参数：Logical_test计算结果为TRUE或FALSE的任何数值或表达式；而且Value_if_true和Value_if_false都可以是一个表达式；如果logical_test为FALSE并且省略value_if_false，则返回FALSE。实例：A1序号成绩A2245A3390A4478公式说明（结果）=IF(A289,A,IF(A279,B,IF(A269,C,IF(A259,D,F)为第一个成绩指定一个字母等级(F)=IF(A389,A,IF(A379,B,IF(A369,C,IF(A359,D,F)为第二个成绩指定一个字母等级(A)=IF(A489,A,IF(A479,B,IF(A469,C,IF(A459,D,F)为第三个成绩指定一个字母等级(C),2.函数公式在日常工作中的运用,2.函数公式在日常工作中的运用,SUMIF(range,criteria,sum_range)SUMIF函数语法具有以下参数：range必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。criteria必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为32、32、B5、32、苹果或TODAY()。重要任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号()括起来。如果条件为数字，则无需使用双引号。sum_range可选。要求和的实际单元格（如果要对未在range参数中指定的单元格求和）。如果省略sum_range参数，Excel会对在range参数中指定的单元格（即应用条件的单元格）求和。可以在criteria参数中使用通配符（包括问号(?)和星号(*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符()。,2.函数公式在日常工作中的运用,SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.)=SUMIFS(A2:A9,B2:B9,=A*,C2:C9,卢宁)=SUMIFS(A2:A9,B2:B9,香蕉,C2:C9,卢宁),2.函数公式在日常工作中的运用,IfAndOrIserror,VlookupIndexMatchOffsetIndirect,ColumRowRankModRoundRounddownRoundup,2.函数公式在日常工作中的运用,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)例如：=VLOOKUP(105,A2:C7,2,TRUE)=VLOOKUP(“陈,B2:E7,2,FALSE)lookup_value（必需参数）要查找的值。要查找的值必须位于table-array中指定的单元格区域的第一列中。例如，如果table-array指定的单元格为B2:D7，则lookup_value必须位于列B中。Table_array（必需参数）VLOOKUP在其中搜索lookup_value和返回值的单元格区域。该单元格区域中的第一列必须包含lookup_value。此单元格区域中还需要包含您要查找的返回值。col_index_num（必需参数）其中包含返回值的单元格的编号（table-array最左侧单元格为1开始编号）。range_lookup（可选参数）一个逻辑值，指定希望VLOOKUP查找精确匹配值还是近似匹配值：TRUE假定表中的第一列按数字或字母排序，然后搜索最接近的值。这是未指定值时的默认方法。FALSE在第一列中搜索精确值。,2.函数公式在日常工作中的运用,PVFVNPERPMTRATE,1.必须有等号：=2.每个函数都带一对大括号:()3.公式横拉竖拉前，记得锁单元格,1.非EXCEL格式的外部数据导入,3.日常管理实务数据分析,2.数据透视表的基本概念及生成数据透视表,可以把Excel想象成一个很大的工具箱，数据透视表是Excel工具箱中的一个工具。可以将数据透视表看作是对准数据集的万花筒。通过数据透视表查看数据集时，有可能看到之前没有注意到的细节。此外，还可以旋转数据透视表，以便从不同的角度查看数据。数据集本身没有变化，也不连接到数据透视表。数据透视表是可以用来产生对数据进行独特透视的工具。,3.日常管理实务数据分析,整理数据区域修改非法日期把文本型数字转换为纯数字删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删除不必要的小计行和总计行将二维表格整理为数据清单等等,2.怎样规范导入的数据：特殊字符和数据格式如何处理,3.日常管理实务数据分析,Excel是如何处理日期的？日期是特殊的数字。日期永远是大于零的正整数。正确输入日期正确输入日期：2010-10-8，2010/10/8，8/oct/2010，8-oct-2010错误的输入日期方式：2010.10.08，或者20101008错误日期的类型输入错误从系统导入的日期是文本格式如何把日期显示为需要的格式？修改非法日期,3.日常管理实务数据分析,如何将文本型数字转换为纯数字有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，需要转换为纯数字。方法1：利用智能标记方法2：利用公式（两个负号或者乘以1或除以1）方法3：利用选择性粘贴的批量修改功能（乘以1或者除以1）如何把数字转换为文本型数字?使用分列工具使用TEXT函数案例将文本型数字转化为纯数字,3.日常管理实务数据分析,从系统中导入的数据中，可能会有很多“垃圾”字符会影响到数据的处理和分析，应当予以删除。比如：删除数据中的空格删除字符中的打印不出的特殊字符（案例）,3.日常管理实务数据分析,对于不规范的表格，例如多表头，有合并单元格，等等，需要进行相应的整理。方法：根据情况，可以使用复制/粘贴，函数、VBA等方法。案例取消合并单元格并快速填充数据,3.日常管理实务数据分析,很多表格是多行表头或者多列表头，造成数据处理分析很难，需要将其整理为数据清单。案例表格架构的重新搭建,3.日常管理实务数据分析,3.生成数据透视表,3.日常管理实务数据分析,1,2,3.生成数据透视表,3.日常管理实务数据分析,快速更换显示平均值等,选中单元格，右键单击选择“值字段设置”,3.生成数据透视表,3.日常管理实务数据分析,快速更换显示平均值等,各种占比一招摆平,3.生成数据透视表,3.日常管理实务数据分析,步长的选择若日期中含有年份，在选择步长时，不但要按“月”作为步长，还应该按住键的同时选择“年”。这样保证不会将不同年份的同一月份组合在一起,右击部分单元格后选择“创建组”选项,快速组合分析,3.生成数据透视表,3.日常管理实务数据分析,重复/不重复项目标签一步搞定,巧妙更改数据透视表布局,3.生成数据透视表,3.日常管理实务数据分析,使数据透视打开时自动更新,手动更新方法一单击数据透视表中的任一单元格，单击”选项”选项卡中的”刷新”按钮手动更新方法二在键单击数据透视表中的任一单元格，然后在弹出的快捷菜单中选择”刷新”选项,1,2,3,3.生成数据透视表,3.日常管理实务数据分析,无限方便的切片器,切片器：易于使用的筛选组件，可以使您能够快速地筛选数据透视表中的数据，而无需打开下拉列表以查找要筛选的项目。,切片器可同时创建多个，不通过筛选下拉菜的情况下对透视表进行筛选,3.生成数据透视表,3.日常管理实务数据分析,在数据透视中添加计算项,1,2,3,1.把EXCEL的表格或图表复制到PPT中,4.Excel与其他软件的结合,1).把表格或表格的一部分复制到PPT在Excel中选中数据区域或表格并“复制”，在PPT中“粘贴”即可。2).在PPT中粘贴的表格数据随着Excel表格数据的修改而变化打开保存数据的工作簿，激活要插入PPT的工作表，在PPT中执行“插入/对象/由文件创建”操作，单击“浏览”按钮在打开的对话框中找到并选中文件后单击“确定”返回“插入”对话框，选中“链接”后“确定”即可。,2.汇总多个工作簿的工作表,4.Excel与其他软件的结合,5.Excel图表展示,前言.图表的选择,选择图表的步骤,第一步：确定你的信息从数据到信息选择合适的图形关键在于你，作为一个设计者，首先，最重要的是明确你想要表达的具体信息,第二步：确定相对关系从信息到相对关系你决定的信息在一定程度上包含5种基本类别中的一种：成分、项目、时间序列、频率分布及相关性。,第三步：选择图表形式从相对关系到图表每一种相对关系都会相应引导出5种相对的图表形式,5.Excel图表展示,前言.图表的选择,第一步:决定你的信息,练习：利用这些数据画出尽可能多的草图，注意：越多越好。,5.Excel图表展示,前言.图表的选择,西部,北部,东部,南部,南部,北部,西部,东部,饼图强调的是A公司和B公司的组合销售量不同,5.Excel图表展示,前言.图表的选择,条形图强调的是A公司和B公司的销售额所占的百分比随着地域的不同而改变,5.Excel图表展示,前言.图表的选择,A公司销售额是南部最高B公司的销售额是北部最高,5.Excel图表展示,前言.图表的选择,13%,25%,27%,35%,公司,A,A公司销售额是南部最高，而B公司在南部的销售额最低,5.Excel图表展示,前言.图表的选择,在南部地区，A公司与B公司相比在利润上以一个巨大的差额领先；在东部地区，A公司与B公司势均力敌；在北部地区，A公司则明显落后于B公司,5.Excel图表展示,前言.图表的选择,产品销售量，单位：千美元,你的注意可能集中在1月至5月的总销售额变化趋势以及销售额格随时间变化的规律，那么你的信息就是:自1月以来销售额正在稳步提升,5.Excel图表展示,前言.图表的选择,产品销售量，单位：千美元,在5月里，产品的销售额大幅领先产品B和,5.Excel图表展示,前言.图表的选择,产品销售量，单位：千美元,在5月里，产品销售额所占比例是公司所有产品中最大的,5.Excel图表展示,前言.图表的选择,使用相同的数据却得到完全不同的信息，强调销售额还是排名是你决定的，而这个决定就是你要表达的信息,产品的销售额大幅领先产品B和,产品销售额所占比例是公司所有产品中最大的,5.Excel图表展示,前言.图表的选择,公司的销售额趋势变化产品地域生产力分布资产分配百分比雇员年龄分布薪水与利润的关系,这样的标题很神秘,5.Excel图表展示,前言.图表的选择,西部,北部,东部,南部,这张图你想表达什么信息呢？,5.Excel图表展示,西部,北部,东部,南部,西部,北部,东部,南部,一些观众可能认为你想说：西部地区利润占公司总额近半,一些观众可能认为你想说：北部收益份额最小,用“各地区所占利润的百分比”来做图表标题，不如用下面红色字来做表题，最好还把你想表达的数据着重表达出来，例如换一种颜色。,5.Excel图表展示,54,你可能在向观众传达以下信息：订单的数量在增长订单的数量在上下波动在8月，订单的数量到达了最高点在8个月中，有两个月订单的数量在下降,5.Excel图表展示,第二步:确定相对关系,成分相对关系项目相对关系时间序列关系频率分布相对关系相关性相对关系,5.Excel图表展示,1.成分相对关系,在一个成分相对关系里，我们的兴趣主要在于每一个部分所占的百分比。如：在5月份，A产品预计将占到公司总销售额的最大份额。2005年客户的市场份额少于整个行业10%。,成分：占总体的百分比,5.Excel图表展示,2.项目相对关系,在项目相对关系里，我们想要比较事物的排列方法。他们是差不多，还是一个比另一个更多或更少？如：在5月份，A产品的销售额相当于B、C销售额之和。销售额中顾客的回报排名第四。,项目：项目的排名,5.Excel图表展示,3.时间序列关系,时间序列相对关系：对它怎么随着时间变化感兴趣；它每星期、每月、每季度和每年的变化趋势是增长的、减少的、上下波动的或者是基本保持不变。如：自从1月以来销售额稳定增长。投资回报在过去的5年里急剧下跌。,时间：如何随着时间变化,5.Excel图表展示,4.频率分布相对关系,各数值范围内个包含了多少个项目。如：在5月，大多数地区的销售额在1,000到2,000美元之间。大多数的出货要5到6天的时间交付。,频率：项目的分布情况；,5.Excel图表展示,5.相关性相对关系,两个变量的关系是否符合你所想要证明的模式。如：5月份销售业绩显示出销售业绩与销售员的经验没有联系。CEO的薪水并不随着公司规模的变化而改变。,相对性：变量之间的关系,5.Excel图表展示,第三步:选择图表形式,饼图,条形图,柱形图,折线图,散点图,5.Excel图表展示,饼图,条形图,柱形图,折线图,散点图,成分,项目,时间序列,频率分布,相对性,5.Excel图表展示,表示成分最好使用饼图。在使用中不宜多于6种成分。如果你的使用超过了6种，那么就选择6种最重要的，并将未选中的列为“其他”的范畴。因为人的眼镜比较习惯于按顺时针方向进行观察，所以应该将最重要的部分放在紧靠12点钟的位置，并且使用强烈的颜色对比以显示突出（比如在黑色背景下使用黄色），或者在黑白图中使用最强烈的阴影效果，或者将此部分与其他部分分离开。如果没有哪一个部分比其他部分更加重要，那么就应该考虑让它们以大到小的顺序排列，并且以同一种颜色或者干脆不使用阴影来绘制图表的每一个部分。,成分：占总体的百分比,5.Excel图表展示,第四季度销售额所占比例最少,第四季度销售额所占比例最少,红色标注突出数据并放置在12点方向,分离出来单独显示,5.Excel图表展示,第四季度销售额所占比例最少,第四季度销售额所占比例最少,环形图也是商业图表中用得比较多的,5.Excel图表展示,在条形图中，要做到保持条形图之间的距离比条的宽度小。使用最能形成对比的颜色及阴影来强调重要的项目，以此来强调信息的标题。为了突显数值，可在顶端（或者在底端）使用一个刻度尺，或者在条形末尾标注数字，但是注意不要两者同时使用。如果你只是想简单标示项目间的关联，可用刻度尺；如果数字对你很重要那么就是用数字。有时，使用刻度尺的同时标示一个需要特殊强调的数值，也是个好办法。,项目：项目的排名,5.Excel图表展示,宗庆后家族仍是中国首富,数据来源：胡润中国排行榜,5.Excel图表展示,全球手机游戏营业额不断增长,单位:10亿美元,数据来源：Digi-Capital2011年全球电子游戏业投资报告,5.Excel图表展示,选择柱状图或折线图取决于用哪一个比较方便。如果你的图表中只有少的几个点（如7到8个），那么就是用柱形图；反之，如果你必须在图中展示20多年来每个季度的变化趋势，你最好还是使用折线图。柱状图强调的是数量的级别，更适合于表现在一小段时间里发生的事件，产量的数据很适合这个领域。折线图强调的是角度的运动及图像的变换，因此展示数据的发展趋势时最好使用它，存货量就是一个很好的例子。,时间：随着时间变化,5.Excel图表展示,全球电子游戏各部门收入,单位:10亿美元,数据来源：Digi-Capital2011年全球电子游戏业投资报告,5.Excel图表展示,两种主要应用，第一种是在所有样本中进行归纳，在这里是用来预测风险、可能性或者机会的。另一种是描述确定性（不确定性），如掷骰子游戏中，在所有的可能性中猜七点的概率。当只使用几行数据时，柱形图要好一些，而当数量很多时最好使用折线图。,频率：项目的分布情况；,5.Excel图表展示,大多数的销售额在30元至50元之间,5.Excel图表展示,显示两种变量符合或是不符合你所希望出现的模板。成对条形图方案只在数据组较少时才会起作用。如果数据超过了15组甚至更多时，那么你最好不要再标记出每个点，应该换为使用一个更加紧密的散点图。,相对性：变量之间的关系,5.Excel图表展示,8,6,4,0,折扣/美元,2,0,20,40,80,60,单位/销售量,折扣与销售量之间没有关系,5.Excel图表展示,5.Excel图表展示,1.商业图表的制作,5.Excel图表,1.商业图表的制作,商业图表的布局,主标题,副标题,图列区,绘图区,脚注区,5.Excel图表,1.商业图表的制作,商业图表的制作步骤,步骤：1.生成普通的图2.配合单元格，进行底纹填充3.主标题制作：黑底白字4.删除多余的坐标轴5.副标题以及脚注区的文字6.图列手动添加详细见案例操作！,5.Excel图表,1.商业图表的制作,案例：成本费用预算执行情况动态分析报表和图表,5.Excel图表,6.VBA自动化,欢迎使用Excel和VisualBasicforApplication开发应用程序！作为已经能够熟练使用Excel的用户，你一定对Excel强大的功能感到满意。现在，你将逐步提高到更高的水平。即使你以前从来没有编写过程序，也能够使用VisualBasicforApplication开发出解决方案。VisualBasicforApplication是现在可用的最容易学习、最容易使用同时也是最复杂的应用程序自动化语言（过去常常称为宏语言）之一。在这个学时中，通