EXCEL使用技巧大全(ppt格式)

ClerkOffice2003TrainingMaterial文员Office2003培训教材,Exceltrainingmanual-IntermediateMaterialExcel培训手册中级班教材,使用公式公式中的运算符公式中的运算顺序引用单元格创建公式创建公式的方法创建简单公式创建包含引用或名称的公式创建包含函数的公式使用函数常用函数财务函数日期和时间函数数学与三角函数统计函数函数的输入手工输入利用函数向导输入求和计算扩充的自动求和条件求和,建立数据清单建立数据清单的准则使用记录单数据排序对数据清单进行排序创建自定义排序数据筛选自动筛选高级筛选分类汇总分类汇总的计算方法汇总报表和图表插入和删除分类汇总插入图表表示数据复杂的数据链接使用数据透视表创建数据透视表编辑数据透视表controlltoolboxusage(控件工具箱),课程大纲,使用公式,公式中的运算符算术运算符,使用公式,公式中的运算符2.文本运算符“2.含有运算符和运算对象;3.能产生结果公式的3个基本组成部分:数值:包括数字和文本单元格引用操作符,创建公式,创建公式的方法使用编辑栏直接在单元格中输入创建3种类型的公式创建简单公式;2.创建包含引用或名称的公式3.创建包含函数的公式,使用函数,函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。函数是一个已经提供给用户的公式,并且有一个描述性的名称。函数结构函数名称：如果要查看可用函数的列表,可以单击一个单元格并按Shift+F3键；参数：参数可以是数字、文本、逻辑值（TRUE或FALSE）、数值、错误值（如#N/A）或单元格引用。参数工具提示：在输入函数时,会出现一个带有语法和参数的工具提示。输入公式：“插入函数”对话框。函数的参数函数的参数是一个函数体中括号内的部分,逻辑值:FALSE:需要完全一致TRUE:相似值亦可转换,使用函数,函数的类型1.常用函数:常用函数指经常使用的函数,如求和或计算算术平均值等.包括SUM,AVERAGE,ISPMT,IF,HYPERLINK,COUNT,MAX,SIN,SUMIF和PMT.它们的语法和作用如下表:,使用函数,函数的类型2.财务函数:财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付的金额,它们的变量紧密相互关联。如：PMT(Rate,Nper,Pv,Fv,Type)=PMT（0.006,360,-300000)其中,0.006是每一笔贷款项的利率,360是款项的数目,-300000是360笔款项的当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数),注:Rate贷款利率。Nper该项贷款的付款总数。Pv现值,或一系列未来付款的当前值的累积和,也称为本金。Fv为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv则假设其值为零,也就是一笔贷款的未来值为零。Type数字0或1,用以指定各期的付款时间是在期初还是期末。,使用函数,函数的类型3.时间和日期函数:,主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY和YEAR等。,例：函数DATA语法：DATE(year,month,day),在单元格中插入当前日期和时间插入静态的日期和时间当前日期选取一个单元格,并按Ctrl+;当前时间选取一个单元格,并按Ctrl+Shift+；当前日期和时间选取一个单元格,并按Ctrl+;,然后按空格键,最后按Ctrl+Shift+;插入会更新的日期和时间使用TODAY和NOW函数完成这一工作。,小贴士,使用函数,函数的类型4.数学与三角函数:5.统计函数:统计函数是用来对数据区域进行统计分析。,主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN和TUUNC等。,例：ABS函数语法：ABS(number),函数是一种特殊的公式,因此所有的函数要以“=”开始。使用方法如下：1.选中要输入函数的单元格2.单击Insert菜单中的命令,弹出如图所示的对话框：,使用函数,3.在上图的对话框中,选择函数分类下面的函数类型及函数名下面的对应函数名。4.单击确定按钮,弹出如图所示的相应的调用函数对话框：,使用函数,函数的使用(SUM),SUM函数函数名称：SUM主要功能：计算所有参数数值的和。使用格式：SUM（Number1,Number2）参数说明：Number1、Number2代表需要计算的值,可以是具体的数值、引用的单元格（区域）、逻辑值等。特别提醒：如果参数为数组或引用,只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：=SUM(LARGE(D2:D63,1,2,3,4,5),则可以求出前5名成绩的和。,函数的使用(AVERAGE),函数名称：AVERAGE主要功能：求出所有参数的算术平均值。使用格式：AVERAGE(number1,number2,)参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域）,参数不超过30个。特别提醒：如果引用区域中包含“0”值单元格,则计算在内；如果引用区域中包含空白或字符单元格,则不计算在内。,函数的使用(IF),IF函数函数名称：IF主要功能：根据对指定条件的逻辑判断的真假结果,返回相对应的内容。使用格式：=IF(Logical,Value_if_true,Value_if_false)参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容,如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容,如果忽略返回“FALSE”。说明:函数IF可以嵌套七层,用value_if_false及value_if_true参数可以构造复杂的检测条件。在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。如果函数IF的参数包含数组，则在执行IF语句时，数组中的每一个元素都将计算。,函数的使用(COUNTIF单击常用工具栏中的Autosum按钮,则下一单元格中将出现计算的结果。,数据的自动计算与排序,如果要自动求平均值,就可按下面的操作进行：选中要进行计算的区域在状态栏中单击鼠标右键,弹出如图所示的快捷键菜单。单击Average命令,则在状态栏中就会出现求均值的结果。,数据的自动计算与排序(续1),数据的自动计算与排序(续2),B.数据的排序排序是根据某一指定的数据的顺序重新对行的位置进行调整,操作步骤如下：1）单击某一字段名。2）单击工具栏的升序按钮或降序按钮,则整个数据库该列被排序。3）单击Data菜单中的sort命令,弹出sort对话框如图所示。,4)在sortby下面的编缉框中,选出需排序的关键字,单击Ascending或Descending单选框,再次单击OK按钮,即可完成排序。如果排序的是文本区域,那么它们的排序就是按第一个字的拼音字母的升序或降序来排列。,数据的自动计算与排序(续3),数据的筛选,自动筛选高级筛选第一步:建立条件区域;第一行条件区域输入列标签。说明:请确保在条件区域与数据清单之间至少留了一个空白行,不能连接。其他行条件区域输入筛选条件。说明:1.单列上具有多个条件:如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件;2.多列上具有单个条件若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件;3.某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件;未完下页续,数据的筛选,高级筛选第一步:建立条件区域;输入筛选条件:4.两列上具有两组条件之一若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件5.一列有两组以上条件若要找到满足两组以上条件的行，请用相同的列标包括多列。条件：所指定的限制查询或筛选的结果集中包含哪些记录的条件。,数据的筛选,第二步：选定需要筛选的数据清单中任一单元格；第三步：选择菜单”Data/Filter/AdvancedFilter”第四步：在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。第五步：若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。注：如果需要将筛选结果放到另一位置，请选择”CopytoanotherLocation”,数据的筛选,通配符以下通配符可作为筛选以及查找和替换内容时的比较条件:,分类汇总的概述分类汇总是对数据清单进行数据分析的一种方法.Data/Subtotal,分类汇总,确保要进行分类汇总的数据为下列格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。单击要分类汇总的列中的单元格。在上面的示例中，应单击“运动”列（列B）中的单元格。单击“升序排序”或“降序排序”。在“数据”菜单上，单击“分类汇总”。在“分类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。,分类汇总,要分类汇总的列分类汇总,步骤:,如果想在每个分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。单击“确定”。注释可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。,分类汇总,要分类汇总的列分类汇总,删除分类汇总删除分类汇总时，MicrosoftExcel也删除分级显示以及随分类汇总一起插入列表中的所有分页符。在含有分类汇总的列表中，单击任一单元格。在“数据”菜单上，单击“分类汇总”。单击“全部删除”。,分类汇总,删除分类汇总删除分类汇总时，MicrosoftExcel也删除分级显示以及随分类汇总一起插入列表中的所有分页符。在含有分类汇总的列表中，单击任一单元格。在“数据”菜单上，单击“分类汇总”。单击“全部删除”。,分类汇总,1.选中所需要的数据:如图,数据透视表的使用,数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。,源数据第三季度高尔夫汇总的源值数据透视表C2和C8中源值的汇总,数据透视表的使用(续1),何时应使用数据透视表如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格F3中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。数据是如何组织的在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，“运动”列成为“运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。数据字段（如“求和项：销售额”）提供要汇总的值。上述报表中的单元格F3包含的“求和项：销售额”值来自源数据中“运动”列包含“高尔夫”和“季度”列包含“第三季度”的每一行。,数据透视表的使用(续2),创建数据透视表打开要创建数据透视表的工作簿。请单击列表或数据库中的单元格。在“数据”菜单上，单击“数据透视表和数据透视图”。在“数据透视表和数据透视图向导”的步骤1中，遵循下列指令，并单击“所需创建的报表类型”下的“数据透视表”。按向导步骤2中的指示进行操作。按向导步骤3中的指示进行操作，然后决定是在屏幕上还是在向导中设置报表版式。通常，可以在屏幕上设置报表的版式，推荐使用这种方法。只有在从大型的外部数据源缓慢地检索信息，或需要设置页字段来一次一页地检索数据时，才使用向导设置报表版式。如果不能确定，请尝试在屏幕上设置报表版式。如有必要，可以返回向导。,在屏幕上设置报表版式从“数据透视表字段列表”窗口中，将要在行中显示数据的字段拖到标有“将行字段拖至此处”的拖放区域。如果没有看见字段列表，请在数据透视表拖放区域的外边框内单击，并确保“显示字段列表”被按下。若要查看具有多个级别的字段中哪些明细数据级别可用，请单击该字段旁的。对于要将其数据显示在整列中的字段，请将这些字段拖到标有“将列字段拖至此处”的拖放区域。对于要汇总其数据的字段，请将这些字段拖到标有“请将数据项拖至此处”的区域。只有带有或图标的字段可以被拖到此区域。如果要添加多个数据字段，则应按所需顺序排列这些字段，方法是：用鼠标右键单击数据字段，指向快捷菜单上的“顺序”，然后使用“顺序”菜单上的命令移动该字段。将要用作为页字段的字段拖动到标有“请将页字段拖至此处”的区域。若要重排字段，请将这些字段拖到其他区域。若要删除字段，请将其拖出数据透视表。若要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。注释如果在设置报表版式时，数据出现得很慢，则请单击“数据透视表”工具栏上的“始终显示项目”来关闭初始数据显示。如果检索还是很慢或出现错误信息，请单击“数据”菜单上的“数据透视表和数据透视图”，在向导中设置报表布局。,数据透视表的使用(续),在向导中设置报表布局如果已经从向导中退出，则请单击“数据”菜单上的“数据透视表和数据透视图”以返回该向导中。在向导的步骤3中，单击“布局”。将所需字段从右边的字段按钮组拖动到图示的“行”和“列”区域中。对于要汇总其数据的字段，请将这些字段拖动到“数据”区。将要作为页字段使用的字段拖动到“页”区域中。如果希望Excel一次检索一页数据，以便可以处理大量的源数据，请双击页字段，单击“高级”，再单击“当选择页字段项时，检索外部数据源”选项，再单击“确定”按钮两次。（该选项不可用于某些源数据，包括OLAP数据库和“Office数据连接”。）若要重排字段，请将它们拖到其他区域。某些字段只能用于某些区域；如果将一个字段拖动到其不能使用的区域，该字段将不会显示。若要删除字段，请将其拖到图形区之外。如果对版式满意，可单击“确定”，然后单击“完成”。,数据透视表的使用(续),删除数据透视表单击数据透视表。在“数据透视表”工具栏上，单击“数据透视表”，指向“选定”，再单击“整张表格”。在“编辑”菜单上，指向“清除”，再单击“全部”。注释对于数据透视图报表，删除与其相关的数据透视表，将会冻结图表，使得不可再对其进行更改。,数据透视表的使