EXCEL分级显示和合并计算

692 第 34章 分级显示和合并计算 Excel 的工作分级显示功能提供了类似目录树的显示效果，它可以根据层次的需要显示不同 级别的数据。用户可以根据需要展开某个级别，查看该级别下的明细数据，也可以收缩某个级别， 只查看该级别的汇总数据。 合并计算是 Excel 的另一重要功能，它可以把多个工作表的数据，按字段、项目进行自动汇 总计算，特别在报表合并、汇总数据时凸显其魅力。例如，一个公司可能有很多的分公司，各个 分公司具有各自的销售报表和会计报表，为了对整个公司的情况进行全面了解，就要将这些分散 的数据进行合并，从而得到完整的报表。 本章学习要点： 要点 1： 分级显示概述。 要点 2： 创建工作表的分级显示。 要点 3： 对同一工作簿多个工作表的合并计算。 要点 4： 对不同工作簿工作表的合并计算。 要点 5： 为合并计算数据区域添加、删除或修改源区域引用。 34.1　分级显示 分级显示能够将一个明细数据表中的数据按类别组合在一起，通过鼠标单击分级显示按钮 、 、 ，可迅速地设定只显示数据中那些提供汇总或标题的行或列，也可使用分 级显示按钮 、 来查看单个汇总和标题下的明细数据。比如一个银行的报表，总行只想查看 各省分行的汇总数据，省分行只想查看各市分（支）行的汇总数据，而不想查看其他下几级支行 或分理处的明细数据，此时使用分级显示就可以轻松实现。 34.1.1　分级显示概述 示例 34.1 按地区、省份和季度分级显示数据表 图 34-1 展示了一份已建立分级显示的数据表。该数据表是各地区、各省上半年每季度的汇 总数据，以及各省每个月的明细数据。 如果希望隐藏各省明细数据，仅显示各地区的汇总数据，可用鼠标单击行分级显示按钮中的 ，效果如图 34-2 所示。此时，各省的明细数据行已全部隐藏，用户可以更直观地看到各地区 每个月的汇总数据。 如果用户只想查看“华北地区”第一季度明细，可在图 34-2 中的表格单击“华北地区” 左侧的分级显示按钮 ，再单击“第二季度”正上方的分级显示按钮 ，效果如图 34-3 所示。 693 34.1　分级显示 E x c e l 第 章 34 图 34-1　分级显示后的数据表 图 34-2　显示各地区汇总数据 图 34-3　显示华北地区第一季度明细数据 用户可根据需要单击行、列的分级显示按钮 ，显示不同级别下的数据，也可以单击行、 列的分级显示按钮 、 进行任意组合，以查看局部数据，不同组合显示不同的查看效果。 34.1.2　建立分级显示 为数据表格建立分级显示，有自动建立、手动建立和对数据进行分类汇总 3种。 694 第 34 章 分级显示和合并计算 E x c e l 第 章 34 1．自动建立分级显示 自动建立分级显示对数据表格的要求较高，如果表格具备以下特征，用户可以使用自动分级 显示。 （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 选定需要分级显示的单元格区域A1:K39，若要对整个工作表的数据区域分级显示， 可以选定任意单元格。 步 骤2 单击菜单“数据”→“组及分级显示” →“自动建立分级显示”。 如果工作表已有分级显示，Excel 将弹出如图 34-5 所示的对话框，请单击“确定”按钮， Excel 会用新的分级显示替换掉原有的分级显示。 图 34-5　是否修改现有分级显示对话框 通过上述操作后，Excel 将自动根据用户小计行或列、合计行或列中的公式来判断如何分级， 并建立工作表分级显示。 695 34.1　分级显示 E x c e l 第 章 34 若用户只需要建立行的分级显示，请在步骤 1 选定单元格区域 A1:C39，再执行步骤 2，结果 如图 34-6 所示。 图 34-6　只建立行分级显示的数据表 若要对建立的分级显示应用样式，步骤 2操作如下： 单击菜单“数据”→“组及分级显示” →“设置”，Excel 会自动弹出“设置”对话框，根据数 据汇总行、列的方位，决定是否勾选“明细数据的下方”和“明细数据的右侧”复选按钮，再勾选“自 动设置样式”复选按钮，如图34-7所示。最后单击“创建”按钮，建立后的效果如图34-8所示。 图 34-7　分级显示“设置”对话框 图 34-8　应用样式的分级显示 696 第 34 章 分级显示和合并计算 E x c e l 第 章 34 应用样式后，所有的小计行（第 2 级别）都使用斜体字形，字号为 Excel 默认的 12 号，而不 是用户原来设置的字号（本例设置为 10 号），所有的合计行（第 1 级别）字体都加粗，字号与小 计行相同。 2．手动建立分级显示 要手动建立分级显示，要求数据中同一组中的行或列均放在一起，汇总行均在本组数据的上 方或下方，汇总列均在本组数据的左侧或右侧，汇总行、列中不要求使用公式。 每个汇总行在其所在组中的位置必须一致，即要么都在上方，要么都在下方。汇总 列亦同。注意 示例 34.3 手动建立分级显示 以图 34-4 所示的数据表为例，要手动建立分级显示步骤如下： 步 骤1 把光标定位于行号处，选定华北地区省份所在的第2～6行（不含小计行）。 步 骤2 单击菜单“数据”→“组及分级显示” →“组合”。 步 骤3 用同样的方法对其他地区的行进行组合。 步 骤4 选定所有地区的行，不含合计行，即第2～38行，单击菜单“数据”→“组及分级显示” →“组合”。 通过上述操作后，就建立了如图 34-6 所示的行分级显示，如果还要对列进行分级显示，可 用同样的方法对列进行组合。 对行进行组合时，不能把汇总行（如本示例中的小计、合计）与明细数据行一起选 定进行组合。注意 3．利用分类汇总建立分级显示 当用户选定单元格区域，执行菜单“数据”→“分类汇总”命令后。Excel 会自动使用 SUBTOTAL 函数插入分类求和公式，并自动根据汇总字段，建立行向分级显示。有关分类汇总的 具体操作步骤，请参阅 26.6 节。 34.1.3　清除分级显示 当用户不需要分级显示时，可清除分级显示。清除分级显示不会改变任何数据。操作步骤 如下： 697 34.2　合并计算 E x c e l 第 章 34 步 骤1 单击分级显示按钮 中的最大数字，以显示所有明细数据。 步 骤2 选定数据区域内任意单元格，单击菜单“数据”→“组及分级显示” →“清除分 级显示”。 通过以上操作后，分级显示就会自动清除。 建立分级显示和清除分级显示都不能使用“撤消”按钮来撤消，在操作前，请慎重 考虑是否建立或清除。注意 分级显示的深入理解 （1）一个工作表只能有一个分级显示。 （2）分级显示最多只能有8个级别的明细数据，每个内部级别为前面的外部级别提供明细 数据。如图34-6所示，“总计”行为第1级别，各地区的汇总行为第2级别，其他数据为第3 级别。若要显示某个级别的行，可在分级显示按钮 中单击想要查看的级别对应的数字。 （3）可以在不删除整个分级显示的情况下取消分级显示中某些部分的组合。操作方法为： 按住键，单击该组的分级显示按钮 或 ，再单击菜单“数据”→“组及分级显示” →“取消组合”。 （4）若要在不删除分级显示的情况下隐藏分级显示，可单击菜单“工具”→“选项”，再单 击“视图”选项卡，在“窗口选项”中，取消“分级显示符号”复选框的勾选，如图34-9所示。 图 34-9　工具中的“选项”对话框 34.2　合并计算 在 Excel 中可以通过合并计算功能来汇总一个或多个源区域中的数据，具体有两种方法。一 是通过位置，即当源区域有相同位置的数据汇总。二是通过分类，当源区域没有相同的布局时， 则采用分类方式进行汇总。 合并计算的源区域可以是同一工作簿中的多个工作表，也可以是多个不同工作簿中的工作表。 深 入 了 解 E x c e l 第 章 34 698 第 34 章 分级显示和合并计算 E x c e l 第 章 34 34.2.1　对同一工作簿中多个工作表的合并计算 假设在汇总公司员工全年工资时，由于每个月都可能发生员工入职或离职，而且排序也不完 全一样，因此，用户必须通过分类方式进行合并计算。 示例 34.4 合并计算员工年度工资 图 34-10 和图 34-11 展示了某公司 1 年中每个月份的工资总额。每个月份的数据都保存在 单独的工作表中，而且格式相同。A 列为该月份在职员工姓名，B 列为员工在该月份的工资总额。 公司必须计算出每位员工全年的工资总额，以确定全年应缴交社保的月数和基数。为了合并计算 方便，用户需先把每个月的工资总额列的标题改为该月份，如 1月、2 月等。 图 34-10　公司员工 1 月份工资总额 图 34-11　公司员工 2 月份工资总额 699 34.2　合并计算 E x c e l 第 章 34 第 章 具体操作步骤如下： 步 骤1 在汇总表第一行输入如图34-12所示的内容。 图 34-12　为合并计算准备的汇总表 步 骤2 选定A1:M1（也可以整行选定），单击菜单“数据”→“合并计算”，Excel弹出“合 并计算”对话框，在“函数”下拉列表框中选择“求和”，如图34-13所示。 图 34-13　“合并计算”对话框 步 骤3 单击“引用位置”框，接着单击1月工作表标签以激活1月工作表，鼠标指向列 标处以选定A:B列，“引用位置”框中会自动输入“'1月”，单击“添加”按钮，“所 有引用位置”就自动添加一条

记录

混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载

“'1月”。 步 骤4 单击2月工作表标签以激活2月工作表，“引用位置”框中自动显示“'2月”（若 显示的区域不正确，则需要手动选择正确区域），再单击“添加”按钮。 步 骤5 用同样的方法，添加其他月份的引用位置。 步 骤6 勾选“首行”和“最左列”复选框，如果需要创建连至源数据的链接，则勾选“创 建连至源数据的链接”复选框，最后单击“确定”按钮。 700 第 34 章 分级显示和合并计算 E x c e l 第 章 34 通过此操作后，合并计算就完成了，结果如图 34-14 所示。全年所有在职过的员 工都排在 A 列，每个月的工资总额也全部排列在各月份下，如果某月份没有该员工的 记录，则该单元格为空。如最后一行的“苏永中”，只有 1 月份有工资，因为该员工 2 月份已离职，只领了一个月工资；如第 28 行的“刘彬”，因该员工 6 月份才入职，所 以从 6月份起才有工资记录。 图 34-14　合并计算列出每位员工每个月工资 步 骤7 在N2输入公式“=COUNT(B2:M2)”，在O2输入公式“=SUM(B2:M2)”，再选定 N2:O2，把公式下拖复制到第30行，结果如图34-15所示，即计算出每位员工的 缴费月数和缴费基数。 图 34-15　员工应缴费月数和基数 701 34.2　合并计算 E x c e l 第 章 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 章 分级显示和合并计算 E x c e l 第 章 34 图 34-19　汇总前的总公司报表 步 骤1 打开图34-19的汇总报表文件，选定要汇总区域的左上角单元格B3，单击菜单“数 据”→“合并计算”，Excel弹出“合并计算”对话框，在“函数”下拉列表框中 选择“求和”。 步 骤2 单击“浏览”按钮，Excel弹出“浏览”对话框，在“查找范围”下拉列表框中， 定位到济南分公司报表文件所在的文件夹，如图34-20所示。 图 34-20　“浏览”对话框 步 骤3 单击“济南.xls”文件图标，再单击“确定”按钮，Excel自动关闭“浏览”对话框， 并在“合并计算”对话框的“引用位置”中输入完整路径及文件名，后面附加符号“!” （'H:\报表\济南.xls'!），如图34-21所示。 图 34-21　浏览后的“合并计算”对话框 步 骤4 修改 “引用位置”框中的内容为：[文件名]＋工作表名＋英文感叹号（!）＋引用 的单元格地址或名称，如图34-22所示。也可修改为包含完整路径：完整路径＋[文 件名]＋工作表名＋英文感叹号（!）＋单元格地址或名称，如图34-23所示。 703 34.2　合并计算 E x c e l 第 章 34 图 34-22　不输入完整路径的“合并计算”对话框 图 34-23　输入完整路径的“合并计算”对话框 步 骤5 单击“添加”按钮，“引用位置”的字符串将被添加到“所有引用位置”列表框中。 步 骤6 重复步骤2～步骤5，将青岛分公司的数据来源也添加到“所有引用位置”列表框中， 如图34-24所示。 步 骤7 单击“确定”按钮，济南和青岛分公司的报表将被合并到汇总表中，如图34-25所示。 图 34-24　添加了所有引用位置的“合并计算”对话框 图 34-25　合并计算后的结果 34.2.3　自动更新合并计算的数据 创建合并计算后，用户可以利用链接功能来实现计算的自动更新，这样当源数据改变时， Excel 会自动更新合并计算表格中的结果。要实现该功能，需在创建合并计算时在“合并计算”对 话框中勾选“创建连至源数据的链接”复选框，如图 34-26 所示。如此，合并计算表格中将插入 外部引用公式、链接到源数据区域并自动建立分级显示。 图 34-26　为合并计算创建至源数据的链接 704 第 34 章 分级显示和合并计算 E x c e l 第 章 34 当源和目标区域在同一张工作表时，则无法建立这种链接。注意 34.2.4　为合并计算添加、删除或修改源区域引用 对于一个建立合并计算的工作表文件，用户还可以进一步编辑，以添加、删除或修改对源区 域的引用。 这些操作仅适用于合并计算表格没有建立与源区域的链接的情况下，否则必须先删 除合并计算表格中的数据，需要的话还要清除分级显示。注意 1．添加源区域引用 仍以汇总公司报表为例，如果总公司新收取南京分公司报送的报表，则需要把此报表的数据 也汇总到汇总表中。 示例 34.6 为合并计算汇总表添加分公司报表 图 34-27 显示的是南京分公司报送的报表。 图 34-27　南京分公司报表 步 骤1 选定总公司报表的B3单元格，单击菜单“数据”→“合并计算”，Excel弹出“合 并计算”对话框。 步 骤2 如果南京分公司报表处于打开状态，单击“引用位置”框，在任务栏单击南京分 公司的文件图标，以激活南京分公司的工作簿，选定Sheet1工作表的单元格区域 B3:D6，单击“添加”按钮，如图34-28所示，最后单击“确定”按钮。Excel将 重新计算“所有引用位置”框中的数据区域。 图 34-28　添加源区域的“合并计算”对话框 705 34.2　合并计算 E x c e l 第 章 34 如果南京分公司报表不在打开状态，重复上例中步骤 2～步骤 5，再单击“确定”按钮。 计算结果如所示。 图 34-29　添加源区域后的计算结果 2．删除或修改源区域引用 合并计算引用的源区域，也可以被删除或修改。 （1）删除一个源区域引用 步 骤1 选定合并计算表格中目标区域左上角第1个单元格，单击菜单“数据”→“合并计算” 命令。 步 骤2 在“合并计算”对话框的“所有引用位置”框中选定想要删除的源区域。 步 骤3 单击“删除”按钮，最后单击“确定”按钮，利用新的源区域来重新合并计算。 源区域引用被删除后，不可使用“撤消”按钮来撤消删除，只能重新添加。注意 （2）修改一个源区域引用 步 骤1 选定合并计算表格中目标区域左上角第1个单元格，单击菜单“数据”→“合并计算” 命令。 步 骤2 在“合并计算”对话框的“所有引用位置”框中选定想要修改的源区域。 步 骤3 在“引用位置”框中修改引用源区域，单击“添加”按钮。 步 骤4 删除原有引用源区域，利用新的源区域来重新合并计算，单击“确定”按钮。 也可以直接把原有引用源删除，再添加新的引用源。