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

《人资常用EXCEL公式与使用技巧.ppt》由会员分享，可在线阅读，更多相关《人资常用EXCEL公式与使用技巧.ppt（31页珍藏版）》请在三一办公上搜索。

1、,LOGO,2023年6月14日,函数,函数定義:,预先定义，执行计算、分析等处理数据任务的特殊公式,目的:提高效率(一天完成的工作量,也許只需10分鐘就可完成)提高品質方法:勤思考 多練習 多請教,目錄,案例分析,VLOOKUPDAYS360DATEDIFNetworkdaysRoundLEFT&MID&RIGHT,其它常用公式解析,資料剖析&IF倒置HOUR&MINUTE設定格式化的條件DATEDIF,VLOOKUP,案例1：,VLOOKUP,A單位年終績效考核時,總應考核人數為1,200人,實際收回考核結果的只有1,195人.如何知道這1,195人就是1,200人中的人?如何知道差哪些人

2、沒有績效考核?,解決方案:關連公式(VLOOKUP)作用:補充完整資料 核對資料的正確性和找出差異公式:=VLOOKUP(參照物,目標檔案,需要的資料),VLOOKUP,DAYS360,現有人事資料中往往有員工的出生日期,但主管要看的是員工的年齡.如何操作?用計算器一個一個的計算?,案例2：,解決方案:日期計算:DAYS360 作用:計算年齡或年資公式公式:=DAYS360(起點日期,結束日期,FALSE)/360,DAYS360,案例3：,DATEDIF,在發放某种津貼時,要以員工實際出差天數來計算(如10元/天).如何操作?扳手指?,解決方案:日期計算:DATEDIF 作用:計算天數公式:

3、=DATEDIF(起點日期,結束日期,D),DATEDIF,Networkdays,2007年10月份新進1,200名員工(入廠日期不同),薪資專員先要計算出這些人員從入廠日期至月底周一至周五的天數(周六周日不計薪),以便計算每一名員工薪資.如何操作?拿著台歷一個一個的數?,案例4：,解決方案:工作日計算:Networkdays 作用:求兩個日期間周一至周五的天數公式:=Networkdays(前一日期,后一日期),Networkdays,P.S 先要在Excel選項/增益集/分析工具箱選上,案例5：,在動員大會上要按一定比例分配各單位參加人數時,雖然可以用一般的四舍五入的辦法保留整數(事實上

4、是小數),可能對最終加總有影響,從主管的角度來看不可理解.分火車票時也是如此!如何操作?手工算,一個一個的輸入電腦?,在摸彩晚會上設置中獎人數和預算獎金時,根本不能設置成中獎人數有小數(用一般的四舍五入的辦法保留整數也不可以),ROUND,解決方案:取絕對數字:ROUND 作用:按四舍五入的原則取絕對位數的值公式:=ROUND(某一數字或計算結果,位數),ROUND,P.S=TRUNC 將小數絕對舍去,案例6：,往往社保局的資料中只有身份證號碼,沒有出身日期,要知道每人 出生日期.如何操作?用肉眼看?然后再輸入電腦?,LEFT&MID&RIGHT,解決方案:取值:LEFTMIDRIGHT作用:

5、取指定範圍內的值公式:=LEFT(指定儲存格,取幾位)=MID(指定儲存格,從第幾位開始取,取幾位)=RIGHT(指定儲存格,取幾位),LEFT&MID&RIGHT,資料剖析,案例7：,在提交投保資料給社保局時,現有出生年月日在一個儲存格中,但要求年月日分別在三個儲存格中.如何操作?一個一個的分家?,解決方案:資料剖析作用:將相似的資料分開,資料剖析,案例8：,在儲存格單位名稱前全部加上相同的上級單位名稱.如何操作?一個一個的加上去?,兩個儲存格的內容要放在一個儲存格內.如何操作?一個一個的操作?,&,解決方案:連接：&作用:兩個儲存格的內容放在一個儲存格內公式:=儲存格&儲存格,&,案例9：

6、,IF,A單位總共有1,200人,現各課將07年的績效考核結果傳給行政助理匯總,行政助理 用肉眼發現有的姓名是錯&人員重複的.如何快速找到姓名錯誤的員工&重複的員工?,社保專員在預提每人社保費用時,要依據員工深戶/非深戶/月薪(上下限)這些條件分別計算出公司付養老/個人付養老/公司付醫療/個人付醫療/公司付生育/個人付生育/公司付工傷/個人付工傷/公司付失業/個人付失業.,IF,解決方案:假設公式:IF作用:根據設定的條件判定結果公式:=IF(判斷運算式，真時顯示，假時顯示）,IF,案例10：,在制作報表時,往往可能遇到橫欄和豎欄的抬頭要互換位置.如何操作?重新統計?,倒置,解決方案:復制-選

7、擇性貼上-倒置作用:報表欄和列的位置互換,案例11：,將遲到時間格式 01:56轉換成數字格式116分鐘,HOUR&MINUTE,解決方案:時間命令：HOUR&MINUTE 作用:計算小時和分鐘,公式:=HOUR()*60+MINUTE(),設定格式化的條件,在統計報表中,要將符合某种條件的數字用另外的一种形式(如紅色/底線字表示).用手工操作?,案例12：,解決方案:設定格式化的條件 作用:將符合某种條件的數字用另外的一种形式表示,案例13：,DATEDIF,SER每天要知道未成工的名單.如何操作?每天都將年齡重新計算一次?,解決方案:時間公式：DATEDIF 作用:時間差值引申計算,公式:=DATEDIF(日期1，日期2，“Y&M&D”),輕鬆一刻,其它公式分析,樞紐分析,合並列印,提取時間,隨機公式,指定文本匯總,字符串對比,其它公式分析,求小記,自動計數序列,定義名稱,數據隱藏與保護,插入數據,打印标题行,謝謝！,