Excel中级培训教程

Excel培训—中级Excel中级培训目标----Excel方法论能够熟练的运用各种公式来完成Excel计算任务。方便的处理Excel中的文字信息。对Excel提供的各种功能有所了解，并且能够灵活的在日常工作中运用。绘制图表，美化图表。中级培训内容----Excel方法论1.用公式简化你的Excel工作2.了解Excel提供的各种功能3.绘制图表辅助数据阅读目录1.用公式简化你的Excel工作1.公式简介2.常用的计算公式和函数3.一次键入和自动填充4.引用格式5.逻辑公式6.文字处理公式7.与时间相关的公式8.其他9.选择性粘贴2.其他功能3.图表的制作公式和函数简介公式和函数使得电子表格变得非常有用。尤其是在处理大量数据的时候。使用Excel中的公式来计算电子表格中的数据以得到结果。当数据更新后，无需做额外的工作，公式将自动更新结果。函数可以大大加强公式的功能和进行普通运算符不能完成的计算。Excel包括300多个函数，甚至可以从第三方供应商处购买额外的专用函数甚至可以创建自己的定值函数（VBA），但是通常使用的也只有十几个函数。公式和函数简介公式由下列几个元素组成：运算符，如“+”，“*”；单元格（或区域）引用；数值或文本；工作表函数，如“sum”，“average”；例如：=sum(A1*B1,A2*B2,A3*B3)/3函数是可以嵌套的。例如：=sum(average(A1：C1),average(A2:C2))必须记住的函数常用计算公式和函数：运算符号：+、-、*、/、^逻辑判断符号：=,,,,=,=逻辑运算函数：And(,),Or(,)Not()+*也可以用于逻辑运算条件函数：If(,,)数字计算Average(A1:A10)平均数Sum(A1:E10)总数max(A1:E10)最大值min(A1:E10)最小值Count(A1:E10)计数ABS()绝对值Rnd()随机数注意：&不是逻辑运算符号自动计算部分点击右键在表格下方区域，同时可以完成相同的命令。高级计算Sumproduct：乘积和Sumproduct（数据列1：数据列2）主要用于加权计算、知道样本量与百分比求总体等Rank:排序rank(number,区域，0或1)，0为降序，1为升序。条件函数If是Excel常用的命令。它的基本语句是:If（逻辑判断，true-value，false-value）例如：If(A160,”pass”,”fail”).If(and(A160,A260),”allpass”,”fail”).If可以与其他的命令组合使用，例如：Sumif：sumif(区域，”0”，求和区域）Countif：countif(区域，”0”）逻辑公式TrueFalse{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可以给区域进行命名命名：选择区域，在名称框内写入名称即可。或者插入名称定义，选择区域与名称删除命名：插入名称定义，选择名称，然后删除查找更多的函数1.“插入””函数”，选择函数搜索需要的函数函数分类选择具体的函数选中函数的简易帮助选中函数的详细帮助查找更多的函数2.填写函数参数。填写参数参数帮助打印公式公式在一般情况下仅显示结果。也可以显示工作表中的所有公式，然后再隐藏所有公式。“工具”“公式审核”“公式审核模式”。要隐藏公式，再单击“公式审核模式”。快捷键CTRL+`（“1”键旁边）组合键来显示和隐藏公式。灵活运用公式的案例1，在两个名单里面查找匹配2，在同一个名单里面找出重复的样本3，用公式完成报告4，随机抽选样本目录1.公式和函数2.其他功能1.分列2.高级筛选3.条件格式4.分类汇总5.数据有效性3.图表的制作分列分列可以将一条数据转换为多条数据：操作步骤Step1:”数据””分列”。Step2:选择分列方式：分割符号或者固定宽度Step3:在相应的位置填写分列的符号，例如本例在“其他”栏中填写“.”。Step4:下一步完成，即可。注意：分列会由一列生成多列，所以在分之前一定要预留空白位置，以防止覆盖掉后面的数据。高级筛选“数据区域”选择所有的数据和表头；“条件区域”选择筛选条件，如例题；可以选择不重复的记录；可以选择将筛选结果复制到其他位置。条件格式条件格式可以突出显示符合条件的单元格。例如，可以通过设置使区域内的所有负数背景颜色为红色。条件格式为动态的，如果把单元格写入一个负数，既可以出现红色背景，改为正数则背景消失。条件格式的使用“格式””条件格式”，打开下列对话框。选择满足条件的格式选择是数值还是公式选择条件，如大于，小于，介于等条件限制添加其他条件，最多三个条件条件格式的使用条件格式可以用于：自动标记出错误、异常、值得注意的数据自动描绘整体表格格式分类汇总选择要进行分类汇总的单元格区域。如下列数据省份ASNAMEQ8.1_HRQ8B_HRQ8C_HRQ8D_HRQ23_HRQ24_HR安徽邓中亚3535.2安徽邓中亚4331..安徽邓中亚455544安徽邓中亚433240安徽邓中亚4332..安徽邓中亚45554.安徽邓中亚4332..安徽邓中亚4555..安徽邓中亚155551安徽邓中亚5555..北京孙春渊455552北京孙春渊455541甘肃张志学555341甘肃张志学4554..甘肃张志学555545甘肃张志学533542分类汇总“数据””分类汇总”，弹出如下对话框。选择分类的标准选择数据汇总的方式，如求和，求平均值。定需要汇总的数据列汇总结果的输出位置分类汇总汇总结果见右图。注意分类字段必须要排序。有效性Excel的数据有效性特性在很多方面类似于条件格式特性。这个特性使用户可以建立一定的规则，它规定可以向单元格中输入的内容。例如，在某些指定单元格中只能输入。目录1.公式和函数2.其他功能3.图表的制作1.创建图表2.选择合适的图表类型3.复杂图表制作4.选择具有专业外观的图表创建图表图表可以分为内嵌图表和独立图表两种。嵌入图表是指该图表作为“对象”与数据放置在同一张工作表上。它可以移动和调整大小。它还可以与源数据一起打印。独立图表为单独的一页，可以直接打印创建图表的两种方法1．用图表向导创建图表2．用“图表”工具栏创建简单的图表使用图表向导创建图表图表用于将数据转化成图片。图表向导第一步Step1：选择希望插入图表中的数据，同时选择列标题和行标题。Step2：单击工具栏上的“图表向导”按钮打开“图表向导”。Step3：当该向导打开时，会自动选择柱形图类型。您可以方便地选择其他图表类型。Step4：接下来，您可以单击向导底部的“完成”按钮。图表向导第二步可以通过选择系列产生在“行”或“列”，来选择数据比较的方式。“系列”选项卡在该选项卡上，您可以为图表删除或添加数据系列。例如，您可能决定只将其中两个月的数据插入图表，而不是将您在工作表上选择的所有三个月的数据插入图表。通过该选项卡可以进行更改，而无须返回到工作表，并且可以预览所做的更改。注意在该选项卡上删除或添加数据系列时，并不会改变工作表上的数据。图表向导第三步为图表添加说明性的文字是一个好习惯。“标题”选项卡上有三个文本框，对应于该图表的三个标题：一个对应于图表标题，位于最上端；另外两个分别对应于图表的两个坐标轴（水平和垂直）。在输入标题后，它们将出现在该选项卡的预览中。图表向导第三步“图表向导”中还有更多的选项卡，坐标轴您可以在此处隐藏或显示沿坐标轴显示的信息。网格线您可以在此处隐藏或显示贯穿图表的线条。图例您可以在此处将图表图例放置于图表的不同位置。数据标志您可以在此处使用各个值的行和列标题（以及数值本身）为图表加上标签。请小心操作，因为很容易使图表变得