2.5　重要数据合并汇总的函数方法

在汇总当前工作簿的多个工作表数据时，可以使用很多函数来完成。不过，使用函数汇总，无法做并集合并（也就是将各工作表数据合并堆积到一起，列不变，行增加），只能提取每个工作表中所关心的重要数据。

具体使用什么函数，需要根据具体表格结构、具体汇总要求确定，有时候是查找满足条件的数据，有时候是加总满足条件的数据，前者一般使用查找引用函数，后者一般使用求和函数或计数函数，此外，还要配合，如逻辑判断、错误处理、文本处理、日期处理等其他函数。

2.5.1　使用查找引用函数汇总工作表

图2-66是各银行账户的日记账，现在要求制作每个银行账户的累计收入、累计支出和当前余额，如图2-67所示。

图2-66　各银行账户的日记账

图2-67　要求制作的汇总表

这样的汇总并不复杂，主要包括：

（1）提取各账户的期初余额，可以使用VLOOKUP函数。

（2）汇总各账户的累计收入和累计支出，可以使用SUM函数。

（3）提取各账户的当前余额，可以使用LOOKUP函数。

如果账户工作表很多，想做通用公式汇总，而不是一个工作表一个工作表来设计公式，可以使用INDIRECT函数间接引用。

针对这个例子，相关公式如下，结果如图2-68所示。

（1）各账户余额，单元格C3公式：

    =INDIRECT(B3&"!H2")

（2）各账户的累计收入，单元格D3公式：

    =SUM(INDIRECT(B3&"!F:F"))

（3）各账户的累计支出，单元格E3公式：

    =SUM(INDIRECT(B3&"!G:G"))

（4）各账户的当前余额，单元格F3公式：

    =LOOKUP(1,0/(INDIRECT(B3&"!H2:H1000")<>""),INDIRECT(B3&"!H2:H1000"))

图2-68　汇总结果

图2-69是另一个例子，也需要使用查找引用函数进行汇总。各门店每天的销售日报表，要求制作两个汇总报告，如图2-70所示。

报告1：每天所有门店的销售总计。

报告2：指定门店每天的销售统计。

图2-69　各门店销售日报表

图2-70　需要制作的汇总表

这两个问题，其实都是查找数据问题：

第一个报告是从每天的工作表中查找底部的合计数，由于每个工作表的底部合计数在相同行，因此直接引用即可，也可以使用VLOOKUP函数提取合计数。

第二个报告是获取指定门店每天的数据，可以使用VLOOKUP函数。

此外，由于日报表有很多，我们不可能一个一个单元格做公式，需要使用INDIRECT函数做工作表的间接引用。这样就很容易做出两个报告的公式。

报告1如图2-71所示，单元格C6公式：

    =IfERROR(VLOOKUP("合计",INDIRECT(汇总表!$B6&"!A:I"),COLUMN(B1),0),"")

或者

    =FERROR(INDIRECT($B6&"!R24C"&COLUMN(B1),FALSE),"")

比较一下这两个公式，前者实用性更广，因为是通过“合计”这个条件查找数据，因此没必要关注“合计”在哪一行；后者则是把“合计”固定在第24行，并且使用的是R1C1引用方式。

图2-71　每天所有门店的销售统计汇总表

报告2如图2-72所示，单元格M6公式：

    =IfERROR(VLOOKUP($M$3,INDIRECT(汇总表!$B6&"!A:I"),COLUMN(B1),0),"")

不论是报告1还是报告2，都需要使用两个最基本的函数：INDIRECT和IfERROR，前者用于间接引用每个工作表，以创建高效公式，后者用于处理找不到数据后的错误值。

图2-72　指定门店的每天销售统计表

图2-73是一个比较复杂的例子，原始数据是从系统导出的管理费用表，现在要求制作如图2-74所示的汇总表。

图2-73　系统导出的各月数据

图2-74　要求制作的汇总表

这个问题的难点是：在各月数据表中，部门和费用在一列，这样看起来就比较复杂。其实不尽然，仔细观察表格结构，我们可以通过A列是否为空，来判断B列的数据是部门名称还是费用名称。

不过，针对此例子，每月数据表的费用项目下面都是7个部门，因此可以使用OFFSET引用每项费用的数据区域，再用VLOOKUP函数提取数据。

以总经办1月份的各项费用为例，各单元格公式如下。

单元格C3：

    =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B3,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C4：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B4,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C5：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B5,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C6：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B6,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C7：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B7,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C8：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B8,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C9：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B9,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C10：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B10,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

单元格C11：

   =IfERROR(VLOOKUP("*"&$A2,OFFSET(INDIRECT(C$1&"!B1"),MATCH($B11,
INDIRECT(C$1&"!B2:B100"),0),,8,2),2,0),"")

其他各部门的公式可以复制得到。

公式看起来比较复杂，但核心是使用OFFSET函数获取每项费用下的数据区域，以及使用INDIRECT函数间接引用各工作表，而查找数据的具体工作，就交给VLOOKUP函数来完成。

2.5.2　使用分类汇总函数汇总工作表

在很多情况下，我们需要把每个工作表中满足条件的同类数据进行加总合计，此时就需要使用分类汇总函数SUMIF、SUMIFS或者SUMPRODUCT。

在2.4.2节中，介绍了一个这样的汇总例子。下面再介绍一个实用的例子。

图2-75是各地区的销售明细，现在要求制作如图2-76所示的汇总表，用来查看指定产品在各地区、各月的销售量。

图2-75　各地区的销售明细

图2-76　要求的汇总表

这是一个两个条件的求和问题，唯一的难点是要计算月份，而月份在A列的日期中，因此，可以使用TEXT函数提取月份，用SUMPRODUCT函数进行求和。

单元格C5的公式如下，其他单元格公式通过复制得到： mb27l59jRvozWRMmP5Pn90ayuYayeiH469I0IXT2IgH/NW6wrwO40tklXyngjHyf

    =SUMPRODUCT(
            (TEXT(INDIRECT(C$4&"!A2:A1000"),"m月")=$B5)*1,
            (INDIRECT(C$4&"!B2:B1000")=$C$2)*1,
            INDIRECT(C$4&"!C2:C1000")
            )