|
每到學(xué)期結(jié)束時(shí)，教師的一項(xiàng)重要工作就是要統(tǒng)計(jì)學(xué)生的成績，在電腦逐步普及的今天，我們就把這項(xiàng)繁雜的工作交給Excel去自動(dòng)完成吧。 本節(jié)任務(wù)：制作一個(gè)學(xué)生成績自動(dòng)統(tǒng)計(jì)表，可以自動(dòng)統(tǒng)計(jì)最高分、最低分、總分、平均分、名次、三率等數(shù)據(jù)信息，還可以根據(jù)自定條件以不同的顏色顯示分?jǐn)?shù)。自動(dòng)統(tǒng)計(jì)表做好以后還可以保存成模板，以便以后使用。 涉及術(shù)語：單元格、工作表、工作薄、引用(相對/絕對)、自動(dòng)填充、排序、條件格式等。 涉及函數(shù)：AVERAGE、COUNTIF 、MAX、MIN、RANK、SUM 任務(wù)一：統(tǒng)計(jì)最高分、最低分、總分、平均分、名次、三率等數(shù)據(jù)信息。 1、啟動(dòng)Excel，同時(shí)選中A1至L1單元格，按“格式”工具條上的“合并及居中”按鈕，將其合并成一個(gè)單元格，然后輸入統(tǒng)計(jì)表的標(biāo)題“高一（1）班期末成績統(tǒng)計(jì)表”（參見圖1）。 [img,553,353]file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/ksohtml/wps_clip_image1.png 2、根據(jù)統(tǒng)計(jì)表的格式，將有關(guān)列標(biāo)題及相關(guān)內(nèi)容輸入到相應(yīng)的單元格中（參見圖1）。 提示：其中學(xué)號(hào)的輸入可通過“填充柄”快速完成。 3、選中K3單元格，輸入公式：=SUM(C3:J3)，用于計(jì)算第一位學(xué)生的總分。 4、選中L3單元格，輸入公式：=RANK(K3,$K$3:$K$12)，計(jì)算出第一位學(xué)生總分成績的名次（此處，假定共有10位學(xué)生）。 5、同時(shí)選中K3和L3單元格，將鼠標(biāo)移至L3單元格右下角的成“細(xì)十字”狀時(shí)（通常稱這種狀態(tài)為“填充柄”狀態(tài)），按住左鍵向下拖拉至L12單元格，完成其他學(xué)生的總分及名次的統(tǒng)計(jì)處理工作。 6、分別選中C16、C17單元格，輸入公式：=MAX(C3:C12)和=MIN(C3:C12)，用于統(tǒng)計(jì)“語文”學(xué)科的最高分和最低分。 7、選中C18單元格，輸入公式：=AVERAGE(C3:C12)，用于統(tǒng)計(jì)“語文”學(xué)科的平均分。 注意：如果成績表中沒有輸入成績時(shí)，這一公式將顯示出一個(gè)錯(cuò)誤的值“#DIV/0!”,這個(gè)錯(cuò)誤代碼將在數(shù)據(jù)輸入后消失。 8、選中C19單元格，輸入公式：=SUM(C3:C12)，用于統(tǒng)計(jì)“語文”學(xué)科的總分。 9、選中C20單元格，輸入公式：=COUNTIF(C3:C12,">=80")/COUNTIF(C3:C12,">0")，用于統(tǒng)計(jì)“語文”學(xué)科的優(yōu)秀率。同樣在C21內(nèi)輸入相應(yīng)公式統(tǒng)計(jì)良好率。 10、同時(shí)選中C16至C21單元格，用“填充柄”將上述公式復(fù)制到D16至J21單元格中，完成其它學(xué)科及總分的最高分、最低分、平均分、總分、優(yōu)秀率和良好率的統(tǒng)計(jì)工作。 至此，一個(gè)基本的成績統(tǒng)計(jì)表制作完成，下面我們來進(jìn)一步處理一下。 任務(wù)二：根據(jù)自定條件以不同的顏色顯示分?jǐn)?shù)。 （在此例中，讓每科分?jǐn)?shù)高于等于平均分的分?jǐn)?shù)顯示藍(lán)色，低于的則顯示紅色） 11、選中C3單元格，執(zhí)行“格式、條件格式”命令，打開“條件格式”對話框（如圖2），在中間方框選中“大于或等于”，在右側(cè)的方框中輸入公式：=C18 (平均分所在單元格)，然后按“格式”按鈕，打開“單元格格式”對話框，將字體顏色設(shè)置為“藍(lán)色”。再按“添加”按鈕，仿照上面的操作，設(shè)置小于平均分的分?jǐn)?shù)字體顏色為“紅色”（參見圖2）。 注意：經(jīng)過這樣的設(shè)置后，當(dāng)學(xué)生的“語文”成績大于或等于平均分時(shí)，顯示藍(lán)色，反之顯示紅色。[img,539,240]file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/ksohtml/wps_clip_image2.png 12、再次選中C3單元格，按“格式”工具條上的“格式刷”按鈕，然后在C3至J12單元格區(qū)域上拖拉一遍，將上述條件格式復(fù)制到相應(yīng)的區(qū)域中，完成其他學(xué)科及總分的條件格式設(shè)置工作。 把學(xué)生的成績填入到表格中試試看，效果不錯(cuò)吧。 任務(wù)二：將制作完成的統(tǒng)計(jì)表保存為模板。 如果你經(jīng)常要統(tǒng)計(jì)學(xué)生的成績，我們將其保存為模板，方便隨時(shí)調(diào)用。 13、將工作表中的學(xué)生成績等內(nèi)容刪除，執(zhí)行“文件、保存（另存為）”命令，打開“另存為”對話框（如圖4），按“保存類型”右側(cè)的下拉按鈕，在隨后出現(xiàn)的下拉列表中，選“模板（*.xlt）”選項(xiàng)，然后給定一個(gè)名稱（如“成績統(tǒng)計(jì)”），按下保存按鈕。 [img,559,340]file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/ksohtml/wps_clip_image3.jpg 14、以后需要統(tǒng)計(jì)成績時(shí)，啟動(dòng)Excel，執(zhí)行“文件、新建”命令，展開“新建工作簿”任務(wù)窗格（如圖5），點(diǎn)擊其中的“本機(jī)上的模板”選項(xiàng)，打開“模板”對話框，雙擊“成績統(tǒng)計(jì)”模板，即可新建一份新的成績統(tǒng)計(jì)表。 [img,209,235]file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/ksohtml/wps_clip_image4.jpg 15、將學(xué)生的成績填入相應(yīng)的單元格中，取名保存即可快速完成成績統(tǒng)計(jì)處理工作。 在Excel中用宏實(shí)現(xiàn)自動(dòng)生成簡單的成績條 Excel具有強(qiáng)大的數(shù)據(jù)處理和打印輸出功能，并且易學(xué)易用，是廣大用戶喜歡使用的電子表格處理軟件。現(xiàn)在一些學(xué)校人員喜歡用Excel打印本學(xué)校的學(xué)生成績條，但在Excel中要將成績總表(手工地轉(zhuǎn)換為成績條則是一件比較煩瑣的事，下面是我編寫的一個(gè)Excel宏，運(yùn)行這個(gè)宏就可將編輯好了的成績總表很方便地轉(zhuǎn)換為成績條打印輸出。 在Excel中新建一個(gè)文件，將其命名為“成績條”，在工作表“sheet1”中輸入并編輯成績表,成績共30條，如圖： [img,552,414]file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/ksohtml/wps_clip_image5.png 然后，點(diǎn)擊“工具”菜單→“宏”→“宏…”→輸入宏名“編輯成績生成成績條”→創(chuàng)建，輸入如下的宏的各行文本，輸入完成后保存該宏： Sub Macro1() End Sub 最后執(zhí)行宏，成績條如下： [img,552,414]file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/ksohtml/wps_clip_image6.png 以后每月要打印成績條時(shí)，只需將“30”改成總成績表的記錄數(shù)就可以了。 解決問題的思路 因?yàn)槊總€(gè)學(xué)生只有一個(gè)學(xué)號(hào)，所以學(xué)號(hào)是惟一的，根據(jù)學(xué)號(hào)惟一性這一特點(diǎn)，使用VBA里的判斷語句，如果學(xué)號(hào)不同，就在兩者之間插入一個(gè)空白行，然后再在每個(gè)空白行粘貼復(fù)制的表頭，最后使用循環(huán)語句，自動(dòng)制作每個(gè)學(xué)生成績記錄的表頭。 解決問題的方法 打開學(xué)生的成績表，我們需要另存為另外一個(gè)表來制作成績條，以免影響成績表的原貌。 在VBA的工程資源管理器中雙擊Sheet1，然后出現(xiàn)代碼窗口，在代碼窗口輸入如下代碼： Sub cjt() Application.ScreenUpdating = False Sheets(1).[A1].CurrentRegion.Copy Sheets(2).[A1] '將表一的成績表復(fù)制到表二 a=(Application.WorksheetFunction.CountA(Sheets(2).[b2:b2000]))*2 'sheets(1).[b2:b2000]的字符數(shù)的2倍 Sheets(2).[A1:R1].Borders(xlEdgeTop).LineStyle = xlDouble 'sheets(2).[a1:r1]的下邊框是雙線 For i = 2 To a If Sheets(2).Cells(i, 3) <> Sheets(2).Cells(i + 1, 3) And (Sheets(2).Cells(i, 3) <> "") Then Sheets(2).Rows(i + 1).Insert End If '如果第三列的上下單元格的值不相等，則在它們之間插入一個(gè)空白行 If Sheets(2).Cells(i, 3) = "" Then Sheets(2).[A1:R1].Copy Sheets(2).Cells(i, 1) End If '如果第三列中的單元格是空的，則將Sheets(2).[A1:R1]復(fù)制到此行 Next Application.ScreenUpdating = True End Sub |
|
|
