Excel中级培训教程

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel,培训,中级,Excel,中级培训目标,-Excel,方法论,能够熟练的运用各种公式来完成,Excel,计算任务。,方便的处理,Excel,中的文字信息。,对,Excel,提供的各种功能有所了解，并且能够灵活的在日常工作中运用。,绘制图表，美化图表。,中级培训内容,-Excel,方法论,用公式简化你的,Excel,工作,了解,Excel,提供的各种功能,绘制图表辅助数据阅读,目录,用公式简化你的,Excel,工作,公式简介,常用的计算公式和函数,一次键入和自动填充,引用格式,逻辑公式,文字处理公式,与时间相关的公式,其他,选择性粘贴,其他功能,图表的制作,公式和函数简介,公式和函数使得电子表格变得非常有用。尤其是在处理大量数据的时候。,使用,Excel,中的公式来计算电子表格中的数据以得到结果。当数据更新后，无需做额外的工作，公式将自动更新结果。,函数可以大大加强公式的功能和进行普通运算符不能完成的计算。,Excel,包括,300,多个函数，甚至可以从第三方供应商处购买额外的专用函数甚至可以创建自己的定值函数（,VBA,），但是通常使用的也只有十几个函数。,公式和函数简介,公式由下列几个元素组成：,运算符，如“,+”,，“*”；,单元格（或区域）引用；,数值或文本；,工作表函数，如“,sum”,，“,average”,；,例如：,=sum(A1*B1,A2*B2,A3*B3)/3,函数是可以嵌套的。,例如：,=sum(average(A1,：,C1),average(A2:C2),必须记住的函数,常用计算公式和函数：,运算符号：,+,、,-,、*、,/,、,逻辑判断符号：,=, , =, 60,”pass”,”fail”).,If(and(A160,A260),”all pass”, ”fail”).,If,可以与其他的命令组合使用，例如：,Sumif,：,sumif,(,区域，,”,0”,，求和区域）,Countif,：,countif,(,区域，,”,0”,）,逻辑公式,True False 1,，,0,And(),，,OR(), Not(),在,Excel,公式中，逻辑判断中使用到,and,或,or,的格式是：,and,（ ， ）；,or,（ ， ）,;not( ),例如,If(and(A11,B11,C11),1,0),If(or(A11,B11,C11),1,0),If(not(A11),1,0),用公式处理文本,Upper:,把文本全部转换为大写。,Lower:,把文本全部转换为小写。,Proper:,把文本转换为“恰当”形式,（每一个单词的第一个字母大写）。,Left:,从字符串的开始返回特定数量的字符。,Left(“Add012”,3)=“ADD”,Right:,从字符串的尾部返回特定数量的字符。,Right(“Add012”,3)=012,Mid:,从字符串的任意位置开始返回特定数量的字符。,Mid(“Add012”,2,2)=“,dd,”,用公式处理文本,Trim,：除了两个单词之间的单个空格，可以去掉文本参数中的所有空格。,Clean,：从字符串中去掉不能打印的字符。当引入某种类型数据时，常会出现这些“垃圾”字符。,Exact,：比较两个字符串是否完全相同。,Len,：返回字符串中的字符个数。,Find:,查找某个字符在文本中的位置。,Concatenate,：将两个单元格中的信息合并到一个单元格中 。,使用,Left,和,Find,函数分割名字,Left(A2,find(“”,A2)-1),Right(A2,len(A2)-find(“”,A2),查找数值,Vlookup,Hlookup,lookup,目的：查找一个值，返回另一个值。例如，查找姓名，返回电话号码。,查询分成两种：,精确查询：查询的数值如果不存在则输出不存在的标志；,不精确查询：查询的数值如果不存在则输出小于此数的最大值所对应的值。,vlookup,:,进行垂直查询,Vlookup,(,查找的数值，查找的数列区域，返回值所在第几列，,True(False,),True,或确省，代表不精确匹配，查找列必须为升序排列。,False,，代表精确匹配，不需按顺序排列。,Hlookup,：进行水平查询,同,Vlookup,相同，只不过把列操作变为行操作。,Lookup:,查询,lookup(,查找的数值，查找的数所在列或行，返回值所在列或行,),查找的数值所在的列必须为升序排列；,为不精确查询。,与时间相关的公式,您所看到的日期与,Excel,的内部日期表示是不同的。对您来说，,2005,年,8,月,22,日是一个带有年、月、日的日期。而对,Excel,来说，,2005,年,8,月,22,日是序列数,38586,。,对于,Excel,，凌晨,12:00,点则是,0,，而中午,12:00,点则是小数,0.5,。,与时间相关的公式,Now(),返回当前的时间。,Today(),返回当前的日期。,A1-B1:,计算两个日期之前的天数。,Year(A1),moth(A1),day(A1),hour(A1),minute(A1),Second(A1):,返回某事件表示的各细项。,Networkdays(A1,B1):,计算两个日期之前的工作日天数。,Workday(A1,B1,C1),：从某日期开始，经过指定数目的工作日之后的日期 。,公式错误,有时,Excel,会由于公式错误而无法进行计算。如果出现这种情况，您将在单元格中看到一个错误值，而不是结果。以下是三个常见的错误值：,#,列的宽度不够显示该单元格中的内容。可以通过下列方法进行改正：增加列宽、缩减内容以适应列宽或者应用其他数字格式。,#REF!,单元格引用无效。单元格可能被删除或粘贴覆盖。,#NAME?,函数名拼写错误或者使用了,Excel,不能识别的名字。您应该知道，带有错误值（如,#NAME?,）的单元格可能显示一个彩色的三角形。,引用类型,相对 公式中的每个相对引用单元格在公式被沿列或跨行复制时都自动改变。,绝对 一个绝对单元格引用是固定的。绝对引用在您将一个公式从一个单元格复制到另一个单元格时不发生变化。绝对引用中包含美元符号,($),，如,$D$9,。,混合型 混合单元格引用既可以包含一个绝对列和一个相对行，也可以包含一个绝对行和一个相对列。例如，,$A1,是一个到列,A,的绝对引用和到行,1,的相对引用。,给区域命名,为了方面的撰写公式，,Excel,可以给区域进行命名,命名：,选择区域，在名称框内写入名称即可。,或者 插入,名称,定义，选择区域与名称,删除命名：,插入,名称,定义， 选择名称，然后删除,查找更多的函数,“插入”,”,函数”，选择函数,搜索需要,的函数,函数分类,选择具体的函数,选中函数的,简易帮助,选中函数的,详细帮助,查找更多的函数,填写函数参数。,填写参数,参数帮助,打印公式,公式在一般情况下仅显示结果。,也可以显示工作表中的所有公式，然后再隐藏所有公式。,“工具”,“,公式审核”,“,公式审核模式”。要隐藏公式，再单击“公式审核模式”。,快捷键,CTRL+,（“,1”,键旁边）组合键来显示和隐藏公式。,灵活运用公式的案例,1,，在两个名单里面查找匹配,2,，在同一个名单里面找出重复的样本,3,，用公式完成报告,4,，随机抽选样本,目录,公式和函数,其他功能,分列,高级筛选,条件格式,分类汇总,数据有效性,图表的制作,分列,分列可以将一条数据转换为多条数据：,操作步骤,Step 1:”,数据”,”,分列”。,Step 2:,选择分列方式：分割符号 或者 固定宽度,Step 3:,在相应的位置填写分列的符号，例如本例在“其他”栏中填写“,.”,。,Step 4:,下一步,完成，即可。,注意：分列会由一列生成多列，所以在分之前一定要预留空白位置，以防止覆盖掉后面的数据。,高级筛选,“数据区域”选择所有的数据和表头；,“条件区域”选择筛选条件，如例题；,可以选择不重复的记录；,可以选择将筛选结果复制到其他位置。,条件格式,条件格式可以突出显示符合条件的单元格。例如，可以通过设置使区域内的所有负数背景颜色为红色。,条件格式为动态的，如果把单元格写入一个负数，既可以出现红色背景，改为正数则背景消失。,条件格式的使用,“格式”,”,条件格式”，打开下列对话框。,选择满足,条件的格式,选择是数值,还是公式,选择条件，如大于，,小于，介于等,条件限制,添加其他条件，,最多三个条件,条件格式的使用,条件格式可以用于：,自动标记出错误、异常、值得注意的数据,自动描绘整体表格格式,分类汇总,选择要进行分类汇总的单元格区域。如下列数据,省份,ASNAME,Q8.1_HR,Q8B_HR,Q8C_HR,Q8D_HR,Q23_HR,Q24_HR,安徽,邓中亚,3,5,3,5,.,2,安徽,邓中亚,4,3,3,1,.,.,安徽,邓中亚,4,5,5,5,4,4,安徽,邓中亚,4,3,3,2,4,0,安徽,邓中亚,4,3,3,2,.,.,安徽,邓中亚,4,5,5,5,4,.,安徽,邓中亚,4,3,3,2,.,.,安徽,邓中亚,4,5,5,5,.,.,安徽,邓中亚,1,5,5,5,5,1,安徽,邓中亚,5,5,5,5,.,.,北京,孙春渊,4,5,5,5,5,2,北京,孙春渊,4,5,5,5,4,1,甘肃,张志学,5,5,5,3,4,1,甘肃,张志学,4,5,5,4,.,.,甘肃,张志学,5,5,5,5,4,5,甘肃,张志学,5,3,3,5,4,2,分类汇总,“数据”,”,分类汇总” ，弹出如下对话框。,选择分类的标准,选择数据汇总的方式，,如求和，求平均值。,定需要汇总的数据列,汇总结果的,输出位置,分类汇总,汇总结果见右图。,注意分类字段必须要排序。,有效性,Excel,的数据有效性特性在很多方面类似于条件格式特性。这个特性使用户可以建立一定的规则，它规定可以向单元格中输入的内容。,例如，在某些指定单元格中只能输入。,目录,公式和函数,其他功能,图表的制作,创建图表,选择合适的图表类型,复杂图表制作,选择具有专业外观的图表,创建图表,图表可以分为内嵌图表和独立图表两种。,嵌入图表是指该图表作为“对象”与数据放置在同一张工作表上。它可以移动和调整大小。它还可以与源数据一起打印。,独立图表为单独的一页，可以直接打印,创建图表的两种方法,1,用图表向导创建图表,2,用“图表”工具栏创建简单的图表,使用图表向导创建图表,图表用于将数据转化成图片。,图表向导第一步,Step1,：选择希望插入图表中的数据，同时选择列标题和行标题 。,Step2,：单击工具栏上的“图表向导” 按钮打开“图表向导”。,Step3,：当该向导打开时，会自动选择柱形图类型。您可以方便地选择其他图表类型 。,Step4,：接下来，您可以单击向导底部的“完成”按钮 。,图表向导第二步,可以通过选择系列产生在“行”或“列”，来选择数据比较的方式。,“系列”选项卡,在该选项卡上，您可以为图表删除或添加数据系列。例如，您可能决定只将其中两个月的数据插入图表，而不是将您在工作表上选择的所有三个月的数据插入图表。通过该选项卡可以进行更改，而无须返回到工作表，并且可以预览所做的更改。,注意,在该选项卡上删除或添加数据系列时，并不会改变工作表上的数据。,图表向导第三步,为图表添加说明性的文字是一个好习惯。,“标题” 选项卡上有三个文本框，对应于该图表的三个标题：一个对应于图表标题，位于最上端；另外两个分别对应于图表的两个坐标轴（水平和垂直）。在输入标题后，它们将出现在该选项卡的预览中。,图表向导第三步,“图表向导”中还有更多的选项卡，,坐标轴,您可以在此处隐藏或显示沿坐标轴显示的信息。,网格线,您可以在此处隐藏或显示贯穿图表的线条。,图例,您可以在此处将图表图例放置于图表的不同位置。,数据标志,您可以在此处使用各个值的行和列标题（以及数值本身）为图表加上标签。请小心操作，因为很容易使图表变得混乱并且难于阅读。,数据表,您可以在此处显示包含用于创建图表的所有数据的表。如果您要将图表放置于工作簿中的独立工作表上，并且希望通过图表查看数据，您可能需要这么做。接下来是下一个。,图表向导第四步,选择“作为新工作表输入,”,，则单独为新的一页工作表，您可以为其选择标题。,选择“作为其中的对象插入,”,，它将与用于创建它的工作表数据在同一个工作表中。,在图表向导任何一步中，都可以点击“完成”来结束图标的制作。,选择正确的图表类型,选择最有效的图表类型将使您的数据更清晰、更有力，同时能够表达更多信息。,Microsoft Office Excel,提供了很多种图表类型以及用于选择和预览它们的简单方法。,选择正确的图表类型,Correlation,关 连 性,Frequency,频 率,Time series,时 间 顺序,Item,项 目,component,成 分,Pie,圆 型,Bar,横 条 型,Column,直 条 型,Line,线 型,Dot,点 型,基 本 图 表,对比,创建组合图表,有时，一种图表类型最适合于一部分数据，而另一种图表类型最适合于其余数据。您不必为此左右为难，因为利用,Excel,，您可以将两种或更多类型的图表组合在单个图表中。,创建组合图表,右键单击其中一个白色柱形。,单击快捷菜单上的“图表类型”。,在“图表类型”列表中，单击“折线图”。在“子图表类型”区域中，单击中间行中的第一个图表，然后单击“确定”。,创建次坐标轴,有时，组合图表中的不同数据集之间的差异如此巨大，以至于有的数据系列无法清楚的表现，例如一组数据为数值，而另外一组为百分比。,您可以通过添加另一个（次）图表坐标轴来解决这个问题。,创建次坐标轴,单击图表区域外部的单元格选中该图表（在它的四个角上将显示黑色正方形。,右键单击图表底部附近的“佣金”折线，然后单击“数据系列格式”。（如果在菜单上看到的是“数据点格式”，表明您选择了折线上的某个点而不是整个数据系列。在这种情况下，请单击空白图表区域并重试。）,单击“坐标轴”选项卡，单击“次坐标轴”，然后单击“确定”。,将“列表”功能用于图表,无论您何时更改工作表数据，,Excel,都会立即更新任何使用该数据的图表。但如果您将一个完整的新数据行添加到工作表中，会发生什么现象呢？图表不会更改。除非该图表使用的数据已经应用了“列表”功能。,“列表”功能是,Excel 2003,中新增的一种使图表保持最新的方法。,使用,Excel 2003“,数据”菜单上的“列表”命令将您的数据添加到列表中，然后使用该数据创建图表，则添加到该列表底部的任何新行都会自动添加到图表中。,将“列表”功能用于图表,单击数据中的任一单元格。“数据”,“,列表”,“,创建列表”。单击“确定”。 现在数据就变成了一个列表，列表中的每列都在标题行中具有“自动筛选”箭头。,单击“常用”工具栏上的“图表向导” 按钮。（或者在“插入”菜单上，单击“图表”。）柱形图类型已选中。单击“完成”。,单击列表数据内的任何位置。例如，单击单元格,D2,。列表周围将出现深蓝色边框，而列表数据的底部将出现一个输入行。现在，您要将一些新数据添加到列表中。,在单元格,A5,中，键入,“金龙”,（只须在单元格中的星号上面键入），然后按,Enter,。金龙的名字将自动添加到图表中。在将数据转换成列表之前，请与前面的第,2,步进行比较。,创建具有专业外观的图表,通过简单的、内置的格式效果使数据更加引人注意。,创建具有专业外观的图表,去除不必要的网格线。,添加确切的数字,更改颜色并使用填充效果。,删除或添加图例，图标的边框。,图表周围添加阴影。,更改字体。,若要更改单个标题（例如，图表标题），选择要更改的标题，转到“格式”工具栏上的“字体”框，然后单击需要的字体。,若要同时更改所有字体，选择整个图表框，然后单击“字体”框中的某种字体。,添加趋势线。,创建具有专业外观的图表,改变柱形图的宽度，折线的宽度,更改坐标轴起始点。,更改图表上数据的顺序。,单击图表最外侧的边缘可选择图表区。单击内部选择绘图区。,可以将图片粘贴到图上，使图表更加生动。,END,