人资常用EXCEL公式与使用技巧

,LOGO,人資常用,Excel,公式,&,技巧,*,*,*,函数,函数,定義,:,预先定义，执行计算、分析等处理数据任务的特殊公式,目的,:,提高效率,(,一天完成的工作量,也許只需,10,分鐘就可完成,),提高品質,方法,:,勤思考,多練習,多請教,目錄,案例分析,VLOOKUP,DAYS360,DATEDIF,Networkdays,Round,LEFT&MID&RIGHT,其它常用公式解析,資料剖析,&,IF,倒置,HOUR&MINUTE,設定格式化的條件,DATEDIF,VLOOKUP,案例,1,：,A,單位有,1,200,人,提報其中,700,人參加動員大會,提報格式為,工號,姓名,部門,提報完成后,單位主管要知道這些人員的資位和管理職,.,如何操作,?,一個一個的查找,?,VLOOKUP,A,單位年終績效考核時,總應考核人數為,1,200,人,實際收回考核結果的只有,1,195,人,.,如何知道這,1,195,人就是,1,200,人中的人,?,如何知道差哪些人沒有績效考核,?,解決,方案,:,關連公式,(VLOOKUP),作用,:,補充完整資料,核對資料的正確性和找出差異,公式,:,=VLOOKUP(,參照物,目標檔案,需要的資料,),VLOOKUP,DAYS360,現有人事資料中往往有員工的出生日期,但主管要看的是員工的年齡,.,如何操作,?,用計算器一個一個的計算,?,案例,2,：,解決,方案,:,日期計算,:DAYS360,作用,:,計算年齡或年資公式,公式,:,=DAYS360(,起點日期,結束日期,FALSE)/360,DAYS360,案例,3,：,DATEDIF,在發放某种津貼時,要以員工實際出差天數來計算,(,如,10,元,/,天,).,如何操作,?,扳手指,?,解決,方案,:,日期計算,:,DATEDIF,作用,:,計算天數,公式,:,=,DATEDIF(,起點日期,結束日期,D),DATEDIF,Networkdays,2007,年,10,月份新進,1,200,名員工,(,入廠日期不同,),薪資專員先要計算出這些人員從入廠日期至月底周一至周五的天數,(,周六周日不計薪,),以便計算每一名員工薪資,.,如何操作,?,拿著台歷一個一個的數,?,案例,4,：,解決,方案,:,工作日,計算,:Networkdays,作用,:,求兩個日期間周一至周五的天數,公式,:,=Networkdays(,前一日期,后一日期,),Networkdays,P.S,先要在,Excel,選項,/,增益集,/,分析工具箱選上,案例,5,：,在動員大會上要按一定比例分配各單位參加人數時,雖然可以用一般的四舍五入的辦法保留整數,(,事實上是小數,),可能對最終加總有影響,從主管的角度來看不可理解,.,分火車票時也是如此,!,如何操作,?,手工算,一個一個的輸入電腦,?,在摸彩晚會上設置中獎人數和預算獎金時,根本不能設置成中獎人數有小數,(,用一般的四舍五入的辦法保留整數也不可以,),ROUND,解決,方案,:,取絕對數字,:ROUND,作用,:,按四舍五入的原則取絕對位數的值,公式,:,=ROUND(,某一數字或計算結果,位數,),ROUND,P.S =TRUNC,將小數絕對舍去,案例,6,：,往往社保局的資料中只有身份證號碼,沒有出身日期,要知道每人 出生日期,.,如何操作,?,用肉眼看,?,然后再輸入電腦,?,LEFT&MID&RIGHT,解決,方案,:,取值,:LEFT,MID,RIGHT,作用,:,取指定範圍內的值,公式,:,=LEFT(,指定儲存格,取幾位,),=MID(,指定儲存格,從第幾位開始取,取幾位,),=RIGHT(,指定儲存格,取幾位,),LEFT&MID&RIGHT,資料剖析,案例,7,：,在提交投保資料給社保局時,現有出生年月日在一個儲存格中,但要求年月日分別在三個儲存格中,.,如何操作,?,一個一個的分家,?,現有格式,需達成之結果,日期格式,年,月,日,1996/6/24,1996,6,24,1996/9/11,1996,9,11,1996/11/2,1996,11,2,1996/11/11,1996,11,11,解決,方案,:,資料剖析,作用,:,將相似的資料分開,資料剖析,案例,8,：,在儲存格單位名稱前全部加上相同的上級單位名稱,.,如何操作,?,一個一個的加上去,?,兩個儲存格的內容要放在一個儲存格內,.,如何操作,?,一個一個的操作,?,&,解決,方案,:,連接,：,&,作用,:,兩個儲存格的內容放在一個儲存格內,公式,:,=,儲存格,&,儲存格,&,案例,9,：,IF,A,單位總共有,1,200,人,現各課將,07,年的績效考核結果傳給行政助理匯總,行政助理 用肉眼發現有的姓名是錯,&,人員,重複的,.,如何快速找到姓名錯誤的員工,&,重複的員工,?,社保專員在預提每人社保費用時,要依據員工,深戶,/,非深戶,/,月薪,(,上下限,),這些條件分別計算出,公司付養老,/,個人付養老,/,公司付醫療,/,個人付醫療,/,公司付生育,/,個人付生育,/,公司付工傷,/,個人付工傷,/,公司付失業,/,個人付失業,.,IF,解決,方案,:,假設公式,:IF,作用,:,根據設定的條件判定結果,公式,:,=IF(,判斷運算式，真時顯示，假時顯示）,IF,案例,10,：,在制作報表時,往往可能遇到橫欄和豎欄的抬頭要互換位置,.,如何操作,?,重新統計,?,倒置,解決,方案,:,復制,-,選擇性貼上,-,倒置,作用,:,報表欄和列的位置互換,案例,11,：,將遲到時間格式,01:56,轉換成數字格式,116,分鐘,HOUR&MINUTE,解決,方案,:,時間命令：,HOUR&MINUTE,作用,:,計算小時和分鐘,公式,:,=HOUR()*60+MINUTE(),設定格式化的條件,在統計報表中,要將符合某种條件的數字用另外的一种形式,(,如紅色,/,底線字表示,).,用手工操作,?,案例,12,：,解決,方案,:,設定格式化的條件,作用,:,將符合某种條件的數字用另外的一种形式表示,案例,13,：,DATEDIF,SER,每天要知道未成工的名單,.,如何操作,?,每天都將年齡重新計算一次,?,解決,方案,:,時間公式：,DATEDIF,作用,:,時間差值引申計算,公式,:,=DATEDIF(,日期,1,，日期,2,，“,Y&M&D,”,),輕鬆一刻,其它公式分析,樞紐分析,合並列印,提取時間,隨機公式,指定文本匯總,字符串對比,其它公式分析,求小記,自動計數序列,定義名稱,數據隱藏與保護,插入數據,打印标题行,謝謝！,