第5章数据存储与预处理
【实验目的】
(1) 掌握Excel 2016的基本功能。
(2) 掌握工作簿和工作表的基本操作。
(3) 掌握工作表数据的输入、编辑和修改操作。
(4) 掌握外部数据的导入操作。
(5) 掌握工作表的格式化操作。
(6) 掌握单元格的引用、公式和函数的使用方法。
(7) 掌握各类图表的创建、编辑和修饰操作。
(8) 掌握数据的排序、筛选与分类汇总操作。
(9) 掌握数据透视表的使用方法。
(10) 通过上述实验达到初步利用Excel 2016进行数据存储与预处理的目的。
【实验环境】
中文Windows 10及更高版本，Excel 2016。
【实验内容】
(1) 认识Excel 2016。
(2) 创建和编辑Excel工作簿。
(3) 工作表数据的输入、编辑和修改。
(4) 在工作表中导入外部数据。
(5) 工作表的基本操作。
(6) 对工作表进行格式化处理。
(7) 使用各类公式和函数进行计算。
(8) 使用图表比较和描述工作表中的数据。
(9) 使用排序、筛选、分类汇总和数据透视表对工作表进行分析。
 实验5．1班级考试成绩统计
【实验要求】
打开素材文件“班级考试成绩．xlsx”，完成如下操作： 
(1) 打开工作表Sheet1，将标题单元格区域A1:G1中的单元格设置为“合并后居中”，姓名单元格区域A4:A10的对齐方式设置为“分散对齐(缩进)”，将单元格区域A1:G14添加内边框“细实线”、外边框“粗匣框线”，设置单元格区域C4:G13中的字体颜色为标准色“深蓝”，字形为“倾斜”，对齐方式为“居中”格式。






(2) 利用函数计算“总平均分”“平均分”和“最高分”(结果均保留到整数)，分别填入单元格区域G4:G10、C12:G12和C13:G13中。
(3) 在J4开始的位置建立垂直查询数据表，然后用垂直查询函数对四个学期和总平均分共5项的“平均分”进行评价，要求分为良好(≥85分)、中等(≥70分)、合格(≥60分)、不合格(＜60分)4个等级。
(4) 根据后4位学生在四个学期的学习情况绘制一个带数据标记的堆积折线图，图例为学期，设置图表布局为“布局12”。在第三学期的成绩数据点右侧显示数据标签。形状样式设置为“彩色轮廓橙色,强调颜色2”。
(5) 打开工作表Sheet2，在单元格A1开始位置建立数据透视表，按性别分别统计“第一学期”和“第三学期”成绩的平均值(性别为行、结果保留两位小数)。应用“白色，数据透视表样式浅色24”样式。
(6) 将工作表Sheet2重命名为“数学成绩”。
【实验素材】
班级考试成绩.xlsx
班级考试成绩素材内容如图51所示。


图51班级考试成绩素材内容


【实验步骤】
(1) 将工作表Sheet1中的标题区域A1:G1选中，单击“开始”选项卡“对齐方式”组的“合并后居中”按钮，完成标题区域的单元格合并，标题区域操作如图52所示。


图52标题区域操作


选中“姓名”列对应的单元格区域A4:A10，单击“开始”选项卡“对齐方式”组右下角按钮
，打开“设置单元格格式”对话框的“对齐”选项卡，在“水平对齐”列表框中选择“分散对齐(缩进)”选项，单击“确定”按钮。“姓名”列单元格对齐方式设置如图53所示。


图53“姓名”列单元格对齐方式设置


选中单元格区域A1:G14为其设置边框，单击“开始”选项卡“字体”组的“下框线”按钮
右侧的下拉箭头，在弹出的下拉菜单中选择“其他边框”命令，打开“设置单元格格式”对话框的“边框”选项卡。在选项卡中对选中范围的外边框进行设置，首先在“直线”下的“样式”列表框中选中“粗匣框线”
(右列由上到下第6个)，单击“外边框”按钮
确定设置的内容。接下来设置内边框的“直线样式”为“细实线”
(左列由下到上第1个)，单击“内部”按钮
确定设置的内容，最后单击“确定”按钮。设置单元格区域的内、外边框样式及结果如图54所示。


图54设置单元格区域的内、外边框样式及结果


选中单元格区域C4:G13，通过“开始”选项卡“字体”组的颜色设置按钮
旁的向下箭头将字体颜色设为标准色“深蓝”，单击
按钮将字形设置为“倾斜”，单击“开始”选项卡“对齐方式”组右下角的按钮
，打开“设置单元格格式”对话框的“对齐”选项卡，在“水平对齐”列表框中选择“居中”选项，单击“确定”按钮。设置数字区域的字体和对齐方式结果如图55所示。



图55设置数字区域的字体和对齐方式结果



(2) 利用AVERAGE函数计算每一位学生的“总平均分”。以姓名为“郝乐萌”的学生“总平均分”计算为例，选中单元格G4，在单元格内输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中单击“其他函数”下方的箭头，在弹出的下拉菜单中选择“统计”→AVERAGE命令，弹出AVERAGE函数的“函数参数”对话框。在参数Number1右侧的输入框中已自动填入了记录学生“郝乐萌”各学期数学成绩的单元格范围C4:F4，此处无须更改，单击“确定”按钮。使用AVERAGE函数计算总平均分如图56所示。


图56使用AVERAGE函数计算总平均分


重新选中单元格G4，将鼠标移至该单元格右下角的黑色小方块(即填充柄)上，当鼠标指针变为一个黑色十字形
后，向下拖动鼠标至单元格G10，利用自动填充的方式复制G4中的公式，计算每一个学生的总平均分。
然后利用MAX函数计算各学期及总平均分的最高分。以“第一学期”的最高分计算为例，选中“第一学期”最高分对应的单元格C12，在单元格内输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中，单击“其他函数”下方的箭头，在弹出的下拉菜单中选择“统计”→MAX命令，弹出MAX函数的“函数参数”对话框，在参数Number1右侧的输入框中输入记录第一学期全体同学成绩的单元格范围C4:C10，单击“确定”按钮。接着再次利用自动填充的功能复制C12中的公式至G12，计算每一学期的最高分和最高总平均分。
接下来再次利用AVERAGE函数计算每一学期所有同学的平均分。以“第一学期”的平均分计算为例，选中“第一学期”平均分对应的单元格C13，在单元格内输入等号“=”，进入公式编辑状态，利用相同的方式弹出AVERAGE函数的“函数参数”对话框，在参数Number1右侧的输入框中输入第一学期全体同学成绩的单元格范围C4:C10，单击“确定”按钮。接着再次利用自动填充的功能复制C13中的公式至G13，计算所有同学每一学期的平均分和总平均分。
最后分别对单元格区域G4:G10、C12: G13内的计算结果进行取整。同时选中这两个区域，然后在“开始”选项卡的“数字”组中单击右下角的对话框开启按钮
，打开“设置单元格格式”对话框的“数字”选项卡，在“分类”列表框中选择“数值”类型，将右侧的“小数位数”设置为0，单击“确定”按钮。函数统计计算结果如图57所示。


图57函数统计计算结果


(3) 要使用垂直查询函数对四个学期和总平均分共5项的“平均分”进行评价，首先需要在J4开始的位置建立垂直查询数据表作为评价依据。在垂直查询函数中，若将Range_lookup参数设置为TRUE或者默认值，则表示在查找过程中使用近似匹配原则；如果找不到与Lookup_value参数精确匹配的值，则返回小于Lookup_value参数的最大值。利用该原则，可以将分段评价的底分设置为垂直查询数据表中被查询的值，将评价结果设置为垂直查询数据表中返回的值。垂直查询数据表设置如表51所示。


表51垂直查询数据表设置



被查询的值(底分)
返回的值(评价值)
分数范围
85
良好
85~100
70
中等
70~84
60
合格
60~69
0
不合格
0~59


图58垂直查询数据表

垂直查询数据表如图58所示，将表51中前两列的内容填入垂直查询数据表中。选中第一学期“成绩评价”单元格C14，
在单元格内输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中单击“查找与引用”下方的箭头，在弹出的下拉菜单中选择VLOOKUP命令，弹出VLOOKUP函数的“函数参数”对话框。在参数Lookup_value右侧的输入框中设置要查找的值，即表示“第一学期平均分”的单元格C13。在参数Table_array右侧的输入框中设置被查找的范围，这里设置垂直查询数据表对应的单元格区域$J$4:$K$7。需要注意的是，这里使用了绝对引用，这是为了在复制公式时使搜索的范围固定不变。在参数Col_index_num右侧的输入框中设置返回数据在参数Table_array中的列号，由于评价结果在垂直查询数据表的第2列中，这里将其设置为“2”。在参数Range_lookup右侧的输入框中输入TRUE或保持默认状态，使用近似匹配原则进行查询。设置完成后，单击“确定”按钮。VLOOKUP函数参数设置如图59所示。


图59VLOOKUP函数参数设置


由于第一学期平均分计算结果为56，在被查询范围$J$4:$K$7的第一列(0,60,70,85)中没有与之匹配的值，根据近似匹配原则，将返回小于56的最大值0对应的评价结果“不及格”。利用自动填充的功能复制C14中的公式至G14，使用相同的查询方式获取每一学期“平均分”及“总平均分”的评价结果。VLOOKUP函数计算结果如图510所示。


图510VLOOKUP函数计算结果


(4) 选中需要插入折线图的关联数据单元格区域，包含四个部分： 单元格A3、单元格区域A7:A10、单元格区域C3:F3及单元格区域C7:F10，选中关联数据单元格区域如图511所示。在“插入”选项卡的“图表”组中单击“插入折线图或面积图”按钮，在弹出的下拉菜单中选择“堆积折线图”命令完成基本图表的插入，插入的基本堆积折线图如图512所示。


图511选中关联数据单元格区域




图512插入的基本堆积折线图


接下来对图512中的基本图表进行编辑，使其符合实验内容的要求。首先选中图表，在“图表工具设计”选项卡的“数据”组中单击“切换行/列”按钮，让学生“姓名”在横坐标轴下方显示，将图例更改为“学期”，单击“确定”按钮完成设置。再单击“图表布局”组中“快速布局”按钮，在布局列表中选择“布局12”应用到图表中。对基本图表进行的编辑如图513所示。


图513对基本图表进行的编辑


在绘图区中选中“第三学期”所代表的数据系列，在“图表工具设计”选项卡的“图表布局”组中单击“添加图表元素”按钮，在弹出的下拉菜单中选择“数据标签”→“右侧”命令，将数据标签添加在数据系列中数据点的右侧。再次选中图表，在“图表工具格式”选项卡的“形状样式”列表中选择“彩色轮廓橙色,强调颜色2”，设置了数据标签与形状样式的图表如图514所示。


图514设置了数据标签与形状样式的图表


(5) 单击Sheet1工作表标签右侧的“新工作表”按钮
，在其右侧插入新工作表Sheet2。在Sheet2工作表中选中A1单元格，在“插入”选项卡的“表格”组中单击“数据透视表”按钮，弹出“创建数据透视表”对话框，如图515所示。


图515“创建数据透视表”对话框


在“请选择要分析的数据”栏中选中“选择一个表或区域”单选按钮，在下方的“表/区域”右侧的输入框旁单击选择按钮
，选择数据透视表的数据源单元格区域Sheet1中的单元格区域A3:G10，在“选择放置数据透视表的位置”栏中，已经自动选择了将透视表插入至工作表Sheet2的单元格A1，这里不做更改。单击“确定”按钮插入一个空白数据透视表，同时在右侧显示“数据透视表字段”窗格。“数据透视表字段”窗格如图516所示。


图516“数据透视表字段”窗格


在“选择要添加到报表的字段”下方的字段列表中，分别勾选字段“性别”“第一学期”和“第三学期”，将其添加到数据透视表的行、列和值区域中，在“数值”下方列表中单击“求和项： 第一学期”，在弹出的下拉菜单中选择“值字段设置”命令，弹出“值字段设置”对话框，设置值字段的统计方式和数字格式如图517所示。


图517设置值字段的统计方式和数字格式


在“值字段设置”对话框“计算类型”列表框中选择“平均值”选项，单击左下方的“数字格式”按钮，打开“设置单元格格式”对话框的“数字”选项卡，在“分类”列表框中选择“数值”类型，在右侧的“小数位数”输入框中设置小数位数为2，单击“确定”按钮回到“值字段设置”对话框，再次单击“确定”按钮完成值字段的设置。按照相同的方式对“求和项： 第三学期”的统计方式和数字格式进行设置。选中数据透视表中的任何一个单元格，在“数据透视表工具设计”选项卡的“数据透视表样式”组中单击样式列表中的其他按钮，在弹出的样式列表中选择“白色，数据透视表样式浅色24”，得到的数据透视表插入结果如图518所示。


图518数据透视表插入结果


(6) 在Sheet2工作表标签上双击，此时工作表标签名称将进入可编辑状态，输入新的工作表名“数学成绩”后按Enter键确认。
 实验5．2员工工资统计
【实验要求】
打开素材文件“员工工资．xlsx”，完成如下操作： 
(1) 重命名Sheet1工作表为“筛选统计”。
(2) 在A1行前插入标题行，输入标题内容为“长城电脑公司职工工资表”，字体设置为“华文彩云”，字的颜色设置为“标准色红色”，字号设置为“16”，将标题单元格区域A1:F1设置为“合并后居中”格式。
(3) 利用公式计算公积金(按基本工资的12%缴纳)、实发工资(=基本工资+奖金-公积金)，用函数计算研发部所有员工实发工资之和(上述结果均保留2位小数)，统计公司中基本工资高于2000元的员工人数。
(4) 使用条件格式将“实发工资”列内大于4000元的值设置为红色、加粗格式。
(5) 根据前4位员工的“基本工资”和“实发工资”绘制三维簇状柱形图，图例为姓名，图表标题为“工资比较图”，选择“图表布局1”和“图表样式11”，形状样式选择“强烈效果橙色,强调颜色6”。将图表放在工作表右侧。
(6) 在A14开始的位置建立筛选条件，利用“高级筛选”功能查找“研发部”奖金在2500元以上或实发工资在5000元以上的职工记录，并将筛选结果显示在F15开始位置。
【实验素材】
员工工资．xlsx
员工工资素材内容如图519所示。


图519员工工资素材内容


【实验步骤】
(1) 在Sheet1工作表标签上双击，此时工作表标签名称将进入可编辑状态，输入新的工作表名“筛选统计”后按Enter键确认。
(2) 单击工作表左侧的行号“1”，选中工作表的第一行，在第一行上右击，在弹出的快捷菜单中选择“插入”命令，在第一行上方插入新的一行。选中单元格A1，输入标题“长城电脑公司职工工资表”后，在“开始”选项卡“字体”组中的“字体”下拉菜单中选择字体“华文彩云”，在“字体颜色”选择框中选择“红色”，在“字号”下拉菜单中设置字体大小为“16”，完成标题字体格式的设置。再选中标题区域A1:F1，在“开始”选项卡的“对齐方式”组中单击“合并后居中”按钮，完成标题区域的单元格合并。
(3) 计算每位员工的公积金、实发工资金额并对其进行统计。实验要求计算结果精确到2位小数，因此需要分别对单元格区域E3:F9和单元格E11内的数字格式进行设置。方法为选中单元格区域E3:F9和单元格E11，然后在“开始”选项卡的“数字”组中单击右下角的按钮
，打开“设置单元格格式”对话框的“数字”选项卡。在左侧“分类”列表框中选择“数值”选项，将右侧“小数位数”设置为2，单击“确定”按钮。
接着计算每位员工的公积金和实发工资金额。首先利用公式计算每位员工应缴纳的公积金(按基本工资的12%缴纳)，以员工“王军”的公积金计算为例，在单元格E3中输入公式“=C3*12%”(其中单元格C3的值代表员工“王军”的基本工资数额，公式将计算基本工资的12%作为员工“王军”的公积金填入单元格E3)。使用填充柄复制E3中的公式到单元格E9，计算每一个员工的公积金金额。再利用公式计算每位员工的实发工资金额(=基本工资+奖金-公积金)，以员工“王军”的实发工资计算为例，在单元格F3中输入公式“=C3+D3-E3”(其中单元格C3、D3和E3的值分别代表员工“王军”的基本工资、奖金和公积金数额，得到的实发工资计算结果将填入单元格F3)。使用填充柄复制F3中的公式到单元格F9，计算每一个员工的实发工资金额。员工工资计算结果如图520所示。


图520员工工资计算结果


然后利用SUMIF函数统计所有研发部员工实发工资之和。选中统计结果单元格E11，在单元格内输入等号“=”，进入公式编辑状态，在“公式”选项卡的“函数库”组中单击“数学和三角函数”下方的箭头，在弹出的下拉菜单中选择SUMIF函数，打开SUMIF函数的“函数参数”对话框。在参数Range右侧的输入框中选择进行求和条件判断的单元格区域，此处单击输入框右侧的选择按钮
，选中存放员工所在“部门”信息的单元格区域A3:A9； 在参数Criteria右侧的输入框中输入求和条件“研发部”，即对“部门”为“研发部”的员工信息进行统计； 在Sum_range参数右侧的输入框中输入实际用于求和的单元格区域，此处单击输入框右侧的选择按钮
，选中存放员工“实发工资”信息的单元格区域F3:F9，利用SUMIF函数统计研发部员工实发工资之和如图521所示。完成上述设置后单击“确定”按钮。


图521利用SUMIF函数统计研发部员工实发工资之和


最后利用COUNTIF函数统计公司基本工资大于2000元的员工人数。选中统计结果单元格E12，在单元格内输入等号“=”，进入公式编辑状态，在“公式”选项卡的“函数库”组中单击“其他函数”下方的箭头，在弹出的下拉菜单中选择“统计”→COUNTIF命令，打开COUNTIF函数的“函数参数”对话框。在参数Range右侧的输入框中选择进行计数条件判断的单元格区域，此处单击输入框右侧的选择按钮
，选中存放员工“基本工资”信息的单元格区域C3:C9； 在参数Criteria右侧的输入框中输入计数条件“>2000”，即对基本工资大于2000元的员工数量进行统计，利用COUNTIF函数统计基本工资大于2000元的员工个数如图522所示。完成上述设置后单击“确定”按钮。员工工资信息统计结果如图523所示。


图522利用COUNTIF函数统计基本工资大于2000元的员工个数




图523员工工资信息统计结果


(4) 对“实发工资”列中大于4000元的值进行条件格式设置。首先选中该列对应的单元格区域F3:F9，在“开始”选项卡的“样式”组中单击“条件格式”按钮，在弹出的下拉菜单中选择“突出显示单元格规则”→“大于”命令，弹出“大于”对话框，在“大于”和“设置单元格格式”对话框中设置条件格式如图524所示。


图524在“大于”和“设置单元格格式”对话框中设置条件格式


在“为大于以下值的单元格设置格式： ”下方的输入框中输入条件“4000”，即对“实发工资”大于4000元的单元格进行条件格式设置，在“设置为”右侧的下拉菜单中选择“自定义格式”，打开“设置单元格格式”对话框的“字体”选项卡。设置条件格式字体颜色为“红色”，字形为“加粗”，如图524所示。单击“确定”按钮完成格式设置回到“大于”对话框，再次单击“确定”按钮完成条件格式的设置，条件格式设置结果如图525所示。


图525条件格式设置结果



(5) 选中需要插入三维簇状柱形图的关联数据单元格区域，该区域包含3部分： 前4位员工的“姓名”区域B3:B6； 前4位员工的“基础工资”和“实发工资”区域C3:C6和F3:F6。在“插入”选项卡的“图表”组中单击“柱形图”按钮，在弹出的下拉菜单中选择“三维簇状柱形图”完成基本图表的插入，如图526所示。


图526插入基本三维簇状柱形图


接下来对图526中的基本图表进行编辑，使其符合实验内容的要求。首先选中图表区，在“图表工具设计”选项卡的“数据”组中单击“切换行/列”按钮，将图例更改为“姓名”。保持图表区的选中状态，在“图表工具设计”选项卡的“图表布局”组中单击“快速布局”按钮，在“快速布局”下拉菜单中选择“布局1”。在右侧的“图表样式”组中单击“其他”按钮，在“图表样式”下拉菜单中选择“样式11”。切换到“格式”选项卡，在“形状样式”组中单击“其他”按钮，在“主题样式”列表框中选择“强烈效果橙色,强调颜色6”。选中图表区中的标题文本框，在其中输入图表标题“工资比较图”。拖动整个图表区至工作表右侧，得到的三维簇状柱形图编辑结果如图527所示。


图527三维簇状柱形图编辑结果


(6) 利用“高级筛选”功能查找“研发部”奖金在2500元以上或实发工资在5000元以上的职工记录。在A14开始的位置建立筛选条件区域，此区域由三行组成： 第一行为所要筛选的字段名称，包括“部门”“奖金”和“实发工资”； 第二行为筛选的第一类条件，即“部门”满足“=研发部”，“奖金”满足“>2500”； 第三行为筛选的第二类条件，即“部门”满足“=研发部”，“实发工资”满足“>5000”。注意： 第二、三行的筛选条件与第一行中的字段名对齐； 在向单元格输入筛选条件“=研发部”时，应在等号“=”前面添加一个英文半角单引号“’”，以免Excel默认认为等号“=”后面为公式内容。
确立了高级筛选条件后，单击需要显示筛选结果的位置，这里选择单元格F15。在“数据”选项卡的“排序和筛选”组中单击“高级”按钮，弹出“高级筛选”对话框。在“方式”下方的单选框中选择筛选结果的存放方式，这里选择“将筛选结果复制到其他位置”单选按钮，在“列表区域”框中选择进行筛选的区域，这里选择工作表中工资数据区域A2:F9，在“条件区域”框中选择筛选条件所在的区域，这里选择工作表中的筛选条件区域A14:J16，在“复制到”框中选中单元格F15，此时筛选结果将从F15开始向右向下填充，如图528所示。单击“确定”按钮，得到所有“研发部”奖金在2500元以上或实发工资在5000元以上的职工记录，利用“高级筛选”功能进行查找如图529所示。


图528设置“高级筛选”对话框




图529利用“高级筛选”功能进行查找


 实验5．3员工个税情况统计
【实验要求】
打开素材文件“员工个税情况．xlsx”，完成如下操作： 
(1) 利用函数计算“应发工资”(保留2位小数)。
(2) 用IF函数找出应发工资高于5000元的职工，并用“需纳税”字样显示，其他无显示(输入一对西文双引号)。
(3) 将单元格区域C3:F8设置为保留2位小数，字形为“倾斜”，水平对齐方式为“居中”格式。
(4) 将单元格区域A1:G8设置内边框为“最细虚线”，外边框为“双线”格式。
(5) 将“程程”的工资数据绘制成一张饼图，比较“基本工资”“补贴”和“奖金”所占“应发工资”的百分比。图表标题为“程程工资”，选择“图表样式8” 和“图表布局1”，显示百分比(保留2位小数)和类别名称。
(6) 将单元格区域A1:G8中的内容复制到Sheet2工作表首行(A1为起始位置)，将Sheet2工作表重命名为“工资统计”。
(7) 在工作表“工资统计”中建立分类汇总表，按车间分别统计“奖金”和“应发工资”的总计(提示： 排序按升序)。
【实验素材】
员工个税情况．xlsx
员工个税情况素材内容如图530所示。


图530员工个税情况素材内容


【实验步骤】
(1) 利用SUM函数计算每位员工的“应发工资”，即对“基本工资”“补贴”和“奖金”三项进行求和运算。以员工“王勇军”的“应发工资”计算为例，在单元格F3内输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中单击“数学和三角函数”下方的箭头，在弹出的下拉菜单中选择SUM命令，弹出SUM函数的“函数参数”对话框。在参数Number1右侧的输入框中已自动填入了记录员工“王勇军”各项工资金额的单元格区域C3:E3，此处无须更改，直接单击“确定”按钮完成求和计算，利用SUM函数计算员工的“应发工资”如图531所示。使用填充柄将F3中的公式复制到F8，即可计算每位员工的“应发工资”。


图531利用SUM函数计算员工的“应发工资”


(2) 利用IF函数判断每位员工是否需要缴纳个税，即判断其“实发工资”金额是否高于5000元。以员工“王勇军”的个税缴纳判断为例，在单元格G3中输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中单击“逻辑”下方的箭头，在弹出的下拉菜单中选择IF命令，弹出IF函数的“函数参数”对话框。在参数Logical_test右侧的输入框中输入逻辑判断表达式“F3>5000”，其中F3存放的值为员工“王勇军”的应发工资值，判断其是否大于5000元； 在参数Value_if_true右侧的输入框中输入当“F3>5000”为真时返回到单元格G3中的值，这里输入“需纳税”，即当应发工资值大于5000元时，应缴纳个税； 在参数Value_if_false右侧的输入框中输入当“F3>5000”为假时返回到单元格G3中的值，这里输入一对西文双引号（""）返回空值，即当应发工资不大于5000元时，无须缴纳个税，如图532所示。设置完毕后单击“确定”按钮完成函数的计算。使用填充柄将G3中的公式向下复制填充至G8，即可对每位员工是否需要缴税进行判断。


图532利用IF函数判断员工是否需要缴纳个税


(3) 设置单元格区域C3:F8的数值格式。首先选中单元格区域C3:F8，在“开始”选项卡的“数字”组中单击右下角的按钮
，打开“设置单元格格式”对话框的“数字”选项卡。在左侧“分类”列表框中选择“数值”选项，将右侧“小数位数”设置为“2”，完成数值格式的设置。然后切换至“对齐”选项卡，在“水平对齐”列表框中选择“居中”选项，完成对齐方式的设置。最后切换至“字体”选项卡，在“字形”列表框中选择“倾斜”选项，完成字形的设置。单击“确定”按钮实现上述所有的格式设置。
(4) 为单元格区域A1:G8设置边框。首先选中单元格区域A1:G8，在“开始”选项卡的“字体”组中单击“下框线”
按钮右侧的下拉箭头，在弹出的下拉菜单中选择“其他边框”命令，打开“设置单元格格式”对话框的“边框”选项卡。在选项卡中对选中范围的外边框进行设置，首先在“线条样式”列表中选中“双线”
(右列最下方的线型)，单击“外边框”按钮
确定设置的内容。接下来设置内边框的“线条样式”为“最细虚线”
(左列最上方的线型)，单击“内边框”按钮
确定设置的内容，最后单击“确定”按钮完成边框设置。单元格区域边框设置结果如图533所示。


图533单元格区域边框设置结果


(5) 选中需要插入饼图的关联数据单元格区域，即员工“程程”的“基本工资”“补贴”和“奖金”对应的列名与单元格区域C2:E2及C7:E7。单击“插入”选项卡“图表”组中的“插入饼图或圆环图”按钮，在弹出的下拉菜单中选择“二维饼图”命令完成基本图表的插入。插入的基本饼图如图534所示。


图534插入的基本饼图


接下来对图534中的基本图表进行编辑，以满足实验要求。首先选中图表区，在“图表工具设计”选项卡的“图表样式”组中选择“样式8”，在“图表布局”组中单击“快速布局”按钮，在弹出的下拉菜单中选择“布局1”。再单击数据标签，调出“设置数据标签格式”窗格，切换到“标签选项”选项卡。在“标签选项”选项卡下的“标签包括”选项中依次勾选“类别名称”“百分比”复选框； 在“数字”选项中选择“百分比”类别，小数位

数设置为“2”，即显示保留2位小数的百分比数字作为饼图中的数据系列标签。最后选中图表区中的标题文本框，在其中输入图表标题“程程工资”。饼图编辑结果如图535所示。


图535饼图编辑结果


(6) 选中单元格区域A1:G8，右击，在弹出的快捷菜单中选择“复制”命令。单击Sheet1工作表标签右侧的“新工作表”按钮
，在其右侧插入新工作表Sheet2。选中单元格A1，在“开始”选项卡的“剪贴板”组中单击“粘贴”下方的箭头，在弹出的“粘贴”下拉菜单中选择“保留源格式”命令，完成单元格区域的复制、粘贴操作。
在Sheet2工作表标签上双击，此时工作表标签名称将进入可编辑状态，输入新的工作表名“工资统计”后按Enter键确认。
(7) 在工作表“工资统计”中建立分类汇总对各个车间的工资情况进行统计。为了按照不同的车间进行分类汇总，首先需要对工作表中的数据按照车间进行排序。这里选中“车间”字段范围A3:A8中的任意一个单元格(如单元格A5)，在“数据”选项卡的“排序和筛选”组中单击“升序”按钮
，对工作表中的数据按照“车间”字段的内容升序排序，如图536所示。


图536按照“车间”字段的内容升序排序


接下来完成数据的汇总操作。单击单元格区域A2:G8中的任意一个单元格(如单元格C3)，在“数据”选项卡的“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框。在“分类字段”下拉列表框中选择分类字段，此处选择“车间”选项。在“汇总方式”下拉列表框中选择汇总方式，此处选择“求和”选项，即对每个车间员工工资进行总计求和。在“选定汇总项”下拉列表框中选择汇总字段，此处勾选“奖金”和“应发工资”复选框，统计每个车间员工“奖金”和“应发工资”之和，如图537所示。


图537在“分类汇总”对话框中对分类汇总参数进行设置


单击“确定”按钮，完成数据分类汇总分析，工作表分类汇总结果如图538所示。


图538工作表分类汇总结果


 实验5．4产品销售信息统计
【实验要求】
销售部需要对2018年的产品销售情况进行统计分析，以便制订新一年的销售计划。完成如下操作： 
(1) 新建一个空白的工作簿文档，将该文档以“产品销售信息．xlsx”为文件名保存在本章实验素材文件夹下。
(2) 将以制表符分隔的文本文件“产品销售表”自单元格A1开始导入Sheet1工作表中，并将该工作表标签颜色设置为“红色(标准色)”。
(3) 在Sheet1工作表数据区域的右侧增加一列，在单元格P1中输入“总销售量”作为列名并计算每月的总销售量。在Sheet1工作表数据区域的底部增加一行，在单元格A14中输入“平均”作为行名并计算每种产品全年平均月销售量(保留2位小数)。
(4) 在Sheet1工作表“总销售量”列的右侧增加一列，在单元格Q1中输入“销量超过85的产品数”作为列名，统计每个月销售量超过85的产品总数填入该列相应的单元格。
(5) 将Sheet1工作表套用表格格式“橙色，表样式中等深浅10”。
(6) 将Sheet1工作表的单元格区域A1:P13的数值及格式复制到Sheet2工作表，对Sheet2工作表中的内容进行自动筛选，要求筛选出“产品一”销量小于95，且总销量大于1150的销售数据。
(7) 在Sheet2工作表后添加Sheet3工作表，将Sheet1工作表中单元格区域A1:A15及P3:P15的单元格内容复制到Sheet3工作表。
(8) 将Sheet3工作表中的内容先按照总销量大小降序排序，再按照自定义序列“一月，二月，……，十二月”次序排序。
(9) 将Sheet3工作表中的数据绘制二维簇状柱形图，其中“月份”为水平轴标签，“总销售量”为图例项，要求添加对数趋势线(红色实线)。
【实验素材】
产品销售表．txt
产品销售素材内容如图539所示。


图539产品销售素材内容(以记事本方式打开)


【实验步骤】
(1) 启动Excel 2016，在Excel窗口右侧单击“空白工作簿”按钮，即创建一个空白的工作簿文档。单击“快速访问工具栏”中的“保存”按钮或按Ctrl+S组合键保存文件，在“另存为”对话框中选择保存位置为本章实验素材文件夹，输入文件名为“产品销售信息．x1sx”，单击“保存”按钮。
(2) 选中单元格A1，在“数据”选项卡的“获取外部数据”组中单击“自文本”按钮，弹出“导入文本文件”对话框。在对话框中选择要导入的文本文件，这里选择“产品销售表．txt”文件，单击“导入”按钮。弹出“文本导入向导第1步，共3步”对话框，系统会自动判断数据中是否具有分隔符，单击“下一步”按钮。在弹出的“文本导入向导第2步，共3步”对话框中设置分隔数据所包含的分隔符号，这里勾选“Tab键”复选框，单击“完成”按钮，在弹出的“导入数据”对话框中输入导入的开始位置，单击“确定”按钮完成导入。在Excel中完成文本文件的导入结果如图540所示。


图540在Excel中完成文本文件的导入结果


在Sheet1工作表标签上右击，在弹出的快捷菜单中选择“工作表标签颜色”命令，在弹出的级联菜单中选择标准色中的“红色”。
(3) 在单元格A14中输入行标题“平均”，使用AVERAGE函数统计每类产品全年平均月销售量。以“产品一”的全年平均月销售量计算为例，在单元格B14内输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中单击“其他函数”下方的箭头，在弹出的下拉菜单中选择“统计”→AVERAGE命令，弹出AVERAGE函数的“函数参数”对话框。在参数Number1右侧的输入框中输入求平均值的单元格范围，单击“确定”按钮完成平均值统计计算，如图541所示。利用填充柄将B14中的公式复制到O14，计算每类产品的月平均销量。


图541利用AVERAGE函数统计各类产品的月平均销量


实验要求将产品月平均销量统计结果精确到小数点后2位，因此需要对平均销量统计结果对应的单元格区域B14:O14进行数值格式设置。首先选中单元格区域B14:O14，在“开始”选项卡的“数字”组中单击右下角的按钮
，打开“设置单元格格式”对话框的“数字”选项卡，在“分类”列表框中选择“数值”类型，将右侧的“小数位数”设置为“2”，即保留2位小数，单击“确定”按钮完成设置。
接下来在单元格P1中输入列标题“总销售量”，使用SUM函数对每月所有产品的总销量进行统计。以“一月”的总销量统计为例，在单元格P2中输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中单击“数学和三角函数”下方的箭头，在弹出的下拉菜单中选择SUM命令，弹出SUM函数的“函数参数”对话框，在参数Number1右侧的输入框中输入待求和的单元格范围，如图542所示。单击“确定”按钮完成求和统计计算。 


图542利用SUM函数统计每月所有商品销量之和


利用填充柄将P2中的公式和函数复制到P13，计算每月所有产品的月销量之和。各类产品月平均销量及所有产品每月总销售量统计结果如图543所示。


图543各类产品月平均销量及所有产品每月总销售量统计结果


(4) 在单元格Q1中输入列标题“销量超过85的产品数”，再利用COUNTIF函数统计每月销量超过85的产品个数。以“一月”中销量超过85的产品个数统计为例，在单元格Q2内输入等号“=”，进入公式编辑状态。在“公式”选项卡的“函数库”组中单击“数学和三角函数”下方的箭头，在弹出的下拉菜单中选择“统计”→COUNTIF命令，弹出COUNTIF函数的“函数参数”对话框。在参数Range右侧的输入框中选择进行计数条件判断的单元格区域，此处单击输入框右侧的选择按钮
，选中存放“一月”各产品销量信息的单元格区域B2:O2； 在参数Criteria右侧的输入框中输入计数条件“>85”，即对销量大于85的产品数量进行统计。利用COUNTIF函数统计每月销量超过85的产品个数如图544所示。完成上述设置后单击“确定”按钮。 


图544利用COUNTIF函数统计每月销量超过85的产品个数


利用填充柄将Q2中的公式复制到Q13，计算每月销量超过85的产品个数，每月销量大于85的产品个数统计结果如图545所示。


图545每月销量大于85的产品个数统计结果


(5) 接下来完成表格的格式套用。首先选中Sheet1工作表的单元格区域A1:Q14，在“开始”选项卡的“样式”组中单击“套用表格样式”按钮，在弹出的表格样式列表中选择“橙色，表样式中等深浅10”，弹出“套用表格式”对话框。在“表数据的来源”下方的数据框中确定其中的单元格区域为A1:Q14，并勾选“表包含标题”复选框，完成表格式的设置操作，如图546所示，单击“确定”按钮完成表格式的设置。


图546在“套用表格式”对话框中完成表格式的设置


完成表格格式套用后，工作表Sheet1的单元格区域A1:Q14的格式如图547所示。


图547表格格式套用后，工作表Sheet1的单元格区域A1:Q14的格式


(6) 将Sheet1工作表的单元格区域A1:P13的数值及格式复制到Sheet2工作表中。首先选中Sheet1工作表的单元格区域A1:P13，右击，在弹出的快捷菜单中选择“复制”命令。单击Sheet1工作表标签右侧的“新工作表”按钮
，在其右侧插入新工作表Sheet2，选中单元格A1，在“开始”选项卡的“剪贴板”组中单击“粘贴”按钮下方的箭头，在弹出的“粘贴”下拉菜单中选择“保留源格式”命令，完成工作表单元格区域的复制、粘贴。工作表单元格区域复制、粘贴结果如图548所示。


图548工作表单元格区域复制、粘贴结果


接下来完成对Sheet2工作表中内容的自动筛选。首先选中Sheet2工作表的单元格区域A1:P13内的任意一个单元格，这里选中单元格H6。在“数据”选项卡的“排序和筛选”组中单击“筛选”按钮，或者在“开始”选项卡的“编辑”组中选择“排序和筛选”下拉菜单中的“筛选”命令，让单元格区域A1:P13进入自动筛选状态，此时在每列的列名右侧会出现一个筛选箭头
。根据“产品一”列的销量信息进行筛选，单击“产品一”列标题旁的筛选箭头，弹出“筛选器选择”对话框，单击“数字筛选”按钮，在弹出的子菜单列表中选择“小于”命令，弹出“自定义自动筛选方式”对话框，在“产品一”中“小于”右侧的输入框中输入“95”，表示对“产品一”销量小于95的数据进行筛选，如图549所示。单击“确定”按钮完成关于“产品一”销量数据的筛选。对“产品一”销售信息进行筛选后的结果如图550所示。


图549对“产品一”销量小于95的数据进行筛选




图550对“产品一”销售信息进行筛选后的结果


然后在图550结果的基础上根据“总销售量”列的销量信息进行再次筛选。单击“总销售量”列标题旁的筛选箭头，弹出“筛选器选择”对话框，单击“数字筛选”按钮，在弹出的子菜单列表中选择“大于”命令，弹出“自定义自动筛选方式”对话框，在“总销售量”中“大于”右侧的输入框中输入“1150”，表示对“总销售量”大于1150的数据进行筛选，如图551所示。单击“确定”按钮完成关于“总销售量”数据的筛选，得到自动筛选最终结果如图552所示。



图551对“总销售量”大于1150的数据进行筛选




图552自动筛选最终结果


(7) 在工作簿中新增一个工作表Sheet3。单击Sheet2工作表标签右侧的“新工作表”按钮
，在其右侧插入新工作表Sheet3。再通过工作表标签切换至Sheet1工作表，同时选中单元格区域A1:A13及P1:P13，右击，在弹出的快捷菜单中选择“复制”命令。再次通过工作表标签切换至Sheet3工作表中，选择单元格A1作为粘贴的起始单元格，在“开始”选项卡的“剪贴板”组中单击“粘贴”按钮下方的箭头，在弹出的“粘贴”选项列表中选择“值和源格式”命令，完成工作表单元格区域的复制、粘贴。单元格区域复制结果如图553所示。

(8) 对Sheet3工作表中的内容先按照总销量大小降序排序。选择“总销售量”列的单元格区域B1:B13中的任意一个单元格，这里选择单元格B2。在“数据”选项卡的“排序和筛选”组中单击
“降序”按钮，即可根据每月的“总销售量”大小进行降序排序，“总销售量”降序排序结果如图554所示。



图553单元格区域复制结果




图554“总销售量”降序排序结果


接下来再按照自定义序列“一月，二月，……，十二月”的次序排序。选择单元格区域A1:B13中的任意一个单元格，如单元格B6，在“数据”选项卡“排序和筛选”组中单击
“排序”按钮，或者在“开始”选项卡的“编辑”组中选择“排序和筛选”下拉菜单中的“自定义排序”命令，在弹出的“排序”对话框中进行排序设置，如图555所示。


图555在“排序”对话框中进行排序设置


在“主要关键字”下拉列表框中选择“月份”选项作为排序条件，在“排序依据”下拉列表框中选择“单元格值”选项，在“次序”下拉列表框中选择“自定义序列”选项，弹出“自定义序列”对话框。在此对话框中选择序列“一月，二月，……，十二月”作为排序依据，单击“确定”按钮完成设置，在“自定义序列”对话框中选择自定义序列操作如图556所示。


图556在“自定义序列”对话框中选择自定义序列操作


回到“排序”对话框，单击“确定”按钮完成自定义排序，得到与图553中右侧单元格区域一样的排序结果。
(9) 在Sheet3工作表中插入二维簇状柱形图。首先选择图表关联数据单元格区域A1:B13，在“插入”选项卡的“图表”组中单击“插入柱状图或条形图”按钮，在弹出的下拉菜单中选择“簇状柱形图”命令完成基本图表的插入，再将“布局3”应用到该图表中。选中基本图表，在“图表工具设计”选项卡的“图表布局”组中单击“快速布局”下拉按钮，在弹出的下拉菜单中选择“布局3”应用到该图表中。图中水平轴标签为“月份”，图例为“总销售量”。插入基本二维簇状柱形图如图557所示。


图557插入基本二维簇状柱形图


接下来为柱形图添加趋势线。单击柱状图形，选中对应的数据系列，右击，在弹出的快捷菜单中选择“添加趋势线”命令，打开“设置趋势线格式”窗格。在“趋势线选项”选项卡下，选中“趋势线选项”下的“对数”单选按钮； 切换到“填充与线条”选项卡，选中“线条”下的“实线”单选按钮，设置线条类型为“实线”，颜色为“红色”，宽度为“2磅”，短画线类型为“实线”，如图558所示。

单击“关闭”按钮完成趋势线的添加，调整柱形图的大小使其更加美观。二维簇状柱形图趋势线添加结果如图559所示。



图558在“设置趋势线格式”窗格中设置趋势线




图559二维簇状柱形图趋势线添加结果