哈嘍,大家好?。?/p> 都說(shuō)一個(gè)好漢三個(gè)幫,一個(gè)籬笆三個(gè)樁。 其實(shí)在函數(shù)界也是這樣的,單獨(dú)使用的效果非常有限,但是通過(guò)函數(shù)與函數(shù)的組合,就能解決更多復(fù)雜的、多元的問(wèn)題。 下面就來(lái)給大家推薦8對(duì)函數(shù)界的黃金搭檔,一定能大大提高你的工作效率。 一、 VLOOKUP+COLUMN VLOOKUP函數(shù)是大家非常熟悉的一個(gè)函數(shù),可以按照指定的條件匹配到需要的數(shù)據(jù),但如果要匹配的數(shù)據(jù)是多列,就比較麻煩。而VLOOKUP+COLUMN組合就可以實(shí)現(xiàn)多列數(shù)據(jù)的匹配。 例如公式=VLOOKUP($G2,$A:$E,COLUMN(B1),0)可以按照指定的員工ID匹配到所有信息。 需要注意的是,因?yàn)楣揭依缘谝粎?shù)要混合引用,也就鎖定列。第二參數(shù)要絕對(duì)引用,防止右拉時(shí)范圍變動(dòng)。 二、INDEX+MATCH 這對(duì)函數(shù)組合堪稱(chēng)數(shù)據(jù)查找的最佳搭檔,可以按照行和列的不同條件匹配數(shù)據(jù)。 例如公式=INDEX($B$2:$D$13,MATCH(F2,$A$2:$A$13,0),MATCH(G2,$B$1:$D$1,0))可以按照姓名和學(xué)科匹配成績(jī)。 關(guān)于這對(duì)函數(shù)組合的詳細(xì)介紹,可以參考之前的教程,這里不贅述了。 查找公式:INDEX+ MATCH 三、IF+COUNTIFS COUNTIFS函數(shù)本來(lái)是解決多條件計(jì)數(shù)問(wèn)題的,和IF函數(shù)組合后可以用來(lái)做多條件重復(fù)性判斷。 例如公式=IF(COUNTIFS(B:B,B2,C:C,C2)=1,"","重復(fù)")可以按照銷(xiāo)售人員和商品名稱(chēng)兩個(gè)條件判斷數(shù)據(jù)是否重復(fù)。 根據(jù)自己的實(shí)際問(wèn)題可以增加判斷的條件,這對(duì)組合也是挺棒的。 四、MID+FIND 這對(duì)函數(shù)組合通常用來(lái)按照某個(gè)關(guān)鍵字符提取單元格內(nèi)的字符。 例如公式=MID(A2,FIND("]",A2,1)+1,99)可以提取出單元格里的公司名稱(chēng)。 在這個(gè)例子里,關(guān)鍵字符就是“]”,公司名稱(chēng)的前面都有個(gè)“]”。利用FIND函數(shù)定位出這個(gè)關(guān)鍵字的位置,再由MID函數(shù)截取需要的內(nèi)容,兩個(gè)函數(shù)配合的非常完美。 五、SUBSTITUTE+LEN 這對(duì)函數(shù)組合的作用是計(jì)算一個(gè)單元格內(nèi)指定字符的個(gè)數(shù),最典型的應(yīng)用就是統(tǒng)計(jì)單元格里的人數(shù)。 例如公式=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1就能計(jì)算出每個(gè)小組的人數(shù)。 在這個(gè)公式中,SUBSTITUTE函數(shù)的作用是將單元格中的頓號(hào)全部刪掉,再利用LEN函數(shù)計(jì)算刪掉頓號(hào)的字?jǐn)?shù),與原來(lái)的字?jǐn)?shù)相減,得到的結(jié)果是單元格中頓號(hào)的個(gè)數(shù),實(shí)際人數(shù)比頓號(hào)的個(gè)數(shù)多1,所以最后加1就是正確的結(jié)果。 六、SUMPRODUCT+COUNTIF 這對(duì)函數(shù)組合的最常用的功能就是統(tǒng)計(jì)不重復(fù)數(shù)據(jù)的個(gè)數(shù)。 例如公式=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))可以統(tǒng)計(jì)出實(shí)際的銷(xiāo)售員人數(shù)。 這個(gè)公式中COUNTIF(B2:B15,B2:B15)統(tǒng)計(jì)出了每個(gè)人在區(qū)域中出現(xiàn)的次數(shù),1/COUNTIF(B2:B15,B2:B15)是對(duì)次數(shù)進(jìn)行平均,例如,夏淼一共出現(xiàn)了五次,那么每次都算0.2,最后將五個(gè)0.2相加為1,最后實(shí)現(xiàn)了每個(gè)人都按一次計(jì)算。 七、SUM+SUMIFS 我們知道SUMIFS是一個(gè)多條件求和的函數(shù),現(xiàn)在加上求和函數(shù)SUM,就能實(shí)現(xiàn)很多非常復(fù)雜的條件求和問(wèn)題。 例如公式=SUM(SUMIFS(D:D,C:C,{"*洗衣機(jī)","*冰箱"},B:B,{"夏淼","張珂"}))可以計(jì)算出夏淼銷(xiāo)售的洗衣機(jī)與張珂銷(xiāo)售的冰箱合計(jì)。 如果不知道這對(duì)函數(shù)組合的話(huà),要解決這個(gè)問(wèn)題還是有點(diǎn)困難的,有興趣的同學(xué)不妨試試其他的思路。 關(guān)于這個(gè)公式的原理,涉及到數(shù)組的維度與多條件之間的一些邏輯關(guān)系,要解釋的話(huà)還得費(fèi)些口舌了,想了解原理的可以留言,以后單獨(dú)寫(xiě)一篇教程。 八、LOOKUP+FIND 最后給大家介紹的這對(duì)搭檔,可以實(shí)現(xiàn)一些特殊的數(shù)據(jù)匹配問(wèn)題。 例如公式=LOOKUP(1,0/FIND(D2,$A$2:$A$5),$B$2:$B$5)可以根據(jù)人員安排匹配出門(mén)店的對(duì)應(yīng)負(fù)責(zé)人是哪個(gè)。 這種用法的核心是利用FIND函數(shù)找到指定門(mén)店存在于安排表的哪個(gè)位置,然后再用LOOKUP函數(shù)得到對(duì)應(yīng)位置的人員。 |
|
來(lái)自: Excel教程平臺(tái) > 《待分類(lèi)》