如何拯救HR?用Excel做一張在職員工統(tǒng)計表
?
作者:郅龍來源:部落窩教育發(fā)布時間:2021-08-16 10:26:02點擊:6387
編按:
制作人員統(tǒng)計表是HR的工作之一。性別、學歷、司齡、工齡、員工人數(shù)等是構(gòu)成人員統(tǒng)計表的基本元素,如何在繁雜的系統(tǒng)導出信息的基礎上做一張簡潔明了的人員統(tǒng)計表,我們一起來學習一下~
小李是公司人資部的專員,平時負責在人事系統(tǒng)中維護員工檔案,也會做一些和人員信息相關的數(shù)據(jù)統(tǒng)計工作,比如下圖這樣的在職人員統(tǒng)計表。
這樣的統(tǒng)計表往往需要從系統(tǒng)中導出人員明細,然后用Excel加工制作,導出的明細表往往有好幾十列,看上去密密麻麻的。
如何利用這樣的一份數(shù)據(jù)源做成需要的統(tǒng)計表呢?下面一步一步和大家介紹。
首先是基礎數(shù)據(jù)整理,從需要的結(jié)果來看,需要用到以下信息:部門,性別,學歷,年齡和工齡,因此只保留這五列信息就夠了,其他的可以都刪掉。
整理后的數(shù)據(jù)源如圖所示:
是不是清爽了很多?
相對于統(tǒng)計結(jié)果來說,年齡和司齡是用了區(qū)間統(tǒng)計法,因此還需要對數(shù)據(jù)源加工一下,把年齡段和司齡段填進去。
年齡區(qū)間分成了三段,25歲以下、25-35歲、35歲以上,用兩個IF嵌套或者用LOOKUP函數(shù)都可以實現(xiàn)。
IF嵌套:=IF(D2<25,"25歲以下",IF(D2<35,"25-35歲","35歲以上"))
LOOKUP函數(shù):=LOOKUP(D2,{0,25,35},{"25歲以下","25-35歲","35歲以上"})
兩個公式的結(jié)果是一樣的,對于IF嵌套的用法,之前的教程有過介紹,也屬于比較基礎的知識了,這里就不再啰嗦了。
關于LOOKUP函數(shù)在這里的用法,我們結(jié)合司齡區(qū)間的公式詳細解釋一下。
司齡的劃分比工齡復雜一些,分成了半年以下、半年-1年、1-2年、2-3年、3年以上五個區(qū)間,如果用IF嵌套的話就得4個IF,比較麻煩,推薦使用LOOKUP函數(shù)來實現(xiàn)。
對于新手來說,直接用LOOKUP做區(qū)間引用是有難度的,下面介紹一個比較容易學會的方法。
在表格的空白處做一個對照表(如下圖)。司齡段是按照實際統(tǒng)計的需要填寫,關鍵是司齡下限的填寫,表示的是每個司齡段所對應的司齡的最小值。
有了這樣一個對照表,再來寫LOOKUP的公式就非常簡單。
公式為:=LOOKUP(E2,$K$2:$L$6)
完成后選中公式中的$K$2:$L$6,按一下F9鍵,公式會變成這樣的:
=LOOKUP(E2,{0,"半年以下";0.5,"半年-1年";1,"1-2年";2,"2-3年";3,"3年以上"})
這樣即使刪除輔助的對照表結(jié)果也不會受影響了。
至此我們完成了數(shù)據(jù)源的優(yōu)化,刪除了多余的無用信息,又添加了需要統(tǒng)計的信息。
現(xiàn)在就可以來完成統(tǒng)計表了。
經(jīng)過這樣處理的數(shù)據(jù)源,要形成最終的統(tǒng)計表只需要用到兩個函數(shù):COUNTIF和COUNTIFS。下面,分別來看看每個項目是如何使用公式的。
員工總數(shù):=COUNTIF(數(shù)據(jù)源!A:A,A4)
按照數(shù)據(jù)源中A列的部門,統(tǒng)計出匯總表中對應部門的人數(shù)。
性別:=COUNTIFS(數(shù)據(jù)源!$A:$A,$A4,數(shù)據(jù)源!$B:$B,C$3)
按性別統(tǒng)計時涉及到兩個條件,部門和性別,公式不難理解,注意公式中$的用法,因為這個公式既要考慮到下拉的情況,還要考慮右拉的情況,所以對于$混合引用的用法要求是比較高的。
學歷:=COUNTIFS(數(shù)據(jù)源!$A:$A,$A4,數(shù)據(jù)源!$C:$C,E$3)
學歷的統(tǒng)計與性別類似,只是將條件區(qū)域從B列改成C列,同樣需要注意$在公式中的作用。
年齡:=COUNTIFS(數(shù)據(jù)源!$A:$A,$A4,數(shù)據(jù)源!$F:$F,I$3)
工齡:=COUNTIFS(數(shù)據(jù)源!$A:$A,$A4,數(shù)據(jù)源!$G:$G,L$3)
因為在數(shù)據(jù)源有了年齡段和工齡段,年齡和工齡的統(tǒng)計就變得非常方便。
總結(jié)一下:很多同學在遇到問題的時候,往往忽視了對數(shù)據(jù)源的處理,直接拿著系統(tǒng)導出的數(shù)據(jù)就開始干活,干擾項太多不說,有時候數(shù)據(jù)源里缺少了什么東西也不清楚。所以按照最終統(tǒng)計的要求對數(shù)據(jù)源做精簡是非常有必要的。另一方面,是否有必要增加年齡段和工齡段,可能有的同學會說,不加這兩個也可以用公式直接統(tǒng)計的,這當然沒問題,但是公式就會更復雜一點。
最后,這個問題其實也可以用數(shù)據(jù)透視表來完成,不過數(shù)據(jù)透視表可能無法嚴格按照最終需要的順序來呈現(xiàn),總之是各有利弊。
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
版權(quán)申明:
本文作者郅龍;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!