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

LOGO 2020年3月17日 函数 函数定義 预先定义 执行计算 分析等处理数据任务的特殊公式 目的 提高效率 一天完成的工作量 也許只需10分鐘就可完成 提高品質方法 勤思考多練習多請教 目錄 案例分析 VLOOKUPDAYS360DATEDIFNetworkdaysRoundLEFT MID RIGHT 其它常用公式解析 資料剖析 IF倒置HOUR MINUTE設定格式化的條件DATEDIF VLOOKUP 案例1 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 解決方案 取值 LEFTMIDRIGHT作用 取指定範圍內的值公式 LEFT 指定儲存格 取幾位 MID 指定儲存格 從第幾位開始取 取幾位 RIGHT 指定儲存格 取幾位 LEFT MID RIGHT 資料剖析 案例7 在提交投保資料給社保局時 現有出生年月日在一個儲存格中 但要求年月日分別在三個儲存格中 如何操作 一個一個的分家 解決方案 資料剖析作用 將相似的資料分開 資料剖析 案例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作用 時間差值引申計算 公式 DATED