大学信息技术基础 - 万水书苑-出版资源网

第 4 章 电子表格处理软件 Excel 2010 

人们在日常生活、工作中会遇到各种各样的计算问题。如商业上要进行商业统计，教师 要对学生的成绩进行统计分析，各种报表的制作，理财、贷款表的制作，还有对数据的统计、 计算、筛选、分析等操作，都可以使用电子表格制作软件 Excel 2010 来实现。Excel 2010 已经 成为了电子表格制作中最流行的软件，也是用户在办公自动化软件中使用最多的软件之一。 

4.1 任务一——制作“教师信息表”

4.1.1 任务提出  Excel 2010 是一款用来存储和管理数据的办公自动化软件， 我们经常会使用它来制作学生 信息表、工资表、成绩单来存储信息。下面通过制作教师信息表来了解 Excel  2010  的基本使 用方法，如图 4­1 所示。 图 4­1  教师信息表  4.1.2 任务目标 l 熟悉 Excel 2010 的启动和退出方法，了解 Excel 2010 的窗口组成。 l 掌握工作簿的建立、保存、打开和关闭的方法。 l 掌握工作簿、工作表、单元格的基本概念。 l 掌握向工作表中录入内容的方法。 l 掌握数据类型的基本概念。 l 掌握编辑工作表和格式化工作表的基本方法。  4.1.3 任务准备 （1）工作窗口介绍。Excel 2010 的启动和退出与 Word 2010 类似，其工作窗口如图 4­2 所示。

图 4­2    Excel 2010 工作窗口 （2）工作簿。一个 Excel 文档文件称为一个工作簿，Excel 默认的文件名为工作簿 1、工 作簿 2……，扩展名为.xls。用户存盘时，应重命名一个与文档内容更贴近的文件名。 （3）工作表。工作表（Sheet）是一个二维表格，  Excel 默认有 3 张工作表，即 Sheet1、  Sheet2 和 Sheet3，通过单击“文件”选项卡，选择“选项”命令，在“Excel 选项”对话框内 的“常规”标签内还可以自定义新工作簿包含的工作表数，其最大设定值为 255，如图 4­3 所 示。在工作簿中，高亮度显示的工作表标签为当前工作表。 图 4­3  “Excel 选项”对话框 （4）工作表的行和列。一张工作表由 65536 行，256 列（A～Z 及 AA～IV 列）的网格线 组成。 名称框 行 活动单元格 编辑栏 列 工作表标签 插入工作表按钮 _{普通视图按钮 页面布局按钮 分页预览按钮}

（5）单元格。在工作表中，一行与一列交叉处的小方格称为一个单元格，一张工作表共 有 65536×256 个单元格，即 1600 多万个单元格，可见一张工作表能够处理足够多的数据，列 标和行号构成了一个单元格的地址。 （6）活动单元格。工作表中加黑框线的单元格为活动单元格或称为当前单元格。活动单 元格可通过单击鼠标随意指定，用户只能在活动单元格内输入数据。 （7）名称框。名称框用来显示活动单元格的地址。用户为快速定位某个单元格或某个选 定的单元格区域，还可以为其定义一个名称，该名称不再由列标和行号构成，而是用户自己定 的名称，例如，为图 4­2 的活动单元格 A16 定义一个“数据 1”名称，可以选择“公式”选项 卡“定义的名称”组，单击“定义名称”命令，打开如图 4­4 所示的“新建名称”对话框。在 “名称”文本框输入自定义名称“数据 1”后，单击“确定”按钮。此时，A16 单元格的地址 名称为“数据 1” ，无论活动单元格处在工作表的任何地方，只要在名称框中选中“数据 1” ， 则立即将活动单元格定位在 A16 单元格。 （8）编辑栏。编辑栏能够显示活动单元格的内容，用户通常在活动单元格内输入内容， 但也可以在编辑栏内输入内容，尤其在编辑公式和函数时更方便。  1．工作表的基本操作 （1）插入工作表。当工作簿的工作表不够用时，可以随时用插入工作表按钮插入若干个 新工作表，例如，在 Sheet3 后可以插入 Sheet4、Sheet5 等新表，也可在当前工作表上右击执 行“插入”命令插入新工作表。 （2）删除工作表。当多余的工作表需要删除时，可以单击“开始”选项卡，选择位于“单 元格”组的“删除工作表”命令或在要删除的工作表标签上右击执行“删除”命令。 （3）重命名工作表。 可以为某个工作表 Sheet 重新定义一个更有意义的名字， 如在 Sheet1  标签上右击执行“重命名”命令，使光标激活成为编辑状态，然后输入新的工作表名。 （4）移动或复制工作表。在同一个工作簿内移动或复制工作表，选中待移动或复制的工 作表标签，如 Sheet1，右击执行“移动或复制工作表”命令，打开“移动或复制工作表”对话 框，如图 4­5 所示。将光标移到要移动或复制到的目标位置，如 Sheet3，单击“确定”按钮， 则将 Sheet1 移动到 Sheet3 之前，若要复制 Sheet1，应在该对话框中选中“建立副本”复选项。 同理，在不同的工作簿之间移动或复制工作表，应先打开两个工作簿文件，在该对话框中选中 要移至或复制到哪一个工作簿，其余操作相同。 图 4­4  “新建名称”对话框 图 4­5  “移动或复制工作表”对话框  2．单元格的基本操作 （1）选定单元格。单击或用键盘移动键，可以确定某个单元格为活动单元格，使用鼠标

拖动或按下“Shift”键的同时使用键盘移动键，可以选定一个连续的单元格区域（如 A1:C6）， 如图 4­6 所示。按下“Ctrl”键的同时使用鼠标拖动，可以选定多个不连续的单元格区域，如 图 4­7 所示。 图 4­6    6 行×3 列单元格区域选择 图 4­7  选择多个不连续的单元格区域 （2）插入单元格。在活动单元格处单击“开始”选项卡，选择位于“单元格”组的“插 入单元格”命令，打开如图 4­8 所示“插入”对话框，可选择“活动单元格下移”或“活动单 元格右移”单选项，实现插入一个单元格的功能。 （3）删除单元格。在待删除的活动单元格处单击“开始”选项卡，选择位于“单元格” 组的“删除单元格”命令，打开如图 4­9 所示的“删除”对话框，可选择“右侧单元格左移” 或“下方单元格上移”单选项，实现删除一个单元格的功能。 图 4­8  “插入”对话框 图 4­9  “删除”对话框 （4）合并单元格。选定待合并单元格的区域，在“开始”选项卡中，单击“合并后居中” 按钮 ，实现单元格合并及居中，如果再单击“合并后居中”按钮 ，可以取消合并单元格。 同理，也可以在位于“单元格”组的“格式”按钮下拉菜单中，选择“设置单元格格式”命令， 打开如图 4­10 所示的“设置单元格格式”对话框，在“对齐”选项卡中选中“合并单元格” 复选项。要取消合并单元格，则去掉“合并单元格”复选框中的“√”即可。  3．输入数据 （1）直接输入。在活动单元格中输入数字并按 Enter 键后，Excel 默认识别输入的内容为 数值型，并将数字在单元格中右对齐。若输入文本型字符，在单元格中自动左对齐。要输入 “02985392000”类似电话号码的文本型数字，必须先输入英文的单引号，再输入数字。如果 直接输入 3/4，单元格中显示为 3 月 4 日，即系统自动识别为日期型，但要输入分数 3/4，应 先输入一个 0， 接着输入一个空格， 再输入 3/4。 对日期型数据， 可以输入 2010­4­26 或 2010/4/26。 （2）自动填充。在某个活动单元格内先填入数字  1，再移动光标到本单元格右下角的一 个黑色小方块处，该黑色小方块称为“填充柄” （此时空心“十”字光标变为黑色“十”字光 标），按下“Ctrl”键的同时使用鼠标向下拖动“填充柄” ，则按等差序列填充数值型数据，如 图 4­11 所示。

图 4­10  “设置单元格格式”对话框 还可先输入一个初始值，再向下选中若干个单元格或向右选中若干个单元格的待填充区 域，通过单击“开始”选项卡，在位于“编辑”组中，选择“填充”|“序列”命令，在如图  4­12 所示的“序列”对话框中设置好步长值，可按等差或等比序列自动填充数值。 图 4­11  自动填充数据 图 4­12  “序列”对话框 由于 Excel 内置了某些数据的填充序列， 所以用户可以以填充方式周期性地快速输入如星 期一、星期二、星期三、……、星期日，甲、乙、丙、……、辛，一、二、三……，具体数据 可参见“文件”选项卡中的“选项”|“高级”|“编辑自定义列表”命令，可打开“自定义序 列”对话框，如图 4­13 所示。用户也可以在“输入序列”文本框中输入自己经常要用的数据 序列，例如，CPU,RAM,CON,MOSE 等（注意必须使用英文的“,”作分隔符），然后单击“添 加”按钮，可以添加一个新序列。  4．表格的格式设置 （1）单元格数据类型设置。虽然 Excel 可以自动识别输入的数据类型，但用户可以通过 单击“开始”选项卡，选择“设置单元格格式”命令，在如图 4­14 所示的“设置单元格格式” 对话框“数字”选项卡中选择“数值”或“文本”及其他类型，可以事先为选定的单元格或单 元格区域设置好数据类型。如选择“数值”型，还可设置小数位数，选择“文本”型可以输入  00001 这样的文本。 （2）单元格边框设置。在默认情况下，工作表的网格线是浅灰色的，表明在打印时，网 格线是不显示的。为了显示出网格线，在“设置单元格格式”对话框中的“边框”选项卡中， 可以为选定的单元格或单元格区域设置不同颜色及不同线条的边框线，如图 4­15 所示。

图 4­13  “自定义序列”对话框 图 4­14  “数字”选项卡 图 4­15  “边框”选项卡 （3）字体、对齐方式和填充背景颜色及图案的设置。可以在“设置单元格格式”对话框 中的“字体”“对齐”和“填充”选项卡中，对选定的单元格或单元格区域设置字体、字形、 字号、字体颜色、文本的对齐方式、背景颜色及图案等参数。  5．行高和列宽的调整 （1）不精确调整。将光标移到相邻两行或相邻两列之间的网格线上，按住鼠标并拖动可 以随意调整行的高度或列的宽度。 （2）精确调整。选定单元格或单元格区域，单击“格式”按钮下拉菜单，选择“行高” 命令，在“行高”对话框中输入具体的值，可以精确设定行高，如图 4­16 所示。同理，选择 “列宽”命令，在“列宽”对话框中输入具体的值，可以精确设定列宽，如图 4­17 所示。 图 4­16  “行高”对话框 图 4­17  “列宽”对话框  4.1.4 任务实施 （1）启动 Excel 2010 应用程序，系统自动创建一个空白工作簿“工作簿 1” 。 （2）保存文件。输入文档的名称“教师信息表” ，保存类型为默认文件类型。

（3）录入数据。  1）输入“教师编号”内容。在 A1 单元格输入“教师编号” ，在 A2 单元格输入“j001” ， 将鼠标指针移到 A2 单元格右下角的填充柄上，此时鼠标指针呈现黑色“十”字形，按下鼠标 左键进行填充，拖曳填充柄至 A6 单元格，则填充了 j001～j005 的数字。使用快速填充的方法 完成剩余教师编号的输入，如图 4­18 和图 4­19 所示。 图 4­18  单击填充柄 图 4­19  拖曳填充  2）输入“姓名”及其他各列标题。在 B1 单元格中输入“姓名” ，在 C1、D1、E1、F1、  G1、H1、I1、J1 单元格中输入各列名称。在各列输入信息后如图 4­20 所示。 图 4­20  教师信息表 （4）编辑数据表信息。  1）将表中各字段加粗。选中表中“A1:J1”单元格区域，单击字体选项卡中的加粗按钮。  2）添加表头。在表中第 1 行的位置上，单击鼠标右键，选择“插入”命令，此时在数据 表的顶部插入新的一行，选中 A1:J1 区域，在“开始”选项卡的“对齐方式”分组中选择“合 并后居中”按钮，将 A1:J1 的单元格区域合并为一个单元格 A1，在  A1 单元格中输入“教师信息表” ，并设置字号 14 磅，字体加粗。 右击第 1 行，选择“行高”命令，在弹出的对话框中输入“25” ， 如图 4­21 所示。 （5）给表格添加边框。选中数据所在区域 A1:J14，在“开始” 选项卡中的“字体”分组中单击“边框”图标右侧的下三角按钮， 在弹出的下拉列表中选择“所有框线”选项，如图 4­22 所示。 图 4­21  设置行高

图 4­22  设置框线  4.1.5 任务拓展——条件格式 使用条件格式可以使满足一定条件的数据按用户设定的字体格式和颜色显示，以区别于 其他不同的数据。例如，先选定待筛选的单元格区域，在“样式”组中，单击“条件格式”按 钮，选择“新建规则”命令，在如图 4­23 所示的“新建格式规则”对话框中，选择规则类型 为“只为包含以下内容的单元格设置格式” ，将下拉组合框中的“介于”改选为“大于” ，在文 本框中输入“60” ，再单击“格式”按钮，打开如图 4­24 所示“设置单元格格式”对话框，选 择“红色”“加粗” ，则可以将选定区域中大于 60 的所有数据以红色粗体显示。 图 4­23  “新建格式规则”对话框

图 4­24  “设置单元格格式”对话框 

4.2 任务二——制作“成绩分析表”

4.2.1 任务提出 通过对学生成绩表中数据内容的计算，掌握 Excel 中公式和函数的使用。Excel 的强大之 处在于对数据的分析和计算能力。 建立以下文档，要求： （1）平均成绩和总成绩用公式计算，保留 2 位小数。 （2）平均分、最高分、最低分用公式计算，保留  2  位小数；优秀（≥90  分）、良好（＜90  分，并且≥80  分）、中等（＜80  分，并且≥70  分）、及格（＜70  分，并且≥60  分）、不及格 （＜60 分）人数用公式进行统计。 （3）不及格的成绩设置为红色、倾斜，90 分以上的成绩加粗。 （4）以“成绩分析表”为文件名保存。  4.2.2 任务目标 l 掌握公式的组成结构和使用方法。 l 掌握公式的引用方法。 l 掌握函数的基本结构。 l 掌握 Excel 常用函数的使用方法。 l 掌握函数的嵌套使用。  4.2.3 任务准备  1．输入公式 在单元格内输入用户自定义公式时，首先要输入“=” （等号），然后输入数字或某个单元 格地址与运算符组成的表达式，按“Enter”键后得到运算结果。例如，对 A4 和 B4 单元格的

数值进行求和计算，可在 C4 单元格内输入公式，如图 4­25 所示。按“Enter”键后求得计算 结果，如图 4­26 所示。 图 4­25  输入公式 图 4­26  运算结果  2．插入函数  Excel 内置了许多函数，可以利用这些函数完成各类统计计算或数据处理。例如，上面求 和的例子可以在 C4 单元格内通过单击“公式”选项卡，选择“插入函数”命令，在如图 4­27  所示的“插入函数”对话框中找到求和函数 SUM，单击“确定”按钮，出现如图 4­28 所示的 “函数参数”对话框，确认“Number1”的待求和区域 A4:B4 无误后，单击“确定”按钮。 图 4­27  “插入函数”对话框 图 4­28  “函数参数”对话框  3．公式的复制 在向一个单元格输入公式后，如果其他单元格也要用到同样的公式，可以采用复制公式 的办法，最简单的复制方法是使用“填充柄” 。例如，如图 4­29 所示，先在活动单元格内插入

求平均值函数  AVERAGE，求得结果后，使用“填充柄”向下拖动，可以将函数  AVERAGE  复制到其他单元格中求得其他平均分，如图 4­30 所示。 图 4­29  在一个单元格内插入函数 图 4­30  将函数复制到其他单元格  4．相对引用、绝对引用和混合引用 （1）相对引用。Excel  在复制公式时，通常引用的单元格区域不是照原样复制下来的， 而是参照原来公式对单元格引用区域的相对位置， 复制到目标位置后， 仍然保持相对位置不变 的原则自动计算出列标和行号，这叫做单元格区域的相对引用。例如，如图 4­31 所示，原公 式处在 C3 单元格（C3=A1+B1），从此处可以看出原公式与引用单元格 A1 和 B1 的相对位置， 即 C3 单元格与 A1、B1 单元格相距两行，且与 A1 相距两列，与 B1 相距一列。该公式复制到  D6 单元格后，如图 4­32 所示，与 D6 单元格相距两行、相距两列的一定是 B4 单元格，且相 距两行、相距一列的一定是 C4 单元格（D6=B4+C4），即保持相对位置不变的原则。 图 4­31  复制前公式 C3=A1+B1  图 4­32  将公式复制到 D6=B4+C4  （2）绝对引用。绝对引用是指在复制公式时，被引用的单元格区域复制到目标位置后， 其列标和行号不发生变化， 即原样复制。 绝对引用的表示方法是在列标和行号前面同时加上美 元符号$。 （3）混合引用。把相对引用和绝对引用结合起来使用就是混合引用，即只在列标前面加 上美元符号$或只在行号前面加上美元符号$。 据此，在公式中对单元格地址的引用有 3 种方式 4 种表示方法，如图 4­31 所示，选中 C3  单元格， 在编辑栏内选中公式的 B1 单元格地址， 反复按 F4 键， 引用方法按下列顺序 B1→$B$1  →B$1→$B1 周期性地变化。  4.2.4 任务实施 （1）打开“成绩分析表” ，如图 4­33 所示。 （2）完成第一个学生的“平均成绩”计算。选中 F3 单元格，选择插入函数 fx，在常用函 数中选择“AVERAGE”函数，出现“函数参数”对话框，如图 4­34 所示，如果默认参数不正 确，单击 Number1 后面的折叠按钮，重新选择参数，单击 C3 单元格，拖曳鼠标至 E3 单元格， 即选择“C3:E3”区域，如图 4­35 所示，单击其后的返回按钮，回到参数设置的对话框，单击 “确定”按钮，完成了第一个学生平均成绩的计算。

图 4­33  成绩分析表 图 4­34  AVERAGE 函数参数的设置 图 4­35  函数参数设置 （3）完成“平均分”的计算。单击  F3  单元格，拖动填充柄向下填充，完成所有学生的 平均分计算，如图 4­36 所示。 （4）完成“总成绩”的计算。选中 G3 单元格，输入公式“=C3+D3+E3” ，或者利用插入 函数 fx，选中常用函数中的“sum”函数，设置参数，完成对第一个学生的总成绩计算。其他 学生的总成绩计算，利用填充柄填充完成。 （5）利用函数 RANK.AVG 根据“平均成绩”字段完成“名次”字段的计算。选中 H3 单 元格，插入统计函数 RANK.AVG，出现如图 4­37 所示的对话框。在指定的“Number”文本框 中输入 F3，在“Ref”中输入“$F$3:$F$12” （绝对引用），单击“确定”按钮，完成一个学生 的名次计算，对其他学生的名次计算利用填充柄完成，结果如图 4­38 所示。

图 4­36  填充数据

图 4­37  RANK 函数参数的设置

（6）利用“AVERAGE”函数计算各科的平均分。选择 C13 单元格，插入“AVERAGE” 函数，选择参数后，计算数学课程的平均分，其他 2 门课的平均成绩，采用拖动填充柄向右填 充，完成各科平均分的计算。 （7）利用“MAX”函数，计算各科的最高分。选择 C14 单元格，插入常用函数“MAX” ， 选择参数，如图 4­39 所示。设置完成后单击“确定”按钮，完成数学课程最高分的计算，其 他课程最高分的计算，利用填充柄完成。 图 4­39  MAX 函数参数的设置 （8）利用“MIN”函数，计算各科的最低分。选择 C15 单元格，插入常用函数“MIN” ， 选择参数，单击“确定”按钮，完成数学课程最低分的计算，其他课程最低分的计算，利用填 充柄完成。 （9）利用“COUNTIF”函数，统计优秀、良好、中等、及格以及不及格人数。选择 C16  单元格，插入统计函数“COUNTIF” ，出现如图 4­40 所示的对话框，在“Range”文本框中输 入“C3:C12” ，条件“Criteria”文本框中输入“>=90” ，单击“确定”得到结果，其他各科利 用填充柄完成。 统计 “良好”“中等”“及格” 以及 “不及格” 人数， 方法类似， 使用 “COUNTIF” 完 成 ， 注 意 在 条 件 输 入 时 分 别 为 “ =COUNTIF(C3:C12,">=80")­COUNTIF(C3:C12,">=90") ” “=COUNTIF(C3:C12,">=70")­COUNTIF(C3:C12,">=80")”“=COUNTIF(C3:C12,">=60")­COUNTIF  (C3:C12,">=70")”“=COUNTIF(C3:C12,"<60")” 。 图 4­40  COUNTIF 函数参数的设置 （10）选中所有数据，居中对齐，选中“平均成绩”一列数据，右击，选择“设置单元 格格式”|“数字”|“数值”命令，保留 2 位小数，结果如图 4­41 所示。

图 4­41  完成数据的统计 

4.3 任务三——对“教师信息表”进行数据管理

4.3.1 任务提出 从“教师信息表”中通过“排序”和“筛选”的方式找到符合要求的数据。 （1）对“系别”进行排序。 （2）按自定义序列对“职称”进行排序。 （3）筛选“政治面貌”为“党员”的教师。 （4）使用“高级筛选”筛选出  2000  年以前参加工作，学历为研究生并且职称为副教授 的老师。 （5）使用条件格式标识表中的女教师。  4.3.2 任务目标 l 掌握排序的使用方法。 l 掌握筛选的使用方法。 l 掌握条件格式的使用方法。  4.3.3 任务准备  1．创建数据清单 在 Excel 中，同一列中的数据为同一类型，没有数据重复的行和列的二维表，即一个表由 若干行和若干列构成，表中的首行是每一列的标题，从首行的下一行开始为数据，每一列称为 一个字段，列标题称为字段名，每一行称为一条记录，这样的工作表可以称之为数据清单。例 如，图 4­38 所示的成绩分析表就是一张典型的数据清单。  2．数据排序 排序是指按用户指定的字段值的大小以升序或降序对记录重新排列次序，Excel 默认的排 序依据为：汉字以汉语拼音字母排列顺序排序，英文以英语字母排列顺序排序。排序时，先在

数据清单中单击任一单元格或选中整个数据清单（注意：不能只选择某一列数据，否则排序后 出现数据混乱），再单击“数据”选项卡，在“排序和筛选”组中，选择“排序”命令，打开 “排序”对话框，如图 4­42 所示。 图 4­42  “排序”对话框 以图 4­38 的数据清单为例，按降序排出总分的高低。可以看出在该对话框中可以按多个 关键字进行排序，如本例“主要关键字”选择“总分”字段，在总分值相同的情况下，还可多 次按下“添加条件”按钮增加“次要关键字” ，如可以选择“数学”等字段作为次要关键字， 直至可以进一步区分大小为止。若选中“数据包含标题”复选项，表明字段名所在的标题行不 参加排序，否则相反。单击“选项”按钮，还可选择按“行”或按“笔划”排序。  3．数据筛选 （1）自动筛选。用户如果要在数据清单中每次筛选出只满足一个条件的所有记录，可以 使用自动筛选功能。筛选时，先在数据清单中单击任一单元格，再单击“数据”选项卡，选择 “筛选”命令后，出现如图 4­43 所示的样式，例如，若要筛选出英语≥70 分的所有记录，应 单击“英语”右边的下拉按钮，在打开的下拉列表中选择“数字筛选”|“大于或等于”命令， 出现如图 4­44 所示的对话框，在文本框中输入“70” ，单击“确定”按钮即可。 图 4­43  自动筛选 图 4­44  “自定义自动筛选方式”对话框

若要取消自动筛选，可再单击一次 “筛选”命令按钮，则恢复原始数据。 （2）高级筛选。用户若要筛选出同时满足两个或两个以上条件的记录，必须使用高级筛 选功能。首先在数据清单中任选一个空白处作为条件区域，并输入筛选条件，如图 4­45 左下 方所示，然后单击“数据”选项卡，选择“高级”命令，在“高级筛选”对话框中可以选择“将 筛选结果复制到其他位置”单选项，单击“列表区域”文本框，然后拖动鼠标选择整个数据清 单，单击“条件区域”文本框，拖动鼠标选择条件区域，单击“复制到”文本框，在工作表的 空白处单击任一个单元格，最后单击“确定”按钮完成操作。 图 4­45  高级筛选  4.3.4 任务实施 （1）对“系别”进行排序。单击 H2 单元格，选择“开始”|“排序和筛选”|“升序”命 令，完成对系别的排序，如图 4­46 所示。 图 4­46  系别排序后的数据 （2）按自定义序列对“职称”进行排序。单击“文件”|“选项”|“高级”|“编辑自定 义列表”命令，在弹出的“自定义序列”对话框中，输入序列“教授”“副教授”“讲师” ，如 图 4­47 所示。输入完成后，单击“添加”按钮，输入的序列添加到了自定义序列中。 选中表中任一单元格，单击“开始”|“排序和筛选”|“自定义排序”命令，弹出“排序” 对话框，在“主要关键字”中，选择“职称” ，在“次序”列表框中，选择“自定义序列” ，并 选定刚才添加的职称序列，如图 4­48 所示。 设置完成后单击“确定”按钮，即实现了职称的自定义序列排序，如图 4­49 所示。

图 4­47  “自定义序列”对话框 图 4­48  “排序”对话框 图 4­49  职称排序结果 （3）筛选“政治面貌”为“党员”的教师。单击表中任一单元格，单击“开始”|“排序 和筛选”|“筛选”命令，在表中每一个字段下面出现下三角按钮。单击“政治面貌”中的下 三角按钮，选中“党员”前的复选框，单击“确定”按钮，即筛选出所有的党员教师。 （4）使用“高级筛选”筛选出  2000  年以前参加工作，学历为研究生并且职称为副教授 的老师。选中教师信息表中下面的任一空白区域，如选择  A17:C18  作为设置条件区域，分别 输入高级筛选的条件，如图 4­50 所示。单击“数据”|“排序和筛选”|“高级”命令，弹出“高 级筛选”对话框，在“方式”选项组中选择“将筛选结果复制到其他位置” 。在“列表区域” 中，单击右边的折叠按钮，选择数据表 A2:J14 区域，并返回。在“条件区域”中，单击右边 的折叠按钮，选择条件区域 A17:C18，并返回。在“复制到”编辑框中，单击右边的折叠按钮， 选择任一空白单元格（如 A20） ，返回，如图 4­51 所示。单击“确定”按钮后，筛选出所求结 果，如图 4­52 所示。

图 4­50  设置高级筛选条件

图 4­51  “高级筛选”对话框的设置

图 4­52  筛选结果

（5）使用条件格式标识表中的女教师。选中“性别”列，单击“开始”|“条件格式”|  “突出显示单元格规则”|“文本包含”命令，在弹出的“文本中包含”对话框中输入“女” ，

设置默认格式，如图 4­53 所示，单击“确定”按钮即可完成使用条件格式标识表中的女教师。 图 4­53  条件格式参数的设置 

4.4 任务四——创建销售图表

4.4.1 任务提出 根据电器销售表的统计情况，以图表的形式显示各电器的销售情况。图表可以使数据更 加形象、直观、有趣、易于阅读和评价。图表也可以帮助我们分析和比较数据。  4.4.2 任务目标 l 了解图表的各种类型。 l 掌握图表的创建方法。 l 掌握图表的编辑方法。  4.4.3 任务准备  1．图表概念 在 Excel 中图表是指将工作表中的数据用图形表示出来。 当基于工作表选定区域建立图表 时，Excel 使用来自工作表的值，并将其当作数据点在图表上显示。数据点用条形、线条、柱 形、切片、点及其他形状表示，这些形状称作数据标示。建立了图表后，可以通过增加图表项， 如数据标记、图例、标题、文字、趋势线、误差线及网格线来美化图表及强调某些信息。大多 数图表项可被移动或调整大小。也可以用图案、颜色、对齐、字体及其他格式属性来设置这些 图表项的格式。  2．创建图表 将如图  4­54  所示的工作表中的数据用图形来表达则更直观易懂，生成图表前，一定要 考虑好使用工作表中的哪些数据作为创建图表的数据源，选择的区域可以连续也可以不连 续，在选定好数据区域后，单击“插入”选项卡“图表”组中，可以在“柱形图”的按钮中 选择“所有图表类型”命令，在如图  4­55  所示的“插入图表”对话框中选择所需要的图表 类型（共有  11  个）后，再选择其图表子类型，然后单击“确定”按钮插入图表。当然也可 在“图表”组中，直接选择“柱形图”“折线图”或“饼图”等图表类型的某个图表子类型， 在当前工作表中完成插入图表工作， 之后也可将该图表以默认名为 Chart1 移到工作簿中作为 新工作表。

图 4­54  选定数据区域 图 4­55  “插入图表”对话框  3．编辑图表 图表创建好后，还可以进行编辑修改，例如，在图表的垂直坐标轴区（纵轴刻度处）双 击鼠标左键或单击鼠标右键，打开“设置坐标轴格式”对话框，在“坐标轴选项”区域可以自 定义设置刻度最大值、主要刻度单位等值，如图 4­56 所示。同理，在水平（类别）轴、图例、 数据系列格式、图表区和绘图区双击鼠标左键或单击鼠标右键，可以打开其相应的对话框，设 置其参数。 图 4­56  “设置坐标轴格式”对话框

4.4.4 任务实施  1．创建图表 打开“电器销售统计表” ，使用“Ctrl”键分别选中图表中需要使用的两列数据：品名和 合计列，如图 4­57 所示。 选择“插入”选项卡，单击“柱形图”|“簇状柱形图”命令，确认后生成如图 4­58 所示 的图表。 图 4­57  选择图表所使用数据 图 4­58  生成图表  2．更改图表类型 选择已经创建好的柱形图表，选择“设计”|“更改图表类型”|“饼图”命令，如图 4­59  所示。 3．更改图表布局 选定建立好的饼形图，单击“设计”|“图表布局”|“布局 1”命令，如图 4­60 所示，将 饼形图按照百分比的方式来显示，如图 4­61 所示。 图 4­59  饼图效果 图 4­60  图表布局选项  4．添加图表标题 选定图表，单击“布局”|“图表标题”|“居中覆盖标题”命令，为图表添加标题，如图  4­62 所示。

图 4­61  图表布局效果 图 4­62  为图表添加标题  5．添加数据标签 选定图表，单击“布局”|“数据标签”|“最佳匹配”命令，数据会显示在图表的最佳位 置上，方便数据之间的查看和比较。 

4.5 任务五——对商品进行分类汇总

4.5.1 任务提出 建立一张如图 4­63 所示的工作表，并完成下列操作： （1）计算出每一种商品的价值（价值=单价×数量），填入相应的单元格中。 （2）将“单价”栏中的数据设置为货币样式，并将列宽设定为 20。 （3）按商品名称的拼音字母顺序进行升序排列，并按商品名称对数量进行分类汇总求和 （替换当前分类汇总，汇总结果显示在数据下方）。 图 4­63  部分商品统计表  4.5.2 任务目标 l 掌握分类汇总。 l 掌握数据透视表的使用。

4.5.3 任务准备 按某个字段进行分类，并对一个或多个数值型字段求和或求平均值等，也可以为非数值 型字段计数，这些操作称为分类汇总。如对某采购商品进行分类汇总： （1）先对分类字段（例如，商品名称）进行排序（类似合并同类项） 。 （2）单击“数据”选项卡，选择“分类汇总”命令，在如图 4­64 所示的“分类汇总”对 话框中选择“分类字段”为“商品名称” ，选择“汇总方式”为“求和” ，选择“选定汇总项” 可以是数量和价值等数值型字段。 图 4­64  “分类汇总”对话框  4.5.4 任务实施 （1）计算出每一种商品的价值操作步骤如下：  1）在 D3 单元格中输入“=B3×C3” ，按“Enter”键。  2）用“填充柄”复制公式求其他单元格的结果。 （2）设置货币样式及列宽操作步骤如下：  1）将 B3:B12 区域选中，单击“开始”选项卡，选择“格式”|“设置单元格格式”命令， 弹出如图 4­65 所示的“设置单元格格式”对话框，在“数字”选项卡的“分类”区域中选择 “货币” ，在“货币符号”中选择“￥” ，单击“确定”按钮。 图 4­65  “设置单元格格式”对话框

2）单击“格式”按钮，选择“列宽”命令，在弹出的“列宽”对话框中输入“20” ，单 击“确定”按钮。 （3）排序及分类汇总操作步骤如下：  1）在进行分类汇总前，应先对数据清单进行排序，在数据清单中单击任一单元格，然后 单击“数据”选项卡，选择“排序”命令，弹出如图 4­66 所示的对话框，在“主要关键字” 的下拉列表框中选择“商品名称” ，单击“确定”按钮。  2）单击“数据”选项卡，选择“分类汇总”命令，弹出如图 4­67 所示的对话框，在“分 类字段”的下拉列表框中选择“商品名称” ，在“汇总方式”中选择“求和” ，在“选定汇总项” 中选择“数量” ，最后单击“确定”按钮。 图 4­66  “排序”对话框 图 4­67  “分类汇总”对话框  4.5.5 任务拓展  1．数据透视表 分类汇总只适合对一个字段分类，对一个或多个数值型字段进行汇总。如果要对多个字 段分类并汇总，就需要利用数据透视表这个工具来解决问题。例如，如图 4­68 所示，要统计 各科室各职称的人数，既要按“科室”分类，又要按“职务”分类，此时要用到数据透视表。 图 4­68  数据清单 首先在数据清单中单击任一单元格，然后再单击“插入”选项卡，选择位于“表格”组 的“数据透视表”命令，弹出如图 4­69 所示的“创建数据透视表”对话框，按默认选择单击 “确定”按钮，在如图 4­70 所示的工作表中，将位于“选择要添加到报表的字段”处的“科 室”字段拖至工作表左上角的“行字段”处，将“职称”字段拖至“列字段”处，再将“职称” 字段拖至“值字段”处，则将完成后的数据透视表作为新建工作表插入到当前工作簿中。

图 4­69  “创建数据透视表”对话框 图 4­70  创建数据透视表  2．合并计算 对于两个具有相同表结构的数据清单，可以使用“合并计算”功能做统计运算。例如， 针对如图 4­71 和图 4­72 所示的“十一月工资”表和“十二月工资”表，可以合并计算出这两 个月工资表中某个字段的总额，或平均值、最大值等，如图 4­73 所示。 图 4­71  十一月工资 图 4­72  十二月工资 图 4­73  十一月、十二月工资统计 使用“合并计算”功能操作步骤如下： （1）打开“十一月工资”“十二月工资”两个工作表（或工作簿文件） 。 （2）建立好如图 4­73 所示的“十一月、十二月工资统计”工作表（或工作簿文件），单 击 B3 单元格，然后单击“数据”选项卡，选择“合并计算”命令，打开如图 4­74 所示的“合 并计算”对话框，选择“函数”为“求和” 。

图 4­74  “合并计算”对话框 （3）在“引用位置”文本框处，单击其右侧的折叠按钮，然后在“十一月工资”表中选 择  B3:B6  区域的数据，再单击该折叠按钮以展开对话框，在“合并计算”对话框中单击“添 加”按钮，将刚才选定的区域添加到“所有引用位置”的列表框中，如图 4­75 所示。使用同 样的操作将“十二月工资”表中的  B3:B6  区域添加到该列表框中，最后单击“确定”按钮， 得到如图 4­76 所示的 B3:B6 区域实发工资总额值。 图 4­75  “合并计算”对话框 图 4­76  统计结果 同理，欲求得“十一月、十二月工资统计”工作表中的最高奖金，先在该表中单击  C3  单元格，其余操作参照上述的第（2）步。注意：在“合并计算”对话框中的“所有引用位 置”列表框中要删除刚才添加的所有数据，然后选择“函数”为“最大值” ，再参照上述的 第（3）步，分别将十一月、十二月工资表中的 C3:C6 区域数据添加到对话框中，如图 4­76  所示为最后的统计结果。

习题 4 

4­1 选择题  1．在 Excel 2010 单元格中输入字符型数据，当宽度大于单元格宽度时，哪种描述是错误的（ ）。  A．无须增加单元格宽度  B．当右侧单元格已经有数据时也不受限制，允许超宽输入  C．右侧单元格中的数据将被覆盖，右侧单元格被覆盖的部分会丢失

D．右侧单元格中的数据将被覆盖，右侧单元格被覆盖的部分不会丢失  2．在 Excel 2010 中，下面描述正确的是（ ）。  A．单元格的名称是不能改动的  B．单元格的名称可以有条件地改动  C．单元格的名称是可以改动的  D．单元格是没有名称的  3．在 Excel 2010 中，公式中引用了某单元格的相对地址，（ ）。  A．当公式单元格用于复制和填充时，公式中的单元格地址随之改变  B．仅当公式单元用于填充时，公式中的单元格地址随之改变  C．仅当公式单元用于复制时，公式中的单元格地址随之改变  D．当公式单元用于复制和填充时，公式中的单元格地址不随之改变  4．在 Excel 2010 中，有关嵌入式图表，下面哪个描述是错误的（ ）。  A．对生成后的图表进行编辑时，首先要激活图表  B．图表生成后不能改变图表类型，例如：三维变二维  C．表格数据修改后，相应的图表数据也随之变化  D．图表生成后可以向图表中添加新的数据  5．在 Excel 2010 中（ ）单元格。  A．只能选定连续的  B．可以选定不连续的  C．可以有若干个活动  D．反相显示的都是活动  6．Excel 2010 中，有关列宽的描述，下面错误的说法是（ ）。  A．系统默认列的宽度是一致的  B．不调整列宽的情况下，系统默认设置列宽自动以输入的最多字符的长度为准  C．列宽不随单元格中的字符增多而自动加宽  D．一次可以调整多列的列宽  7．在  Excel  2010  工作表中，在不同单元格输入下面内容，其中被  Excel  2010  识别为字符型数据的是 （ ）。  A．1999­3­4  B．34%  C．$100  D．南京溧水  8．在 Excel 2010 中，将某一单元格内容“星期一”向下拖放填充 6 个单元格，其内容为（ ）。  A．连续 6 个“星期一”  B．连续 6 个空白  C．星期二、星期三、星期四、星期五、星期六、星期日  D．以上都不对  9．关于 Excel 2010 的数据筛选，下列说法中正确的是（ ）。  A．筛选后的表格中只含有符合筛选条件的行，其他行被删除  B．筛选后的表格中只含有符合筛选条件的行，其他行被暂时隐藏  C．筛选条件只能是一个固定的值  D．筛选条件不能由用户自定义，只能由系统确定  10．在 Excel 2010 中，单元格可设置自动换行，也可以强行换行，强行换行可按（ ）键。  A．Ctrl+Enter  B．Alt+Enter

C．Shift+Enter  D．Tab  11．在 Excel 2010 中，某工作表 D2 单元格中含有公式“=A2+B2­C2” ，则将该公式复制到该表的 D3 单 元格时，D3 单元格中的结果应是（ ）。  A．=A2+B2­C2  B．=A3+B3­C3  C．=B2+C2­D2  D．无法复制  12．Excel 2010 中，添加边框、颜色操作，在格式下拉按钮中选择（ ）。  A．设置单元格格式  B．行  C．列  D．工作表  13．在 Excel 2010 中，不连续选择单元格，只要按住（ ）的同时选择所要选的单元格。  A．Ctrl  B．Shift  C．Alt  D．ESC  14．下列关于 Excel 2010 打印与预览操作的说法中正确的是（ ）。  A．输入数据时是在表格中进行的，打印时肯定有表格线  B．尽管输入数据时是在表格中进行，但如果不特意进行设置，那么打印时将不会有表格线  C．可在“页面设置”中选“工作表”选项卡，然后单击“网格线”前面的“□”使“√”消失， 这样打印时会有表格线  D．除了在“页面设置”中进行设置可以打印表格线外，再没有其他方式可以打印出表格线了  15．已在 Excel 2010 某工作表的 F10 单元格中输入了八月，再拖动该单元格的“填充柄”往上移动，则 在 F9、F8、F7 单元格会出现的内容是（ ）。  A．九月、十月、十一月  B．七月、六月、五月  C．五月、六月、七月  D．八月、八月、八月  16．在 Excel 2010 中，在进行分类汇总前必须（ ）。  A．先按欲分类汇总的字段进行排序  B．先对符合条件的数据进行筛选  C．先排序，再筛选  D．各选项都不需要  17．在 Excel 2010 中，数据清单是工作表中（ ）。  A．没有空行的区域  B．没有空列的区域  C．任何区域  D．没有空行和空列的区域  18．在 Excel 2010 中，函数有函数名和函数参数，参数可以是（ ）。  A．数字、文本、逻辑值  B．数字、文本、日期/时间  C．数字、逻辑值、日期/时间  D．数字、文本、单元格名称、单元格引用  19．在 Excel 2010 中复制公式时，为使公式中的（ ），必须使用绝对地址（引用）。  A．单元格地址随新位置而变化  B．单元格地址不随新位置而变化  C．范围随新位置而变化  D．范围大小随新位置而变化  4­2 操作题  1．按照如图 4­77 所示内容建立一张工作表，并完成下列操作： （1）计算学生的总分。 （2）计算学生的平均分（结果保留 1 位小数）。 （3）用函数 COUNTIF()统计各学科成绩≥20 分的人数。 （4）将学生的姓名和平均分用簇状柱形图表示出来并存放到 Sheet1 中。

图 4­77  工作表 1  2．建立如图 4­78 所示的工作表，完成下列操作： （1）将工作表 Sheet1 的 A1:C1 单元格合并为一个单元格，水平对齐方式设置为居中，计算“人数” 及“所占百分比”列（注：一定要按照先填写 C3:C6 之中某一单元格公式后，其他单元格复制（可用填充 柄）的方法完成此题，所占百分比=人数/总计）， “所占百分比”列单元格格式为“百分比”型（保留  2  位 小数）。 （2）选取“Sheet1”的“职称” （不包括“总计”行）和“所占百分比”两列单元格的内容建立“分离 型圆环图” （系列产生在“列” ），在数据标签内显示数据标志，标题为“师资情况图” ，插入到表的  A9:D19  单元格区域内。  3．建立学生成绩单工作表，如图 4­79 所示，将“学生编号” （如 001、002 等）、 “平均成绩”“平均成绩 等级”列内容补充完整，要求： “平均成绩”“平均成绩等级”列内容必须采用函数进行计算得出。 图 4­78  工作表 2  图 4­79  工作表 3  4．按照如图 4­80 所示内容建立一张工作表，并完成下列操作： （1）将标题改为红色楷体，16 号字，标题行行高设为 30。 （2）将歌手编号用 001、002、……、010 来表示，并居中（提示：把格式设为文本）。 （3）将所有评委给出的大于或等于 6 分的得分信息用红色表示（使用条件格式设置）。 （4）给整个表格加上蓝色细实线作为表格线，标题除外。 ≥

图 4­80  得分统计表  5．按照如图 4­81 所示内容建立一张工作表，并完成下列操作： （1）计算“人数”列的“合计”项。 （2）计算“所占比例”列（所占比例=人数/合计），结果保留 2 位小数。 图 4­81  某大学在校生人数情况表  6．按照如图 4­82 所示内容建立一张工作表，并完成下列操作： （1）用函数 RANK.AVG 根据“平均”字段完成“名次”字段的填写（注：一定要按照先填写 I2:I5 之中某 一单元格公式后，其他单元格用复制（可用填充柄）的方法完成此题，最高分填 1（第 1 名），依次类推。 （2）将 A1:I5 单元格内容居中，并将“英语”“数学”“化学”“总分”“平均”5 列单元格中小于 60 分 的单元格的字体用红色表示（注：随着单元格中数据值的变化，该单元格字体发生变化）。 图 4­82  工作表  7．按照如图 4­83 所示内容建立一张工作表，并完成下列操作： （1）计算所有学生的总分。 （2）用函数 COUNTIF 统计各学科≥90 分的人数。 （3）将统计结果按科目用簇状柱形图表示出来存放到 Sheet1 中。 图 4­83  各科成绩工作表

8．按照如图 4­84 所示内容建立一张工作表，并完成下列操作： （1）用公式求出实发工资（实发工资=基本工资+奖金+补贴­房租）。 （2）将奖金和补贴两列数据互换位置。 （3）在职工工资表中以“姓名”和“实发工资” 为数据区建立一个簇状柱形图图表，刻度最大值为 1500， 主要刻度单位为 150，图表位置放在 A11:J25 区域内。 图 4­84  职工工资表  9．按照如图 4­85 所示内容建立一张工作表，并完成下列操作： （1）利用公式填写评语，条件是：月考 1 与月考 4 成绩比较，如月考 1 大于月考 4，则评语为“成绩退 步，严重警告中” ，如果月考 4 大于月考 1 成绩 40 分以上，则评语为“进步神速，希望更加努力” ，除此以外 的情况评语都为“进步平平，望加倍努力” 。 （2）根据表格的特点，给表格制作一张能够反映某同学  4  次月考成绩升降情况的独立图表（要求：图 表类型为簇状柱形图，图表标题为“月考成绩情况表” ，图表名称为“图表 1” ）。 图 4­85  成绩表