一、实验目的

（1）熟悉Excel工作表中数据的复制、粘贴。

（2）掌握Excel工作表中常用公式和函数的使用方法。

（3）掌握数据表的格式设置。

二、实验内容

时间：预计70 min。

打开“学生第一学期期末成绩表.xlsx”工作簿，完成“第一学期期末成绩”和“第二学期期末成绩”两个工作表中的空白项。

1.公式与函数应用

（1）利用SUM（）、AVERAGE（）分别计算每个学生的“总分”及“平均分”，且平均分保留一位小数。

（2）利用IF（）计算综合评定，其条件为：平均分大于等于90为“优”；平均分为80～89为“良”；平均分低于80为“中”。这里可以巧用名称框。

（3）利用RANK（）计算出每个学生的名次。

（4）利用COUNTIF（）计算出优秀数。

（5）利用COUNT（）计算总人数。这里要注意COUNT（）和COUNTA（）的区别。

（6）利用公式计算优秀率，公式是

优秀率＝优秀数/总人数

（7）利用MAX（）、MIN（）计算出各科的最高分、最低分。

2.工作表的格式化

（1）设置“第一学期期末成绩”工作表。

① 在第一行插入一空白行并设置行高为40，插入艺术字内容为“第一学期期末成绩”，艺术字样式为“填充：蓝色，主题色1；阴影”，合并后居中。

② 设置A2：J17单元格区域为“套用表格格式”→“表样式浅色8”。

③ 利用条件格式对平均分大于或等于90的成绩用红色（标准色）字体显示，平均分小于60的成绩用浅蓝色（标准色）填充。

④ 完成设置后将Sheet2更名为“第一学期期末成绩表”。

完成后的效果如图1.3.6所示。

图1.3.6　第一学期期末成绩表

（2）设置“第二学期期末成绩”工作表。

① 在第一行插入一空行，行高设置为50，插入艺术字内容为“第二学期期末成绩”，艺术字样式为“填充：蓝色，主题色1；阴影”，合并后居中。

② 设置A2：J14单元格区域为“套用表格格式”→“表样式浅色9”。

③ 将A16：A17单元格区域和I16：I17单元格区域方向设置为30°。

④ 将A15单元格区域对齐方向设置为垂直文本。

⑤ 利用条件格式将D2：D14单元格区域小于60，E2：E14单元格区域小于60，F2：F14单元格区域小于60的数值均用红色（标准色）标出。

完成后的效果如图1.3.7所示。

图1.3.7　第二学期期末成绩表

三、技能进阶

第1题：打开“excel.xlsx”工作簿并按下列要求进行操作。

时间：预计20 min。(www.daowen.com)

（1）将Sheet1工作表的A1：E1单元格合并为一个单元格，内容水平居中。

（2）在E4单元格内计算所有职工的平均年龄（利用AVERAGE函数计算，保留小数点后1位）。

（3）在E5和E6单元格内计算男职工人数和女职工人数（利用COUNTIF函数计算）。

（4）在 E7和 E8单元格内计算男职工的平均年龄和女职工的平均年龄（先利用 SUMIF函数分别求出年龄总和，保留小数点后1位，再利用总成绩/人数计算平均年龄）。

（5）最后将工作表命名为“年龄统计表”，保存excel.xlsx文件。

完成后的效果如图1.3.8所示。

图1.3.8　年龄统计表

第2题：打开“A6.xlsx”工作簿并按下列要求进行操作。

时间：预计20 min。

（1）设置工作表行、列。

① 在标题下插入一行，行高为17.75。

② 在“账目”为“224”之前插入两空行；随即将“201”和“211”的两行移至“224”一行之前。

③ 删除多余的空行。

（2）设置单元格格式。

① 标题文字格式：字体采用黑体，字号为 18，将 A1：E1单元格区域合并后居中，底纹使用深蓝色（标准色），字体颜色为“白色，背景1”。

② 单元格格式设置：表格中数据的单元格区域数字分类为货币，小数位数为 2位，应用货币符号是“￥”，负数格式为－1,234.00（红色）。

③ 将A4：A6单元格和A7：A9单元格合并后居中，“合计”单元格与其左侧单元格合并后居中；表头和“账目”一列单元格居中。

（3）设置表格边框线。

为表格设置“内部”框线，表格的上、下框线为粗线。

（4）设置批注。

为负值单元格插入批注“超支”。

（5）重命名并复制工作表。

将Sheet1工作表重命名为“预算表”，并将此工作表复制到Sheet2工作表中。

（6）设置打印标题。

在Sheet2工作表的行号为7的一行前插入分页线；设置标题（第1行）和表头行（第3行）为打印标题。

完成后的效果如图1.3.9所示。

图1.3.9　Sheet2工作表

第3题：打开“人力资源情况表.xlsx”工作簿并按下列要求进行操作。

时间：预计10 min。

（1）利用COUNTIF（）计算开发部职工人数置于C10单元格中。

（2）利用SUMIF（）计算开发部职工平均工资置于C11单元格中（保留2位小数）。

完成后的效果如图1.3.10所示。

图1.3.10　人力资源情况表