第5章 Excel电子表格
学习目标
 熟练编辑工作表
 熟练设置工作表格式
 熟练工作表数据处理
 熟练绘制工作表图标
Excel被称为电子表格，其功能非常强大，可以进行各种数据的处理、统计分析和辅助决策操作等，广泛应用于管理、统计财经、金融等众多领域。最新的Excel 2010能够用比以往使用更多的方式来分析、管理和共享信息，从而帮助用户做出更明智的决策。新的数据分析和可视化工具会帮助用户跟踪重要的数据趋势，将文件上传到Web并与他人同时在线工作，用户可以从Web浏览器来随时访问Excel表格中的重要数据。
5.1Excel工作环境介绍

同Word 2010一样，Excel的功能区也是由选项卡组成的。除此之外Excel还包括多个其特有的元素，如图5.1所示。


图5.1Excel 2010工作环境


1. 行号和列标
工作表中单元格的地址由列标加行号构成。列标由英文字母A、B、C…表示，行号由阿拉伯数字1、2、3…来表示。
2. 单元格
工作表中的矩形小方格称为单元格。单元格名称由列标+行号构成。例如，第C列第2行的单元格名称为C2。其用于显示和存储用户输入的所有内容以及运算结果。
3. 单元格引用
引用单元格是通过特定的单元格符号来表示工作表上的单元格或单元格区域，指明公式中所使用的数据位置。通过单元格的引用，可以在公式中使用工作表中不同单元格的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格，甚至其他应用程序中的数据。
1) 引用类型
在Excel中引用单元格有三种方式： 相对引用、绝对引用和混合引用。
(1) 相对引用。
默认情况下，Excel使用的是相对引用。相对引用是基于公式引用单元格的相对位置。如果公式所在的单元格的位置变化，引用也随之改变，但引用的单元格与包含公式的单元格之间的相对位置不变。表示方法为“列标+行号”，如A5。




(2) 绝对引用。
绝对引用指向工作表中固定的单元格，表示方法在行号和列号前加“$”符号，例如，$A$5。在某些操作中，若需要固定引用某个单元格中的内容来进行计算，那么这个单元格的地址就要采用绝对引用，它在公式中始终保持不变。
(3) 混合引用。
混合引用指的是在一个单元格地址中，既有绝对引用又有相对引用。如果需要在复制公式时只有行或只有列保持不变，那么就要使用混合引用。如A$3，$K8等。

用户可以使用快捷键F4在相对引用、绝对引用和混合引用表示方式之间进行切换。
此外，不同工作表之间单元格的引用，需要在单元格地址前加工作表名称，中间用“！”分隔。不同工作簿间引用单元格时需要用下面格式： “［工作簿名］工作表名!单元格地址”。
2) 引用运算符
引用单元格或单元格区域时采用3种引用运算符，冒号、逗号和空格。
(1) 冒号。
若要引用连续的单元格区域(即一个矩形区)，应使用冒号“:”分隔引用区域中的第一个单元格和最后一个单元格。
(2) 逗号。
若要引用不相交的两个区域，则使用联合运算符，即逗号“，”。例如， B2:C5，C8:D11。
(3) 空格。
引用两个区域交叉重叠部分的数据。例如， B3:C7C5:D9。
4. 名称框
名称框用于定义单元格或单元格区域的名称，或者根据名称查找单元格或单元格区域。在默认状态下，显示当前活动单元格的位置。
5. 编辑栏
编辑栏用于输入和修改工作表数据。在工作表中的某个单元格中输入数据时，编辑栏中同时会显示输入内容。若在单元格中输入公式，则在单元格中显示计算结果，而在编辑栏中显示所用公式。
6. 工作表标签
工作表标签位于工作表编辑区的左下方，由工作表标签滚动按钮、工作表标签和“插入工作表”按钮组成。
5.2案例

5.2.1【案例1】Excel工作表编辑
案例描述
(1) 创建一个新工作簿文件，内容如图5.2所示。
(2) 在A1单元格输入标题“学生成绩表”，在A2:F2中输入如图5.2所示的各列标题。


图5.2学生成绩表


(3) 用填充柄自动填充“学号”，从201412001开始，按步长为1的等差序列顺序填充，其余单元格按所给内容输入。
(4) 将“数学”列和“外语”列交换。
(5) 将单元格A1:F1合并并居中，设置标题(学生成绩表)为20号黑体字、加粗。
(6) 套用表格格式“表样式中等深浅2”，为数据清单加粗外边框、细内边框。
(7) 第二行表头区设置文字水平居中，字体加粗。
(8) 工作表Sheet1重命名为“学生成绩表”。
(9) 自动调整行高与列宽。
(10) 为D3:F12数据设置条件格式，数值小于60的单元格文本突出显示为红色、加粗。
(11) 保存工作簿文件为“学生成绩表.xlsx”，结果如图5.3所示。


图5.3案例1样文


知识要点
(1) Excel文件的建立、保存与打开。
(2) 工作表的选择、添加、删除、重命名、复制与移动。
(3) 单元格的输入、编辑等基本操作。
案例操作
(1) 数据填充。选中单元格A3，将光标移至选中单元格的右下角，此时光标变成实心十字，称为填充柄。向下拖动填充柄选取填充区域。然后单击自动填充选项，然后在弹出的列表中选择“填充序列”单选按钮。
(2)  合并并居中。选中A1:F1单元格，“开始”选项卡→“对齐方式”组中的“合并后居中”按钮。
(3)  表格自动套用格式。选中数据清单，“开始”选项卡→“样式”组中的“套用表格格式”按钮，然后从弹出的下拉列表中选择需要的表格样式。
(4) 设置外边框与内边框。选中数据清单，“开始”选项卡→“字体”组

按钮，从弹出的下拉列表中选择“边框和底纹”命令，然后在弹出的“设置单元格格式”对话框中进行设置，如图5.4所示。


图5.4“设置单元格格式”对话框



(5) 工作表重命名。双击工作表标签Sheet1，输入“学生成绩表”后，按Enter键。
(6) 调整行高与列宽。选中需要调整的行或列，“开始”选项卡→“单元格”组中的“格式”按钮。
(7) 设置条件格式。选中需要设置条件格式的单元格，“开始”选项卡→“样式”组中的“条件格式”按钮，在弹出的列表中选择“突出显示单元格规则”→“小于”，然后在弹出的“小于”对话框中进行设置，如图5.5所示。


图5.5“小于”对话框


5.2.2【案例2】Excel工作表基本操作
案例描述
(1) 创建一个新工作簿文件，内容如图5.6所示。


图5.62014年CBA全明星赛首发球员票选结果


（2） 在A1单元格输入标题“2014年CBA全明星赛首发球员票选结果”，在单元格A2： D2中输入如图5.6所示的各列标题，其余单元格按所给内容输入。
（3） 将单元格A1:D1合并并居中，设置标题为楷体_GB2312、14磅、加粗。
（4） 将数据清单外边框设置为红色双线、内边框设置为黑色单线，标题行设置黄色底纹。
（5） 第二行表头区设置文字水平居中，字体加粗。
（6） 工作表Sheet1重命名为“2014年CBA全明星赛首发球员票选结果”。
（7） 对“票数”列设置条件格式，用绿色数据条实心填充票数列。
（8） 保存工作簿文件为“2014年CBA全明星赛首发球员票选结果.xlsx”，结果如图5.7所示。


图5.7案例2样文


知识要点
(1) Excel文件的建立、保存与打开。
(2)  工作表的选择、添加、删除、重命名、复制与移动。
(3)  单元格的输入、编辑等基本操作。
案例操作
(1) 设置底纹。选中需要设置底纹的单元格，“开始”选项卡→“字体”组

按钮，然后从弹出的下拉列表中选择需要的底纹颜色。
(2)  设置条件格式。选中需要设置条件格式的单元格，“开始”选项卡→“样式”组中的“条件格式”按钮→“数据条”。
5.2.3【案例3】Excel公式应用
案例描述
利用图5.8所示的数据，完成下列操作。


图5.8某书店图书销售情况表


(1) 将工作表Sheet1的单元格A1:F1合并为一个单元格，水平对齐方式设置为居中。
(2) 用填充柄自动填充“图书编号”，从1001开始，按步长为1的等差序列顺序填充。
(3) 利用公式计算“销售额”(销售额=销售数量*单价)。
(4) 利用公式计算“总计”及“所占百分比”(所占百分比=销售额/总计)，“所占百分比”单元格格式为“百分比”型(小数点后保留2位)，结果如图5.9所示。


图5.9案例3样文


(5) 将“所占百分比”列数据复制到Sheet2工作表的A列中。
知识要点
(1) 工作表的修饰、公式的应用。
(2) 相对引用和绝对引用的应用。
案例操作
(1) 利用公式计算“销售额”。选中单元格E3，输入公式“=C3*D3”，然后按Enter键。利用填充柄计算其余销售额。
(2)  利用公式计算“总计”。选中单元格E9，输入公式“=E3+E4+E5”，然后按Enter键，利用填充柄计算其余总计。
(3)  利用公式计算“所占百分比”。选中单元格F3，输入公式“=E3/$E$9”，然后按Enter键。利用填充柄计算其余所占百分比。
(4) 设置单元格格式为“百分比”型。选中需要设置格式单元格，“开始”选项卡→“数字”组%
按钮。
(5)  保留2位小数。选中需要设置格式单元格，“开始”选项卡→“数字”组

或

按钮。也可以通过“设置单元格格式”对话框实现单元格格式与小数位数的设置，如图5.10所示。


图5.10设置小数位数


（6） 添加数据到Sheet2工作表。复制数据，然后切换到Sheet2工作表，“开始”选项卡→“剪贴板”组中的“粘贴”下拉按钮→“选择性粘贴”，然后在弹出的“选择性粘贴”对话框中，选择“值和数字格式”单选按钮。
5.2.4【案例4】Excel函数应用
案例描述
利用图5.11所示的数据，完成下列操作。


图5.11学生成绩表


(1) 用函数计算总成绩。
(2) 用函数计算每个学生是否通过，三个科目的平均分＜60为不通过，否则通过。
(3) 用函数统计各科优秀的人数(成绩≥90为优秀)。
(4) 用函数计算优秀率，优秀率=(优秀人数/总人数)，保留一位小数，如图5.12所示。


图5.12案例4样文


知识要点
工作表函数的应用。
案例操作
(1) 利用函数计算总分。选中单元格G3，“公式”选项卡→“函数库”组中的“插入函数”按钮，然后在“插入函数”对话框选择SUM函数，在“函数参数”对话框中设置函数参数。然后，利用填充柄求其余学生的总分。
(2) 计算“是否通过”列。选中单元格I3，“公式”选项卡→“函数库”组中的“插入函数”按钮，然后在“插入函数”对话框选择IF函数，在弹出的“函数参数”对话框中进行IF函数参数设置，如图5.13所示。


图5.13IF函数参数设置


然后，将光标放置<60之前，再单击编辑栏“×”左侧的箭头，选择AVERAGE函数，再在弹出的“函数参数”对话框中设置AVERAGE函数参数。最后，按“确定”按钮。
完成后，单元格I3中的公式为“=IF(AVERAGE(D3:F3)<60，“不通过”，“通过”)”。其余学生是否通过利用填充柄完成。
(3) 利用COUNTIF函数计算优秀人数。选中单元格D13，“公式”选项卡→“函数库”组中的“插入函数”按钮，在“或选择类别”下拉列表框中，选择“统计”，然后在“选择函数”列表框中选择COUNTIF函数。然后设置COUNTIF函数参数，如图5.14所示。


图5.14设置COUNTIF函数参数


(4) 计算优秀率。需要使用COUNTIF函数统计优秀的人数和COUNT函数统计总人数。选中单元格D14，先插入COUNTIF函数，然后在编辑栏输入“/”号，再插入COUNT函数。
5.2.5【案例5】Excel图表应用
案例描述
用图5.15所示数据， 完成如下操作。


图5.15某地区经济增长指数对比表


(1) 将Sheet1工作表的A1:F1单元格合并为一个单元格，内容水平居中； 用公式计算三年各月经济增长指数的平均值，保留小数点后2位，将A2:F6区域的全部框线设置为双线样式，颜色为蓝色，将工作表命名为“经济增长指数对比表”，保存EXCEL5.XLSX文件。
(2) 选取A2:F5单元格区域的内容建立“带数据标记的堆积折线图”,(系列产生在“行”)，图表标题为“经济增长指数对比图”，图例位置在底部，网格线为分类(X)轴和数值(Y)轴显示主要网格线，将图插入到表的A8:F18单元格区域内，如图5.16所示。


图5.16案例5样文


知识要点
图表的插入、编辑与修饰。
案例操作
(1) 创建图表。选取用于创建图表的数据区域，“插入”选项卡→“图表”组中的“柱形图”按钮，从弹出的下拉列表中选择“簇状柱形图”。
(2)  设置横坐标标题。选中图表，“图表工具/布局”选项卡→“标签”组中的“坐标轴标题”→“主要横坐标轴标题”→“坐标轴下方标题”。
(3)  设置纵坐标标题。选中图表，“图表工具/布局”选项卡→“标签”组中的“坐标轴标题”→“主要纵坐标轴标题”→“竖排标题”。
5.2.6【案例6】迷你图的制作
案例描述
用图5.17所示数据，在F列以各季度销售数据为数据源，为各电器创建迷你折线图，并在折线图上显示出高点、低点、首点、尾点，结果如图5.18所示。


图5.17某商店电器销售情况表




图5.18案例6样文


知识要点
迷你图表的插入、编辑与修饰。
案例操作
(1) 创建迷你图。选中单元格F3，“插入”选项卡→“迷你图”组中的“折线图”按钮，然后在“创建迷你图”对话框中设置参数。单击“数据范围”文本框后的折叠按钮，从屏幕上选取用于创建迷你图的数据区域。用同样方法创建其余单元格中的迷你图。
(2)  显示迷你图表特殊点。选中单元格F3，“迷你图工具/设计”选项卡→“显示”组，勾选“高点”“低点”“首点”“尾点”复选框。
5.2.7【案例7】数据透视表
案例描述
对图5.19所示数据，分页显示各班级男女生各科平均成绩，数据保留一位小数。


图5.19学生成绩单


知识要点
数据透视表的使用。
案例操作
选中数据清单任一单元格，“插入”选项卡→“表格”组中的“数据透视表”。然后进行数据透表布局设置，如图5.20所示。


图5.20数据透视表布局设置


5.2.8【案例8】数据排序
案例描述
(1) 以图5.21所示数据为例，按“数学”成绩降序排序。
(2)  以图5.21所示数据为例，以“计算机”为主要关键字升序排序，“外语”为次要关键字降序排序。
(3)  以图5.21所示数据为例，将数据清单按照“李亮、王晓阳、高明、王桐、华小安、赵胜、张经、叶子”的顺序排序。



图5.21案例8样文


知识要点
工作表数据排序。
案例操作
(1) 单关键字排序。选中数据清单中任一单元格，“数据”选项卡→“排序和筛选”组中的“排序”按钮。然后在弹出的“排序”对话框中“主要关键字”后的下拉列表中选择“数学”选项，在“次序”下的下拉列表中选择“降序”。
(2)  多关键字排序。选中数据清单中任一单元格，先设置“主要关键字”，再单击“添加条件”按钮，设置“次要关键字”，如图5.22所示。


图5.22多关键字排序设置


(3) 按自定义序列排序，如图5.23所示。


图5.23按自定义序列排序设置


(4) 添加自定义序列，如图5.24所示。



图5.24添加自定义序列


5.2.9【案例9】筛选
案例描述
(1) 以图5.21所示数据为例，用自动筛选方法查找出计算机优秀(成绩≥90)和不及格的学生记录。
(2)  用高级筛选方法查找出计算机优秀(成绩≥90)或外语优秀(成绩≥90)的学生记录，筛选结果从第12行开始显示。
知识要点
自动筛选和高级筛选的操作方法。
案例操作
(1) 自动筛选。选中数据清单中任一单元格，“数据”选项卡→“排序和筛选”组中的“筛选”按钮。单击“计算机”列后的下三角，然后从弹出的下拉列表中选择“数据筛选”→“自定义筛选”，再在弹出的“自定义自动筛选方式”对话框中进行筛选设置，如图5.25所示。


图5.25自动筛选条件设置


(2) 高级筛选。首先建立条件区域，然后选中数据清单中任一单元格，“数据”选项卡→“排序和筛选”组中的“高级”按钮，最后设置高级筛选参数。参数设置及高级筛选结果，如图5.26所示。


图5.26高级筛选参数设置及筛选结果


5.2.10【案例10】分类汇总
案例描述
对图5.21所示数据进行分类汇总。分类字段为“性别”，汇总方式为“平均值”，汇总项为各科目，汇总结果显示在数据下方，汇总结果如图5.27所示。


图5.27案例10样文


知识要点
分类汇总的使用。
案例操作
选中数据清单中任一单元格，“数据”选项卡→“分级显示”组中的“分类汇总”按钮，然后在“分类汇总”对话框中进行参数设置，如图5.28所示。


图5.28分类汇总参数设置

5.3本章小结

本章介绍了Microsoft Office系列办公自动化软件中的三个重要成员之一： 电子表格软件Excel 2010。本章主要介绍了如何使用Excel电子表格处理软件，在工作表中对复杂的数据内容进行操作与处理。以案例的形式贯穿各知识点的介绍，通过日常工作中常见的事务为用户介绍如何使用软件中的各项操作设置功能，进而掌握各功能的用法。通过对本章的学习，可了解Excel常用功能； 熟练掌握常用功能的操作技巧； 学会建立、保存和管理文档，达到在实际生活中自如运用办公软件解决实际问题的目的。
实验8Excel工作表编辑

【实验目的】
(1) 掌握Excel文件的建立、保存与打开。
(2) 掌握工作表的选择、添加、删除、重命名、复制与移动。
(3)  掌握单元格的输入、编辑等基本操作。


实验81



实验81制作课程表
(1) 创建一个新工作簿文件，在sheet1中建立“课程表”，内容如图5.29所示。


图5.29实验81样文


(2) 在B2单元格中输入“星期一”后，利用填充柄填充单元格C2:F2中内容。
(3) 利用自定义序列功能，填充单元格A3:A6。
(4) 在单元格A2中绘制斜线表头，添加内容如图5.29所示。
(5) 将单元格A1:F1单元格合并并居中，设置标题(课程表)为20号黑体字。
(6) 设置单元格A3:A6内容设为18号楷体字，并设置水平和垂直分别居中。
(7) 将单元格A2字体设为10号楷体。
(8) 为单元格B2:F2添加底纹，颜色为“橙色，强调文字颜色6，淡色40%”。
(9) 为工作表加粗外边框、细内边框。


实验82


(10) 将Sheet1重命名为“课程表”。
实验82制作学生成绩表
打开实验82.xlsx文件，实现其样文效果，如图5.30所示。


图5.30实验82样文


(1) 将单元格A1:F1合并并居中，并添加黄色底纹。
(2) 将工作表中的文字水平居中。
(3) 套用表格格式“表样式中等深浅12”，并设置数据清单所有边框为单实线。
(4) 用图标集为数据清单标识不同范围的数据。其中，成绩

标识； 60≤成绩＜70，用

标识； 70≤成绩＜80，用

标识； 80≤成绩＜90，用

标识； 其余成绩，用

标识。
实验83制作火车时刻表
打开实验83.xlsx文件，实现其样文效果，如图5.31所示。


实验83




图5.31实验83样文



(1) 将Sheet1工作表的A1:E1单元格合并为一个单元格，内容水平居中。
(2) 将A2:E6区域的底纹颜色设置为红色、底纹图案类型和颜色分别设置为灰色和黄色。
(3) 将工作表命名为“列车时刻表”。
实验84制作降雨量统计表
打开实验84.XLSX文件： 实现其样文效果，如图5.32所示。


图5.32实验84


(1) 将Sheet1工作表的A1:E1单元格合并为一个单元格，内容水平居中。
(2) 将“全年平均值”行和“月最高值”列的内容格式设置为数值型，保留小数点后两位。
(3) 利用条件格式将B3:D14区域内数值大于或等于100.00的单元格字体颜色设置为绿色(绿色的RGB值为： 0,176,80)。
(4) 将工作表命名为“降雨量统计表”。


实验85

实验85制作职工工资情况表
打开实验85.XLSX文件，实现其样文效果，如图5.33所示。
(1) 将Sheet1工作表的A1:E1单元格合并为一个单元格，内容水平居中。


图5.33实验85样文


(2) 利用条件格式将总工资大于或等于6000的单元格文字设置为绿色(RGB值： 0,176,80)，把A2:E17区域格式设置为套用表格格式“表样式浅色2”。
(3) 将工作表命名为“职工工资情况表”。
(4) 复制该工作表为SheetA工作表。

实验9Excel公式与函数

【实验目的】
(1) 掌握工作表的修饰、公式的应用。
(2)  理解相对引用和绝对引用。
(3)  掌握工作表函数的应用。


实验91


(4)  掌握图表的插入、编辑与修饰。
(5)  学会数据透视表的使用。

实验91差旅报销管理
打开实验91.xlsx文件，实现其效果，如图5.34所示。
(1) 在“费用报销管理”工作表“日期”列的所有单元格中，标注每个报销日期属于星期几，例如日期为“2013年1月20日”的单元格应显示为 “2013年1月20日 星期日”，日期为“2013年1月21日”的单元格应显示为“2013年1月21日 星期一”。
(2) 如果“日期”列中的日期为星期六或星期日，则在“是否加班”列的单元格中显示“是”，否则显示“否”(必须使用公式)。


图5.34实验91样文


(3) 使用公式统计每个活动地点所在的省份或直辖市，并将其填写在“地区”列所对应的单元格中，例如“北京市”“浙江省”。
(4) 依据“费用类别编号”列内容，使用VLOOKUP函数，生成“费用类别”列内容。对照关系参考“费用类别”工作表。
(5) 在“差旅成本分析报告”工作表B3单元格中，统计2013年第二季度发生在北京市的差旅费用总金额。
(6) 在“差旅成本分析报告”工作表B4单元格中，统计2013年员工钱顺卓报销的火车票费用总额。
(7) 在“差旅成本分析报告”工作表B5单元格中，统计2013年差旅费用中，飞机票费用占所有报销费用的比例，并保留2位小数。
(8) 在“差旅成本分析报告”工作表B6单元格中，统计2013年发生在周末(星期六和星期日)的通信补助总金额。

实验92销售订单明细表
打开实验92.xlsx文件，实现样文效果，如图5.35所示。
(1) 请对“订单明细表”工作表进行格式调整，通过套用表格格式方法将所有的销售记录调整为“表样式浅色 10”的外观格式，并将“单价”列和“小计”列所包含的单元格调整为“会计专用”(人民币)数字格式。


图5.35实验92样文


(2) 根据图书编号，请在“订单明细表”工作表的“图书名称”列中，使用VLOOKUP函数完成图书名称的自动填充。“图书名称”和“图书编号”的对应关系在“编号对照”工作表中。

实验92


(3) 根据图书编号，请在“订单明细表”工作表的“单价”列中，使用VLOOKUP函数完成图书单价的自动填充。“单价”和“图书编号”的对应关系在“编号对照”工作表中。
(4) 在“订单明细表”工作表的“小计”列中，计算每笔订单的销售额。
(5) 根据“订单明细表”工作表中的销售数据，统计所有订单的总销售金额，并将其填写在“统计报告”工作表的B3单元格中。
(6) 根据“订单明细表”工作表中的销售数据，统计《MS Office高级应用》一书在2012年的总销售额，并将其填写在“统计报告”工作表的B4单元格中。

(7) 根据“订单明细表”工作表中的销售数据，统计隆华书店在2011年第三季度的总销售额，并将其填写在“统计报告”工作表的B5单元格中。
(8) 根据“订单明细表”工作表中的销售数据，统计隆华书店在2011年的每月平均销售额(保留2位小数)，并将其填写在“统计报告”工作表的B6单元格中。

实验93


实验93期末成绩表
打开实验93.xlsx，实现其样表效果，如图5.36所示。
（1） 将“第一学期期末成绩”工作表套用表格格式“表样式浅色 16”，将第一列“学号”列设为文本，将所有成绩列设为保留两位小数的数值，设置居中对齐。


图5.36实验93样文


（2） 利用sum和average函数计算每一个学生的总分及平均成绩。
（3） 学号第 4、5 位代表学生所在的班级，例如，“C120101”代表12级1班。请通过函数提取每个学生所在的专业并按下列对应关系填写在“班级”列中： 
“学号”的4、5位对应班级
011班
022班
033班
（4） 根据学号，请在“第一学期期末成绩”工作表的“姓名”列中，使用VLOOKUP函数完成姓名的自动填充。“姓名”和“学号”的对应关系在“学号对照”工作表中。

实验94员工档案表
打开实验94.xlsx，实现其样表效果，如图5.37所示。


图5.37实验94样文




实验94

(1) 对“员工档案表”工作表进行格式调整，将所有工资列设为保留两位小数的数值。
(2) 根据身份证号，在“员工档案表”工作表的“出生日期”列中，使用MID函数提取员工生日，单元格式类型为“yyyy'年'm'月'd'日'”。
(3) 根据入职时间，在“员工档案表”工作表的“工龄”列中，使用TODAY函数和INT函数计算员工的工龄，工作满一年才计入工龄。
(4) 引用“工龄工资”工作表中的数据来计算“员工档案表”工作表员工的工龄工资，在“基础工资”列中，计算每个人的基础工资(基础工资=基本工资+工龄工资)。
(5) 根据“员工档案表”工作表中的工资数据，统计所有人的基础工资总额，并将其填写在“统计报告”工作表的B2单元格中。
(6) 根据“员工档案表”工作表中的工资数据，统计职务为项目经理的基本工资总额，并将其填写在“统计报告”工作表的B3单元格中。
(7) 根据“员工档案表”工作表中的数据，统计天天公司本科生平均基本工资，并将其填写在“统计报告”工作表的B4单元格中。
实验95计算机销售统计图表
打开实验95.xlsx文件，实现其样表效果，如图5.38所示。


图5.38实验95样文


(1) 将单元格A1至E1合并并居中，同时输入“2014年计算机销售统计图表”。
(2) 以A8至E12区域为数据源，在工作表中插入“簇状柱形图”。
① 设置图表布局为“布局3”。
②  设置图表标题为“2014年计算机销售统计表”，并设置图表标题艺术字样式为“渐变填充紫色，强调文字颜色4，映像”。
③  设置主要纵网格线为“主要网格线”。

④  调整图表大小，并将图表放置A9至E23单元格区域。
实验96数据透视表的应用


实验96


打开实验96，实现其样表效果，如图5.39和图5.40所示。


图5.39实验96数据透视表1




图5.40实验96数据透视表2


实验10Excel数据管理与分析

【实验目的】
(1) 掌握工作表数据排序。
(2) 掌握自动筛选和高级筛选的操作方法。
(3) 掌握分类汇总的使用方法。

实验101排序的应用
打开实验101.xlsx文件，实现其样表效果，如图5.41所示。


实验101




图5.41实验101样文


(1) 合并并居中单元格A1至I1，在其中输入内容“2014年计算机专业录取表”，并文字设置为华文楷体、16号、加粗。
(2) 合并A2~A3、B2~B3、C2~C3、G2~G3、H2~H3、I2~I3单元格区域。
(3) 计算总成绩和平均成绩，保留小数点后2位。
(4) 利用RANK()函数计算名次。
(5) 将第2至14行的行高设置为23。
(6) 为数据清单添加内外边框线。
(7) 将A2至I3单元格区域的样式设置为“强调文字颜色5”，并将单元格中字体设置为加粗。
(8) 按照名次对数据清单进行升序排序。
将第5、7、9、11和13行的A至I列单元格样式设置为“40%强调文字颜色5”； 第4、6、8、10和12行的A至I列单元格样式设置为“20%强调文字颜色5”。

实验102排序的应用2
打开实验102.xlsx文件，实现其样表效果，如图5.42所示。


图5.42实验102样文


(1) 将Sheet1工作表的A1:F1单元格合并为一个单元格，内容水平居中。
(2) 计算“总积分”列的内容(金牌获10分，银牌获7分，铜牌获3分)，按递减次序计算各队的积分排名(利用RANK函数)。
(3) 按主要关键字“金牌”降序次序，次要关键字“银牌”降序次序，第三关键字“铜牌”降序次序进行排序。
(4) 将工作表命名为“成绩统计表”。
实验103筛选的应用
打开实验103.xlsx文件，按要求，实现其样表效果。
(1) 对工作表“计算机专业成绩单”内数据清单的内容进行自动筛选，条件为数据库原理、操作系统、体系结构三门课程均大于或等于75分，对筛选后的内容按主要关键字“平均成绩”的降序次序和次要关键字“班级”的升序次序进行排序，结果如图5.43所示。


实验103




图5.43实验103自动筛选样文


（2） 对工作表“产品销售情况表”内数据清单的内容按主要关键字“分公司”的降序次序和次要关键字“季度”的升序次序进行排序，对排序后的数据进行高级筛选(在数据清单前插入四行，条件区域设在A1:G3单元格区域, 请在对应字段列内输入条件，条件是： 产品名称为“空调”或“电视”且销售额排名在前20名，工作表名不变，结果如图5.44所示。



图5.44实验103高级筛选样文


实验104高级筛选应用
打开实验104.xlsx文件，按要求实现其样表效果，如图5.45所示。


实验104




图5.45实验104样文


对工作表“产品销售情况表”内数据清单的内容建立高级筛选，在数据清单前插入四行，条件区域设在B1:F3单元格区域，请在对应字段列内输入条件，条件是：  “西部2”的“空调”和“南部1”的“电视”， 销售额均在10万元以上，工作表名不变，保存为实验104.XLSX工作簿。

实验105分类汇总的应用
打开实验105.xlsx文件，实现其样表效果，如图5.46所示。


图5.46实验105样文


(1) 将标题“职员登记表”所在行的单元格A1:G1合并成一个单元格，单元格的水平对齐方式为“居中”，字号为“16”，字体为“楷体_GB2312”。
(2) 在Sheet2中，将数据按照性别进行分类，汇总男、女工资的平均值。