如何按數(shù)據(jù)區(qū)間匯總求和?介紹5個(gè)實(shí)用的數(shù)據(jù)分析公式
?
作者:老菜鳥來(lái)源:部落窩教育發(fā)布時(shí)間:2022-02-28 09:20:18點(diǎn)擊:6988
編按:
數(shù)據(jù)匯總求和是數(shù)據(jù)分析當(dāng)中最基本的操作,對(duì)于簡(jiǎn)單的加減乘除我們都了然于胸。在實(shí)際業(yè)務(wù)中,簡(jiǎn)單的加減乘除往往不能滿足工作的需求,今天就通過5個(gè)案例,給大家分享在眾多的數(shù)據(jù)中按特定的條件挑選并對(duì)數(shù)據(jù)求和、按月匯總數(shù)據(jù)等最有用的函數(shù)求和技巧。
案例1:如何按照數(shù)據(jù)區(qū)間匯總求和
下圖為某企業(yè)員工的工資表,現(xiàn)需要匯總月薪在4000至6000的工資總額。
可以使用公式=SUMIFS(D2:D20,D2:D20,">=4000",D2:D20,"<=6000")進(jìn)行統(tǒng)計(jì)。
數(shù)據(jù)區(qū)間實(shí)際上是兩個(gè)條件,本例中利用SUMIFS函數(shù)支持多條件求和的特性,設(shè)置條件1為">=4000",條件2為"<=6000",就可以求出月薪在4000至6000的工資總額。
案例2:按照不同的權(quán)重系數(shù)計(jì)算綜合得分
下圖為某企業(yè)員工的績(jī)效考評(píng)評(píng)分表,其中B3:G3單元格區(qū)域?yàn)楦黜?xiàng)目考評(píng)權(quán)重,員工的綜合得分等于各項(xiàng)評(píng)分與對(duì)應(yīng)考評(píng)權(quán)重的乘積之和。
可以使用公式=SUMPRODUCT($B$3:$G$3,B4:G4)計(jì)算出每位員工的總分。
本例利用SUMPRODUCT函數(shù)支持參數(shù)數(shù)組對(duì)應(yīng)元素相乘并求和的特性,將考評(píng)系數(shù)與員工的評(píng)分對(duì)應(yīng)相乘并求和,得出員工的綜合得分。
案例3:驗(yàn)證多級(jí)匯總的數(shù)據(jù)勾稽關(guān)系是否正確
在處理審計(jì)、財(cái)務(wù)核算等業(yè)務(wù)時(shí),經(jīng)常需要驗(yàn)算財(cái)務(wù)報(bào)表或多級(jí)項(xiàng)目的數(shù)據(jù)勾稽關(guān)系。
下圖為一份現(xiàn)金流量表,金額數(shù)據(jù)是由各級(jí)代碼的關(guān)系進(jìn)行逐級(jí)匯總的,現(xiàn)需要用代碼來(lái)驗(yàn)算現(xiàn)金流量表內(nèi)部各級(jí)項(xiàng)目金額數(shù)據(jù)的勾稽關(guān)系是否正確。
對(duì)于這類問題首先需要使用SUMIF函數(shù)匯總項(xiàng)目下級(jí)代碼對(duì)應(yīng)的金額,然后將匯總所得數(shù)據(jù)與C列金額進(jìn)行比較判斷。
驗(yàn)算時(shí)使用公式:=SUMIF(A:A,A2&REPT("?",6-LEN(A2)),C:C)
判斷是否正確使用公式:=C2=D2,結(jié)果為FALSE的就是有錯(cuò)誤的數(shù)據(jù)。
這個(gè)例子利用了SUMIF函數(shù)可以支持通配符的原理,用REPT("?",6-LEN(A2))生成一定位數(shù)的通配符,從而實(shí)現(xiàn)了按代碼級(jí)別進(jìn)行匯總的效果,進(jìn)一步與原有金額進(jìn)行比較找出有錯(cuò)誤的數(shù)據(jù)。
案例4:按月匯總數(shù)據(jù)
在按日期進(jìn)行數(shù)據(jù)分析時(shí),有時(shí)只需要匯總其中某個(gè)月的數(shù)據(jù)做同期比較。例如下圖為某批發(fā)公司在2019年全年銷售記錄,現(xiàn)要求出其中6月份的銷售量,可以使用公式=SUMPRODUCT((MONTH(D2:D100)=H2)*F2:F100)進(jìn)行匯總。
本例中使用MONTH函數(shù)求出D列日期的月份,然后與H3單元格的月份進(jìn)行比較判斷,再乘以F列對(duì)應(yīng)的數(shù)據(jù),最后使用SUMPRODUCT函數(shù)對(duì)數(shù)組求和即可得出6月份的銷售量。
案例5:多條件匯總
在日常工作中,經(jīng)常需要根據(jù)某些條件進(jìn)行數(shù)據(jù)匯總。
下圖所示為某公司員工基本情況登記表,現(xiàn)在需要統(tǒng)計(jì)性別為“女”、學(xué)歷為“本科”的員工的工資總和,可以使用公式=SUMIFS(H3:H18,C3:C18,"男",F3:F18,"本科")
SUMIFS函數(shù)可以設(shè)置多達(dá)128個(gè)區(qū)域/條件對(duì)對(duì)單元格區(qū)域進(jìn)行求和,本例中只應(yīng)用了其中的兩個(gè)區(qū)域/條件對(duì),即“性別/女”、“學(xué)歷/本科”,然后對(duì)“工資”列進(jìn)行條件求和,即可得出性別為“女”、學(xué)歷為“本科”的員工的工資總和。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù),非得用VBA才能實(shí)現(xiàn)嗎?
Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬(wàn)能!
版權(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收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(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ù)