【1.3.3】Excel 数据合并计算

Excel 能对多个工作表和工作簿内相类似的数据进行合并计算,将合并的结果放到一张 合并工作表内。这一功能可以从下至上地进行报表汇总,如一个总公司下设多个销售区,每 个销售区又有多个门市部,那么可以先将各门市部的销售收入汇总成销售区的销售收入,再 把各个销售区的数据合并成总公司的销售收入;这一功能还可以按时间进行报表汇总,如统 计部门能将月度资料汇总成季度资料,再将其汇总成年度资料。这里的合并计算,不仅仅指 求和计算,还可以计算平均数、最大值、最小值等 11 种指标。

要合并的多张工作表,可以是标题排列顺序完全相同的表,也可以不完全相同。如果是 完全相同,Excel 按各工作表内对应位置对各单元格施行合并计算;如果是不完全相同,可 以要求 Excel 根据各个源工作表内数据的行、列标题来进行合并,合并计算那些有相同行和 相同列标题的单元格内的数据,而不管行、列标题在各个源工作表内的位置次序。

一、完全相同型的合并

对于各个要合并计算的工作表,不仅行、列标题完全一样,而且次序也完全一样,但各 个数据区的位置可以不一样。这种合并可以按照存放合并数据的目标工作表单元格或区域的 选取。 【例 3.3】现有宏大计算机公司某月上、中、下旬销售利润表,如图 3.10,要求汇总各 旬情况,完成月度销售利润表。

Excel 解决方案:

  1. 将表格的文字部分复制到另一个工作表中,并适当修改,如将“上旬”改为“月度”, 该表将存放汇总的结果。
  2. 选定一个目标区域,本例 B3:E11,即只选要显示合并数据的位置,注意文本、日期 等数据是不能参与合并的。
  3. 选取“数据”—“合并计算”,弹出“合并计算”对话框,见图 3.11。
  4. 在“函数”列表框内选取合并的计算方式,预设为求和,不改变时可以跳过。
  5. 在“引用位置”文本框内,通过选取或直接键入方式确定源区域。如果源区域位于 打开的工作表上,可以通过鼠标单击加拖动方式。
  6. 单击“添加”按钮,将“引用位置”文本框内的区域加入到“所有引用位置”列表 框内。
  7. 重复第五、六步,将所有源区域(可达 255 个)添加到“所有引用位置”列表框内。 8 注销“标记位置”复选钮组内的“首行”、“最左列”选择框,注销(也可选)“与源 数据链接”选择框。如果选中“与源数据链接”选择框,结果在目标工作表内生成分级 显示;最后单击“确定”按钮,完成汇总。见图 3.12。

特别注意: 在做报表汇总时,第一,要留足适当大空间给目标区域,否则合并计算结果要覆盖原有数据,造成数据丢失。第二,只有数值数据参与合并计算,文本和公式不能进行合 并计算。

二、不完全相同型的合并

当源工作表上数据的行列标题位置不完全相同时,要使用行、列标题进行合并计算。这 种方法最为灵活方便,容许每个源区域的实际位置不同。

【例 3.4】 某计算机总公司下属宏大公司、正大公司和光大公司 1-4 月的销售量统计报 表已上报,要求汇总出分品种、分月份的销售量,这三个公司销售品种是不同的。

Excel 解决方案:

  1. 新建一张工作表用以存放汇总的结果,将光标移至存放结果的左上角。
  2. 选取“数据”—“合并计算”,弹出“合并计算”对话框。
  3. 在“函数”列表框内选取合并的计算方式,预设为求和,不改变时可以跳过。
  4. 在“引用位置”文本框内,通过选取或直接键入方式确定源区域。如果源区域 位于打开的工作表上,可以通过鼠标单击加拖动方式。单击“增加”按钮,将“引 用位置”文本框内的区域加入到“所有引用位置”列表框内。
  5. 重复上一步,将所有源区域(可达 255 个)增加到“所有引用位置”列表框内。
  6. 在“标记位置”复选钮组选中“首行”、“最左列”选择框,注销(也可选)“与 源数据链接”选择框。见图 3.13。
  7. 最后单击“确定”按钮,生成汇总结果,见图 3.14 上方。
  8. 如果在第6步选中“与源数据链接”选择框,其结果如图 3.14 下方,它有两点 不同:一是 Excel 自动创建分级显示符号,它可以查看数据的来源;二是如果源数 据区域中的数据被修改,则汇总表的数据也随之改变,而前者不会发生变化。

参考资料

  • 《统计学》 南京财经大学 陈耀辉、王芳、王庚、韩中、张艳芳、黄莉芳
个人公众号,比较懒,很少更新,可以在上面提问题,如果回复不及时,可发邮件给我: tiehan@sina.cn

Sam avatar
About Sam
专注生物信息 专注转化医学