Excel講解及練習(xí)員工工資表

1、模塊三 Excel 電子表格制作項(xiàng)目一不變項(xiàng)目二 制作員工工資表（調(diào)整了任務(wù)和素材）項(xiàng)目說明工資是企業(yè)在一定時間內(nèi)直接支付給本單位員工的勞動報酬，也是企業(yè)進(jìn)行各種費(fèi)用計(jì)提的基礎(chǔ)。工資管理是企業(yè)管理的重要組成部分，是每個單位財會部門最基本的業(yè)務(wù)之一。手工進(jìn)行工資核算，需要占用財務(wù)人員大量的精力和時間，并且容易出錯，采用計(jì)算機(jī)進(jìn)行工資核算可以有效提高工資核算的準(zhǔn)確性和及時性。項(xiàng)目分析本項(xiàng)目要求建立員工工資表，計(jì)算其中的相關(guān)數(shù)據(jù)，對工資數(shù)據(jù)進(jìn)行查詢與匯總分析，并打印工資發(fā)放條。2010 年 4 月份某公司員工基本工資和出勤情況如表3-2-1 所示。表 3-2-1 2010 年 4 月份某公司員工基本

2、工資和出勤情況表職工代碼姓名性別年齡所屬部門職工類別基本工資事假 天數(shù)病假 天數(shù)A001許振男57管理部管理人員3000A002徐仁華女55管理部管理人員28002A003張焱女36管理部管理人員26005B001鄭昂男27銷售部銷售員2000B002李帆男32銷售部銷售員2000B003吳星男36銷售部銷售員150015B004唐嘉男45銷售部銷售員1500B005孫麗女29銷售部銷售員150020C001許濤男21生產(chǎn)部工人12002C002陳蘇蘇女26生產(chǎn)部工人1200C003王飛飛女22生產(chǎn)部工人120016任務(wù) 1 建立員工工資表并輸入基本數(shù)據(jù)分析或說明：建立基本工資表主要是搭建工資

3、表的框架，在工資表中列出一些有關(guān)員工工資的項(xiàng)目，并輸入基本數(shù)據(jù)。在輸入數(shù)據(jù)時為了防止出錯可以進(jìn)行有效性控制。步驟:1、啟動 Excel 應(yīng)用程序，單擊“文件”7“另存為”菜單項(xiàng)，在彈出的“另存為”對話框中，輸入文件名“工資”，文件類型為“ .xls ”。2、 雙擊工作表標(biāo)簽，將工作表“ sheetl ”重命名為“ 2010 年 4 月”。3、 在工作表第一行輸入如下標(biāo)題：職工代碼、姓名、性別、年齡、所屬部門、職工類別、基本工資、崗位工資、獎金、應(yīng)發(fā)合計(jì)、事假天數(shù)、事假扣款、病假天數(shù)、病假扣款、扣款合計(jì)、養(yǎng)老金、醫(yī)療金、公積金、應(yīng)發(fā)工資、所得稅、實(shí)發(fā)工 資。4、參照表 3-2-1 中的數(shù)據(jù)輸入第

4、一列“職工代碼”和第二列“姓名”。5、光標(biāo)放在 C2 單元格，單擊“數(shù)據(jù)”7“有效性”菜單項(xiàng)，打開“數(shù)據(jù)有 效性”對話框，如圖 3-2-1 所示。6、在“允許”列表中選擇“序列”，在“來源”中輸入“男，女”（用英文逗 號隔開），單擊“確定”按鈕完成設(shè)置。7、將光標(biāo)移動到 C2 單元格右下角，當(dāng)光標(biāo)變?yōu)閷?shí)心的十字形時，按住鼠標(biāo)左鍵向下拖拉，將 C2 單元格的有效性控制復(fù)制到C 列其他單元格。圖 3-2-1 數(shù)據(jù)有效性對話框8、根據(jù)表格 3-2-1 的數(shù)據(jù)輸入“性別”、“年齡” “所屬部門”和“職工類別”等數(shù)據(jù)，其中“所屬部門”和“職工類別”兩列數(shù)據(jù)采用有效性控制輸入，最后結(jié) 果如圖3-2-2 所

5、示。E3Microsoft ExceL -工侯，sis 017巴文件迢）鋼輯迢）視囹（町 播入 格武）工且 數(shù)據(jù) 窗口（町 幫助01）-X圖 3-2-2 2010 年 4 月工資表任務(wù) 2 計(jì)算“崗位工資”、“獎金”和“應(yīng)發(fā)合計(jì)”分析或說明：IF 函數(shù)可以根據(jù)設(shè)定的條件在相應(yīng)單元格中自動填入數(shù)據(jù)。在2010 年 4月工資表中，崗位工資和獎金根據(jù)每位員工的職工類別不同而有所不同，具體情況如下表3-2-2 所示。本任務(wù)是先用 IF 函數(shù)輸入崗位工資和獎金，然后用公式“應(yīng)發(fā)合計(jì)=基本工資+崗位工資+獎金”計(jì)算應(yīng)發(fā)合計(jì)。表 3-2-2 崗位工資和獎金表職工類別崗位工資獎金管理人員500200工人200

6、300銷售員300獎金=銷售額X提成比例（銷售額 3 萬元，提成比例為 2%,3 萬元w銷售額V10 萬元，比例為 4%,銷售額10 萬元， 比例為8%）步驟：1、光標(biāo)放在 H2 單元格，單擊“插入”T“函數(shù)”菜單項(xiàng)，在彈出的“粘貼專jA晶心*3出空I也J蝴E豪屯氧亠八八A包1234567職工代碼A001A002A003B001B002另準(zhǔn)男女女男男華名振仁&昂帆姓許徐張鄭李D EF車齡倂屬部門職工類別57 管理部管理人員55管理部36管理部27 銷售部32 銷售部管理人員管理人員硝售員銷售員8瓦101112B003B004B005C001C002C003陳蘇蘇王飛飛364529銷售部

7、銷售部銷售酩21生產(chǎn)部26 生產(chǎn)部22 生產(chǎn)部人人人.ITTG基本工資30002800260020002000150015001500120012001200H _產(chǎn) 崗位工茁 n 20104H/Sheet2/Sheet3/2、在彈出的“函數(shù)參數(shù)”對話框中，輸入各項(xiàng)參數(shù)，如圖3-2-3 所示。函數(shù)”對話框中選擇 IF 函數(shù)，單擊“確定”按鈕。圖 3-2-3 if函數(shù)參數(shù)對話框之一3、用鼠標(biāo)按住 H2 單元格右下角的填充柄向下拖拉復(fù)制函數(shù)，輸入其他人員 的崗位工資。4、打開素材文件夾中的“銷售額.doc ”文檔，單擊“編輯”7“復(fù)制”菜單 項(xiàng)，復(fù)制全部數(shù)據(jù)。5、光標(biāo)放在工作表 sheet2 的

8、A1 單元格，單擊“編輯”宀“粘貼”菜單項(xiàng)，將 Word 文檔表格中的數(shù)據(jù)轉(zhuǎn)換成Excel 工作表，雙擊工作表標(biāo)簽“ sheet2 ”，將其重命名為“銷售表”。6、光標(biāo)放在“ 2010 年 4 月”工作表的 I2 單元格，單擊“插入”7“函數(shù)” 菜單項(xiàng)，在彈出的“粘貼函數(shù)”對話框中選擇 IF 函數(shù)，單擊“確定”按鈕。7、在彈出的“函數(shù)參數(shù)”對話框中，輸入各項(xiàng)參數(shù)，所圖3-2-4 所示。8、用鼠標(biāo)左鍵按住 I2 單元格右下角的填充柄向下拖拉復(fù)制函數(shù)，輸入其他人員的獎金，結(jié)果如圖3-2-5 所示。9、光標(biāo)放在 I5 單元格，單擊“插入”7“函數(shù)”菜單項(xiàng)，在彈出的“粘貼 函數(shù)”對話框中選擇 IF 函

9、數(shù)，單擊“確定”按鈕。10、 在彈出的“函數(shù)參數(shù)”對話框中，輸入各項(xiàng)參數(shù)，所圖3-2-6 所示。在Excel 編輯欄內(nèi)可得到如下 IF 函數(shù)的表達(dá)式：“ =IF(F5=銷售員,IF(銷售表！E2=10,銷售表！E2*8%,銷售表！E2*4%)*10000) ”。11、 用鼠標(biāo)左鍵按住 I5 單元格的填充柄， 向下拖拉到 I9 為止， 輸入其他銷售 員的獎金。12、單擊 J2 單元格，輸入公式：“ =G2+H2+I2 ”，單擊“確定”按鈕，計(jì)算應(yīng)發(fā)合計(jì)。13、用鼠標(biāo)左鍵按住 J2 單元格的填充柄，向下拖拉輸入其他人員的應(yīng)發(fā)合計(jì)。圖 3-2-4 if函數(shù)參數(shù)對話框之二口口Microsoft Exc

10、el -工資工資. .sis目回目回I: :色文件迥色文件迥編編輯氓）輯氓）視圖視圖M撅入撅入（D格式格式（工具工具（D數(shù)數(shù)搖迥搖迥窗口窗口（D= =幫助幫助徂）徂）-fl x：- -討討A弩丄啟弩丄啟 7 7 匕匕 - -”出出I 3 “肓肓二二12JBf屯土屯土_BK14EFGHIJ1所屬部門職T類別基本 T 資崗位工資 獎金應(yīng)發(fā)合計(jì) 事假：2管理部管理人員30005002003管理部管理人員23005002004管理部管理人員26005002005銷售部銷售員2000300銷售員6銷售部銷售員2000300銷售員1銷售部銷售員1500300銷售員8銷售部銷售員1500300銷售員9銷售部

11、銷售員1500300 銷售員10 生產(chǎn)部工人120020030011 生產(chǎn)部工人12002003Q012生產(chǎn)部工人120020030014 4制2010 年4月/銷售/Sheet3/就緒就緒數(shù)字?jǐn)?shù)字圖 3-2-5 計(jì)算獎金甌數(shù)參數(shù)岡=9600Logical.test任何一個可判斷為 TRUE 或 FALSE 的數(shù)值或表達(dá)式計(jì)算姑果=9600有關(guān)T哀函數(shù)的幫助00圖 3-2-6 if函數(shù)參數(shù)對話框之三任務(wù) 3 計(jì)算“事假扣款”、“病假扣款”和“扣款合計(jì)”分析或說明：該公司規(guī)定，病事假應(yīng)扣款額和病事假天數(shù)有關(guān)，具體扣款金額如下表3-2-3 所示。本任務(wù)利用 IF 函數(shù)計(jì)算病事假扣款，然后用公式“扣

12、款合計(jì)=事假扣款+病假扣款”計(jì)算扣款合計(jì)。表 3-2-3 病事假扣款表病事假天數(shù)應(yīng)扣款金額（元）事假病假工人其他人員 15應(yīng)發(fā)合計(jì)X80%400500步驟：1、 光標(biāo)放在 L2 單元格中，單擊“插入”7“函數(shù)”菜單項(xiàng)，在彈出的“粘貼函數(shù)”對話框中選擇 IF 函數(shù)，單擊“確定”按鈕。2、 在彈出的“函數(shù)參數(shù)”對話框中，輸入各項(xiàng)參數(shù)，所圖3-2-7 所示。在編輯欄內(nèi)得到 IF 函數(shù)的表達(dá)式為：“ =IF（K215,J2/22*K2,J2*80%）取涓TRUE9600判斷一卜乗件星否滿足.如果満足返回一個值.如果不滿星則返回另一個值IFT.ogLcal_test 任何一個可判斷為 TEUE 或 FA

13、LSE 的數(shù)值或表這式計(jì)算結(jié)果=有關(guān)該函數(shù)的幫勛伯）圖 3-2-7 if 函數(shù)參數(shù)對話框之四3、用鼠標(biāo)左鍵按住 L2 單元格右下角的填充柄向下拖拉，計(jì)算其他人員的事 假扣款。4、光標(biāo)放在 N2 單元格中，單擊“插入”T“函數(shù)”菜單項(xiàng)，在彈出的“粘貼函數(shù)”對話框中選擇IF 函數(shù)，單擊“確定”按鈕。5、在彈出的“函數(shù)參數(shù)”對話框中，輸入各項(xiàng)參數(shù)，所圖3-2-8 所示。在編輯欄內(nèi)得到 IF 函數(shù)的表達(dá)式為：=IF（M2=0,0,IF（M2=15,IF（F2= 工人 ,200,300）,IF（F2= 工人,400,500）。6、 用鼠標(biāo)左鍵按住 N2 單元格右下角的填充柄向下拖拉，計(jì)算其他人員的病 假

14、扣款。7、 在 02 單元格中輸入公式：“ =L2+N2，單擊“確定”按鈕，計(jì)算扣款合計(jì)。8、 用鼠標(biāo)左鍵按住 O2 單元格右下角的填充柄向下拖拉，計(jì)算其他人員的扣款合計(jì)。確定取梢=0判斷一個祭件是辭足*如果扁足返回一亍值，如果蘋禱足則返回另一個圖 3-2-8 if函數(shù)參數(shù)對話框之五9、 選擇 L2 : 012 數(shù)據(jù)區(qū)域，單擊“格式”“單元格”菜單項(xiàng)，在彈出的對話框中單擊“數(shù)字”選項(xiàng)卡。10、在“數(shù)字”選項(xiàng)卡的“數(shù)值”分類中，將事假扣款、病假扣款、扣款合計(jì) 的小數(shù)位數(shù)設(shè)置為 2 位，如圖 3-2-9 所示。 Microsoft Excel一 工養(yǎng).xl呂任務(wù) 4 計(jì)算“應(yīng)發(fā)工資”分析或說明：應(yīng)

15、發(fā)工資=應(yīng)發(fā)合計(jì)-扣款合計(jì)-養(yǎng)老金-醫(yī)療金-公積金。其中，養(yǎng)老金按應(yīng)發(fā)合計(jì)的 8%扣除，醫(yī)療金按應(yīng)發(fā)合計(jì)的 2%扣除，公積金按應(yīng)發(fā)合計(jì)的10%扣除。本任務(wù)先分別計(jì)算“養(yǎng)老金”、“醫(yī)療金”和“公積金”，然后再用上述公式計(jì)算應(yīng)發(fā)工資。 步驟：1、 光標(biāo)放在 P2 單元格中，輸入公式：“ =J2*8% ”，按回車鍵算出養(yǎng)老金。2、 用鼠標(biāo)左鍵按住 P2 單元格右下角的填充柄向下拖拉，計(jì)算其他人員的養(yǎng)老金。3、 光標(biāo)放在 Q2 單元格中，輸入公式：“ =J2*2% ”，按回車鍵算出醫(yī)療金。4、 用鼠標(biāo)左鍵按住 Q2 單元格右下角的填充柄向下拖拉，計(jì)算其他人員的醫(yī)療金。5、 光標(biāo)放在 R2 單元格中，輸

16、入公式：“ =J2*10% ”，按回車鍵算出公積金。6、 用鼠標(biāo)左鍵按住 R2 單元格右下角的填充柄向下拖拉，計(jì)算其他人員的公積金。:宋體12% TaP18*KLN0PQ1事假天數(shù)事假扣款病假天數(shù)病假扣款 扣款合計(jì)養(yǎng)老金醫(yī)療簽20. 0000.0032318. 180318. 1840. 005300300. 0050. 0000. 0060. 0000. 00715 4224. 0004224. 0080 0000.0090. 0020500500. 00100. 002200200. 00110. 0000. 00120. 0016400400. 00V14 4 n2010年4月/鐺書表/

17、弘歸詣/|就細(xì)數(shù)宇：可 文件 編輯 視圖 描入 格式迫）ZM（I）數(shù)據(jù) 窗口:幫助-iS X圖 3-2-9各項(xiàng)扣款7、 光標(biāo)放在 S2 單元格中，輸入公式：“ =J2-O2-P2-Q2-R2 ”，按回車鍵算出應(yīng)發(fā)工資。8、 用鼠標(biāo)左鍵按住 S2 單元格右下角的填充柄向下拖拉，計(jì)算其他人員的應(yīng)發(fā)工資，如圖 3-2-10 所示。圖 3-2-10 計(jì)算應(yīng)發(fā)工資任務(wù) 5 計(jì)算“所得稅”和“實(shí)發(fā)工資”分析或說明：我國相關(guān)稅法規(guī)定，工資、薪金所得應(yīng)繳納個人所得稅。即每月取得工資收入后，先減去個人承擔(dān)的基本養(yǎng)老保險金、醫(yī)療保險金和失業(yè)保險金，以及按省級政府規(guī)定標(biāo)準(zhǔn)繳納的住房公積金，再減去 2000 元/月（

18、個人所得稅起征額為 2000 元），為應(yīng)納稅 所得額，按5%至 45%的九級超額累進(jìn)稅率計(jì)算應(yīng)繳納個人所得稅。計(jì)算公式為：“個人所得稅=應(yīng)納稅所得額X稅率-速算扣除數(shù)”。表 3-2-4 所得稅稅率表級數(shù)全月應(yīng)納稅所得額稅率%速算扣除數(shù)（元）1不超過 500 元的502超過 500 元至 2000 元的部分10253超過 2000 兀至 5000 兀的部分151254超過 5000 兀至 20000 兀的部分203755超過 20000 元至 40000 元的部分2513756超過 40000 元至 60000 元的部分3033757超過 60000 元至 80000 元的部分3563758超過

19、 80000 元至 100000 元的部分40103759超過 100000 元的部分4515375步驟：1、 光標(biāo)放在 T2 單元格，單擊“插入”T“函數(shù)”菜單項(xiàng)，在彈出的“粘貼函數(shù)”對 話框中選擇 IF 函數(shù)，單擊“確定”按鈕。2、 在彈出的“函數(shù)參數(shù)”對話框中，輸入各項(xiàng)參數(shù)，所圖3-2-11 所示。在編輯欄內(nèi)得 到IF函 數(shù) 的 表 達(dá) 式 為：“ =IF(S2-2000500,0,IF(S2-20002000,(S2-2000)*10%-25,IF(S2-20005000,(S2-2000)*15%-125,IF(S2-200020000,(S2-2000)*20%-375, 復(fù)核)”

20、。3、 考慮到該企業(yè)員工的應(yīng)發(fā)工資在扣除2000 元之后，沒有超過 20000 元的，所以 IF函數(shù)只判斷到 S2-2000368 260 0035+4 2563. 60200. 0013634：1701160.000 1160.000. 00136341701360.000 1360.00100. 0013634170960,000960, .00M0PTU0002160. 00270 2160.000 2160.00545002000400“中”，IF （成績V90, “良”，“優(yōu)”）”。表 3-2-5 學(xué)生成績表成績等級成績V60不及格60成績V70及格70W成績V80中80W成績V90良90w成績優(yōu)2、有效性控制Excel 強(qiáng)大的制表功能給我們的工作帶來了方便，但是在數(shù)據(jù)錄入過程中難免會出錯， 比如重復(fù)的身份證號碼， 超出范圍的無效數(shù)據(jù)等。 其實(shí)可以通過有效性控制來限定輸入的內(nèi) 容，以避免錯誤數(shù)據(jù)的輸入。 把既定的規(guī)律化的輸入內(nèi)容作為選項(xiàng)，在有效性設(shè)置對話