Excel 中级培训.ppt

《Excel 中级培训.ppt》由会员分享，可在线阅读，更多相关《Excel 中级培训.ppt（54页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、Excel培训中级Excel2021/9/261中级培训目标-Excel方法论n能够熟练的运用各种公式来完成Excel计算任务。n方便的处理Excel中的文字信息。n对Excel提供的各种功能有所了解，并且能够灵活的在日常工作中运用。n绘制图表，美化图表。2021/9/262中级培训内容-Excel方法论1.用公式简化你的Excel工作2.了解Excel提供的各种功能3.绘制图表辅助数据阅读2021/9/263目录1.用公式简化你的Excel工作1.公式简介2.常用的计算公式和函数3.一次键入和自动填充4.引用格式5.逻辑公式6.文字处理公式7.与时间相关的公式8.其他9.选择性粘贴2.其他功

2、能3.图表的制作2021/9/264公式和函数简介n公式和函数使得电子表格变得非常有用。尤其是在处理大量数据的时候。n使用Excel中的公式来计算电子表格中的数据以得到结果。当数据更新后，无需做额外的工作，公式将自动更新结果。n函数可以大大加强公式的功能和进行普通运算符不能完成的计算。Excel包括300多个函数，甚至可以从第三方供应商处购买额外的专用函数甚至可以创建自己的定值函数（VBA），但是通常使用的也只有十几个函数。2021/9/265公式和函数简介n公式由下列几个元素组成：q运算符，如“+”，“*”；q单元格（或区域）引用；q数值或文本；q工作表函数，如“sum”，“average”

3、；n例如：q=sum(A1*B1,A2*B2,A3*B3)/3n函数是可以嵌套的。n例如：q=sum(average(A1：C1),average(A2:C2)2021/9/266必须记住的函数n常用计算公式和函数：q运算符号：+、-、*、/、q逻辑判断符号：=,=,60,”pass”,”fail”).qIf(and(A160,A260),”allpass”,”fail”).nIf可以与其他的命令组合使用，例如：qSumif：sumif(区域，”0”，求和区域）qCountif：countif(区域，”0”）2021/9/2610逻辑公式nTrueFalse1，0nAnd()，OR(),Not

4、()n在Excel公式中，逻辑判断中使用到and或or的格式是：and（，）；or（，）;not()n例如nIf(and(A11,B11,C11),1,0)nIf(or(A11,B11,C11),1,0)nIf(not(A11),1,0)2021/9/2611用公式处理文本nUpper:把文本全部转换为大写。nLower:把文本全部转换为小写。nProper:把文本转换为“恰当”形式（每一个单词的第一个字母大写）。nLeft:从字符串的开始返回特定数量的字符。qLeft(“Add012”,3)=“ADD”nRight:从字符串的尾部返回特定数量的字符。qRight(“Add012”,3)=01

5、2nMid:从字符串的任意位置开始返回特定数量的字符。qMid(“Add012”,2,2)=“dd”2021/9/2612用公式处理文本nTrim：除了两个单词之间的单个空格，可以去掉文本参数中的所有空格。nClean：从字符串中去掉不能打印的字符。当引入某种类型数据时，常会出现这些“垃圾”字符。nExact：比较两个字符串是否完全相同。nLen：返回字符串中的字符个数。nFind:查找某个字符在文本中的位置。nConcatenate：将两个单元格中的信息合并到一个单元格中。n使用Left和Find函数分割名字qLeft(A2,find(“”,A2)-1)qRight(A2,len(A2)-f

6、ind(“”,A2)2021/9/2613查找数值Vlookup,Hlookup,lookupn目的：查找一个值，返回另一个值。例如，查找姓名，返回电话号码。n查询分成两种：q精确查询：查询的数值如果不存在则输出不存在的标志；q不精确查询：查询的数值如果不存在则输出小于此数的最大值所对应的值。nvlookup:进行垂直查询qVlookup(查找的数值，查找的数列区域，返回值所在第几列，True(False)nTrue或确省，代表不精确匹配，查找列必须为升序排列。nFalse，代表精确匹配，不需按顺序排列。nHlookup：进行水平查询q同Vlookup相同，只不过把列操作变为行操作。nLook

7、up:查询qlookup(查找的数值，查找的数所在列或行，返回值所在列或行)n查找的数值所在的列必须为升序排列；n为不精确查询。2021/9/2614与时间相关的公式n您所看到的日期与Excel的内部日期表示是不同的。对您来说，2005年8月22日是一个带有年、月、日的日期。而对Excel来说，2005年8月22日是序列数38586。n对于Excel，凌晨12:00点则是0，而中午12:00点则是小数0.5。2021/9/2615与时间相关的公式nNow(),返回当前的时间。nToday(),返回当前的日期。nA1-B1:计算两个日期之前的天数。nYear(A1),moth(A1),day(A

8、1),hour(A1),minute(A1),Second(A1):返回某事件表示的各细项。nNetworkdays(A1,B1):计算两个日期之前的工作日天数。nWorkday(A1,B1,C1)：从某日期开始，经过指定数目的工作日之后的日期。n2021/9/2616公式错误n有时Excel会由于公式错误而无法进行计算。如果出现这种情况，您将在单元格中看到一个错误值，而不是结果。以下是三个常见的错误值：q#列的宽度不够显示该单元格中的内容。可以通过下列方法进行改正：增加列宽、缩减内容以适应列宽或者应用其他数字格式。qq#REF!单元格引用无效。单元格可能被删除或粘贴覆盖。q#NAME?函数名

9、拼写错误或者使用了Excel不能识别的名字。您应该知道，带有错误值（如#NAME?）的单元格可能显示一个彩色的三角形。2021/9/2617引用类型n相对公式中的每个相对引用单元格在公式被沿列或跨行复制时都自动改变。n绝对一个绝对单元格引用是固定的。绝对引用在您将一个公式从一个单元格复制到另一个单元格时不发生变化。绝对引用中包含美元符号($)，如$D$9。n混合型混合单元格引用既可以包含一个绝对列和一个相对行，也可以包含一个绝对行和一个相对列。例如，$A1是一个到列A的绝对引用和到行1的相对引用。2021/9/2618给区域命名n为了方面的撰写公式，Excel可以给区域进行命名q命名：n选择区

10、域，在名称框内写入名称即可。n或者插入名称定义，选择区域与名称q删除命名：n插入名称定义，选择名称，然后删除2021/9/2619查找更多的函数1.“插入”函数”，选择函数搜索需要的函数函数分类选择具体的函数选中函数的简易帮助选中函数的详细帮助2021/9/2620查找更多的函数2.填写函数参数。填写参数参数帮助2021/9/2621打印公式n公式在一般情况下仅显示结果。n也可以显示工作表中的所有公式，然后再隐藏所有公式。n“工具”“公式审核”“公式审核模式”。要隐藏公式，再单击“公式审核模式”。n快捷键CTRL+（“1”键旁边）组合键来显示和隐藏公式。2021/9/2622灵活运用公式的案例

11、n1，在两个名单里面查找匹配n2，在同一个名单里面找出重复的样本n3，用公式完成报告n4，随机抽选样本2021/9/2623目录1.公式和函数2.其他功能其他功能1.分列分列2.高级筛选高级筛选3.条件格式条件格式4.分类汇总分类汇总5.数据有效性数据有效性3.图表的制作2021/9/2624分列n分列可以将一条数据转换为多条数据：n操作步骤qStep1:”数据”分列”。qStep2:选择分列方式：分割符号或者固定宽度qStep3:在相应的位置填写分列的符号，例如本例在“其他”栏中填写“.”。qStep4:下一步完成，即可。n注意：分列会由一列生成多列，所以在分之前一定要预留空白位置，以防止覆

12、盖掉后面的数据。2021/9/2625高级筛选n“数据区域”选择所有的数据和表头；n“条件区域”选择筛选条件，如例题；n可以选择不重复的记录；n可以选择将筛选结果复制到其他位置。2021/9/2626条件格式n条件格式可以突出显示符合条件的单元格。例如，可以通过设置使区域内的所有负数背景颜色为红色。n条件格式为动态的，如果把单元格写入一个负数，既可以出现红色背景，改为正数则背景消失。2021/9/2627条件格式的使用n“格式”条件格式”，打开下列对话框。选择满足条件的格式选择是数值还是公式选择条件，如大于，小于，介于等条件限制添加其他条件，最多三个条件2021/9/2628条件格式的使用n条

13、件格式可以用于：q自动标记出错误、异常、值得注意的数据q自动描绘整体表格格式2021/9/2629分类汇总n选择要进行分类汇总的单元格区域。如下列数据省份ASNAMEQ8.1_HRQ8B_HRQ8C_HRQ8D_HRQ23_HRQ24_HR安徽 邓中亚3535.2安徽 邓中亚4331.安徽 邓中亚455544安徽 邓中亚433240安徽 邓中亚4332.安徽 邓中亚45554.安徽 邓中亚4332.安徽 邓中亚4555.安徽 邓中亚155551安徽 邓中亚5555.北京 孙春渊455552北京 孙春渊455541甘肃 张志学555341甘肃 张志学4554.甘肃 张志学555545甘肃 张志学

14、5335422021/9/2630分类汇总n“数据”分类汇总”，弹出如下对话框。选择分类的标准选择数据汇总的方式，如求和，求平均值。定需要汇总的数据列汇总结果的输出位置2021/9/2631分类汇总n汇总结果见右图。n注意分类字段必须要排序。2021/9/2632有效性nExcel的数据有效性特性在很多方面类似于条件格式特性。这个特性使用户可以建立一定的规则，它规定可以向单元格中输入的内容。n例如，在某些指定单元格中只能输入。2021/9/2633目录1.公式和函数2.其他功能3.图表的制作图表的制作1.创建图表创建图表2.选择合适的图表类型选择合适的图表类型3.复杂图表制作复杂图表制作4.选

15、择具有专业外观的图表选择具有专业外观的图表2021/9/2634创建图表n图表可以分为内嵌图表和独立图表两种。q嵌入图表是指该图表作为“对象”与数据放置在同一张工作表上。它可以移动和调整大小。它还可以与源数据一起打印。q独立图表为单独的一页，可以直接打印n创建图表的两种方法q1用图表向导创建图表q2用“图表”工具栏创建简单的图表2021/9/2635使用图表向导创建图表n图表用于将数据转化成图片。2021/9/2636图表向导第一步Step1：选择希望插入图表中的数据，同时选择列标题和行标题。Step2：单击工具栏上的“图表向导”按钮打开“图表向导”。Step3：当该向导打开时，会自动选择柱形

16、图类型。您可以方便地选择其他图表类型。Step4：接下来，您可以单击向导底部的“完成”按钮。2021/9/2637图表向导第二步n可以通过选择系列产生在“行”或“列”，来选择数据比较的方式。2021/9/2638“系列”选项卡n在该选项卡上，您可以为图表删除或添加数据系列。例如，您可能决定只将其中两个月的数据插入图表，而不是将您在工作表上选择的所有三个月的数据插入图表。通过该选项卡可以进行更改，而无须返回到工作表，并且可以预览所做的更改。n注意注意在该选项卡上删除或添加数据系列时，并不会改变工作表上的数据。2021/9/2639图表向导第三步n为图表添加说明性的文字是一个好习惯。n“标题”选项

17、卡上有三个文本框，对应于该图表的三个标题：一个对应于图表标题，位于最上端；另外两个分别对应于图表的两个坐标轴（水平和垂直）。在输入标题后，它们将出现在该选项卡的预览中。2021/9/2640图表向导第三步n“图表向导”中还有更多的选项卡，q坐标轴坐标轴您可以在此处隐藏或显示沿坐标轴显示的信息。q网格线网格线您可以在此处隐藏或显示贯穿图表的线条。q图例图例您可以在此处将图表图例放置于图表的不同位置。q数据标志数据标志您可以在此处使用各个值的行和列标题（以及数值本身）为图表加上标签。请小心操作，因为很容易使图表变得混乱并且难于阅读。q数据表数据表您可以在此处显示包含用于创建图表的所有数据的表。如果

18、您要将图表放置于工作簿中的独立工作表上，并且希望通过图表查看数据，您可能需要这么做。接下来是下一个。2021/9/2641图表向导第四步n选择“作为新工作表输入”，则单独为新的一页工作表，您可以为其选择标题。n选择“作为其中的对象插入”，它将与用于创建它的工作表数据在同一个工作表中。n在图表向导任何一步中，都可以点击“完成”来结束图标的制作。2021/9/2642选择正确的图表类型n选择最有效的图表类型将使您的数据更清晰、更有力，同时能够表达更多信息。MicrosoftOfficeExcel提供了很多种图表类型以及用于选择和预览它们的简单方法。2021/9/2643选择正确的图表类型Corre

19、lationCorrelation关关 连连 性性 FrequencyFrequency频频 率率 Time series Time series 时时 间间 顺序顺序 ItemItem项项 目目 componentcomponent成成 分分 PiePie圆圆 型型 BarBar横横 条条 型型 ColumnColumn直直 条条 型型 LineLine线线 型型 DotDot点点 型型 基基 本本 图图 表表 对比对比 2021/9/2644创建组合图表创建组合图表n有时，一种图表类型最适合于一部分数据，而另一种图表类型最适合于其余数据。您不必为此左右为难，因为利用Excel，您可以将两种

20、或更多类型的图表组合在单个图表中。2021/9/2645创建组合图表创建组合图表n右键单击其中一个白色柱形。n单击快捷菜单上的“图表类型”。n在“图表类型”列表中，单击“折线图”。在“子图表类型”区域中，单击中间行中的第一个图表，然后单击“确定”。2021/9/2646创建次坐标轴n有时，组合图表中的不同数据集之间的差异如此巨大，以至于有的数据系列无法清楚的表现，例如一组数据为数值，而另外一组为百分比。n您可以通过添加另一个（次）图表坐标轴来解决这个问题。2021/9/2647创建次坐标轴n单击图表区域外部的单元格选中该图表（在它的四个角上将显示黑色正方形。n右键单击图表底部附近的“佣金”折线

21、，然后单击“数据系列格式”。（如果在菜单上看到的是“数据点格式”，表明您选择了折线上的某个点而不是整个数据系列。在这种情况下，请单击空白图表区域并重试。）n单击“坐标轴”选项卡，单击“次坐标轴”，然后单击“确定”。2021/9/2648将“列表”功能用于图表n无论您何时更改工作表数据，Excel都会立即更新任何使用该数据的图表。但如果您将一个完整的新数据行添加到工作表中，会发生什么现象呢？图表不会更改。除非该图表使用的数据已经应用了“列表”功能。n“列表”功能是Excel2003中新增的一种使图表保持最新的方法。n使用Excel2003“数据”菜单上的“列表”命令将您的数据添加到列表中，然后使

22、用该数据创建图表，则添加到该列表底部的任何新行都会自动添加到图表中。2021/9/2649将“列表”功能用于图表n单击数据中的任一单元格。“数据”“列表”“创建列表”。单击“确定”。现在数据就变成了一个列表，列表中的每列都在标题行中具有“自动筛选”箭头。n单击“常用”工具栏上的“图表向导”按钮。（或者在“插入”菜单上，单击“图表”。）柱形图类型已选中。单击“完成”。n单击列表数据内的任何位置。例如，单击单元格D2。列表周围将出现深蓝色边框，而列表数据的底部将出现一个输入行。现在，您要将一些新数据添加到列表中。n在单元格A5中，键入“金龙金龙”（只须在单元格中的星号上面键入），然后按Enter。

23、金龙的名字将自动添加到图表中。在将数据转换成列表之前，请与前面的第2步进行比较。2021/9/2650创建具有专业外观的图表n通过简单的、内置的格式效果使数据更加引人注意。2021/9/2651创建具有专业外观的图表n去除不必要的网格线。n添加确切的数字n更改颜色并使用填充效果。n删除或添加图例，图标的边框。n图表周围添加阴影。n更改字体。q若要更改单个标题（例如，图表标题），选择要更改的标题，转到“格式”工具栏上的“字体”框，然后单击需要的字体。q若要同时更改所有字体，选择整个图表框，然后单击“字体”框中的某种字体。n添加趋势线。2021/9/2652创建具有专业外观的图表n改变柱形图的宽度，折线的宽度n更改坐标轴起始点。n更改图表上数据的顺序。n单击图表最外侧的边缘可选择图表区。单击内部选择绘图区。n可以将图片粘贴到图上，使图表更加生动。2021/9/2653END2021/9/2654