实验四EXCEL数据处理
动填充柄得C列全部数据。选择B3:C9区域，单击“开始”选项卡｜“样式”组｜“条件格式”按钮｜“突出显示单元格规则”｜“小于”选项，在弹出的对话框中输入“0”，设置为“红色文本”，单击“确定”按钮。
表4-4-1函数计算总表 X -3 -2 -1 0 1 2 3 Y=X3 Z=15+ X3
(2) 选择A2:C9区域，单击“插入”选项卡｜“图表”组｜“折线图”按钮｜“折线图”。单击“图表工具-布局”选项卡｜“标签”组｜“图表标题”按钮，选“图表上方”选项，将“图表标题”修改为“函数计算表”。右击图表标题，从快捷菜单中选“字体”，将图表标题字设为“黑体、20号、红色”格式。双击“图例”，将图例位置设为“底部”。
(3) 保存工作薄。 2. 高级筛选及分类汇总
表4-4-2 编号 000001 000002 000003 000004 000005 000006 000007 000008 要求：
（1）在工作表Sheet1中给出表4-4-2所示的数据。将Sheet1复制命名为GZB，在GZB表中插入标题行，并将标题行输入内容“职工情况一览表”，标题格式为“合并居中、隶书、19号字、加双下划线”；列标题行加底纹。边框线为红色、双实线。
（2）将Sheet1复制命名为YSB。在YSB中，隐藏姓名为“张三”所在行。删除姓名为“江滨”所在行。交换性别和年龄两列位置。
（3）将Sheet1复制命名为PX表，按年龄从高到低的顺序排列。
（4）将Sheet1复制命名为SX表，将男性“职员”和“主管”的记录筛选出来，将筛选
职工情况一览表 性别 男 女 男 男 女 女 男 女 年龄 职务 24 经理 23 职员 20 职员 25 主管 35 经理 30 主管 19 职员 19 职员 姓名 李四 张三 程鑫 刘红兵 钟舒 江滨 王建钢 璐娜 26
《大学计算机基础》实验指导
结果复制到原来表的下面。
（5）将Sheet1复制命名为HZ表，按职务分类统计各职务的年龄最大值。 操作步骤：
(1) 打开工作薄“案例4-2.xlsx”，右键单击工作表Sheet1标签，选择“移动或复制”，在弹出的对话框中选中“建立副本”复选框，单击“确定”按钮，将复制的工作表重命名为GZB。右击行号“1”，从快捷菜单中选“插入”，单击单元格A1，输入内容“职工情况一览表”。选择A1:E1区域，单击“开始”选项卡｜“对齐方式”组｜“合并及居中”按钮；在“开始”功能区的“字体”组，设置“隶书、19号字、双下划线”。选择A2:E2区域，单击“开始”功能区的“字体”组的“填充颜色”列表框，选择标准色“蓝色”。选择A2:E10区域，右击，从快捷菜单中选“设置单元格格式”，单击“边框”选项卡，线条样式选“双线”，颜色选红色，边框选择“外边框”和“内部”，单击“确定”按钮。
(2) 复制Sheet1工作表，重命名为YSB。右击姓名为“张三”所在行号，从快捷菜单中选“隐藏”。右击姓名为“江滨”所在行号，从快捷菜单中选“删除”。选中D列，在其列号上右击，选择“剪切”；然后选择C列，在C列号上右击，选择“插入剪切单元格”，完成两列的位置交换。
(3) 复制Sheet1工作表，重命名为PX。选择“年龄”所在单元格D1，单击“数据”选项卡｜“排序和筛选”组｜“降序”按钮。
(4)复制Sheet1工作表，重命名为SX。选择单元格C11，输入内容“性别”，单元格C12输入“男”，单元格C13输入“男”，单元格D11输入“职务”，单元格D12输入“职员”，单元格E11输入“职务”，单元格E13输入“主管”，选择A1:E9区域，单击“数据”选项卡｜“排序和筛选”组｜“高级”按扭，在弹出的“高级筛选”对话框中，列表区域为“$A$1:$E$9”，条件区域选择“$C$11:$E$13”，方式选“将筛选结果复制到其他位置”，复制到区域为“$A$15”，单击“确定”按钮。
(5)复制Sheet1工作表，命名为HZ。选择“职务”所在单元格E1，单击“数据”选项卡｜“排序和筛选”组｜“升序”按钮。单击“数据”选项卡｜“分级显示”组｜“分类汇总”按钮，在弹出的“分类汇总”对话框中，分类字段选“职务”，汇总方式为“最大值”，选定汇总项为“年龄”，单击“确定”按钮。
(6)保存工作薄。 二级模拟题（一）
打开文件\学生成绩单.xlsx\（课程中心网站下载），要求：
1. 对工作表\第一学期期末成绩\中的数据列表进行格式化操作：将第一列\学号\列设为文本，将所有成绩列设为保留两位小数的数值；适当加大行高列宽，改变字体、字号，设置对齐方式，增加适当的边框和底纹以使工作表更加美观。
2. 利用\条件格式\功能进行下列设置：将语文、数学、英语三科中不低于110分的成绩所在的单元格以一种颜色填充，其他四科中高于95分的成绩以另一种字体颜色标出，所用颜色深浅以不遮挡数据为宜。
27
实验四EXCEL数据处理
3. 利用sum和average函数计算每一个学生的总分及平均成绩。
4. 学号第 3、4位代表学生所在的班级，例如：\代表12级1班5号。请通过函数提取每个学生所在的班级并按下列对应关系填写在\班级\列中：
\学号\的3、4位 01 02 03
对应班级 1班 2班 3班
5. 复制工作表\第一学期期末成绩\，将副本放置到原表之后；改变该副本表标签的颜色，并重新命名，新表名需包含\分类汇总\字样。
6. 通过分类汇总功能求出每个班各科的平均成绩，并将每组结果分页显示。
7. 以分类汇总结果为基础，创建一个簇状柱形图，对每个班各科平均成绩进行比较，并将该图表放置在一个名为\柱状分析图\新工作表中。 操作步骤：
1．选择A、B、C列，单击\开始\｜\数字\，选择\文本\。居中。
2．选择所有成绩列，单击\开始\｜\数字\，选择\数值\，小数位数2。居中。 3．选择整个表格，行高列宽分别为20和10，黑体，12号，套用“表样式浅色11”。 4．选择语文、数学、英语三列成绩，单击\条件格式\｜\突出显示单元格规则\｜\其他规则\选项，选择规则类型为\只为包含以下内容的单元格设置格式\；\单元格值\，\大于或等于\，\；点\格式\按钮，填充黄色。选择其他四科成绩，单击\条件格式\｜\突出显示单元格规则\｜“大于”，在弹出的对话框中输入95，红色文本。
5．选择K2单元格，输入公式:=SUM(D2:J2)；选择L2单元格，输入公式: =AVERAGE(D2:J2)，拖动填充柄完成所有学生的总分和平均分计算。
6．选择C2单元格，输入公式: =MID(A2,4,1)&\班\，利用填充柄完成所有学生的班级填充。
7．按CTRL键拖动工作表标签复制工作表，并重命名为\第一学期期末成绩分类汇总\，右击选择工作表标签颜色。
8．单击班级列中任一单元格，在\数据\选项卡中，单击“升序”按钮。
9．单击数据清单中任一单元格，单击\分级显示\组的\分类汇总\命令；选择“班级”、“平均值”，复选框选择所有科目。选中\汇总结果显示在数据下方\、\每组数据分页\两个复选框。最后单击\确定\按钮。
10．选定要操作的数据范围，单击\插入\｜\图表\｜\柱形图\｜簇状柱形图；选择图表，右击，移动图表，新工作表。
11．双击新工作表标签，重命名为“柱状分析图”。 二级模拟题（二）
小李今年毕业后，在一家计算机图书销售公司担任市场部助理，主要的工作职责是为部门经理提供销售信息的分析和汇总。请你根据销售数据报表（\文件），按照如下
28
《大学计算机基础》实验指导
要求完成统计和分析工作：
1. 请对\订单明细\工作表进行格式调整，通过套用表格格式方法将所有的销售记录调整为一致的外观格式，并将\单价\列和\小计\列所包含的单元格调整为\会计专用\（人民币）数字格式。
2．根据图书编号，请在\订单明细\工作表的\图书名称\列中，使用VLOOKUP函数完成图书名称的自动填充。\图书名称\和\图书编号\的对应关系在\编号对照\工作表中。 3．根据图书编号，请在\订单明细\工作表的\单价\列中，使用VLOOKUP函数完成图书单价的自动填充。\单价\和\图书编号\的对应关系在\编号对照\工作表中。 4．在\订单明细\工作表的\小计\列中，计算每笔订单的销售额。
5．根据\订单明细\工作表中的销售数据，统计所有订单的总销售金额，并将其填写在\统计报告\工作表的B3单元格中。
6．根据\订单明细\工作表中的销售数据，统计《MS Office高级应用》图书在2012年的总销售额，并将其填写在\统计报告\工作表的B4单元格中。 并将其填写在\统计报告\工作表的B5单元格中。
8．根据\订单明细\工作表中的销售数据，统计隆华书店在2011年的每月平均销售额（保留2位小数），并将其填写在\统计报告\工作表的B6单元格中。 9．保存\文件。 操作步骤：
1．选择要输入的单元格；
2．如果简单的计算公式，可以通过手动来书写，如果公式比较复杂，可以通过单击\公式\选项卡的\插入函数\命令来选择相应的函数；
3．然后再选择相应的计算区域。 \图书名称\列公式（以E3单元格为例）：
=VLOOKUP([@图书编号],表2[[图书编号]:[图书名称]],2)
\单价\列公式（以F3单元格为例）：=VLOOKUP([@图书编号],表2,3) \小计\列公式（以H3单元格为例）：=F3*G3 \所有订单的总销售金额\列公式：=SUM(表_5[小计])
\《MS Office高级应用》图书在2012年的总销售额\列公式：=SUMIFS(订单明细表!H3:H636,订单明细表!B3:B636,\订单明细
表!B3:B636,\订单明细表!E3:E636,\《MS Office高级应用》\隆华书店在2011年
第3季度（7月1日~9月30日）的总销售额\列公式：=SUMIFS(表_5[小计],表_5[日期],\表_5[日期],\表_5[书店名称],\隆华书店\\隆华书店在2011年的每月平均销售额（保留2位小数）\列公式：=SUMIFS(订单明细表!H3:H636,订单明细表!B3:B636,\订单明细表!B3:B636,\订单明细表!C3:C636,\隆华书店\
7．根据\订单明细\工作表中的销售数据，统计隆华书店在2011年第3季度的总销售额，
29
相关推荐：
loading