條件格式和函數在學生成績統計與查詢中的應用

黎菁華

(中南大學商學院，長沙410083)

1 成績表的建立與轉化

1.1 導入學生成績原始數據并轉化其類型

圖1是2010級工商管理一班(30人)第一學期成績原始數據的一部分，首先利用Excel的函數value()，將原文本型的數字轉化為純數字。如在P4單元格中輸入=value(D4)，其他類推，再把整個數字區域復制到D4:K33，因學號位數較多，同一個班的學生，學號除了最后兩位數不同外，其余數字均相同，所以，可用excel的單元格自定義功能進行設置。選中A4:A33，右擊鼠標，點擊設置單元格格式，選擇單元格格式自定義對話框，在其中輸入“20101008”00，再在A4單元格中輸入1，在A5單元格中輸入2，利用填充柄完成A6:A33單元格，即刻顯示完整的學號，如圖1所示:

圖1 學生成績原始數據Fig.1 Data of student achievement

1.2 應用條件格式對分數進行相關設置

選取D4:K33區域，設置條件格式:≥90的字體為藍色，＜60的字體為紅色，介于60～90的字體為綠色。如圖2所示:

圖2 條件格式設置Fig.2 Conditional formatting

2 成績統計與分析

2.1 使用sum()和rank()函數進行總分的統計與排名

在L4單元格輸入公式“=sum(D4:K4)”，并填充至L33，完成“總分”的計算。選中M4單元格，單擊編輯欄上“fx”，選取rank函數，在函數參數對話框中輸入number為“L4”，ref為“$L$4:$L$33”(使用絕對地址)。將 M4自動填充至M33，完成“排名”列的計算。Rank函數參數圖形界面如圖3所示:

圖3 Rank函數參數設置Fig.3 Parameters setting of the Rank function

使用條件格式和sum()、rank()函數后的樣式如圖4所示:

圖4 使用條件格式和sum()、rank()函數后的效果圖Fig.4 Effect of using conditional formatting and sum()，rank()function of the renderings

2.2 應用 Countif、Sumif、Max()、Min()、Average()函數對成績表進行統計分析

應用countif函數計算男生人數，利用sumif函數和己經求得的男生人數，可計算男生總分的平均分。同樣的方法，可以計算女生人數和女生的平均分。使用countif函數還可完成各分數段人數的統計。利用max函數統計各科(或總分)最高分，利用min函數統計各科(或總分)最低分，應用Average函數統計平均分，聯合運用sumif和countif函數可完成各科男生平均分和女生平均分的統計。具體步驟如下:

在C39單元格，輸入=Max(D4:D33)，填充到 J39，完成全班各科最高分的統計。

在C40單元格，輸入=Min(D4:D33)，填充到J40，完成全班各科最低分的統計。

在C41單元格，輸入=Average(D4:D33)，再填充到J41，完成各科全班平均分的統計。

在C42單元格，輸入 =SUMIF(C4:C33，“男”，D4:D33)/COUNTIF(C4:C33，“男”)，填充到 J42，完成各科男生平均分的統計。

在C43單元格，輸入 =SUMIF(C4:C33，“女”，D4:D33)/COUNTIF(C4:C33，“女”)，填充到 J43，完成各科女生平均分的統計。

在C44單元格，輸入 =COUNTIF(D4:D33，“＞=90”)，填充到J44，完成90分以上的分數段人數統計。

在C45單元格，輸入 =COUNTIF(D4:D33，“＞=80”)－COUNTIF(D4:D33，“＞ =90”)，填充到 J45，完成各科在80～90分(不含90)的人數統計。

在C46單元格，輸入 =COUNTIF(D4:D33，“＞=70”)－COUNTIF(D4:D33，“＞ =80”)，填充到 J46，完成各科在70～80分(不含90)的人數統計。

在C47單元格，輸入 =COUNTIF(D4:D33，“＞=60”)－COUNTIF(D4:D33，“＞ =70”)，填充到 J47，完成各科在60～70分(不含90)的人數統計。

在 C48 單元格，輸入 =COUNTIF(D4:D33，“＜60”)，填充到J48，完成各科60分以下人數統計。成績分布如圖5所示:

圖5 成績統計分析表Fig.5 Analysis of performance statistics

3 成績查詢界面的設計

運用VLOOKUP()函數制作成績查詢界面。

VLOOKUP()函數的功能是在表格或數值數組的首列查找指定的數值，并由此返回表格或數組中該數值所在行中指定列處的數值［1］。

這里所說的“數組”，可以理解為表格中的一個區域。數組的列序號:數組的“首列”，就是這個區域的第一縱列，此列右邊依次為第2列、第3列……假定某數組區域為A4:M33，那么 A4:A33為第1列、B4:B33為第2列……

語法:

VLOOKUP(查找值，區域，列序號，邏輯值)

“查找值”:為需要在數組第一列中查找的數值，它可以是數值、引用或文字符串。“區域”:數組所在的區域，如“A4:M33”，也可以使用對區域或區域名稱的引用，例如數據庫或數據清單。

“列序號”:即希望區域(數組)中待返回的匹配值的列序號，為1時，返回第一列中的數值。為2時，返回第二列中的數值，以此類推;若列序號小于1，函數VLOOKUP返回錯誤值 #VALUE!;如果大于區域的列數，函數VLOOKUP返回錯誤值 #REF!。

“邏輯值”:為TRUE或FALSE。它指明函數 VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找值”的最大數值;如果“邏輯值”為FALSE，函數VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。如果“查找值”為文本時，“邏輯值”一般應為FALSE。另外如果“查找值”小于“區域”第一列中的最小數值，函數 VLOOKUP返回錯誤值 #N/A。如果函數 VLOOKUP找不到“查找值”且“邏輯值”為FALSE，函數 VLOOKUP返回錯誤值 #N/A。

成績查詢功能在于輸入任意學生的座號，即可查找出該學生的姓名、各科成績、總分以及班級排名等詳細信息。

對單元格P4做同樣的設置(在P4單元格中，右鍵點擊—設置單元格格式—自定義—“2010008”00，學生座號定義后，要查詢某位學生成績與排名，只需在P4單元格輸入該生學號的最后兩位數字即可)，在單元格P6中輸入=VLOOKUP($P$4，$A$4:$M$33，2)，在單元格 P7 中輸入 =VLOOKUP($P$4，$A$4:$M$33，4)，在 P8 單元格中輸入 =VLOOKUP($P$4，$A$4:$M$33，5)，在單元格P9中輸入=VLOOKUP($P$4，$A$4:$M$33，6)，在單元格P10中輸入 =VLOOKUP($P$4，$A$4:$M$33，7)，在單元格P11中輸入 =VLOOKUP($P$4，$A$4:$M$33，8)，在單元格 P11 中輸入 =VLOOKUP($P$4，$A$4:$M$33，8)，在單元格 P12中輸入 =VLOOKUP($P$4，$A$4:$M$33，9)，在單元格P13中輸入=VLOOKUP($P$4，$A$4:$M$33，10)，在單元格P14中輸入=VLOOKUP($P$4，$A$4:$M$33，11)，在單元格 P15中輸入 =VLOOKUP($P$4，$A$4:$M$33，12)，在單元格 P16 中輸入 =VLOOKUP($P$4，$A$4:$M$33，13)，如果此時在P4單元格中輸入30即學號為201000830的學生，則可立即查到該學生的姓名、各科成績及排名情況，如圖6所示:

4 結論

Excel函數具有強大的數據處理能力，如果能善加利用Excel的條件格式和函數參數，則會給我們的工作帶來極大便利，達到事半功倍之效，利用Excel的條件格式和函數參數進行成績統計、查詢與獎學金的評定，操作簡便、準確可靠、易于修改，高效可行。

圖6 成績查詢界面Fig.6 Interface of query results

［1］杜茂康.Excel與數據處理(第2版)［M］.北京:電子工業出版社，2006.