Excel表存在合并單元格怎么分別統(tǒng)計(jì)組員獎(jiǎng)勵(lì)金額
?
作者:老菜鳥來(lái)源:部落窩教育發(fā)布時(shí)間:2020-12-17 17:39:12點(diǎn)擊:3964
編按:
表中存在合并單元格后統(tǒng)計(jì)很麻煩。譬如項(xiàng)目獎(jiǎng)勵(lì)表中的金額按項(xiàng)目名進(jìn)行了分組合并,現(xiàn)在要分別統(tǒng)計(jì)各項(xiàng)目組員的獎(jiǎng)勵(lì)金額,怎么做才能快速完成?文中將分享兩種方法,用到的函數(shù)都很簡(jiǎn)單:COUNT、IF、COUNTIF或者M(jìn)ATCH。
周雨是一家廣告公司的HR,但每月統(tǒng)計(jì)獎(jiǎng)勵(lì)金額都是一件讓她十分頭疼的事。
因?yàn)楣久吭露紩?huì)由業(yè)務(wù)部門派發(fā)具體的項(xiàng)目給不同的項(xiàng)目組,而每個(gè)項(xiàng)目組的人數(shù)都不同。如果項(xiàng)目是一個(gè)人完成的,就可以獨(dú)自獲得該項(xiàng)目的全部獎(jiǎng)勵(lì),如果是多人合作的,則需要按參與人數(shù)平均分配獎(jiǎng)勵(lì)金額。
以11月為例來(lái)說(shuō),其中人數(shù)最少的項(xiàng)目組有四個(gè)成員:阿斗、貝貝、曹磊和大寶,他們共完成了9個(gè)項(xiàng)目,統(tǒng)計(jì)結(jié)果如圖所示:
表中按項(xiàng)目進(jìn)行了分組統(tǒng)計(jì),同一項(xiàng)目的金額進(jìn)行了合并。
阿斗參與了5個(gè)項(xiàng)目,可以得到的獎(jiǎng)勵(lì)金額在D列單獨(dú)標(biāo)注出來(lái)了,合計(jì)就能獲得1500的獎(jiǎng)勵(lì)。其他人員的算法也是如此。最小的項(xiàng)目組這樣統(tǒng)計(jì)還比較簡(jiǎn)單,但是還有很多這樣的項(xiàng)目組,有些項(xiàng)目組的人數(shù)更是遠(yuǎn)超10人。
如果統(tǒng)計(jì)時(shí)間比較緊急,這樣的操作就很難按時(shí)完成工作!
周雨也找高人求助過(guò),倒是得到一個(gè)解決辦法,具體做法如下:
D列加了一個(gè)輔助列,使用公式:=IF(C2,C2/MATCH(1=0,C3:$C$17=0,-1),D1),即可以得到每個(gè)人在不同項(xiàng)目中的對(duì)應(yīng)獎(jiǎng)勵(lì)數(shù)額。
然后再用SUMIF函數(shù)就能得到每個(gè)人最終能拿到的全部獎(jiǎng)勵(lì)數(shù)額。
解決問(wèn)題后,周雨滿心歡喜,但才高興了沒(méi)多久,就又犯難了:“最后這個(gè)SUMIF函數(shù)自己倒是會(huì)做,但是輔助列的這個(gè)公式咋用,完全摸不著頭腦。公式用于這個(gè)數(shù)據(jù)源沒(méi)問(wèn)題,但是換一個(gè)數(shù)據(jù)源我就不會(huì)改公式了?!?/span>
周雨不由得恨自己平時(shí)沒(méi)有好好學(xué)習(xí)。那么問(wèn)題究竟要怎么解決呢?
這里有一個(gè)適合初級(jí)用戶的解法,把一個(gè)輔助列分開為三個(gè)輔助列,公式的難度就會(huì)降低很多。
下面就一起來(lái)看看是什么方法:
輔助列1:=COUNT($F$2:F2),得到的結(jié)果如圖所示。
實(shí)際上就是對(duì)相同的項(xiàng)目進(jìn)行了編號(hào),讓同一個(gè)項(xiàng)目序號(hào)相同。這樣相似的解法很多,只要能得到輔助列1這樣的結(jié)果就都可行。
輔助列2:=IF(F2>0,F2,B1),得到的結(jié)果如圖所示。
這一列的作用就是把項(xiàng)目的獎(jiǎng)勵(lì)金額與項(xiàng)目編號(hào)進(jìn)行了一一對(duì)應(yīng)。
實(shí)際上前兩個(gè)輔助列的作用都是為了把數(shù)據(jù)源中的兩列合并單元格拆分。因?yàn)閱?wèn)題之所以變得麻煩,就是由于合并單元格的存在。
輔助列3:=B2/COUNTIF(A:A,A2)
輔助列3是單人在不同項(xiàng)目中的所得金額,用項(xiàng)目總金額除以項(xiàng)目參與的人數(shù)即可。人數(shù)的計(jì)算是利用了COUNTIF函數(shù)和輔助列1統(tǒng)計(jì)出來(lái)的。
有了輔助列3,再用SUMIF就可以完成最終統(tǒng)計(jì)了。
通過(guò)今天分享的這個(gè)案例,想和大家說(shuō)明幾個(gè)問(wèn)題:
1、對(duì)于需要統(tǒng)計(jì)的數(shù)據(jù)表格,盡量避免使用合并單元格,按照制表規(guī)范會(huì)避免很多麻煩。
2、萬(wàn)不得已用到合并單元格,想一步到位得到需要的結(jié)果往往比較有難度,在大家實(shí)力還達(dá)不到的情況下,靈活使用輔助列是非常有效的方法。
3、老菜鳥提供的解決方案,用到了三個(gè)輔助列,涉及到的函數(shù)都很基礎(chǔ)。COUNT、IF、COUNTIF可以說(shuō)是每個(gè)職場(chǎng)人必須掌握的基本函數(shù),我們需要具備把它運(yùn)用起來(lái)的能力。
4、文中提到的這個(gè)公式=IF(F2,F2/MATCH(1=0,F3:$F$17=0,-1),G1),雖然也只用到了兩個(gè)基礎(chǔ)函數(shù)IF和MATCH,但是公式的思路并不容易理解,要解釋清楚的話或許還得一篇教程,想搞清楚其中奧妙的同學(xué)可以留言。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
MATCH:函數(shù)哲學(xué)家,找巨人做伴。新出道必學(xué)!
怎么用FREQUENCY函數(shù)統(tǒng)計(jì)連勝次數(shù)?
版權(quán)申明:
文本作者老菜鳥;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)