條件格式和函數(shù)在學(xué)生成績統(tǒng)計與查詢中的應(yīng)用

黎菁華

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

1 成績表的建立與轉(zhuǎn)化

1.1 導(dǎo)入學(xué)生成績原始數(shù)據(jù)并轉(zhuǎn)化其類型

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

圖1 學(xué)生成績原始數(shù)據(jù)Fig.1 Data of student achievement

1.2 應(yīng)用條件格式對分?jǐn)?shù)進(jìn)行相關(guān)設(shè)置

選取D4:K33區(qū)域，設(shè)置條件格式:≥90的字體為藍(lán)色，＜60的字體為紅色，介于60～90的字體為綠色。如圖2所示:

圖2 條件格式設(shè)置Fig.2 Conditional formatting

2 成績統(tǒng)計與分析

2.1 使用sum()和rank()函數(shù)進(jìn)行總分的統(tǒng)計與排名

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

圖3 Rank函數(shù)參數(shù)設(shè)置Fig.3 Parameters setting of the Rank function

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

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

2.2 應(yīng)用 Countif、Sumif、Max()、Min()、Average()函數(shù)對成績表進(jìn)行統(tǒng)計分析

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

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

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

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

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

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

在C44單元格，輸入 =COUNTIF(D4:D33，“＞=90”)，填充到J44，完成90分以上的分?jǐn)?shù)段人數(shù)統(tǒng)計。

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

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

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

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

圖5 成績統(tǒng)計分析表Fig.5 Analysis of performance statistics

3 成績查詢界面的設(shè)計

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

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

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

語法:

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

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

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

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

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

對單元格P4做同樣的設(shè)置(在P4單元格中，右鍵點擊—設(shè)置單元格格式—自定義—“2010008”00，學(xué)生座號定義后，要查詢某位學(xué)生成績與排名，只需在P4單元格輸入該生學(xué)號的最后兩位數(shù)字即可)，在單元格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即學(xué)號為201000830的學(xué)生，則可立即查到該學(xué)生的姓名、各科成績及排名情況，如圖6所示:

4 結(jié)論

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

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

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