Vlookup擴展應用之一對多合併

Helen來信提出了一個有關Vlookup的問題——

我覺得這個問題很有代表性，也很有趣，因為這對於函數的運用是一次很好的思維鍛煉。於是，發了一條微博讓大家參與討論http://weibo.com/1426389024/zDpBkEKrz。

得到了一些想法，如透視表、數組公式、BI、先排序……

我的想法卻有些不同，其實，只要換一種思維，就能抓住這題的小尾巴。那些技巧你可能都會，但能不能想得到，串得起來，就靠經驗和創新了。

核心思路有三點：

1、得把每個介紹人對應的業務員匹配出來，由於Vlookup只能一對一匹配，在天然不存在唯一的情況下，需要製造唯一；

2、匹配出來的業務員應該放在同一行，而不是同一列，這樣，才能方便用公式合併單元格；

3、以特製的序號標籤幫助Vlookup實現匹配。

最後，合併單元格就能得到最終效果。

第一步——製造唯一

製造唯一是Vlookup的經典擴展用法，主要會用到countif動態計數以及&符號。這在前面的博文中已經分享過，詳見《Vlookup一對多匹配》：http://blog.sina.com.cn/s/blog_5504f82001016lo1.html

第二步——設計表格樣式（同一行與序號標籤）設計出同一介紹人對應的不同業務員在同一行的表格樣式，並特製序號標籤，用於與「介紹人」合併成為唯一的查找條件，如「許新民1」、「程紅藝9」……

在用於查找的數據區域中添加輔助列，將A列「介紹人」與C列「序號」合併起來作為第二參數首列。公式為=B1&D1。在H3單元格寫公式=VLOOKUP($G3&H$2,$A:$C,3,0)，需要注意Vlookup函數第一參數的引用類型，公式向右向下複製後，「介紹人」應該行變列不變，所以鎖定列，「序號」應該列變行不變，所以鎖定行。而第二參數的引用規範是，在99.9999%的情況下，Vlookup引用的用於查找的數據區域，也就是第二參數，全都應該是「絕對引用」。因為，無論公式複製到哪裡，都在這個固定的數據區域中進行查找。

第三步——公式升級（去除錯誤值）由於不知道同一個介紹人會介紹幾個業務員，所以，特製的序號標籤有可能預設10個以上，甚至更多。而每個介紹人介紹的業務員數量又是不同的，就會出現經典的Vlookup錯誤值#N/A（如上圖）。有它存在，在單元格合併時就將合併到錯誤值，而偏離預想的呈現效果。幸好，2010版出現了一個新函數IFERROR，可以輕鬆的替換Vlookup錯誤值，而不用像以前那般需要寫很長的IF函數。IFERROR的公式是=IFERROR(VLOOKUP($G3&H$2,$A:$C,3,0),"")，第一參數代表一個運算，第二參數代表「如果第一參數的運算結果為錯誤值時所顯示的由我們自己設定的值」。這裡，可以設定為""，也就是「空」。

第四步——合併單元格神奇的&符號又來了，用它就能將單元格合併成各種想要的樣子，例如在兩段內容之間用頓號隔開=I3&"、"&J3。

好了，完成了。當G列有介紹人時，H列就會將屬於他的業務員全體呈現。

不過，如果仔細觀察，合併單元格這個環節是有問題的，關鍵就是多出來的那些頓號。正好，這就變成一道新的題目。

推薦閱讀：