一、建立基礎資訊表
建立如表1所爾的基礎資訊表,在單元格B2中輸入月份數,在單元格12中輸入出勤天數。根據考勤表輸入事假天數、病假天數、加班天數;根據上浮工資分配表輸入浮動工資,根據後勤部門統計的房租輸入房租。為防止輸入資料引起銀行卡 號誤差可以將“銀行卡 號”這一列隱藏。
基礎資訊工作表等
二、建立工資表
第一步,在單元格A1中輸入“=”南通××公司2006年“&基礎資訊!B2&“月上資表””,併合單元格A1:Q1,如表2所示。表2中月份資料隨“基礎資訊表”中月份變化而變化。
第二步,利用Excel不同表之間資料鏈接功能,在單元格A3、B3、G3、L3中分別輸入“=基礎資訊!A4”、“=基礎資訊!B4”、“=基礎資訊!G4”、“=基礎資訊!H4”,基礎資訊表中的部門、姓名、浮動工資、房租將自動出現在工資表中。利用Excel“填充柄”將其它各員工的有關資料自動產生。
第三步,將工資卡片中各員工的基本工資、崗位工資、綜合補貼等記錄,分別輸入單元格C3、D3、E3所在列。
第四步,根據公司加班政策,假設加一天班為20元,在單元格F3中輸入“=基礎資訊!F4*20”;根據事假扣除標準“(基本工資+崗位工資+綜合補貼)÷本月出勤天數×事假天數”,病假扣除標準“(基本工資+崗位工資+綜合補貼)÷本月出勤天數×事似天數-病假天數×5”,分別在單元格H3、13中輸入“=IF(基礎資訊!D4=”“,0,ROUND((C3+D3+E3)/基礎資訊!＄I＄2★基礎資訊!D4,2))”、“=IF(基礎資訊!E4=”“,0,ROUND((工資表!C3+工資表!D3+工資表!E3)/基礎資訊!＄I＄2*基礎資訊!E4-5*基礎資訊!E4,2))”,這裡運用ROUND函式表示對事假或病假扣除金額四捨五入,IF函式表示如果沒有請事假或病假則扣額為零,否則按事假或病假標準扣除。需注意公式中＄表示絕對引用符號不可省略,複製公式時引用範圍不會發生變化。根據三金(養老保險、失業保險、醫療保險)扣除標準分別為基本工資、崗位工資、綜合補貼之和的8%、1%、2%,在單元格J3中輸入“=ROUND((C3+D3+E3)*11%,2)”。
第五步,為了方便利用函式計算個人所得稅,增加“應稅所得額”列,平時可將其隱藏。在單元格M3中輸入“=IF((J3-K3-1600)>O,J3-K3-1600,0)”表示應稅所得額只有超過免徵額1600元才徵稅,否則不徵稅。在單元格N3中輸入“=ROUND(IF(M3<=500,M3*0.05,IF(M3<=2000,M3*10%-25,IF(M3<=5000,M3*15%-125,IF(M3<=20000,M3*20%-375,IF(M3<=40000,M3*25%-1375,M3*30%-3375))),2)”,這裡運用IF函式的層層巢狀計算個人所得稅。
第六步,在單元格J3中輸入“=C3+D3+E3+F3+G3-H3-13”,表示“應發工資=基本工資+崗位工資+綜合補貼+加班+浮動工資-事假、病假扣除”;在單元格03中輸入“=K3+L3+N3”表示扣除合計總額;在單元格P3中輸入“=J3-03”表示實發工資總額;在單元格B12中輸入“=SUBTOTAL(3,工資表!A3:A11)&“人””,這裡運用了SUBTOTAL動態計數函式計算出人數;在單元格C12中輸入“SUM(C3:C11)”表示對所有人員的基本工資求和。最後再利用“填充柄”,自動生成相應資料。
三、建立彙總表
建立如表3所示的工資彙總表。工資彙總表中主要運用了SUMIF()求和函式,從其它工作表中獲取資料。其功能是在滿足指定的統計條件下,對給定資料區域中的某一欄目範圍進行累計求和。在單元格B3中輸入“=SUMIF(工資表!＄A＄3:＄A＄11,＄A3,工資表!＄C＄3:＄C＄11)”,表示將“部門”列中所有“生產”的行所對應的“基本工資”合計。“生產”部門其餘工資各專案彙總公式均參照單元格B3填寫,只需將單元格B3中需要求和的物件“工資表!＄C＄3:＄C＄11”變動一下,其餘均不變,如在單元格C3中輸入“=SUMIF(工資表!＄A＄3:＄A＄11,＄A3,工資表!＄D＄3:＄D＄11)”。在單元格B7中輸入“=SUM(B3:B6)”,表示對彙總數求和,再利用“填充柄”,生成相應彙總數及合計數。
四、建立銀行表
建立如表4所示的銀行表。在單元格A2中輸入“=基礎資訊!B4”,利用“填充柄”,生成各員工姓名。“賬號”、“實發工資”自動產生,主要運用了VLOOKUP()函式,其功能是搜尋表區域首列滿足條件的元素,確定待檢索單元格在區域中的行序號,再進一步返回選定單元格的`值。在單元格中B2中輸入“=IF(A2:”“,”“,VLOOKUP(A2,基礎資訊!B4:C21,2,FALSE))”,表示如果姓名A2為空格,那麼賬號B2為空,否則賬號B2為“基礎資訊”表中姓名和賬號兩列區域中第一列的數值等於單元格A2中的數值的同一行的第二列數值。同樣在單元格C2中輸入“=IF(B2=”“,”“,VLOOKUP(A2,工資表!B3:P11,15,FALSE))”,再利用“填充柄”,生成各員工的賬號及實發工資。
五、建立“巨集”生成工資條
為了提高工作效率,可以通過建立“巨集”生成工資條。本文建立工資條通過錄制互動操作過程逐步建立,此種方法相對簡單,具體步驟如下:
第一步,啟用巨集。用滑鼠單擊[工具]-[巨集]-[錄製新巨集],鍵入巨集名“工資條”;在“儲存在(I):”下拉框中,選擇“當前工作簿”;在“說明(D):”編輯框中鍵入相應的文字;單擊【確定】。
第二步,錄製巨集。按住CTRL健的同時,沿工作表標籤行拖動“工資表”標籤,建立副本,並雙擊將“工資表(2)”改名為“工資條”,然後將“工資條”中的資料編輯修改如表5所示。注意,此步驟中每一步都應小心謹慎操作,因為若在錄製巨集時出現失誤,更正失誤的操作也會記錄在巨集中。
第三步,單擊“停止錄製”按鈕,結束巨集錄製。
第四步,建立“按鈕”。用滑鼠單擊【按鈕】圖示,在“工資表”劃一個小方框,選中小方框,右擊滑鼠→編輯文字→輸入“工資條”→右擊滑鼠→單擊“指定巨集(N)…”→選擇“工資條”→“確定”。以後每次要生成工資條時。只要將原“工資條”刪除,按一下“工資條”按鈕,就可以完成上述一系列操作,自動生成“工資條”。
工資彙總表等
一套工資核算模型建立完成,下月進行工資核算時,可以先複製一下該檔案,然後只要將“基礎資訊”表中月份、本月出勤天數、事假天數、病假天數、加班天數、浮動工資、房租等資料輸入,工資表、工資彙總表、銀行表、工資條都將自動生成,大大提高了工作效率。另外在使用過程中應注意以下幾點:(J)在輸入數字時,小數點有時輸成中文句號,可以選取“基礎資訊”表中的D、E、F、G、H五列,單擊選單[資料]-[有效性]-[輸入法模式]中選取“關閉”。(2)為防止單元格中建立的公式被誤刪或更改,可以先選中有公式的單元格,在選單[格式]-[單元格]-[保護]中的[鎖定]前打勾,然後單擊選單[工具]-[保護]-[保護工作表],輸入密碼。(3)為限制不同使用者檢視和修改檔案,在檔案建立完成後,可以輸入開啟許可權密碼和修改許可權密碼,具體單擊[檔案]-[另存為]-[工具]-[常規選項],在[開啟許可權密碼]和[修改許可權密碼]中輸入不同的密碼。
