Excel中级培训教程.ppt

《Excel中级培训教程.ppt》由会员分享，可在线阅读，更多相关《Excel中级培训教程.ppt（54页珍藏版）》请在优知文库上搜索。

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

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

3、(average(A1：C1),average(A2:C2)必须记住的函数n常用计算公式和函数：q运算符号：+、-、*、/、q逻辑判断符号：=, , , =, 60,”pass”,”fail”).qIf(and(A160,A260),”all pass”, ”fail”).nIf可以与其他的命令组合使用，例如：qSumif：sumif(区域，”0”，求和区域）qCountif： countif(区域，”0”）逻辑公式nTrue False 1，0nAnd()，OR(), Not()n在Excel公式中，逻辑判断中使用到and或or的格式是： and（ ， ）；or（ ， ）;not( )n例如

4、nIf(and(A11,B11,C11),1,0)nIf(or(A11,B11,C11),1,0)nIf(not(A11),1,0)用公式处理文本nUpper: 把文本全部转换为大写。nLower: 把文本全部转换为小写。nProper: 把文本转换为“恰当”形式（每一个单词的第一个字母大写）。nLeft:从字符串的开始返回特定数量的字符。qLeft(“Add012”,3)=“ADD”nRight:从字符串的尾部返回特定数量的字符。qRight(“Add012”,3)=012nMid:从字符串的任意位置开始返回特定数量的字符。qMid(“Add012”,2,2)=“dd”用公式处理文本nTri

5、m：除了两个单词之间的单个空格，可以去掉文本参数中的所有空格。nClean：从字符串中去掉不能打印的字符。当引入某种类型数据时，常会出现这些“垃圾”字符。nExact：比较两个字符串是否完全相同。nLen：返回字符串中的字符个数。nFind: 查找某个字符在文本中的位置。nConcatenate：将两个单元格中的信息合并到一个单元格中 。 n使用Left和Find函数分割名字qLeft(A2,find(“”,A2)-1)qRight(A2,len(A2)-find(“”,A2)查找数值Vlookup,Hlookup,lookupn目的：查找一个值，返回另一个值。例如，查找姓名，返回电话号码。n

6、查询分成两种：q精确查询：查询的数值如果不存在则输出不存在的标志；q不精确查询：查询的数值如果不存在则输出小于此数的最大值所对应的值。nvlookup:进行垂直查询qVlookup(查找的数值，查找的数列区域，返回值所在第几列，True(False)nTrue或确省，代表不精确匹配，查找列必须为升序排列。nFalse，代表精确匹配，不需按顺序排列。nHlookup：进行水平查询q同Vlookup相同，只不过把列操作变为行操作。nLookup:查询qlookup(查找的数值，查找的数所在列或行，返回值所在列或行)n查找的数值所在的列必须为升序排列；n为不精确查询。与时间相关的公式n您所看到的日期

7、与 Excel 的内部日期表示是不同的。对您来说，2005 年 8 月 22 日是一个带有年、月、日的日期。而对 Excel 来说，2005 年 8 月 22 日是序列数 38586。n对于 Excel，凌晨 12:00 点则是 0，而中午 12:00 点则是小数 0.5。 与时间相关的公式nNow(),返回当前的时间。nToday(),返回当前的日期。 nA1-B1:计算两个日期之前的天数。nYear(A1),moth(A1),day(A1),hour(A1),minute(A1),Second(A1):返回某事件表示的各细项。nNetworkdays(A1,B1):计算两个日期之前的工作日

8、天数。nWorkday(A1,B1,C1)：从某日期开始，经过指定数目的工作日之后的日期 。n 公式错误n有时 Excel 会由于公式错误而无法进行计算。如果出现这种情况，您将在单元格中看到一个错误值，而不是结果。以下是三个常见的错误值： q# 列的宽度不够显示该单元格中的内容。可以通过下列方法进行改正：增加列宽、缩减内容以适应列宽或者应用其他数字格式。q q#REF! 单元格引用无效。单元格可能被删除或粘贴覆盖。 q#NAME? 函数名拼写错误或者使用了 Excel 不能识别的名字。您应该知道，带有错误值（如 #NAME?）的单元格可能显示一个彩色的三角形。引用类型n相对 公式中的每个相对引

9、用单元格在公式被沿列或跨行复制时都自动改变。n绝对 一个绝对单元格引用是固定的。绝对引用在您将一个公式从一个单元格复制到另一个单元格时不发生变化。绝对引用中包含美元符号 ($)，如 $D$9。n混合型 混合单元格引用既可以包含一个绝对列和一个相对行，也可以包含一个绝对行和一个相对列。例如，$A1 是一个到列 A 的绝对引用和到行 1 的相对引用。给区域命名n为了方面的撰写公式，Excel 可以给区域进行命名q命名：n选择区域，在名称框内写入名称即可。n或者 插入 名称 定义，选择区域与名称q删除命名：n插入 名称 定义， 选择名称，然后删除查找更多的函数1.“插入”函数”，选择函数搜索需要的函

10、数函数分类选择具体的函数选中函数的简易帮助选中函数的详细帮助查找更多的函数2.填写函数参数。填写参数参数帮助打印公式n公式在一般情况下仅显示结果。n也可以显示工作表中的所有公式，然后再隐藏所有公式。n“工具”“公式审核”“公式审核模式”。要隐藏公式，再单击“公式审核模式”。 n快捷键CTRL+（“1”键旁边）组合键来显示和隐藏公式。 灵活运用公式的案例n1，在两个名单里面查找匹配n2，在同一个名单里面找出重复的样本n3，用公式完成报告n4，随机抽选样本目录1.公式和函数2.其他功能其他功能1.分列分列2.高级筛选高级筛选3.条件格式条件格式4.分类汇总分类汇总5.数据有效性数据有效性3.图表的

11、制作分列n分列可以将一条数据转换为多条数据：n操作步骤qStep 1:”数据”分列”。qStep 2:选择分列方式：分割符号 或者 固定宽度qStep 3:在相应的位置填写分列的符号，例如本例在“其他”栏中填写“.” 。qStep 4:下一步完成，即可。n注意：分列会由一列生成多列，所以在分之前一定要预留空白位置，以防止覆盖掉后面的数据。高级筛选n“数据区域”选择所有的数据和表头；n“条件区域”选择筛选条件，如例题；n可以选择不重复的记录；n可以选择将筛选结果复制到其他位置。条件格式n条件格式可以突出显示符合条件的单元格。例如，可以通过设置使区域内的所有负数背景颜色为红色。n条件格式为动态的，

12、如果把单元格写入一个负数，既可以出现红色背景，改为正数则背景消失。条件格式的使用n“格式”条件格式”，打开下列对话框。选择满足条件的格式选择是数值还是公式选择条件，如大于，小于，介于等条件限制添加其他条件，最多三个条件条件格式的使用n条件格式可以用于：q自动标记出错误、异常、值得注意的数据q自动描绘整体表格格式分类汇总n选择要进行分类汇总的单元格区域。如下列数据省份ASNAMEQ8.1_HRQ8B_HRQ8C_HRQ8D_HRQ23_HRQ24_HR安徽 邓中亚3535.2安徽 邓中亚4331.安徽 邓中亚455544安徽 邓中亚433240安徽 邓中亚4332.安徽 邓中亚45554.安徽

13、邓中亚4332.安徽 邓中亚4555.安徽 邓中亚155551安徽 邓中亚5555.北京 孙春渊455552北京 孙春渊455541甘肃 张志学555341甘肃 张志学4554.甘肃 张志学555545甘肃 张志学533542分类汇总n“数据”分类汇总” ，弹出如下对话框。选择分类的标准选择数据汇总的方式，如求和，求平均值。定需要汇总的数据列汇总结果的输出位置分类汇总n汇总结果见右图。n注意分类字段必须要排序。有效性nExcel的数据有效性特性在很多方面类似于条件格式特性。这个特性使用户可以建立一定的规则，它规定可以向单元格中输入的内容。n例如，在某些指定单元格中只能输入。目录1.公式和函数2

14、.其他功能3.图表的制作图表的制作1.创建图表创建图表2.选择合适的图表类型选择合适的图表类型3.复杂图表制作复杂图表制作4.选择具有专业外观的图表选择具有专业外观的图表创建图表n图表可以分为内嵌图表和独立图表两种。q嵌入图表是指该图表作为“对象”与数据放置在同一张工作表上。它可以移动和调整大小。它还可以与源数据一起打印。q独立图表为单独的一页，可以直接打印n创建图表的两种方法q1用图表向导创建图表q2用“图表”工具栏创建简单的图表使用图表向导创建图表n图表用于将数据转化成图片。图表向导第一步Step1：选择希望插入图表中的数据，同时选择列标题和行标题 。Step2：单击工具栏上的“图表向导”

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

16、题” 选项卡上有三个文本框，对应于该图表的三个标题：一个对应于图表标题，位于最上端；另外两个分别对应于图表的两个坐标轴（水平和垂直）。在输入标题后，它们将出现在该选项卡的预览中。 图表向导第三步n“图表向导”中还有更多的选项卡， q坐标轴坐标轴 您可以在此处隐藏或显示沿坐标轴显示的信息。 q网格线网格线 您可以在此处隐藏或显示贯穿图表的线条。 q图例图例 您可以在此处将图表图例放置于图表的不同位置。 q数据标志数据标志 您可以在此处使用各个值的行和列标题（以及数值本身）为图表加上标签。请小心操作，因为很容易使图表变得混乱并且难于阅读。 q数据表数据表 您可以在此处显示包含用于创建图表的所有数据的表。如果您要将图表放置于工作簿中的独立工作表上，并且希望通过图表查看数据，您可能需要这么做。接下来是下一个。 图表向导第四步n选择“作为新工作表输入”，则单独为新的一页工作表，您可以为其选择标题。n选择“作为其中的对象插入”，它将与用于创建它的工作表数据在同一个工作表中。n在图表向导任何一步中，都可以点击“完成”来结束图标的制作。 选择正确的图表类型n选择最有效的图表类型将使您的数据更清晰、更有