案例實(shí)踐五：Excel(合并版).ppt

電子報(bào)表處理軟件Excel,主要內(nèi)容,工作薄、工作表和單元格 數(shù)據(jù)的輸入 設(shè)置數(shù)據(jù)有效性與數(shù)據(jù)填充自定義序列 區(qū)域選擇、單元格格式、條件格式和自動(dòng)套用格式 公式與函數(shù)以及單元格引用 數(shù)據(jù)表的排序、篩選與匯總 圖表的建立,案例描述,一、Excel 2003 工作表的建立與編輯 任務(wù)1：新建一個(gè)工作簿文件，并將其以“學(xué)生成績(jī)管理.xls”為文件名保存到“D:”，在該文件中新建一張名為“學(xué)生信息”的工作表，參照效果圖，輸入各行數(shù)據(jù)。 其中：“學(xué)生姓名”可以通過(guò)建立自定義序列（工具-選項(xiàng)-自定義序列）方便自動(dòng)填充輸入；“專業(yè)”通過(guò)設(shè)置數(shù)據(jù)有效性（利用“基礎(chǔ)數(shù)據(jù)”表中的數(shù)據(jù)源將專業(yè)定義為名稱，插入-名稱-定義；需要輸入專業(yè)的列設(shè)置“數(shù)據(jù)有效性”，數(shù)據(jù)-有效性），通過(guò)下拉列表進(jìn)行選擇填入。 任務(wù)2：在“學(xué)生信息”表列標(biāo)題所在行前插入標(biāo)題，內(nèi)容為“學(xué)生個(gè)人信息表”，為姓名為“陳一鳴”的同學(xué)在姓名單元格上添加一批注，其內(nèi)容為“學(xué)生會(huì)副主席”。,案例描述,一、Excel 2003 工作表的建立與編輯（續(xù)） 任務(wù)3：新建學(xué)生各門課程的成績(jī)表（課程不少于5門），參照效果圖輸入5次平時(shí)成績(jī)、期中以及期末成績(jī)。 任務(wù)4：將“學(xué)生信息”表中的“學(xué)號(hào)”、“姓名”2列數(shù)據(jù)（包括列標(biāo)題）復(fù)制到sheet8中，將sheet8命名為“成績(jī)匯總表”。 任務(wù)5：為“成績(jī)匯總表”建立一個(gè)備份，放在最后，將其命名為“成績(jī)表備份”。,預(yù)備知識(shí),Excel 2003 的啟動(dòng)和退出 Excel 2003 窗口基本組成（軟件環(huán)境介紹） Excel 2003 窗口組成 工作簿 工作表 單元格 不同數(shù)據(jù)類型的數(shù)據(jù)輸入說(shuō)明 數(shù)值數(shù)據(jù)及其輸入 文本數(shù)據(jù)及其輸入 日期、時(shí)間數(shù)據(jù)及其輸入 工作表與單元格的選擇 設(shè)置數(shù)據(jù)有效性與數(shù)據(jù)填充 查找與替換 操作的撤銷與恢復(fù),任務(wù)完成,任務(wù)1 步驟1：?jiǎn)?dòng)Excel 2003后，系統(tǒng)自動(dòng)新建一個(gè)工作簿文件，默認(rèn)名稱為“Book1”，若原來(lái)Excel已啟動(dòng)，可單擊主窗口上“文件”菜單中的“新建”命令。 （若該“學(xué)生成績(jī)管理”文件已經(jīng)存在，選擇“打開”菜單打開該文件。案例中為方便后續(xù)操作，已新建該文件，并已有“基礎(chǔ)數(shù)據(jù)”工作表。 步驟2：選擇工作表Sheet1右擊，在彈出的快捷菜單中選擇“重命名”，或雙擊工作表標(biāo)簽，此時(shí)直接在光標(biāo)顯示位置上輸入“學(xué)生信息”。 步驟3：選擇“基礎(chǔ)數(shù)據(jù)”表，選定G2:G7數(shù)據(jù)區(qū)域，單擊“插入”名稱”定義”，在彈出的“定義名稱”對(duì)話框中輸入名稱“專業(yè)”，再單擊“確定”按鈕。 步驟4：“學(xué)生信息”工作表：“專業(yè)”列，單擊菜單“數(shù)據(jù)”有效性”，在彈出的“數(shù)據(jù)有效性”對(duì)話框中“設(shè)置”，設(shè)置允許“序列”，來(lái)源“=專業(yè)”，單擊“確定”按鈕。此時(shí)選中專業(yè)列各單元格，右邊出現(xiàn)下拉小箭頭，單擊下拉選擇專業(yè)進(jìn)行輸入。,任務(wù)完成,任務(wù)1（續(xù)） 步驟5：選擇“基礎(chǔ)數(shù)據(jù)”表，選定C2:C31數(shù)據(jù)區(qū)域，單擊“工具”-“選項(xiàng)”-“自定義序列”，建立填充柄數(shù)據(jù)。 步驟6： “學(xué)生信息”工作表：“姓名”列，輸入第一個(gè)學(xué)生的姓名后，其他姓名可以通過(guò)自動(dòng)填充輸入。其他各列對(duì)照效果圖在“學(xué)生信息”工作表中正確輸入相應(yīng)的數(shù)據(jù)。 步驟7：?jiǎn)螕糁鞔翱谏稀拔募辈藛蜗碌摹氨４妗泵睿騿螕簟俺Ｓ谩惫ぞ邫谏系摹氨４妗卑粹o，此時(shí)系統(tǒng)彈出“另存為”對(duì)話框，在該對(duì)話框中選擇D盤，輸入正確的文件名“學(xué)生成績(jī)管理”。,任務(wù)完成,任務(wù)2 步驟1：選定第一行或光標(biāo)移至第一行中，利用“插入”菜單下的“行”命令插入一空行；或直接在該行上右擊，從其快捷菜單中選擇“插入”命令，打開“插入”對(duì)話框，在對(duì)話框中選定“整行”后單擊“確定”按鈕，系統(tǒng)將插入一空行。 步驟2：在插入的行中的第一個(gè)單元格（即A1單元格）中輸入標(biāo)題文本“學(xué)生個(gè)人信息表”。 步驟3：?jiǎn)螕簟熬庉嫛辈藛沃械摹安檎摇泵?，打開“查找和替換”對(duì)話框，在該對(duì)話框的“查找”選項(xiàng)卡中”查找內(nèi)容”輸入為“陳一鳴”，單擊“查找下一個(gè)”按鈕并單擊“關(guān)閉”按鈕。 步驟4：?jiǎn)螕簟安迦搿辈藛沃械摹芭ⅰ泵?，在彈出的文本框中輸入文本“學(xué)生會(huì)副主席”，然后單擊批注框外任意一個(gè)單元格。,任務(wù)完成,任務(wù)3 步驟1：右鍵單擊最后一張工作標(biāo)簽，在彈出的快捷菜單中選擇“插入”命令，在彈出的“插入”對(duì)話框中選擇“插入工作表”，單擊“確定”按鈕完成插入。右鍵單擊剛插入的工作標(biāo)簽，在彈出的快捷菜單中選擇“重命名”命令，將其重命名為“高等數(shù)學(xué)成績(jī)”。 步驟2：選中“學(xué)生信息”表中的A2:B32單元格區(qū)域，利用“編輯”菜單下的“復(fù)制”命令；或單擊常用工具欄中的“復(fù)制”按鈕（或Ctrl+C） 。選中“高等數(shù)學(xué)成績(jī)”表，光標(biāo)移到A1單元格，選擇“編輯”菜單下的“粘貼”命令；或單擊常用工具欄中的“粘貼”按鈕（或Ctrl+V） 。 步驟3：對(duì)照效果圖，在“高等數(shù)學(xué)成績(jī)”表中相應(yīng)位置插入列，并輸入相關(guān)內(nèi)容。 步驟4：依次重復(fù)步驟1、步驟2和步驟3，完成另4門課程的成績(jī)表。,任務(wù)完成,任務(wù)4 步驟：重復(fù)任務(wù)3中的步驟1和步驟2，創(chuàng)建“成績(jī)匯總表”。,任務(wù)完成,任務(wù)5 步驟1：選擇“成績(jī)匯總表”（工作表標(biāo)簽），右鍵彈出快捷菜單，選擇“移動(dòng)或復(fù)制工作表”命令，在彈出的“移動(dòng)或復(fù)制工作表”對(duì)話框中選擇位置，本任務(wù)中“移至最后”，同時(shí)選擇“建立副本”，最后單擊“確定”按鈕。 步驟2：右鍵單擊“成績(jī)匯總表（2）”工作表標(biāo)簽，選擇“重命名”命令，將其重命名為“成績(jī)表備份”。,案例描述,二、Excel 數(shù)據(jù)計(jì)算（參考效果圖） 任務(wù)1：在各“*成績(jī)表”中計(jì)算“平時(shí)成績(jī)”、計(jì)算各位同學(xué)的總評(píng)，其計(jì)算規(guī)則為：平時(shí)成績(jī)15%、期中成績(jī)15%、期末成績(jī)70%。并按總評(píng)對(duì)其進(jìn)行單科排名，總分和名次放入所對(duì)應(yīng)的單元格。 任務(wù)2：在每個(gè)課程成績(jī)工作表中，計(jì)算課程各分?jǐn)?shù)段分布人數(shù)及比例、平均分、 標(biāo)準(zhǔn)方差。計(jì)算平均分和標(biāo)準(zhǔn)方差時(shí)，小數(shù)點(diǎn)后保留2位小數(shù)。 任務(wù)3：參考效果圖，從各課程成績(jī)表中引用總評(píng)成績(jī)，并填入“成績(jī)匯總表”中各相應(yīng)單元格。計(jì)算單科平均分、各科總分。 任務(wù)4：參考效果圖，在“成績(jī)匯總表”中，按總分進(jìn)行排名，并利用函數(shù)判斷各科是否超過(guò)單科平均分。,預(yù)備知識(shí),Excel中的計(jì)算公式 公式的使用 運(yùn)算符 單元格引用（絕對(duì)引用、相對(duì)引用、混合引用） 出錯(cuò)信息 函數(shù) 函數(shù)基本知識(shí) 常用函數(shù)（SUM、AVERAGE、MAX/MIN、COUNT） 選擇性粘貼數(shù)據(jù),任務(wù)完成,任務(wù)1 步驟1：選擇“*成績(jī)表”，用AVERAGE公式求得平時(shí)成績(jī)填入H列相應(yīng)單元格，其余同學(xué)的平時(shí)成績(jī)可利用填充柄來(lái)完成計(jì)算。選擇H3單元格，將鼠標(biāo)指針移動(dòng)至該單元格右下角位置，出現(xiàn)黑色十字形時(shí)拖動(dòng)至相應(yīng)需要填充的最后一個(gè)單元格。 步驟2：選中單元格K3，在編輯欄內(nèi)輸入“=ROUND（H3*15%+I3*15%+J3*70%）,2”。按“回車”鍵確認(rèn)，求得總評(píng)成績(jī)（四舍五入，保留整數(shù)），用填充柄向下填充，完成所有學(xué)生總評(píng)成績(jī)的輸入。 步驟3：選中單元格L3，輸入“=RANK(K3,$K$3:$K$32,0)”，回車確認(rèn)，求得的排名依次填入相應(yīng)單元格。,任務(wù)完成,任務(wù)2 步驟1：在每個(gè)課程成績(jī)工作表中，在學(xué)生成績(jī)列表下方建立各成績(jī)段分布人數(shù)及比例平均分和標(biāo)準(zhǔn)方差的相應(yīng)單元格。 步驟2：利用SUMPRODUCT函數(shù)計(jì)算各個(gè)分?jǐn)?shù)段分布人數(shù)，比如“=SUMPRODUCT(K3:K32=90)*(K3:K32=100)”可以計(jì)算90-100分段的人數(shù)。同時(shí)計(jì)算該分?jǐn)?shù)段的人數(shù)分布比例。 步驟3：選定K3:K32，用AVERAGE公式求得該課程的平均分。 步驟3：選定K3:K32，用STDEV公式求得該課程的標(biāo)準(zhǔn)方差。,任務(wù)完成,任務(wù)3 步驟1：選擇“成績(jī)匯總表”，用VLOOKUP公式從各課程成績(jī)表中引用總評(píng)成績(jī)并填入相應(yīng)單元格。比如，在編輯欄中輸入“=VLOOKUP(A3,高等數(shù)學(xué)成績(jī)!$A$3:$K$32,11,0)”，回車確認(rèn)，再利用填充柄完成其余同學(xué)的總評(píng)成績(jī)。 步驟2：重復(fù)步驟1，完成其他四門課程總評(píng)成績(jī)的輸入。 步驟3：選中單元格C33，單擊編輯欄中的“插入函數(shù)”按鈕，在彈出的“插入函數(shù)”對(duì)話框中選擇“AVERAGE”函數(shù)，在打開的AVERAGE“函數(shù)參數(shù)”對(duì)話框中的第一個(gè)參數(shù)“Number1”文本框中輸入C3：C32（也可以通過(guò)選擇區(qū)域完成輸入），單擊“確定”按鈕完成第一位同學(xué)高等數(shù)學(xué)平均分的計(jì)算。 步驟4：其余同學(xué)可利用填充柄來(lái)完成計(jì)算。 步驟5：總分的計(jì)算可參考步驟3和步驟4，將AVERAGE函數(shù)換成SUM函數(shù)，并修改相應(yīng)的數(shù)據(jù)區(qū)域C3:G3即可。,任務(wù)完成,任務(wù)4 步驟1：選中單元格I3，在編輯欄內(nèi)輸入“=RANK(K3,K$3:K$32,0)。按“回車”鍵確認(rèn)，求得按總分的排名，用填充柄向下填充，完成所有學(xué)生的排名。 步驟2：選中單元格J3，在編輯欄內(nèi)輸入=IF(AND(E3=$E$33,F3=$F$33,G3=$G$33,H3=$H$33,I3=$I$33),“是“,“否“)， IF函數(shù)是判斷條件（第一個(gè)參數(shù)），條件為真返回“是”，為假返回“否”。以此求得單科是否超過(guò)平均分。用填充柄向下填充，完成對(duì)所有同學(xué)的判斷。,案例描述,三、工作表格式化 任務(wù)1：設(shè)置“學(xué)生信息”表標(biāo)題格式為隸書、22號(hào)字，并要求合并及居中。 任務(wù)2：將“成績(jī)匯總表”中的“單科平均分”保留兩位小數(shù)，并將各列以“最合適列寬”顯示。 任務(wù)3：將“成績(jī)匯總表”中各科成績(jī)低于60分的學(xué)生成績(jī)用紅色的字體。黃色的底紋顯示。 任務(wù)4：為“學(xué)生信息”表中數(shù)據(jù)所在區(qū)域加田字框：粗線外框，細(xì)線內(nèi)框。,預(yù)備知識(shí),工作表的格式化 數(shù)字的格式化 字體的設(shè)置 設(shè)置列寬與行高 數(shù)據(jù)對(duì)齊方式設(shè)置 設(shè)置工作表的邊框與底紋 自動(dòng)套用格式設(shè)置 條件格式的設(shè)置,任務(wù)完成,任務(wù)1 步驟1：選定“學(xué)生信息”表。 步驟2：選定A1：G1單元格區(qū)域，利用“格式”菜單下的“單元格”命令，或直接右擊選中的單元格區(qū)域，在彈出的快捷菜單中選擇“設(shè)置單元格格式”，彈出“單元格格式”對(duì)話框。 步驟3：在“單元格格式”對(duì)話框中，單擊“字體”選項(xiàng)卡，設(shè)置相應(yīng)的字體為隸書，字號(hào)為22。 步驟4：?jiǎn)螕簟皩?duì)齊”選項(xiàng)卡，水平對(duì)齊方式選擇“居中”，并選中其中的“合并單元格”復(fù)選框，最后單擊“確定”按鈕。,任務(wù)完成,任務(wù)2 步驟1：選定“成績(jī)匯總表”。 步驟2：選定C33：G33單元格區(qū)域，單擊“格式”菜單下的“單元格”命令，或直接右擊選中的單元格區(qū)域，在彈出的快捷菜單中選擇“設(shè)置單元格格式”，彈出“單元格格式”對(duì)話框。 步驟3：在“單元格格式”對(duì)話框中，單擊“數(shù)字”選項(xiàng)卡，在“分類”列表框中選中“數(shù)值”，設(shè)置小數(shù)位數(shù)為2，單擊“確定”按鈕。 步驟4：選定整張“成績(jī)匯總表”中的數(shù)據(jù)區(qū)域，單擊“格式”菜單下的“列”“最合適的列寬”命令，即可完成最合適的列寬的設(shè)置。,任務(wù)完成,任務(wù)3 步驟1：選定“成績(jī)匯總表”。 步驟2：選定C3：G32單元格區(qū)域，單擊“格式”菜單下的“條件格式”命令，彈出“條件格式對(duì)話框”。 步驟3：在“條件格式”對(duì)話框中的“條件1”欄中的3個(gè)組合框中分別選擇或輸入“單元格數(shù)值”、“小于”、“60” 。 步驟4：?jiǎn)螕簟皸l件格式”對(duì)話框中的“格式”按鈕，在彈出的“單元格格式”對(duì)話框的“字體”選項(xiàng)卡中選擇紅色字體，在“圖案”選項(xiàng)卡中選擇黃色底紋，最后單擊“確定”按鈕。,任務(wù)完成,任務(wù)4 步驟1：選定“學(xué)生信息”表。 步驟2：選定數(shù)據(jù)區(qū)域A1：G32，單擊“格式”菜單下的“單元格”命令，或直接右擊“設(shè)置單元格格式”命令，彈出“單元格格式”對(duì)話框。 步驟3：在“單元格格式”對(duì)話框中選擇“邊框”選項(xiàng)卡，在該選項(xiàng)卡中先選擇粗線的線條樣式，再單擊“預(yù)置”欄內(nèi)的“外邊框”按鈕；同樣再選擇細(xì)線的線條樣式，單擊“預(yù)置”欄內(nèi)的“內(nèi)部”按鈕，最后單擊“確定”按鈕。,作業(yè)要求,1、虛擬一些學(xué)生的信息（不少于20人），新建學(xué)生信息工作表，包括：學(xué)號(hào)、姓名、性別、出生年月、年齡、專業(yè)、聯(lián)系方式； 2、新建學(xué)生各個(gè)課程的成績(jī)工作表（課程不少于5門），每個(gè)表包括：平時(shí)成績(jī)（5次）、平時(shí)成績(jī)總評(píng)、期中、期末、期末總評(píng)； 3、輸入數(shù)據(jù)的有效性（成績(jī)采用百分制，符合常規(guī)要求）