Excel講解及練習(xí)--員工工資表(共14頁)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上模塊三Excel電子表格制作項(xiàng)目一 不變項(xiàng)目二 制作員工工資表（調(diào)整了任務(wù)和素材）項(xiàng)目說明工資是企業(yè)在一定時(shí)間內(nèi)直接支付給本單位員工的勞動(dòng)報(bào)酬，也是企業(yè)進(jìn)行各種費(fèi)用計(jì)提的基礎(chǔ)。工資管理是企業(yè)管理的重要組成部分，是每個(gè)單位財(cái)會(huì)部門最基本的業(yè)務(wù)之一。手工進(jìn)行工資核算，需要占用財(cái)務(wù)人員大量的精力和時(shí)間，并且容易出錯(cuò)，采用計(jì)算機(jī)進(jìn)行工資核算可以有效提高工資核算的準(zhǔn)確性和及時(shí)性。項(xiàng)目分析本項(xiàng)目要求建立員工工資表，計(jì)算其中的相關(guān)數(shù)據(jù)，對(duì)工資數(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ù)時(shí)為了防止出錯(cuò)可以進(jìn)行有效性控制。步驟： 1、 啟動(dòng)Excel應(yīng)用程序，單擊“文件”“另存為”菜單項(xiàng)，在彈出的“另存為”對(duì)話框中，輸入文件名“工資”，文件類型為“.xls”。2、 雙擊工作表標(biāo)簽，將工作表“sheet1”重命名為“2010年4月”。3、 在工作表第一行輸入如下標(biāo)題：職工代碼、姓名、性別、年齡、所屬部門、職工類別、基本工資、崗位工資、獎(jiǎng)金、應(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ù)”“有效性”菜單項(xiàng)，打開“數(shù)據(jù)有效性”對(duì)話框，如圖3-2-1所示。6、 在“允許”列表中選擇“序列”，在“來源”中輸入“男,女”（用英文逗號(hào)隔開），單擊“確定”按鈕完成設(shè)置。7、 將光標(biāo)移動(dòng)到C2單元格右下角，當(dāng)光標(biāo)變?yōu)閷?shí)心的十字形時(shí)，按住鼠標(biāo)左鍵向下拖拉，將C2單元格的有效性控制復(fù)制到C列其他單元格。圖3-2-1數(shù)據(jù)有效性對(duì)話框8、 根據(jù)表格3-2-1的數(shù)據(jù)輸入“性別”、“年齡”“所屬部門”和“職工類別”等數(shù)據(jù)，其中“所屬部門”和“職工類別”兩列數(shù)據(jù)采用有效性控制輸入，最后結(jié)果如圖3-2-2所示。 圖3-2-2 2010年4月工資表任務(wù)2 計(jì)

5、算“崗位工資”、“獎(jiǎng)金”和“應(yīng)發(fā)合計(jì)”分析或說明：IF函數(shù)可以根據(jù)設(shè)定的條件在相應(yīng)單元格中自動(dòng)填入數(shù)據(jù)。在2010年4月工資表中，崗位工資和獎(jiǎng)金根據(jù)每位員工的職工類別不同而有所不同，具體情況如下表3-2-2所示。本任務(wù)是先用IF函數(shù)輸入崗位工資和獎(jiǎng)金，然后用公式“應(yīng)發(fā)合計(jì)=基本工資+崗位工資+獎(jiǎng)金”計(jì)算應(yīng)發(fā)合計(jì)。表3-2-2 崗位工資和獎(jiǎng)金表職工類別崗位工資獎(jiǎng)金管理人員500200工人200300銷售員300獎(jiǎng)金銷售額×提成比例（銷售額<3萬元，提成比例為2，3萬元銷售額10萬元，比例為4，銷售額10萬元，比例為8）步驟： 1、 光標(biāo)放在H2單元格，單擊“插入”“函數(shù)”菜單項(xiàng)，

6、在彈出的“粘貼函數(shù)”對(duì)話框中選擇IF函數(shù)，單擊“確定”按鈕。2、 在彈出的“函數(shù)參數(shù)”對(duì)話框中，輸入各項(xiàng)參數(shù)，如圖3-2-3所示。圖3-2-3 if函數(shù)參數(shù)對(duì)話框之一3、 用鼠標(biāo)按住H2單元格右下角的填充柄向下拖拉復(fù)制函數(shù)，輸入其他人員的崗位工資。4、 打開素材文件夾中的“銷售額.doc”文檔，單擊“編輯”“復(fù)制”菜單項(xiàng)，復(fù)制全部數(shù)據(jù)。5、 光標(biāo)放在工作表sheet2的A1單元格,單擊“編輯”“粘貼”菜單項(xiàng)，將Word文檔表格中的數(shù)據(jù)轉(zhuǎn)換成Excel工作表，雙擊工作表標(biāo)簽“sheet2”，將其重命名為“銷售表”。6、 光標(biāo)放在“2010年4月”工作表的I2單元格，單擊“插入”“函數(shù)”菜單項(xiàng)，在

7、彈出的“粘貼函數(shù)”對(duì)話框中選擇IF函數(shù)，單擊“確定”按鈕。7、 在彈出的“函數(shù)參數(shù)”對(duì)話框中，輸入各項(xiàng)參數(shù)，所圖3-2-4所示。8、 用鼠標(biāo)左鍵按住I2單元格右下角的填充柄向下拖拉復(fù)制函數(shù)，輸入其他人員的獎(jiǎng)金，結(jié)果如圖3-2-5所示。9、 光標(biāo)放在I5單元格，單擊“插入”“函數(shù)”菜單項(xiàng)，在彈出的“粘貼函數(shù)”對(duì)話框中選擇IF函數(shù)，單擊“確定”按鈕。10、 在彈出的“函數(shù)參數(shù)”對(duì)話框中，輸入各項(xiàng)參數(shù)，所圖3-2-6所示。在Excel編輯欄內(nèi)可得到如下IF函數(shù)的表達(dá)式：“=IF(F5="銷售員",IF(銷售表!E2<3,銷售表!E2*2%,IF(銷售表!E2>=10,

8、銷售表!E2*8%,銷售表!E2*4%)*10000)”。11、 用鼠標(biāo)左鍵按住I5單元格的填充柄，向下拖拉到I9為止，輸入其他銷售員的獎(jiǎng)金。12、 單擊J2單元格，輸入公式：“ =G2+H2+I2”，單擊“確定”按鈕，計(jì)算應(yīng)發(fā)合計(jì)。13、 用鼠標(biāo)左鍵按住J2單元格的填充柄，向下拖拉輸入其他人員的應(yīng)發(fā)合計(jì)。圖3-2-4 if函數(shù)參數(shù)對(duì)話框之二圖3-2-5計(jì)算獎(jiǎng)金圖3-2-6 if函數(shù)參數(shù)對(duì)話框之三任務(wù)3 計(jì)算“事假扣款”、“病假扣款”和“扣款合計(jì)”分析或說明：該公司規(guī)定，病事假應(yīng)扣款額和病事假天數(shù)有關(guān)，具體扣款金額如下表3-2-3所示。本任務(wù)利用IF函數(shù)計(jì)算病事假扣款，然后用公式“扣款合計(jì)事假

9、扣款+病假扣款”計(jì)算扣款合計(jì)。表3-2-3 病事假扣款表病事假天數(shù)應(yīng)扣款金額（元）事假病假工人其他人員15（應(yīng)發(fā)合計(jì)/22）×事假天數(shù)20030015應(yīng)發(fā)合計(jì)×80400500步驟： 1、 光標(biāo)放在L2單元格中，單擊“插入”“函數(shù)”菜單項(xiàng)，在彈出的“粘貼函數(shù)”對(duì)話框中選擇IF函數(shù)，單擊“確定”按鈕。2、 在彈出的“函數(shù)參數(shù)”對(duì)話框中，輸入各項(xiàng)參數(shù)，所圖3-2-7所示。在編輯欄內(nèi)得到IF函數(shù)的表達(dá)式為：“=IF(K2<15,J2/22*K2,J2*80%)”。圖3-2-7 if函數(shù)參數(shù)對(duì)話框之四3、 用鼠標(biāo)左鍵按住L2單元格右下角的填充柄向下拖拉，計(jì)算其他人員的事假扣款

10、。4、 光標(biāo)放在N2單元格中，單擊“插入”“函數(shù)”菜單項(xiàng)，在彈出的“粘貼函數(shù)”對(duì)話框中選擇IF函數(shù)，單擊“確定”按鈕。5、 在彈出的“函數(shù)參數(shù)”對(duì)話框中，輸入各項(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)。圖3-2-8 if函數(shù)參數(shù)對(duì)話框之五6、 用鼠標(biāo)左鍵按住N2單元格右下角的填充柄向下拖拉，計(jì)算其他人員的病假扣款。7、 在O2單元格中輸入公式：“ =L2+N2”，單擊“確定”按鈕，計(jì)算扣款合計(jì)。8、 用鼠標(biāo)左

11、鍵按住O2單元格右下角的填充柄向下拖拉，計(jì)算其他人員的扣款合計(jì)。9、 選擇L2：O12數(shù)據(jù)區(qū)域，單擊“格式”“單元格”菜單項(xiàng)，在彈出的對(duì)話框中單擊“數(shù)字”選項(xiàng)卡。10、 在“數(shù)字”選項(xiàng)卡的“數(shù)值”分類中，將事假扣款、病假扣款、扣款合計(jì)的小數(shù)位數(shù)設(shè)置為2位，如圖3-2-9所示。圖3-2-9 各項(xiàng)扣款 任務(wù)4 計(jì)算“應(yīng)發(fā)工資”分析或說明：應(yīng)發(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單元格中，輸入公式：“=

12、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單元格中，輸入公式：“=J2*10%”，按回車鍵算出公積金。6、 用鼠標(biāo)左鍵按住R2單元格右下角的填充柄向下拖拉，計(jì)算其他人員的公積金。7、 光標(biāo)放在S2單元格中，輸入公式：“=J2-O2-P2-Q2-R2”，按回車鍵算出應(yīng)發(fā)工資。8、 用鼠標(biāo)左鍵按住S2單元格右下角的填充柄向下拖拉，計(jì)算其他人員的應(yīng)發(fā)工資，如圖3-2

13、-10所示。圖3-2-10 計(jì)算應(yīng)發(fā)工資任務(wù)5 計(jì)算“所得稅”和“實(shí)發(fā)工資”分析或說明：我國相關(guān)稅法規(guī)定，工資、薪金所得應(yīng)繳納個(gè)人所得稅。即每月取得工資收入后，先減去個(gè)人承擔(dān)的基本養(yǎng)老保險(xiǎn)金、醫(yī)療保險(xiǎn)金和失業(yè)保險(xiǎn)金，以及按省級(jí)政府規(guī)定標(biāo)準(zhǔn)繳納的住房公積金，再減去2000元/月（個(gè)人所得稅起征額為2000元），為應(yīng)納稅所得額，按5%至45%的九級(jí)超額累進(jìn)稅率計(jì)算應(yīng)繳納個(gè)人所得稅。計(jì)算公式為：“個(gè)人所得稅=應(yīng)納稅所得額×稅率-速算扣除數(shù)”。表3-2-4 所得稅稅率表級(jí)數(shù)全月應(yīng)納稅所得額稅率%速算扣除數(shù)（元）1不超過500元的502超過500元至2000元的部分10253超過2000元至5

14、000元的部分151254超過5000元至20000元的部分203755超過20000元至40000元的部分2513756超過40000元至60000元的部分3033757超過60000元至80000元的部分3563758超過80000元至元的部分40103759超過元的部分4515375步驟： 1、 光標(biāo)放在T2單元格，單擊“插入”“函數(shù)”菜單項(xiàng)，在彈出的“粘貼函數(shù)”對(duì)話框中選擇IF函數(shù)，單擊“確定”按鈕。2、 在彈出的“函數(shù)參數(shù)”對(duì)話框中，輸入各項(xiàng)參數(shù)，所圖3-2-11所示。在編輯欄內(nèi)得到IF函數(shù)的表達(dá)式為：“=IF(S2-2000<500,0,IF(S2-2000<2000,

15、(S2-2000)*10%-25,IF(S2-2000<5000,(S2-2000)*15%-125,IF(S2-2000<20000,(S2-2000)*20%-375,"復(fù)核")”。3、 考慮到該企業(yè)員工的應(yīng)發(fā)工資在扣除2000元之后，沒有超過20000元的，所以IF函數(shù)只判斷到S2-2000<20000，當(dāng)S2-200020000時(shí)，作復(fù)核處理。圖3-2-11 if函數(shù)參數(shù)對(duì)話框之六4、 用鼠標(biāo)左鍵按住T2單元格右下角的填充柄向下拖拉，計(jì)算其他人員的所得稅。5、 光標(biāo)放在U2單元格中，輸入公式：“=S2-T2”，按回車鍵計(jì)算實(shí)發(fā)工資。6、 用鼠標(biāo)左鍵

16、按住U2單元格右下角的填充柄向下拖拉，計(jì)算其他人員的實(shí)發(fā)工資，如圖3-2-12所示。圖3-2-12 所得稅和實(shí)發(fā)工資項(xiàng)目總結(jié)本項(xiàng)目是制作一張員工工資表，包括工資表數(shù)據(jù)輸入與統(tǒng)計(jì)分析。在數(shù)據(jù)輸入時(shí)可以利用有效性控制和IF函數(shù)，快速正確地輸入指定數(shù)據(jù)。工資表數(shù)據(jù)輸入完成后，可以運(yùn)用數(shù)據(jù)透視表及數(shù)據(jù)透視圖進(jìn)行匯總及統(tǒng)計(jì)分析。拓展延伸1、IF函數(shù)的使用函數(shù)格式：if(logical_test,value_if_true,value_if_false)。其中：“l(fā)ogical_test”表示設(shè)定的條件；“value_if_true”表示當(dāng)目標(biāo)單元格與設(shè)定條件相符時(shí)返回的函數(shù)值；“value_if_fal

17、se”表示當(dāng)目標(biāo)單元格與設(shè)定條件不符時(shí)返回的函數(shù)值。IF函數(shù)可以嵌套使用，最多可以嵌套7層。以表3-2-5為例，不同分?jǐn)?shù)段要求輸入不同的等級(jí)，則IF函數(shù)為：“IF(成績60，“不及格”，IF (成績70，“及格”，IF (成績80,“中”，IF (成績90，“良”，“優(yōu)”)”。表3-2-5 學(xué)生成績表成績等級(jí)成績60不及格60成績70及格70成績80中80成績90良90成績優(yōu)2、有效性控制Excel強(qiáng)大的制表功能給我們的工作帶來了方便，但是在數(shù)據(jù)錄入過程中難免會(huì)出錯(cuò)，比如重復(fù)的身份證號(hào)碼，超出范圍的無效數(shù)據(jù)等。其實(shí)可以通過有效性控制來限定輸入的內(nèi)容，以避免錯(cuò)誤數(shù)據(jù)的輸入。把既定的規(guī)律化的輸入內(nèi)

18、容作為選項(xiàng)，在有效性設(shè)置對(duì)話框中按“序列”進(jìn)行設(shè)置。從序列的來源劃分，啟用這種“序列”的方法也是有多種的，下面簡要描述設(shè)置方法。 簡單序列值可直接輸入 序列項(xiàng)目較少，并且固定不變的，可以直接在相應(yīng)的單元格設(shè)置有效性的時(shí)候，在數(shù)據(jù)來源框中輸入具體的選項(xiàng)。比如，希望在A1單元格只能輸入“未開始”、“進(jìn)行中”、“已完成”、“已延期”四個(gè)選項(xiàng)中的一個(gè)，則在有效性設(shè)置對(duì)話框中的來源輸入框中輸入：“未開始,進(jìn)行中,已完成,已延期”（選項(xiàng)值之間的逗號(hào)是英文輸入狀態(tài)下的逗號(hào)）。此外，通過有效性設(shè)置對(duì)話框的其他選項(xiàng)卡，可以設(shè)置“輸入信息”和“出錯(cuò)警告”信息。“輸入信息”是指設(shè)置有效性控制的單元格被選中時(shí)出現(xiàn)的輸入提示性信息；“出錯(cuò)警告”則是在設(shè)置有效性控制的單元格中輸入錯(cuò)誤數(shù)據(jù)時(shí)出現(xiàn)的警告信息或說明。 導(dǎo)入數(shù)據(jù)序列若在要設(shè)置有效性控制單元格的同一工作表中已存在數(shù)據(jù)序列，則可通過導(dǎo)入的方式將數(shù)據(jù)序列導(dǎo)入。值得注意的是，數(shù)據(jù)有效性控