最近對怎么用函數(shù)來實(shí)現(xiàn)篩選的功能有點(diǎn)想法，所以就寫幾篇關(guān)于這個問題的文章。昨天說了用vlookup函數(shù)來實(shí)現(xiàn)一對多的查詢，也就是篩選的功能。其實(shí)用lookup函數(shù)也可以實(shí)現(xiàn)這個功能，接下來就說一說如何實(shí)現(xiàn)。
-01-
具體應(yīng)用
1.根據(jù)姓名查找出所有出現(xiàn)的記錄
還是昨天那個例子，今天用lookup函數(shù)來完成，思路和昨天是一樣的。先要給姓名后面添加序號，讓相同的姓名變成不同的姓名。
如下圖所示，在A列添加個輔助列，A2單元格中輸入公式=COUNTIF(B$2:B2,H$1)，下拉。這樣H1單元格的姓名在B列中第1次出現(xiàn)時，A列對應(yīng)的序號就是1；第2次出現(xiàn)時對應(yīng)的序號就是2?？梢钥聪聢D的紅框標(biāo)記出來的單元格。
下一步就是將B列的姓名和A列的序號連接起來，也就是$B$2:$B$11&$A$2:$A$11這部分，形成的效果如下。
形成的這1列要和H1單元格的姓名進(jìn)行比較，看是否相等。由于一個添加了序號，一個沒有添加序號，肯定是不會相等的。所以要給H1單元格也連接個序號，可以用row函數(shù)。也就是$H$1&ROW(1:1)這部分，下拉時序號會增加，就相當(dāng)于黛玉1，黛玉2。
$B$2:$B$11&$A$2:$A$11=$H$1&ROW(1:1)這樣就可以進(jìn)行比較了，黛玉第1次出現(xiàn)時，會找到黛玉1；下拉也就是第2次出現(xiàn)時，會找到黛玉2。
然后就可以用lookup來查找了，公式為=LOOKUP(1,0/($B$2:$B$11&$A$2:$A$11=$H$1&ROW(1:1)),B$2:B$11)。這是lookup的經(jīng)典查找方式，LOOKUP(1,0/(條件區(qū)域=條件),返回區(qū)域)。由于返回區(qū)域的列號沒有鎖定，所以右拉時會相對變化，這樣剛好符合我們的需要。
但是下拉時會出現(xiàn)錯誤值，要處理錯誤，最后的公式為=IFNA(LOOKUP(1,0/($B$2:$B$11&$A$2:$A$11=$H$1&ROW(1:1)),B$2:B$11),"")。右拉下拉完成。
另一種簡單的方法就是在輔助列中將姓名和序號連接起來。在A15單元格輸入公式=B15&COUNTIF(B$15:B15,H$14)，下拉。最后的公式就會簡化一些，在G17單元格輸入公式=IFNA(LOOKUP(1,0/($A$15:$A$24=$H$14&ROW(1:1)),B$15:B$24),"")，右拉下拉完成。
如果不用輔助列呢？公式為=IFNA(LOOKUP(1,0/($A$28:$A$37&COUNTIF(OFFSET($A$28,,,ROW($28:$37)-27),$H$27)=$H$27&ROW(1:1)),A$28:A$37),"")。還是用到offset的多維引用，將序號列放在1個數(shù)組中，就可以免去輔助列。
這個公式還可以寫為=IFNA(LOOKUP(1,0/(($A$28:$A$37=$H$27)*(COUNTIF(OFFSET($A$28,,,ROW($28:$37)-27),$H$27)=ROW(1:1))),A$28:A$37),"")。
其實(shí)今天和昨天的思路是一樣的，后來我才意識到這種思路是把一對多查詢的問題轉(zhuǎn)化成了多條件查詢的問題。
鏈接：
https://pan.baidu.com/s/1KMpogKXY2J8DQPwFq90EOQ
提取碼：p4bv
