項目七

WPS表格數(shù)據(jù)編輯、運算、統(tǒng)計操作及其AI應(yīng)用制作員工工資表、員工出勤情況統(tǒng)計表項目總要求李先生在某公司從事人事管理工作，每個月末需要處理大量的員工工資信息。處理員工工資信息時既需要輸入大量的員工基本信息及與工資相關(guān)的原始數(shù)據(jù)，還需要對這些數(shù)據(jù)進(jìn)行統(tǒng)計計算，得出每名員工的實發(fā)工資。最終，提交給人事組織部門WPS表格文件“員工工資情況總覽.xlsx”。解決方案“員工工資.xlsx”工作簿的制作可以分解為兩部分進(jìn)行。1．制作員工工資表①在初始“員工工資情況總覽.xlsx”工作簿中，復(fù)制員工職位表，產(chǎn)生新的工作表，重命名為“員工工資表”。②制作“員工工資表”的表結(jié)構(gòu)，并復(fù)制相關(guān)數(shù)據(jù)。③利用公式和IF函數(shù)完成員工工資表中數(shù)據(jù)的計算。2．制作員工出勤情況統(tǒng)計表①插入新的工作表，重命名為“員工出勤情況統(tǒng)計表”。②引用工作表間的數(shù)據(jù)，利用COUNTIF函數(shù)統(tǒng)計員工加班、請假總?cè)藬?shù)。③利用COUNT函數(shù)統(tǒng)計員工總數(shù)，并使用公式分別計算加班人數(shù)、請假人數(shù)占員工總數(shù)的百分比。④利用SUM函數(shù)計算員工加班、請假的總工時，并計算公司工資總額。⑤利用MAX函數(shù)統(tǒng)計員工加班、請假的最長工時。⑥利用AVERAGE函數(shù)計算員工加班、請假的平均工時。效果展示員工工資表最終效果員工出勤情況統(tǒng)計表任務(wù)一

任務(wù)實現(xiàn)一、創(chuàng)建員工工資表二、工作表間的數(shù)據(jù)復(fù)制及條件格式標(biāo)識三、使用公式及函數(shù)完成“員工工資表”的制作制作“員工工資表”一、創(chuàng)建員工工資表選中工作表“員工職位表”崗位工資數(shù)據(jù)區(qū)域E2:E31，單擊【數(shù)據(jù)】|【數(shù)據(jù)工具】|【有效性】按鈕，打開“數(shù)據(jù)有效性”對話框，進(jìn)行相應(yīng)設(shè)置（假設(shè)崗位工資限定為2000～50000的整數(shù)）。再次單擊【數(shù)據(jù)】|【數(shù)據(jù)工具】|【有效性】按鈕的下拉箭頭，在打開的下拉菜單中選擇“圈釋無效數(shù)據(jù)”命令，此時E2:E31中不屬于有效范圍的數(shù)據(jù)將被圈釋。步驟1：有效性檢查一、創(chuàng)建員工工資表在“員工職位表”工作表標(biāo)簽上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“創(chuàng)建副本”命令，產(chǎn)生了一個新的工作表“員工職位表（2）”。雙擊“員工職位表（2）”工作表標(biāo)簽，使其工作表名成為編輯狀態(tài)，重命名為“員工工資表”。步驟2：復(fù)制工作表選中F1:L1數(shù)據(jù)區(qū)域，依次輸入員工工資表的標(biāo)題：加班工時、加班工資、滿勤獎金、應(yīng)發(fā)金額、請假工時、請假扣款、實發(fā)金額。步驟3：修改表結(jié)構(gòu)一、創(chuàng)建員工工資表二、工作表間的數(shù)據(jù)復(fù)制及條件格式標(biāo)識選中“員工加班請假情況匯總表”的D列數(shù)據(jù)，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“復(fù)制”命令，復(fù)制加班工時數(shù)據(jù)。選中“員工工資表”的F2單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“粘貼為數(shù)值”按鈕，完成加班工時數(shù)據(jù)的復(fù)制。還可以在彈出的快捷菜單中單擊“選擇性粘貼”按鈕，打開“選擇性粘貼”對話框，選中“數(shù)值”單選按鈕，完成加班工時數(shù)據(jù)的復(fù)制。說明：默認(rèn)情況下，在WPS表格中進(jìn)行復(fù)制（或剪切）和粘貼時，原來單元格或單元格區(qū)域的數(shù)據(jù)、格式、公式、有效性驗證和批注等都將被粘貼到目標(biāo)單元格，這也是按Ctrl+V組合鍵粘貼時發(fā)生的情況。由于這可能不是用戶想要的，“選擇性粘貼”功能可以選擇內(nèi)容的數(shù)值、公式、格式、批注、有效性驗證等選項進(jìn)行復(fù)制，具體取決于要復(fù)制的內(nèi)容。二、工作表間的數(shù)據(jù)復(fù)制及條件格式標(biāo)識選中“員工工資表”中的F列數(shù)據(jù)，單擊【開始】|【樣式】|【條件格式】|【突出顯示單元格規(guī)則】|【其他規(guī)則】命令，在彈出的“新建格式規(guī)則”對話框中，“選擇規(guī)則類型”設(shè)置為“只為包含以下格式的單元格設(shè)置格式”，“編輯規(guī)則說明”中設(shè)置單元格值為大于或等于30，單擊“格式”按鈕，彈出“單元格格式”對話框，設(shè)置字體為粗體。完成后，大于或等于30的加班工時將加粗顯示。單擊G2單元格，使G2單元格成為活動單元格，輸入“=”，單擊F2單元格（用以引用單元格地址F2），此時活動單元格仍為G2單元格，其內(nèi)容變?yōu)椤?F2”。接著輸入“*40”，最終G2單元格內(nèi)容為“=F2*40”，按Enter鍵結(jié)束輸入，此時G2單元格內(nèi)容顯示為公式計算結(jié)果值，編輯欄內(nèi)顯示公式。步驟1：計算加班工資三、使用公式及函數(shù)完成“員工工資表”的制作使用選擇性粘貼完成加班工資的計算。先復(fù)制G2單元格中的內(nèi)容，再選中G3:G31單元格區(qū)域，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“選擇性粘貼”命令，在打開的“選擇性粘貼”對話框中選中“公式”單選按鈕，單擊“確定”按鈕完成加班工資的計算。步驟1：計算加班工資三、使用公式及函數(shù)完成“員工工資表”的制作單擊H2單元格，使H2單元格成為活動單元格，單擊編輯欄左邊的“插入函數(shù)”按鈕，打開“插入函數(shù)”對話框，在“全部函數(shù)”選項卡中選擇函數(shù)為IF函數(shù)，單擊“確定”按鈕，打開“函數(shù)參數(shù)”對話框。在“函數(shù)參數(shù)”對話框中，將光標(biāo)定位在第一個參數(shù)“測試條件”處，輸入“J2=0”，并依次在“真值”和“假值”參數(shù)處分別輸入數(shù)值“300”和“0”，如圖7-9所示，單擊“確定”按鈕完成函數(shù)的應(yīng)用。步驟2：計算滿勤獎金三、使用公式及函數(shù)完成“員工工資表”的制作單擊I2單元格，使I2單元格成為活動單元格，輸入“=”，單擊E2單元格，此時活動單元格仍為I2單元格，其內(nèi)容為“=E2”。接著輸入“+”，單擊G2單元格，再輸入“+”，單擊H2單元格，按Enter鍵結(jié)束輸入，最終I2單元格中的內(nèi)容為“=E2+G2+H2”。此時，I2單元格的值等于E2、G2、H2單元格的值之和，結(jié)果為9200。使用自動填充功能完成應(yīng)發(fā)金額的計算。步驟3：計算應(yīng)發(fā)金額三、使用公式及函數(shù)完成“員工工資表”的制作選中L2單元格，直接輸入“=I2-K2”，按Enter鍵結(jié)束輸入，即得到其實發(fā)金額為8880元。再使用自動填充功能完成其他實發(fā)金額的計算。步驟4：計算實發(fā)金額三、使用公式及函數(shù)完成“員工工資表”的制作按Ctrl鍵，依次單擊列標(biāo)E、G、H、I、K、L，同時選中這6列與工資金額相關(guān)的列。單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“設(shè)置單元格格式”命令，在打開的“單元格格式”對話框“數(shù)字”選項卡“分類”列表中選擇“貨幣”，小數(shù)位數(shù)設(shè)置為0，貨幣符號選擇“￥”。步驟5：設(shè)置數(shù)字格式三、使用公式及函數(shù)完成“員工工資表”的制作單擊【開始】|【樣式】|【套用表格樣式】按鈕，在打開的下拉列表中選擇“表樣式2”。步驟6：美化工作表三、使用公式及函數(shù)完成“員工工資表”的制作任務(wù)二

任務(wù)實現(xiàn)一、制作“員工出勤情況統(tǒng)計表”二、使用函數(shù)進(jìn)行統(tǒng)計制作“員工出勤情況統(tǒng)計表”一、制作“員工出勤情況統(tǒng)計表”插入新工作表，命名為“員工出勤情況統(tǒng)計表”，如右圖所示為員工出勤情況統(tǒng)計表的表結(jié)構(gòu)。二、使用函數(shù)進(jìn)行統(tǒng)計選中“員工出勤情況統(tǒng)計表”工作表的B3單元格，單擊編輯欄左邊的“插入函數(shù)”按鈕，選擇COUNTIF函數(shù)，完成參數(shù)設(shè)置，如下圖所示，統(tǒng)計加班人數(shù)。選中B4單元格，插入COUNTIF函數(shù)，統(tǒng)計請假人數(shù)，B4單元格內(nèi)的公式的完整形式為“=COUNTIF(員工工資表!J2:J31,”>0“)”。步驟1：完成出勤情況統(tǒng)計二、使用函數(shù)進(jìn)行統(tǒng)計選中“員工出勤情況統(tǒng)計表”工作表的C7單元格，插入COUNT函數(shù)，計算公司員工總數(shù)的參數(shù)Value1是“員工工資表!A2:A31”，函數(shù)輸入完畢后，C7單元格內(nèi)公式的完整形式為“=COUNT(員工工資表!A2:A31)”。步驟2：計算員工總數(shù)二、使用函數(shù)進(jìn)行統(tǒng)計選中“員工出勤情況統(tǒng)計表”工作表的C3單元格，輸入公式“=B3/$C$7”，計算加班人數(shù)占員工總數(shù)的百分比，如圖7-13所示。再使用自動填充功能將公式復(fù)制到C4單元格，此時C4單元格中的公式為“=B4/$C$7”。在“單元格格式”對話框中設(shè)置C3:C4單元格區(qū)域的數(shù)值類型為百分比，小數(shù)位數(shù)設(shè)為1，完成加班人數(shù)、請假人數(shù)占員工總數(shù)的百分比。步驟3：計算比例二、使用函數(shù)進(jìn)行統(tǒng)計計算加班總工時，選中D3單元格，單擊編輯欄左邊的“插入函數(shù)”按鈕，選擇SUM函數(shù)，完成設(shè)置，如下圖所示。計算請假總工時：選中D4單元格，插入SUM函數(shù)，計算請假總工時的Number1參數(shù)是“員工工資表!J2:J31”，參數(shù)輸入完畢后，D4單元格內(nèi)公式的完整形式為“=SUM(員工工資表!J2:J31)”。選中C6單元格，插入SUM函數(shù)，計算工資總額的Number1參數(shù)是“員工工資表!L2:L31”，參數(shù)輸入完畢后，C6單元格內(nèi)公式的完整形式為“=SUM(員工工資表!L2:L31)”。步驟4：計算加班、請假總工時和工資總額二、使用函數(shù)進(jìn)行統(tǒng)計計算最長加班工時，單擊E3單元格，單擊編輯欄左邊的“插入函數(shù)”按鈕，選擇MAX函數(shù)，如下圖所示，完成函數(shù)參數(shù)的設(shè)置后，E3單元格內(nèi)公式的完整形式為“=MAX(員工工資表!F2:F31)”。計算最長請假工時，選中E4單元格，插入MAX函數(shù)，E4內(nèi)公式的完整形式為“=MAX(員工工資表!J2:J31)”。步驟5：統(tǒng)計最長工時二、使用函數(shù)進(jìn)行統(tǒng)計計算平均加班工時，單擊F3單元格，單擊編輯欄左邊的“插入函數(shù)”按鈕，選擇AVERAGE函數(shù)，如下圖所示，設(shè)置Number1參數(shù)為“員工工資表!F2:F31”，完成統(tǒng)計。計算平均請假工時，選中F4單元格，插入AVERAGE函數(shù)，F(xiàn)4單元格內(nèi)公式的完整形式為“=AVERAGE(員工工資表!J2:J31)”。完成后的員工出勤情況統(tǒng)計表如下圖所示。步驟6：統(tǒng)計平均工時任務(wù)三

任務(wù)實現(xiàn)一、使用AI寫公式二、使用AI進(jìn)行條件格式標(biāo)識WPS表格的AI應(yīng)用一、使用AI寫公式選中“員工工資表”工作表的L34單元格，單擊選項卡右側(cè)的WPSAI按鈕，彈出下拉菜單，選擇“AI寫公式”命令，彈出“AI寫公式”面板，如圖7-18（a）所示，在面板的文本框中輸入計算要求“對L列求和”，單擊“發(fā)送”按鈕，面板變?yōu)閳D7-18（b）所示形式，AI自動推薦求和函數(shù)SUM，確認(rèn)無誤后，單擊“完成”按鈕。步驟1：AI推薦公式解釋一、使用AI寫公式在7.3.2任務(wù)3中使用的計數(shù)函數(shù)是COUNT，而此處的計數(shù)函數(shù)是COUNTA，它們有何區(qū)別？單擊“AI寫公式”面板上的“fx對公式的解釋”按鈕，面板如圖7-20所示，可以看到COUNTA函數(shù)的作用是計算參數(shù)列表中值的個數(shù)，可以用于各類數(shù)據(jù)的計數(shù)。將圖7-20所示文本框中的函數(shù)修改為COUNT，單擊“fx對公式的解釋”右側(cè)的“刷新公式解釋”按鈕，可以看到COUNT函數(shù)的作用是計算參數(shù)列表中數(shù)字的個數(shù)，只用于統(tǒng)計包含數(shù)字的單元格個數(shù)。步驟2：AI對公式解釋二、使用AI進(jìn)行條件格式標(biāo)識單擊選項卡右側(cè)的WPSAI按鈕，彈出下拉菜單，選擇“AI條件格式”命令，彈出“AI條件格式”面板，在文本框中輸入“將J列大于或等于10的單元格標(biāo)識為紅色底紋”，單擊“發(fā)送”按鈕，AI條件格式生成完畢后，單擊“完成”按鈕即可。項目總結(jié)

在本項目中，我們主要完成了“員工工資表”和“員工出勤情況統(tǒng)計表”的制作。①在完