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