關鍵字:數據分析;Sumifs
欄目:函數
哈喽,小夥伴們,你們好呀~
數據彙總求和是數據分析當中最基本的操作,對于簡單的加減乘除我們都了然于胸。
但是在實際業務中,簡單的加減乘除往往不能滿足工作的需求。
今天就通過5個案例,給大家分享在衆多的數據中按特定的條件挑選并對數據求和、按月彙總數據等最有用的函數求和技巧。
案例1:如何按照數據區間彙總求和
下圖為某企業員工的工資表,現需要彙總月薪在4000至6000的工資總額。
可以使用公式=SUMIFS(D2:D20,D2:D20,">=4000",D2:D20,"<=6000")進行統計。
數據區間實際上是兩個條件,本例中利用SUMIFS函數支持多條件求和的特性,設置條件1為">=4000",條件2為"<=6000",就可以求出月薪在4000至6000的工資總額。
案例2:按照不同的權重系數計算綜合得分
下圖為某企業員工的績效考評評分表,其中B3:G3單元格區域為各項目考評權重,員工的綜合得分等于各項評分與對應考評權重的乘積之和。
可以使用公式=SUMPRODUCT($B$3:$G$3,B4:G4)計算出每位員工的總分。
本例利用SUMPRODUCT函數支持參數數組對應元素相乘并求和的特性,将考評系數與員工的評分對應相乘并求和,得出員工的綜合得分。
案例3:驗證多級彙總的數據勾稽關系是否正确
在處理審計、财務核算等業務時,經常需要驗算财務報表或多級項目的數據勾稽關系。
下圖為一份現金流量表,金額數據是由各級代碼的關系進行逐級彙總的,現需要用代碼來驗算現金流量表内部各級項目金額數據的勾稽關系是否正确。
對于這類問題首先需要使用SUMIF函數彙總項目下級代碼對應的金額,然後将彙總所得數據與C列金額進行比較判斷。
驗算時使用公式:=SUMIF(A:A,A2&REPT("?",6-LEN(A2)),C:C)
判斷是否正确使用公式:=C2=D2,結果為FALSE的就是有錯誤的數據。
這個例子利用了SUMIF函數可以支持通配符的原理,用REPT("?",6-LEN(A2))生成一定位數的通配符,從而實現了按代碼級别進行彙總的效果,進一步與原有金額進行比較找出有錯誤的數據。
案例4:按月彙總數據
在按日期進行數據分析時,有時隻需要彙總其中某個月的數據做同期比較。例如下圖為某批發公司在2019年全年銷售記錄,現要求出其中6月份的銷售量,可以使用公式=SUMPRODUCT((MONTH(D2:D100)=H2)*F2:F100)進行彙總。
本例中使用MONTH函數求出D列日期的月份,然後與H3單元格的月份進行比較判斷,再乘以F列對應的數據,最後使用SUMPRODUCT函數對數組求和即可得出6月份的銷售量。
案例5:多條件彙總
在日常工作中,經常需要根據某些條件進行數據彙總。
下圖所示為某公司員工基本情況登記表,現在需要統計性别為“女”、學曆為“本科”的員工的工資總和,可以使用公式:
=SUMIFS(H3:H18,C3:C18,"男",F3:F18,"本科")
SUMIFS函數可以設置多達128個區域/條件對對單元格區域進行求和,本例中隻應用了其中的兩個區域/條件對,即“性别/女”、“學曆/本科”,然後對“工資”列進行條件求和,即可得出性别為“女”、學曆為“本科”的員工的工資總和。
好啦,以上便是今天的5個數據分析公式啦!
躍躍欲試?不妨打開你的Excel,操作一番!
有什麼新發現新用法,歡迎留言分享!
有疑問的同學,歡迎大家進我們的讀者群交流讨論~ |
有話要說...