在工作中，面對(duì)一張表格，我們經(jīng)常需要做些查詢工作。

比如，下圖表格中，需要根據(jù)左邊的部門人員清單，查找每個(gè)部門對(duì)應(yīng)的所有人員姓名。

這是一個(gè)典型的一對(duì)多查詢問題，根據(jù)部門查找多個(gè)對(duì)應(yīng)的姓名。

很多函數(shù)高數(shù)都會(huì)給出?INDEX+SMALL+IF+ROW?的公式組合，如下：

• =IFERROR(INDEX($C$2:$C$11,SMALL(IF($B$2:$B$11=$E2,ROW($1:$10),4^8),COLUMN(A1))),"")

并且在公式編輯完成之后，還會(huì)「Ctrl+Shift+Enter」三鍵回車。

可是，作為一個(gè)大白，我完全看不懂復(fù)雜的函數(shù)公式。

稍有數(shù)據(jù)改動(dòng)，就會(huì)束手無策。

那么，有什么其它簡(jiǎn)單易懂的方法嗎？

答案是有的！

來，跟小 E 一起學(xué)習(xí)學(xué)習(xí)。

思路解析之 VLOOKUP

要解決這個(gè)問題，需要我們最熟悉的?VLOOKUP?函數(shù)。

但是 VLOOKUP 函數(shù)只會(huì)返回查找區(qū)域中，查找值第一個(gè)對(duì)應(yīng)的值。

比如下圖。

在查找人事部的人員姓名時(shí)，雖然林晨和張琴都是人事部門人員。

但是，VLOOKUP 函數(shù)只會(huì)返回「林晨」這一個(gè)值。

隔壁小美：你剛剛不是說，VLOOKUP 可以解決問題的嗎，怎么又不行了？

小 E：小美，你別心急啊，聽我慢慢跟你 Suo。

思路解析之改變查找值

既然 ，VLOOKUP 函數(shù)只會(huì)返回第一個(gè)對(duì)應(yīng)值，那我們只需要將查找值，變得不同就可以了。

比如說將后面出現(xiàn)的「人事部」變成「人事部 1」、「人事部 2」……

然后分別查找「人事部 1」，「人事部 2」對(duì)應(yīng)的人員姓名就可以了。

所以，我們要分兩步來解決這個(gè)問題。

第一步：將查找值「人事部」變成「人事部 1」和「人事部 2」

「人事部」三個(gè)字是不變的，變化的是用來區(qū)分不同的后綴：1 和 2。

簡(jiǎn)單說，就是固定的「人事部」連接（&）變化的「數(shù)字」構(gòu)成新的查找值來實(shí)現(xiàn)查找值的不同。

在此例中，我們可以使用?COLUMN?函數(shù)，返回指定單元格引用的列號(hào)，來實(shí)現(xiàn)后綴的變化。

COLUMN 函數(shù)，返回指定單元格引用的列號(hào)。

例如，公式?=COLUMN(D10)?返回 4，因?yàn)榱?D 為第四列。

下圖中，在 F9 單元格輸入以下公式：

• =$E9&COLUMN(A1)
  

向右拖拽，由于單元格列號(hào)的變化，就會(huì)得到「人事部 1」，「人事部 2」，以此類推。

公式中要固定「人事部」，即 E9 單元格的列號(hào)要用「$」鎖定。
「&」起連接作用。

思路解析之改變查找區(qū)域

第二步，要在查找區(qū)域中有「人事部 1」，「人事部 2」。

在下面這張表格中，A 列「輔助列」是我們新增的查找區(qū)域。

如何根據(jù)原本的 B 列「部門」，來生成我們需要的輔助列，是這一步的關(guān)鍵所在。

具體方法是：
在 A2 單元格輸入公式：

• =B2&COUNTIF($B$2:B2,B2)
  

向下填充即可。

接下來，我們分析一下，公式為什么這樣寫。

首先，COUNTIF 函數(shù)，是用于統(tǒng)計(jì)滿足某個(gè)條件的單元格的數(shù)量。

但這個(gè)公式的思路，不僅僅是使用 COUNTIF 函數(shù)，還是單元格的引用方式。

上圖中 E9:E13 區(qū)域是第一次出現(xiàn)「人事部」，所以得到「1」；

E9:E17 區(qū)域是第二次出現(xiàn)「人事部」，所以得到「2」。

注意到這兩個(gè)區(qū)域的開始部分都是 E9。

所以在生成輔助列的時(shí)候，開始區(qū)域是絕對(duì)引用，結(jié)束區(qū)域是相對(duì)引用。

這樣得到的次數(shù)才會(huì)遞增。

完成公式編寫

經(jīng)過上面幾步的解析，我們已經(jīng)完成了改變查找值和改變查找區(qū)域兩步。

但是，當(dāng)公式在 H3,查找「人事部 3」的時(shí)候：

查找區(qū)域中并沒有「人事部 3」，就會(huì)返回 #N/A 錯(cuò)誤。

所以，還需要使用 IFERROR 函數(shù)，來屏蔽錯(cuò)誤值。

最終 ，F(xiàn)1 單元格的公式是：

• =IFERROR(VLOOKUP($E2&COLUMN(A1),$A$1:$C$11,3,0),"")

呀，這么復(fù)雜的問題終于解決了！

趕緊向小美炫耀一下我的厲害！

? ? ?

總結(jié)

針對(duì)上面的問題，我總結(jié)出了兩點(diǎn)結(jié)論：

? 對(duì)于一些復(fù)雜問題，我們可以使用輔助列和比較熟悉的函數(shù)來解決。

? 寫公式的時(shí)候，思路很重要。先有思路，才會(huì)有公式。

Excel 里的技巧還有很多，學(xué)習(xí)也很簡(jiǎn)單，可惜很多人都不知道~

所以我強(qiáng)烈推薦你加入《秋葉 Excel 3 天集訓(xùn)營(yíng)》，僅需 1 元，你就能學(xué)到超多 Excel 小技巧，以及表格設(shè)計(jì)思路！

秋葉 Excel 3 天集訓(xùn)營(yíng)

課程原價(jià)?99?元

?現(xiàn)在?僅需 1 元！

實(shí)操練習(xí)+名師帶學(xué)

3 天提升你的 Excel 能力！

趕緊掃碼搶課吧！

??????

??

*廣告