三類最常見的統(tǒng)計問題,三個套路全搞定!
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2019-06-26 10:20:52點擊:4804
編按:
哈嘍,大家好!在日常的辦公中,我們經(jīng)常會統(tǒng)計excel里各種數(shù)據(jù)。在excel里關(guān)于統(tǒng)計的函數(shù)也是數(shù)不勝數(shù),SUM、SUIMIF、SUMIFS、COUNT、COUNTIFS等等。今天我們總結(jié)了三類小伙伴們經(jīng)常遇到的統(tǒng)計問題,也將分享三種對應(yīng)的解決方法,以后再面對這三類統(tǒng)計問題,就再也不怕啦~
善于在工作中使用函數(shù)、公式可以提高工作效率,結(jié)合近期學(xué)員們遇到的問題,老菜鳥總結(jié)了三個非常實用的公式,每個公式都可以解決一類問題。學(xué)會這三個公式套路,就能解決日常遇到的很多麻煩事。
第一類問題:對指定時間段的數(shù)據(jù)進行匯總
例如在一組銷售數(shù)據(jù)中,需計算出2018年4月1日至2018年6月30日期間的銷售額合計??梢允褂霉?span>=SUMIFS(B:B,A:A,">=2018-4-1",A:A,"<=2018-6-30")得到所需要的結(jié)果。
在這個公式中,用到了SUMIFS函數(shù)進行匯總。SUMIFS是一個多條件求和的函數(shù),基本格式為SUMIFS(要求和的區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2,……)。
本例中要求和的區(qū)域是銷售額所在的B列,條件區(qū)域是銷售日期所在的A列,具體的條件有兩個:
條件1:大于等于開始日期2018年4月1日。
條件2:小于等于結(jié)束日期2018年6月30日。
需要注意的是:公式中使用日期作為條件時,一定要在表示日期的字符串兩端加上英文狀態(tài)下的半角引號。如果需要用到比較符號,則需要將符號與日期同時放在引號中間。
掌握這個套路之后,再遇到按日期區(qū)間求和的問題時,只需要選擇對應(yīng)的求和區(qū)域、條件區(qū)域和起止日期就能完成統(tǒng)計。
第二類問題:按照多個指定的數(shù)據(jù)區(qū)間統(tǒng)計人數(shù)
例如需要按照成績劃分為四個區(qū)間,并統(tǒng)計出每個區(qū)間內(nèi)的人數(shù)??梢允褂霉?span>=FREQUENCY($B$2:$B$17,{60,75,90}-0.01)得到所需結(jié)果。
通常遇到這類問題我們首先想到的是COUNTIF或是COUNTIFS函數(shù),但它們都需要對每個區(qū)間進行設(shè)置,比較繁瑣。
實際上在Excel的函數(shù)中,有個專門統(tǒng)計區(qū)間內(nèi)數(shù)據(jù)個數(shù)的函數(shù)——FREQUENCY。
函數(shù)的基本格式為FREQUENCY(數(shù)據(jù)區(qū)域,臨界點)。在本例中,數(shù)據(jù)區(qū)域是成績所在的單元格區(qū)域B2:B17。統(tǒng)計結(jié)果劃分了四個區(qū)間,相當(dāng)于有了三個臨界點60、75和90。
由于這個函數(shù)得到的是一個數(shù)組結(jié)果,所以必須同時按Ctrl+Shift+回車鍵完成數(shù)組公式的輸入。
如果統(tǒng)計數(shù)據(jù)中正好存在與臨界點相同的數(shù)據(jù),則該數(shù)據(jù)會計入靠前的區(qū)間。例如成績中有90分的話,會計入第三個區(qū)間“大于等于75且小于90”:
這與本例的統(tǒng)計需求不符,因此將第二參數(shù)-0.01,對臨界點進行修正。
掌握FREQUENCY的用法需要注意兩點:
1、這是一個數(shù)組函數(shù),需要先選中統(tǒng)計結(jié)果所在的單元格區(qū)域,在再編輯欄輸入公式。公式輸入完畢后按住Ctrl+Shift+回車鍵,一次得到全部統(tǒng)計結(jié)果;
2、臨界值(FREQUENCY函數(shù)的第二參數(shù))可以是單元格區(qū)域,也可以是常量數(shù)組的方式存在。同時還需要根據(jù)具體情況對臨界值進行修正以確保統(tǒng)計結(jié)果的正確性。
第三類問題:工齡工資的計算問題
最后一類問題是工齡工資的計算。例如企業(yè)規(guī)定員工每滿一年加100元工齡工資,15年封頂??梢允褂霉?span>=100*MIN(15,DATEDIF(B2,C2,"y"))計算出每個人的工齡工資。
在這個公式中,用到了MIN和DATEDIF函數(shù)。
DATEDIF函數(shù)的功能是計算員工的工齡,函數(shù)格式為DATEDIF(開始日期,結(jié)束日期,統(tǒng)計方式)。本例中開始日期就是每個人的入職日期,結(jié)束日期為計算日期(也可以使用TODAY()函數(shù)作為結(jié)束日期),統(tǒng)計方式“y”表示以年為單位進行計算。關(guān)于DATEDIF函數(shù)的詳細用法,小伙伴們可以參考之前的教程《用上DATEDIF,您永不再缺席那些重要的日子!》。
MIN函數(shù)的功能是得到一組數(shù)據(jù)中的最小值,函數(shù)格式為MIN(數(shù)據(jù)區(qū)域)或MIN(數(shù)據(jù)1,數(shù)據(jù)2,……)。本例使用的是第二種用法,作用是得到工齡與15中的較小值。這就可以實現(xiàn)當(dāng)工齡超過15年時,還是以15年來計算。
在計算上限時往往可以使用MIN函數(shù)代替IF函數(shù),同理計算下限時可以使用MAX函數(shù)代替IF函數(shù),公式會更加簡潔。
最后使用100乘上有效工齡就得到了所需的結(jié)果。
本例給了大家一個非常重要的啟示就是MIN函數(shù)在限制上限時的用法,可以借鑒到一些績效計算,提成計算等方面。
今天分享的三個公式涉及到三類完全不相干但是非常有代表性的統(tǒng)計問題,如果能夠做到舉一反三,那就能解決日常遇到的很大一部分問題了,而這也是學(xué)習(xí)Excel函數(shù)、公式的目的所在。
今后我們會繼續(xù)針對有代表性的案例進行分享,帶領(lǐng)大家學(xué)以致用,提高效率。如果你有什么好的案例,也歡迎在評論區(qū)留言告訴我們。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
DATEDIF函數(shù)《用上DATEDIF,您永不再缺席那些重要的日子!》
SUMIF函數(shù)竟然出錯了?《無往而不利的SUMIF面對這種條件求和竟然傻眼了!》
基本的數(shù)組概念《加了*的 SUMPRODUCT函數(shù)無所不能》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!