搜索
692第34章分级显示和合并计算Excel的工作表分级显示功能提供了类似目录树的显示效果它可以根据层次的需要显示不同级别的数据用户可以根据需要展开某个级别查看该级别下的明细数据也可以收缩某个级别只查看该级别的汇总数据合并计算是Excel的另一重要功能它可以把多个工作表的数据按字段项目进行自动汇总计算特别在报表合并汇总数据时凸显其魅力例如一个公司可能有很多的分公司各个分公司具有各自的销售报表和会计报表为了对整个公司的情况进行全面了解就要将这些分散的数据进行合并从而得到完整的报表本章学习要点要点1：分级显示概述。要点2：创建工作表的分级显示。要点3：对同一工作簿多个工作表的合并计算。要点4：对不同工作簿工作表的合并计算。要点5：为合并计算数据区域添加、删除或修改源区域引用。34.1 分级显示分级显示能够将一个明细数据表中的数据按类别组合在一起通过鼠标单击分级显示按钮可迅速地设定只显示数据表格中那些提供汇总或标题的行或列也可使用分级显示按钮来查看单个汇总和标题下的明细数据比如一个银行的报表总行只想查看各省分行的汇总数据省分行只想查看各市分支行的汇总数据而不想查看其他下几级支行或分理处的明细数据此时使用分级显示就可以轻松实现34.1.1 分级显示概述示例34.1按地区、省份和季度分级显示数据表图34-1展示了一份已建立分级显示的数据表该数据表是各地区各省上半年每季度的汇总数据以及各省每个月的明细数据如果希望隐藏各省明细数据仅显示各地区的汇总数据可用鼠标单击行分级显示按钮中的效果如图34-2所示此时各省的明细数据行已全部隐藏用户可以更直观地看到各地区每个月的汇总数据如果用户只想查看华北地区第一季度明细可在图34-2中的表格单击华北地区左侧的分级显示按钮再单击第二季度正上方的分级显示按钮效果如图34-3所示69334.1 分级显示Excel第章34图34-1 分级显示后的数据表图34-2 显示各地区汇总数据图34-3 显示华北地区第一季度明细数据用户可根据需要单击行列的分级显示按钮显示不同级别下的数据也可以单击行列的分级显示按钮进行任意组合以查看局部数据不同组合显示不同的查看效果34.1.2 建立分级显示为数据表格建立分级显示有自动建立手动建立和对数据进行分类汇总3种方法694第34章分级显示和合并计算Excel第章341．自动建立分级显示自动建立分级显示对数据表格的要求较高如果表格具备以下特征用户可以使用自动分级显示1同一组中的行或列均放在一起如图34-1把属于各地区的省份的行都放在一起2汇总行均在每组数据的上方或下方汇总列均在每组数据的左侧或右侧汇总行和汇总列使用求和公式SUM或分类汇总公式SUBTOTAL引用数据中的单元格在图34-1所示的表格中单元格C7公式是=SUM(C2:C6)单元格F2公式是=SUM(C2:E2)单元格K2的公式是=F2+J2示例34.2自动建立分级显示图34-4展示了图34-1中的数据表在未建立分级显示前的状态各汇总行列已用求和公式SUM引用了各求和单元格要自动建立分级显示可按以下操作步骤执行图34-4 中国各地区各省份明细数据表步骤1步骤2如果工作表已有分级显示Excel将弹出如图34-5所示的对话框请单击确定按钮Excel会用新的分级显示替换掉原有的分级显示图34-5 是否修改现有分级显示对话框通过上述操作后Excel将自动根据用户小计行或列合计行或列中的公式来判断如何分级并建立工作表分级显示69534.1 分级显示Excel第章34若用户只需要建立行的分级显示请在步骤1选定单元格区域A1:C39再执行步骤2结果如图34-6所示图34-6 只建立行分级显示的数据表若要对建立的分级显示应用样式步骤2操作如下单击菜单数据→组及分级显示→设置Excel会自动弹出设置对话框根据数据汇总行列的方位决定是否勾选明细数据的下方和明细数据的右侧复选按钮再勾选自动设置样式复选按钮如图34-7所示最后单击创建按钮建立后的效果如图34-8所示图34-7 分级显示“设置”对话框图34-8 应用样式的分级显示696第34章分级显示和合并计算Excel第章34应用样式后所有的小计行第2级别都使用斜体字形字号为Excel默认的12号而不是用户原来设置的字号本例设置为10号所有的合计行第1级别字体都加粗字号与小计行相同2．手动建立分级显示要手动建立分级显示要求数据中同一组中的行或列均放在一起汇总行均在本组数据的上方或下方汇总列均在本组数据的左侧或右侧汇总行列中不要求使用公式注意示例34.3手动建立分级显示以图34-4所示的数据表为例要手动建立分级显示步骤如下步骤1步骤2步骤3步骤4通过上述操作后就建立了如图34-6所示的行分级显示如果还要对列进行分级显示可用同样的方法对列进行组合注意3．利用分类汇总建立分级显示当用户选定单元格区域执行菜单数据→分类汇总命令后Excel会自动使用SUBTOTAL函数插入分类求和公式并自动根据汇总字段建立行向分级显示有关分类汇总的具体操作步骤请参阅26.6节34.1.3 清除分级显示当用户不需要分级显示时可清除分级显示清除分级显示不会改变任何数据操作步骤如下69734.2 合并计算Excel第章34步骤1步骤2通过以上操作后分级显示就会自动清除注意分级显示的深入理解图34-9 工具中的“选项”对话框34.2 合并计算在Excel中可以通过合并计算功能来汇总一个或多个源区域中的数据具体有两种方法一是通过位置即当源区域有相同位置的数据汇总二是通过分类当源区域没有相同的布局时则采用分类方式进行汇总合并计算的源区域可以是同一工作簿中的多个工作表也可以是多个不同工作簿中的工作表深入了解Excel第章34698第34章分级显示和合并计算Excel第章3434.2.1 对同一工作簿中多个工作表的合并计算假设在汇总公司员工全年工资时由于每个月都可能发生员工入职或离职而且排序也不完全一样因此用户必须通过分类方式进行合并计算示例34.4合并计算员工年度工资图34-10和图34-11展示了某公司1年中每个月份的工资总额每个月份的数据都保存在单独的工作表中而且格式相同A列为该月份在职员工姓名B列为员工在该月份的工资总额公司必须计算出每位员工全年的工资总额以确定全年应缴交社保的月数和基数为了合并计算方便用户需先把每个月的工资总额列的标题改为该月份如1月2月等图34-10 公司员工1月份工资总额图34-11 公司员工2月份工资总额69934.2 合并计算Excel第章34第章具体操作步骤如下步骤1图34-12 为合并计算准备的汇总表步骤2图34-13 “合并计算”对话框步骤3步骤4步骤5步骤6700第34章分级显示和合并计算Excel第章34通过此操作后合并计算就完成了结果如图34-14所示全年所有在职过的员工都排在A列每个月的工资总额也全部排列在各月份下如果某月份没有该员工的记录则该单元格为空如最后一行的苏永中只有1月份有工资因为该员工2月份已离职只领了一个月工资如第28行的刘彬因该员工6月份才入职所以从6月份起才有工资记录图34-14 合并计算列出每位员工每个月工资步骤7图34-15 员工应缴费月数和基数70134.2 合并计算Excel第章34如果不需要计算应缴费月数可把各月份工作表的B1单元格都改为同样的内容如工资总额汇总表A1和B1分别输入姓名和工资总额再按步骤2步骤6操作结果如图34-16所示图34-16 员工年度应缴费基数34.2.2 对不同工作簿工作表的合并计算分公司往往将各自的报表保存在单独的工作簿文件上报送到总公司此时总公司必须将各分公司的报表合并计算才能形成总公司的报表这就需要对多个工作簿的工作表进行合并计算示例34.5汇总分公司报表图34-17和图34-18分别是济南和青岛分公司报送的报表图34-19是总公司要汇总的报表因为报表的布局完全一样用户可以通过位置来合并计算数据图34-17 济南分公司报表图34-18 青岛分公司报表702第34章分级显示和合并计算Excel第章34图34-19 汇总前的总公司报表步骤1步骤2图34-20 “浏览”对话框步骤3图34-21 浏览后的“合并计算”对话框步骤470334.2 合并计算Excel第章34图34-22 不输入完整路径的“合并计算”对话框图34-23 输入完整路径的“合并计算”对话框步骤5步骤6步骤7图34-24 添加了所有引用位置的“合并计算”对话框图34-25 合并计算后的结果34.2.3 自动更新合并计算的数据创建合并计算后用户可以利用链接功能来实现计算的自动更新这样当源数据改变时Excel会自动更新合并计算表格中的结果要实现该功能需在创建合并计算时在合并计算对话框中勾选创建连至源数据的链接复选框如图34-26所示如此合并计算表格中将插入外部引用公式链接到源数据区域并自动建立分级显示图34-26 为合并计算创建至源数据的链接704第34章分级显示和合并计算Excel第章34注意34.2.4 为合并计算添加、删除或修改源区域引用对于一个建立合并计算的工作表文件用户还可以进一步编辑以添加删除或修改对源区域的引用注意1．添加源区域引用仍以汇总公司报表为例如果总公司新收取南京分公司报送的报表则需要把此报表的数据也汇总到汇总表中示例34.6为合并计算汇总表添加分公司报表图34-27显示的是南京分公司报送的报表图34-27 南京分公司报表步骤1步骤2图34-28 添加源区域的“合并计算”对话框70534.2 合并计算Excel第章34如果南京分公司报表不在打开状态重复上例中步骤2步骤5再单击确定按钮计算结果如所示图34-29 添加源区域后的计算结果2．删除或修改源区域引用合并计算引用的源区域也可以被删除或修改（1）删除一个源区域引用步骤1步骤2步骤3注意（2）修改一个源区域引用步骤1步骤2步骤3步骤4也可以直接把原有引用源删除再添加新的引用源