第8章 合并计算与分类汇总.doc

EXcel实用办公技巧（2010版）第 8 章 合并计算与分类汇总138.1 合并计算技巧1： 按位置合并计算如果数据的源区域中包含不同工作表的相似数据（如图8-1所示，三个工作表中包含不同地区的销售数据），要合并数据得到总计值，可以按位置合并计算。图8-1操 作 步 骤（1） 新建一张工作表，将其命名为“一季度统计”，在该工作表输入内容，如图8-2所示。新建工作表 图8-2（2） 选择B3:D6单元格区域，在“数据”选项卡的“数据工具”组中单击“合并计算”按钮，如图8-3所示，打开“合并计算”对话框。选择单元格区域 图8-3（3） 单击“引用位置”文本框右侧的拾取器按钮（如图8-所示），在“长沙”工作表中选择数据区域，如图8 -4所示。图8-图8-（4） 选择后再单击拾取器返回到“合并计算”对话框，单击“添加”按钮即可将引用位置添加至列表中，如图8-所示。 图8-4（5） 再用同样的方法引用“杭州”、“济南”工作表中的数据区域，如图8-5所示。添加后图8-5 （6） 单击“确定”按钮，合并数据到“一季度统计”的单元格区域B3:D6中，如图8-6所示。合并计算结果图8-6应 用 扩 展要删除合并计算中的源区域数据，操作方法很简单，只需打开“合并计算”对话框，在“所有引用位置”列表框中选择要删除的源区域引用，然后单击右侧的“删除”按钮，如图8-10所示，即可删除引用。选择图8-10技巧2： 按类别合并计算在日常工作中，有时需要合并多个表格获取汇总的数据。如图8-所示为一月份销售业绩，如图8-所示为二月份销售业绩，两个表格中姓名有相同的也有不同时，这时需要通过合并计算功能对两个表格数据汇总。 图8-操 作 步 骤（1） 选中方式汇总结果存放的单元格，如A2，在“数据”选项卡“数据工具”组中，单击“合并计算”按钮，如图8-所示，打开“合并计算”对话框。 图8-（2） 在“函数”中选中“求和”，单击“引用位置”右侧的拾取器，依次添加“一月份”与“二月份”两个表格中的数据源，然后勾选上“最左列”复选框，如图8-所示。图8-计算结果图8-（3） 单击“确定”按钮，即可得到合并计算的结果，如图8-所示。技巧3： 巧用合并计算统计数量在Excel中，使用合并计算功能能够达到统计的目的。下面以统计各种数码设备的销售数量为例，具体介绍。操 作 步 骤（1） 各种数码产品的名称和销售量统计如图8-15所示。产品销量统计 图8-15（2） 选择存放计算结果的单元格，在“数据”选项卡的“数据工具”组中单击“合并计算”按钮，打开“合并计算”对话框。（3） 单击“引用位置”右侧的拾取器，单击“Sheet1”工作表标签，并选择表中的数据区域，如图8-所示。（4） 选择后，再次单击拾取器返回对话框，并勾选“首行”和“最左列”复选框，如图8 -16所示。（5） 单击“确定”按钮，得到合并计算的结果，在“销售量”列中统计出各种数码产品的销售数量，如图8-17所示。选取数据源图8- 图8-16计算结果图8-17技巧4： 巧用合并计算商品平均价格如图8-所示是一家商店的学生用品报价表，如图8-所示是统计的另一家商店的学生商品报价。两家商店的价格不一样，而且统计的商品有重复的，也有不重复的，下面计算出两家商店各商品的平均价格。图8-图8-操 作 步 骤（1） 建立统计平均价格的工作表，选择存放计算结果的单元格，如A3单元格，在“数据”选项卡的“数据工具”组中单击“合并计算”按钮（如图8-所示），打开“合并计算”对话框。图8- （2） 在“函数”下拉列表中选择“平均值”（如图8-所示），将光标定位到“引用位置”编辑框，然后单击拾取器按钮，返回到“报价表1”选取数据源，如图8-所示。图8-选取数据源图8- （3） 再次单击拾取器按钮返回到“合并计算”对话框，单击“添加”按钮，即可将选取的数据源添加到“所有引用位置”列表框中，如图8-所示。图8-（4） 按照同样的方法选取“报价表2”的数据源，并添加到“所有引用位置”列表框；取消选中“首行”复选框，并选中“最左列”复选框，如图8-所示。（5） 单击“确定”按钮，即可计算出各商品两种报价的平均价格，如图8-所示。图8-图8-技巧5： 巧用合并计算统计重复次数在合并计算中，使用不同的函数可以得到不同的计算结果。如图8-所示的加班记录工作表，下面统计每个员工的加班次数，具体操作方法如下。图8-操 作 步 骤（1） 选择计算存放重复次数的单元格，并打开“合并计算”对话框。在“函数”下拉列表中选择“计数”选项，如图8-所示。（2） 单击“引用位置”右侧的拾取器，单击“Sheet1”工作表标签，并选择表中的数据区域，如图8-所示。图8-（3） 选择后再单击拾取器返回对话框，单击“添加”按钮，添加引用位置，并选中“最左列”复选框，如图8-所示。图8-18（4） 单击“确定”按钮，即统计出所有员工的加班次数，并且将E列的“日期”格式设置成“常规”，即可得到结果，如图8-19所示。计算的重复项图8-19若合并计算中的引用位置出现错误或需要重新添加引用位置，就必须将原来的引用位置删除，再重新添加。 专 家 点 拨技巧6： 设置自动更新数据源如果合并计算的数据源改变，那么合并计算的结果也需要更新。若结果不同自动更新，可按如下方法设置。操 作 步 骤（1） 单击“合并计算”按钮，打开“合并计算”对话框，设置函数和引用位置，然后选中“创建指向源数据的链接”复选框，如图8-20所示。 图8-20（2） 单击“确定”按钮后，将进行合并计算，并自动汇总数据，如果更改工作表中的数据，合并计算表中的数据将随之发生变化。8.2 分类汇总技巧7： 更改汇总的计算方式在进行分类汇总时，有几种汇总方式可以选择（求和、计数、最大值等），若默认汇总方式不满足需要，可以按照下面方法进行更改。操 作 步 骤（1） 例如如图8-所示的表格中按销售人员统计了金额总计值。图8-（2） 选择表格中任意单元格，单击“分类汇总”按钮，打开“分类汇总”对话框。单击“汇总方式”设置框右侧下拉按钮，在列表中单击选择“最大值”，如图8 -31所示。（3） 单击“确定”按钮，即可看到汇总结果为每位销售人员的单笔最高金额，如图8-32所示。 图8-31更改了汇总方式 图8-32技巧8： 创建多种统计结果的分类汇总分类汇总不但可以对一个字段进行汇总，还可以得出多种统计结果的分类汇总。如图8-所示，首先将商品名称按照“入库单价”进行最大值分类汇总，然后再按照“期初库存”进行最大值分类汇总。图8-操 作 步 骤（1） 选择表格编辑区域的任意单元格，在“数据”选项卡的“分级显示”组中单击“分类汇总”按钮（如图8-21所示），打开“分类汇总”对话框。图8-21（2） 在“分类字段”下拉列表中选择“名称”选项，在“汇总方式”下拉列表中选择“最大值”选项，在“选定汇总项”列表框中勾选“入库单价”复选框，如图8-22所示。图8-22（3） 单击“确定”按钮，即可得出汇总结果，如图8-23所示。（4） 再次打开“分类汇总”对话框，在“选定汇总项”列表框中再选中“期初库存”，并取消勾选“替换当前分类汇总”复选框，如图8-24所示。（5） 单击“确定”按钮，即可完成两种分类汇总的结果的统计，如图8-25所示。第一次汇总结果图8-23 图8-24多种分类汇总结果图8-25 专 家 点 拨1使用此方法可以对更多的选项进行汇总，关键是要在“分类汇总”对话框中取消选中“替换当前分类汇总”复选框。2对数据分类汇总前，需要按照某个字段对表格进行排序，才能进行汇总。技巧9： 创建多级分类汇总在Excel中，默认情况下，只能按工作表中的一个字段进行分类汇总，如果想要按两个或两个以上的字段进行分类汇总，可以创建多级分类汇总。如图8-所示的表格首先按类别进行分类汇总，再对同一类别下的不同的商品进行了分类汇总。图8-操 作 步 骤（1） 选择“类别”列任意单元格，对此列进行“升序”或“降序”排列，效果如图8-26所示。降序排列 图8-26（2） 在“数据”选项卡的“分级显示”组中单击“分类汇总”按钮，打开“分类汇总”对话框。设置“分类字段”为“类别”；“汇总方式”为“平均值”；“选定汇总项”为“本站价”，如图8-27所示。 图8-27 （3） 单击“确定”按钮，即可按照“类别”完成第一级汇总，如图8-28所示。第一级分类汇总图8-28 （4） 再次打开“分类汇总”对话框，将“分类字段”改为“商品”，其他保持不变，并取消选中“替换当前分类汇总”复选框，如图8-29所示。图8-29 （5） 单击“确定”按钮，即可完成第二级分类汇总。技巧10： 快速清除分级显示符号对表格进行分类汇总后，在左侧窗格中会有分级显示符号，如图8-33所示。如果不显示分级显示符号，可按如下方法操作。分级显示符号图8-33操 作 步 骤在“数据”选项卡的“分级显示”组中，单击“取消组合”下拉按钮，单击“消除分级显示”命令（如图8-34所示），即可清除分级显示符号，效果如图8-35所示。 图8-34分级显示符号被清除图8-35 专 家 点 拨若想再显示分级符号，再次打开“分类汇总”对话框，单击“确定”按钮即可显示。技巧11： 每页显示固定数量的汇总记录如果在打印分类汇总表格时，要想使每页只显示5条（可按实际需要自定义）数据记录，并且在每页的最下方显示汇总结果，可以在分类汇总时按如下步骤操作。操 作 步 骤（1） 打开“员工福利管理表”，在I3单元格中输入公式：=INT(ROW(A3)-3)/5)，按Enter键，得出结果，如图8-36所示。输入公式图8-36（2） 拖动填充柄向下填充公式，然后选择所有填充公式单元格，按“Ctrl+C”组合键进行复制，在原来位置单击鼠标右键，在右键菜单中单击“值”选项（如图8-所示），将公式结果转化为值。（3） 打开“分类汇总”对话框，在“分类字段”下拉列表中选择“（列I）”选项，在“汇总方式”下拉列表中选择“求和”选项，在“选定汇总项”列表框中勾选“合计”复选框，并勾选“每组数据分页”复选框，如图8-38所示。图8- 图8-38 （4） 单击“确定”按钮，得出分类结果，并且可以看到分页的效果，如图8-39所示。分页线图8-39（5） 将新增加的辅助列删除，再进行打印等其他操作即可。 专 家 点 拨在辅助列建立公式时，根据实际需要可以任意设置每页显示的记录数。关键在于对“=INT(ROW(A3)-3)/5)”此公式的设置。例如要每页显示6条记录，则将公式更改为“=INT(ROW(A3)-3)/6)”、要每页显示7条记录，则将公式更改为“=INT(ROW(A3)-3)/7)”，依次类推。技巧12： 只显示分类汇总的结果如果只想查看分类汇总的结果，可以通过下面的方法实现。操 作 步 骤（1） 单击分级显示符号窗格中的按钮，即可看到各部门的“合计”汇总，如图8-40所示。部门汇总图8-40（2） 单击按钮，即可看到所有部门“合计”的总和，如图8-41所示。所有汇总 图8-41应 用 扩 展许多用户在使用分类汇总以后，希望能够把汇总结果复制到其他工作表中去，但是在将汇总项的数据列表进行复制并粘贴到其他工作表中时，发现明细数据也被复制了。如果只需复制汇总结果，可按如下方法操作。单击工作表左上角的按钮，得到汇总项，选择A2：H30单元格区域，按F5键，弹出“定位”对话框，单击“定位条件”按钮（如图8-42所示），打开“定位条件”对话框，选择“可见单元格”单选框，如图8-