任務助手：EXCEL函數(shù)，包括SUM（求和）、SUMIF(條件求和)、AVERAGE（求平均值）、COUNT（求值）、COUNTIF（條件求值）、MAX（求最大值）、MIN（求最小值）及RANK（求名次）等及以上函數(shù)的嵌套。
心動不如行動，且隨我一起開始具體打造過程——
步驟一：輸入基本信息
打開EXCEL，在表格中輸入基本信息，包括學生姓名、科目、總分、名次、及格率等。設置各科目及總分、均分、最高分、最低分格式為數(shù)值，保留兩位小數(shù)；設置及格人數(shù)格式為數(shù)值，不保留小數(shù)；設置及格率、優(yōu)秀率格式為百分比，保留兩位小數(shù)。為方便圖解，假定學生數(shù)為20人，表格結構如圖一所示。
步驟二：運用公式求各科目成績統(tǒng)計項
1. 求語文這一科目的總分，在B22語文欄中輸入“=SUM(B2:B21)”（不包括引號），回車確認。
2. 在B23欄中輸入“=AVERAGE(B2:B21)”,求出學生語文平均分。
3. 求出語文及格人數(shù)。用到“COUNTIF（條件求值）函數(shù)”，在B24欄中輸入“=COUNTIF(B2:B21,">=60")”，意思是求出從B2到B21單元格區(qū)域中數(shù)值大于等于60分（及格）的人數(shù)。提示：括號中的引號為英文半角狀態(tài)下輸入，下同。
4. 求出語文課的及格率。及格率的計算公式為“及格人數(shù)/參加考試人數(shù)*100%”，根據以上推導，在B25中輸入“=(COUNTIF(B2:B21,">=60")/COUNT(B2:B21))”即可。
5. 計算語文課的優(yōu)秀率。優(yōu)秀率的算法和及格率相似，只需將公式中的“＞＝60”改為“＞＝80”（假定80分為優(yōu)秀）即可。
6. 在B26及B27單元格中分別輸入“=MAX(B2:B21)”及“=MIN(B2:B21)”就可以得出最高分及最低分，非常簡單。
其它科目運用公式和語文相同，只要向右拖動復制語文中的公式即可。各科相關數(shù)據計算完成，現(xiàn)在讓我們進入下一環(huán)節(jié)。
步驟三：計算學生總分及名次
1. 計算學生總分。在J2單元格中輸入“=SUM(B2:I2)”，就可求出學生各科總分。把J2單元格向下拖動復制，使每位學生的總分都顯示出來。由于學生各科成績還沒有輸入，所以現(xiàn)在結果顯示為0。
2. 根據總分，自動計算學生名次。計算學生名次我們常用的辦法是，先給總分排序，然后在名次列中按序輸入序列的方法來完成。但這種方法比較啰嗦，需將學生所有成績輸完以后才可排序，而且如果學生某一科成績改變就須重新排序并輸入序列。能不能用一種方法，能根據學生總分自動為學生排名呢？回答是肯定的，運用RANK函數(shù)可以實現(xiàn)這一功能，該函數(shù)可以返回一個數(shù)值在一組數(shù)值中的排位，數(shù)值的排位是與數(shù)據清單中其他數(shù)據的相對大小。
由于這個函數(shù)我們平常不常用，所以我在這里詳細進行講解：選擇J2單元格，單擊[fx]按鈕，出現(xiàn)“插入函數(shù)”對話框。在類別中找到“全部”，然后找到我們需要的RANK函數(shù)，單擊[確定]按鈕，出現(xiàn)“函數(shù)參數(shù)”對話框，第一個參數(shù)Number為需要找到排位的數(shù)字，在這里我們依總分進行排名，所以在其中輸入“J2”；第二個參數(shù)Ref為包含一組數(shù)字的數(shù)組或引用，在其中輸入“$J$2:$J$21”，為了使排名隨總分改變而改變，我們加上“$”，這樣單元格地址就變成絕對引用了；第三個參數(shù)Order為一數(shù)字，用來指明排位的方式，如果Order為0或省略，則EXCEL按降序排列的數(shù)據清單進行排位，在這里我們輸入0或省略（見圖3）。單擊[確定]按鈕，完成的整個函數(shù)語法形式為“=RANK(J2,$J$2:$J$21)”，向下拖動復制，就可實現(xiàn)排名功能，而且如果我們修改某個學生的成績，顯示結果會自動變化。
步驟四：使各科最高分、最低分及不及格成績一目了然
為了使老師快速掌握各科成績中的最高分、最低分及不及格學生，針對不同層次學生因材施教，我們可以運用不同顏色使這些成績區(qū)別開來。
仍以語文成績?yōu)槔齺碚f明：
1. 選定所有的語文成績單元格區(qū)域，單擊[格式]菜單中的“條件格式”，在彈出的“條件格式”對話框中進行相應設置。
2. 設置最高分的格式。先在“條件1（1）”列表框中選擇“單元格數(shù)值”，接著在“算法”列表框中選擇“等于”，在參數(shù)框中輸入“=MAX($B$2:$B$21)”，然后單擊[格式]按鈕，在“單元格格式”中選擇字體顏色為“綠色”。
3. 設置最低分的格式。單擊“添加”按鈕，擴展“條件格式”對話框，在“條件2（2）”中設置和1中相同，不過要修改“MAX”為“MIN”，并設置顏色為藍色。
4. 設置不及格成績的格式。單擊“添加”按鈕，擴展“條件格式”對話框，在“條件3（3）”的“算法”中選擇“小于”，在“參數(shù)”中輸入“60”，選擇字體為紅色，單擊[確定]按鈕.
5. 再次單擊[確定]按鈕，語文課的最高分自動以綠色凸顯示，最低分自動以藍色顯示，不及格成績就會亮起“紅燈”，結果如圖5所示。
其它科目如法炮制進行。提示：可用格式刷 設置各科格式，然后修改單元格地址即可。
步驟五：單元格保護
至此，我們的表格功能基本實現(xiàn)，但工作還沒完成。為了防止別人對你煞費心機做好的表格無意中破壞，我們還須對單元格進行保護。也就是說，在表格中我們只需讓老師們輸入學生各科成績就行，其它統(tǒng)計工作讓EXCEL自動完成。這樣就要把除學生成績以外的區(qū)域保護起來，禁止別人修改。
1. 在默認狀態(tài)下，工作表的所有單元格都被鎖定，在對工作表進行保護前，必須先解除鎖定。選擇“格式”/“單元格”菜單項，在彈出的“單元格格式”對話框中單擊“保護”選項卡，如圖6所示。取消選擇“鎖定”復選框，如果需要隱藏不想讓他人查看的公式，需選中“隱藏”復選框。單擊[確定]按鈕返回。
2. 選定允許編輯的單元格區(qū)域，也就是此表中的學生成績區(qū)域，然后選擇“工具”/“保護”/“保護工作表”菜單項，打開“保護工作表”對話框（見圖7）。
⑴在“保護工作表”對話框中選定“保護工作表及鎖定的單元格內容”復選框，可以防止對工作表的更改，并且可以防止對鎖定單元格的更改。
⑵在“取消工作表保護時使用的密碼”文本框中輸入密碼，防止其他用戶刪除或更改單元格。
⑶在“允許此工作表的所有用戶進行”列表框中選中“選定未選定的單元格”復選框和其它允許用戶進行的操作。
⑷設置完畢后單擊[確定]按鈕，在彈出的“確認密碼“對話框中重新輸入剛才的密碼進行確認，并單擊[確定]按鈕。
3.如果要撤銷保護工作表，則選擇“工具”/“保護”/“撤銷工作表保護”選項，打開“撤銷工作表保護”對話框，輸入密碼后單擊[確定]按鈕即可。
步驟六：保存為模板，以便隨時調用
單擊“文件”/“保存”菜單，在“另存為”對話框“文件名”中輸入“學生成績統(tǒng)計表”，在“保存類型”中選擇“模板”，回車確定即可。
至此，一個超強的學生成績統(tǒng)計模板打造成功。應用時，打開此模板，在其中輸入學生成績，其余一切交給電腦來完成。如果學生人數(shù)、科目有所改變的話，改變其中部分內容即可使用。雖然打造模板的過程花了一些時間，但“磨刀不誤砍柴工”，細細想來還是很劃算的。
聯(lián)系客服
