資源簡介

(共52張PPT)
任務 三　
任務 一
計算總分及各科平均分、最高峰、最低分
任務 二　
評定獎勵等級
計算總分排名
實戰與小結
任務 四　
使用VOOKUP函數制作學生成績查詢表
任務一
計算總分及各科平均分、最高分、最低分
能夠使用SUM、AVERAGE、MAX 及MIN 函數進行計算。
一、認識常用函數及其語法格式
函數實際上是一個預先定義的特定計算公式。使用函數不僅可以完成復雜的計算，還可以簡化公式的繁雜程度。函數作為公式的一種特殊形式，也是以“=”開始，如“=SUM(C3:G3)”，其含義是對C3、D3、E3、F3、G3 單元格數值進行求和。
1. SUM 函數
功能：返回所有數值之和。
語法：SUM( 數值1, 數值2,…)。其中“數值1，數值2，…”為需要求和的1 到255 個數值。
2. AVERAGE 函數
功能：返回所有數值的平均值（算術平均值）。
語法：AVERAGE( 數值1, 數值2,…)。其中“數值1，數值2，…”為需要計算平均值的1 到255 個數值。
3. MAX 函數
功能：返回數值列表中的最大值，忽略文本值和邏輯值。
語法：MAX( 數值1, 數值2,…)。其中“數值1，數值2，…”為需要計算最大值的1 到255 個數值。
4. MIN 函數
功能：返回參數列表中的最小值，忽略文本值和邏輯值。
語法：MIN( 數值1, 數值2,…)。其中“數值1，數值2，…”為需要計算最小值的1 到255 個數值。
以上函數中，均可像前例一樣，用“：”標示數據范圍。
二、計算總分
1. 插入SUM 函數
打開素材文件“學生成績表.xlsx”中的“學生成績表”工作表，選定H3 單元格，單擊“公式”選項卡下的“自動求和”下拉按鈕，在下拉列表中，單擊“求和”命令，如下圖a 所示；參數部分直接輸入或拖動鼠標選中C3:G3 單元格區域，按下“Enter”鍵，完成學生“張麗”總分的計算，如下圖b 所示。
a）選項位置 b）輸入公式效果
插入SUM 函數
2. 使用填充柄復制公式
選定H3 單元格，將光標移動至H3 單元格的右下角，當鼠標指針變成如下圖a所示。黑色粗十字形標志“+”時，按住鼠標左鍵向下拖動至H22 單元格，完成總分列的計算，如下圖b 所示。
a）鼠標指針變為十字形 b）填充效果
使用填充柄復制公式
數值的引用
1. 在計算總分時，除了可使用SUM 函數計算外，還可以使用手動錄入公式的方法計算。使用公式進行計算時，先在編輯欄錄入“=”，再輸入參數和運算符，按下“Enter”鍵。例如，本任務中計算總分，選定H3 單元格，在編輯欄中錄入“=C3+D3+E3+F3+G3”，按下“Enter”鍵，即可計算出“張麗”的總分。
2. 在實際工作中，很少直接輸入單元格的數值進行數據計算，而是通過引用數值所在的單元格或單元格區域進行數據計算。通過單擊選定或拖動選定直接引用數值所在的單元格或單元格區域，既方便又可提高數據的準確性。
3. 使用填充柄復制公式的過程，是使用了相對引用，即公式所在單元格的位置改變，則引用也會隨之改變。
三、計算平均分
1. 插入AVERAGE 函數
選定C23 單元格，單擊“公式”選項卡下的“自動求和”下拉按鈕，在下拉列表中，單擊“平均值”命令；參數部分直接輸入或拖動鼠標選定C3:C22 單元格區域，按下“Enter”鍵，完成“思政”平均分的計算，如右圖所示。
a）選項位置 b）完成效果
插入AVERAGE 函數
2. 使用填充柄復制公式
選定C23 單元格，使用填充柄向右填充至H23 單元格，完成語文、數學、英語、計算機及總分平均分的計算，如下圖所示。
使用填充柄計算平均分
四、計算最高分、最低分
使用MAX 函數在C24 單元格中計算出“思政”的最高分，其公式為“=MAX
(C3:C22)”，如下圖a 所示；使用MIN 函數在C25 單元格中計算出“思政”的最低分，其公式為“=MIN(C3:C22)”，如下圖b 所示；然后使用填充柄向右復制公式分別計算語文、數學、英語、計算機及總分的最高分和最低分。
a）計算最高分 b）計算最低分
使用MAX 函數、MIN 函數計算最高分、最低分
任務二
評定獎勵等級
能夠使用IF 函數根據條件進行計算。
一、認識IF 函數的功能和語法格式
功能：判斷一個條件是否滿足；如果滿足返回一個值，如果不滿足則返回另外一個值。
語法：IF( 測試條件，真值，假值)。其中，測試條件是作為判斷條件的表達式，表達式成立結果為真，不成立則結果為假；真值表示測試條件為真時返回的值；假值表示測試條件為假時返回的值。
本任務中，獎勵等級檔次劃分見下表。
獎勵等級檔次劃分表
上一任務完成的學生成績表中， 評定“張麗” 的獎勵等級， 其公式為“=IF(H3>=450," 一等",IF(H3>=425," 二等",IF(H3>=400," 三等"," 無")))”。
二、插入IF 函數
選定J3 單元格，單擊“公式”選項卡下的“插入函數”按鈕，如下圖a 所示；彈出“插入函數”對話框，單擊“選擇函數”列表框中的“IF”函數；單擊“確定”按鈕，如下圖b 所示。
a）按鈕位置 b）“插入函數”對話框
插入IF 函數
三、設定參數并嵌套兩條IF 函數
1. 輸入參數
在彈出的“函數參數”對話框，設置“測試條件”為“H3>=450”，“真值”為“一等”，然后單擊“假值”文本框后，單擊“名稱框”列表中的“IF”函數，如下圖a 所示。
2. 嵌套第二條IF 函數
單擊“名稱框”列表的中“IF”函數后，彈出“函數參數”對話框，設置“測試條件”為“H3>=425”，“真值”為“二等”，單擊“假值”文本框后，單擊名稱框中“IF”函數，如下圖b 所示。
3. 嵌套第三條IF 函數
在彈出“函數參數” 的對話框中， 設置“測試條件” 為“H3>=400”，“真值”
為“三等”，“假值”為“無”；單擊“確定”按鈕，計算出“張麗”的獎勵等級，如下圖c 所示。
函數使用熟練后，也可在公式編輯欄或單元格中直接輸入函數表達式。
4. 使用填充柄復制公式
選定J3 單元格，使用填充柄向下填充至J22 單元格，完成每位學生獎勵等級的計
算，如下圖d 所示。
a）輸入參數 b）嵌套第二條IF 函數
c）嵌套第三條IF 函數 d）使用填充柄復制公式
設定參數并嵌套兩條IF 函數
函數嵌套
函數嵌套是指函數內嵌套另外一個函數或一個函數成為另外一個函數的參數。在實際應用中，如果一條IF 函數無法滿足計算需求，這時可以使用多條IF 函數進行嵌套。
如本任務中， 使用了如下的公式：“=IF(H3>=450," 一",IF(H3>=425," 二等",IF(H3>=400," 三等"," 無")))”， 上述IF 函數語句的語法解釋為： 如果H3>=450 成立，則執行第二個參數（返回結果“一等”）；如果不成立，則執行第三個參數，在這里第三個參數為第二個IF 函數，繼續判定單元格H3 的值是否大于425，成立則返回“二等”，否則繼續執行第三個IF 函數，最后完成四個獎勵等級的判斷。
IF 函數最多可嵌套七層，在嵌套下一條函數時，要確保插入點光標在正確位置，否則會出現語法錯誤。
任務三
計算總分排名
能夠使用RANK 函數進行計算。
一、認識RANK 函數的功能和語法格式
功能：返回某數字在一列數字中相對于其他數值的大小排名。
語法：RANK（數值，引用，排位方式），其中“數值”為需要找到排位的指定數字；“引用”為一組數或對一個數據列表的引用，非數字值將被忽略；“排位方式”為指定排位的方式，如為0 或空，為降序；如為非零值，為升序。
本任務中，根據學生的總分以降序方式進行排名。其中計算“張麗”名次的公式為“=RANK(H3,$H$3:$H$22,0)”，其中“$H$3:$H$22”使用了絕對引用。“$”表示在使用填充柄復制公式時，不改變其后的行號或列號。
二、插入RANK 函數
選定I3 單元格，單擊“公式”選項卡下的“插入函數”按鈕，如下圖a 所示；彈出“插入函數”對話框，在“查找函數”文本框中輸入“rank”，選擇“選擇函數”列表框中的“RANK”函數；單擊“確定”按鈕，如下圖b 所示。
a）按鈕位置 b）“插入函數”對話框
插入RANK 函數
三、設定參數并進行計算
1. 設定參數
在彈出的“函數參數”對話框中，設置“數值”為“H3”，“引用”為“$H$3:
$H$22”（選定單元格范圍后按“F4”鍵可快速添加絕對引用符“$”，反復按“F4”鍵還可切換不同添加形式），“排位方式”為“0”；單擊“確定”按鈕，如下圖a所示。
2. 使用填充柄復制公式
選定I3 單元格，使用填充柄向下填充至I22 單元格，完成每位學生排名的計算，如下圖b 所示。
a）“函數參數”對話框 b）完成效果
設定RANK 函數參數
引用的類型和相同數值的排序規則
絕對引用是指在計算過程中，公式所在單元格位置改變，但公式中引用的單元格保持不變。使用絕對引用時，被引用的單元格需添加絕對引用符“$”，如$A$1。按“F4”鍵可快速添加絕對引用符。在本任務中，“$H$3:$H$22”添加絕對引用符，是為確保使用填充柄向下復制時，被引用的H3:H22 單元格區域保持不變。
使用函數RANK 排名時，經常會出現相同數值，相同的數值排位相同，但會影響后一位的排位。如本任務中，出現了兩個相同數值，其排位都是1，此時就不再有排位2 的數值，后一位的排位是3。
任務四
使用VLOOKUP函數
制作學生成績查詢表
能夠使用VLOOKUP 函數查找數據。
一、認識VLOOKUP 函數的功能和語法格式
功能：給定一個需要查找的目標，再從指定的查找區域中查找到相應的數據，并將此數據返回到指定位置。
語法：VLOOKUP( 查找值, 數據表, 列序數, 匹配條件)VLOOKUP 函數參數說明見下表。
VLOOKUP 函數參數說明
二、新建工作表
在素材文件“學生成績表.xlsx”中，新建一個名為“成績查詢表”的工作表；在A1 單元格中錄入內容“學生成績查詢表”，設置其字體為“黑體”，字號為“12”，字形為“加粗”，合并居中A1:I1 單元格區域；在A2:I2 單元格區域中錄入成績查詢表的各項標題，如下圖所示。
學生成績查詢表
三、設置數據有效性
在A3 單元格中，使用數據有效性，引用“學生成績表”工作表中的B2:B22 單元格區域，制作姓名下拉列表，如下圖所示。
a）“數據有效性”對話框 b）完成效果
使用數據有效性制作姓名下拉列表
四、使用VLOOKUP 函數
1. 插入VLOOKUP 函數
選定B3 單元格；單擊編輯欄中的“插入函數”按鈕，彈出“插入函數”對話框， 在“查找函數” 文本框中輸入“VLOOKUP”， 選擇“選擇函數” 列表框中的“VLOOKUP”函數；單擊“確定”按鈕。
2. 設定參數
彈出“函數參數”對話框，設置“查找值”為“A3”，“數據表”為“學生成績表!$B$3:$J$22”，“列序數”為“2”，“匹配條件”為“FALSE”，單擊“確定”按鈕；完成后可查詢A3 單元格顯示的同學的“思政”成績，如下圖所示。
a）“函數參數”對話框 b）完成效果
選擇姓名查詢“思政”成績
3. 查詢其他各項成績信息
按照相同的方法，在“C3”“D3”“E3”“F3”“G3”“H3”“I3”單元格中分別使用VLOOKUP 函數查詢各項成績信息，如下圖所示。
選擇姓名查詢學生成績
VLOOKUP 函數使用技巧
查找區域可以跨工作表引用，且可使用絕對引用，形式如“學生成績表!$B$3:$H$22”。
“函數參數”對話框中設定“數據表”項的查找區域時，查找值必須在此單元格區域中的第一列，且該區域中必須包含返回值。
在VLOOKUP 函數中可嵌套COLUMN 函數獲取當前列序號，提高效率，例如制作學生成績查詢表時，具體操作步驟如下：
在B3 單元格中插入VLOOKUP 函數，彈出“函數參數”對話框，設置“查找值”為“$A3”，“數據表” 為“學生成績表!$B$3:$J$22”，“列序數” 為“COLUMN()”，“匹配條件”為“FALSE”，單擊“確定”按鈕；完成后，使用填充柄向右填充至I3 單元格即可實現整行內容的查詢，不必再逐一輸入函數，如右圖 所示。
VLOOKUP+COLUMN 函數的使用
實戰與小結
制作員工工資表
某企業每月都需要制作員工工資表，制作完成后打印成工資條發放給每位員工。此工資表由6 部分組成，其中，基本工資、代扣保險和其他扣款是固定的，工齡工資、績效獎金、崗位津貼則根據員工自身情況浮動。打開素材文件“員工工資表.xlsx”中的“員工工資表”工作表，參照以下思路完成制作。制作完成后的效果圖如下兩圖所示。
“員工工資表”效果圖
“員工工資條”效果圖
思路如下：
1. 使用IF 函數計算員工工齡工資。員工工齡低于5 年（不含5 年），工齡工資為每年50 元；其余人員工齡工資為每年100 元。
2. 使用IF 函數計算員工績效獎金。員工績效評分大于或等于80 分，績效獎金為1 000 元；績效評分在60 分至80 分（不含80 分）之間，績效獎金為每分10 元，小于60 分則績效獎金為0 元。
3. 使用VLOOKUP 函數查詢員工崗位津貼。員工的崗位津貼是根據員工職務而定的，“崗位津貼標準”表中列舉了各職務的崗位津貼數據。
4. 使用SUM 函數計算員工實發工資。
5. 使用VLOOKUP 函數制作工資條。在“工資條”工作表的A2:M2 單元格區域中錄入工資條的各項標題；在A3 單元格中錄入員工工號“S-001”；在“B3”單元格中使用VLOOKUP 函數和COLUMN 函數，查詢出員工的姓名數據，使用填充柄復制公式至B3:M3 單元格區域；第一名員工工資條制作完成后選定A1:M4 單元格區域，使用填充柄向下填充至第60 行。
6. 保存文件。
制作員工培訓成績表
結合所學公式和函數的相關知識，完成“員工培訓成績表”的制作。制作完成后的效果圖如下圖所示。
“員工培訓成績表”效果圖
思路如下：
1. 打開素材文件“員工培訓成績表.xlsx”。
2. 使用SUM 函數計算總分列數據。
3. 使用AVERAGE 函數計算平均分列數據。
4. 使用RANK 函數對總分以降序方式計算排名。
5. 使用IF 函數評定獎勵等級。其中，總分≥360 分，獎勵等級為一等；360＞總分≥330 分，獎勵等級為二等；330＞總分≥300 分，獎勵等級為三等；其余為無。
6. 使用VLOOKUP 函數根據獎勵等級計算獎勵金額。
7. 保存表格。
本項目主要介紹了SUM、AVERAGE、MAX、MIN、IF、RANK、VLOOKUP 函數的使用方法，其知識內容如下。
1. 單元格的引用
單元格的引用是指在公式中使用單元格的地址來代替單元格中的數據，見下表。
引用定義表
2. 常用函數公式
函數是WPS 表格預先定義好的內置公式，由標識符、函數名、參數組成，并按特定的順序或結構進行計算。函數的參數與參數之間用半角逗號分隔，使用函數運算后得出的結果稱為返回值。WPS 表格常用的函數見下表。
常用函數表

展開更多......

收起↑