文章最後有彩蛋!好禮相送!
Excel秘籍大全,正文開始
現在要實現一對多查詢,如圖所示,根據部門獲取這個部門的數據:
案例
這個時候,我們需要構建一個輔助列:
構建輔助列
公式爲:=(B2=$G$2)+A1
這個公式是什麽意思,如果當前的部門和要查詢的部門相等,那麽就加上一個單元格,如果B2=$G$2 成立,返回1,而A1是0,所以返回1,公式向下填充,公式變化,生成1,2,3的序列。
然後使用VLOOKUP函數,公式向右向左填充:
使用VLOOKUP
=VLOOKUP(ROW(A1),$A$2:$E$10,COLUMN(B:B),FALSE)
當ROW(A1)向下填充的時候,生成序號1,2,3,4等等,查詢的值就是1,2,3,4,而COLUMN(B:B向右填充的時候,生成的序號位2,3,4等等,這樣就查詢出了結果。
爲了屏蔽錯誤值,可以用iferror屏蔽錯誤值:
使用iferror
還沒完,這個是按部門查詢,如果是使用通配符呢,我們就可以借助另外一種方法。
使用countif構建輔助列
使用countif
=COUNTIF($B$2:B2,$G$2&"*")
這樣就給姓李的人生成了連續的序號,而第一個例子的VLOOKUP函數就不用變化,只是變化了輔助列,就獲取了結果
獲取結果
=IFERROR(VLOOKUP(ROW(A1),$A$2:$E$10,COLUMN(B:B),FALSE),"")
看到了嗎,兩個不同的輔助列,一個公式,搞定了兩個複雜的查詢,所以說VLOOKUP+輔助列真是如虎添翼啊。
以下《900套高逼格工作模板.xls 》免費下載,不收一分錢!
常用Excel
用Excel玩好報表
是必不可缺的技能
要知道一張好的圖表
可以做到一圖勝千言!
今天推薦的超實用幹貨是
《900套高逼格工作模板.xls 》
3.2G高逼格Excel可視化模板
制作精美 可直接套用
適合自用和內部培訓使用
領取方式
關注我們
私信發送關鍵字:900
即可免費領取
資料來源于網絡,公益分享,如有侵權,聯系刪除