excel的應用工資管理

1、第七次實驗 員工工資管理 2011.09 實驗內容 ? 一、本實驗知識點 ? 二、設計步驟 建立基本數(shù)據(jù)表格 工資管理系統(tǒng)的創(chuàng)建 建立工作表并輸入數(shù)據(jù) 增加查詢數(shù)據(jù) 制作工資條 保存模板 工資管理系統(tǒng)模板的創(chuàng)建 工資表的管理 分析工資數(shù)據(jù) 工資條的制作 打印工資條 使用模板 一、本實驗知識點 ? 1.名稱的定義 ? 2. ? 3. ? 4. 自動篩選 函數(shù)的應用:IF() 數(shù)據(jù)透視表、數(shù)據(jù)透視圖 、VLOOKUP() 1.名稱的定義 ? 在EXCEL工作表中，可以使用表格中的列標志和行 標志來引用這些行和列中的單元格，還可通過創(chuàng) 建名稱來代表單元格、 單元格區(qū)域、公式或者常 量值。如果公式引用

2、的是相同工作表中的數(shù)據(jù)， 可以使用行標志或者列標志；如果想引用另一張 工作表上的數(shù)據(jù)，則可使用名稱。 ? 本實驗在進行工資數(shù)據(jù)的輸入以及工資條的制作 時就用到了此功能， 可以對預先設置好的單元格 中的數(shù)據(jù)內容直接進行引用，減少錯誤率 。 1.名稱的定義 ? 基本工資=基本工資!$A$2:$E$13 ? 工資表=工資表!$A$3:$K$14 ? 補貼表=補貼記錄表!$A$2:$E$13 ? 考勤表=考勤表!$A$2:$E$13 ? 員工獎金記錄表=獎金記錄表!$A$2:$E$13 2.自動篩選 ? 數(shù)據(jù)篩選是指把數(shù)據(jù)庫或者數(shù)據(jù)清單中所有不滿 足條件的數(shù)據(jù)記錄隱藏起來，只顯示滿足條件的 數(shù)據(jù)記錄，

3、該條件由用戶針對某列指定。常用的 篩選方法有自動篩選、自定義篩選和高級篩選 3種， 篩選是查找和處理區(qū)域中數(shù)據(jù)子集的重要的快捷 方式。 ? 本實驗在對工資數(shù)據(jù)信息進行查詢時就用到了其 中的自動篩選功能， 通過簡單的篩選操作，篩選 出要查詢的數(shù)據(jù)。 3.函數(shù)的應用 ?（1）VLOOKUP（）用法 ?在表格數(shù)組的首列查找值，并由此返回表格數(shù)組當前行中其他列的值。 VLOOKUP 中的 V 表示垂直方向。當比較值位于需要查找的數(shù)據(jù)左邊的一列時，可 以使用 VLOOKUP。 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Loo

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

5、ay 第一列中的值是由 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 為邏輯值，指定希望 VLO

6、OKUP 查找精確的匹配值還是近似匹配值： 例如： VLOOKUP(A4, 基本工資 !$A$2:$E$13,2,0) 3.函數(shù)的應用 ?（2）IF()用法 ?IF(logical_test,value_if_true,value_if_false) Logical_test 表示計算結果為 TRUE 或 FALSE 的任意值或表達式。例 如，A10=100 就是一個邏輯表達式，如果單元格 A10 中的值等于 100，表 達式即為 TRUE，否則為 FALSE。本參數(shù)可使用任何比較運算符。 Value_if_true logical_test 為 TRUE 時返回的值。例如，如果本參數(shù) 為文本

7、字符串“預算內”而且 logical_test 參數(shù)值為 TRUE，則 IF 函數(shù) 將顯示文本“預算內”。如果 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 且忽略了 Va

8、lue_if_false（即 value_if_true 后沒有逗號），則會返回邏輯值 FALSE。如果 logical_test 為 FALSE 且 Value_if_false 為空（即 value_if_true 后有逗號，并緊跟著右括號），則本參數(shù)返回 0（零）。 Value_if_false 也可以是其他公式。 4.數(shù)據(jù)透視表、圖 ? 數(shù)據(jù)透視圖是以圖形的形式來表達數(shù)據(jù)透 視表中的數(shù)據(jù)，用戶可以像更改數(shù)據(jù)透視 表那樣對數(shù)據(jù)透視圖的布局和數(shù)據(jù)的顯示 方式進行修改。 ? 本實驗在進行工資數(shù)據(jù)的分析時就用到了 此功能，通過創(chuàng)建數(shù)據(jù)透視圖，可以更加 方便地對大量的工資數(shù)據(jù)進行查看和匯總 分析

9、。 二、實驗步驟 ? 1.建立基本表： 基本工資表 考勤表 獎金記錄表 補貼記錄表 ? 2.建立工資匯總表 ? 3.制作工資條 ? 4.生成透視表、透視圖 第1步 建立基本工資表 第2步 定義“基本工資”名稱 ? 由于【基本工資】表中的數(shù)據(jù)在后面很多地方都要用到， 為了方便起見，可按圖中方式定義“基本工資”名稱（也 叫別名）： 基本工資基本工資= =基本工資基本工資!$A$3:$E$13 !$A$3:$E$13 第3步 建立考勤表 在姓名這一列的在姓名這一列的B3B3單元單元 格輸入函數(shù)。原參數(shù)格輸入函數(shù)。原參數(shù) “基本工資基本工資!$A$3:$E$13” 現(xiàn)在用參數(shù)現(xiàn)在用參數(shù)“基本工資基本工

10、資” 代替。代替。 第3步 建立考勤表 本區(qū)域數(shù)據(jù)需引用本區(qū)域數(shù)據(jù)需引用 VLOOKUPVLOOKUP函數(shù)。函數(shù)。 考勤表的【公式審核模式】 第4步 建立獎金記錄表 第4步 建立獎金記錄表 在在E3單元格輸入單元格輸入 嵌套的嵌套的IF()函數(shù)。函數(shù)。 本區(qū)域數(shù)據(jù)需引用本區(qū)域數(shù)據(jù)需引用 VLOOKUPVLOOKUP函數(shù)。函數(shù)。 完善后的獎金記錄表 引用引用VLOOKUPVLOOKUP函數(shù)。函數(shù)。 注：本區(qū)域數(shù)據(jù)僅注：本區(qū)域數(shù)據(jù)僅 是評定獎金的依據(jù)，是評定獎金的依據(jù)， 該表本身并沒有被該表本身并沒有被 第5步 建立補貼記錄表 E3E3單元格中輸入如圖單元格中輸入如圖 所示所示IF()IF()函數(shù)

11、。函數(shù)。 注：本區(qū)域數(shù)據(jù)僅注：本區(qū)域數(shù)據(jù)僅 是分配補貼的依據(jù)，是分配補貼的依據(jù)， 該表本身并沒有被該表本身并沒有被 第6步 定義“考勤表”名稱 考勤表考勤表= =考勤表考勤表!$A$3:$E$13 !$A$3:$E$13 第7步 定義“員工獎金記錄表”名 稱 第8步 定義“補貼表”名稱 第9步 建立工資發(fā)放明細表 主要工資項主要工資項 的計算依據(jù)的計算依據(jù) 第9步 建立工資發(fā)放明細表 ? “ 工資發(fā)放明細表”中 第四行單元格中各公式說明： B4：=VLOOKUP(A4,基本工資,2,0) C4：=VLOOKUP(A4,基本工資,3,0) D4：=VLOOKUP(A4,基本工資,4,0) E4：

12、=VLOOKUP(A4,基本工資,5,0) F4：=VLOOKUP(A4,員工獎金記錄表,5,0) G4：=VLOOKUP(A4,補貼表,5,0) H4：=E4+F4+G4 I4：=ROUND(E4/30*(VLOOKUP(A4,考勤表,5,0),0) J4：=IF(H45000,H4*15%,0) K4：=H4-I4-J4 工資發(fā)放明細表公式審核模式 續(xù)續(xù) 表表 第10步 定義“工資表”名稱 第11步 建立工資條 第11步 建立工資條 ? ? ? ? ? ? ? ? ? ? ? ? 工資發(fā)放明細表中第四行單元格中各公式說明： A3：=NOW() 【顯示當前日期】 C3：=VLOOKUP(B3

13、,工資表,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步 建立工資條 利用自動填充方式利用自動填充方式 完善工資條。完善工資條。 預覽工資條 第12步 建立數(shù)據(jù)透視圖（表） ? 第一步：單擊菜單【數(shù)據(jù)】【數(shù)據(jù)透視表和數(shù) 據(jù)透視圖】，出現(xiàn)如下對話框，選擇紅色方框 內選項，單擊【下一步】。 第12步 建立數(shù)據(jù)透視圖（表） ? 第二步：選定區(qū)域，單擊下一步。 第12步 建立數(shù)據(jù)透視圖（表） ? 第