|
送人玫瑰,，手有余香，請(qǐng)將文章分享給更多朋友 動(dòng)手操作是熟練掌握EXCEL的最快捷途徑,！ 【置頂公眾號(hào)】或者【設(shè)為星標(biāo)】及時(shí)接收更新不迷路
小伙伴們好,，今天要和大家分享一則和數(shù)據(jù)查找、提取有關(guān)的題目,。 原題目是這樣的：  左側(cè)是源數(shù)據(jù),，右側(cè)表格中已知了成績(jī)，怎樣將對(duì)應(yīng)的代碼提取出來,。由于有重復(fù)值,，因此不能直接使用VLOOKUP函數(shù)了。有什么好的方法和思路呢,？ 萬金油公式  在單元格E2中輸入“=INDEX(A:A,SMALL(IF(F2=$B$2:$B$19,ROW($2:$19)),COUNTIF($F$2:F2,F2)))”,，三鍵回車并向下拖曳即可。 思路： 這是一個(gè)萬金油公式的套路,，其它部分都很簡(jiǎn)單,，不再過多介紹了。 特別講一下COUNTIF($F$2:F2,F2)部分,。在動(dòng)態(tài)區(qū)間$F$2:F2,，統(tǒng)計(jì)F2中的個(gè)數(shù)。如果有重復(fù)值,，那么隨著公式向下拖曳,，它返回的值會(huì)逐次增加。作為SMALL函數(shù)的第二個(gè)參數(shù),，它的變化將會(huì)改變INDEX函數(shù)最最終結(jié)果的返回,，從而實(shí)現(xiàn)了提取不同的代碼,。  在單元格E2中輸入“=OFFSET(A$1,MATCH(LARGE(B:B,ROW(A1)),IF(COUNTIF(E$1:E1,A$2:A$19)=0,B$2:B$19),),)”，三鍵回車并向下拖曳即可,。 思路：
這里特別講一點(diǎn)COUNTIF(E$1:E1,A$2:A$19)=0的作用,。隨著區(qū)域的擴(kuò)大,，在E$1:E1中的數(shù)值，COUNTIF函數(shù)都會(huì)返回值1,，也就是不滿足“=0”的條件,，就會(huì)被排除在IF(COUNTIF(E$1:E1,A$2:A$19)=0,B$2:B$19)函數(shù)返回的清單之外，從而實(shí)現(xiàn)了查找不同代碼的目的,。  在單元格E2中輸入公式“=INDEX(A:A,MOD(SMALL(ROW($2:$19)-$B$2:$B$19/1%%,ROW(A1)),100))”,，三鍵回車并向下拖曳即可。 一句話解釋： 這個(gè)公式實(shí)際上利用了加權(quán)的處理方法,。ROW($2:$19)-$B$2:$B$19/1%%部分,，在成績(jī)相同的情況下，擴(kuò)大1萬倍后,，越小的行號(hào)與其差越小,，會(huì)被首先提取到。 請(qǐng)看一下下面這個(gè)公式提取的效果和上面的這個(gè)公式的有什么不同,。  它的公式是“=INDEX($A$2:$A$19,MOD(LARGE($B$2:$B$19*10000+ROW($1:$18),ROW(A1)),100))” 加權(quán)上不同的操作,，帶來的效果是各不相同的。  在單元格E2中輸入公式“=INDEX(A:A,MOD(-LARGE($B$2:$B$19*10^6-ROW($2:$19),ROW($A1)),10^3))”,，三鍵回車并向下拖曳即可,。 一句話解釋： 這個(gè)公式也用到了加權(quán)的技巧。這次由于是對(duì)成績(jī)進(jìn)行加權(quán),，再減去行號(hào),。由于這里是減去行號(hào)，直接用MOD函數(shù)求余就得不到行號(hào)了,，因此在MOD函數(shù)求余是用了個(gè)小技巧,。 MOD函數(shù)兩個(gè)參數(shù)異號(hào)整數(shù)求余： 1、函數(shù)值符號(hào)規(guī)律(余數(shù)的符號(hào)) mod(負(fù),，正)=正 mod(正,，負(fù))=負(fù)。 結(jié)論：兩個(gè)整數(shù)求余時(shí),，其值的符號(hào)為除數(shù)的符號(hào),。 2,、取值規(guī)律，先將兩個(gè)整數(shù)看作是正數(shù),，再作除法運(yùn)算,。 ①能整除時(shí),，其值為0 （或沒有顯示）,。 ②不能整除時(shí)，其值=除數(shù)×(整商+1)-被除數(shù),。 -END-  |
|
|
