《信息素養概論》第三章WPS表格處理與分析第3章WPS表格處理與分析目錄員工信息表中數據的編輯2員工工資表中數據的處理4員工工資表中數據的分析5員工工資表中數據的展示與打印6員工工資表中數據的計算3員工信息表的創建1第3章WPS表格處理與分析目錄員工信息表的創建1員工信息表中數據的編輯2員工工資表中數據的計算3員工工資表中數據的處理4員工工資表中數據的分析5員工工資表中數據的展示與打印6問題引入小張是天天向上公司財務部員工，經理需要他制作一份公司員工信息表，用于本月員工工資的計算和分析等，表格文件以“天天向上員工信息表.xlsx”命名保存，表格文件里面包含“員工信息表”等工作表。完成該任務，需熟悉WPS表格的工作界面，掌握WPS表格中工作簿、工作表和單元格的基本操作等內容。操作演示：創建、打開、保存工作簿3.1.1工作簿的基本操作1.新建工作簿2.打開工作簿3.保存工作簿4.關閉工作簿相關知識1.創建一個空白WPS表格保存到D盤，命名為“天天向上員工信息表.xlsx”。任務實施3.1.1工作簿的基本操作學生練習教師操作演示3.1.2工作表的基本操作1.插入工作表2.刪除工作表3.重命名工作表4.設置工作表標簽顏色5.移動或復制工作表6.顯示或隱藏工作表7.保護工作表相關知識1.將“第3章\任務1\標題.xlsx”文件中“表格標題”工作表復制到“天天向上員工信息表.xlsx”文件中；將“天天向上員工信息表.xlsx”文件中的“表格標題”工作表名稱重命名為“員工信息表”，并且復制一個工作表并重命名為“備份”工作表；設置“備份”工作表標簽顏色為“紅色”及刪除“Sheet1”工作表。任務實施3.1.2工作表的基本操作學生練習教師操作演示3.1.3單元格的基本操作1.基本概念2.選擇單元格3.調整行高和列寬4.插入或刪除行列5.合并或拆分單元格相關知識1.將“員工信息表”工作表中的A2單元格和第一行刪除，然后將A1:H1單元格區域合并居中和調整行高為30。任務實施3.1.3單元格的基本操作學生練習第3章WPS表格處理與分析目錄員工信息表中數據的編輯2員工信息表的創建1員工工資表中數據的計算3員工工資表中數據的處理4員工工資表中數據的分析5員工工資表中數據的展示與打印6問題引入小張通過上一任務的相關知識，創建了“員工信息表”工作表，現需錄入工作表內容，并進行整理和美化，并在“員工信息表”工作表基礎上快速制作“月考勤表”工作表。完成該任務，需掌握工作表中數據的編輯等操作，包括工作表中數據錄入、查找與替換、批注、整理和修飾等內容。教師操作演示3.2.1數據錄入1.認識數據類型2.輸入數據3.修改數據4.快速填充數據5.輸入有效數據6.使用下拉列表輸入數據相關知識1.在“員工信息表”工作表中輸入如圖3-11所示的內容。任務實施3.2.1數據錄入學生練習教師操作演示3.2.2查找與替換數據1.查找數據2.替換數據相關知識1.使用替換功能，把在“員工信息表”工作表中姓名為“雷大歷”改為“雷大力”。任務實施3.2.2查找與替換數據學生練習教師操作演示3.2.3批注1.添加批注2.編輯批注3.查看批注4.刪除批注相關知識1.為“員工信息表”工作表中姓名為“雷大力”的單元格添加批注，內容為“更改之前為：雷大歷”。任務實施3.2.3批注學生練習教師操作演示3.2.4整理和修飾1.設置單元格格式2.應用單元格樣式3.應用條件格式4.套用表格樣式相關知識1.整理及美化“員工信息表”工作表，效果如圖3-23所示。任務實施3.2.4整理和修飾學生練習2.使用“員工信息表”工作表，快速完成“月考勤表”工作表的創建、整理及美化，效果如圖3-26所示。任務實施3.2.4整理和修飾學生練習第3章WPS表格處理與分析目錄員工工資表中數據的計算3員工信息表中數據的編輯2員工信息表的創建1員工工資表中數據的處理4員工工資表中數據的分析5員工工資表中數據的展示與打印6問題引入小張通過上一任務完成了“員工信息表”和“月考勤表”工作表的創建、整理及美化。現需結合其他工作表及規定的數據計算每位員工的本月工資，如獎金、應發工資、個稅、實發工資等，并統計各工資段的人數及各部門工資信息。完成該任務，需掌握工作表中數據的計算等操作，包括單元格引用、公式和函數的應用等內容。操作演示3.3.1公式與單元格引用1.輸入公式2.修改公式3.復制公式4.公式中的運算符5.引用單元格相關知識1.將上一任務完成的“員工信息表”和“月考勤表”工作表復制到“\任務3\工資表.xlsx”工作簿中，并且計算“月考勤表”工作表中的資金項數據，使用J10:K16單元格區域中給定的條件計算每位員工的獎金，如圖3-30所示。獎金的計算公式為：獎金=遲到次數ⅹ扣除金額+早退次數ⅹ扣除金額+加班天數ⅹ獎勵金額+請假天數ⅹ扣除金額。因為遲到和早退扣除的金額相同，因此公式可以改為：獎金=(遲到次數+早退次數)ⅹ扣除金額+加班天數ⅹ獎勵金額+請假天數ⅹ扣除金額。任務實施3.3.1公式與單元格引用學生練習教師操作演示3.3.2函數1.函數的概念2.函數的結構3.函數的類型4.使用函數相關知識1.用最大值函數MAX()，在“月獎金表”工作表中，計算員工所得的“最高獎金額”。2.用最小值函數MIN、求和函數SUM、求平均值函數AVERAGE，在“月獎金表”工作表中，計算出員工的“最低獎金額”、“總獎金額”、“平均獎金額”。3.用排序函數RANK，計算出員工所得獎金的“名次”。RANK函數：返回某數字在一列數字中相對于其他數值的大小排名。格式：RANK(number,ref,order)。Number(數值)：指定的數字。Ref(引用)：一組數或對一個數據列表的引用。Ref中的非數字值將被忽略。Order(排位方式)：指定排位的方式。如果order為0或省略，按照降序排列；非零，則按照升序排列。任務實施13.3.2函數學生練習4.使用日期函數TODAY、YEAR，在“員工信息表”工作表中計算出每位員工的“工齡”。計算“工齡”的公式為：工齡=當前年份-參加工作年份。其中，在計算“當前年份”時，先使用TODAY函數獲取系統日期，再用YEAR函數獲取當年的年份，即當前年份=YEAR(TODAY())；參加工作年份=YEAR(工作日期)。5.在“月應發工資表”工作表中的“工齡”列引入“員工信息表”工作表中的“工齡”列數據。任務實施23.3.2函數學生練習6.使用“VLOOKUP”函數，參照“工資對照表”工作表中的“職位工資”表，在“月應發工資表”工作表中計算出“職位工資”。VLOOKUP函數：按列查找，最終返回該列所需查詢列序所對應的值。格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。Lookup_value(查找值)：為需要在數據表第一列中進行查找的數值，可以為數值、引用或文本字符串。Table_array(數據表)：為需要在其中查找數據的數據表。col_index_num(列序數)：為table_array中待返回的匹配值的列序號。col_index_num為1時，返回table_array第一列的數值，以此類推。Range_lookup(匹配條件)：為一邏輯值，指明函數VLOOKUP查找時是精確匹配，還是近似匹配。如果為false或0，則返回精確匹配，如果找不到，則返回錯誤值#N/A。任務實施33.3.2函數學生練習7.在“月應發工資表”工作表中的“獎金”列引入“月考勤表”工作表中的“獎金”列數據。8.使用邏輯判斷函數IF，參照“工資對照表”工作表中的“工齡工資”表，實現在“月應發工資表”工作表中計算出“工齡工資”。IF函數：是判斷一個條件是滿足，如果滿足返回一個值，如果不滿足則返回另一個值。格式：IF(logical_test,Value_if_true,Value_if_false)。logical_test(測試條件)：表示計算結果可判斷為True或False的任意值或表達式。Value_if_true(真值)：表示“logical_test”結果為True時的值。Value_if_false(假值)：表示“logical_test”結果為False時的值。例如：IF(1>=2,"條件為真","條件為假")，因為表達式“1>=2”的結果為False，所以該IF函數的返回結果為“條件為假”。IF函數還可以用“Value_if_true(真值)”及“Value_if_false(假值)”參數構造復雜的判斷條件，形成函數的多層嵌套。任務實施43.3.2函數學生練習9.計算“月應發工資表”工作表中的“應發工資”，并把值引入到“月實發工資表”工作表中的“應發工資”。計算方法為：應發工資=工齡工資+職位工資+獎金。任務實施53.3.2函數學生練習10.計算“月實發工資表”工作表的養老保險、醫療保險、失業保險、住房公積金。三險一金一般包括養老保險、醫療保險、失業保險、住房公積金。其中，個人繳納部分的計算方法如下：養老保險=基本工資*8%，醫療保險=基本工資*2%失業保險=基本工資*1%，住房公積金=基本工資*10%其中，基本工資=工齡工資+職位工資下面以“養老保險”計算為例進行介紹，操作如下：選中“月實發工資表”工作表中的F3單元格，輸入“=”，之后單擊“月應發工資表”工作表中的G3單元格，之后輸入“+”，再點擊“月應發工資表”工作表中的H3單元格，然后，將兩者用括號括起來，再在后面乘以0.08，按【Enter】鍵，此時在G3單元格的編輯欄中顯示的最終公式為“=(月應發工資表!G3+月應發工資表!H3)*0.08”，使用填充柄填充剩下的單元格。其余醫療保險、失業保險、住房公積金的計算方法類似，請讀者自行完成。任務實施63.3.2函數學生練習11.參照“稅率計算表”工作表中的“稅率表”，計算“月實發工資表”工作表的“應納稅額”和“個稅”。應納稅額是對月收入超過5000以上的部分進行征稅，計算方法為：應納稅額=每月工資(薪金)所得-三險一金-起征點(5000)其中“每月工資(薪金)所得”為每月的“應發工資”，因此，應納稅額可以使用IF函數計算，公式為“=IF(E3>=5000,E3-F3-G3-H3-I3-5000,0)”。個稅的計算方法為：個稅=應納稅額(月)×適用"稅率"。假設，在本任務該公司的應納稅額最高不超過12000的情況下，則K3單元格的計算公式為“=IF(J3<=3000,J3*0.03,J3*0.1-210)”。12.在“月實發工資表”工作表中計算出“實發工資”。計算方法為：實發工資=應發工資-個稅任務實施73.3.2函數學生練習13.用統計函數COUNTA或COUNT，將“月實發工資表”工作表中實發工資的總人數統計到“月工資統計表”的相應單元格中。14.用條件統計函數COUNTIF，將“月實發工資表”中各實發工資段的人數統計到“月工資統計表”的相應單元格中。15.使用COUNTIF函數，將“月實發工資表”工作表中各部門的人數統計到“月工資統計表”工作表的相應單元格中。16.使用SUMIF函數，將“月應發工資表”工作表中各部門的獎金、應發工資和“月實發工資表”工作表中各部門實發工資統計到“月工資統計表”工作表的相應單元格中。任務實施83.3.2函數學生練習第3章WPS表格處理與分析目錄員工工資表中數據的處理4員工信息表中數據的編輯2員工工資表中數據的計算3員工信息表的創建1員工工資表中數據的分析5員工工資表中數據的展示與打印6問題引入小張通過上一任務完成了“月應發工資表”工作表數據的計算。為了更直觀地查看、理解并快速查找需要的數據，現需對工作表中的數據進行適當的處理，如排序、篩選、分類匯總等。完成該任務，需掌握工作表中數據的處理等操作，包括有數據的排序，數據的篩選、數據的分類匯總等內容。教師操作演示3.4.1數據的排序1.簡單排序2.多條件排序相關知識1.在“第3章\任務4\工資表.xlsx”文件中，將“月應發工資表”工作表創建2個副本，分別重命名為“簡單排序”和“多條件排序”；在“簡單排序”工作表中實現所有數據按“應發工資”降序排序；在“多條件排序”工作表中實現所有數據按“應發工資”列降序排序，當“應發工資”相同時按“姓名”升序排序。任務實施3.4.1數據的排序學生練習教師操作演示3.4.2數據的篩選1.自動篩選2.高級篩選相關知識1.將“月應發工資表”工作表創建2個副本，分別重命名為“自動篩選”和“高級篩選”；在“自動篩選”工作表中，篩選出“銷售部”并且“工齡”10年以上的員工信息；在“高級篩選”工作表中，篩選出“研發部”獎金大于等于2000或者“客服部”的“應發工資”大于等于10000的員工信息，并顯示在L6單元格開始的區域中。任務實施3.4.2數據的篩選學生練習教師操作演示3.4.3數據的分類匯總1.分類匯總2.多重分類匯總相關知識1.將“月應發工資表”工作表創建2個副本，分別重命名為“分類匯總”和“多重分類匯總”；在“分類匯總”工作表中匯總出各部門“工齡工資”和“獎金”的最大值；在“多重分類匯總”工作表中匯總出各部門男女職工的“工齡工資”和“獎金”的最小值。任務實施3.4.3單元格的基本操作學生練習第3章WPS表格處理與分析目錄員工工資表中數據的分析5員工信息表中數據的編輯2員工工資表中數據的計算3員工工資表中數據的處理4員工信息表的創建1員工工資表中數據的展示與打印6問題引入小張通過之前的任務完成了“月工資統計表”工作表的統計和“月應發工資表”工作表的計算。為了讓平面、抽象的數據變得立體、形象并能直觀地反映數據信息，現需要使用圖形化表現形式對數據分析，如圖表、數據透視表、數據透視圖等。完成該任務，需掌握工作表中數據的分析等操作，包括有圖表的創建、編輯和美化及數據透視表和數據透視圖的操作等內容。操作演示：創建、打開、保存工作簿3.5.1創建圖表1.插入圖表2.移動圖表3.圖表的組成相關知識1.在“月工資統計表”工作表中，根據各部門總計金額制作圖表，圖表類型為“簇狀柱形圖”；將圖表移動到新的工作表中并命名為“各部門工資統計圖”。任務實施3.5.1創建圖表學生練習教師操作演示3.5.2編輯并美化圖表1.圖表布局2.圖表樣式3.選擇數據4.更改類型5.圖表模板相關知識1.對上一任務創建的“各部門工資統計圖”工作表進行編輯，要求：

更改圖表的布局為“布局1”；將圖表標題為“各部門工資統計圖”；將橫坐標軸標題為“部門”和縱坐標軸標題為“金額”并設置縱坐標軸標題的文字方向為“豎排”；從圖表中刪除“獎金總計”項數據。2.對“各部門工資統計圖”工作表進行美化，要求：

將圖表標題設置為“楷體、紅色、20號”；設置繪圖區的填充顏色為“金色，背景2，深色50%”；設置圖例的輪廓顏色為“藍色”。任務實施3.5.2編輯并美化圖表學生練習教師操作演示3.5.3數據透視表1.創建數據透視表2添加字段3.設置值字段數據格式4.設置值字段匯總方式5.排序和篩選字段項6.美化數據透視表相關知識1.在“月應發工資表”工作表中，根據每個部門男女職工的應發工資數據創建一個以部門為列，性別為行，對應發工資求和的數據透視表，并命名為“應發工資分析表”，完成后如圖3-71所示。2．將數據透視表中的數據格式設置為貨幣型并將數據透視表中的值字段匯總方式設置為平均值，完成后如圖3-72所示。3.將數據透視表按性別“降序”排列，再篩選出部門的應發工資平均值大于或等于“11000”的記錄；將數據透視表應用“數據透視表樣式中等深淺13”樣式并添加邊框，完成后如圖3-73所示。。任務實施3.5.3數據透視表學生練習教師操作演示3.5.4創建數據透視圖1.創建數據透視圖2.移動數據透視圖3.更改數據透視圖類型相關知識1.在“月應發工資表”工作表中，根據每個部門男女職工的應發工資數據創建一個以部門為列，性別為行，對應發工資求和的數據透視圖，并命名為“應發工資分析圖1”，完成后如圖3-74所示。2.將數據透視圖移到一個新的工作表中并命令為“應發工資分析圖2”；更新數據透視