Excel技巧应用篇：合并计算的使用技巧

在实际工作中，经常会碰到需要将分月份、分地区或者分产品等制作的多张数据表格进行汇总的情况。有的同学可能会老老实实地用公式制作一张汇总表，实际上，使用Excel的“合并计算”功能就可以轻松完成多张数据表格的汇总等数据处理。

“合并计算”的主要功能是将多个区域的值合并到一个新区域，多个区域可以在一个工作表，也可以在一个工作薄的多个工作表，还可以分散在不同工作薄。我们用分散在一个工作簿不同工作表的多张数据表格为例介绍“合并计算”的使用方法。

1、基本用法

大家注意观察例子中的数据源，即工作表“Jan”、“Feb”和“Mar”中的数据表格，其结构基本相同，但是工作表“Mar”中没有“产品9”和“产品10”的数据，新增了“产品11”和“产品12”的数据。

首先插入一个新的工作表作为汇总工作表用于存放“合并计算”生成的合并数据表格，然后将光标放置在准备存放合并数据表格区域的第一个单元格，譬如单元格A1。

接下来，点击“数据”选项卡，点击“合并计算”按钮，弹出“合并计算”对话框。

在“函数”下拉框中选择汇总方式，此处选择“求和”。

在“引用位置”处添加数据源即需要合并的数据表格地址，选中工作表“Jan”的单元格区域A1:C11，然后点击“添加”按钮，同样方法添加工作表“Feb”和“Mar”的数据表格至“所有引用位置”列表框。

引用位置添加之后，只要不删除工作表，就可以在工作表中重复使用。

勾选标签位置的“首行”和“最左列”复选框，则表示使用数据源的列标题和行标题作为汇总数据表格的列标题和行标题，相同标题的数据将进行汇总计算。

点击“确定”按钮，生成汇总数据表格如下，罗列了所有行标题，包括工作表“Mar”中新增的“产品11”和“产品12”。但是第一个列标题缺省，且不会复制数据源数据表格的格式，需要自己添加或者利用格式刷设置。（关于格式刷的使用，请参阅Excel格式刷的使用方法和技巧）

如果不勾选“首行”和“最左列”复选框，则会生成如单元格E1:G11所示的合并数据表格，没有标题列和标题行，数据也不会按照不同标题分类汇总，而是按照数据表格中的位置汇总。

如果勾选“合并计算”的“创建指向源数据的链接”，然后“确定”。

则生成的汇总表格是分类汇总样式，且数据项都是公式，因此当数据源修改数据时，汇总数据表格可以自动更新，但每个数据源表格的数据都会单独占一项，因此整个表格的数据项较多。

2、合并分类明细

上一个例子中，如果需要在汇总表格中体现不同月份的明细该如何实现呢？

“合并计算”没有直接实现此需求的功能，但可以利用合并计算按照不同标签（即列标题和行标题）分类汇总的特性来实现。

将数据源数据表格的列标题加上月份标识。

然后再打开“合并计算”对话框，重新生成一个汇总表格，就可以看到各个月份的明细了。

3、合并多个非数字分类项和选择性合并

“合并计算”只能识别标题列和标题行，其余数据项都会按照函数选择的类型进行计算，因此，除了标题列和标题行之外非数字数据在进行“求和”等计算之后将会丢失。那么，如何在“合并计算”碰到多列非数字数据时保证合并数据表格的完整性呢？我们可以通过创建辅助列的方法来解决这个问题。

通过公式“=B2&","&C2”在数据源数据表格中创建辅助列，将所有非数字分类项合并在一个单元格。

接下来生成合并数据表格，这里可以选择只合并“产品+规格”、“数量”和“金额”三列。问题又来了，如何实现有选择性的合并呢？

首先在单元格区域A1:C1分别键入标题“产品+规格”、“数量”和“金额”，然后用光标选中该单元格区域，即将三个键入了标题的单元格全部选中，否则还是会合并所有标题列。

然后打开“合并计算”对话框，务必勾选“首行”和“最左列”复选框，生成的汇总表格将只有提前键入了列标题的这些数据列，这样就实现了有选择性地合并。

最后，我们还需要利用“分列”功能将A列还原成两列，保证合并数据表格的规范。

先在合并数据表格中插入一列空白列，然后选择单元格区域A1:A13，点击“数据”选项卡，点击“分列”按钮，弹出“文本分列向导”。

步骤1选择“分隔符号”。

步骤2勾选复选框“逗号”和“其他”，键入“+”号。

步骤3选择目标区域，即区域的第一个单元格A1。

点击“完成”，即将A列分成我们需要的两列数据。

4、设置动态引用

“合并计算”对话框中的“引用位置”默认都是绝对引用，所以当数据源表格新增数据时需要重新设置，可以通过创建动态引用的名称，然后在“引用位置”处引用名称来实现动态引用（关于名称的使用，请参阅http://baijiahao.baidu.com/builder/preview/s?id=1697696173859498719）。

利用Offset函数创建动态引用的名称。

点击“公式”选项卡，点击“定义名称”按钮，在弹出的“新建名称”对话框中，创建名称“JAN”，在“引用位置”处键入公式“=OFFSET(Jan!$A$1,,,COUNTA(Jan!$A:$A),3)”（注意公式中使用绝对引用）。

同样方法创建名称“FEB”和“MAR”，分别键入公式“=OFFSET(Feb!$A$1,,,COUNTA(Feb!$A:$A),3)”和“=OFFSET(Mar!$A$1,,,COUNTA(Mar!$A:$A),3)”。

Offset函数的功能是以指定的引用为参照，通过给定偏移数值生成一个新的引用，其语法结构为Offset(reference,rows,cols,[height],[width])：reference是偏移的基点，可以是单元格也可以是单元格区域；rows指定上下偏移的行数，缺省则不偏移；cols指定左右偏移的列数，缺省同上；height是要返回的新引用区域的行数；width是要返回的新引用区域的列数。（关于Offset函数的使用，请参阅http://baijiahao.baidu.com/builder/preview/s?id=1697756285523339464）

名称创建完成后可以在“名称管理器”查看和编辑。

然后打开“合并计算”对话框，在“引用位置”处分别键入名称并添加。

之后，在数据源表格中增加新的数据行就不用更改引用位置，只需要在“合并计算”对话框重新“确定”即可。

“合并计算”除了可以进行合并和汇总数据之外，还可以利用其分类合并汇总的特性进行筛选不重复数据和核对数据等工作。是不是觉得真香了