函數(shù)界黃金搭檔,解決從多條件排重到求和的八大典型統(tǒng)計問題
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2022-11-28 10:39:37點擊:1451
編按:
哈嘍,大家好啊!今天給大家介紹8對函數(shù)界的黃金搭檔吧!這些函數(shù)組合可以解決包括同時按不同行和列條件查找匹配、多條件檢查重復(fù)值、按條件統(tǒng)計不重復(fù)值、同一單元格中多個數(shù)據(jù)的統(tǒng)計、多人或多類目下多條件求和、同一單元格中有多個數(shù)據(jù)的查找匹配等等需要。
都說一個好漢三個幫,其實函數(shù)也是這樣的。單獨的函數(shù)能產(chǎn)生的效果是有限的,只有通過函數(shù)之間的組合,才能解決更多復(fù)雜的問題。今天就給大家推薦8對函數(shù)界的黃金搭檔,一定能大大提高你的工作效率。
一、VLOOKUP+COLUMN 多列查找匹配
VLOOKUP函數(shù)是大家非常熟悉的一個函數(shù),可以按照指定的條件匹配到需要的數(shù)據(jù),但如果要匹配的數(shù)據(jù)是多列,就比較麻煩。而VLOOKUP+COLUMN組合就可以實現(xiàn)多列數(shù)據(jù)的匹配。
例如公式=VLOOKUP($G2,$A:$E,COLUMN(B1),0)可以按照指定的員工ID匹配到所有信息。
需要注意的是,因為公式要右拉,所以第一參數(shù)要混合引用,也就鎖定列。第二參數(shù)要絕對引用,防止右拉時范圍變動。
二、INDEX+MATCH 同時按不同行和列條件匹配
這對函數(shù)組合堪稱數(shù)據(jù)查找的最佳搭檔,可以按照行和列的不同條件匹配數(shù)據(jù)。
例如公式=INDEX($B$2:$D$13,MATCH(F2,$A$2:$A$13,0),MATCH(G2,$B$1:$D$1,0))可以按照姓名和學科匹配成績。
關(guān)于這對函數(shù)組合的詳細介紹,可以參考之前的教程,這里不贅述了。
MATCH函數(shù)詳細用法:《MATCH:函數(shù)哲學家,找巨人做伴。新出道必學!》
INDEX函數(shù)詳細用法:《INDEX:函數(shù)中的精確制導(dǎo)導(dǎo)彈,最強大的瘸子》
三、IF+COUNTIFS 多條件檢查重復(fù)值
COUNTIFS函數(shù)本來是解決多條件計數(shù)問題的,和IF函數(shù)組合后可以用來做多條件重復(fù)性判斷。
例如公式=IF(COUNTIFS(B:B,B2,C:C,C2)=1,"","重復(fù)")可以按照銷售人員和商品名稱兩個條件判斷數(shù)據(jù)是否重復(fù)。
根據(jù)自己的實際問題可以增加判斷的條件,這對組合也是挺棒的。
四、SUMPRODUCT+COUNTIF 統(tǒng)計不重復(fù)個數(shù)
這對函數(shù)組合的最常用的功能就是統(tǒng)計不重復(fù)數(shù)據(jù)的個數(shù)。
例如公式=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))可以統(tǒng)計出實際的銷售員人數(shù)。
這個公式中COUNTIF(B2:B15,B2:B15)統(tǒng)計出了每個人在區(qū)域中出現(xiàn)的次數(shù),1/COUNTIF(B2:B15,B2:B15)是對次數(shù)進行平均,例如,夏淼一共出現(xiàn)了五次,那么每次都算0.2,最后將五個0.2相加為1,最后實現(xiàn)了每個人都按一次計算。
五、MID+FIND 按特定字符位置提取字符
這對函數(shù)組合通常用來按照某個關(guān)鍵字符提取單元格內(nèi)的字符。
例如公式=MID(A2,FIND("]",A2,1)+1,99)可以提取出單元格里的公司名稱。
在這個例子里,關(guān)鍵字符就是“]”,公司名稱的前面都有個“]”。利用FIND函數(shù)定位出這個關(guān)鍵字的位置,再由MID函數(shù)截取需要的內(nèi)容,兩個函數(shù)配合的非常完美。
六、SUBSTITUTE+LEN 同一單元格中有多個數(shù)據(jù)的統(tǒng)計
這是在不規(guī)范數(shù)據(jù)中統(tǒng)計,同一單元格中有多個數(shù)據(jù)。這對函數(shù)組合的作用是計算一個單元格內(nèi)指定字符的個數(shù),最典型的應(yīng)用就是統(tǒng)計單元格里的人數(shù)。
例如公式=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1就能計算出每個小組的人數(shù)。
在這個公式中,SUBSTITUTE函數(shù)的作用是將單元格中的頓號全部刪掉,再利用LEN函數(shù)計算刪掉頓號的字數(shù),與原來的字數(shù)相減,得到的結(jié)果是單元格中頓號的個數(shù),實際人數(shù)比頓號的個數(shù)多1,所以最后加1就是正確的結(jié)果。
七、SUM+SUMIFS 多人或多類目的多條件求和
我們知道SUMIFS是一個多條件求和的函數(shù),現(xiàn)在加上求和函數(shù)SUM,就能實現(xiàn)很多非常復(fù)雜的條件求和問題。
例如公式=SUM(SUMIFS(D:D,C:C,{"*洗衣機","*冰箱"},B:B,{"夏淼","張珂"}))可以計算出夏淼銷售的洗衣機與張珂銷售的冰箱合計。
如果不知道這對函數(shù)組合的話,要解決這個問題還是有點困難的,有興趣的同學不妨試試其他的思路。
關(guān)于這個公式的原理,涉及到數(shù)組的維度與多條件之間的一些邏輯關(guān)系,要解釋的話還得費些口舌了,想了解原理的可以留言,以后單獨寫一篇教程。
八、LOOKUP+FIND 同一單元格中有多個數(shù)據(jù)的查找匹配
這是在不規(guī)范數(shù)據(jù)中查找匹配,同一單元格中有多個數(shù)據(jù)。最后給大家介紹的這對搭檔,可以實現(xiàn)一些特殊的數(shù)據(jù)匹配問題。
例如公式=LOOKUP(1,0/FIND(D2,$A$2:$A$5),$B$2:$B$5)可以根據(jù)人員安排匹配出門店的對應(yīng)負責人是哪個。
這種用法的核心是利用FIND函數(shù)找到指定門店存在于安排表的哪個位置,然后再用LOOKUP函數(shù)得到對應(yīng)位置的人員。
好的,以上就是今天推薦的八個函數(shù)搭檔,你覺得哪一對是最佳搭檔呢?
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學習Excel:
相關(guān)推薦:
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
10種職場人最常用的excel多條件查找方法?。ńㄗh收藏)
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者老菜鳥;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!