项目七 报表数据分类汇总

项目七报表数据分类汇总【学习目标】掌握在工作表中插入分类汇总数据的方法掌握建立多级分类汇总的方法掌握多工作表数据合并方法掌握多工作表的单元格引用与链接方法任务本项目要求对“员工信息表”中的信息进行分类汇总。项目包括5个任务，它们分别是：插入分类汇总利用公式建立多级分类汇总多工作表数据汇总数据汇总中的单元格引用和链接删除分类汇总任务一

插入分类汇总假设某公司有8名员工，他们分别属于4个部门，表中登记了他们的姓名、性别、工作时间、所在部门、政治面貌、学历及某月份的工资和奖金收入情况，见图7.3。任务一

插入分类汇总计算并给出全公司总共的工资和奖金数额，汇总操作过程包括：按职工的“所在部门”将职工分类；计算各部门的职工工资和奖金总额，并得出全公司的工资奖金额；对汇总项的摘要信息做适当编辑。（一）按所在部门分类这项操作的目的是，将所在部门相同的所有员工信息集中排列在一起。【步骤解析】在“公司员工信息表”上，选中“所在部门”列的（D3:D10）作为排序列，使该区域背景颜色加深。任务一

插入分类汇总单击鼠标右键，在弹出的快捷菜单中选【排序】功能。在弹出的活动菜单中选择“升序（S）”，或降序（O）即可。排序后的公司员工信息表恰好实现了按所在部门分类的要求，图7.4是按升序排序的结果。（二）建立分类汇总数据下面在各个科室人员的记录后面增加一条汇总记录，计算并填入该科室员工的工资和奖金汇总数据，然后在表的最后填上一条全公司的汇总记录。【步骤解析】从单元格A2处按下鼠标左键，拖动至H10，将区域（A2：H10）作为操作对象，即数据汇总区域（注意，汇总区域一定要包含列标签所在的行。否则将出现汇总错误）。任务一

插入分类汇总打开【数据】选项卡。在【分级显示】组中选择【分类汇总】命令，弹出【分类汇总】对话框，见图7.5所示。在【分类汇总】对话框中设定汇总的要求：比如，通过操纵右侧的下拉列表，将分类字段的名字设定为“所在部门”；将【汇总方式】设定为【求和】（默认选择）；通过复选方式将【选定汇总项】设定为【工资】和【奖金】；在复选框的选择中，勾选“替换当前分类汇总”和“汇总结果显示在数据下方”。确信无误后按

按钮，系统就可将相同关键字值进行工资和奖金数据求和，结果见图7.6所示。任务一

插入分类汇总（三）对分类汇总中的摘要信息进行编辑系统添加的汇总行中附带地添加了简单的摘要（即文本类信息），如果你对系统填加的摘要不满意，可以进行改动。比如，将图中的“销售科汇总”信息改为：“销售科工资与奖金合计”你应当先删除原有信息，根据情况要求进行单元格合并，然后填入新信息。【步骤解析】任务一

插入分类汇总删除D5中的老信息。用鼠标选择操作区域（A5:F5），在其中的一个单元格上击右键，弹出快捷菜单。选择其中的【设置单元格格式】功能项，打开其对话框。选择【对齐】选项卡中的【合并单元格】即可得到。（四）数据的分级显示读者可以在表的左上角看到对应的3个数码

，这里，第1层指的是最外层，也就是总计信息层。第2层是按科室汇总的信息，基础数据是第3层。如果点击3个数码

中的1，将只显示总计数据，点击2显示第1和第2层的数据，点击3显示的是表中的基础数据及其前两层汇总的信息（内容如图7.5所示）。点击1和2，分别显示一级汇总数据和一、二级汇总数据，见图7.8和图7.9所示。任务二

利用公式建立多级分类汇总（一）建立多级汇总表下面针对“公司员工信息表”，在原来的汇总基础上增加一层汇总数据——按“业务部”和“政工部”进行汇总，具体操作过程需要插入政工部的汇总行和业务部的汇总行，并填入汇总数据。1．插入政工部的汇总行在第8行后面插入一个空白行，以便插入“政工部汇总数据”行并填写汇总数据。任务二

利用公式建立多级分类汇总【步骤解析】将鼠标移到第9行的行号上，击右键弹出活动菜单，选择其中的【插入】按钮。在弹出的活动菜单中选择“整行（R）”项，按

按钮后，新插入一个空行作为第9行。将第9行的前6个单元格合并成一个单元格，将“政工部汇总数据”信息输入到第9行中作为该行的摘要信息。将摘要内容设置为黑体，水平居中（操作步骤是，用鼠标选定该单元格，击右键打开活动菜单，选择【设置单元格格式】项，在弹出的对话框中选【字体】卡，然后选择合适的选项）。在第9行的“工资”单元格G9中输入如下公式：“=SUBTOTAL（9,G3:G8）”其中的汇总区域为（G3:G8），表示将单元格G3到G8的数据汇总起来。任务二

利用公式建立多级分类汇总在第9的“奖金”单元格H9中输入如下公式（见图7.11所示）：“=SUBTOTAL（9,H3:H8）”2．插入业务部的汇总行类似地，可在第15行后面插入“业务部汇总数据”行。作为新插入的第16行，在其“工资”单元格G16中输入如下公式“=SUBTOTAL（9,G10:G15）”，在单元格H16中输入公式“=SUBTOTAL（9,H10:H15）”。这样，一个多级汇总表就设计出来了，见图7.12。任务二

利用公式建立多级分类汇总任务二

利用公式建立多级分类汇总（二）建立4级显示功能我们可在“公司员工信息表”中建立分级显示，具体操作步骤如下。【步骤解析】选中“公司员工信息表”内的汇总区域，或表内一个单元格。打开【数据】选项卡，在【分组显示】组中，单击【组合】功能右侧的下拉按钮，弹出下菜单。选择【自动建立分级显示】项后，系统打开【MicrosoftOfficeExcel】对话框。任务二

利用公式建立多级分类汇总按其中的

按钮后，系统对该工作表进行分析，自动建立起4级显示机制。处理过程见图7.13所示。任务二

利用公式建立多级分类汇总（三）显示功能测试显示窗口的左侧是分级显示控制区，其上方有一排控制按钮

，分别对应4级显示方式。用鼠标点击1，屏幕显示与前面的图7.8相同，点击4的显示是4层汇总信息（内容如图7.13所示）,点击2和3的显示数据见图7.14和图7.15所示。任务三

多工作表数据汇总制作了上半年的公司员工信息表之后，可以如法炮制下半年公司员工信息表。这里重点介绍如何利用它们建立年终公司员工信息表——公司员工信息表（年终）。任务三

多工作表数据汇总（一）制作“公司员工信息表（下半年）”在工作簿的Sheet2中建立“公司员工信息表（下半年）”作为第二个源工作表。【步骤解析】在Sheet1中的“公司员工信息表（上半年）”，选择操作区域（A1:H17），按组合键Ctrl+C，将该区域的数据拷贝到剪贴板中。在Sheet2中（如果有垃圾数据，将它们清除），选择操作区域（A1:H17），按组合键Ctrl+V，将剪贴板中的数据拷贝到该区域。任务三

多工作表数据汇总将表头信息改为“公司员工信息表（下半年）”，而后将8位员工下半年的工资和奖金实际数据通过数据编辑方式填入相应的单元格中。填写后的工作表入图7.17所示。任务三

多工作表数据汇总（二）制作“公司员工信息表（年终）”类似地，我们在工作簿的Sheet3中建立“公司员工信息表（年终）”作为主工作表，然后将表中的工资和奖金数据删除，以便填入全年的合计数据。操作结果见图7.18所示。任务三

多工作表数据汇总【步骤解析】在主工作表（也就是sheet3）中，用鼠标选中欲进行合并计算的数据区域（G3:H17）共计30个单元格。确保鼠标在该区域中，打开【数据】选项卡，选择【数据工具】组中的合并计算按钮

，打开【合并计算】对话框。【合并计算】对话框顶部是【函数】选择，有11个函数（如第一节所述）供选择。此处我们选择的是【求和】函数，这也是默认的选择。【合并计算】对话框中部是被引用数据的【位置选择】，在它的编辑框中输入“SHEET1！$G$3:$H$17”，按

按钮，将输入的第一个引用位置添到“所有引用位置”的窗口中。任务三

多工作表数据汇总然后再在编辑框中输入“SHEET2！$G$3:$H$17”，并按

按钮，将输入的第二个引用位置添到“所有引用位置”的窗口中（见图7.19所示）。按

按钮后，系统将上半年和下半年的工资和奖金数据求和后自动填入年终信息表中了（见图7.20所示）。任务四

数据汇总中的单元格引用和链接（一）单元格引用设计任务是在该工作簿中增加一个新工作表Sheet4作为“企业员工收入分析表”，将上半年、下半年各部门的工资、奖金求和，获得平均值填入表中。同时计算出公司全员收入合计，见图7.21所示。【步骤解析】以Sheet4中的“企业员工收入分析表”作为主工作表，选择其中的单元格C4，以便计算公司全员收入中的工资总额部分。任务四

数据汇总中的单元格引用和链打开【数据】选项卡，选择【数据工具】组中的“合并计算”按钮

，打开【合并计算】对话框。我们选择的是【求和】函数。输入C4的生成公式，以C4的第一个公式为例。首先在【引用位置】编辑框内输入“Sheet1!$G$17”,单击右侧的

按钮，将本次输入的引用的单元格位置添加到“所有引用位置”的窗口内。清空编辑框，再在该框内的输入“Sheet2!$G$17”单击

按钮，将本次输入添加到“所有引用位置”的窗口中。此时，所有引用位置”的窗口中应出现两个单元格位置。如果该窗口中出现不正确的行，应选中它，按

按钮将它们删除。重复本步骤将正确的内容输入。输入后的结果见图7.22所示。任务四

数据汇总中的单元格引用和链接按

按钮后，该项数据汇总起来，汇总结果.生成D4的一个简便的方法是在单元格中输入计算公式“=C4/B4”。生成E4的过程与生成C4的过程类似，只是在“合并计算”对话框中引用的单元格地址不同而已，见图7.24所示。生成F4的方法也是在单元格中输入计算公式“=E4/B4”。生成G4的一个简便的方法是在单元格中输入计算公式“=C4+E4”。图7.25是单元格区域（C4：G4）中数据的生成结果。任务四

数据汇总中的单元格引用和链接（二）创建指向源数据的链接在主工作表上为单元格C4和E4建立指向源工作表的链接，旨在使这两个单元格随着源工作表数据的改变而改变。任务四

数据汇总中的单元格引用和链接【步骤解析】选择Sheet4中的“企业员工收入分析表”中的单元格C4，以便建立公司全员工资收入数据的链接。打开【数据】选项卡，选择【数据工具】组中的合并计算按钮

，打开【合并计算】对话框。在该对话框中，我们已经选择了【求和】函数，并在所有引用位置”窗口中定义了两个源表的单元格引用位置：Sheet1!$H$17和Sheet2!$H$17”,若用户认为这里的显示内容需要修改的话，可以在此进行删除或添加。任务四

数据汇总中的单元格引用和链接当两个源表的单元格引用地置全部添加完成后，再在窗口下部的复选框中勾选【创建指向源数据的链接】。选择后的情况如图11.27所示。按

按钮后，该项数据的链接就完成了。仿照上述步骤，为单元格E4建立相应的链接。任务五

删除分类汇总如果用户不需要汇总数据时，可以将汇总行从工作表中清除。清除汇