2015年终奉献: Excel超难的多表查找、求和与合并公式

在excel中多表处理是很多新手和高手心中的结，2015年马上过去，兰色不想让大家带着这个困惑进入2016，今天我们就一个一个的克服他们。（同学们一定要收藏起来，你在其他书和教程中是找不到的这些公式的）

解决这几个excel中最棘手的难题，要借助一个宏表函数，它就是Get.workbook，它可以取得所有工作表的名字，只是它不能直接用到单元格公式中，需要先定义一个名称。

公式选项卡 - 定义名称（excel2003中 插入 - 定义 - 名称），在打开的新建名称窗口中，输入“名称”并在引用位置框中输入公式：

=get.workbook(1)

定义后，在公式中就可以直接使用“工作表”这个名称了。另外当前文件要另存为“启用宏的工作簿”类型即xlsm后辍。（excel2003不需要）

1、查找一个人在哪个部门？

要求：在总表中设置公式，查找对应的姓名在哪个分表中。

公式：

=MID(LOOKUP(2,1/COUNTIF(INDIRECT(工作表&'!A:A'),A2),工作表),7,99)

2、用vlookup函数实现多表查找

要求：在总表中从各个分表中查找该员工的学历信息

公式：

=VLOOKUP(B2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT(工作表&'!a:a'),B2),工作表)&'!a:b'),2,0)

3、用sumif和sumifs实现多表汇总

要求：对各地区的明细表的产品销量进行汇总

公式：

=SUMPRODUCT(SUMIF(INDIRECT(工作表&'!B:B'),C2,INDIRECT(工作表&'!C:C')))

注意：为了防止循环引，集团表中列前插入2列。

4、用公式把各个表格合并到总表中。

要求：把所有分表的数据合并到总表中去，增加、删除分表时，总表会自动更新。

公式：合并表A2单元格

=IFERROR(INDIRECT(INDEX(shdate,ROW(A1))&'!'&ADDRESS(INDEX(Myrow,ROW(A1))+1,COLUMN(A2))),'')

定义的名称：

Leijia =MMULT((shcountdata>TRANSPOSE(shcountdata))*1,shrowcount-1)

Myrow =总行数数组-LOOKUP(总行数数组-1,Leijia)

rowdata =LOOKUP(总行数数组-1,Leijia,总行数数组-1)

sh =LOOKUP(ROW(INDIRECT('2:'&COUNTA(工作表))),ROW(INDIRECT('1:99')),工作表)

shcountdata =ROW(INDIRECT('1:'&COUNTA(shrowcount)))

shdate =LOOKUP(总行数数组-1,Leijia,sh)

shrowcount =SUBTOTAL(3,INDIRECT(sh&'!A:A'))

工作表 =GET.WORKBOOK(1)

总行数数组 =ROW(INDIRECT('1:'&SUM(shrowcount-1)))

示例表格下载：http://pan.baidu.com/s/1i3XxT9z

兰色说：最后一个多表合并的公式，写了2个多小时才完成，虽然公式非常的复杂，但实现的功能还是很强大的。如果同学们觉得有用，分享给自已的朋友吧，同时也是鼓励一下兰色。

---

---

点击左下角“阅读原文”查看兰色和小妖录制的数据透视表全套+函数全套+技巧全套+VBA编程全套视频教