|
本文包含兩部分內(nèi)容。 第一部分,，借助輔助列,，VLOOKUP函數(shù)提取符合條件的多個(gè)結(jié)果。 第二部分,，不借助輔助列,，VLOOKUP函數(shù)依然可以提取符合條件的多個(gè)結(jié)果。 1,，提出問(wèn)題 VLOOKUP函數(shù)是Excel中最常用最簡(jiǎn)單的條件查詢(xún)函數(shù)，號(hào)稱(chēng)函數(shù)世界中的大眾情人（不明白大眾情人意思的親們返回看本文的封面）,。它有兩個(gè)眾所周知的特點(diǎn),，查找值必須要在查找范圍的首列；以及它只提取查詢(xún)范圍中符合條件的首個(gè)查詢(xún)結(jié)果,。 但在實(shí)際工作中,，我們常常面臨這樣一個(gè)問(wèn)題：查詢(xún)符合條件的結(jié)果并非一個(gè)，而是多個(gè)或一個(gè),。 此時(shí)如果依然使用VLOOKUP函數(shù),，怎么處理哩？ 舉個(gè)栗子,，如下圖所示,根據(jù)A1:C10單元格區(qū)域的數(shù)據(jù),，計(jì)算A13人員的考核分結(jié)果，結(jié)果可能是多個(gè),，也可能是一個(gè),，例如看見(jiàn)星光。 2,，輔助列解法 首先,，我們?cè)贏列前面插入一列，作為輔助列,。A2單元格輸入以下公式,，并向下復(fù)制填充。 =B2&COUNTIF(B$2:B2,B2) 公式計(jì)算后的結(jié)果如下： COUNTIF函數(shù)用于計(jì)算指定單元格范圍內(nèi)某個(gè)值的重復(fù)次數(shù),。 COUNTIF(B$2:B2,B2),，計(jì)算B2在B$2:B2區(qū)域中的重復(fù)次數(shù),。由于查詢(xún)范圍的開(kāi)始行是絕對(duì)引用（B$2鎖死），結(jié)束行是相對(duì)引用（B2開(kāi)放）,，因此當(dāng)公式復(fù)制向下填充時(shí),，COUNTIF的查詢(xún)范圍不斷擴(kuò)展，例如B$2:B3,、B$2:B4……以此對(duì)重復(fù)值形成重復(fù)次數(shù)累加計(jì)數(shù)的結(jié)果,。 =B2&COUNTIF(B$2:B2,B2)，在COUNTIF函數(shù)計(jì)算結(jié)果的前面加上人員姓名,，意思就是每個(gè)姓名重復(fù)的次數(shù),，使之成為獨(dú)一無(wú)二的標(biāo)識(shí)。 輔助列構(gòu)建完成后,，在C13單元格使用以下VLOOKUP函數(shù),，即可得到結(jié)果。 =VLOOKUP($B13&COLUMN(A1),$A$1:$D$10,4,0) VLOOKUP的查找值是$B13&COLUMN(A1),，公式橫向填充后,，意思就是在$A$1:$D$10的單元格范圍內(nèi)，查找看見(jiàn)星光重復(fù)1次的結(jié)果,，看見(jiàn)星光重復(fù)2次的結(jié)果…… 如果需要屏蔽公式錯(cuò)誤值,，可以嵌套IFERROR函數(shù)，如下： =IFERROR(VLOOKUP($B13&COLUMN(A1),$A$1:$D$10,4,0),'''') 3,，一個(gè)公式 如果不用輔助列,，直接使用一個(gè)VLOOKUP函數(shù)計(jì)算出符合條件的多個(gè)結(jié)果可不可以呢？ 當(dāng)然也可以的,，只是計(jì)算效率并不高,，通常不建議使用。 我們前面講過(guò),，VLOOKUP只提取查找范圍的首個(gè)匹配結(jié)果…… 但如果查找范圍不同,，它自然就可以依次提取出多個(gè)結(jié)果。 這句話什么意思呢,？ 例如查找“看見(jiàn)星光”,，查找范圍是A1:D10時(shí)，首個(gè)結(jié)果是A2,，但當(dāng)查找范圍內(nèi)縮成A3:D10時(shí),，首個(gè)結(jié)果就成了A5…… 把這樣的想法形成公式表達(dá)，如下（數(shù)組公式）： =VLOOKUP($A13,INDIRECT(''a''&SMALL(IF($A$1:$A$10=$A13,ROW($1:$10)),COLUMN(A1))&'':c10''),3,0) SMALL(IF($A$1:$A$10=$A13,ROW($1:$10)),COLUMN(A1))部分,，如果A1:A10的值等于A13,，則返回對(duì)應(yīng)的行號(hào)，否則返回邏輯值FALSE,，然后使用SMALL函數(shù)從中依次由小到大取行數(shù),。 搭配INDIRECT函數(shù),，就構(gòu)成了隨公式向右填充，不斷變化的VLOOKUP查詢(xún)區(qū)間,，例如INDIRECT(A1:A10),，INDIRECT(A3:A10)…… 最后使用VLOOKUP查詢(xún)?nèi)?shù)，即可得出符合條件的多個(gè)查詢(xún)結(jié)果,。 需要說(shuō)明的是,，該公式未屏蔽錯(cuò)誤值，如需屏蔽錯(cuò)誤值,，請(qǐng)召喚IFERROR函數(shù)~ 就醬紫了,。 嗯，那誰(shuí),，星光大叔還是重申一下,，該公式為數(shù)組公式，輸入時(shí)需同時(shí)按下Ctlr Shift 回車(chē)……另外該公式運(yùn)算效率偏差,，只作于開(kāi)拓思路,，加深對(duì)VLOOKUP函數(shù)運(yùn)算機(jī)制的認(rèn)識(shí)，不建議大范圍使用…… 啊,，冷,，大叔睡覺(jué)覺(jué)去鳥(niǎo)~ The End |
|
|
