在很多Excel使用者眼中,會用Vlookup函式、Lookup函式似乎很利害了。其實遠遠不夠,在實際工作中有很多高難問題需要多個函式組合才能完成。
有一個查詢是很多人做夢都想實現的,可惜vlookup、Lookup都實現不了。它就是多表查詢。讓大家先見識一下這種查詢有多牛。
【例】工資的跨表查詢
Excel工作簿中有N個部門的工資表
在查詢表中,給定姓名,竟然可以查出該員工所在的部門和工資
是不是你夢想中的查詢公式?下面蘭色就一步步揭開這個神秘公式的真面目。
判斷員工是哪個部門的,可以用Countif函式計算個數,而多個表也難不住我們,前天的跨表求和中已學會了indirect函式多表引用的方法。
=COUNTIF(INDIRECT({“財務部”;“人事部”;“服務部”}&“!a:a”),A2)
測試結果:
選取公式按F9鍵可以返回一個數組{0;1;0},其中非0數字1的位置的即是員工所在表的位置(該員工在第2個表中)
問題是知道1的位置,怎麼把表名給提取出來?接下來要請了第3個大神函式:Lookup函式。用它經典的lookup(1,0/ 套路正好可以解決這個問題
=LOOKUP(1,0/COUNTIF(INDIRECT({“財務部”;“人事部”;“服務部”}&“!a:a”),A2),{“財務部”;“人事部”;“服務部”})
至此,所在部門查詢完成!
有了部門查工資,就簡單多了,Vlookup配合indirect函式可以輕鬆搞定!
=VLOOKUP(A2,INDIRECT(B2&“!A:B”),2,0)
我們用了4個大神函式(Vlookup函式、Lookup、Countif、Indirect函式)搞定了這個歷史性的查詢難題。