《信息技術(shù)基礎(chǔ)實訓(xùn)（WPS Office）》課件 實訓(xùn)項目3 使用WPS表格

實訓(xùn)項目3

使用WPS表格信息技術(shù)基礎(chǔ)目錄CONTENTS任務(wù)3.1制作與美化課程表任務(wù)3.2統(tǒng)計分析學(xué)生成績?nèi)蝿?wù)3.3綜合實訓(xùn)——企業(yè)收入數(shù)據(jù)的統(tǒng)計與分析01PARTONE任務(wù)3.1制作與美化課程表【任務(wù)描述】用WPS表格處理軟件制作與美化課程表：錄入的信息包含課程、時間等，要求錄入完整。錄入信息的格式要設(shè)置正確。通過設(shè)置行高、列寬、邊框和底紋等，保證表格美觀，易于閱讀?！救蝿?wù)描述】最終效果如下圖所示【任務(wù)分析】啟動WPS表格處理軟件，新建空白表格。根據(jù)表格內(nèi)容，依次錄入標(biāo)題和數(shù)據(jù)。選中相應(yīng)的單元格區(qū)域，打開“單元格格式”對話框，設(shè)置單元格格式。設(shè)置對齊方式、行高列寬、邊框和底紋等，排版美化工作表?！救蝿?wù)實現(xiàn)】1.

新建“課程表”工作簿雙擊桌面“WPSOffice”圖標(biāo)啟動軟件，或在“開始”菜單中選擇“WPSOffice教育版”里的“WPSOffice”命令啟動軟件，選擇“新建→表格→空白表格”命令，即可創(chuàng)建新的工作簿和工作表，單擊快速訪問工具欄中的“保存”按鈕，彈出“另存為”對話框，設(shè)置文件名為“課程表”，保存至指定的目錄，即完成創(chuàng)建。【任務(wù)實現(xiàn)】2.

錄入數(shù)據(jù)打開“課程表”，選中A1單元格，參照素材文件“課程表.txt”，依次將素材的內(nèi)容復(fù)制并粘貼到編輯區(qū)域。注意，在輸入不同的列數(shù)據(jù)時，要設(shè)置對應(yīng)的單元格格式如“制表時間”單元格需要設(shè)置單元格格式為“日期”，如右圖所示：【任務(wù)實現(xiàn)】2.

錄入數(shù)據(jù)錄入數(shù)據(jù)后，表格效果如右圖所示：【任務(wù)實現(xiàn)】3.

排版工作表選擇A1:G1區(qū)域；單擊“開始”選項卡的“合并居中”按鈕，合并該區(qū)域單元格并將單元格里的內(nèi)容居中顯示；單擊字號的下拉菜單，選擇字號為26，并單擊“加粗”按鈕將所選字體內(nèi)容加粗；單擊字體的下拉菜單，選擇字體為“宋體”；單擊“開始”選項卡的“字體顏色”下拉菜單，選擇“標(biāo)準(zhǔn)色”中的“藍色”作為字體顏色?！救蝿?wù)實現(xiàn)】3.

排版工作表選擇A3單元格；單擊“字號”的下拉菜單，設(shè)置字號為12；分別選擇C3:G3和A4:B11兩個區(qū)域，設(shè)置字號為14。分別選擇A2:B2和A12:B13兩個區(qū)域，設(shè)置字號為11。選擇C4:G11區(qū)域，設(shè)置字號為16；選擇A2:G13區(qū)域，設(shè)置字體為“宋體”,并單擊“加粗”按鈕將所選字體內(nèi)容加粗，選擇“標(biāo)準(zhǔn)色”中的“藍色”作為字體顏色?！救蝿?wù)實現(xiàn)】3.

排版工作表分別選擇C4:C5、C6:C7、C8:C9、C10:C11、D4:D5、D6:D7、D8:D9、D10:D11、E4:E5、E6:E7、E8:E9、E10:E11、F4:F5、F6:F7、F8:F9、F10:F11、G4:G6、G8:G9、G10:G11區(qū)域，單擊“開始”選項卡的“合并居中”按鈕，合并該區(qū)域單元格并將單元格里的內(nèi)容居中顯示。分別選擇A4:A7、A8:A11、B12:C12、B13:C13區(qū)域，單擊“開始”選項卡的“合并居中”按鈕，合并該區(qū)域單元格并將單元格里的內(nèi)容居中顯示?！救蝿?wù)實現(xiàn)】3.

排版工作表選擇A3:B3區(qū)域，單擊“開始”選項卡的“合并居中”按鈕，合并該區(qū)域單元格并將單元格里的內(nèi)容居中顯示；選擇A3:G3區(qū)域，單擊“開始”選項卡的“填充顏色”下拉菜單，選擇“巧克力黃，著色2，淺色60%”作為填充顏色，如右圖所示。【任務(wù)實現(xiàn)】3.

排版工作表分別選擇區(qū)域，單擊“開始”選項卡的“合并居中”按鈕，合并該區(qū)域單元格并將單元格里的內(nèi)容居中顯示。分別選擇A4:A7、A8:A11、B12:C12、B13:C13區(qū)域，單擊“開始”選項卡的“合并居中”按鈕，合并該區(qū)域單元格并將單元格里的內(nèi)容居中顯示?！救蝿?wù)實現(xiàn)】3.

排版工作表選擇第1行，單擊“開始”選項卡的“行和列”下拉菜單，單擊“行高”按鈕，在彈出的對話框中設(shè)置行高為固定值35磅，再選擇第3行到第11行以相同的方式設(shè)置行高為固定值40磅；選擇第2行、第12行、第13行，單擊“行和列”下拉菜單中“最適合的行高”按鈕，讓每行的行高自動適配其內(nèi)容；【任務(wù)實現(xiàn)】3.

排版工作表選擇第A列，單擊“開始”選項卡的“行和列”下拉菜單，單擊“列寬”按鈕，在彈出的對話框中設(shè)置列寬為固定值9字符，再選擇第B列、第C列到第G列以相同的方式分別設(shè)置列寬為固定值6字符、固定值20字符?！救蝿?wù)實現(xiàn)】3.

排版工作表分別選擇B4:B11、C3:G3兩個區(qū)域，單擊“開始”選項卡的“水平居中”按鈕，將文字居中對齊；選擇A3:G11區(qū)域，右擊選擇“設(shè)置單元格格式”按鈕，打開“單元格格式”對話框，單擊“邊框”選項卡，給表格添加框線，設(shè)置框線顏色為藍色，所有外邊框線條樣式為雙線型，內(nèi)邊框線條樣式為單線型，A3單元格對角線線條樣式為虛線型?！救蝿?wù)實現(xiàn)】3.

排版工作表排版美化后的效果如右圖所示。【任務(wù)實現(xiàn)】4.

打印工作表單擊“頁面”選項卡的“紙張方向”按鈕，選擇紙張方向為“橫向”。單擊“頁面”選項卡的“打印預(yù)覽”按鈕，查看文檔打印時的效果，對打印做詳細設(shè)置。設(shè)置完成后，單擊打印設(shè)置中的“打印”按鈕，即可打印文檔，打印完成后單擊“退出預(yù)覽”按鈕即可退出打印預(yù)覽。如右圖所示?！救蝿?wù)實現(xiàn)】4.

打印工作表單擊“頁面”選項卡的“紙張方向”按鈕，選擇紙張方向為“橫向”。單擊“頁面”選項卡的“打印預(yù)覽”按鈕，查看文檔打印時的效果，對打印做詳細設(shè)置。設(shè)置完成后，單擊打印設(shè)置中的“打印”按鈕，即可打印文檔，打印完成后單擊“退出預(yù)覽”按鈕即可退出打印預(yù)覽。如右圖所示?！救蝿?wù)實現(xiàn)】5.保護工作簿單擊“審閱”選項卡，在“保護”功能區(qū)單擊“保護工作簿”按鈕，彈出“保護工作簿”對話框，如右圖所示。【任務(wù)實現(xiàn)】在對話框中輸入密碼“123”，單擊“確定”按鈕，會彈出“確認(rèn)密碼”對話框，如圖3-1-8所示，輸入相同的密碼，開啟工作簿的保護功能。5.保護工作簿【任務(wù)拓展】根據(jù)給定素材“公司員工信息表.xlsx”（如圖所示），完成以下設(shè)置。1.設(shè)置文字樣式。設(shè)置第一行文字的字體為仿宋，字號為16，并加粗處理。設(shè)置其余行文字的字體為仿宋，字號為14。2.設(shè)置表格邊框和背景。設(shè)置表格線框為無線框。設(shè)置第一行單元格背景顏色為“鋼藍，著色1”，設(shè)置第2、4、6、8、10行單元格背景顏色為“白色，背景1”，設(shè)置第3、5、7、9、11行單元格背景顏色為“鋼藍，著色1，淺色80%”。3.設(shè)置單元格格式。設(shè)置第F列單元格格式為“日期”。4.設(shè)置最合適的行高和列寬。02PARTTWO任務(wù)3.2

統(tǒng)計與分析學(xué)生成績?nèi)蝿?wù)描述統(tǒng)計出每個學(xué)生的總分、平均分，各班級的平均分、人數(shù)。根據(jù)公式計算出每個學(xué)生的評級。生成班級總平均分統(tǒng)計圖用公式和函數(shù)進行統(tǒng)計計算：任務(wù)描述完成效果如下圖所示：任務(wù)分析打開已經(jīng)創(chuàng)建的“學(xué)生成績分析表”工作表，添加“總分”、“平均分”和“評級”三列。運用SUM求和函數(shù)計算每個學(xué)生的總分。運用AVERAGE函數(shù)，計算每個學(xué)生的平均分。運用IF函數(shù)，計算每個學(xué)生的評級。運用AVERAGEIF函數(shù)，計算班級總平均分。運用COUNTIF函數(shù)，計算班級總?cè)藬?shù)。生成圖表。【任務(wù)實現(xiàn)】1.

打開并編輯“學(xué)生成績分析表”工作表雙擊“學(xué)生成績分析表.xlsx”工作簿圖標(biāo)，打開“學(xué)生成績分析表”工作表，在表格最后一列后繼續(xù)添加數(shù)據(jù)，在H2、I2、J2單元格中分別輸入標(biāo)題“總分”、“平均分”和“評級”。設(shè)置第I列單元格數(shù)字格式為“數(shù)值”，小數(shù)位數(shù)2位。如圖所示?！救蝿?wù)實現(xiàn)】2.

計算“總分”列選擇H3單元格，單擊“公式”選項卡中的“求和”按鈕，調(diào)整數(shù)值區(qū)域為“D3:G3”，單擊編輯欄前的“輸入”按鈕，或按【ENTER】鍵完成當(dāng)前單元格的數(shù)值計算。如圖所示?！救蝿?wù)實現(xiàn)】2.

計算“總分”列拖動填充柄計算其他學(xué)生的總分，如圖所示?！救蝿?wù)實現(xiàn)】3.

計算“平均分”列選擇I3單元格，單擊“公式”選項卡中的“求和”菜單下的“平均值”按鈕，調(diào)整數(shù)值區(qū)域為“D3:G3”，單擊編輯欄前的“輸入”按鈕，或按【ENTER】鍵完成當(dāng)前單元格的數(shù)值計算。如圖所示。【任務(wù)實現(xiàn)】3.

計算“平均分”列拖動填充柄計算其他學(xué)生的平均分，如圖所示。【任務(wù)實現(xiàn)】4.

計算“評級”列計算本列數(shù)值需要使用條件函數(shù)IF，該函數(shù)的功能是判斷一個條件是否滿足：如果滿足則返回一個值，如果不滿足則返回另外一個值。選中I3單元格，單擊“公式”選項卡的“插入”按鈕，選擇函數(shù)類別為“邏輯”，選擇“IF”函數(shù)，點擊確定打開“函數(shù)參數(shù)”對話框，在測試條件框中輸入“I3>=90”，在真值框中輸入“"優(yōu)秀"”，在假值框中輸入“IF(I3>=75,"良好",(IF(I3>=60,"及格","不及格")))”，如圖所示，按【ENTER】鍵完成學(xué)生評級計算，再拖動填充柄計算其他學(xué)生的評級?！救蝿?wù)實現(xiàn)】5.

計算“班級總平均分”列計算本列數(shù)值需要使用函數(shù)AVERAGEIF，該函數(shù)的功能是返回某個區(qū)域內(nèi)滿足給定條件的所有單元格的算術(shù)平均值。選中B20單元格，單擊“公式”選項卡的“插入”按鈕，選擇函數(shù)類別為“統(tǒng)計”，選擇“AVERAGEIF”函數(shù)，點擊確定打開“函數(shù)參數(shù)”對話框，在區(qū)域框中輸入“$A$3:$A$17”，在條件框中輸入“A20”，在求平均值區(qū)域框中輸入“$I$3:$I$17”，其中“$A$3:$A$17”、“$I$3:$I$17”代表對區(qū)域“A3:A17”和區(qū)域“I3:I17”的絕對引用，如圖所示，按【ENTER】鍵完成班級總平均分計算，再拖動填充柄計算其他班級的總平均分?！救蝿?wù)實現(xiàn)】6.

計算“班級總?cè)藬?shù)”列計算本列數(shù)值需要使用函數(shù)COUNTIF，該函數(shù)的功能是計算區(qū)域中滿足給定條件的單元格的個數(shù)。選中C20單元格，單擊“公式”選項卡的“插入”按鈕，選擇函數(shù)類別為“統(tǒng)計”，選擇“COUNTIF”函數(shù)，點擊確定打開“函數(shù)參數(shù)”對話框，在區(qū)域框中輸入“$A$3:$A$17”，在條件框中輸入“A20”，其中“$A$3:$A$17”代表對區(qū)域“A3:A17”的絕對引用，如圖所示，按【ENTER】鍵完成班級總?cè)藬?shù)算，再拖動填充柄計算其他班級的總?cè)藬?shù)?！救蝿?wù)實現(xiàn)】7.

插入分班級顯示平均分柱狀圖選中A19:B22區(qū)域，選擇“插入”選項卡中“全部圖表”按鈕，打開“圖表”對話框，選擇“柱形圖”全部樣式的第一種樣式創(chuàng)建好一張柱形圖，如圖所示?！救蝿?wù)實現(xiàn)】8.

保存工作表單擊快速訪問工具欄中的“保存”按鈕或使用Ctrl+S組合鍵，將修改過的表格保存至計算機中。單擊標(biāo)題欄的“關(guān)閉”按鈕，關(guān)閉工作簿，退出WPS軟件。【任務(wù)實現(xiàn)】完成效果如下圖所示：【任務(wù)拓展】根據(jù)給定素材“業(yè)務(wù)員銷售情況.xlsx”（如圖所示），完成以下設(shè)置。1.在I列計算第一到第四季度銷售總額。2.在J列計算四個季度平均銷售額。3.在K列計算評級。平均銷售額大于或等于30萬元的為“優(yōu)秀”，大于或等于15萬元且小于30萬元的為“良好”，小于15萬元的為“一般”。4.在B15:B17區(qū)域計算三個營銷部的平均銷售額。5.在C15:C17區(qū)域計算三個營銷部的人數(shù)。6.插入徐萍各季度銷售額折線圖。03PARTTHREE任務(wù)3.3綜合實訓(xùn)企業(yè)收入數(shù)據(jù)的統(tǒng)計與分析【任務(wù)描述】企業(yè)收入數(shù)據(jù)的統(tǒng)計與分析：小劉作為某建筑業(yè)企業(yè)的會計，肩負著公司財務(wù)工作的重要任務(wù)。年底公司進行反思總結(jié)，部門經(jīng)理交給小劉兩項任務(wù)，一是要求小劉對公司職工全年收入數(shù)據(jù)進行統(tǒng)計分析，二是要求小劉對建筑業(yè)企業(yè)相關(guān)指標(biāo)進行分析并做出圖形化說明。接到任務(wù)后，小劉認(rèn)真的對數(shù)據(jù)報表進行圖形化制作，并完成了圖表的美化?！救蝿?wù)描述】完成效果如下圖所示：【任務(wù)分析】提取員工姓氏。計算員工月收入。計算員工工齡并隱藏截止日期列。設(shè)置各部門工資的分類匯總。制作建筑企業(yè)相關(guān)指標(biāo)數(shù)據(jù)透視表。制作建筑業(yè)企業(yè)從業(yè)人員數(shù)組合圖。批量替換數(shù)據(jù)。設(shè)置篩選?！救蝿?wù)實現(xiàn)】1.打開工作表雙擊“建筑企業(yè)收入數(shù)據(jù)表.xlsx”工作簿圖標(biāo)，打開“員工工資統(tǒng)計表”工作表?！救蝿?wù)實現(xiàn)】2.提取員工姓氏計算本列數(shù)值需要使用函數(shù)LEFT，該函數(shù)的功能是從一個文本字符串的第一個字符開始返回指定個數(shù)的字符。選中C2單元格，單擊“公式”選項卡的“插入”按鈕，選擇函數(shù)類別為“文本”，選擇“LEFT”函數(shù)，點擊確定打開“函數(shù)參數(shù)”對話框，在字符串框中輸入“B2”，在字符個數(shù)框中輸入“1”，如圖所示?！救蝿?wù)實現(xiàn)】2.提取員工姓氏按【ENTER】鍵完成員工姓氏統(tǒng)計，再拖動填充柄統(tǒng)計其他員工的姓氏，如圖所示。【任務(wù)實現(xiàn)】3.計算員工月收入選擇E2單元格，設(shè)置單元格格式為數(shù)值，小數(shù)位數(shù)為0，如圖所示?！救蝿?wù)實現(xiàn)】3.計算員工月收入在E2單元格中輸入“=D2/12”，如圖所示，按【ENTER】鍵完成計算，再拖動填充柄計算其他員工的月收入?！救蝿?wù)實現(xiàn)】4.計算員工工齡并隱藏截止日期列計算本列數(shù)值需要使用函數(shù)DATEDIF，該函數(shù)的功能是計算兩個日期間的天數(shù)、月數(shù)、或年數(shù)，其中"Y"代表計算時間段中的整年數(shù)，"M"代表計算時間段中的整月數(shù)，"D"代表計算時間段中的天數(shù)。選中H2單元格，單擊“公式”選項卡的“插入”按鈕，選擇函數(shù)類別為“日期與時間”，選擇“DATEDIF”函數(shù)，點擊確定打開“函數(shù)參數(shù)”對話框，在開始日期框中輸入“F2”，在終止日期框中輸入“G2”，在比較單位框中輸入“"Y"”，如圖所示。按【ENTER】鍵完成員工工齡計算，再拖動填充柄計算其他員工的工齡?！救蝿?wù)實現(xiàn)】4.計算員工工齡并隱藏截止日期列選中G列，單擊“開始”選項卡的“行和列”下拉菜單，單擊“隱藏與取消隱藏”菜單中“隱藏列”按鈕，即可隱藏截止日期列，如圖所示?！救蝿?wù)實現(xiàn)】5.設(shè)置各部門工資的分類匯總選擇A1:H17區(qū)域，單擊“數(shù)據(jù)”選項卡中的“分類匯總”按鈕，打開“分類匯總”對話框。在“分類匯總”對話框內(nèi)的分類字段設(shè)置為“部門”，匯總方式為“求和”，選定匯總項為“年收入”、“月收入”，如圖所示?！救蝿?wù)實現(xiàn)】5.設(shè)置各部門工資的分類匯總單擊“確認(rèn)”按鈕，完成分類匯總設(shè)置?！救蝿?wù)實現(xiàn)】6.制作建筑企業(yè)收入數(shù)據(jù)表雙擊“建筑企業(yè)收入數(shù)據(jù)表.xlsx”工作簿圖標(biāo)，打開“建筑企業(yè)相關(guān)指標(biāo)分析表”工作表?！救蝿?wù)實現(xiàn)】7.制作建筑企業(yè)相關(guān)指標(biāo)數(shù)據(jù)透視表選中E7單元格，單擊“插入”菜單中的“數(shù)據(jù)透視表”按鈕，打開“創(chuàng)建數(shù)據(jù)透視表”對話框，選擇單元格區(qū)域“A1:C37”，選擇放置透視表位置“現(xiàn)有工作表”，如圖所示?！救蝿?wù)實現(xiàn)】7.制作建筑企業(yè)相關(guān)指標(biāo)數(shù)據(jù)透視表單擊數(shù)據(jù)透視表區(qū)域，在“數(shù)據(jù)透視表字段”列表中勾選“指標(biāo)”、“年份”、“數(shù)量”字段，并將“指標(biāo)”拖動到“數(shù)據(jù)透視表區(qū)域”的行中，將“年份”拖動到“數(shù)據(jù)透視表區(qū)域”的篩選器中，將“數(shù)量”拖動到“數(shù)據(jù)透視表區(qū)域”的值中，如圖所示。【任務(wù)實現(xiàn)】7.制作建筑企業(yè)相關(guān)指標(biāo)數(shù)據(jù)透視表單擊“求和項：數(shù)量”下拉菜單中的“值字段設(shè)置”按鈕，選擇用于匯總所選字段數(shù)據(jù)的計算類型為“平均值”如圖所示?！救蝿?wù)實現(xiàn)】7.制作建筑企業(yè)相關(guān)指標(biāo)數(shù)據(jù)透視表最后選擇年份篩選器為“2020年”，完成設(shè)置，如圖所示?！救蝿?wù)實現(xiàn)】8.