办公常用EXCEL大全.ppt

1、Clerk Office 2003 Training Material 文员Office 2003培训教材,Excel training manual- Intermediate Material Excel培训手册 中级班教材,使用公式 公式中的运算符 公式中的运算顺序 引用单元格 创建公式 创建公式的方法 创建简单公式 创建包含引用或名称的公式 创建包含函数的公式 使用函数 常用函数 财务函数 日期和时间函数 数学与三角函数 统计函数 函数的输入 手工输入 利用函数向导输入 求和计算 扩充的自动求和 条件求和,建立数据清单 建立数据清单的准则 使用记录单 数据排序 对数据清单进行排序 创建

2、自定义排序 数据筛选 自动筛选 高级筛选 分类汇总 分类汇总的计算方法 汇总报表和图表 插入和删除分类汇总 插入图表表示数据 复杂的数据链接 使用数据透视表 创建数据透视表 编辑数据透视表 controll toolbox usage (控件工具箱),课程大纲,使用公式,公式中的运算符 算术运算符,使用公式,公式中的运算符 2. 文本运算符 “ 2. 含有运算符和运算对象; 3. 能产生结果 公式的3个基本组成部分: 数值:包括数字和文本 单元格引用 操作符,创建公式,创建公式的方法 使用编辑栏 直接在单元格中输入 创建3种类型的公式 创建简单公式; 2. 创建包含引用或名称的公式 3. 创建

3、包含函数的公式,使用函数,函数是指预定义的内置公式,可以进行数学、文本和逻辑的运算或者查找工作表的信息。 函数是一个已经提供给用户的公式,并且有一个描述性的名称。 函数结构 函数名称：如果要查看可用函数的列表,可以单击一个单元格并按Shift+F3键； 参数：参数可以是数字、文本、逻辑值（TRUE或FALSE）、数值、错误值（如#N/A）或单元格引用。 参数工具提示：在输入函数时,会出现一个带有语法和参数的工具提示。 输入公式：“插入函数”对话框。 函数的参数 函数的参数是一个函数体中括号内的部分,逻辑值: FALSE :需要完全一致 TRUE: 相似值亦可转换,使用函数,函数的类型 1. 常

4、用函数: 常用函数指经常使用的函数,如求和或计算算术平均值等.包括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笔款项的当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一

5、个通常是负数),注: Rate 贷款利率。 Nper 该项贷款的付款总数。 Pv 现值,或一系列未来付款的当前值的累积和,也称为本金。 Fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv 则假设其值为零,也就是一笔贷款的未来值为零。 Type 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。,使用函数,函数的类型 3. 时间和日期函数:,主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。,例：函数DATA 语法：DATE(year,month,day),在单元格中插入当前日期和

6、时间 插入静态的日期和时间 当前日期 选取一个单元格,并按 Ctrl+; 当前时间 选取一个单元格,并按 Ctrl+Shift+； 当前日期和时间 选取一个单元格,并按 Ctrl+;,然后按空格键,最后按 Ctrl+Shift+; 插入会更新的日期和时间 使用 TODAY 和 NOW 函数完成这一工作。,小贴士,使用函数,函数的类型 4. 数学与三角函数: 5. 统计函数: 统计函数是用来对数据区域进行统计分析。,主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。,例：ABS函数 语法：ABS(number),函数

7、是一种特殊的公式,因此所有的函数要以“=”开始。 使用方法如下：1.选中要输入函数的单元格 2.单击Insert菜单中的 命令,弹出如图所示的对话框：,使用函数,3.在上图的对话框中,选择函数分类下面的函数类型及 函数名下面的对应函数名。 4. 单击确定按钮,弹出如图所示的相应的调用 函数对话框：,使用函数,函数的使用 (SUM),SUM函数 函数名称：SUM 主要功能：计算所有参数数值的和。 使用格式：SUM（Number1,Number2） 参数说明：Number1、Number2代表需要计算的值,可以是具体的 数值、引用的单元格（区域）、逻辑值等。 特别提醒：如果参数为数组或引用,只有其

8、中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：=SUM(LARGE(D2:D63,1,2,3,4,5),则可以求出前5名成绩的和。,函数的使用 (AVERAGE),函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,) 参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域）,参数不超过30个。 特别提醒：如果引用区域中包含“0”值单元格,则计算在内；如果引用区域中包含空白或字符单元格,则不计算在内。,函数的使用 (IF),IF函数 函数名称：IF

9、主要功能：根据对指定条件的逻辑判断的真假结果,返回相对应的内容。 使用格式：=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 和 val

10、ue_if_false 后，函数 IF 返回相应语句执行后的返回值。 如果函数 IF 的参数包含数组，则在执行 IF 语句时，数组中的每一个元素都将计算。,函数的使用 (COUNTIF 单击常用工具栏中的 Auto sum按钮,则下一单元格中将出现计算的结果。,数据的自动计算与排序,如果要自动求平均值,就可按下面的操作进行： 选中要进行计算的区域 在状态栏中单击鼠标右键,弹出如图所示的快捷键菜单。 单击Average命令,则在状态栏中就会出现求均值的结果。,数据的自动计算与排序(续1),数据的自动计算与排序(续2),B. 数据的排序 排序是根据某一指定的数据的顺序重新对行的位置进行调整,操作步

11、骤如下： 1）单击某一字段名。 2）单击工具栏的 升序按钮或 降序按钮,则整个数据库该列被排序。 3）单击Data菜单中的sort命令,弹出sort对话框如图所示。,4) 在sort by下面的编缉框中,选出需排序的关键字,单击Ascending或Descending单选框,再次单击OK按钮,即可完成排序。 如果排序的是文本区域,那么它们的排序就是按第一个字的拼音字母的升序或降序来排列。,数据的自动计算与排序(续3),数据的筛选,自动筛选 高级筛选 第一步: 建立条件区域; 第一行条件区域输入列标签。 说明: 请确保在条件区域与数据清单之间至少留了一个空白行,不能连接。 其他行条件区域输入筛选

12、条件。 说明: 1.单列上具有多个条件: 如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件; 2.多列上具有单个条件 若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件; 3.某一列或另一列上具有单个条件 若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件; 未完 下页续,数据的筛选,高级筛选 第一步: 建立条件区域; 输入筛选条件: 4.两列上具有两组条件之一 若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件 5.一列有两组以上条件 若要找到满足两组以上条件的行，请用相同的列标

13、包括多列。 条件：所指定的限制查询或筛选的结果集中包含哪些记录的条件。,数据的筛选,第二步：选定需要筛选的数据清单中任一单元格； 第三步：选择菜单”Data / Filter / Advanced Filter” 第四步：在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。 第五步：若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。 注：如果需要将筛选结果放到另一位置，请选择”Copy to another Location”,数据的筛选,通配符 以下通配符可作为筛选以及查找和替换内容时的比较条件:,分类汇总的概述 分类汇总是对数据清单进行数据分析的一种方法. Data/

14、Subtotal,分类汇总,确保要进行分类汇总的数据为下列格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。 单击要分类汇总的列中的单元格。在上面的示例中，应单击“运动”列（列 B）中的单元格。 单击“升序排序” 或“降序排序”。 在“数据”菜单上，单击“分类汇总”。 在“分类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。 在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。 在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。,分类汇总,要分类汇总的列 分类汇总,步骤:,如果想在每个

15、分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。 如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。 单击“确定”。 注释 可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。,分类汇总,要分类汇总的列 分类汇总,删除分类汇总 删除分类汇总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。 单击“全部删除”。,分类汇总,删除分类汇总 删

16、除分类汇总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。 单击“全部删除”。,分类汇总,1.选中所需要的数据:如图,数据透视表的使用,数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。,源数据 第三季度高尔夫汇总的源值 数据透视表 C2 和 C8 中源值的汇总,数据透视表的使用(续1),何时应使用数据透视表 如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视

17、表。在上面所述报表中，用户可以很清楚地看到单元格 F3 中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。 数据是如何组织的 在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，“运动”列成为“运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。 数据字段（如“求和项：销售额”）提供要汇总的值。上述报表中的单元格 F3 包含的“求和项：销售额”值来自源数据中“运动”列包含“高尔夫”和“季度”列包含“第三季度”的每一行。,数据透视表的

18、使用(续2),创建数据透视表 打开要创建数据透视表的工作簿。 请单击列表或数据库中的单元格。 在“数据”菜单上，单击“数据透视表和数据透视图”。 在“数据透视表和数据透视图向导”的步骤 1 中，遵循下列指令，并单击“所需创建的报表类型”下的“数据透视表”。 按向导步骤 2 中的指示进行操作。 按向导步骤 3 中的指示进行操作，然后决定是在屏幕上还是在向导中设置报表 版式。 通常，可以在屏幕上设置报表的版式，推荐使用这种方法。只有在从大型的外部数据源缓慢地检索信息，或需要设置页字段来一次一页地检索数据时，才使用向导设置报表版式。如果不能确定，请尝试在屏幕上设置报表版式。如有必要，可以返回向导。,

19、在屏幕上设置报表版式 从“数据透视表字段列表”窗口中，将要在行中显示数据的字段拖到标有“将行字段拖至此处”的拖放区域。 如果没有看见字段列表，请在数据透视表拖放区域的外边框内单击，并确保“显示字段列表” 被按下。 若要查看具有多个级别的字段中哪些明细数据级别可用，请单击该字段旁的 。 对于要将其数据显示在整列中的字段，请将这些字段拖到标有“将列字段拖至此处”的拖放区域。 对于要汇总其数据的字段，请将这些字段拖到标有“请将数据项拖至此处”的区域。 只有带有 或 图标的字段可以被拖到此区域。 如果要添加多个数据字段，则应按所需顺序排列这些字段，方法是：用鼠标右键单击数据字段，指向快捷菜单上的“顺序

20、”，然后使用“顺序”菜单上的命令移动该字段。 将要用作为页字段的字段拖动到标有“请将页字段拖至此处”的区域。 若要重排字段，请将这些字段拖到其他区域。若要删除字段，请将其拖出数据透视表。 若要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。 注释如果在设置报表版式时，数据出现得很慢，则请单击“数据透视表”工具栏上的“始终显示项目” 来关闭初始数据显示。如果检索还是很慢或出现错误信息，请单击“数据”菜单上的“数据透视表和数据透视图”，在向导中设置报表布局。,数据透视表的使用(续),在向导中设置报表布局 如果已经从向导中退出，则请单击“数据”菜单上的“数据透视表和数据透视图”以返回该向导中。

21、 在向导的步骤 3 中，单击“布局”。 将所需字段从右边的字段按钮组拖动到图示的“行”和“列”区域中。 对于要汇总其数据的字段，请将这些字段拖动到“数据”区。 将要作为页字段使用的字段拖动到“页”区域中。 如果希望 Excel 一次检索一页数据，以便可以处理大量的源数据，请双击页字段，单击“高级”，再单击“当选择页字段项时，检索外部数据源”选项，再单击“确定”按钮两次。（该选项不可用于某些源数据，包括 OLAP 数据库和“Office 数据连接”。） 若要重排字段，请将它们拖到其他区域。某些字段只能用于某些区域；如果将一个字段拖动到其不能使用的区域，该字段将不会显示。 若要删除字段，请将其拖到

22、图形区之外。 如果对版式满意，可单击“确定”，然后单击“完成”。,数据透视表的使用(续),删除数据透视表 单击数据透视表。 在“数据透视表”工具栏上，单击“数据透视表”，指向“选定”，再单击“整张表格”。 在“编辑”菜单上，指向“清除”，再单击“全部”。 注释对于数据透视图报表，删除与其相关的数据透视表，将会冻结图表，使得不可再对其进行更改。,数据透视表的使用(续),制作与使用图表,创建图表 图表是Excel为用户提供的强大功能,通过创建各种不同类 型的图表,为分析工作表中的各种数据提供更直观的表示结果。 使用图表向导 下面我们先在工作薄中建立数据表如下页图所示,根据 工作表中的数据,创建不同

23、类型的图表。 图表的编辑 1.插入数据标志及增加图表标题 2.改变图表的文字、颜色和图案,图,操作步骤如下： 1)选定工作表中包含所需数据的所有单元格。 2)单击常用工具栏中的图表向导按钮 ,弹出图（1-16）所 示的对话框：,图表的使用,图表的使用（续1）,3）在显示的图表向导中,你可以选择最合适的图表类型,单击 standard type标签,在chart type下拉式列表框中选择任何一种图表类型,再在其相应的子图表类型的样式中选择其中一种子图表类型,然后按下查看示例按钮不放,就可预览所选类型的示例。单击下一步按钮,弹出如图的对话框：,图表的使用（续2）,4）然后继续单击下一步弹出图对话

24、框,选择图表存放的位置,若要将图表放到另一个新的工作表上, 请选中As new sheet单选框,然后作为新工作表插入文本框, 键入新工作表的名字,默认为,如果选择As object in 单选框,可将：,图（1-18）,图表插入到当前打开的工作表中,接着再单击完成按钮,便可得到如图所示的图表。,图表的编辑,一、插入数据标志及增加图表标题 加入数据标志的过程如下：1.激活要添加数据标志的图表 2.单击chart菜单中的chart options命令,弹出如图(1-19),图表标题,分类X轴,数值Y轴,图（1-20）,单击data labels,出现如图,择所需选项即可。,数据标志的解说,二、改

25、变图表的文字、颜色和图案,图表的编辑,若要对标题进行编辑,可将鼠标移至标题的左下角处双击,即会出现右图所示对话框,用该对话框可以对标题的字体、字体的背景色等进行修改。,图(1-23),若要对图表区域进行修改,可将鼠标移至图表的空白区域双击鼠标,则出现图（1-23）对话框,在此对话框中可选定要修改的内容。,图(1-24),若要对图例进行修改,可将鼠标移至图例区双击鼠标,则出现如图（1-24）对话框。在此对话框中可对图例进行编辑,如图案、字体及位置。,附加内容-控件工具箱Control Box,复选框 可通过选中或清除来打开或关闭的选项。您可以在一个工作表中同时选中多个复选框。 文本框 可以向其中

26、键入文字的框。 命令按钮 单击可启动某项操作的按钮。 选项按钮用于从一组选项中选择某一选项的按钮。 列表框包含项目列表的框。 组合框 含有下拉列表框的文本框。您可以从列表中选择,或在框中键入所需内容。 切换按钮单击后保持按下状态,再单击时又弹起的按钮。 微调按钮可附加在单元格上或文本框上的按钮。若要增加数值,请单击向上箭头；若要减小数值,请单击向下箭头。 滚动条可通过单击滚动箭头或拖动滚动块来滚动数据区域的一种控件。单击滚动箭头与滚动块之间的区域时,可以滚动整页数据。 标签 添加到工作表或窗体中,用以提供有关控件、工作表或窗体信息的文本。 图像 将图片嵌入到窗体中的控件。 其他控件其他 Act

27、iveX 控件的列表。,附加内容宏的录制与运用,关于宏 如果经常在 Microsoft Excel 中重复某项任务,那么可以用宏自动执行该任务。宏是一系列命令和函数,存储于 Visual Basic 模块中,并且在需要执行该项任务时可随时运行。 例如,如果经常在单元格中输入长文本字符串,则可以创建一个宏来将单元格格式设置为文本可自动换行。 录制宏 在录制宏时,Excel 在您执行一系列命令时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或“回放”这些命令。如果在录制宏时出错,所做的修改也会被录制下来。Visual Basic 在附属于某工作薄的新模块中存储每个宏。,附加内容宏的录制与

28、运用,使宏易于运行 可以在“宏”对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏,可将宏指定给某个工具栏按钮、键盘快捷键或工作表中的图形对象。 管理宏 宏录制完后,可用 Visual Basic 编辑器查看宏代码以进行改错或更改宏的功能。例如,如果希望用于文本换行的宏还可以将文本变为粗体,则可以再录制另一个将单元格文本变为粗体的宏,然后将其中的指令复制到用于文本换行的宏中。 “Visual Basic 编辑器”是一个为初学者设计的编写和编辑宏代码的程序,而且提供了很多联机帮助。不必学习如何编程或如何用 Visual Basic 语言来对宏进行简单的修改。利用

29、“Visual Basic 编辑器”,您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。,附加内容宏的录制与运用,宏安全性 Excel 对可通过宏传播的病毒提供安全保护。如果您与其他人共享宏,则可使用数字签名来验证其他用户,这样就可保证其他用户为可靠来源。无论何时打开包含宏的工作簿,都可以先验证宏的来源再启用宏。,附加内容宏的录制与运用,创建宏 录制宏 将安全级设置为“中”或“低”。 操作方法 在“工具”菜单上,单击“选项”。 单击“安全性”选项卡。 在“宏安全性”之下,单击“宏安全性”。 单击“安全级”选项卡,再选择所要使用的安全级。 在“工具”菜单上,指向

30、“宏”,再单击“录制新宏”。 在“宏名”框中,输入宏的名称。 注意 宏名的首字符必须是字母,其他字符可以是字母、数字或下划线。宏名中不允许有空格；可用下划线作为分词符。 宏名不允许与单元格引用重名,否则会出现错误信息显示宏名无效。 如果要通过按键盘上的来运行宏, 请在“快捷键”框中,输入一个字母。可用 Ctrl+字母（小写字母）或 Ctrl+Shift+字母（大写字母）,其中字母可以是键盘上的任意字母键。快捷键字母不允许是数字或特殊字符（如 或 #）。 注释当包含宏的工作簿打开时,宏快捷键优先于任何相当的 Microsoft Excel 的默认快捷键。,附加内容宏的录制与运用,在“保存在”框中

31、,单击要存放宏的地址。 如果要使宏在使用 Excel 的任何时候都可用,请选中“个人宏工作簿”。 如果要添加有关宏的说明,请在“说明”框中键入该说明。 单击“确定”。 如果要使宏相对于活动单元格位置运行,请用相对单元格引用来录制该宏。在“停止录制”工具栏上,单击“相对引用” 以将其选中。Excel 将继续用“相对引用”录制宏,直至退出 Excel 或再次单击“相对引用” 以将其取消。 执行需要录制的操作。 在“停止录制”工具栏上,单击“停止录制” 。 用 Microsoft Visual Basic 创建宏 在 Microsoft Excel 的“工具”菜单上,指向“宏”,再单击“Visual Basic 编辑器”。 在“插入”菜单上,单击“模块”。 将代码键入或复制到模块的