实验7-10（有详细步骤）

实验7 Excel操作基础

一、实验目的

1． 掌握工作表的建立、工作簿的保存及打开的操作。 2． 掌握工作表的基本操作及工作表的编辑与设置。 3． 初步掌握公式的复制方法。

二、实验内容与操作步骤

在Excel环境下依次完成以下各操作。 1． Excel的启动与退出

⑴ 启动：单击“开始|所有程序|Microsoft Office|Microsoft Excel 2010”命令 ⑵退出：单击“文件”按钮，然后在弹出的菜单中选择“关闭”命令，或单击Excel窗口右上角的“关闭窗口”按钮2． 新建工作簿并录入数据 ⑴ 启动Excel。

⑵ 在工作表Sheet1！A1：E9中输入如下表所示的工作表数据

实验表7-1 学生成绩表

学生成绩表 学号 20023001 20023002 20023003 20023004 20023005 20023006 合计 姓名 张珊嘉 李世应 王武学 罗陆院 何海美 陈大丽 计算机 89 96 67 55 89 98 外语 67 87 89 70 90 86 平均分 。

(3)以E71.XLSX为文件名存入D:\\EX7中。

(注：保存后先不要关闭工作簿，继续完成以下各操作) 3．工作表字体的设置

选定区域A2：E2，右击鼠标，从弹出的快捷菜单中选择“设置单元格格式”命令，在弹出的对话框中单击“字体”标签，将字体设置为：黑体、倾斜、16号、蓝色字体。

1

4．在“平均分”之前插入“总分”列

单击E列的列号，然后单击“开始”功能区的“单元格”选项组中的“插入”按钮，或右击E列的列号，然后在弹出的快捷菜单中的选择“插入”命令。

5．列宽的设置

⑴选定A列、B列，然后单击“开始”功能区的“单元格”选项组中的“格式”按钮，在弹出的下拉菜单中的选择“列宽”命令，输入列宽值12，单击“确定”铵钮。

⑵ (选定不连续的列）选定B列，按CTRL+单击D列，然后单击“开始”功能区的“单元格”选项组中的“格式”按钮，在弹出的下拉菜单中的选择“自动调整列宽”（最合适列宽）命令。

⑶分别双击C、E、F各列列号的右边框，设置各列为最合适列宽。 6．工作表标题“学生成绩表”对齐方式的设置

选定A1：F1单元格，然后单击“开始”功能区的“单元格”选项组中的“格式”按钮，在弹出的下拉菜单中的选择“设置单元格格式”命令，在弹出的对话框中单击“对齐”标签，设置“水平对齐”为：跨列居中，“垂直对齐”为：居中。

7．公式的复制

⑴采用公式复制的方法，计算各学生的总分与平均分。

在E3单元输入公式：=C3+D3，然后双击E3单元格的填充柄。在F3单元输入公式：=（C3+D3）/2，然后双击F3单元格的填充柄

⑵采用“自动求和”的方法，计算各科、总分及平均分的全班合计数。

单击C9单元格，然后单击“公式”功能区的“函数库”选项组中的“自动求和”按钮，在C9单元格中显示“=SUM（C3：C8）”后，按回车键。拖曳C9单元的填充柄，将公式复制到区域D9：F9中。

8．数字格式的设置

⑴将区域Sheet1!C3：E8中的数据显示格式设置成2位小数的数值形式。

选定C3：E8单元格区域，然后单击“开始”功能区的“单元格”选项组中的“格式”按钮，在弹出的下拉菜单中的选择“设置单元格格式”命令，在弹出的对话框中单击“数字”标签，单击“分类”中的“数值”项，将“小数位数”设置成2位小数。

(2)将区域Sheet1!F3：F8中的数据显示格式设置成“常规”形式。 与操作⑴类似。 9．表格边框的设置

给表格添加“内外边框线”：选定A1：F9区域，然后单击“开始”功能区的“单元格”选项组中的“格式”按钮，在弹出的下拉菜单中的选择“设置单元格格式”命令，在弹出的对话框中选择“边框”标签，单击“预置”中的“外边框”和“内部”按钮，最后单击“确定”按钮。

2

10．将工作表Sheet1改名为“成绩表”

右击工作表标签Sheet1，在弹出的快捷菜单中单击“重命名”命令，输入新名“成绩表”后按回车键。

11．复制工作表并完成相关操作

⑴ 将“成绩表”工作表复制一个备份：按住Ctrl键，同时单击该工作表标签向左或向右拖曳，依次松开鼠标和Ctrl键后，生成一个标签为“成绩表（2）”的备份工作表。

⑵ 清除区域内容：选定要清除内容的区域“成绩表（2）！A2：F2”，按“Delete”键（区域的格式仍保留）。

⑶ 区域信息的“全部清除”：选定要全部清除信息的区域“成绩表（2）！C3:F8”，单击“开始”功能区的“编辑”选项组中的“清除”下拉按钮

，在打开的下拉列表框中

选择“全部清除”命令（可清除单元格格式、内容、批注、超链接等）。

⑷删除“成绩表（2）”工作表：右击“成绩表（2）”工作表的标签，在弹出的快捷菜单中选择“删除”命令，并在对话框中单击“删除”按钮。

12．把工作簿以E73.XLSX为文件名存入D:\\EX7中。 13．退出Excel。

三、思考与练习

1．在Excel环境下打开D:\\EX7\\E72.XLSX文件，并完成以下各操作： (1) 单击Sheet1工作表，在Sheet1工作表中完成以下各操作：

①设置工资表标题：将“职工工资表”文本显示在区域A1：F1的中部，并将其字体设置为：黑体、16号、红色字体。

步骤：选中A1:F1，选择“开始”功能区中“对齐方式”里的“合并后居中”。 ②在最后一位职工后插入一位新职工，数据为：职工号（1009）、姓名（张嘉应）、基本工资（2300）、补贴（780）、扣款（385.5）。

步骤：参考实验内容第4题插入新的一行，并录入数据。

③采用公式复制的方法，计算各职工的“实发工资”及工资表的“合计”项。 步骤：同实验内容题7。

④设置区域Sheet1!A2：F2、A12：B12区域中的数据：对齐方式：水平居中、垂直居中，字体设置为：华文彩云、14号、蓝色。区域Sheet1!C12：F12、F3：F11中的数据显示格式设置成2位小数的数值形式，红色字体显示。

步骤：同实验内容题8。

3

⑤给B3单元增加批注，内容为：他是班长。广东梅州人，男，1985年生。 步骤：右击单元格，选择“插入批注”命令。

⑥给区域Sheet1!A2：F12区域设置内外边框：外边框为粗线框，内部框为线框。 步骤：同实验内容题9。

⑦将工作表标签改名为“工资表”。 步骤：同实验内容题10。

编辑后的工资表如实验图7-1所示。

实验图7-1 职工工资表效果图

(2) 在工作表Sheet2中用公式求出各货物的金额与库存天数。使用条件格式，将库存天数用不同格式设置：大于1000天的用红色、斜体、带单下划线显示；库存天数大于或等于500且小于或等于1000天的用黄色、粗体显示；库存天数小于500天的用绿色显示。 步骤：将库存天数所在单元格选中，单击“开始”功能区“样式”功能组中的“条件格式”按钮，在下拉列表中执行“管理规则”命令，在弹出对话框中单击“新建规则”按钮，再在对话框中选择“只为包含以下内容的单元格设置格式”，然后在下方设置规则和格式即可。

(3)在工作表Sheet3中，用复制公式的方法快速完成下图所示“九九乘法表”的制作。要求给单元格区域A1：J10：增加外边框为红色双实线，内部框为蓝色单细实线；设置区域数据水平对齐方式、垂直对齐方式均为居中；选择合适的列宽与行高。如实验图7-2所示。

提示：先写出B2单元格中的公式，然后进行复制即可。但是为了在进行公式复制时不至于出错，要思考B2中的公式里哪些该用绝对地址，哪些该用相对地址。

4

实验图7-2 九九乘法表

(4)以原文件名保存结果后退出Excel。

2．比较“开始”功能区的“编辑”选项组中“清除”按钮“单元格”选项组中“删除”按钮的功能。

3．比较公式中的“相对地址”与“绝对地址”在公式复制和移动操作中的变化原则。

与在“开始”功能区的

5

实验8公式、序列及函数的使用

一、实验目的

1． 熟练掌握序列填充及公式复制的方法；

2． 掌握常用函数的功能及使用方法，并能用它们来解决一些实际问题。

二、实验内容与操作步骤

在Excel环境下完成以下各操作： （一）序列填充及公式的使用 1.在Excel中创建一个空白工作簿

2.利用Excel提供的数据填充功能，在Sheet1工作表中输入以下数据： （1）在区域A1：A9中从上到下填入：2，4，6，8，10，12，14，16，18

步骤：在A1、A2单元格中分别输入2、4，再选定两个单元格，然后右击这个单元格区域右下角的填充柄并拖动到A9单元格后松开鼠标，在弹出的快捷菜单中选择“等差序列”，然后输入步长值和终止值。

（2）区域B1：B9中从上到下填入：1，2，4，8，16，32，64，128，256 步骤：参照第（1）题，在最后一个步骤中选择“等比序列”

（3）区域C1：C12中从上到下填入：JAN，FEB，MAR，APR，MAY，JUN，JUL，AUG，SEP，OCT，NOV，DEC

步骤：先在C1单元格中输入“JAN”，再单击拖动其填充柄至C12单元格。

（4）在区域D1：D7中从上到下填入：星期日，星期一，星期二，星期三，星期四，星期五，星期六

步骤：参照第（3）题。

（5）先在Excel“自定义序列”列表框中增加新序列：数学系、物理系、化学系、中文系、外语系、生物系、政法系、地理系，然后在区域F1：F8中从上到下填充：数学系，物理系，化学系，中文系，外语系，生物系，政法系，地理系

步骤：?单击“文件”功能区中左边列表里的“选项”按钮。?在弹出的对话框中选择左边列表中的“高级”按钮。?将垂直滚动条拖到最下方，再单击“常规”区域里面的“编辑自定义列表”按钮。?在弹出来的对话框右边的“输入序列”框中输入序列（注意：每输入1项按一次回车键，输入完毕后按“添加”按钮），按“确定”关闭相应的对话框。?在

6

F1单元格中输入“数学系”，然后单击填充柄拖动填充至单元格F8即可。

3．在Sheet2工作表中，利用公式计算二次函数ax+bx+c的值，其中a=2，b=3，c=5，x从-3到4变化，每隔0.5取一个函数值。操作步骤写出如下：

提示：取一段连续的单元格区域（如A1:A15）输入x的所有值（-3、-2.5、-2??、3.5、4），再在该区域外一个单元格（如B1）输入相应的公式计算x为-3时二次函数的值，（其中x用-3所在单元格的地址代替，乘方符号为上档字符“^”，与数字符“6”在同一个键上），然后单击拖动该单元格的填充柄进行填充（如单击拖动B1的填充柄填充至B15）。

4．把工作簿以E81.XLSX为文件名存入D:\\EX8中。 （二）函数的使用

在Excel环境下打开D:\\EX8\\E82.XLSX文件,依次完成以下各操作后按E83.XLSX为文件名存入D:\\EX8中。

1．统计函数的使用

⑴单击“统计函数”工作表；

(2)区域F3:G8中用“统计函数”计算出各分店的统计值。

操作方法是：先在F3单元及G3单元输入的计算公式（可单击“开始”功能区内“自动求和”按钮右边的下拉按钮，在弹出的列表中选择所需的统计方式）,然后选定区域F3：G3后双击其填充柄。其中F3单元的公式为，G3单元的公式为。

⑶在区域B9:E12用“统计函数”计算出各季度的统计值。操作方法同上。 2．条件函数与频率分布函数的使用 ⑴ 单击“条件函数”工作表。

⑵ 算出各学生的平均分；操作方法同题1。

⑶ 给定各学生的成绩等级，规则如下：平均分≥90为“A”，80≤平均分＜90为“B，70≤平均分＜80为“C”，60≤平均分＜70为“D”，平均分＜60为“E”。以此规则在区域F3:F62用IF函数确定各学生的等级。

提示：在F3中输入公式计算第一个同学的等级，再使用公式填充功能。

⑷ 用FREQUENCY函数在区域I2：I5中统计出平均分0~59.9，60~79.9，80~99.9，100各分数段的学生人数。

3．文本函数的使用

⑴单击“文本函数”工作表；

⑵在区域A2:F32给出的数据清单中，编号的前3位为系别信息，101为数学系，102为物理系，103为化学系，据此在区域B3:B32用函数求出每位教师的系别。

⑶已知身份证号的第7至第10位数为出生年份，据此在区域F3:F32用函数求出每位教师的出生年份。

4．日期函数的使用

7

2

⑴单击“日期函数”工作表；

⑵在区域A2:F32给出的数据清单中，在区域E3:E32用日期函数求出每位职工的工龄。 ⑶在区域F3:F32用日期函数求出每位职工的工作天数（即自参加工作以来已经过的总天数。

5．财务函数的使用

使用 PMT 函数完成以下有关的操作： ⑴单击“财务函数”工作表；

⑵某企业向银行贷款5 万元，准备4年还清，假定当前年利率为 4 % ，在 B5 单元计算每个月应向银行偿还贷款的数额，根据条件在 B2 : B4 补充所需内容。

⑶假定当前年利率为 5 %，为使 5 年后得到 10 万元的存款，在 D5 单元计算现在开始每月应存多少钱？根据条件在 D2 : D4 补充所需内容。

6．排位函数的使用

⑴单击“排位函数”工作表；

⑵使用函数和公式在F列计算参赛者在各个洞口打出的杆数总和(即总杆数)，在G列计算总杆数与标准总杆数的差值；

⑶使用RANK函数在H列计算名次,名次排名原则为总杆数越少排名越前。其中单元格H2使用的公式为。

三、思考与练习

在Excel环境下打开D:\\EX8\\E83.XLSX文件，依次完成以下各操作后按原文件名保存。 1．在“频率分布函数”工作表中，用FREQUENCY函数统计出学生人数为不足100人，100~199人，200~299人，300~399人，400人及以上的系别个数，并将统计结果放在区域E2:F6中。

2．在“日期函数”工作表中，用日期函数在区域G3:G32求出每位职工的年龄（以上机时的实际日期来计算）。

3．在“综合函数”工作表中，按要求完成以下操作： ⑴在I1单元格中计算出年龄不超过40岁的人数。

⑵在I2单元格中求出年龄不超过40岁的人数占全体员工的百分比 ⑶在I3单元格中求出平均年龄并使用ROUND函数取整数。 ⑷在I4单元格中求出最大年龄。 4．以原文件名保存结果后退出Excel。

8

实验9图表的制作

一、实验目的

通过作图的实例练习，掌握Excel图表制作方法及其编辑技巧。

二、实验内容与操作步骤

在Excel环境下打开D:\\EX9\\E91.XLSX文件,依次完成以下各操作后以E92.XLSX为文件名存入D:\\EX9中。

1.制作内嵌图表

根据Sheet1!A2:D6区域中提供的数据，制作一个按城市分类比较每季度下雨天数的三维簇状柱形图。图表标题设为“三城市比较各季度下雨天数柱形图”，主要横坐标轴标题、主要纵坐标轴标题分别设为“城市”和“下雨天数”，图表嵌入到Sheet1工作表中。操作步骤如下：

⑴ 单击Sheet1工作表，选定区域A2:D6；

⑵ 在“插入”功能区中，单击“图表”选项组中的“柱形图”下拉按钮，在弹出的下拉列表中选择“三维柱形图”中的“三维簇状柱形图”按钮，在工作表中显示所创建的“三维簇状柱形图”；

⑶ 单击图表，系统显示“图表工具”菜单，选择“图表工具”菜单下的“设计”功能区，在其中单击“数据”选项组中的“切换行/列”按钮，将图表由“按季度分类”改为“按城市分类”。

⑷ 单击“布局”功能区中的“标签”选项组的“图表标题”按钮，在弹出下拉列表中选择“图表上方”选项，并在图表的“图表标题”框中录入标题文本：三城市比较各季度下雨天数柱形图。

⑸ 单击“布局”功能区中的“标签”选项组的“坐标轴标题”按钮，输入主要横坐标轴、主要纵坐标轴标题分别为“城市”和“下雨天数”

⑹ 适当调整图表的位置及大小； ⑺单击图表外的区域，图表制作完成。 2．编辑修改图表

9

工作表Sheet2中的图表所使用的数据取自Sheet2!A2:D6，现要求将其进行编辑修改：向图表增加数据系列，将Sheet2!E2:E6加入其中；将图表类型修改为表现各城市一年中气温变化的“带数据标记的折线图”；增加图表标题，标题文本为“比较四城市一年中气温变化折线图”；图表改成独立的图表工作表Chart1。编辑修改步骤如下：

⑴ 单击Sheet2工作表；

⑵ 向图表增加数据系列：选定图表，这时数据源区域A2:D6四周显示蓝色边框。将鼠标指针移到蓝色边框右下角的控点上，当指针变成双箭头形时按下鼠标左键拖动鼠标，使蓝色边框扫过区域E2:E6后松开鼠标。

⑶ 修改“图表类型”：单击“设计”功能区“类型”选项组中的“更改图表类型”按钮，在弹出“更改图表类型”对话框中选图表类型为“折线图”，图表子类型为“带数据标记的折线图”，单击“确定”按钮。

⑷ 单击“设计”功能区“数据”选项组中的“切换行/列”按钮，将图表修改为表现各城市一年中的气温变化。

⑸ 增加图表标题：单击“布局”功能区中的“标签”选项组的“图表标题”按钮，在弹出下拉列表中选择“图表上方”选项，并在图表的“图表标题”框中录入标题文本：比较四城市一年中气温变化折线图。单击“确定”按钮。

⑹ 修改图表存放位置：单击“设计”功能区中的“移动图表”按钮，在弹出“移动图表”对话框中单击“新工作表”单选按钮，单击“确定”按钮。

至此，编辑修改完毕。 3．制作独立图表

根据Sheet3!A1:C12区域中提供的数据制作独立图表，要求表现两个一元函数f(x)和g(x)图象的XY散点图（带平滑线的散点图）。操作步骤如下：

⑴ 单击Sheet3工作表，选定区域A1:C12；

⑵ 在“插入”功能区中，单击“图表”选项组中的对话框启动器，打开“插入图表”对话框，在弹出的对话框中选择“XY（散点图）”中的“带平滑线的散点图”；

⑶ 单击“设计”功能区中的“移动图表”按钮，在弹出“移动图表”对话框中单击“新工作表”单选按钮，单击“确定”按钮。

至此，图表制作完毕。

4．把工作簿以E92.XLSX为文件名存入D:\\EX9中。 5．关闭工作簿文件。

10

三、思考与练习

再次打开D:\\EX9\\E92.XLSX工作簿文件，依次完成以下各操作：

1．修改图表工作表Chart1中的图表标题，要求的图表标题为A1单元内容，并随A1单元内容的变化而变化。

步骤：单击选中图表标题，在功能区下方的编辑栏中输入“=Sheet2!$A$1”，再按回车键。

2．修改图表工作表Chart2中的独立图表，要求如下： (1)增加图表标题为“函数f(x)和g(x)图象的XY散点图”； 步骤：同实验内容题2（5）。

(2) 将图表子类型改为“带平滑线和数据标记的散点图”； 步骤：同实验内容题2（3）。

(3) 修改图表标题的格式：字体（隶书）、字号（18）、颜色（深红）； 步骤：右击图表标题，选择“字体”命令。

(4)修改f(x)曲线颜色为红色，g(x) 曲线颜色为绿色；

步骤：右击相应曲线，选择“设置数据系列格式”，再选择“线条颜色”|“实线”，然后选择所需颜色。

(5) 将图表绘图区的背景为白色大理石。

步骤：右击绘图区（将鼠标在图表各个区域作短暂停留，系统将提示该处是什么区域），选择“设置绘图区格式”命令，在弹出的对话框中选择“填充”｜“图片或纹理填充”，再选择所需的纹理。

3．单击Sheet4工作表，根据Sheet4!A1:E7区域中提供的数据完成以下操作： (1)制作青菜、胡萝卜、毛豆三种蔬菜热量、水分比较的三维圆柱图，要求有图例、有图表标题。适当修改图表大小，将图表放置在Sheet4!A9:F23区域中。如实验图9-1所示。

方法1步骤：①选中A2:C3、E2:E3、A7:C7、E7单元格区域（不连续单元格的选定可使用辅助键Ctrl）。②在“插入”功能区中选择插入三维圆柱图。③添加图表标题。可参考实验内容题1。④图表创建好之后可单击拖动至题目要求的区域。

方法2步骤：①选中A2:C7、E2:E7单元格区域（不连续单元格的选定可使用辅助键

11

Ctrl）。②在“插入”功能区中选择插入三维圆柱图。③单击“图表工具”|“设计”功能区中“数据”功能组里的“切换行/列”按钮。④在图表中分别右击三个不需要的数据系列，在弹出的快捷菜单中选择“删除”命令。⑤添加图表标题。可参考实验内容题1。

(2) 制作胡萝卜营养含量成分比较的分离型三维饼图，要求有图例、有图表标题、有数据标签，标签包括类别名称、百分比及显示引导线等。适当调整图表中各对象的位置，修改图表大小，将图表放置在Sheet4!G9:L23区域中。如实验图9-2所示。

步骤：①选定A2:A7、C2:C7单元格区域后，插入分离型三维饼图。②添加图表标题，然后单击拖动其到合适位置。③单击“图表工具”|“布局”功能区中的“数据标签”按钮，在下拉列表中选择“其它数据标签选项”。④在弹出对话框的左边列表中选择“标签选项”，再勾选右边的“类别名称”、“百分比”、“显示引导线”复选框后关闭对话框。在图表中单击拖动标签到合适位置。⑤图表创建好之后可单击拖动至题目要求的区域。

实验图9-1 三种蔬菜热量水分比较三维圆柱图

实验图9-2 胡萝卜营养成分比较分离型三维饼图

4．单击Sheet5工作表，依次完成以下操作：

⑴建立“带数据标记的折线图”以显示各个区在各个月份的二手楼成交均价，数据系列产生在行。可参考实验内容题1。

(2)图表标题为“二手楼价走势图”。可参考实验内容题1。

(3) 主要横坐标轴标题为“月份”，主要纵坐标轴标题为“均价”。可参考实验内容题1。 (4) 垂直（值）轴刻度的最小值为2000，最大值为6000，主要刻度单位为500； 步骤：单击“图表工具”|“布局”功能区中的“坐标轴”按钮，再在下拉列表中选择“主要纵坐标轴”|“其它主要纵坐标轴选项”命令，然后在弹出的对话框中进行设置。 (5)建立的图嵌入在原工作表中。

12

5．把工作簿以E93.XLSX为文件名存入D:\\EX9中。 6．关闭工作簿文件并退出Excel。

实验10 数据库操作

一、实验目的

1．掌握数据库的记录筛选、排序、分类汇总及数据透视表等操作。 2．掌握数据库函数的使用。

二、实验内容与操作步骤

在Excel环境下打开D:\\EX10\\E101.XLSX文件,依次完成以下各操作后以E102.XLSX为文件名存入D:\\EX10中。

1．自动筛选操作。

区域Sheet1!A1:C301所给的数据清单按班次顺序列出了班车到达和出发的情况，根据此数据清单在区域Sheet2!B2:C151中填入相应的数据。操作步骤如下：

(1) 单击Sheet1工作表。 (2)选定数据清单的任一单元。

(3) 在“数据”功能区的“排序和筛选”选项组中单击“筛选”按钮。

(4)单击“到/发”字段名右边的下拉箭头，在弹出的下拉列表中选择“到达”(并取消勾选“出发”前的复选框）。

(5)把Sheet1！A2:A300（只有150个单元）的数据复制到区域Sheet2！B2:B151。 (6) 单击“到/发”字段名右边的下拉箭头，在弹出的下拉列表中选择“出发”(并取消勾选“到达”前的复选框）。

(7)把Sheet1！A3:A301（只有150个单元）的数据复制到区域Sheet2！C2:C151。 (8)单击“数据”功能区的“排序和筛选”选项组的“筛选”按钮。。

2．高级筛选操作

从区域Sheet3!A1:D31所给的数据清单中，筛选出1979年底之前参加工作（条件A）或基本工资不小于300元（条件B）且不超过400元（条件C）的记录，要求将筛选结果放

13

入以单元格A34为左上角的区域中。

条件之间的关系： A或(B 与C)

操作步骤如下： (1) 单击Sheet3工作表；

(2)创建条件区域，在区域Sheet3!F1:H3输入如下的条件区域：

工作日期 <=1979-12-31 基本工资 >=300 实验图10-1条件区域①

基本工资 <=400 (3)选定数据清单的任一单元；

(4)单击“数据”功能区的“排序和筛选”选项组中“高级”按钮，弹出“高级筛选”对话框；

(5)在对话框中作以下的设置：

① 在“方式”栏中选定“将筛选结果复制到其他位置”单选框 ② 输入“列表区域”为：Sheet3!A1:D31 ③ 输入“条件区域”为：Sheet3!F1:H3

④ 在“复制到”文本框中输入“输出区域”的左上角的单元坐标A34。 （上述区域坐标或单元坐标用鼠标选定更方便） ⑹单击“确定”按钮。

3．数据库记录的排序

将区域Sheet4!A1:F47所给的数据清单中的记录排序，要求按发表论文数量降序排列，发表论文数相同时先男后女。操作步骤如下：

(1)单击Sheet4工作表； (2) 选定数据清单的任一单元；

(3)单击“数据”功能区的“排序和筛选”选项组中的“排序”按钮，打开“排序”对话框；

(4)在弹出的对话框中选择“主要关键字”为“篇数”字段、“排序依据”为“数值”，“次序”为“降序”；

(5) 单击“添加条件”按钮，选择“次要关键字”为“性别”字段，“排序依据”为“数

14

值”，“次序”为“升序”

(6)单击“确定”按钮。

4．分类汇总操作

对区域Sheet5!A2:F48所给的数据清单使用分类汇总操作，按职称分类求发表论文篇数的平均值及不同职称教师的平均年龄。操作步骤如下：

(1) 单击Sheet5工作表；

(2) 选定数据清单“职称”字段名所在的单元，即选定E2单元；

⑶单击“数据”功能区的“排序和筛选”选项组中的“升序”或“降序”按钮（升序或降序均可），使数据清单按“职称”字段“升序”或“降序”排序；

⑷单击“数据”功能区的“分级显示”选项组中的“分类汇总”命令，打开“分类汇总”对话框；

⑸在对话框作如下选择：选定“分类字段”的名字为“职称”、“汇总方式”为“平均值”，“选定汇总项”为“年龄”和“篇数”字段等；

⑹单击“确定”按钮。

5．数据库函数的使用

根据Sheet6!A2:F48区域中提供的数据清单，要求使用数据库函数计算：年龄大于30岁男讲师人数（放入I2单元），年龄大于30岁男讲师发表论文的平均数（放入I3单元），及男教授的平均年龄（放入I4单元）。操作步骤如下：

⑴计算年龄大于30岁男讲师人数 ①单击Sheet6工作表；

②在区域H7:J8输入如下的条件区域：

性别 男 年龄 >30 职称 讲师 ③在Sheet6！I2单元输入公式=DCOUNTA(A2:F48,1,H7:J8)。 ⑵计算年龄大于30岁男讲师发表论文的平均数

在Sheet6！I3单元输入公式=DAVERAGE(A2:F48,F2,H7:J8)。 ⑷ 计算男教授的平均年龄

① 在区域H10:I11中输入如下的条件区域：

15

性别 男 职称 教授 ②在Sheet6！I5单元输入公式=DAVERAGE(A2:F48,D2,H10:I11)。

三、思考与练习

1．比较自动筛选和高级筛选功能的异同。

2．在Excel环境下打开D:\\EX10\\E101.XLSX文件,依次完成以下各操作后以E103.XLSX为文件名存入D:\\EX10中：

⑴区域Sheet1!A1:C301所给的数据清单按班次顺序列出了班车到达和出发的情况，根据此数据清单在区域Sheet7!B2:C151中填入相应的数据。要求使用记录排序的方法完成。

步骤：先进行排序，以“到/发”字段为主要关键字，降序；以“班次”为次要关键字，升序（参考实验题3）。排序后选取所需记录进行复制即可。

⑵将区域Sheet4!A1:F47所给的数据清单中的记录排序，按教师的职称由高至低进行排列，即按“教授?副教授?讲师?助教”的顺序，职称相同时先男后女。

步骤：①先创建自定义序列（参考实验8实验内容的（一）.2.(5)题），然后选定清单并打开排序对话框。②指定“职称”字段为主要关键字，并在其“次序”下拉列表中选择“自定义序列”，再在弹出的对话框中选择步骤①中创建的自定义序列，按“确定”按钮。③返回“排序”对话框后，再次打开主要关键字的“次序”下拉列表，并根据题目要求选择所需的次序。④添加次要关键字为“性别”，升序。

⑶对区域Sheet5!A2:F48所给的数据清单使用分类汇总操作，按职称分类求发表论文篇数的总和及不同职称教师的最大年龄。

步骤：参考实验题4。由于本题需要实现两种汇总方式（求和、求最大值），因此需要执行两次分类汇总操作，注意在第二次操作时应取消对话框中“替换当前分类汇总”复选框的勾选。

⑷根据区域Sheet6!A2:F48中提供的数据清单，作一个数据透视表：按系别统计各类不同职称中男、女教师的论文总数，要求包含行、列总计项，设置该透视表名称为“教师论文总数统计透视表”，并使其显示在新建工作表Sheet7中。

步骤：①选定清单，单击“插入”功能区的“数据透视表”按钮，在弹出的对话框中检

16

查区域是否正确，并选择放置数据透视表的位置为“新工作表”，然后按“确定”，系统将自动切换至新工作表中。②在“数据透视表字段列表”浮动窗口中，将“系别”字段单击拖动至下方的“报表筛选”区域；将“职称”字段单击拖动至“行标签”区域；将“性别”字段单击拖动至“列标签”区域；将“篇数”字段单击拖动至“数值”区域。③在“数据透视表工具”|“选项”功能区最左端修改数据透视表的名称，修改完毕按回车键即可。

(5) 将文件以E103.XLSX为文件名存入D:\\EX10中。

17

本文来源：https://www.bwwdw.com/article/86ho.html