《大学计算机基础》第3章常用Office办公软件

1、1大学计算机基础北京航空航天大学教学课件2第3章 常用Office办公软件3.3 演示文稿制作软件 PowerPoint 3.1 字处理软件 Word 3.2 电子表格软件 Excel 33.2 电子表格软件Excel3.2.1 Excel简介 3.2.2 Excel的窗口与组成3.2.3 Excel的启动与退出3.2.4 Excel输入与填充3.2.5 Excel公式与函数3.2.6 Excel格式设置3.2.7 Excel数据图表化3.2.8 Excel数据管理3.2.9 Excel的其他功能3.2.10 习题4本节重点Excel数据与填充Excel公式与函数Excel格式设置Excel数

2、据图表化Excel数据管理5将数据用表格及各种统计图、透视图的形式表示, 使制作出的报表图文并茂，信息表述更清晰。3.2.1 Excel简介Excel电子表格软件的特点：Excel 是一个功能强大的电子表格软件。具有强大的数据输入、编辑、计算与分析处理功能。用超级链接功能可快速打开Internet或Intranet上的文件，使Excel可与处于世界任何位置的Internet用户共享图表信息或协同工作。312433.2.2 Excel的窗口与组成编辑栏工作簿文件名单元格名称标题栏当前单元格或称活动单元格 列号当前工作表Excel 工作簿文档窗口工作表名栏行号标签拆分框垂直拆分框水平滚动条水平拆分

3、框垂直滚动条工作表区域全选按钮状态栏选项卡展开或折叠编辑栏视图按钮显示比例快速访问工具栏功能区编辑功能组确认或放弃编辑和插入函数单击弹出更多此按钮的相关选项7工作簿、工作表和单元格的关系 A1、AA1、IU65536、XFD1048576单元格的位置在何处？8单元格的数据信息 一个单元格的信息可以包含三部分：内容文字、数字、日期、时间、公式(包括函数)。批注帮助理解该单元格数据的含义，或者作为数据的 说明。格式系统提供缺省格式。也可以重新对单元格进行格式设置。（格式设置信息包括数据的字体、字体大小、颜色、边框及其线形等）。 93.2.3Excel的启动与退出启动Excel Excel的执行程序

4、文件名是EXCEL.EXE，通常安装Office 2010以后，该文件被存放在“Program FilesMicrosoft Office office14”目录中。用以下不同方法启动Excel：（1）双击桌面上的Excel快捷方式。（2）选择【开始】|【所有程序】| Microsoft Office | Microsoft Excel 2010命令。（3）双击EXCEL.EXE执行文件。（4）在【运行】对话框中输入excel并单击【确定】 。（5）双击以.xls或xlsx为后缀的任意文件。103.2.3Excel的启动与退出（续）退出Excel 当退出Excel时，Excel将自动提示编辑过

5、的文件是否需要保存，待回答并处理后，再退出Excel。用以下不同方法退出Excel：（1）单击标题栏【关闭】Excel命令按钮 。（2）用组合功能键【 Alt+F4】（3）双击Excel窗口控制按钮 。（4）单击Excel窗口控制按钮 ，并单击【关闭】 。（5）选择【文件】|【退出】命令。113.2.4Excel输入与填充 【案例7】：创建一空白工作簿，并制作部分学生成绩表如下。A1单元格Ctrl+;12各类数据的输入 文字（默认左对齐）=7h( =7h)，123(123) 常量 数字（默认右对齐）.,(或带E的科学计数法)Excel 能够 日期、时间（默认右对齐）： 识别 12/31/98、

6、98-8-5、8-5、1-may 、may-1( 1-may)机年、8:12、 两大类 77:10再双击左键重选该单元格单元格编辑栏中的内容输入： 内容为 1900-1-3 5:10:00 ( + ; 当时 + Shift + ; ) 公式 运算符、数据值、单元格地址、函数的全部或部分组合（后不符 合公式规定，Excel会报错。符合公式规定就运算，在单元格中显示 运算结果，在该单元格的编辑栏中显示运算公式。以后一旦数据有变 化，公式单元格中的结果就自动变更） 注输入错误数字或日期时间，Excel自动把它判断成为文字型数据。 输入分式数据“1/4”的方法：0 1/413Excel的自动填充功能

7、根据初始数据信息自动决定以后填充项的内容，它是用户输入有规律数据时的得力助手。例如填充：增序或减序的数据。等差、等比或任意步长的序列。预定义序列。自定义序列。公式等。充分利用填充功能将加速表格数据的建立，达到事半功倍的效果。14单元格的填充柄 数字数据自动增1或减1序列的填充方法：Ctrl+拖填充柄数字数据的复制填充方法：拖填充柄文字数据自动增1或减1序列的填充方法：拖填充柄（日期数据时：“日”增减；时间数据时：“时”增减）文字数据的复制填充方法：Ctrl+拖填充柄 加按Ctrl键 不加按Ctrl键数字数据和非数字数据的填充操作正相反 15输入等差、等比或任意步长的序列 输入此类序列可用两种方

8、法。方法一：（1）选定欲填充序列单元格区域，输入初始值并按【Enter】键。（2）选择【编辑】|【填充】|【系列(s)】菜单命令。（3）在【序列】对话框中，选定类型，修改序列的步长值后单击【确定】。方法二： （1）选定欲填充序列初始值的单元格，输入初始值并选作当前单元格。（2）选择【编辑】|【填充】|【系列(s)】 】菜单命令。（3）在【序列】对话框中，选定序列产生在行或列。（4）再选定类型，修改步长值，输入终止值后单击【确定】。16输入预定义或自定义序列在【自定义序列】对话框中创建工作表中常用的新序列。Excel已经提供了预定义序列共11种。在工作表单元格区域中可以快速填充预定义或自定义序列

9、。 17添加或导入自定义序列 对于输入预定义序列或自定义序列的方法都是相同的，只要在选定单元格中输入序列的某一成员，横向或纵向拖拽填充柄到末端单元格松手，即可滚动输入序列各成员。用添加方法：（1）在【自定义序列】选项卡中选择“新序列”。（2）在【输入序列】文本框中分别输入序列成员，按【 Enter 】键分隔。（3）单击【添加】按钮后，新序列被添加到自定义序列的最后一行。 用导入方法：（1）在工作表中选定已输入序列的单元格区域。（2）选择【文件】|【选项】|【高级】|【编辑自定义列表】。（3）单击【导入】按钮后，新序列被添加到自定义序列的最后一行。18输入特殊字符方法一 输入数据过程中难免需要输

10、入一些特殊字符，可用两种方法进行输入。1）利用输入法的软键盘（共13种）。 （1）激活汉字输入法并用鼠标右键单击软键盘按钮； （2）单击某种软键盘，并在该软键盘中单击特殊字符即可； （3）单击【PC 键盘】恢复键盘正常输入。软键盘列表和软键盘 19输入特殊字符方法二 2）利用字符映射表。 （1）单击【开始】|【程序】|【附件】|【系统工具】|【字符映射表】。 （2）在【字符映射表】对话框中，从【字体】下拉列表选择一种。 （3）双击某字符后【复制字符】显示区显示该字符，再单击【复制】按钮。 （4）在Excel环境，选择【编辑】|【粘贴】菜单命令。 （5）再选定该字符，并将它改成字符映射表的同种字

11、体。选择【插入】|【符号】命令，在【符号】对话框中选择符号或特殊字符标签进行输入 【字符映射表】窗口 203.2.5 Excel公式与函数【案例8】：在部分学生成绩表基础上，利用Excel的公式与函数功能，计算每位学生的总成绩；当总成绩大于等于270分时在“总评”列相应单元格中显示“优秀” ，否则为空格；并按总成绩进行排名。计算每门课程的最高分和最低分，并在相应单元格中显示。制作效果如下。21【案例8】中的转置与截取将部分学生成绩表的行和列进行转置，转置结果如下所示。复制转置结果，只截取两个优秀生的数据如下所示。221）计算每名学生的总成绩先单击F3单元格，单击【自动求和】 及【输入】按钮 ，

12、再用鼠标指向F3单元格的填充柄，并按下左键拖拽至F10单元格松手即可。 232）计算总评 首先选定G3单元格，对总成绩F3单元格的总评计算使用【插入函数】按钮 。IF函数参数对话框输入后产生的表达式当条件不满足时总评的值显示空条件设置为总成绩大于等于270分当条件满足时总评的值显示优秀不需要输入双引号其他学生的总评用G3单元格的填充柄复制即可。 243）按总成绩进行排名 按总成绩进行排名计算使用RANK函数。本案例对总成绩F3单元格的计算排名，先单击H3单元格 。 RANK函数参数对话框输入后产生的表达式返回指定数字在一列数字中的排位一组数或引用，（非数字值将被忽略）指定排位方式（非0值，升序

13、）绝对地址按【F4】功能键进行切换选定 其他学生的排名用H3单元格的填充柄复制即可。 254）计算每门课程的最高分和最低分 对表格中最高分和最低分计算，可分别选择相应单元格，单击 【插入函数】按钮 ，并选择MAX和MIN函数，类似于求总成绩的方法来完成。 265）转置 在部分学生成绩表中，先复制A2:H12单元格区域，然后将插入点定位到J2单元格。选择【粘贴（下拉按钮）】|【选择性粘贴】命令，在【选择性粘贴】对话框中选中【转置】复选框并单击【确定】按钮。调整合适列宽即可。276）截取优秀生数据 在转置表格中截取两个优秀生的数据。先利用复制和粘贴功能在J12起始单元格区域得到转置结果的副本。删除

14、非首列和优秀学生列的其它列区域数据。不能直接用列删除，而先选择无用列数据并按鼠标右键，在弹出菜单中单击“删除”命令，然后在【删除】对话框中选中【右侧单元格左移】并单击【确定】按钮。 28有关函数与公式的说明 1）Excel的函数2）Excel的公式3）Excel的地址与引用4）公式的复制与移动5）公式与函数的出错信息291）Excel的函数 Excel的函数只能出现在公式中。Excel提供了大量的内置函数，为用户对数据运算和分析带来极大方便。范围包括：常用、财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息等。当选用某一函数后，在相应的【函数参数】对话框中将会显示各个

15、参数相应的提示信息。 302）Excel的公式 在Excel公式中，后面是参与计算的运算数和运算符。运算数：可以是常量数值、单元格或单元格区域的引用、标志名称以及函数引用。运算符：有算术、比较、文本和引用运算符。 算术运算符：（单目负号）、（百分比）、（乘幂）、* （乘）、（除）、。比较运算符：、（不相等） 、=、 、=（结果为TRUE或FALSE）。文本运算符：&（连接）。引用运算符： ：（区域）、 ，（并集）、空格（交集）。运算优先级：（）括号先做，再考虑引用运算符（：、，、空格）、算术和比较运算符。同级运算从左到右。31公式举例 例1：文本运算符的使用。 在A1单元格中输入“中”字，A2

16、单元格中输入“国”字，在A3单元格中输入：A1&A2，则A3中的数据是：中国。例2：并集运算符的使用。 =SUM（A1:A4，B1:B2，C1:C4） 相当于 =SUM（A1:A4）+ SUM（B1:B2）+ SUM（C1:C4）例3：交集运算符的使用。 =SUM（A1:C2 C1:D2）相当于 =SUM（C1:C2）。323）Excel的地址与引用 绝对地址、相对地址、混合地址、单元格引用。 绝对地址：在单元格或区域的行名与列名前面均有$符号。例如：$A$1、$A$1: $C$19。相对地址：在单元格或区域的行名与列名前面均无$符号。例如：A1、A1: C19。混合地址：在行名或列名前面出现

17、$符号。例如：$A1、A$1、A$1: A$3。单元格引用：在公式中对单元格和单元格区域进行引用。例如：B4内容为“A1+A2”33绝对、相对和混合引用绝对引用、相对引用和混合引用。 绝对引用：公式中引用的单元格地址是绝对地址，则属于绝对引用。在复制带有绝对引用的公式时，精确地复制被复制公式中的绝对地址。相对引用：公式中引用的单元格地址是相对地址，则属于相对引用。在复制带有相对引用的公式时，自动调整成指向新的单元格，而不是被复制公式中的引用单元格。混合引用：公式中引用的单元格地址既有相对地址又有绝对地址，则属于混合引用。在复制带有混合引用的公式时：只调整没有冠以“$”的列号或行号，而不更改引用

18、的另一部分，即只调整相对地址部分。344）公式的复制与移动 移动公式方法很多，最快方法是：鼠标指向该单元格边框变成四方向箭头后按下左键，并拖至另一单元格。复制公式时，则要加按Ctrl键。公式被移动到其它单元格时，原计算结果不变。公式被复制到其它单元格时，复制的公式计算结果与地址引用有关。如果复制的公式只有绝对地址引用，则结果不变；如果复制的公式非绝对地址引用，则结果会有变化。 355）公式与函数的出错信息 #：公式产生的结果太长，单元格容纳不下。如果对日期和时 间做减法，请确认格式是否正确。#DIV/0! ：试图以或为的单元格作为除数，例：。#REF! ： 引用了一个所在列或行已被删除的单元格

19、。#VALUE! ：算术运算公式中出现文字或单元格区域，例：5*“ok”。 或函数规定只能用单值或单个单元格地址作为参数。#NAME! ：把文字型作为函数的数字型参数。#NUM! ：函数中出现非法数字参数。 365）公式与函数的出错信息（续） 当单元格中显示以上任一出错信息时，左上角显示未被忽略错误的绿色小三角标记，单击该单元格，鼠标指向该单元格右边的惊叹号可显示错误信息；单击该惊叹号，出现下拉菜单，其中有【关于此错误的帮助】、【显示计算步骤】、【错误检查选项】等命令，可用于排查错误信息。 373.2.6Excel格式设置在建立表格时，Excel均以系统默认格式显示。当对表格格式不满意时，可以

20、更改格式，也就是美化工作表。在Excel中，先选择欲设置格式的对象，然后选择【格式】|【设置单元格格式】命令。有些较常用的格式设置通过【开始】|【功能区】中的命令或利用【格式刷】来完成。38 【案例9】部分学生成绩表的格式设置94隶书、加粗倾斜、24字号 底纹颜色为“黄色”，并设“图案”为6.25%灰色 94分以上、60分以下 优秀率百分比样式显示 双线 平均分 39有关格式设置的其它说明 0）单元格格式1）使用格式刷设置格式 2）在单元格中输入多行数据 3）自动套用格式 4）样式401）使用格式刷设置格式剪贴板中的【格式刷】按钮 ，作用是复制格式。将A对象的格式复制给B对象：先选择A对象，单

21、击【格式刷】按钮之后（鼠标指针边上会出现小刷子），用鼠标左键选择B对象再释放左键即可。将A对象的格式复制给多个对象：先选择A对象，双击【格式刷】按钮，则可用鼠标左键重复多次将格式刷应用到不同的对象，解除格式刷，只要单击【格式刷】按钮即可。 412）在单元格中输入多行数据 先在单元格中输入第1行数据。按【 Alt+Enter 】快捷键，就可以在单元格内换行输入第2行数据。更多行数据的输入方法依次类推。一般在每行数据输入之前插入一些空格。单击【插入】|【形状】中的【直线】按钮，在该单元格中画一斜线。需要调整合适的行高和列宽。423）套用表格格式 使用套用表格格式的方法：先选定需要套用格式的单元格区

22、域。选择【套用表格格式】中的命令。如果对套用的格式不满意，单击【撤消】按钮即可。 434）样式 在Excel中样式用于定义或应用于所选内容的格式组合。（即将各种格式的组合定义为样式，然后将其运用到单元格中）。样式中的格式包括6部分，采用了某种样式，即采用了该样式中定义的所有格式。样式可分为内部样式和自定义样式。内部样式：由Excel提供内部预定义的样式。新建工作簿时就默认采用内部样式。自定义样式：由用户自己创建样式，并为样式命名。一般可通过各种格式的设置或选用单元格中已有的格式组合。443.2.7Excel数据图表化数据表格的图表化，能够更清晰地直观反映表格中的数据关系，Excel具有丰富的图

23、表图形和样式可供选择。采用【插入】图表，并利用【图表工具】中【设计】、【布局】和【格式】选项卡的图表编辑方法创建图表。数据图表化可以采用三种方法，即用“迷你图”、插入“图表”或“图表”工作表。 453.2.7Excel数据图表化（续）制作图表时，首先选择图表化的数据区域，然后单击【插入】并选用图表图形（共11种类型）或进一步指定某种样式（共有48种样式）。图表制作完成，应该对图表进行编辑或调整并放置到合适位置。编辑图表时，直接在图表对象上右击，在右键快捷菜单中进行选择需要编辑的内容。46 【案例10】制作高考成绩簇状柱形图簇状柱形图图表标题：华文新魏、20磅、加粗数值轴 图例 分类轴不能在图表

24、上直接改数据471）制作高考成绩簇状柱形图先选中“学号”至“英语”字段名与数据区域，单击【插入】|【柱形图】中的簇状柱形图。单击【图表样式】功能组的下拉按钮后，选择“样式42”。右击“图表区”，单击【设置图表区域格式】命令，在该对话框中，选中【边框样式】|【圆角】复选项。482）调整与编辑图表调整图表的大小后，单击【图表布局】功能组中“布局1”命令添加图表标题；单击【布局】|【坐标轴标题】|【主要横坐标轴标题】|【坐标轴下方标题】可添加横坐标轴标题，用类似方法可添加主要纵坐标轴竖排标题。在【布局】|【网格线】中可设置主要网格线。右击“图例”并单击“设置图例格式”命令，在【设置图例格式】对话框中

25、可以设置图例格式。单击【布局】|【形状】，选择“箭头”形状，在坐标轴上画出箭头，单击【形状样式】的下拉按钮中“中等线-墙的颜色6”。493）图表的对象组合移动图表时，也许会发生添加的对象与图表分离的现象，此时可先选中某对象，然后按Shift键，再选择图表中的各个对象，最后，当在图表中移动的鼠标指针变成具有四个方向箭头时，单击右键，在弹出的快捷菜单中选择【组合】命令即可。50 【案例11】制作三维饼图饼图一般由同类所占百分比数值的饼图块构成，本案例主要演练在Excel中对饼图的深度编辑与美化。511）方法一（在允许改变表格数据格式的情况下） 先将“三维饼图数据”工作表中“学号”数据修改为文本型数

26、据，只需在数字前插入一个西文单引号并填充即可； 选中“学号”、“姓名”和“总分”字段名及其列数据，单击【插入】|【饼图】中的三维饼图，并选择“样式42”。 单击【位置】|【移动图表】命令，在该对话框中选择“新工作表”并输入“三维饼图”，单击【确定】按钮。 按要求调整和设置数据标签格式、编辑“高考总成绩三维饼图”方法与“高考成绩簇状柱形图（样式42）”具有类似的方法。522）方法二（在不允许改变表格数据格式的情况下）。 直接先选择“学号”至“总分”字段名与数据区域，单击【插入】|【饼图】中的三维饼图，选择“样式42”。 单击【数据】功能组的【选择数据】并在该对话框中，删除“图例项”中的高考科目，

27、只需选中后单击【删除】按钮即可。、 同方法一中的步骤。53有关图表制作的其它说明使用【图表工具 】进行编辑修改图表对象是最全面的，包括图表类型的改变，图表数据区域和图例项产生在行或列的改变，对图表系列数据的添加或删除，对标题、坐标轴、网格线、图例、数据标志和数据表的设置与选择，对创建图表存放的位置选择等，并且在编辑修改过程中能够看到图表示例的变化。543.2.8Excel数据管理建立数据清单数据排序数据筛选分类汇总建立数据透视表合并计算有关数据管理的其它说明551. 建立数据清单数据清单（数据列表）：首行的各列是字段名且不重复，后续各行为记录且不重复，字段名下的单元格应具有同一种格式样式，无空

28、行、空列，数据清单与其它数据应该有空行和空列分割。562. 数据排序（按单个关键字）【案例12】 ：按总分降序方式排序（按单个关键字）单击数据清单总分列中的任意一个单元格。单击【开始】|【排序和筛选】中的“降序”命令。572. 数据排序（按多个关键字）【案例13】：将数据列表按性别排列，女生排前，男生排后，性别相同的总成绩按降序排列，若总成绩也相同，则数学高分排前、低分排后。582. 数据排序（按多个关键字）续选中数据清单的任意一个单元格。选择【数据】|【排序】命令，在【排序】对话框中，选中【数据包含标题】单选项。从下拉列表中选择【主要关键字】的字段名为“性别”，排序依据为“数值”，排序次序为

29、“降序”。单击【添加条件】命令按钮后，从下拉列表中选择【次要关键字】的字段名为“总分”，排序次序为“降序”。后续设置如同上述类似方法完成，并单击【确定】按钮。如果要求按笔画顺序排序，单击【选项】按钮，在【排序选项】对话框中进行设置。593. 数据筛选单击数据清单中的任意一个单元格。选择【数据】|【筛选】命令，单击某个字段名【筛选】按钮就能看到该列的非重复数据、【全选】、【数字筛选】或【文本筛选】等选项，即可选择或采用【自定义筛选】的“自定义自动筛选方式”对话框进行查询数据。【案例14】：按条件查询数据列表中的记录，条件是查询女生总成绩在660分至750分的记录。 603. 数据筛选（续）完全撤

30、消数据筛选：单击【撤消】按钮。撤消部分数据筛选：单击【筛选】按钮，在下拉菜单中选择【全选】 |【确定】命令即可。筛选记录的排序：单击【筛选】按钮，并选择【升序】 或【降序】并【确定】即可。 614. 分类汇总 分类汇总是通过所选“分类字段”自动插入小计和合计，汇总多个相关数据行。分类字段、汇总方式、汇总项。嵌套分类汇总。分级显示按钮。删除分类汇总。624. 分类汇总【案例15】：将高考成绩数据列表按“性别”为分类字段，求“语文”至“总分”字段的男、女生的总体标准偏差，并 嵌套分类汇总男、女生人数。使用“分类汇总”的注意事项： 分类汇总前，必须对分类字段先排序后分类汇总，否则分类汇总就毫无意义。

31、 在【数据】|【分类汇总】对话框中，选择分类字段、汇总方式（共11种）和选定汇总项。编辑与美化后的案例结果63清除分级显示区域选择【数据】|【取消组合】|【清除分级显示】命令。恢复原数据清单选择【数据】|【分类汇总】|【全部删除】命令。645. 建立数据透视表 在原有数据列表的基础上，利用建立数据透视表的功能建立一份新的、经过数据分析的报表，即数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格。 【案例16】：按区分“性别”和“数学分班”制作高考科目及总成绩平均值的数据透视表。655. 建立数据透视表（续）使用“数据透视表”的注意事项： 利用【创建数据透视表】对话框、【数据透视表工具

32、】和【数据透视表字段列表】进行创建或自定义布局数据透视表。 当选择“数据透视表字段列表”中的字段名时，默认非数值字段会添加到“行标签”区域，数值字段会添加到“值”区域，且默认为“求和”汇总方式。也可用直接拖动字段名到所需区域的方法制作数据透视表。 双击数据透视表中的值字段名，在【值字段设置】对话框中选择修改汇总方式（共11种）。 当需要查看数据透视表中的详细数据来源时，只需双击数据透视表中的某个数据，则Excel自动创建新工作表，并显示相关数据。66创建高考成绩数据透视表单击数据列表的任意一个单元格，单击【插入】|【数据透视表】命令，在【创建数据透视表】对话框中给出存放位置。在【数据透视表字段

33、列表】中布局数据透视表。676. 合并计算【案例17】：合并计算。 源数据与目标表的准备 ； 按位置合并计算； 查看合并计算（分级显示按钮）。在【数据】| 【合并数据】对话框中选择源数据。687. 有关数据管理的其它说明 数据有效性审核另一种查询有数据列表的汇总数据透视图另一种合并计算691）数据有效性Excel将自动检查非法数据。 设置数据有效性，先选择欲定义有效数据的单元格区域，选择【数据】|【数据有效性】命令。 撤消数据有效性检查，只需单击【数据有效性】对话框中的【全部清除】按钮即可。若有效数据单元格中允许出现空值，应选中【忽略空值】复选框。702）审核对于现有数据的有效性检查。 先选择审核单元格区域；选择【数据】|【数据有效性】|【圈释无效数据】命令进行检查。 清除无效数据标识圈选择【数据】|【数据有效性】|【清除无效数据标识圈】命令。 715）另一种查询高级筛选实现逻辑或的关系，即只要满足一个条件。自动筛选实现逻辑与的关系，即同时满足各个条件。 726）有数据列表的汇总在分类汇总中，如果要把汇总结果显示在数据列表的下面，则选择【汇总结果显示在数据下方】命令。 737）数据透视图选