（中職）計(jì)算機(jī)應(yīng)用基礎(chǔ)單元4Excel基本應(yīng)用教學(xué)課件（工信版）本單元內(nèi)容0203制作單科成績(jī)表04統(tǒng)計(jì)成績(jī)總表05分析成績(jī)統(tǒng)計(jì)表01制作學(xué)籍表管理成績(jī)總表

制作學(xué)籍表1制作學(xué)籍表啟動(dòng)和退出Excel，一、啟動(dòng)Excel二、退出

Excel1.“文件”選項(xiàng)卡，選擇“關(guān)閉”；2.按“Alt+F4”組合鍵；3.按標(biāo)題欄右側(cè)“關(guān)閉”按鈕；4.右擊任務(wù)欄上的Excel圖標(biāo)，在彈出的快捷菜單中選擇“關(guān)閉窗口”；

制作學(xué)籍表Excel工作界面制作學(xué)籍表工作簿的使用（1）新建工作簿（2）保存工作簿（3）打開(kāi)工作簿（4）關(guān)閉工作簿。（5）保護(hù)具有重要數(shù)據(jù)的工作簿

制作學(xué)籍表工作表的使用（1）工作表的重命名（2）工作表的切換制作學(xué)籍表單元格數(shù)據(jù)的輸入（1）文本的輸入（2）數(shù)值的輸入（3）日期和時(shí)間的輸入（4）公式和批注的輸入（5）自動(dòng)填充功能（6）快速填充功能（7）限定數(shù)據(jù)輸入（8）利用記錄單輸入數(shù)據(jù)制作學(xué)籍表單元格基本操作（1）選擇單元格（2）移動(dòng)單元格（3）復(fù)制單元格（4）插入與刪除單元格（5）清除單元格（6）調(diào)整單元格行高列寬制作學(xué)籍表美化工作表（1）數(shù)字格式化（2）文字格式化（3）設(shè)置文本的對(duì)齊方式（4）設(shè)置單元格邊框（5）設(shè)置單元格底紋制作學(xué)籍表——項(xiàng)目實(shí)現(xiàn)項(xiàng)目效果展示制作學(xué)籍表——項(xiàng)目實(shí)現(xiàn)創(chuàng)建“學(xué)籍表”工作表任務(wù)1.打開(kāi)Excel，保存文件為“學(xué)生管理.xlsx”，并將表名改為“學(xué)籍表”。

任務(wù)3.在“學(xué)籍表”工作表中利用記錄單輸入數(shù)據(jù)。

任務(wù)2.在空白“學(xué)籍表”工作表中輸入各種不同類型的數(shù)據(jù)。

制作學(xué)籍表——項(xiàng)目實(shí)現(xiàn)美化“學(xué)籍表”工作表任務(wù)1.對(duì)“學(xué)籍表”進(jìn)行格式化和美化，要求：標(biāo)題“華文中宋”18磅，表頭“黑體”14磅和淡黃色底紋，表內(nèi)“仿宋”14磅，藍(lán)色外邊框中實(shí)線，黑色內(nèi)部細(xì)實(shí)線，背景圖片。

制作單科成績(jī)表2制作單科成績(jī)表工作表的引用15

如果是當(dāng)前工作簿或工作表，引用時(shí)可以省略工作簿或工作表的名稱。如果是其他的工作簿或工作表，引用時(shí)需要在工作簿或工作表的名稱后面加上“！”。

譬如，當(dāng)前工作表是Sheet1，想引用Sheet2工作表的A3單元格，則可以寫(xiě)成Sheet2！A3。制作單科成績(jī)表?xiàng)l件格式16

所謂條件格式就是在工作表中設(shè)置帶有條件的格式。當(dāng)條件滿足時(shí)，單元格將應(yīng)用所設(shè)置的格式。

選中需要設(shè)置條件格式的單元格區(qū)域，單擊“開(kāi)始”選項(xiàng)卡，在“樣式”組中單擊“條件格式”按鈕，彈出“條件格式”面板，在列表中選擇需要設(shè)置的選項(xiàng)進(jìn)行條件的設(shè)置即可。

制作單科成績(jī)表公式和函數(shù)17

（1）公式的使用

運(yùn)算符、單元格的引用（2）函數(shù)的使用

函數(shù)的格式、函數(shù)的分類、函數(shù)的引用（3）通配符的使用（4）常用函數(shù)SUM()函數(shù)、AVERAGE()函數(shù)、MOD()函數(shù)、ROW()函數(shù)。制作單科成績(jī)表——項(xiàng)目實(shí)現(xiàn)項(xiàng)目效果展示制作單科成績(jī)表——項(xiàng)目實(shí)現(xiàn)創(chuàng)建單科成績(jī)表任務(wù)1.打開(kāi)“學(xué)生管理.xlsx”工作簿，創(chuàng)建“英語(yǔ)成績(jī)表”工作表，并輸入數(shù)據(jù)。制作單科成績(jī)表——項(xiàng)目實(shí)現(xiàn)計(jì)算成績(jī)表任務(wù)1.在“英語(yǔ)成績(jī)表”中，計(jì)算所有學(xué)生的總評(píng)成績(jī)，這里：總評(píng)成績(jī)=平時(shí)成績(jī)×40%+期末成績(jī)×60%。

任務(wù)2.在“英語(yǔ)成績(jī)表”中，利用函數(shù)AVERAGE()計(jì)算“大學(xué)英語(yǔ)”這門(mén)課程的平均成績(jī)，并保留小數(shù)點(diǎn)后兩位。

制作單科成績(jī)表——項(xiàng)目實(shí)現(xiàn)格式化成績(jī)表任務(wù)1.合并標(biāo)題并居中，并設(shè)置標(biāo)題“幼圓，加粗，18磅，黑色”，設(shè)置表頭“中宋，14磅，藍(lán)色，水平居中”，設(shè)置數(shù)據(jù)區(qū)數(shù)據(jù)“楷體，12磅，水平垂直居中”。

任務(wù)2.將成績(jī)表的外邊框設(shè)置為“雙細(xì)線，黑色”，內(nèi)邊框設(shè)置為“單細(xì)線，藍(lán)色”。

任務(wù)3.將成績(jī)表的表頭區(qū)域套用單元格樣式，將其“行高”設(shè)置為30。

制作單科成績(jī)表——項(xiàng)目實(shí)現(xiàn)格式化成績(jī)表任務(wù)4.利用條件格式和MOD()函數(shù)、ROW()函數(shù)將成績(jī)表的奇數(shù)行填充為淺綠色。

任務(wù)5.將成績(jī)表表頭中的“平時(shí)成績(jī)、期末成績(jī)、總評(píng)成績(jī)”，在單元格中分兩行顯示，并將所有列的列寬，調(diào)整為最適合的寬度。

統(tǒng)計(jì)成績(jī)總表3統(tǒng)計(jì)成績(jī)總表工作表的移動(dòng)24

移動(dòng)操作可以調(diào)整當(dāng)前的工作表排放次序。（1）在同一個(gè)工作簿中移動(dòng)工作表。（2）在不同工作簿中移動(dòng)工作表。

統(tǒng)計(jì)成績(jī)總表工作表的復(fù)制25

復(fù)制操作可以將一張工作表中的內(nèi)容復(fù)制到另一張工作表中，避免了對(duì)相同內(nèi)容的重復(fù)輸入，從而提高了工作效率。（1）在同一工作簿中復(fù)制工作表。（2）將工作表復(fù)制到其他工作簿中。統(tǒng)計(jì)成績(jī)總表插入與刪除工作表26

在實(shí)際工作中往往需要建立多張工作表。利用“插入工作表”命令即可在當(dāng)前工作表之前插入一張新的工作表。（1）刪除單張工作表。（2）同時(shí)刪除多張工作表。統(tǒng)計(jì)成績(jī)總表保護(hù)工作表27

為了防止他人對(duì)工作表進(jìn)行編輯，最好的辦法就是設(shè)置工作表密碼。統(tǒng)計(jì)成績(jī)總表常用函數(shù)28（1）MAX()函數(shù)（2）MIN()函數(shù)（3）RANK.EQ()函數(shù)（4）COUNT()函數(shù)（5）COUNTA()函數(shù)（6）IF()函數(shù)統(tǒng)計(jì)成績(jī)總表數(shù)據(jù)排序29

排序是將數(shù)據(jù)列表中的記錄按照某個(gè)字段名的數(shù)據(jù)值或條件從小到大或從大到小地進(jìn)行排列。用來(lái)排序的字段名或條件稱為排序關(guān)鍵字。（1）單個(gè)關(guān)鍵字排序（2）多關(guān)鍵字排序統(tǒng)計(jì)成績(jī)總表數(shù)據(jù)篩選30

篩選是查找和處理單元格區(qū)域中數(shù)據(jù)子集的快捷方法。（1）自動(dòng)篩選（2）自定義篩選（3）高級(jí)篩選（4）快速篩選統(tǒng)計(jì)成績(jī)總表——項(xiàng)目實(shí)現(xiàn)項(xiàng)目效果展示統(tǒng)計(jì)成績(jī)總表——項(xiàng)目實(shí)現(xiàn)由多工作表生成成績(jī)總表任務(wù)1.利用工作表移動(dòng)和復(fù)制操作分別將給定的“高等數(shù)學(xué)”、“基礎(chǔ)會(huì)計(jì)”、“計(jì)算機(jī)基礎(chǔ)”3門(mén)單科成績(jī)表復(fù)制到“學(xué)生管理”工作簿中。

任務(wù)2.利用復(fù)制操作將給定的“英語(yǔ)成績(jī)表”、“數(shù)學(xué)成績(jī)表”、“會(huì)計(jì)成績(jī)表”、“計(jì)算機(jī)成績(jī)表”4門(mén)單科成績(jī)表生成成績(jī)總表。

任務(wù)3.在“學(xué)生管理”工作簿中，將“數(shù)學(xué)成績(jī)表”工作表移至“英語(yǔ)成績(jī)表”工作表之前，在“成績(jī)總表”工作表中，將“高等數(shù)學(xué)”列移至“大學(xué)英語(yǔ)”列之前。統(tǒng)計(jì)成績(jī)總表——項(xiàng)目實(shí)現(xiàn)成績(jī)總表的統(tǒng)計(jì)計(jì)算任務(wù)1.在“成績(jī)總表”中增加“總分”列和“平均分”列，計(jì)算每位學(xué)生的總分和平均分。任務(wù)2.在“成績(jī)總表”中增加“名次”列，計(jì)算每位學(xué)生的總分排名，并修改每位學(xué)生的總分排名。

任務(wù)3.在“成績(jī)總表”中，計(jì)算各門(mén)課程的平均分，結(jié)果四舍五入保留2為小數(shù)。任務(wù)4.在“成績(jī)總表”中，計(jì)算各門(mén)課程的最高分和最低分。統(tǒng)計(jì)成績(jī)總表——項(xiàng)目實(shí)現(xiàn)評(píng)定獎(jiǎng)學(xué)金等級(jí)任務(wù)1.在“成績(jī)總表”中，根據(jù)名次評(píng)出上學(xué)期獎(jiǎng)學(xué)金獲等級(jí)。評(píng)選比例：一等獎(jiǎng)5%，二等獎(jiǎng)10%,三等獎(jiǎng)15%。統(tǒng)計(jì)成績(jī)總表——項(xiàng)目實(shí)現(xiàn)美化成績(jī)總表任務(wù)1.套用表格格式，美化“成績(jī)總表”工作表。統(tǒng)計(jì)成績(jī)總表——項(xiàng)目實(shí)現(xiàn)排序單科成績(jī)表任務(wù)1.在“英語(yǔ)成績(jī)表”工作表中，按“總評(píng)成績(jī)”的降序排列。

任務(wù)3.在“會(huì)計(jì)成績(jī)表”工作表中，以“總評(píng)成績(jī)”為關(guān)鍵字降序排列，并利用套用表格的匯總行計(jì)算基礎(chǔ)會(huì)計(jì)平均分。任務(wù)2.在“數(shù)學(xué)成績(jī)表”工作表中，以“性別”為主要關(guān)鍵字升序排列，以“總評(píng)成績(jī)”為第2關(guān)鍵字降序排列，以姓名為第3關(guān)鍵字升序排列，并套用表格樣式美化該工作表。統(tǒng)計(jì)成績(jī)總表——項(xiàng)目實(shí)現(xiàn)篩選成績(jī)總表任務(wù)1.將“成績(jī)總表”工作表復(fù)制一份，并將復(fù)制的工作表改名為“自動(dòng)篩選”。在“自動(dòng)篩選”工作表中篩選滿足以下條件的數(shù)據(jù)記錄：姓“劉”或姓名中最后一個(gè)字為“華”，其“計(jì)算機(jī)基礎(chǔ)”的成績(jī)?cè)?0分（含90）以上，“名次”在前5的“女”同學(xué)。

任務(wù)2.將“成績(jī)總表”工作表復(fù)制一份，并將復(fù)制的工作表改名為“高級(jí)篩選”。在“高級(jí)篩選”工作表中篩選總分小于350分的男生或總分大于等于375的女生。任務(wù)3.將“學(xué)生管理”工作簿復(fù)制一份，并將復(fù)制的工作簿改名為“學(xué)生管理（主題）”，將“學(xué)生管理（主題）”工作簿中所有工作表使用“主題”來(lái)統(tǒng)一風(fēng)格。

分析成績(jī)統(tǒng)計(jì)表4分析成績(jī)統(tǒng)計(jì)表統(tǒng)計(jì)函數(shù)39（1）COUNTIF()函數(shù)（2）COUNTIFS()函數(shù)分析成績(jī)統(tǒng)計(jì)表插入與編輯圖表40（1）圖表（2）圖表的種類（3）圖表的類型（5）創(chuàng)建圖表（6）為圖表添加標(biāo)簽（7）為圖表添加趨勢(shì)線（8）為圖表添加誤差線分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)項(xiàng)目效果展示分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)創(chuàng)建成績(jī)統(tǒng)計(jì)表任務(wù)1.打開(kāi)成績(jī)總表，利用工作表引用操作，生成成績(jī)統(tǒng)計(jì)表。

分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)統(tǒng)計(jì)年級(jí)最高分和最低分任務(wù)1.打開(kāi)成績(jī)總表，利用單元格引用將4門(mén)課程的年級(jí)平均分、最高分和最低分引用到成績(jī)統(tǒng)計(jì)表中。

分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)統(tǒng)計(jì)考試不同情況的人數(shù)任務(wù)1.打開(kāi)成績(jī)統(tǒng)計(jì)表，利用COUNT、COUNTA函數(shù)統(tǒng)計(jì)不同情況的考試人數(shù)。

分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)統(tǒng)計(jì)不同分?jǐn)?shù)段人數(shù)任務(wù)1.打開(kāi)成績(jī)統(tǒng)計(jì)表，利用COUNTIF、COUNTIFS函數(shù)統(tǒng)計(jì)4門(mén)課程不同分?jǐn)?shù)段人數(shù)。

分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)統(tǒng)計(jì)優(yōu)秀率和及格率任務(wù)1.打開(kāi)成績(jī)統(tǒng)計(jì)表，計(jì)算4門(mén)課程的優(yōu)秀率和及格率。

分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)制作課程等級(jí)表任務(wù)1.打開(kāi)成績(jī)總表，利用單元格復(fù)制操作創(chuàng)建等級(jí)表結(jié)構(gòu)并刪除單元格數(shù)據(jù)，利用IF函數(shù)的嵌套并依據(jù)成績(jī)總表數(shù)據(jù)所在范圍轉(zhuǎn)換為等級(jí)值，要求90分以上為A等，75-90為B級(jí)，60-75為C級(jí)，60以下為D級(jí)。

任務(wù)2.在課程等級(jí)表中，利用條件格式將4門(mén)課程中所有B等的單元格設(shè)置為“淺紅色填充深紅色文本”，將所有A等的單元格設(shè)置為“黃色底紋綠色加粗字體”。

分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)利用條件格式設(shè)置成績(jī)總表格式任務(wù)1.打開(kāi)成績(jī)總表，復(fù)制工作表，重命名為“成績(jī)表”，利用條件格式將獲得一等獎(jiǎng)的女學(xué)生以淺紅色背景顯示。任務(wù)2.在“成績(jī)表”工作表中，利用條件格式的圖標(biāo)集將前10名用

表示，后10名用

表示，其余名次用表示。

分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)利用圖表分析成績(jī)統(tǒng)計(jì)表數(shù)據(jù)任務(wù)1.在“成績(jī)統(tǒng)計(jì)表”中，根據(jù)年級(jí)平均分、最高分和最低分制作圖表。要求圖表類型為“簇狀柱形圖”，圖表布局為“布局9”，圖表樣式為“樣式14”。任務(wù)2.在“成績(jī)統(tǒng)計(jì)表”中，為圖表添加“模擬運(yùn)算表”，并將“圖例”的位置移至圖表頂部。任務(wù)3.在“成績(jī)統(tǒng)計(jì)表”中，將圖表類型改為“簇狀圓柱圖”，將“圖表”移至新工作表中，將工作表命名為“成績(jī)統(tǒng)計(jì)圖”。分析成績(jī)統(tǒng)計(jì)表——項(xiàng)目實(shí)現(xiàn)利用圖表分析成績(jī)統(tǒng)計(jì)表數(shù)據(jù)任務(wù)5.在“成績(jī)統(tǒng)計(jì)圖”工作表中，設(shè)置“圖表區(qū)”、“背景墻”的填充效果，修飾圖表標(biāo)題。任務(wù)6.在“成績(jī)統(tǒng)計(jì)圖”工作表中，為圖表添加數(shù)據(jù)標(biāo)簽。任務(wù)4.在“成績(jī)統(tǒng)計(jì)圖”工作表中，調(diào)整圖表三維視圖的角度，修改圖例顏色，將縱向坐標(biāo)軸標(biāo)題橫向。

管理成績(jī)總表5管理成績(jī)總表常用函數(shù)52（1）VLOOKUP()函數(shù)。（2）SUMIF()函數(shù)（3）SUMIFS()函數(shù)管理成績(jī)總表名稱的定義53

在工作表中，可以使用“列標(biāo)”和“行號(hào)”引用單元格，也可以用“名稱”來(lái)表示單元格或單元格區(qū)域。使用名稱可以使用公式更容易理解和維護(hù)，使更新、審核和管理這些名稱更方便。管理成績(jī)總表數(shù)據(jù)透視表及切片器54

數(shù)據(jù)透視表是一種交互式工作表，用于對(duì)現(xiàn)有數(shù)據(jù)列表進(jìn)行匯總和分析。創(chuàng)建數(shù)據(jù)透視表后，可以按不同的需要，依不同的關(guān)系來(lái)提取和組織數(shù)據(jù)。（1）創(chuàng)建數(shù)據(jù)透視表（2）設(shè)置數(shù)據(jù)透視表字段（3）美化數(shù)據(jù)透視表管理成績(jī)總表數(shù)據(jù)透視圖55數(shù)據(jù)透視圖是以圖表的形式表示數(shù)據(jù)透視表中的數(shù)據(jù)。（1）創(chuàng)建數(shù)據(jù)透視圖（2）設(shè)置數(shù)據(jù)透視圖格式管理成績(jī)總表——項(xiàng)目實(shí)現(xiàn)項(xiàng)目效果展示管理成績(jī)總表—項(xiàng)目實(shí)現(xiàn)創(chuàng)建各專業(yè)成績(jī)表任務(wù)1.打開(kāi)“學(xué)籍管理”工作簿，利用“學(xué)籍表”和“成績(jī)總表”，通過(guò)復(fù)制工作表和VLOOKUP函數(shù)生成“各專業(yè)成績(jī)表”。

管理成績(jī)總表—項(xiàng)目實(shí)現(xiàn)計(jì)算不同專業(yè)各門(mén)課程總分任務(wù)1.在“各專業(yè)成績(jī)表”工作表中，利用SUMIF函數(shù)計(jì)算不同專業(yè)不同課程的總分。管理成績(jī)總表—項(xiàng)目實(shí)現(xiàn)計(jì)算不同專業(yè)不同輔導(dǎo)員各門(mén)課程總分任務(wù)1.在“各專業(yè)成績(jī)表”工作表中，利用SUMIFS函數(shù)計(jì)算不同輔導(dǎo)員所帶不同專業(yè)4門(mén)課程的總分。管理成績(jī)總表—項(xiàng)目實(shí)現(xiàn)用分類匯總方法統(tǒng)計(jì)每個(gè)輔