學(xué)生成績管理與分析教材課件

學(xué)生成績管理與分析教材課件項目8學(xué)生成績管理與分析會進行數(shù)據(jù)的選擇性粘貼會創(chuàng)建數(shù)據(jù)清單，會利用記錄單對數(shù)據(jù)清單進行編輯或條件查詢會設(shè)置工作表數(shù)據(jù)清單的樣式會設(shè)置數(shù)據(jù)清單快速排序和高級排序會設(shè)置數(shù)據(jù)自動篩選和高級篩選會設(shè)置數(shù)據(jù)的分類匯總會設(shè)置數(shù)據(jù)透視表會設(shè)置數(shù)據(jù)的圖表分析教學(xué)目標(biāo)項目8學(xué)生成績管理與分析會進行數(shù)據(jù)的選擇性粘貼教學(xué)目標(biāo)8.1任務(wù)要求創(chuàng)建名為“0801班學(xué)生成績管理分析.xls”的新工作簿，含“0801班學(xué)生成績表”的數(shù)據(jù)清單，內(nèi)容來源于項目七中的工作簿文件“08級計算機專業(yè)學(xué)生成績表.xls”內(nèi)的工作表“0801班”的相關(guān)內(nèi)容。8.1任務(wù)要求創(chuàng)建名為“0801班學(xué)生成績管能按單科總分或總分進行排序，快速找出單科總分或總分排名情況能按單科總分或總分進行排序，快速找出單科總分或總分排名情況能按多個關(guān)鍵字段進行復(fù)合排序，如先按總分排序，如果總分相同，再按計算機基礎(chǔ)分數(shù)排序等，即實現(xiàn)高級排序

能按多個關(guān)鍵字段進行復(fù)合排序，如先按總分排序，如果總分相同，根據(jù)指定條件對數(shù)據(jù)進行篩選，查詢符合條件的記錄，如篩選出總分大于300分的記錄

能進行復(fù)合篩選，如篩選總分大于300的A組學(xué)生

根據(jù)指定條件對數(shù)據(jù)進行篩選，查詢符合條件的記錄，如篩選出總分能按指定字段進行數(shù)據(jù)分類匯總，如按學(xué)習(xí)小組分析各組總分平均分、單科平均分等，還能對多個字段進行分類匯總能按指定字段進行數(shù)據(jù)分類匯總，如按學(xué)習(xí)小組分析各組總分平均分能將總分前五名的學(xué)生成績按照單科總分、總分制作柱形圖表能將總分前五名的學(xué)生成績按照單科總分、總分制作柱形圖表8.2解決方案創(chuàng)建名為“0801班學(xué)生成績管理分析.xls”的新工作簿，創(chuàng)建5個工作表，分別命名為“0801班學(xué)生成績數(shù)據(jù)清單”、“排序”、“篩選”、“匯總”和“圖表”等，其中第一個工作表的數(shù)據(jù)來自于項目七使用的對象文件，故可打開該文件，把相關(guān)內(nèi)容復(fù)制粘貼到目標(biāo)工作表中，因項目七中工作簿的許多數(shù)據(jù)是通過公式計算生成的，如果直接復(fù)制粘貼將會顯示錯誤信息，在此操作過程中必須使用數(shù)據(jù)的選擇性粘貼解決粘貼出錯問題。數(shù)據(jù)清單創(chuàng)建后，使用自動套用樣式和記錄單進行樣式設(shè)置和編輯查詢，另外還用到前面項目中已學(xué)習(xí)的工作表復(fù)制、工作表標(biāo)簽重命名等操作；按單科或總分的排序，能進行快速排序和高級排序操作。排序是數(shù)據(jù)處理常見的操作，使用Excel的數(shù)據(jù)排序功能可實現(xiàn)對單個字段排序或多個字段復(fù)合排序；設(shè)置數(shù)據(jù)的篩選，能進行自動篩選或高級篩選操作。在Excel中，提供了數(shù)據(jù)的自動篩選和高級篩選兩種模式，使用它們實現(xiàn)操作；對數(shù)據(jù)進行分類匯總和制作數(shù)據(jù)透視表；對總分前五名學(xué)生按成績制作柱形圖，可以先按總分降序排列學(xué)生成績，然后利用Excel中插入圖表的操作向?qū)е鸩酵瓿伞?.2解決方案創(chuàng)建名為“0801班學(xué)生成績8.3實現(xiàn)步驟1．創(chuàng)建名為“0801班學(xué)生成績管理分析.xls”的工作簿點擊桌面【開始】菜單，點擊【程序】|【MicrosoftOffice】|【MicrosoftOfficeExcel2003】命令，打開Excel2003。8.3.1創(chuàng)建并格式“0801班學(xué)生成績數(shù)據(jù)清單”單擊菜單欄上的【文件】|【保存】命令，打開“另存為”對話框。在文件名編輯欄內(nèi)輸入“0801班學(xué)生成績管理分析”，文件類型選擇Excel文件保存位置選擇在“D：\我的文檔”文件夾下。8.3實現(xiàn)步驟1．創(chuàng)建名為“0801班學(xué)生成績管理分析.x在工作表標(biāo)簽“Sheet1”前插入2個工作表重命名后圖示依次將5張工作表進行重命名在工作表標(biāo)簽“Sheet1”前插入2個工作表重命名后圖示依次2．創(chuàng)建工作表“0801班學(xué)生成績數(shù)據(jù)清單”在工作表“0801班學(xué)生成績數(shù)據(jù)清單”的單元格區(qū)域A1：H1內(nèi)，輸入標(biāo)題行

打開“08級計算機專業(yè)學(xué)生成績表.xls”工作簿，選定工作表“0801班”中的單元格區(qū)域A4:B33，并復(fù)制。在工作表“0801班學(xué)生成績數(shù)據(jù)清單”中，用鼠標(biāo)右鍵單擊單元格A2，在彈出的菜單中，點擊【粘貼】命令或按Ctrl+V組合鍵，“學(xué)號”和“姓名”的數(shù)據(jù)粘貼到單元格區(qū)域A2:B31中

。2．創(chuàng)建工作表“0801班學(xué)生成績數(shù)據(jù)清單”在工作表“08在“08級計算機專業(yè)學(xué)生成績表.xls”，復(fù)制工作表“0801班”中的單元格區(qū)域E4:E33，即“計算機基礎(chǔ)”單科總分

。在工作表“0801班學(xué)生成績數(shù)據(jù)清單”，選擇單元格D2，單擊鼠標(biāo)右鍵，在彈出的菜單中，點擊【選擇性粘貼】命令，在【選擇性粘貼】對話框的【粘貼】選項欄中，系統(tǒng)默認選項為“全部”，選中“數(shù)值”或“值和數(shù)字格式”選項，設(shè)置為粘貼數(shù)值。單擊【確定】按鈕，完成“計算機基礎(chǔ)”分數(shù)的復(fù)制粘貼

。在“08級計算機專業(yè)學(xué)生成績表.xls”，復(fù)制工作表“08為什么選中“數(shù)值”或“值和數(shù)字格式”選項，而不是選擇“全部”呢？設(shè)置為選中“數(shù)值”或“值和數(shù)字格式”選項的原因是“08級計算機專業(yè)學(xué)生成績表.xls”的工作表“0801班”中單元格區(qū)域E4:E33存儲的是運算公式，這里只需要復(fù)制計算結(jié)果即分數(shù)，不需要具體的運算公式。如果使用【選擇性粘貼】對話框的【粘貼】中的默認選項，會復(fù)制原單元格中存儲的公式，公式被粘貼后在新表中重新計算，而運算對象單元格已不是數(shù)字，會顯示出錯信息。使用“選擇性粘貼”功能完成“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”數(shù)據(jù)的復(fù)制粘貼為什么選中“數(shù)值”或“值和數(shù)字格式”選項，而不是選擇“全部”自動填充柄拖動C2的填充柄至C7使用相同操作，完成其他學(xué)習(xí)小組的數(shù)據(jù)錄入。選中數(shù)據(jù)區(qū)域A1：H31，在格式工具欄內(nèi)選擇字體為宋體、字號12磅，單擊居中按鈕，單擊邊框按鈕，設(shè)為無邊框線，單擊任意單元格，取消全選狀態(tài)。自動填充柄拖動C2的填充柄至C7使用相同操作，3．復(fù)制數(shù)據(jù)清單至工作表“排序”、“篩選”、“匯總”和“圖表”中，以便進行數(shù)據(jù)的排序、篩選、匯總和創(chuàng)建圖表等操作。點擊工作表“0801班學(xué)生成績數(shù)據(jù)清單”中全選按鈕，工作表區(qū)呈選定狀態(tài)，在工作表區(qū)任一處單擊鼠標(biāo)右鍵，在彈出的菜單中，執(zhí)行【復(fù)制】命令。打開“排序”工作表，選中單元格A1，單擊鼠標(biāo)右鍵，在彈出的菜單中，執(zhí)行【粘貼】命令。參照上述操作，依次將數(shù)據(jù)清單粘貼至“篩選”、“匯總”和“圖表”3個工作表。3．復(fù)制數(shù)據(jù)清單至工作表“排序”、“篩選”、“匯總”和“圖表4．設(shè)置工作表“0801班學(xué)生成績數(shù)據(jù)清單”的套用樣式選中工作表“0801班學(xué)生成績數(shù)據(jù)清單”的單元格區(qū)域A1:H31

。在格式列表中，拖動滾動條，選擇“序列1”格式，單擊【確定】按鈕

。將工作表“0801班學(xué)生成績數(shù)據(jù)清單”格式設(shè)置為自動套用“序列1”格式單擊菜單欄中的菜單項【格式】|【自動套用格式】命令，打開【自動套用格式】對話框

。4．設(shè)置工作表“0801班學(xué)生成績數(shù)據(jù)清單”的套用樣式選中工5．利用“記錄單”編輯數(shù)據(jù)清單單擊工作表“0801班學(xué)生成績數(shù)據(jù)清單”數(shù)據(jù)清單區(qū)域內(nèi)的任一單元格

。在【記錄單】對話框中，顯示當(dāng)前數(shù)據(jù)清單的首記錄

。單擊菜單欄上【數(shù)據(jù)】|【記錄單】命令，打開【記錄單】對話框

。單擊【上一條】或【下一條】按鈕或拖動垂直滾動條瀏覽數(shù)據(jù)清單中的記錄

。5．利用“記錄單”編輯數(shù)據(jù)清單單擊工作表“0801班學(xué)生成績6.利用“記錄單”進行條件查詢單擊【條件】按鈕輸入條件在“記錄單”中查詢數(shù)據(jù)清單中C組總分超過300分的學(xué)生記錄。單擊【下一條】按鈕，顯示出符合條件的首記錄單擊【下一條】按鈕，查看其他滿足條件的記錄單擊【條件】按鈕，啟動重新查詢，如果單擊【關(guān)閉】按鈕，關(guān)閉【記錄單】對話框。6.利用“記錄單”進行條件查詢單擊【條件】按鈕輸入條件在“記1．對工作表“排序”中的數(shù)據(jù)清單按“總分”字段設(shè)置為降序排列。8．3．2設(shè)置數(shù)據(jù)清單排序單擊工作表“排序”的標(biāo)簽，打開“排序”工作表

。單擊菜單欄上的菜單項【數(shù)據(jù)】|【排序】命令在“主要關(guān)鍵字”欄中選擇“總分”，選擇“降序”方式，在“我的數(shù)據(jù)區(qū)域”設(shè)置為“有標(biāo)題行”，單擊【確定】按鈕。此時每條記錄作為一個整體移動到相應(yīng)的行。1．對工作表“排序”中的數(shù)據(jù)清單按“總分”字段設(shè)置為降序排列其他方法：通過工具欄中的升序按鈕和降序按鈕進行快速排序單擊“總分”列中的任意數(shù)據(jù)單元格

。單擊降序按鈕

。其他方法：通過工具欄中的升序按鈕單擊“總分”列中的任意數(shù)據(jù)單2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果“高等數(shù)學(xué)”分數(shù)相同，再按照“計算機基礎(chǔ)”降序排列，如果“計算機基礎(chǔ)”分數(shù)也相同，再按照“學(xué)號”升序排列。打開工作表“排序”，選中數(shù)據(jù)區(qū)域的任意數(shù)據(jù)單元格

在“主要關(guān)鍵字”欄中選擇“高等數(shù)學(xué)”，選擇“降序”方式，在“次要關(guān)鍵字”選擇“計算機基礎(chǔ)”，選擇“降序”方式，在“第三關(guān)鍵字”選擇“學(xué)號”，選擇“升序”方式。在“我的數(shù)據(jù)區(qū)域”設(shè)置為“有標(biāo)題行”。單擊菜單欄中的菜單項【數(shù)據(jù)】|【排序】命令，打開【排序】對話框2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果“高等數(shù)學(xué)”分數(shù)相同，再按照“計算機基礎(chǔ)”降序排列，如果“計算機基礎(chǔ)”分數(shù)也相同，再按照“學(xué)號”升序排列。首先對以數(shù)字形式存儲的數(shù)字進行排序，然后對以文本形式存儲的數(shù)字進行排序。

在進行多條件的高級排序的操作過程中，彈出了【排序警告】對話框，這是因為工作表區(qū)域A列的學(xué)號是以文本形式存儲的數(shù)字數(shù)據(jù)數(shù)據(jù)類型1數(shù)字10數(shù)字11按文本存儲的數(shù)字5按文本存儲的數(shù)字數(shù)據(jù)數(shù)據(jù)類型1數(shù)字5按文本存儲的數(shù)字10數(shù)字11按文本存儲的數(shù)字同時對以文本和數(shù)字形式存儲的數(shù)字進行排序。因為A列的學(xué)號均為文本格式數(shù)字，故兩種處理方式均可。2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果1．篩選出“總分>300”的數(shù)據(jù)記錄8．3．3設(shè)置數(shù)據(jù)篩選單擊工作表“篩選”的標(biāo)簽，打開“篩選”工作表

。單擊菜單欄上的菜單項【數(shù)據(jù)】|【篩選】｜【自動篩選】命令在數(shù)據(jù)清單中每一列的列標(biāo)題右側(cè)都會出現(xiàn)【自動篩選】下拉按鈕，表示進入了自動篩選狀態(tài)

1．篩選出“總分>300”的數(shù)據(jù)記錄8．3．3設(shè)置數(shù)據(jù)篩單擊“總分”字段旁的下拉按鈕，彈出下拉框，在彈出的下拉框內(nèi)選擇“自定義”

?！翱偡帧毕吕斜磉x擇“大于”，后面的編輯欄中輸入“>300”

篩選結(jié)果單擊“總分”字段旁的下拉按鈕，彈出下拉框，在彈出的下拉框內(nèi)選若篩選出“總分>300”的“王”姓學(xué)生的數(shù)據(jù)記錄條件下拉列表選擇“等于”，編輯欄輸入“王*”單擊“姓名”【自動篩選】下拉按鈕，選擇“自定義”。

篩選結(jié)果若篩選出“總分>300”的“王”姓學(xué)生的數(shù)據(jù)記錄條件下拉列表2.篩選出總分大于300分的A組學(xué)生記錄，在單元格區(qū)域A33：B34內(nèi)錄入篩選條件，篩選結(jié)果從單元格A36開始顯示，原數(shù)據(jù)清單仍顯示。在空白單元格區(qū)域A33:B34內(nèi)輸入篩選條件。單擊菜單欄中【數(shù)據(jù)】|【篩選】|【高級篩選】命令，打開【高級篩選】對話框。單擊“條件區(qū)域”和“復(fù)制到”編輯欄右側(cè)的拾取按取分別將條件區(qū)域A33：B34和A36填入到編輯欄內(nèi)

篩選結(jié)果

2.篩選出總分大于300分的A組學(xué)生記錄，在單元格區(qū)域A338．3．4設(shè)置數(shù)據(jù)匯總打開“匯總”工作表，選中數(shù)據(jù)區(qū)域內(nèi)任一單元格

。單擊菜單欄中的菜單項【數(shù)據(jù)】|【分類匯總】命令，打開【分類匯總】對話框

“匯總方式”選擇“平均值”，“選定匯總項”選擇“計算機基礎(chǔ)”、“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”在工作表“匯總”中，按學(xué)習(xí)小組統(tǒng)計“計算機基礎(chǔ)”、“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”的平均值。8．3．4設(shè)置數(shù)據(jù)匯總打開“匯總”工作表，選中數(shù)據(jù)區(qū)域在工作表“匯總”中，按學(xué)習(xí)小組統(tǒng)計“計算機基礎(chǔ)”、“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”的平均值。匯總結(jié)果單擊數(shù)據(jù)清單左側(cè)的“隱藏明細數(shù)據(jù)符號”符號按鈕，顯示“分類匯總”后的數(shù)據(jù)結(jié)果，此時符號按鈕變成符號按鈕在工作表“匯總”中，按學(xué)習(xí)小組統(tǒng)計“計算機基礎(chǔ)”、“計算機8．3．5設(shè)置數(shù)據(jù)透視表選中工作表“0801班學(xué)生成績數(shù)據(jù)單”的數(shù)據(jù)清單中的任一單元格

。單擊【數(shù)據(jù)】|【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之1】對話框

利用“數(shù)據(jù)透視表”對“0801班學(xué)生成績數(shù)據(jù)清單”按學(xué)習(xí)小組和姓名統(tǒng)計總分。單擊【下一步】按鈕，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2】對話框

。8．3．5設(shè)置數(shù)據(jù)透視表選中工作表“0801班學(xué)生成績此步驟用于選取創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域，系統(tǒng)自動設(shè)定當(dāng)前數(shù)據(jù)清單區(qū)域。如果要重新選定數(shù)據(jù)區(qū)域，可以通過拾取按鈕或直接在編輯欄內(nèi)輸入?yún)^(qū)域，如果選定區(qū)域來自外部文件可通過單擊【瀏覽】來完成

。單擊【下一步】按鈕，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3】對話框，用于設(shè)置數(shù)據(jù)透視表的顯示位置。在此選擇默認值“新建工作表”，單擊【完成】按鈕

。此步驟用于選取創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域，系統(tǒng)自動設(shè)定當(dāng)前數(shù)此時Excel在當(dāng)前工作表前插入了一個新工作表“Sheet1”，并自動打開該工作表，顯示數(shù)據(jù)透視表的頁面布局，頁面布局框的右側(cè)顯示【數(shù)據(jù)透視表字段列表】對話框。頁字段：在數(shù)據(jù)透視表中指定為頁方向的字段。在頁字段中，既可以顯示所有項的匯總，也可以一次顯示一項，篩選掉其他數(shù)據(jù)。數(shù)據(jù)項：在數(shù)據(jù)透視表中要匯總的數(shù)據(jù)。

列（行）字段：數(shù)據(jù)透視表中按列（行）顯示的字段。此時Excel在當(dāng)前工作表前插入了一個新工作在【數(shù)據(jù)透視表字段列表】中拖動“學(xué)習(xí)小組”字段至“請將頁字段拖至此處”字樣的位置，或先選中“學(xué)習(xí)小組”字段，在“添加到”的右側(cè)下拉列表中選擇“頁面區(qū)域”，單擊【添加到】按鈕操作也可。下面，可用拖動方式操作。將“姓名”拖至“將行字段拖至此處”字樣的位置，將“總分”拖至“請將數(shù)據(jù)項拖至此處”字樣的位置，得到數(shù)據(jù)透視表結(jié)果。在【數(shù)據(jù)透視表字段列表】中拖動“學(xué)習(xí)小組”字單擊單元格B2處的下拉按鈕，可以選擇學(xué)習(xí)小組的有效值，如選擇“A組”，則在行字段和數(shù)據(jù)區(qū)域中顯示A組學(xué)生姓名和總分匯總

。雙擊數(shù)據(jù)透視表中的字段單元格，可打開【數(shù)據(jù)透視表字段】對話框，在框中可設(shè)置分類匯總的類型

。單擊單元格B2處的下拉按鈕，可以選擇學(xué)習(xí)小組的有效值，如選擇8．3．6創(chuàng)建數(shù)據(jù)圖表選中工作表“圖表”數(shù)據(jù)清單內(nèi)的“總分”列的任一單元格，單擊工具欄中的降序排列按鈕，此時按總分降序進行排列，前五名學(xué)生依次顯示在第2行至第6行內(nèi)。單擊【下一步】按鈕，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2】對話框

。1．將總分前五名的學(xué)生成績按照單科總分、總分制作柱形圖，創(chuàng)建圖表8．3．6創(chuàng)建數(shù)據(jù)圖表選中工作表“圖表”數(shù)據(jù)清單內(nèi)的“選中數(shù)據(jù)區(qū)域內(nèi)任一單元格，單擊【插入】|【圖表】命令，打開【圖表向?qū)?4步驟之1-圖表類型】對話框

。單擊【數(shù)據(jù)區(qū)域】選項卡

。在【標(biāo)準(zhǔn)類型】選項卡的“圖表類型”列表中，系統(tǒng)默認為“柱形圖”

。“子圖表類型”選擇“簇狀柱形圖”

。單擊“數(shù)據(jù)區(qū)域”編輯欄右側(cè)的拾取按鈕，將前五名學(xué)生的姓名和單科總分、總分拾?。ú僮鞣椒ǎ喊醋trl鍵，依次選擇單擊格區(qū)域B2:B6和D2:H6）

。選中數(shù)據(jù)區(qū)域內(nèi)任一單元格，單擊【插入】|【圖表】命令，打開【單擊【系列】選項卡

。在“系列”列表中，選擇“系列1”，在名稱框內(nèi)輸入“計算機基礎(chǔ)”，仿此操作，依次逐一更名各系列

。單擊【系列】選項卡。在“系列”列表中，選擇“系列1”，在名單擊【標(biāo)題】選項卡

。分類（X）軸欄輸入“姓名”，數(shù)值（Y）軸欄輸入“分數(shù)”

。在圖表標(biāo)題欄輸入“0801班前五名學(xué)生成績圖”

。單擊【圖例】選項卡

。選擇位置“靠上”選項

。單擊【下一步】按鈕

。單擊【標(biāo)題】選項卡。分類（X）軸欄輸入“姓名”，數(shù)值（Y）選擇“作為其中的對象插入”單選項，生成圖表插入到“圖表”工作表中,本項目選擇該項

。單擊【完成】按鈕

。圖表大小控點圖表標(biāo)題圖例繪圖區(qū)圖表區(qū)選擇“作為新工作表插入”，將會插入一個圖表。選擇“作為其中的對象插入”單選項，生成圖表插入到“圖表”工作8.4實踐總結(jié)在本項目的實踐中，通過創(chuàng)建數(shù)據(jù)清單，完成了數(shù)據(jù)的排序、篩選、匯總、數(shù)據(jù)透視和圖表的操作，對數(shù)據(jù)進行快速管理和分析，這些操作應(yīng)用非常廣泛，利用Excel強大的數(shù)據(jù)管理功能，實現(xiàn)方便地組織、管理和分析大量的數(shù)據(jù)信息，因此要熟練掌握其操作要領(lǐng)。8.4實踐總結(jié)在本項目的實踐中，通過創(chuàng)建數(shù)學(xué)生成績管理與分析教材課件項目8學(xué)生成績管理與分析會進行數(shù)據(jù)的選擇性粘貼會創(chuàng)建數(shù)據(jù)清單，會利用記錄單對數(shù)據(jù)清單進行編輯或條件查詢會設(shè)置工作表數(shù)據(jù)清單的樣式會設(shè)置數(shù)據(jù)清單快速排序和高級排序會設(shè)置數(shù)據(jù)自動篩選和高級篩選會設(shè)置數(shù)據(jù)的分類匯總會設(shè)置數(shù)據(jù)透視表會設(shè)置數(shù)據(jù)的圖表分析教學(xué)目標(biāo)項目8學(xué)生成績管理與分析會進行數(shù)據(jù)的選擇性粘貼教學(xué)目標(biāo)8.1任務(wù)要求創(chuàng)建名為“0801班學(xué)生成績管理分析.xls”的新工作簿，含“0801班學(xué)生成績表”的數(shù)據(jù)清單，內(nèi)容來源于項目七中的工作簿文件“08級計算機專業(yè)學(xué)生成績表.xls”內(nèi)的工作表“0801班”的相關(guān)內(nèi)容。8.1任務(wù)要求創(chuàng)建名為“0801班學(xué)生成績管能按單科總分或總分進行排序，快速找出單科總分或總分排名情況能按單科總分或總分進行排序，快速找出單科總分或總分排名情況能按多個關(guān)鍵字段進行復(fù)合排序，如先按總分排序，如果總分相同，再按計算機基礎(chǔ)分數(shù)排序等，即實現(xiàn)高級排序

能按多個關(guān)鍵字段進行復(fù)合排序，如先按總分排序，如果總分相同，根據(jù)指定條件對數(shù)據(jù)進行篩選，查詢符合條件的記錄，如篩選出總分大于300分的記錄

能進行復(fù)合篩選，如篩選總分大于300的A組學(xué)生

根據(jù)指定條件對數(shù)據(jù)進行篩選，查詢符合條件的記錄，如篩選出總分能按指定字段進行數(shù)據(jù)分類匯總，如按學(xué)習(xí)小組分析各組總分平均分、單科平均分等，還能對多個字段進行分類匯總能按指定字段進行數(shù)據(jù)分類匯總，如按學(xué)習(xí)小組分析各組總分平均分能將總分前五名的學(xué)生成績按照單科總分、總分制作柱形圖表能將總分前五名的學(xué)生成績按照單科總分、總分制作柱形圖表8.2解決方案創(chuàng)建名為“0801班學(xué)生成績管理分析.xls”的新工作簿，創(chuàng)建5個工作表，分別命名為“0801班學(xué)生成績數(shù)據(jù)清單”、“排序”、“篩選”、“匯總”和“圖表”等，其中第一個工作表的數(shù)據(jù)來自于項目七使用的對象文件，故可打開該文件，把相關(guān)內(nèi)容復(fù)制粘貼到目標(biāo)工作表中，因項目七中工作簿的許多數(shù)據(jù)是通過公式計算生成的，如果直接復(fù)制粘貼將會顯示錯誤信息，在此操作過程中必須使用數(shù)據(jù)的選擇性粘貼解決粘貼出錯問題。數(shù)據(jù)清單創(chuàng)建后，使用自動套用樣式和記錄單進行樣式設(shè)置和編輯查詢，另外還用到前面項目中已學(xué)習(xí)的工作表復(fù)制、工作表標(biāo)簽重命名等操作；按單科或總分的排序，能進行快速排序和高級排序操作。排序是數(shù)據(jù)處理常見的操作，使用Excel的數(shù)據(jù)排序功能可實現(xiàn)對單個字段排序或多個字段復(fù)合排序；設(shè)置數(shù)據(jù)的篩選，能進行自動篩選或高級篩選操作。在Excel中，提供了數(shù)據(jù)的自動篩選和高級篩選兩種模式，使用它們實現(xiàn)操作；對數(shù)據(jù)進行分類匯總和制作數(shù)據(jù)透視表；對總分前五名學(xué)生按成績制作柱形圖，可以先按總分降序排列學(xué)生成績，然后利用Excel中插入圖表的操作向?qū)е鸩酵瓿伞?.2解決方案創(chuàng)建名為“0801班學(xué)生成績8.3實現(xiàn)步驟1．創(chuàng)建名為“0801班學(xué)生成績管理分析.xls”的工作簿點擊桌面【開始】菜單，點擊【程序】|【MicrosoftOffice】|【MicrosoftOfficeExcel2003】命令，打開Excel2003。8.3.1創(chuàng)建并格式“0801班學(xué)生成績數(shù)據(jù)清單”單擊菜單欄上的【文件】|【保存】命令，打開“另存為”對話框。在文件名編輯欄內(nèi)輸入“0801班學(xué)生成績管理分析”，文件類型選擇Excel文件保存位置選擇在“D：\我的文檔”文件夾下。8.3實現(xiàn)步驟1．創(chuàng)建名為“0801班學(xué)生成績管理分析.x在工作表標(biāo)簽“Sheet1”前插入2個工作表重命名后圖示依次將5張工作表進行重命名在工作表標(biāo)簽“Sheet1”前插入2個工作表重命名后圖示依次2．創(chuàng)建工作表“0801班學(xué)生成績數(shù)據(jù)清單”在工作表“0801班學(xué)生成績數(shù)據(jù)清單”的單元格區(qū)域A1：H1內(nèi)，輸入標(biāo)題行

打開“08級計算機專業(yè)學(xué)生成績表.xls”工作簿，選定工作表“0801班”中的單元格區(qū)域A4:B33，并復(fù)制。在工作表“0801班學(xué)生成績數(shù)據(jù)清單”中，用鼠標(biāo)右鍵單擊單元格A2，在彈出的菜單中，點擊【粘貼】命令或按Ctrl+V組合鍵，“學(xué)號”和“姓名”的數(shù)據(jù)粘貼到單元格區(qū)域A2:B31中

。2．創(chuàng)建工作表“0801班學(xué)生成績數(shù)據(jù)清單”在工作表“08在“08級計算機專業(yè)學(xué)生成績表.xls”，復(fù)制工作表“0801班”中的單元格區(qū)域E4:E33，即“計算機基礎(chǔ)”單科總分

。在工作表“0801班學(xué)生成績數(shù)據(jù)清單”，選擇單元格D2，單擊鼠標(biāo)右鍵，在彈出的菜單中，點擊【選擇性粘貼】命令，在【選擇性粘貼】對話框的【粘貼】選項欄中，系統(tǒng)默認選項為“全部”，選中“數(shù)值”或“值和數(shù)字格式”選項，設(shè)置為粘貼數(shù)值。單擊【確定】按鈕，完成“計算機基礎(chǔ)”分數(shù)的復(fù)制粘貼

。在“08級計算機專業(yè)學(xué)生成績表.xls”，復(fù)制工作表“08為什么選中“數(shù)值”或“值和數(shù)字格式”選項，而不是選擇“全部”呢？設(shè)置為選中“數(shù)值”或“值和數(shù)字格式”選項的原因是“08級計算機專業(yè)學(xué)生成績表.xls”的工作表“0801班”中單元格區(qū)域E4:E33存儲的是運算公式，這里只需要復(fù)制計算結(jié)果即分數(shù)，不需要具體的運算公式。如果使用【選擇性粘貼】對話框的【粘貼】中的默認選項，會復(fù)制原單元格中存儲的公式，公式被粘貼后在新表中重新計算，而運算對象單元格已不是數(shù)字，會顯示出錯信息。使用“選擇性粘貼”功能完成“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”數(shù)據(jù)的復(fù)制粘貼為什么選中“數(shù)值”或“值和數(shù)字格式”選項，而不是選擇“全部”自動填充柄拖動C2的填充柄至C7使用相同操作，完成其他學(xué)習(xí)小組的數(shù)據(jù)錄入。選中數(shù)據(jù)區(qū)域A1：H31，在格式工具欄內(nèi)選擇字體為宋體、字號12磅，單擊居中按鈕，單擊邊框按鈕，設(shè)為無邊框線，單擊任意單元格，取消全選狀態(tài)。自動填充柄拖動C2的填充柄至C7使用相同操作，3．復(fù)制數(shù)據(jù)清單至工作表“排序”、“篩選”、“匯總”和“圖表”中，以便進行數(shù)據(jù)的排序、篩選、匯總和創(chuàng)建圖表等操作。點擊工作表“0801班學(xué)生成績數(shù)據(jù)清單”中全選按鈕，工作表區(qū)呈選定狀態(tài)，在工作表區(qū)任一處單擊鼠標(biāo)右鍵，在彈出的菜單中，執(zhí)行【復(fù)制】命令。打開“排序”工作表，選中單元格A1，單擊鼠標(biāo)右鍵，在彈出的菜單中，執(zhí)行【粘貼】命令。參照上述操作，依次將數(shù)據(jù)清單粘貼至“篩選”、“匯總”和“圖表”3個工作表。3．復(fù)制數(shù)據(jù)清單至工作表“排序”、“篩選”、“匯總”和“圖表4．設(shè)置工作表“0801班學(xué)生成績數(shù)據(jù)清單”的套用樣式選中工作表“0801班學(xué)生成績數(shù)據(jù)清單”的單元格區(qū)域A1:H31

。在格式列表中，拖動滾動條，選擇“序列1”格式，單擊【確定】按鈕

。將工作表“0801班學(xué)生成績數(shù)據(jù)清單”格式設(shè)置為自動套用“序列1”格式單擊菜單欄中的菜單項【格式】|【自動套用格式】命令，打開【自動套用格式】對話框

。4．設(shè)置工作表“0801班學(xué)生成績數(shù)據(jù)清單”的套用樣式選中工5．利用“記錄單”編輯數(shù)據(jù)清單單擊工作表“0801班學(xué)生成績數(shù)據(jù)清單”數(shù)據(jù)清單區(qū)域內(nèi)的任一單元格

。在【記錄單】對話框中，顯示當(dāng)前數(shù)據(jù)清單的首記錄

。單擊菜單欄上【數(shù)據(jù)】|【記錄單】命令，打開【記錄單】對話框

。單擊【上一條】或【下一條】按鈕或拖動垂直滾動條瀏覽數(shù)據(jù)清單中的記錄

。5．利用“記錄單”編輯數(shù)據(jù)清單單擊工作表“0801班學(xué)生成績6.利用“記錄單”進行條件查詢單擊【條件】按鈕輸入條件在“記錄單”中查詢數(shù)據(jù)清單中C組總分超過300分的學(xué)生記錄。單擊【下一條】按鈕，顯示出符合條件的首記錄單擊【下一條】按鈕，查看其他滿足條件的記錄單擊【條件】按鈕，啟動重新查詢，如果單擊【關(guān)閉】按鈕，關(guān)閉【記錄單】對話框。6.利用“記錄單”進行條件查詢單擊【條件】按鈕輸入條件在“記1．對工作表“排序”中的數(shù)據(jù)清單按“總分”字段設(shè)置為降序排列。8．3．2設(shè)置數(shù)據(jù)清單排序單擊工作表“排序”的標(biāo)簽，打開“排序”工作表

。單擊菜單欄上的菜單項【數(shù)據(jù)】|【排序】命令在“主要關(guān)鍵字”欄中選擇“總分”，選擇“降序”方式，在“我的數(shù)據(jù)區(qū)域”設(shè)置為“有標(biāo)題行”，單擊【確定】按鈕。此時每條記錄作為一個整體移動到相應(yīng)的行。1．對工作表“排序”中的數(shù)據(jù)清單按“總分”字段設(shè)置為降序排列其他方法：通過工具欄中的升序按鈕和降序按鈕進行快速排序單擊“總分”列中的任意數(shù)據(jù)單元格

。單擊降序按鈕

。其他方法：通過工具欄中的升序按鈕單擊“總分”列中的任意數(shù)據(jù)單2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果“高等數(shù)學(xué)”分數(shù)相同，再按照“計算機基礎(chǔ)”降序排列，如果“計算機基礎(chǔ)”分數(shù)也相同，再按照“學(xué)號”升序排列。打開工作表“排序”，選中數(shù)據(jù)區(qū)域的任意數(shù)據(jù)單元格

在“主要關(guān)鍵字”欄中選擇“高等數(shù)學(xué)”，選擇“降序”方式，在“次要關(guān)鍵字”選擇“計算機基礎(chǔ)”，選擇“降序”方式，在“第三關(guān)鍵字”選擇“學(xué)號”，選擇“升序”方式。在“我的數(shù)據(jù)區(qū)域”設(shè)置為“有標(biāo)題行”。單擊菜單欄中的菜單項【數(shù)據(jù)】|【排序】命令，打開【排序】對話框2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果“高等數(shù)學(xué)”分數(shù)相同，再按照“計算機基礎(chǔ)”降序排列，如果“計算機基礎(chǔ)”分數(shù)也相同，再按照“學(xué)號”升序排列。首先對以數(shù)字形式存儲的數(shù)字進行排序，然后對以文本形式存儲的數(shù)字進行排序。

在進行多條件的高級排序的操作過程中，彈出了【排序警告】對話框，這是因為工作表區(qū)域A列的學(xué)號是以文本形式存儲的數(shù)字數(shù)據(jù)數(shù)據(jù)類型1數(shù)字10數(shù)字11按文本存儲的數(shù)字5按文本存儲的數(shù)字數(shù)據(jù)數(shù)據(jù)類型1數(shù)字5按文本存儲的數(shù)字10數(shù)字11按文本存儲的數(shù)字同時對以文本和數(shù)字形式存儲的數(shù)字進行排序。因為A列的學(xué)號均為文本格式數(shù)字，故兩種處理方式均可。2．在工作表“排序”中，按“高等數(shù)學(xué)”列設(shè)置為降序排列，如果1．篩選出“總分>300”的數(shù)據(jù)記錄8．3．3設(shè)置數(shù)據(jù)篩選單擊工作表“篩選”的標(biāo)簽，打開“篩選”工作表

。單擊菜單欄上的菜單項【數(shù)據(jù)】|【篩選】｜【自動篩選】命令在數(shù)據(jù)清單中每一列的列標(biāo)題右側(cè)都會出現(xiàn)【自動篩選】下拉按鈕，表示進入了自動篩選狀態(tài)

1．篩選出“總分>300”的數(shù)據(jù)記錄8．3．3設(shè)置數(shù)據(jù)篩單擊“總分”字段旁的下拉按鈕，彈出下拉框，在彈出的下拉框內(nèi)選擇“自定義”

?！翱偡帧毕吕斜磉x擇“大于”，后面的編輯欄中輸入“>300”

篩選結(jié)果單擊“總分”字段旁的下拉按鈕，彈出下拉框，在彈出的下拉框內(nèi)選若篩選出“總分>300”的“王”姓學(xué)生的數(shù)據(jù)記錄條件下拉列表選擇“等于”，編輯欄輸入“王*”單擊“姓名”【自動篩選】下拉按鈕，選擇“自定義”。

篩選結(jié)果若篩選出“總分>300”的“王”姓學(xué)生的數(shù)據(jù)記錄條件下拉列表2.篩選出總分大于300分的A組學(xué)生記錄，在單元格區(qū)域A33：B34內(nèi)錄入篩選條件，篩選結(jié)果從單元格A36開始顯示，原數(shù)據(jù)清單仍顯示。在空白單元格區(qū)域A33:B34內(nèi)輸入篩選條件。單擊菜單欄中【數(shù)據(jù)】|【篩選】|【高級篩選】命令，打開【高級篩選】對話框。單擊“條件區(qū)域”和“復(fù)制到”編輯欄右側(cè)的拾取按取分別將條件區(qū)域A33：B34和A36填入到編輯欄內(nèi)

篩選結(jié)果

2.篩選出總分大于300分的A組學(xué)生記錄，在單元格區(qū)域A338．3．4設(shè)置數(shù)據(jù)匯總打開“匯總”工作表，選中數(shù)據(jù)區(qū)域內(nèi)任一單元格

。單擊菜單欄中的菜單項【數(shù)據(jù)】|【分類匯總】命令，打開【分類匯總】對話框

“匯總方式”選擇“平均值”，“選定匯總項”選擇“計算機基礎(chǔ)”、“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”在工作表“匯總”中，按學(xué)習(xí)小組統(tǒng)計“計算機基礎(chǔ)”、“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”的平均值。8．3．4設(shè)置數(shù)據(jù)匯總打開“匯總”工作表，選中數(shù)據(jù)區(qū)域在工作表“匯總”中，按學(xué)習(xí)小組統(tǒng)計“計算機基礎(chǔ)”、“計算機組裝”、“大學(xué)英語”、“高等數(shù)學(xué)”和“總分”的平均值。匯總結(jié)果單擊數(shù)據(jù)清單左側(cè)的“隱藏明細數(shù)據(jù)符號”符號按鈕，顯示“分類匯總”后的數(shù)據(jù)結(jié)果，此時符號按鈕變成符號按鈕在工作表“匯總”中，按學(xué)習(xí)小組統(tǒng)計“計算機基礎(chǔ)”、“計算機8．3．5設(shè)置數(shù)據(jù)透視表選中工作表“0801班學(xué)生成績數(shù)據(jù)單”的數(shù)據(jù)清單中的任一單元格

。單擊【數(shù)據(jù)】|【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之1】對話框

利用“數(shù)據(jù)透視表”對“0801班學(xué)生成績數(shù)據(jù)清單”按學(xué)習(xí)小組和姓名統(tǒng)計總分。單擊【下一步】按鈕，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之2】對話框

。8．3．5設(shè)置數(shù)據(jù)透視表選中工作表“0801班學(xué)生成績此步驟用于選取創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域，系統(tǒng)自動設(shè)定當(dāng)前數(shù)據(jù)清單區(qū)域。如果要重新選定數(shù)據(jù)區(qū)域，可以通過拾取按鈕或直接在編輯欄內(nèi)輸入?yún)^(qū)域，如果選定區(qū)域來自外部文件可通過單擊【瀏覽】來完成

。單擊【下一步】按鈕，打開【數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3】對話框，用于設(shè)置數(shù)據(jù)透視表的顯示位置。在此選擇默認值“新建工作表”，單擊【完成】按鈕

。此步驟用于選取創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域，系統(tǒng)自動設(shè)定當(dāng)前數(shù)此時Excel在當(dāng)前工作表前插入了一個新工作表“Sheet1”，并自動打開該工作表，顯示數(shù)據(jù)透視表的頁面布局，頁面布局框的右側(cè)顯示【數(shù)據(jù)透視表字段列表】對話框。