如何使用Excel合并计算数据

6.4.2.1　合并计算的基本概念

一个公司内可能有非常多的销售地区或分公司，各个分公司具有各自的销售报表和会计报表，为了对整个公司的所有情况进行全方面的了解，就要将这些分散的数据进行合并，从而得到一份完整的销售统计报表或会计报表。Excel提供了合并计算的功能，可以完成这些汇总工作。

所谓合并计算是指，能通过合并计算的方法来汇总一个或多个源区中的数据。Microsoft Excel 提供了两种合并计算数据的方法。一是通过位置，即我们的源区域有相同位置的数据汇总。二是通过分类，当我们的源区域没有相同的布局时，则采用分类方式进行汇总。此外，我们也常用SUM、SUMIF和COUNTIF等函数来实现多表的合并（表6-2）。

表6-2　Excel合计计算函数

要想合并计算数据，首先必须为汇总信息定义一个目的区，用来显示摘录的信息。此目标区域可位于和源数据相同的工作表上，或在另一个工作表上或工作簿内。其次，需要选择要合并计算的数据源。此数据源能来自单个工作表、多个工作表或多重工作簿中。最后，合并计算不意味着简单的汇总求和，Excel的合并计算还包括求均值、计数、求标准差等运算，如表6-2所示。在“合并计算”的对话框中可以选定不同的合并计算汇总函数。

6.4.2.2　利用SUM函数实现多表数据合并

进行多表数据合并，最好理解的方法就是通过公式的“三维应用”来实现，即使用SUM等函数将来自不同工作表乃至其他不同工作簿的数据进行跨工作表引用。

【例6-21】　如图6-28所示，图中标签名分别为“一月”到“五月”的工作表的数据结构与本月汇总工作表相同。它们分别用来保存整个月份中各个日期的产品数据，“本月汇总”是对它们进行的合并汇总。并且，随着时间的推移，图中“空白表”前可以不断地增加工作表，如“六月”“七月”……，“本月汇总”中的数字也将自动更新。

图6-28　利用SUM对多张具有相同格式的工作表进行合并汇总

该例中“本月汇总”工作表的合并汇总，可以通过SUM函数实现，其操作过程如下。

Step 1：按照前面月份工作表相同的格式，建立“本年累计”表的结构框架。

Step 2：将光标定位于“本年累计”工作表的B2单元格→在编辑栏中输入公式“=SUM（）”→将光标定位于两个括号中间→单击“一月”工作表标签→按下Shift→单击“空白表”工作表标签→单击任意月份工作表或“空白表”的B2单元格。

Step 3：回到编辑栏，可以看到编辑栏中的公式为“=SUM（一月:空白表!B2）”。单击【确认】按钮，在“本年累计”工作表中的B2单元格中的对应汇总被汇总出来。

Step 4：将“本年累计”工作表的B2单元格向下和向右拖动，使得B2:G17中显示出所有合并汇总结果。

以下三点需要说明。

①公式“”中的符号“’”是Excel自动添加的，该公式表示将从“一月”一直到“空白表”之间的所有工作表B2单元格的数据进行求和。

②该例中的“空白表”工作表在这里是一个辅助表，其目的在于，当在它前面插入新的工作表后，该公式的合并范围自动扩展，从而使合并汇总结果总保持最新状态。否则，如果将上面的公式改为“=SUM（一月:五月!B2）”，当在“五月”工作表增加“六月”“七月”……时，“本月汇总”的数据无法自动更新。

③上面利用SUM函数进行多表汇总的方法，只适用于多张工作表具有相同格式的情况；如果需要汇总的表格格式不完全一样时，可以利用SUMIF或COUNTIF函数来实现。

6.4.2.3　通过位置来合并计算数据

通过位置来合并计算数据是指：在所有源区域中的数据被相同地排列，也就是说想从每一个源区域中合并计算的数值必须在被选定源区域的相同的相对位置上。这种方式非常适用于我们处理日常相同表格的合并工作，例如，总公司将各分公司的报表合并形成一个总公司的报表。再如，税务部门能将下级不同地区的税务报表合并形成一个总税务报表等等。

【例6-22】　图6-29是某集团公司五个分公司各自的月份经营数据，分别保存在“甲公司”“乙公司”等工作表中，这些表格都有着与图中可见的“甲公司”表示数据区域完全相同的行标题和列标题，顺序也完全一样。现在到了年末，集团公司需要将这些分公司数据进行合并汇总，以便了解集团的整体运行情况。其操作过程如下。

图6-29　需要合并计算的分公司工作表

Step 1：在“第五分公司”工作表后插入一个新的工作表，并将之命名为“集团合计”，并按照“第一分公司”工作表完全相同的位置和顺序，复制得到“集团合计”工作表的行列标题。

Step 2：选取“集团合计”工作表中的B2:G13单元格区域→【数据】→【合并计算】，弹出【合并计算】对话框→函数用默认的“求和”→将光标置于【引用位置】文本框中→用鼠标选取“第一分公司”工作表的B2:G13单元格区域，使其出现到“引用位置”文本框中→【添加】，则“合并计算”对话框中第一个引用位置添加完成（图6-30）。

Step 3：照此类推，将“第二分公司”到“第五分公司”工作表中的数据依次添加到“合并计算”的引用位置，最后效果如图6-31所示。

图6-30　添加第一个引用位置(www.xing528.com)

图6-31　五个引用位置全部添加后的效果

Step 4：单击【确定】按钮，五张表格的汇总结果即被求出。

注意：

①【标签位置】下的任何复选框都不要选中，它们只适用于后面的分类合并计算；

②按位置进行合并计算时，各工作表中行标题与列标题的位置与顺序必须一致。

对于合并计算，我们还能将保存在不同工作簿中的工作表进行合并操作，其操作步骤是在我们执行上述步骤2时，如果需要的工作表没有被打开，选择【浏览】→从浏览对话框中选定包含源区域的工作簿→点击【确定】→键入单元格引用或源区域的名字，其他操作同上。

注意：

在按位置进行合并时，Excel只是将所引用数据相同位置上的数据进行合并计算，而不考虑其行列标题内容是否相同。这种合并计算常用于源数据表的数据按相同的顺序排列并使用相同的行列标签。否则，计算结果会有错误。

6.4.2.4　按分类进行合并计算

通过分类来合并计算数据是指：当多重来源区域包含相似的数据（即相同的行标题或列标题）却以不同方式排列时（比如位置或顺序不同），或者分类略有差异，此方法可使用标记，依不同分类进行数据的合并计算。即是说，当选定格式的表格具有不同的内容时，我们能根据这些表格的分类来分别进行合并工作。

【例6-23】　图6-32显示了某机械制造集团公司五个分公司各自的管理费用，为了了解整个集团管理费用构成情况，现在需要对五张表格数据进行合并计算。从图上可看出，这五张表的数据行标题的内容是一样的，但顺序不同，类别也各有差异（有些费用其他公司没有）。所以，不能直接进行按位置的“合并计算”，而只能进行按分类的“合并计算”。其操作步骤如下。

图6-32　需要进行合并汇总5张工作表的结构

Step 1：在“第五分公司”工作表后插入一个新的工作表“集团合计”。

Step 2：将光标定位到“集团合计”工作表的A1单元格→【数据】→【合并计算】→在弹出的对话框中【函数】选择求和→将光标置于【引用位置】文本框中→用鼠标选取“第一分公司”工作表的A1:B16单元格区域，使其出现到“引用位置”文本框中→【添加】，同法选取“第二分公司”到“第五分公司”的数据区域并添加到各个引用位置，最终结果如图6-33所示。

Step 3：将【标签位置】下的【首行】和【最左列】复选框选中。如用户需要在合计表中查看明细，还可以勾选【创建指向源数据的链接】→单击【确定】。5张表格的合并汇总结果被求出，如图6-34所示。

图6-33　在【合并计算】对话框中添加位置

图6-34　分类【合并计算】结果

注意：

①图6-33中的【位置标签】下的【最左列】筛选框一定要选中。如有两个及以上的行标签，【首行】也必须选中。这样，才能实现将各个【引用位置】区域中标题相同的单元格后面的对应数据进行合并汇总。

②按分类进行合并计算时，各工作表中的行标题、列标题名称必须完全一样才会被合并到一起。如为英文或拼音，必须保证所有源区域中都以相同的拼写和大小写形式输入。

③【合并计算】还能利用链接功能来实现表格的自动更新。也就是说，如果我们希望当源数据改动时，Excel会自动更新合并计算表，在操作中只需在【合并计算】对话框中勾选【创建指向源数据的链接】复选框。这样，当更新源数据时，我们不必再执行一次【合并计算】。

④当源数据和目标区域在同一张工作表时，是不能够建立链接的。

注意：

①按分类进行合并计算要求数据源包含行列标题，在合并计算过程中还必须选中【合并计算】/【标签位置】分组框中相应的筛选框。

②合并计算不能复制源数据表的格式。