Excel第六章在人力資源與行政管理中的應(yīng)用Excel第六章在人力資源CONTENTS01制作考勤表02出勤情況分析03不同部門工資增幅變化目錄05計算銷售提成04制作、匯總工資表CONTENTS01制作考勤表02出勤情況分析03不同部門工26.1整理考勤機數(shù)據(jù)考勤機數(shù)據(jù)雖然市面上的考勤機種類非常多，功能也越來越強大，但是實際應(yīng)用中，仍然需要人力資源管理人員對請假、遲到、早退、未打卡等一些異常情況進行單獨處理，最終以考勤機的打卡時間為依據(jù)，輔以請休假等特殊情況的記錄生成考勤表。6.1整理考勤機數(shù)據(jù)考勤機數(shù)據(jù)雖然市面上的考勤機種類非常多36.1.1 統(tǒng)計出勤狀態(tài)和早退遲到時間出勤狀態(tài)公式=IF((D2-INT(D2)<=--"9:00")+(D2-INT(D2)>=--"18:00"),"正常","遲到/早退")日期和時間數(shù)據(jù)日期和時間混合型的數(shù)據(jù)，實際上是由整數(shù)組成的日期序列值和由小數(shù)組成的時間序列值兩部分組成。6.1.1 統(tǒng)計出勤狀態(tài)和早退遲到時間出勤狀態(tài)公式日期和時間4統(tǒng)計遲到早退次數(shù)篩選不重復(fù)記錄6.1.2 制作加班統(tǒng)計表統(tǒng)計遲到早退次數(shù)篩選不重復(fù)記錄6.1.2 制作加班統(tǒng)計表5公式的復(fù)制與填充方法1 拖曳填充柄。單擊輸入公式的單元格，指向該單元格右下角，當(dāng)鼠標指針變?yōu)楹谏啊弊中吞畛浔鷷r，按住鼠標左鍵向下拖曳至目標單元格。方法2 雙擊填充柄。單擊輸入公式的單元格，雙擊該單元格右下角的填充柄，公式將向下填充到當(dāng)前單元格所位于的不間斷區(qū)域的最后一行。方法3 填充公式。在一個單元格中輸入公式后，然后選中需要輸入相同公式的單元格區(qū)域，按<Ctrl+D>組合鍵或單擊【開始】選項卡的【填充】下拉按鈕，在擴展菜單中單擊【向下】按鈕。公式的復(fù)制與填充方法1 拖曳填充柄。6按指定條件求和SUMIF函數(shù)第一參數(shù)用于判斷條件的單元格區(qū)域。第二參數(shù)用于確定求和的條件。第三參數(shù)是要求和的實際單元格區(qū)域。其語法可以理解為：SUMIF(條件判斷區(qū)域,求和條件,求和區(qū)域)求和條件使用通配符按指定條件求和SUMIF函數(shù)求和條件使用通配符7按多個條件求和SUMIFS函數(shù)用法：SUMIFS(求和區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2,……)多求和條件使用通配符按多個條件求和SUMIFS函數(shù)用法：多求和條件使用通配符8除了使用考勤機打卡記錄員工的考勤之外，還有部分企業(yè)會使用“考勤表”的形式記錄員工出勤。設(shè)計合理的考勤表不僅能夠直觀顯示員工的出勤狀況，還可以減少統(tǒng)計人員的工作量。6.2用Excel制作考勤表考勤表除了使用考勤機打卡記錄員工的考勤之外，還有部分企業(yè)會使用“考9常用日期類函數(shù)DAY函數(shù)返回某個日期的在一個月中的天數(shù)，結(jié)果是介于1到31之間的整數(shù)。YEAR函數(shù)返回對應(yīng)于某個日期的年份，結(jié)果為1900~9999之間的整數(shù)。MONTH函數(shù)返回日期中的月份，結(jié)果是介于1（一月）到12（十二月）之間的整數(shù)。DATE函數(shù)可以根據(jù)指定的年份數(shù)、月份數(shù)和一月中的天數(shù)返回日期序列。EOMONTH函數(shù)返回指定日期之前或之后指定月份數(shù)的月末日期。WEEKDAY函數(shù)返回對應(yīng)于某個日期的一周中的第幾天。常用日期類函數(shù)DAY函數(shù)10一個月結(jié)束后，通常需要制作一份月度出勤匯總表，對員工的出勤情況進行匯總后張貼公示或提請領(lǐng)導(dǎo)簽字后再以此制作工資表。6.3制作月度出勤匯總表匯總表效果一個月結(jié)束后，通常需要制作一份月度出勤匯總表，對員工的出勤情116.4部門出勤情況分析統(tǒng)計部門出勤情況時，通常會統(tǒng)計部門的出勤率或是缺勤率，部門出勤率的計算方法為部門實際出勤天數(shù)之和除以應(yīng)出勤天數(shù)之和*100%。部門出勤率分析表平均出勤率分析表6.4部門出勤情況分析統(tǒng)計部門出勤情況時，通常會統(tǒng)計部門的126.5 制作職工帶薪年休假申請審批單凡連續(xù)工作1年以上的均可以享受帶薪年休假，職工在休假期間享受與正常工作期間相同的工資收入。累計工作滿1年不滿10年的，年休假5天；滿10年不滿20年的，年休假10天；滿20年的年休假15天。職工年休假記錄臺帳帶薪年休假申請審批單6.5 制作職工帶薪年休假申請審批單凡連續(xù)工作1年以上的均可136.6 用圖表展示不同部門工資增幅變化年度工資結(jié)構(gòu)分析通常包括薪資增長狀況、不同薪資結(jié)構(gòu)對比、不同職位和級別的薪資數(shù)據(jù)、獎金福利狀況、長期激勵措施以及未來薪資走勢分析等。本節(jié)以工資增幅數(shù)據(jù)為例，學(xué)習(xí)使用圖表展示不同部門工資增幅變化的情況。不同部門工資增幅變化6.6 用圖表展示不同部門工資增幅變化年度工資結(jié)構(gòu)分析通常包146.7 使用條件格式展示工資增幅變化使用圖表展示數(shù)據(jù)的優(yōu)點是效果美觀，但是其操作步驟較多，剛剛學(xué)習(xí)Excel的用戶往往不容易掌握，可以使用條件格式實現(xiàn)類似的效果。不同部門工資增幅變化6.7 使用條件格式展示工資增幅變化使用圖表展示數(shù)據(jù)的優(yōu)點是156.8 制作工資表企業(yè)有每月為職工代扣、代繳工資薪金所得部分個人所得稅的義務(wù)。根據(jù)有關(guān)法規(guī)：工資薪金所得以每月收入額減除費用3500元后的余額為應(yīng)納稅所得額，即：

月應(yīng)納稅所得額=月工資收入額-3500（基數(shù)）

月應(yīng)納稅額=月應(yīng)納稅所得額×適用稅率-速算扣除數(shù)個人所得稅稅率表級數(shù)含稅級距稅率(%)計算公式速算扣除數(shù)1不超過1500元的3T=(A-3500)*3%-002超過1500元至4500元的部分10T=(A-3500)*10%-1051053超過4500元至9000元的部分20T=(A-3500)*20%-5555554超過9000元至35000元的部分25T=(A-3500)*25%-100510055超過35000元至55000元的部分30T=(A-3500)*30%-277527756超過55000元至80000元的部分35T=(A-3500)*35%-550555057超過80000元的部分45T=(A-3500)*45%-1350513505本表含稅級距指以每月收入額減除費用3500元后的余額或者減除附加減除費用后的余額。6.8 制作工資表企業(yè)有每月為職工代扣、代繳工資薪金所得部分166.8.2計算個人所得稅個稅計算公式=ROUND(MAX((應(yīng)稅工資-3500)*{3;10;20;25;30;35;45}%-{0;105;555;1005;2755;5505;13505},0),2)6.8.2計算個人所得稅個稅計算公式176.9 分類匯總工資表在月度工資表中往往需要按照部門統(tǒng)計當(dāng)月工資的小計，使用分類匯總功能可以簡化統(tǒng)計工作量，提高工作效率。不同部門工資增幅變化部門分類匯總統(tǒng)計打印不同匯總結(jié)果高亮顯示部門小計6.9 分類匯總工資表在月度工資表中往往需要按照部門統(tǒng)計當(dāng)月186.10每頁帶小計的工資表如果工資表的數(shù)據(jù)量比較多，當(dāng)需要打印時，在每一頁的最后加上本頁小計，在最后一頁加上總計，能夠增加表格的可讀性。類似的打印效果在一些財務(wù)軟件中已經(jīng)實現(xiàn)了自動化，用Excel自帶的功能也可以實現(xiàn)這樣的效果。設(shè)置后的打印預(yù)覽效果6.10每頁帶小計的工資表如果工資表的數(shù)據(jù)量比較多，當(dāng)需要196.11按部門拆分工作表如果將工資表中的內(nèi)容按不同的部門分別拆分到不同工作表內(nèi)，能夠便于查詢和打印。本節(jié)學(xué)習(xí)按部門拆分工資表的技巧。顯示報表篩選頁6.11按部門拆分工作表如果將工資表中的內(nèi)容按不同的部門分206.12批量制作員工工資條工資條是單位定期給員工反映工資的憑據(jù)，記錄著員工的月收入分項和收入總額，通常包括工號、職工姓名、基本工資、獎金福利、代繳保險、應(yīng)發(fā)工資、個人所得稅和實發(fā)工資等項目。

工資條的制作可通過多種方法來實現(xiàn)，主要有排序法、函數(shù)法以及郵件合并法等。工資條效果6.12批量制作員工工資條工資條是單位定期給員工反映工資的216.13計算銷售提成銷售提成計算主要包括以下四種：

1、純提成制，銷售人員的工資全部由提成構(gòu)成，不發(fā)放固定的基本工資。

2、底薪+提成制，銷售人員的工資由基本工資和業(yè)績提成兩部分構(gòu)成。

3、底薪+提成+獎金制，銷售人員除了底薪和業(yè)績提成外，還會