Excel培训—中级_第1页
Excel培训—中级_第2页
Excel培训—中级_第3页
Excel培训—中级_第4页
Excel培训—中级_第5页
已阅读5页,还剩49页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

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数据表数据表 您可以在此处显示包含用于创建图表的所有数据

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

18、性性 FrequencyFrequency频频 率率 Time series Time series 时时 间间 顺序顺序 ItemItem项项 目目 componentcomponent成成 分分 PiePie圆圆 型型 BarBar横横 条条 型型 ColumnColumn直直 条条 型型 LineLine线线 型型 DotDot点点 型型 基基 本本 图图 表表 对比对比 创建组合图表创建组合图表n有时,一种图表类型最适合于一部分数据,而另一种图表类型最适合于其余数据。您不必为此左右为难,因为利用 Excel,您可以将两种或更多类型的图表组合在单个图表中。 创建组合图表创建组合图表n右键

19、单击其中一个白色柱形。 n单击快捷菜单上的“图表类型”。 n在“图表类型”列表中,单击“折线图”。在“子图表类型”区域中,单击中间行中的第一个图表,然后单击“确定”。 创建次坐标轴 n有时,组合图表中的不同数据集之间的差异如此巨大,以至于有的数据系列无法清楚的表现,例如一组数据为数值,而另外一组为百分比。n您可以通过添加另一个(次)图表坐标轴来解决这个问题。 创建次坐标轴n单击图表区域外部的单元格选中该图表(在它的四个角上将显示黑色正方形。 n右键单击图表底部附近的“佣金”折线,然后单击“数据系列格式”。(如果在菜单上看到的是“数据点格式”,表明您选择了折线上的某个点而不是整个数据系列。在这种情况下,请单击空白图表区域并重试。) n单击“坐标轴”选项卡,单击“次坐标轴”,然后单击“确定”。 将“列表”功能用于图表 n无论您何时更改工作表数据,Excel 都会立即更新任何使用该数据的图表。但如果您将一个完整的新数据行添加到工作表中,会发生什么现象呢?图表不会更改。除非该图表使用的数据已经应用了“列表”功能。n“列表”功能是 Excel 2003 中新增的一种使图表保持最新的方法。n使用 Excel 2003“数据”菜单上的“列表”命令将您的

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论