Excel2016电子表格 第5章 人们在日常生活、学习和工作中经常会遇到各种数据计算和分析问题。如教师统计学 生成绩、商业上进行销售统计、会计人员对财务报表进行分析等,这些都可以通过电子表格 处理软件来实现。Excel是一款强大的电子表格处理软件,它是MicrosoftOfice办公系列 软件中的一员大将。它不仅具有强大的数据处理分析功能,还提供了图表、财务、统计、求解 规划方程等工具和函数,可以满足用户各方面的需求,因此被广泛应用于财务、金融、统计、 行政和教育领域。Excel2016不仅继承了之前版本的各种强大功能,并且新增了几个更人 性化的功能,让我们的使用更得心应手。 1.新增了六个图表 Excel2016新增了六个图表,分别是树状图、旭日图、直方图、箱形图、瀑布图和排列图 , 这些图表可以帮助我们创建一些常用的数据可视化的财务信息、层次结构的信息和展示相 关统计数据中的属性。 2.一键式预测 在Excel的早期版本中,只能使用线性预测。在Excel2016中,FORECAST 函数进行 了扩展,允许基于指数平滑 ( ETS()… ) 此功能也可以作为新的 例如FORECAST.进行预测 , 一键式预测按钮来使用。在“数据”选项卡上,单击“预测工作表”按钮可快速创建数据系列 的预测可视化效果。 3.3D 地图 最受大家欢迎的三维地理可视化工具PowerMap经过了重命名内置在Excel中,可供 所有Excel2016的客户使用,它已重命名为3D 地图。我们可以通过单击“插入”选项卡上 的“3D 地图”随其他可视化工具一起找到。 4.墨迹公式 在Ofice中进行数学公式的输入一直是令我们头疼的难题,现在如果工作簿中包含了 复杂的数学公式,那我们可以在任何时间转到“插入”→“公式”→“墨迹公式”,如果你拥有触 摸设备,则可以使用手指或触摸笔手动写入数学公式,如果没有触摸设备,也可以使用鼠标 进行写入,Excel2016会自动将它转换为文本,还可以在进行过程中擦除、选择以及更正所 写入的内容。 5.操作说明搜索框 Excel2016中的功能区上的一个文本框,其中显示的是“告诉我您想要做什么”。这是 一个文本字段,我们可以在其中输入与接下来要执行的操作相关的字词和短语,这样可以快 速访问要使用的功能或要执行的操作,还可以选择获取与要查找的内容相关的帮助,或是对 输入的术语执行智能查找。 5.xel2016的基本操作 1 Ec 学会使用Excel首先要掌握工作簿、工作表和单元格这几个基本概念,认识Excel工作 窗口,了解Excel的数据类型和数据的录入。 5.1 Ecl基本概念 1.xe 工作簿是一个Excel的文件,其中可以包含一个或多个表格。工作簿就像一个文件夹, 把相关的表格或图表存在一起,便于处理,每张工作表由单元格组成。 1.工作簿 一个Excel文件就是一个工作簿,这个文件的扩展名默认为xlsx。当启动Excel时,会 自动新建一个工作簿,默认名称为“ xs,(”) 工作簿1.lx一个工作簿可以包含多张工作表。 2.工作表 一个新建的工作簿只包含一个工作表,这个工作表的名称为shet1,用户可以根据需要 添加或删除工作表,每一个工作表都有一个工作表标签,单击它可以实现工作表间的切换。 工作表中以数字标识行,以字母标识列。一张工作表最多可以包含1048576行,16384列, 是一张非常庞大的工作表。 3.单元格 单元格是Excel工作簿的最小组成单位,所有的数据都存储在单元格中,它的内容可以 是数字、字符、公式、日期、图形或声音文件等。在工作表编辑区中,行和列的交叉部分就称 为单元格,每一个单元格都有其固定的地址,用行号和列标进行标识,如A1指的是位于第 A列第1行的单元格。为了区分不同工作表的单元格,需要在单元格地址前加上工作表名 称,如Shet1!B6,表示的是工作表Shet1中的B6单元格。当前正在使用的单元格称为 “活动单元格”。 4.单元格区域 在Excel中,如果选定的是一个单元格区域,则用左上角单元格地址和右下角的单元格 地址共同表示,如A1:D6表示从A1到D6这个区域,共包含24个单元格。 5.2 Ecl工作窗口 1.xe 启动Excel2016,在屏幕上即可显示Excel2016的工作窗口,如图5-1所示。Excel 2016的工作窗口与Word2016的基本相同,不同的主要是编辑栏和工作表编辑区、工作表 标签等。 1.编辑栏:编辑栏是Excel特有的,用于显示和编辑数据、公式。编辑栏有三部分组 成,最左侧是名称框,当选择单元格或单元格区域时,显示活动单元格地址或区域名称;右端 是编辑框,当在工作表的某个单元格中输入数据时,编辑栏会同步显示输入的内容;中间是 “插入函数”按钮,单击它可以打开“插入函数”对话框,同时在它的左边会出现“取消”和“确 定”按钮。 2.工作区:显示由行和列交叉组成的单元格,用于编辑工作表中的数据。 3.工作表标签:位于工作簿窗口的左下角,默认名称为Shet1,单击不同的工作表标签可 Excel2016电子表格 137 第 章 ndows10+Ofic 删除、复制、移动、重命名等操作。 计算机基础(Wie2016) 138 在工作表间进行切换,在这个区域可以完成工作表的增加、 图5-1 Excel2016的工作窗口 5.1.3 Excel数据类型 在Excel的单元格中可以输入多种类型的数据,如文本、数值、日期时间、逻辑类型等。 1.文本型数据。在Excel中,文本型数据包括汉字、英文字母、空格等,每个单元格最多 可容纳32000个字符。默认情况下,文本型数据自动在单元格左对齐。当输入的字符串超 出了当前单元格的宽度时,如果右边相邻单元格里没有数据,那么字符串会往右延伸;如果 右边单元格有数据,超出的那部分数据就会隐藏起来,只有把单元格的宽度变大后才能显示 出来。 2.数值型数据。在Excel中,数值型数据包括0~9中的数字以及含有正号、负号、货币 符号、百分号等任一种符号的数据。默认情况下,数值自动沿单元格右边对齐。在录入数值 型数据时,有以下两种比较特殊的情况要注意。 .负数:在数值前加一个“-”号或把数值放在括号里,都可以输入负数。例如,要在 单元格中输入“-66”,可以输入“-66”或“(66)”,然后按Enter键都可以在单元格 中出现“-66”。 .分数:要在单元格中输入分数形式的数据,应先在编辑框中输入“0一个空格,然 后再输入分数,否则Excel会把分数当作日期处理。例如,要在单元格中输入分数 “2/3可在编辑框中输入“0一个空格,然后接着输入“2/3按一下Enter键,单 元格中就会出现分数“2/3”。和(”) ,(”) 和(”) ,(”) 3.日期时间型数据。在很多管理表格中,经常需要录入一些日期时间型的数据,这些 数据是可以加减计算的,所以输入时默认在单元格中右对齐。在录入日期时间型数据时要 注意以下几点。 . -2020-10-22020/10/2 输入日期:年、月、日之间要用“/”号或“ 号(”) 隔开,如“”或“”。 .输入时间:时、分、秒之间要用冒号隔开,如“10:29:36” 。 同时输入日期和时间:日期和时间之间应该用空格隔开,如 “ . 36”。 2020/10/210:29: 4. 逻辑类型。这种类型的数据只有两个值,表示真的“TRUE”和表示假的“FALSE”, 数据输入后会自动居中对齐。 5.1.4 Excel数据录入 Excel最主要的功能是帮助用户存储和处理数据信息,所有的操作都是在有数据内容 的前提下才是有效的。下面介绍数据录入的一些常用技巧,让用户在录入数据的时候可以 更加高效。 1. 录入数据的几种方法 工作表是由单元格组成,用户想要在工作表中添加数据,其实就是向单元格中输入数 据。对单元格的数据录入有以下两种方法: .在单元格中输入 【例5-1】在A1 单元格中输入“萍乡学院”,操作步骤如下: 步骤1:选中需要输入数据的单元格,如:A1 。 步骤2:当单元格边框变为黑色,说明此单元格已选中,我们直接录入内容即可,如图5-2 所示。 图5-2 在单元格中输入数据 .在编辑栏输入 有时需要录入的内容过长,可能超出所选单元格的边界,这时在单元格内编辑和修改变 得不太方便,就可以选择在“编辑栏”录入内容。 【例5-2】在A1 单元格中录入“2020-2021 学年第1学期软件工程1805 班成绩表”,操 作步骤如下: 步骤1:选中需要录入内容的单元格。 步骤2:在“编辑栏”录入数据,如图5-3所示。 图5-3 在“编辑栏”中输入数据 Excel2016 电子表格 139 第 章 140 计算机基础(Windows10+Ofice2016) 2.录入由数字组成的文本型数据 如果要输入的字符串全部由数字组成,如邮政编码、电话号码、身份证号、序号等,为了 避免Excel把它按数值型数据处理,在输入时就要将它们处理为文本型数据。 例如,成绩表中第一列数据为序号,当用户输入“001时,(”) 系统会将它自动识别为数值型 数据,所以在单元格中只显示“1,(”) 如果一定在单元格中输入以0开头的内容,则可以使用以 下两种方法。 .将单元格的格式设置为文本 在Excel中单元格的数据类型默认是“常规”类型,这是一种不包含任何特定的数字格 式。当向单元格中输入“001”,系统将它看成是一串数字,然而任何数字前面的“0”都是没有 数学意义的,所以系统会将其省略掉,所以需将单元格的格式改为文本格式。 【例5-3】在单元格中输入序号“001,(”) 操作步骤如下: 式”。 步骤1:选中需要更改格式的单元格或单元格区域,在“开始”选项卡中单击“数字格 步骤2:在弹出的“设置单元格格式”对话框中选择“数字”选项卡,在下面的“分类”列表 框中选择“文本”,如图5-4所示。 图5-4 设置单元格格式对话框 步骤3:输入“001”,就会显示成“001”。 .在需要输入的内容前加上撇号 上述方法有点麻烦,在输入由数字构成的文本型数据时,有一种更简便的方法,就是在 需要输入的内容前加上撇号,用以注释当前所输入的内容按文本格式处理。例如,在单元格 中输入‘001,就可以完成001这个文本型数据的输入。 3.使用自动填充录入内容 在Excel中录入数据时,最棒的功能莫过于自动填充,使用自动填充可以使输入或更改 内容的效率提高,节省时间。下面介绍在Excel中常用的几种自动填充的方法。 .使用自动填充复制内容 当需要在一列或者一行输入相同内容的时候,可以使用自动填充来快速实现。 【例5-4】在“成绩表”中添加一个新的字段“专业”,然后给所有的学生都加上“软件工 程”,操作步骤如下: 步骤1:首先打开“成绩表”,在“序号”字段后插入新的一列,在B2 单元格中输入字段 名“专业”,并在B3 单元格中输入“软件工程”。 步骤2:选中B3 单元格,在黑色边框的右下角有一个黑色的十字形,被称之为“填充 柄”,如图5-5所示。 图5-5 单击填充柄 步骤3:拖动填充柄到对应记录的最后一个单元格释放鼠标,或者双击填充柄,则表中 这一列单元格都以“软件工程”填充。 .填充序列 自动填充不仅可以复制内容,还可以对有序的序列进行自动填充,系统会根据前面单元 格的内容计算步长值自动对序列进行填充, 【例5-5】以序列填充“序号”列号,操作步骤如下: 步骤1:在不同列的前两个单元格中分别填“001”“002”和“001”“003”,选中前两个元素 所在的单元格。 步骤2:按住黑色边框的右下角的填充柄,向下拖动,一直到填充的值为16 和31,如 图5-6和图5-7所示。 .填充日期 填充日期可以选择不同的日期单位,如工作日,则在填充日期的时候将忽略周末或其他 国家法定节假日。 【例5-6】用序列填充日期,操作步骤如下: 步骤1:在A1 单元格中输入日期“2020-5-3”,选择需要填充的单元格A2:A10,同时也 包括数据所在单元格A1 。 步骤2:在“开始”选项卡下单击“填充”按钮,从下拉列表中选择“序列”选项,如图5-8 所示。 Excel2016 电子表格 141 第 章 计算机基础(Windows10+Ofice2016) 142 图5-6 以步长值为1填充序列图5-7 以步长值为2填充序列 步骤3:在弹出的“序列”对话框中选择“日期”类型,日期单位选择“工作日”,步长值设 置为1。 步骤4:单击确定显示结果,从显示结果可以看出,系统忽略了2020/5/9和2020/5/10 周六和周末这两天,如图5-9所示。 图5-8 选择填充按钮下的序列图5-9 填充结果 .创建自定义序列填充内容 如果用户所需的序列比较特殊,如张三、李四、王五、赵六,那么就需要先进行定义,再像 内置序列那样使用。 【例5-7】自定义序列“张三、李四、王五、赵六”,操作步骤如下: 步骤1:在菜单“文件”→“选项”→“高级”,移动垂直滚动条找到“常规”选项卡,在下方 可以找到“编辑自定义列表”按钮,如图5-10所示。 步骤2:单击此按钮弹出“自定义序列”对话框,在“输入序列”列表框中输入自定义序列 的全部内容,每输入一条按一次Enter键,完成后单击“添加”按钮,如图5-11所示。 图5-10 找到“编辑自定义列表”按钮 图5-11 “自定义序列”对话框 143 第 章 Excel2016 电子表格 计算机基础(Windows10+Ofice2016) 步骤3:整个序列输入完毕后,单击“确定”按钮。 步骤4:创建好自定义序列,在B1单元格中输入“张三”,用拖放填充柄的方法即可以 进行序列填充。 4.获取外部数据 Excel还可以获取外部数据,单击“数据”选项卡,就会出现一个获取外部数据的按钮 组,如图5-12所示,单击相应的按钮,就可以导入其他数据库(如Aces 、SQLServer等)产 生的文件,也可以导入网页、文本文件、XML文件中的数据等。 144 图5-12 获取外部数据按钮组 5.学生成绩分析 2 一学期结束了,院办教学干事小刘从教务系统中导出了软件工程专业几个班的学生成 绩表,下面协助小刘按以下要求进行成绩分析和管理。 1.打开“成绩表.lx件, Sh1重命名为“。文(”) xs将工作表“ et” 成绩表” 2.按照“001 、002……”的顺序填充“成绩表”中“序号”列。 3.根据原始成绩计算填充“总分”、“平均分”、“排名”这三列的内容。 4.在“成绩表”的“姓名”列前插入一列“班级”,学号的第3、4位代表学生所在班级,例 如,“180206”代表18级2班6号,请提取每个学生所在的班 级,并按如图5-13所示对应关系填写在“班级”列中。 5.标出“成绩表”中各科目不及格的成绩,例如将所在单元 格以粉红色填充,深红色文本显示。图5-13 学号、班级对应关系 6.美化“成绩表”:在成绩表顶端插入标题行“软件工程专 业2020-2021-1学期成绩表”,设置其格式让它在整个表格左右居中,将有小数的成绩列设 为保留一位小数的数值,适当加大行高列宽,改变字体、字号,设置对齐方式,设置适当的边 框和底纹使工作表更加美观。 7.在“成绩表”后面新建一个工作表“期末成绩分析”,求出每科的最高分、最低分、平均成 绩、统计每科优秀人数(及格人数(不及格人数( 80分以上), 60分到80分之间), 60分以下)。 8.根据“成绩表”的“姓名”和“平均分”两列数据,插入簇状柱形图,创建的图表在“成绩 表”的后面。 5.1 工作表的基本操作 2. 1.新建工作表 Excel2016新建的工作簿中默认只包含一张工作表Shet1,一个工作簿可以包含的工 作表数量是没有限制的。当用户需要在工作簿中新增工作表时,通常可以单击“工作表标签 区”中的“新工作表”按钮,如图5-14所示。 图5-14 “新工作表”按钮 或者在Shet1工作表标签上右击弹出快捷菜单,然后选择菜单项中的插入命令,则会 弹出“插入”对话框,这时除了可以插入工作表之外,还可以插入很多其他类型的对象,如图 表、宏表、对话框、数据透视表教程等,如图5-15所示。 图5-15 “插入”对话框 2.工作表的其他操作 在右击工作表标签时弹出的快捷菜单中,可以看到工作表的其他操作:删除、重命名、 移动或复制、保护工作表、工作表标签颜色、隐藏工作表等。移动和复制也是工作表的常用 操作之一。假如需要对“成绩表”进行备份,就需要进行复制操作。如果工作簿中有很多工 作表,需要把经常使用的工作表放在最前面,这时就要进行移动操作。工作表的移动和复制 可以在同一个工作簿中进行,也可以在不同的工作簿中进行。移动操作和复制操作如何区 分呢? 请看“移动或复制工作表”对话框,如图5-16所示,如果选中“建立副本”复选框,则进 行复制工作表的操作,否则就进行移动工作表的操作。 5.2 工作表的美化 2. 1.设置单元格格式 (1)设置数字格式 “开始”选项卡中“数字”组中有相应的按钮,如图5-17所示,使用这些按钮可以改变数 字(包括日期)在单元格中的显示形式,但是不改变编辑栏中的显示形式。 Excel2016电子表格 145 第 章