3-办公自动化（实验指导） - 图文

③选中A列至J列，在“开始”选项卡中的“单元格”组单击“格式”下拉列表中的“自动调整列宽”命令；

(9) 给数据表添加粗外框、双线内框；将区域A2：J2、A3：C20分别设置粉红及绿色的填充色；给考试成绩低于60分的背景设为“浅红填充深红文本”。

①选中A1：J21，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，在“边框”选项卡中，先选中粗线，单击“外框”，再选中“双线”，单击“内部”；

②分别选中A2：J2、A3：C20，选择“设置单元格格式”命令，在“填充”选项卡中进行设置区域的填充色；

③选中D3：F20，在“开始”选项卡“样式”组，单击“条件格式”下拉列表中“突出显示单元格规则”中的“小于”项，在弹出的对话框中根据题目要求设置；

图3-2

范例2：

打开素材文件中的“fl2素材.xlsx”文件，按下列要求操作，以“范例2+班级名+学号.xlsx”文件名保存。

(1) 将Sheet1表中的数据按总分降序排序，若总分相同再按计算机成绩降序排序。 ①选中A1：H19，在“开始”选项卡的“编辑”组单击“排序和筛选”下拉列表中的“自定义排序”命令，弹出“排序”对话框；

②在“排序”对话框中，从“主要关键字”下拉列表中选择“总分”，从“排序依据”下拉列表中选择“数值”，从“次序”下拉列表中选择“降序”；

③单击“添加条件”按钮，添加“次要关键字”，依次在三个下拉列表框中选择“计算

~ 17 ~

机”、“数值”和“降序”；

(2) 在Sheet1工作表的B22：H38区域建立如图3-3所示的图表，添加图表标题，形状样式选择“强烈效果-橄榄色，强调颜色3”；将图例放置到图表底部；图表填充色为预设“雨后初晴”，阴影为外部左下斜偏移，圆角。

①选中C1，E1：G1，C4，E4：G4，C6，E6：G6，C13，E13：G13，C18，E18：G18区域，选择“插入”选项卡中的“图表”组，单击“柱形图”中的“簇状圆柱图”按钮，选中刚建立好的图表，拖曳到B22：H38区域；

②选中图表，在“图表工具/布局”动态选项卡中的“标签”组，单击“图表标题”中的“图表上方”命令，添加图表标题，单击标题，选择“图表工具/格式”动态选项卡中的“形状样式”组，选择“强烈效果-橄榄色，强调颜色3”的形状样式；

③右击“图例”，在弹出的快捷菜单中选择“设置图例格式”命令，在弹出的对话框中，选择图例位置为“底部”； ④右击“图表”，在弹出的快捷菜单中选择“设置图表区域格式”命令，在弹出的对话框中设置图表填充色，阴影和边框样式；

图3-3

(3) 将Sheet2表中的数据筛选出材料系女生，并把结果复制到A24单元格；恢复表格中数据，重新筛选出总分超过平均值的学生，并把结果复制到A31单元格；恢复表格中数据，重新筛选出女生并且计算机分数大于等于90分的或者总分大于等于260分的学生记录。

①单击Sheet2数据表的任一单元格，在“开始”选项卡中的“编辑”组单击“排序和筛选”下拉列表中的筛选命令，在“系别”下拉列表中选择“材料系”，在“性别”下拉列表中选择“女”，将结果复制到A24单元格； ②接着上题内容，单击“系别”和“性别”下拉列表中的“全部”，取消前面的筛选结果，然后在“总分”下拉列表中选择“数字筛选”中的“高于平均值”命令，将结果复制到A31单元格；

③接着上题内容，单击“总分”下拉列表中的“全部”，取消前面的筛选结果，将A1：H1复制到A44单元格，在D45中输入“女”，在G45中输入“>=90”(同时满足两个条件的要写在一行上)，在H46中输入“>=260”(两个条件中只要满足一个条件的要写在不同行上)，选择“数据”选项卡“排序和筛选”组中的“高级”按钮，在弹出的“高级筛选”

~ 18 ~

对话框中，参照图3-4设置；

图3-4

(4) 在Sheet3工作表中进行分类汇总，统计各系的人数和各门课程的平均值，并将工作表改名为“分类汇总”。

①单击Sheet3数据表的任一单元格，选择“数据”选项卡中的“排序/筛选”组，单击“排序”按钮，在弹出的对话框中，对“系别”进行排序；

②在“数据”选项卡的“分级显示”组，单击“分类汇总”按钮，在弹出的对话框中进行设置，“分类字段”为“系别”，“汇总方式”为“计数”，“选定汇总项”为“姓名”一个字段，其他为默认，单击“确定”退出；

③在“数据”选项卡的“分级显示”组，单击“分类汇总”按钮，在弹出的对话框中进行设置，“分类字段”为“系别”，“汇总方式”为“平均值”，“选定汇总项”为“高数”、“英语”、“计算机”三个字段，取消对“替换当前分类汇总”多选框的勾选，其他为默认，单击“确定”退出，结果如图3-5所示；

④右击Sheet3工作表标签，重命名为“分类汇总”； (5) 将Sheet4工作表改名“数据透视表”，制作数据透视表，如图3-6所示。

①右击Sheet4工作表标签，重命名为“数据透视表”；

②单击数据表中任一单元格，选择“插入”选项卡中的“表格”组，单击“数据透视表”下拉列表中的“数据透视表”，在弹出的对话框中，将“选择区域”设为当前工作表的A1：H19，“选择放置数据透视表的位置”设为“现有工作表”中的A22，确定退出；

③在弹出的“数据透视表字段列表”任务窗格中，将“系别”字段拖曳到行标签，将“性别”字段拖曳到列标签，将“高数”、“英语”、“计算机”拖曳到数值区，再单击数值下拉列表中的“值字段设置”，计算类型分别选为“平均值”、“最大值”、“最小值”；

④选中数据区，右击，在快捷菜单中选择“数字格式”，设置小数位为0；单击数据透视表中的“行标签”单元格，改名为“系别”，单击“列标签”单元格，改名为“性别”。 ⑤将光标停在数据透视表内，选择“数据透视表工具/设计”选项卡中的“布局”组，单击“总计”下拉列表中的“仅对列启用”；

⑥将光标停在数据透视表内，选择“数据透视表工具/设计”选项卡中的“数据透视表样式”组，单击样式下拉列表深色区中的“数据透视表样式深色3”；

~ 19 ~

图3-5

图3-6

3.3 实验内容

实验1：

打开sy1素材.xlsx文件，按下列要求操作，结果以“实验1+班级名+学号.xlsx”文件名保存。

(1) 对标题格式化：隶书、24磅、红色粗体，在A1：F1区域跨列居中，并加上如样张所示的绿色边框。

(2) 计算“销售金额”（数量*单价*系数，计算公式中的系数必须用绝对引用）。

(3) 将表格中所有金额数据保留小数1位，并按样张设置会计专用形式；把C3和D3单元格中的内容分成二行。

(4) 根据月份对数据表进行“升序”排序，月份相同的记录根据销售数量进行“降序”

~ 20 ~