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編程全套視頻教