Excel的應用-工資管理課件

第七次實驗——員工工資管理2011.09第七次實驗——員工工資管理1實驗內(nèi)容一、本實驗知識點二、設計步驟工資管理系統(tǒng)的創(chuàng)建工資表的管理工資條的制作工資管理系統(tǒng)模板的創(chuàng)建建立基本數(shù)據(jù)表格建立工作表并輸入數(shù)據(jù)增加查詢數(shù)據(jù)分析工資數(shù)據(jù)制作工資條打印工資條保存模板使用模板實驗內(nèi)容一、本實驗知識點工資管理系統(tǒng)的創(chuàng)建工資表的管理工資條2一、本實驗知識點1.名稱的定義2.自動篩選3.函數(shù)的應用:IF()、VLOOKUP()4.數(shù)據(jù)透視表、數(shù)據(jù)透視圖一、本實驗知識點1.名稱的定義31.名稱的定義在EXCEL工作表中，可以使用表格中的列標志和行標志來引用這些行和列中的單元格，還可通過創(chuàng)建名稱來代表單元格、單元格區(qū)域、公式或者常量值。如果公式引用的是相同工作表中的數(shù)據(jù)，可以使用行標志或者列標志；如果想引用另一張工作表上的數(shù)據(jù)，則可使用名稱。本實驗在進行工資數(shù)據(jù)的輸入以及工資條的制作時就用到了此功能，可以對預先設置好的單元格中的數(shù)據(jù)內(nèi)容直接進行引用，減少錯誤率。1.名稱的定義在EXCEL工作表中，可以使用表格中的列標志和41.名稱的定義基本工資=基本工資!$A$2:$E$13工資表=工資表!$A$3:$K$14補貼表=補貼記錄表!$A$2:$E$13考勤表=考勤表!$A$2:$E$13員工獎金記錄表=獎金記錄表!$A$2:$E$131.名稱的定義基本工資=基本工資!$A$2:$E$135Excel的應用—工資管理課件62.自動篩選數(shù)據(jù)篩選是指把數(shù)據(jù)庫或者數(shù)據(jù)清單中所有不滿足條件的數(shù)據(jù)記錄隱藏起來，只顯示滿足條件的數(shù)據(jù)記錄，該條件由用戶針對某列指定。常用的篩選方法有自動篩選、自定義篩選和高級篩選3種，篩選是查找和處理區(qū)域中數(shù)據(jù)子集的重要的快捷方式。本實驗在對工資數(shù)據(jù)信息進行查詢時就用到了其中的自動篩選功能，通過簡單的篩選操作，篩選出要查詢的數(shù)據(jù)。2.自動篩選數(shù)據(jù)篩選是指把數(shù)據(jù)庫或者數(shù)據(jù)清單中所有不滿足條件73.函數(shù)的應用（1）VLOOKUP（）用法在表格數(shù)組的首列查找值，并由此返回表格數(shù)組當前行中其他列的值。VLOOKUP中的V表示垂直方向。當比較值位于需要查找的數(shù)據(jù)左邊的一列時，可以使用VLOOKUP。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value

為需要在表格數(shù)組

（數(shù)組：用于建立可生成多個結(jié)果或可對在行和列中排列的一組參數(shù)進行運算的單個公式。數(shù)組區(qū)域共用一個公式；數(shù)組常量是用作參數(shù)的一組常量。）第一列中查找的數(shù)值。Lookup_value可以為數(shù)值或引用。若lookup_value小于table_array第一列中的最小值，VLOOKUP將返回錯誤值#N/A。Table_array

為兩列或多列數(shù)據(jù)。請使用對區(qū)域的引用或區(qū)域名稱。table_array第一列中的值是由lookup_value搜索的值。這些值可以是文本、數(shù)字或邏輯值。不區(qū)分大小寫。Col_index_num

為table_array中待返回的匹配值的列序號。Col_index_num為1時，返回table_array第一列中的數(shù)值；col_index_num為2，返回table_array第二列中的數(shù)值，以此類推。如果col_index_num：小于1，VLOOKUP返回錯誤值#VALUE!。大于table_array的列數(shù)，VLOOKUP返回錯誤值#REF!。Range_lookup

為邏輯值，指定希望VLOOKUP查找精確的匹配值還是近似匹配值：例如：VLOOKUP(A4,基本工資!$A$2:$E$13,2,0)3.函數(shù)的應用（1）VLOOKUP（）用法83.函數(shù)的應用（2）IF()用法IF(logical_test,value_if_true,value_if_false)Logical_test

表示計算結(jié)果為TRUE或FALSE的任意值或表達式。例如，A10=100就是一個邏輯表達式，如果單元格A10中的值等于100，表達式即為TRUE，否則為FALSE。本參數(shù)可使用任何比較運算符。Value_if_true

logical_test為TRUE時返回的值。例如，如果本參數(shù)為文本字符串“預算內(nèi)”而且logical_test參數(shù)值為TRUE，則IF函數(shù)將顯示文本“預算內(nèi)”。如果logical_test為TRUE而value_if_true為空，則本參數(shù)返回0（零）。如果要顯示TRUE，則請為本參數(shù)使用邏輯值TRUE。Value_if_true也可以是其他公式。Value_if_false

logical_test為FALSE時返回的值。例如，如果本參數(shù)為文本字符串“超出預算”而且logical_test參數(shù)值為FALSE，則IF函數(shù)將顯示文本“超出預算”。如果logical_test為FALSE且忽略了Value_if_false（即value_if_true后沒有逗號），則會返回邏輯值FALSE。如果logical_test為FALSE且Value_if_false為空（即value_if_true后有逗號，并緊跟著右括號），則本參數(shù)返回0（零）。Value_if_false也可以是其他公式。3.函數(shù)的應用（2）IF()用法94.數(shù)據(jù)透視表、圖數(shù)據(jù)透視圖是以圖形的形式來表達數(shù)據(jù)透視表中的數(shù)據(jù)，用戶可以像更改數(shù)據(jù)透視表那樣對數(shù)據(jù)透視圖的布局和數(shù)據(jù)的顯示方式進行修改。本實驗在進行工資數(shù)據(jù)的分析時就用到了此功能，通過創(chuàng)建數(shù)據(jù)透視圖，可以更加方便地對大量的工資數(shù)據(jù)進行查看和匯總分析。4.數(shù)據(jù)透視表、圖數(shù)據(jù)透視圖是以圖形的形式來表達數(shù)據(jù)透視表中10二、實驗步驟1.建立基本表：基本工資表考勤表獎金記錄表補貼記錄表2.建立工資匯總表3.制作工資條4.生成透視表、透視圖二、實驗步驟1.建立基本表：11第1步建立基本工資表第1步建立基本工資表12第2步定義“基本工資”名稱由于【基本工資】表中的數(shù)據(jù)在后面很多地方都要用到，為了方便起見，可按圖中方式定義“基本工資”名稱（也叫別名）：基本工資=基本工資!$A$3:$E$13第2步定義“基本工資”名稱由于【基本工資】表中的數(shù)據(jù)在后面13第3步建立考勤表在姓名這一列的B3單元格輸入函數(shù)。原參數(shù)“基本工資!$A$3:$E$13”現(xiàn)在用參數(shù)“基本工資”代替。第3步建立考勤表在姓名這一列的B3單元格輸入函數(shù)。原參數(shù)“14第3步建立考勤表本區(qū)域數(shù)據(jù)需引用VLOOKUP函數(shù)。第3步建立考勤表本區(qū)域數(shù)據(jù)需引用VLOOKUP函數(shù)。15考勤表的【公式審核模式】考勤表的【公式審核模式】16第4步建立獎金記錄表第4步建立獎金記錄表17第4步建立獎金記錄表本區(qū)域數(shù)據(jù)需引用VLOOKUP函數(shù)。在E3單元格輸入嵌套的IF()函數(shù)。第4步建立獎金記錄表本區(qū)域數(shù)據(jù)需引用VLOOKUP函數(shù)。在18完善后的獎金記錄表注：本區(qū)域數(shù)據(jù)僅是評定獎金的依據(jù)，該表本身并沒有被引用。引用VLOOKUP函數(shù)。完善后的獎金記錄表注：本區(qū)域數(shù)據(jù)僅是評定獎金的依據(jù)，該表本身19第5步建立補貼記錄表E3單元格中輸入如圖所示IF()函數(shù)。注：本區(qū)域數(shù)據(jù)僅是分配補貼的依據(jù)，該表本身并沒有被引用。第5步建立補貼記錄表E3單元格中輸入如圖所示IF()函數(shù)。20第6步定義“考勤表”名稱考勤表=考勤表!$A$3:$E$13第6步定義“考勤表”名稱考勤表=考勤表!$A$3:$E$121第7步定義“員工獎金記錄表”名稱第7步定義“員工獎金記錄表”名稱22第8步定義“補貼表”名稱第8步定義“補貼表”名稱23第9步建立工資發(fā)放明細表主要工資項的計算依據(jù)第9步建立工資發(fā)放明細表主要工資項的計算依據(jù)24第9步建立工資發(fā)放明細表“工資發(fā)放明細表”中第四行單元格中各公式說明：B4：=VLOOKUP(A4,基本工資,2,0)C4：=VLOOKUP(A4,基本工資,3,0)D4：=VLOOKUP(A4,基本工資,4,0)E4：=VLOOKUP(A4,基本工資,5,0)F4：=VLOOKUP(A4,員工獎金記錄表,5,0)G4：=VLOOKUP(A4,補貼表,5,0)H4：=E4+F4+G4I4：=ROUND(E4/30*(VLOOKUP(A4,考勤表,5,0)),0)J4：=IF(H4>5000,H4*15%,0)K4：=H4-I4-J4第9步建立工資發(fā)放明細表“工資發(fā)放明細表”中第四行單元格中25工資發(fā)放明細表——公式審核模式續(xù)表工資發(fā)放明細表——公式審核模式續(xù)表26第10步定義“工資表”名稱第10步定義“工資表”名稱27第11步建立工資條第11步建立工資條28第11步建立工資條工資發(fā)放明細表中第四行單元格中各公式說明：A3：=NOW()

【顯示當前日期】C3：=VLOOKUP(B3,工資表,2,0)D3：=VLOOKUP(B3,工資表,3,0)E3：=VLOOKUP(B3,工資表,4,0)F3：=VLOOKUP(B3,工資表,5,0)G3：=VLOOKUP(B3,工資表,6,0)H3：=VLOOKUP(B3,工資表,7,0)I3：=VLOOKUP(B3,工資表,8,0)J3：=VLOOKUP(B3,工資表,9,0))K3：=VLOOKUP(B3,工資表,10,0)L3：=VLOOKUP(B3,工資表,11,0)“工資表”名稱在此應用第11步建立工資條工資發(fā)放明細表中第四行單元格中各公式說明29第11步建立工資條利用自動填充方式完善工資條。第11步建立工資條利用自動填充方式完善工資條。30預覽工資條預覽工資條31第12步建立數(shù)據(jù)透視圖（表）第一步：單擊菜單【數(shù)據(jù)】→【數(shù)據(jù)透視表和數(shù)據(jù)透視圖…】，出現(xiàn)如下對話框，選擇紅色方框內(nèi)選項，單擊【下一步】。第12步建立數(shù)據(jù)透視圖（表）第一步：單擊菜單【數(shù)據(jù)】→【數(shù)32第12步建立數(shù)據(jù)透視圖（表）第二步：選定區(qū)域，單擊下一步。第12步建立數(shù)據(jù)透視圖（表）第二步：選定區(qū)域，單擊下一步。33第12步建立數(shù)據(jù)透視圖（表）第三步：選擇【新建工作表】。單擊【布局】。第12步建立數(shù)據(jù)透視圖（表）第三步：選擇【新建工作表】。單34第12步建立數(shù)據(jù)透視圖（表）第四步：將右邊的字段按鈕拖到左邊的圖上相應位置，單擊【確定】。第12步建立數(shù)據(jù)透視圖（表）第四步：將右邊的字段按鈕拖到左35第12步建立數(shù)據(jù)透視圖（表）不同部門不同類別員工的應發(fā)工資統(tǒng)計表（透視表）。第12步建立數(shù)據(jù)透視圖（表）不同部門不同類別員工的應發(fā)工資36第12步建立數(shù)據(jù)透視圖（表）不同部門不同類別員工的應發(fā)工資統(tǒng)計圖（透視圖）第12步建立數(shù)據(jù)透視圖（表）不同部門不同類別員工的應發(fā)工資37附：工資扣款公式請假扣款＝基本工資/30*(請假天數(shù))若應發(fā)工資>5000,所得稅＝應發(fā)工資*15%附：工資扣款公式請假扣款＝基本工資/30*(請假天數(shù))38OVER！OVER！399、有時候讀書是一種巧妙地避開思考的方法。10月-2310月-23Friday,October6,202310、閱讀一切好書如同和過去最杰出的人談話。18:45:5618:45:5618:4510/6/20236:45:56PM11、越是沒有本領(lǐng)的就越加自命不凡。10月-2318:45:5618:45Oct-2306-Oct-2312、越是無能的人，越喜歡挑剔別人的錯兒。18:45:5618:45:5618:45Friday,October6,202313、知人者智，自知者明。勝人者有力，自勝者強。10月-2310月-2318:45:5618:45:56October6,202314、意志堅強的人能把世界放在手中像泥塊一樣任意揉捏。06十月20236:45:56下午18:45:5610月-2