項目二制作學(xué)生成績表—WPS表格公式及函數(shù)運算任務(wù)三

任務(wù)一計算總分及各科平均分、最高峰、最低分任務(wù)二

評定獎勵等級計算總分排名實戰(zhàn)與小結(jié)任務(wù)四

使用VOOKUP函數(shù)制作學(xué)生成績查詢表項目三?制作學(xué)生成績表—WPS表格公式及函數(shù)運算任務(wù)一

計算總分及各科平均分、最高分、最低分能夠使用SUM、AVERAGE、MAX及MIN函數(shù)進行計算。一、認(rèn)識常用函數(shù)及其語法格式函數(shù)實際上是一個預(yù)先定義的特定計算公式。使用函數(shù)不僅可以完成復(fù)雜的計算，還可以簡化公式的繁雜程度。函數(shù)作為公式的一種特殊形式，也是以“=”開始，如“=SUM(C3:G3)”，其含義是對C3、D3、E3、F3、G3單元格數(shù)值進行求和。1.SUM函數(shù)功能：返回所有數(shù)值之和。語法：SUM(數(shù)值1,數(shù)值2,…)。其中“數(shù)值1，數(shù)值2，…”為需要求和的1到255個數(shù)值。2.AVERAGE函數(shù)功能：返回所有數(shù)值的平均值（算術(shù)平均值）。語法：AVERAGE(數(shù)值1,數(shù)值2,…)。其中“數(shù)值1，數(shù)值2，…”為需要計算平均值的1到255個數(shù)值。3.MAX函數(shù)功能：返回數(shù)值列表中的最大值，忽略文本值和邏輯值。語法：MAX(數(shù)值1,數(shù)值2,…)。其中“數(shù)值1，數(shù)值2，…”為需要計算最大值的1到255個數(shù)值。4.MIN函數(shù)功能：返回參數(shù)列表中的最小值，忽略文本值和邏輯值。語法：MIN(數(shù)值1,數(shù)值2,…)。其中“數(shù)值1，數(shù)值2，…”為需要計算最小值的1到255個數(shù)值。以上函數(shù)中，均可像前例一樣，用“：”標(biāo)示數(shù)據(jù)范圍。

二、計算總分1.插入SUM函數(shù)打開素材文件“學(xué)生成績表.xlsx”中的“學(xué)生成績表”工作表，選定H3單元格，單擊“公式”選項卡下的“自動求和”下拉按鈕，在下拉列表中，單擊“求和”命令，如下圖a所示；參數(shù)部分直接輸入或拖動鼠標(biāo)選中C3:G3單元格區(qū)域，按下“Enter”鍵，完成學(xué)生“張麗”總分的計算，如下圖b所示。a）選項位置b）輸入公式效果插入SUM函數(shù)2.使用填充柄復(fù)制公式選定H3單元格，將光標(biāo)移動至H3單元格的右下角，當(dāng)鼠標(biāo)指針變成如下圖a所示。黑色粗十字形標(biāo)志“+”時，按住鼠標(biāo)左鍵向下拖動至H22單元格，完成總分列的計算，如下圖b所示。a）鼠標(biāo)指針變?yōu)槭中蝏）填充效果使用填充柄復(fù)制公式數(shù)值的引用1.在計算總分時，除了可使用SUM函數(shù)計算外，還可以使用手動錄入公式的方法計算。使用公式進行計算時，先在編輯欄錄入“=”，再輸入?yún)?shù)和運算符，按下“Enter”鍵。例如，本任務(wù)中計算總分，選定H3單元格，在編輯欄中錄入“=C3+D3+E3+F3+G3”，按下“Enter”鍵，即可計算出“張麗”的總分。2.在實際工作中，很少直接輸入單元格的數(shù)值進行數(shù)據(jù)計算，而是通過引用數(shù)值所在的單元格或單元格區(qū)域進行數(shù)據(jù)計算。通過單擊選定或拖動選定直接引用數(shù)值所在的單元格或單元格區(qū)域，既方便又可提高數(shù)據(jù)的準(zhǔn)確性。

3.使用填充柄復(fù)制公式的過程，是使用了相對引用，即公式所在單元格的位置改變，則引用也會隨之改變。

三、計算平均分1.插入AVERAGE函數(shù)選定C23單元格，單擊“公式”選項卡下的“自動求和”下拉按鈕，在下拉列表中，單擊“平均值”命令；參數(shù)部分直接輸入或拖動鼠標(biāo)選定C3:C22單元格區(qū)域，按下“Enter”鍵，完成“思政”平均分的計算，如右圖所示。a）選項位置b）完成效果插入AVERAGE函數(shù)

2.使用填充柄復(fù)制公式選定C23單元格，使用填充柄向右填充至H23單元格，完成語文、數(shù)學(xué)、英語、計算機及總分平均分的計算，如下圖所示。使用填充柄計算平均分

四、計算最高分、最低分使用MAX函數(shù)在C24單元格中計算出“思政”的最高分，其公式為“=MAX(C3:C22)”，如下圖a所示；使用MIN函數(shù)在C25單元格中計算出“思政”的最低分，其公式為“=MIN(C3:C22)”，如下圖b所示；然后使用填充柄向右復(fù)制公式分別計算語文、數(shù)學(xué)、英語、計算機及總分的最高分和最低分。a）計算最高分b）計算最低分使用MAX函數(shù)、MIN函數(shù)計算最高分、最低分任務(wù)二評定獎勵等級能夠使用IF函數(shù)根據(jù)條件進行計算。

一、認(rèn)識IF函數(shù)的功能和語法格式功能：判斷一個條件是否滿足；如果滿足返回一個值，如果不滿足則返回另外一個值。語法：IF(測試條件，真值，假值)。其中，測試條件是作為判斷條件的表達式，表達式成立結(jié)果為真，不成立則結(jié)果為假；真值表示測試條件為真時返回的值；假值表示測試條件為假時返回的值。本任務(wù)中，獎勵等級檔次劃分見下表。獎勵等級檔次劃分表上一任務(wù)完成的學(xué)生成績表中，評定“張麗”的獎勵等級，其公式為“=IF(H3>=450,"一等",IF(H3>=425,"二等",IF(H3>=400,"三等","無")))”。二、插入IF函數(shù)選定J3單元格，單擊“公式”選項卡下的“插入函數(shù)”按鈕，如下圖a所示；彈出“插入函數(shù)”對話框，單擊“選擇函數(shù)”列表框中的“IF”函數(shù)；單擊“確定”按鈕，如下圖b所示。a）按鈕位置b）“插入函數(shù)”對話框插入IF函數(shù)

三、設(shè)定參數(shù)并嵌套兩條IF函數(shù)1.輸入?yún)?shù)在彈出的“函數(shù)參數(shù)”對話框，設(shè)置“測試條件”為“H3>=450”，“真值”為“一等”，然后單擊“假值”文本框后，單擊“名稱框”列表中的“IF”函數(shù)，如下圖a所示。2.嵌套第二條IF函數(shù)單擊“名稱框”列表的中“IF”函數(shù)后，彈出“函數(shù)參數(shù)”對話框，設(shè)置“測試條件”為“H3>=425”，“真值”為“二等”，單擊“假值”文本框后，單擊名稱框中“IF”函數(shù)，如下圖b所示。3.嵌套第三條IF函數(shù)在彈出“函數(shù)參數(shù)”的對話框中，設(shè)置“測試條件”為“H3>=400”，“真值”為“三等”，“假值”為“無”；單擊“確定”按鈕，計算出“張麗”的獎勵等級，如下圖c所示。函數(shù)使用熟練后，也可在公式編輯欄或單元格中直接輸入函數(shù)表達式。4.使用填充柄復(fù)制公式選定J3單元格，使用填充柄向下填充至J22單元格，完成每位學(xué)生獎勵等級的計算，如下圖d所示。a）輸入?yún)?shù)b）嵌套第二條IF函數(shù)c）嵌套第三條IF函數(shù)d）使用填充柄復(fù)制公式設(shè)定參數(shù)并嵌套兩條IF函數(shù)函數(shù)嵌套函數(shù)嵌套是指函數(shù)內(nèi)嵌套另外一個函數(shù)或一個函數(shù)成為另外一個函數(shù)的參數(shù)。在實際應(yīng)用中，如果一條IF函數(shù)無法滿足計算需求，這時可以使用多條IF函數(shù)進行嵌套。如本任務(wù)中，使用了如下的公式：“=IF(H3>=450,"一",IF(H3>=425,"二等",IF(H3>=400,"三等","無")))”，上述IF函數(shù)語句的語法解釋為：如果H3>=450成立，則執(zhí)行第二個參數(shù)（返回結(jié)果“一等”）；如果不成立，則執(zhí)行第三個參數(shù)，在這里第三個參數(shù)為第二個IF函數(shù)，繼續(xù)判定單元格H3的值是否大于425，成立則返回“二等”，否則繼續(xù)執(zhí)行第三個IF函數(shù)，最后完成四個獎勵等級的判斷。IF函數(shù)最多可嵌套七層，在嵌套下一條函數(shù)時，要確保插入點光標(biāo)在正確位置，否則會出現(xiàn)語法錯誤。任務(wù)三計算總分排名能夠使用RANK函數(shù)進行計算。

一、認(rèn)識RANK函數(shù)的功能和語法格式功能：返回某數(shù)字在一列數(shù)字中相對于其他數(shù)值的大小排名。語法：RANK（數(shù)值，引用，排位方式），其中“數(shù)值”為需要找到排位的指定數(shù)字；“引用”為一組數(shù)或?qū)σ粋€數(shù)據(jù)列表的引用，非數(shù)字值將被忽略；“排位方式”為指定排位的方式，如為0或空，為降序；如為非零值，為升序。本任務(wù)中，根據(jù)學(xué)生的總分以降序方式進行排名。其中計算“張麗”名次的公式為“=RANK(H3,$H$3:$H$22,0)”，其中“$H$3:$H$22”使用了絕對引用。“$”表示在使用填充柄復(fù)制公式時，不改變其后的行號或列號。

二、插入RANK函數(shù)選定I3單元格，單擊“公式”選項卡下的“插入函數(shù)”按鈕，如下圖a所示；彈出“插入函數(shù)”對話框，在“查找函數(shù)”文本框中輸入“rank”，選擇“選擇函數(shù)”列表框中的“RANK”函數(shù)；單擊“確定”按鈕，如下圖b所示。a）按鈕位置b）“插入函數(shù)”對話框插入RANK函數(shù)三、設(shè)定參數(shù)并進行計算1.設(shè)定參數(shù)在彈出的“函數(shù)參數(shù)”對話框中，設(shè)置“數(shù)值”為“H3”，“引用”為“$H$3:$H$22”（選定單元格范圍后按“F4”鍵可快速添加絕對引用符“$”，反復(fù)按“F4”鍵還可切換不同添加形式），“排位方式”為“0”；單擊“確定”按鈕，如下圖a所示。2.使用填充柄復(fù)制公式選定I3單元格，使用填充柄向下填充至I22單元格，完成每位學(xué)生排名的計算，如下圖b所示。a）“函數(shù)參數(shù)”對話框b）完成效果設(shè)定RANK函數(shù)參數(shù)引用的類型和相同數(shù)值的排序規(guī)則絕對引用是指在計算過程中，公式所在單元格位置改變，但公式中引用的單元格保持不變。使用絕對引用時，被引用的單元格需添加絕對引用符“$”，如$A$1。按“F4”鍵可快速添加絕對引用符。在本任務(wù)中，“$H$3:$H$22”添加絕對引用符，是為確保使用填充柄向下復(fù)制時，被引用的H3:H22單元格區(qū)域保持不變。使用函數(shù)RANK排名時，經(jīng)常會出現(xiàn)相同數(shù)值，相同的數(shù)值排位相同，但會影響后一位的排位。如本任務(wù)中，出現(xiàn)了兩個相同數(shù)值，其排位都是1，此時就不再有排位2的數(shù)值，后一位的排位是3。任務(wù)四使用VLOOKUP函數(shù)制作學(xué)生成績查詢表能夠使用VLOOKUP函數(shù)查找數(shù)據(jù)。一、認(rèn)識VLOOKUP函數(shù)的功能和語法格式功能：給定一個需要查找的目標(biāo)，再從指定的查找區(qū)域中查找到相應(yīng)的數(shù)據(jù)，并將此數(shù)據(jù)返回到指定位置。語法：VLOOKUP(查找值,數(shù)據(jù)表,列序數(shù),匹配條件)VLOOKUP函數(shù)參數(shù)說明見下表。VLOOKUP函數(shù)參數(shù)說明二、新建工作表在素材文件“學(xué)生成績表.xlsx”中，新建一個名為“成績查詢表”的工作表；在A1單元格中錄入內(nèi)容“學(xué)生成績查詢表”，設(shè)置其字體為“黑體”，字號為“12”，字形為“加粗”，合并居中A1:I1單元格區(qū)域；在A2:I2單元格區(qū)域中錄入成績查詢表的各項標(biāo)題，如下圖所示。學(xué)生成績查詢表

三、設(shè)置數(shù)據(jù)有效性在A3單元格中，使用數(shù)據(jù)有效性，引用“學(xué)生成績表”工作表中的B2:B22單元格區(qū)域，制作姓名下拉列表，如下圖所示。a）“數(shù)據(jù)有效性”對話框b）完成效果使用數(shù)據(jù)有效性制作姓名下拉列表四、使用VLOOKUP函數(shù)1.插入VLOOKUP函數(shù)選定B3單元格；單擊編輯欄中的“插入函數(shù)”按鈕，彈出“插入函數(shù)”對話框，在“查找函數(shù)”文本框中輸入“VLOOKUP”，選擇“選擇函數(shù)”列表框中的“VLOOKUP”函數(shù)；單擊“確定”按鈕。2.設(shè)定參數(shù)彈出“函數(shù)參數(shù)”對話框，設(shè)置“查找值”為“A3”，“數(shù)據(jù)表”為“學(xué)生成績表!$B$3:$J$22”，“列序數(shù)”為“2”，“匹配條件”為“FALSE”，單擊“確定”按鈕；完成后可查詢A3單元格顯示的同學(xué)的“思政”成績，如下圖所示。a）“函數(shù)參數(shù)”對話框b）完成效果選擇姓名查詢“思政”成績

3.查詢其他各項成績信息按照相同的方法，在“C3”“D3”“E3”“F3”“G3”“H3”“I3”單元格中分別使用VLOOKUP函數(shù)查詢各項成績信息，如下圖所示。選擇姓名查詢學(xué)生成績VLOOKUP函數(shù)使用技巧查找區(qū)域可以跨工作表引用，且可使用絕對引用，形式如“學(xué)生成績表!$B$3:$H$22”?！昂瘮?shù)參數(shù)”對話框中設(shè)定“數(shù)據(jù)表”項的查找區(qū)域時，查找值必須在此單元格區(qū)域中的第一列，且該區(qū)域中必須包含返回值。在VLOOKUP函數(shù)中可嵌套COLUMN函數(shù)獲取當(dāng)前列序號，提高效率，例如制作學(xué)生成績查詢表時，具體操作步驟如下：在B3單元格中插入VLOOKUP函數(shù)，彈出“函數(shù)參數(shù)”對話框，設(shè)置“查找值”為“$A3”，“數(shù)據(jù)表”為“學(xué)生成績表!$B$3:$J$22”，“列序數(shù)”為“COLUMN()”，“匹配條件”為“FALSE”，單擊“確定”按鈕；完成后，使用填充柄向右填充至I3單元格即可實現(xiàn)整行內(nèi)容的查詢，不必再逐一輸入函數(shù)，如右圖

所示。VLOOKUP+COLUMN函數(shù)的使用實戰(zhàn)與小結(jié)制作員工工資表某企業(yè)每月都需要制作員工工資表，制作完成后打印成工資條發(fā)放給每位員工。此工資表由6部分組成，其中，基本工資、代扣保險和其他扣款是固定的，工齡工資、績效獎金、崗位津貼則根據(jù)員工自身情況浮動。打開素材文件“員工工資表.xlsx”中的“員工工資表”工作表，參照以下思路完成制作。制作完成后的效果圖如下兩圖所示?！皢T工工資表”效果圖“員工工資條”效果圖思路如下：1.使用IF函數(shù)計算員工工齡工資。員工工齡低于5年（不含5年），工齡工資為每年50元；其余人員工齡工資為每年100元。2.使用IF函數(shù)計算員工績效獎金。員工績效評分大于或等于80分，績效獎金為1000元；績效評分在60分至80分（不含80分）之間，績效獎金為每分10元，小于60分則績效獎金為0元。3.使用VLOOKUP函數(shù)查詢員工崗位津貼。員工的崗位津貼是根據(jù)員工職務(wù)而定的，“崗位津貼標(biāo)準(zhǔn)”表中列舉了各職務(wù)的崗位津貼數(shù)據(jù)。4.使用SUM函數(shù)計算員工實發(fā)工資。5.使用VLOOKUP函數(shù)制作工資條。在“工資條”工作表的A2:M2