4.5.1 任务提出
建立一张如图 463 所示的工作表,并完成下列操作:
(1)计算出每一种商品的价值(价值=单价×数量),填入相应的单元格中。
(2)将“单价”栏中的数据设置为货币样式,并将列宽设定为 20。
(3)按商品名称的拼音字母顺序进行升序排列,并按商品名称对数量进行分类汇总求和
(替换当前分类汇总,汇总结果显示在数据下方)。
图 463 部分商品统计表
4.5.2 任务目标
l 掌握分类汇总。
l 掌握数据透视表的使用。
4.5.3 任务准备
按某个字段进行分类,并对一个或多个数值型字段求和或求平均值等,也可以为非数值 型字段计数,这些操作称为分类汇总。如对某采购商品进行分类汇总:
(1)先对分类字段(例如,商品名称)进行排序(类似合并同类项) 。
(2)单击“数据”选项卡,选择“分类汇总”命令,在如图 464 所示的“分类汇总”对 话框中选择“分类字段”为“商品名称” ,选择“汇总方式”为“求和” ,选择“选定汇总项”
可以是数量和价值等数值型字段。
图 464 “分类汇总”对话框
4.5.4 任务实施
(1)计算出每一种商品的价值操作步骤如下:
1)在 D3 单元格中输入“=B3×C3” ,按“Enter”键。
2)用“填充柄”复制公式求其他单元格的结果。
(2)设置货币样式及列宽操作步骤如下:
1)将 B3:B12 区域选中,单击“开始”选项卡,选择“格式”|“设置单元格格式”命令,
弹出如图 465 所示的“设置单元格格式”对话框,在“数字”选项卡的“分类”区域中选择
“货币” ,在“货币符号”中选择“¥” ,单击“确定”按钮。
图 465 “设置单元格格式”对话框
2)单击“格式”按钮,选择“列宽”命令,在弹出的“列宽”对话框中输入“20” ,单 击“确定”按钮。
(3)排序及分类汇总操作步骤如下:
1)在进行分类汇总前,应先对数据清单进行排序,在数据清单中单击任一单元格,然后 单击“数据”选项卡,选择“排序”命令,弹出如图 466 所示的对话框,在“主要关键字”
的下拉列表框中选择“商品名称” ,单击“确定”按钮。
2)单击“数据”选项卡,选择“分类汇总”命令,弹出如图 467 所示的对话框,在“分 类字段”的下拉列表框中选择“商品名称” ,在“汇总方式”中选择“求和” ,在“选定汇总项”
中选择“数量” ,最后单击“确定”按钮。
图 466 “排序”对话框 图 467 “分类汇总”对话框
4.5.5 任务拓展 1.数据透视表
分类汇总只适合对一个字段分类,对一个或多个数值型字段进行汇总。如果要对多个字 段分类并汇总,就需要利用数据透视表这个工具来解决问题。例如,如图 468 所示,要统计 各科室各职称的人数,既要按“科室”分类,又要按“职务”分类,此时要用到数据透视表。
图 468 数据清单
首先在数据清单中单击任一单元格,然后再单击“插入”选项卡,选择位于“表格”组 的“数据透视表”命令,弹出如图 469 所示的“创建数据透视表”对话框,按默认选择单击
“确定”按钮,在如图 470 所示的工作表中,将位于“选择要添加到报表的字段”处的“科 室”字段拖至工作表左上角的“行字段”处,将“职称”字段拖至“列字段”处,再将“职称”
字段拖至“值字段”处,则将完成后的数据透视表作为新建工作表插入到当前工作簿中。
图 469 “创建数据透视表”对话框
图 470 创建数据透视表 2.合并计算
对于两个具有相同表结构的数据清单,可以使用“合并计算”功能做统计运算。例如,
针对如图 471 和图 472 所示的“十一月工资”表和“十二月工资”表,可以合并计算出这两 个月工资表中某个字段的总额,或平均值、最大值等,如图 473 所示。
图 471 十一月工资 图 472 十二月工资
图 473 十一月、十二月工资统计 使用“合并计算”功能操作步骤如下:
(1)打开“十一月工资”“十二月工资”两个工作表(或工作簿文件) 。
(2)建立好如图 473 所示的“十一月、十二月工资统计”工作表(或工作簿文件),单 击 B3 单元格,然后单击“数据”选项卡,选择“合并计算”命令,打开如图 474 所示的“合 并计算”对话框,选择“函数”为“求和” 。
图 474 “合并计算”对话框
(3)在“引用位置”文本框处,单击其右侧的折叠按钮,然后在“十一月工资”表中选 择 B3:B6 区域的数据,再单击该折叠按钮以展开对话框,在“合并计算”对话框中单击“添 加”按钮,将刚才选定的区域添加到“所有引用位置”的列表框中,如图 475 所示。使用同 样的操作将“十二月工资”表中的 B3:B6 区域添加到该列表框中,最后单击“确定”按钮,
得到如图 476 所示的 B3:B6 区域实发工资总额值。
图 475 “合并计算”对话框 图 476 统计结果
同理,欲求得“十一月、十二月工资统计”工作表中的最高奖金,先在该表中单击 C3 单元格,其余操作参照上述的第(2)步。注意:在“合并计算”对话框中的“所有引用位 置”列表框中要删除刚才添加的所有数据,然后选择“函数”为“最大值” ,再参照上述的 第(3)步,分别将十一月、十二月工资表中的 C3:C6 区域数据添加到对话框中,如图 476 所示为最后的统计结果。
习题 4
41 选择题
1.在 Excel 2010 单元格中输入字符型数据,当宽度大于单元格宽度时,哪种描述是错误的( )。 A.无须增加单元格宽度
B.当右侧单元格已经有数据时也不受限制,允许超宽输入
C.右侧单元格中的数据将被覆盖,右侧单元格被覆盖的部分会丢失
D.右侧单元格中的数据将被覆盖,右侧单元格被覆盖的部分不会丢失
A.199934 B.34%
C.$100 D.南京溧水
A.Ctrl+Enter B.Alt+Enter
C.Shift+Enter D.Tab
11.在 Excel 2010 中,某工作表 D2 单元格中含有公式“=A2+B2C2” ,则将该公式复制到该表的 D3 单 元格时,D3 单元格中的结果应是( )。
A.=A2+B2C2 B.=A3+B3C3
C.=B2+C2D2 D.无法复制
12.Excel 2010 中,添加边框、颜色操作,在格式下拉按钮中选择( )。
A.设置单元格格式 B.行
C.列 D.工作表
13.在 Excel 2010 中,不连续选择单元格,只要按住( )的同时选择所要选的单元格。
A.Ctrl B.Shift
C.Alt D.ESC
14.下列关于 Excel 2010 打印与预览操作的说法中正确的是( )。
图 477 工作表 1 2.建立如图 478 所示的工作表,完成下列操作:
(1)将工作表 Sheet1 的 A1:C1 单元格合并为一个单元格,水平对齐方式设置为居中,计算“人数”
及“所占百分比”列(注:一定要按照先填写 C3:C6 之中某一单元格公式后,其他单元格复制(可用填充 柄)的方法完成此题,所占百分比=人数/总计), “所占百分比”列单元格格式为“百分比”型(保留 2 位 小数)。
(2)选取“Sheet1”的“职称” (不包括“总计”行)和“所占百分比”两列单元格的内容建立“分离 型圆环图” (系列产生在“列” ),在数据标签内显示数据标志,标题为“师资情况图” ,插入到表的 A9:D19 单元格区域内。
3.建立学生成绩单工作表,如图 479 所示,将“学生编号” (如 001、002 等)、 “平均成绩”“平均成绩 等级”列内容补充完整,要求: “平均成绩”“平均成绩等级”列内容必须采用函数进行计算得出。
图 478 工作表 2 图 479 工作表 3 4.按照如图 480 所示内容建立一张工作表,并完成下列操作:
(1)将标题改为红色楷体,16 号字,标题行行高设为 30。
(2)将歌手编号用 001、002、……、010 来表示,并居中(提示:把格式设为文本)。
(3)将所有评委给出的大于或等于 6 分的得分信息用红色表示(使用条件格式设置)。
(4)给整个表格加上蓝色细实线作为表格线,标题除外。
≥
图 480 得分统计表 5.按照如图 481 所示内容建立一张工作表,并完成下列操作:
(1)计算“人数”列的“合计”项。
(2)计算“所占比例”列(所占比例=人数/合计),结果保留 2 位小数。
图 481 某大学在校生人数情况表 6.按照如图 482 所示内容建立一张工作表,并完成下列操作:
(1)用函数 RANK.AVG 根据“平均”字段完成“名次”字段的填写(注:一定要按照先填写 I2:I5 之中某 一单元格公式后,其他单元格用复制(可用填充柄)的方法完成此题,最高分填 1(第 1 名),依次类推。
(2)将 A1:I5 单元格内容居中,并将“英语”“数学”“化学”“总分”“平均”5 列单元格中小于 60 分 的单元格的字体用红色表示(注:随着单元格中数据值的变化,该单元格字体发生变化)。
图 482 工作表 7.按照如图 483 所示内容建立一张工作表,并完成下列操作:
(1)计算所有学生的总分。
(2)用函数 COUNTIF 统计各学科≥90 分的人数。
(3)将统计结果按科目用簇状柱形图表示出来存放到 Sheet1 中。
图 483 各科成绩工作表
8.按照如图 484 所示内容建立一张工作表,并完成下列操作:
(1)用公式求出实发工资(实发工资=基本工资+奖金+补贴房租)。
(2)将奖金和补贴两列数据互换位置。
(3)在职工工资表中以“姓名”和“实发工资” 为数据区建立一个簇状柱形图图表,刻度最大值为 1500,
主要刻度单位为 150,图表位置放在 A11:J25 区域内。
图 484 职工工资表 9.按照如图 485 所示内容建立一张工作表,并完成下列操作:
(1)利用公式填写评语,条件是:月考 1 与月考 4 成绩比较,如月考 1 大于月考 4,则评语为“成绩退 步,严重警告中” ,如果月考 4 大于月考 1 成绩 40 分以上,则评语为“进步神速,希望更加努力” ,除此以外 的情况评语都为“进步平平,望加倍努力” 。
(2)根据表格的特点,给表格制作一张能够反映某同学 4 次月考成绩升降情况的独立图表(要求:图 表类型为簇状柱形图,图表标题为“月考成绩情况表” ,图表名称为“图表 1” )。
图 485 成绩表