李艷 徐運(yùn)武

摘要：為了解決手工錄入成績費(fèi)時又易錯位的問題，通過使用VLOOKUP函數(shù)將模考成績快速搜索并寫入?yún)R總統(tǒng)計(jì)表功能，然后利用lNDEX函數(shù)、MATCH函數(shù)和MAX三個函數(shù)結(jié)合實(shí)時查找最高分人的姓名，并使用COUNTIF函數(shù)實(shí)時統(tǒng)計(jì)及格人數(shù)和及格率等，經(jīng)實(shí)驗(yàn)測試實(shí)現(xiàn)了快速搜索并寫入數(shù)據(jù)，實(shí)時查找和統(tǒng)計(jì)功能，最后對模擬實(shí)驗(yàn)后成績提取和生成進(jìn)行了探討。

關(guān)鍵詞：函數(shù);快速;搜索;寫入;實(shí)時;統(tǒng)計(jì)

中圖分類號：TP399 文獻(xiàn)標(biāo)識碼：A

文章編號：1009-3044（2019）34-0204-02

隨著計(jì)算機(jī)和網(wǎng)絡(luò)技術(shù)的快速發(fā)展，高等院校的學(xué)生不僅要有過硬的專業(yè)技能，還要有解決實(shí)際問題的能力。通過對本校往屆畢業(yè)生回訪，發(fā)現(xiàn)《計(jì)算機(jī)應(yīng)用基礎(chǔ)》是學(xué)生工作中最急需、最實(shí)用的一門課程，因而廣東松山職業(yè)技術(shù)學(xué)院把這門課程作為非計(jì)算機(jī)專業(yè)一級統(tǒng)考課程，并作為鍛煉學(xué)生的實(shí)際操作技能和創(chuàng)新開發(fā)能力重要課程之一。該校學(xué)生在往年的廣東省一級統(tǒng)考中一直名列前茅，最關(guān)鍵是因?yàn)樵撔＝處熥⒅嘏囵B(yǎng)學(xué)生的上機(jī)實(shí)踐操作能力，并將模擬實(shí)驗(yàn)課作為重要突破環(huán)節(jié)。為了激勵學(xué)生不斷進(jìn)步取得更好的成績，該課程任課教師會制作一個匯總統(tǒng)計(jì)表來統(tǒng)計(jì)學(xué)生每次模擬實(shí)驗(yàn)成績，幾乎都是手動將成績復(fù)制粘貼到統(tǒng)計(jì)表，這樣即費(fèi)時又會出現(xiàn)成績錯位情況，為了解決以上問題使用VLOOKUP函數(shù)快速搜索外來多個工作簿中的數(shù)據(jù)并寫入統(tǒng)計(jì)表，使用INDEX函數(shù)、MATCH函數(shù)和MAX三個函數(shù)結(jié)合實(shí)時查找每次模擬成績中最高分人的姓名，使用COUNTIF函數(shù)統(tǒng)計(jì)及格人數(shù)，將COUNTIF與COUNTA結(jié)合統(tǒng)計(jì)及格率。

1 設(shè)計(jì)思路

此統(tǒng)計(jì)表主要實(shí)現(xiàn)快速搜索并寫入每次模擬成績，統(tǒng)計(jì)各小班的及格人數(shù)、及格率、安全線上人數(shù)、最高分及最高分人姓名等，從而實(shí)現(xiàn)快速將所有的模擬實(shí)驗(yàn)成績綜合在一個工作表中，實(shí)時反映學(xué)生的學(xué)習(xí)情況。

2 搜索并寫入所有模擬成績后統(tǒng)計(jì)表的局部效果

3 功能分析與實(shí)現(xiàn)

3.1 功能分析

主要在匯總統(tǒng)計(jì)表中完成以下幾個功能：

①快速搜索交卷后生成的成績表中的準(zhǔn)考證號并將對應(yīng)的成績寫入?yún)R總統(tǒng)計(jì)表中;②實(shí)時統(tǒng)計(jì)各小班及格人數(shù)、大于等于75分人數(shù);③實(shí)時統(tǒng)計(jì)各小班及格率，合班及格率;④實(shí)時統(tǒng)計(jì)每次模擬最高分、查找最高分人的姓名;⑤使用豐富多彩的顏色和箭頭直觀顯示不同分?jǐn)?shù)段值。

3.2 關(guān)鍵技術(shù)與功能實(shí)現(xiàn)

3.2.1使用VLOOKUP函數(shù)快速搜索每次?？汲煽儾懭?yún)R總統(tǒng)計(jì)表

函數(shù)格式簡寫：= VLOOKUP（查找值，查找區(qū)域，返回值在查找區(qū)域中的列序號（第幾列），TRUE或FALSE）[1]快速搜索并寫入模擬l成績的實(shí)現(xiàn)方法：第一步：在匯總統(tǒng)計(jì)表CIO單元格中輸入公式：=VLOOKUP（$BIO，[scoreOl.xls]Sheetl， ！ $A$2： $B$85，2，F(xiàn)ALSE）并按Enter鍵;第二步：將公式復(fù)制到C11： C85。

公式分析：公式中的$B1I為查找值即準(zhǔn)考證號，因?yàn)橐檎宜腥说臏?zhǔn)考證號即查找值是變量所以使用單元地址，由于要進(jìn)行公式復(fù)制并且準(zhǔn)考證號放在匯總統(tǒng)計(jì)表B列，所以此處用混合地址;'[scoreOl.xls]Sheetl！$A$2$B$85表示在Excel文件scoreOl.xls的Sheetl工作表的$A$2：$B$85區(qū)域中查找，其中$A$2：$B$85區(qū)域A列數(shù)據(jù)必須是準(zhǔn)考證號，B列必須是成績，這樣才能保證搜索結(jié)果的正確性;找到此準(zhǔn)考證號后返回此行第2列的值即成績，F(xiàn)ALSE表示精確查找。特別強(qiáng)調(diào)：該公式中查找區(qū)域必須以查找值為首列;如要進(jìn)行公式復(fù)制，查找值用混合地址，查找區(qū)域要用絕對地址。

將CIO中的公式橫向復(fù)制到D10中，然后將D10公式中的scoreOl.xls改為對應(yīng)的存放模擬2成績的Excel文件名，再將修改后的公式復(fù)制到D11： D85即可搜索并寫入模擬2成績，以此類推即可完成其他模擬成績的搜索和寫入。

3.2.2 及格人數(shù)、及格率等統(tǒng)計(jì)實(shí)現(xiàn)

實(shí)時統(tǒng)計(jì)模擬1成績相應(yīng)及格人數(shù)、及格率等實(shí)現(xiàn)方法：

在C1中輸入公式：=COUNTIF（CIO：C45，”>=60”）并按Enter鍵，統(tǒng)計(jì)l班及格人數(shù);在C2中輸入公式：=COUNTIF（CIO：C45，”>=60”）/COUNTA（$A$10：$A$45）并按Enter鍵，統(tǒng)計(jì)1班及格率，其中$A$10：$A$45為1班所有人的姓名;在C3中輸入公式：=COUNTIF（C46：C85.”>=60”）并按Enter鍵，統(tǒng)計(jì)2班及格人數(shù);在C4中輸入公式：=COUNTIF（C46：C85.”>=60”）/COUNTA（$A$46：$A$85）并按Enter鍵，統(tǒng)計(jì)2班及格率;在C5中輸入公式：=COUNTIF（CIO： C85，”>=60”）/COUNTA（$A$10：$A$85）并按Enter鍵，統(tǒng)計(jì)合班及格率;在C6中輸入公式：=COUNTIF（CIO：C85，”>=75”）并按Enter鍵，統(tǒng)計(jì)高于或等于75分人數(shù);在C7中輸入公式：=MAX（C10：C85）并按Enter鍵，求最高分。

將這幾個公式進(jìn)行橫向復(fù)制到相應(yīng)的單元格就可對相應(yīng)模擬成績進(jìn)行相關(guān)統(tǒng)計(jì)。

3.2.3 實(shí)現(xiàn)查找最高分人的姓名

將INDEX函數(shù)、MATCH函數(shù)和MAX三個函數(shù)結(jié)合應(yīng)用可實(shí)現(xiàn)查找最高分人的姓名。MATCH函數(shù)功能是返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相對位置。INDEX函數(shù)功能是返回列表或數(shù)組中的元素值，此元素由行序號和列序號的索引值進(jìn)行確定。

INDEX函數(shù)格式簡寫：=INDEX（單元格區(qū)域或數(shù)組常量，行序號，列序號）。

在C8中輸入公式：=INDEX（$A$10：$A$85，MATCH（MAX（CIO：C85），CIO：C85，0》后按回車鍵即可實(shí)現(xiàn)查找模擬1成績最高分人的姓名。

公式分析：公式中的MAX（CIO：C85）用來統(tǒng)計(jì)區(qū)域CIO：C85中的最大值;MATCH函數(shù)用于在區(qū)域CIO：C85中查找MAX函數(shù)的結(jié)果，它的返回值是最高分在區(qū)域CIO：C85中的相對位置：71，然后再使用該返回值作為INDEX函數(shù)的行序號參數(shù)。因此本例中的INDEX函數(shù)就成了如下的形式：=INDEX（$A$lO：$A$85，71），此公式返回區(qū)域AIO：A85中的第71行中的值。特別強(qiáng)調(diào)：此處的行序號參數(shù)是相對于所引用的單元格區(qū)域而言，并不是Excel工作表中的行序號。

將C8中的公式橫向復(fù)制到D8：L8區(qū)域中即可查找其他模擬成績中最高分人的姓名。

也可使用VLOOKUP函數(shù)來實(shí)現(xiàn)查找最高分人的姓名，但使用該函數(shù)必須在數(shù)據(jù)清單右邊再復(fù)制粘貼姓名列，否則無法通過復(fù)制公式來得到正確結(jié)果，并且還要對復(fù)制粘貼后的公式進(jìn)行修改，操作太煩瑣所以不予采用。

3.2.4 使用豐富多彩的顏色和箭頭直觀顯示不同分?jǐn)?shù)段值。

如果統(tǒng)計(jì)表數(shù)據(jù)量大，都采用同一種顏色顯示會加重視覺疲勞，并且不能直觀區(qū)分不同分?jǐn)?shù)段的值。適當(dāng)?shù)膽?yīng)用條件格式中的“突出顯示單元格規(guī)則”功能設(shè)置多種字體、填充顏色能更直觀地顯示不同分?jǐn)?shù)段值;使用條件格式中“圖標(biāo)集”中的“五向箭頭（彩色）”功能來設(shè)置不同顏色不同方向的箭頭標(biāo)注每個單元格中的數(shù)據(jù)，能直觀顯示數(shù)據(jù)的變化情況：低于60分的用紅色斜體顯示;低于75分的用綠色字體顯示，并在數(shù)據(jù)左側(cè)添加由左下向右上的斜體金黃色箭頭，高于（包含）75分并且低于79分的數(shù)據(jù)用綠色字體顯示，并在數(shù)據(jù)左側(cè)添加由左下向右上的斜體金黃色箭頭;高于（包含）80分并的數(shù)據(jù)用綠色字體顯示，并在數(shù)據(jù)左側(cè)添加向上的綠色箭頭;為方便瀏覽每條記錄的所有數(shù)據(jù)可選中CIO單元格后進(jìn)行窗口凍結(jié)。

3.2.5 匯總統(tǒng)計(jì)表的使用方法

使用Excel函數(shù)制作完基于多工作溥及工作表的復(fù)雜統(tǒng)計(jì)表，未寫入任何數(shù)據(jù)的匯總統(tǒng)計(jì)表的局部效果如圖2所示。

以搜索并寫入模擬1成績?yōu)槔齺碇v解匯總統(tǒng)計(jì)表的使用方法：可將匯總統(tǒng)計(jì)表與模擬1成績表scoreOl.xls放在同一路徑下，然后打開匯總統(tǒng)計(jì)表，選中單元格CIO，雙擊該單元格右下方的填充柄（+）即可將模擬1成績快速寫入?yún)R總統(tǒng)計(jì)表并實(shí)時完成所要求的統(tǒng)計(jì)工作，也可在系統(tǒng)彈出的“打開”對話框中選擇打開相應(yīng)模擬成績表后完成數(shù)據(jù)的寫入和相應(yīng)統(tǒng)計(jì)。用同樣的方法可完成其他模擬成績的快速探索寫入和實(shí)時統(tǒng)計(jì)。

4 探討與研究

每個學(xué)生交卷之后會在服務(wù)器自動生成以學(xué)生準(zhǔn)考證號為文件名的文件夾，在該文件夾中有一個以此準(zhǔn)考證號為文件名的文本文件，該文本文件中的三位整數(shù)就是學(xué)生成績。下一步準(zhǔn)備研究設(shè)計(jì)一個應(yīng)用程序，實(shí)現(xiàn)以下功能：第一步：搜索以準(zhǔn)考證號為文件名的文件夾，并提取該文件夾名寫入新生成的一個Excel文件中且按從小到大順序排列;第二步：打開文件夾中以準(zhǔn)考證號為文件名的文本文件，提取該文件中的三位整數(shù)并除以10得到學(xué)生成績，并將該成績寫入到此Excel文件對應(yīng)的準(zhǔn)考號證號后，從而完成每次模擬實(shí)驗(yàn)所有學(xué)生的成績提取。

5 結(jié)束語

使用Excel函數(shù)制作成績匯總統(tǒng)計(jì)表，只要打開該統(tǒng)計(jì)表就能迅速完成所有學(xué)生成績的搜索和寫入以及統(tǒng)計(jì)計(jì)算，大大節(jié)省了教師的工作時間，使老師能迅速把握學(xué)生學(xué)習(xí)情況和效果，并適時對教學(xué)做出相應(yīng)的調(diào)整，從而大大提高教學(xué)效率和教學(xué)效果;相關(guān)函數(shù)的應(yīng)用對高級辦公人員在查詢與統(tǒng)計(jì)數(shù)據(jù)方面起示范作用。

參考文獻(xiàn)：

[1]羅亞玲.計(jì)算機(jī)應(yīng)用基礎(chǔ)應(yīng)用教程（windows7+office2010）[M].北京：清華大學(xué)出版社，2014：270-274.

[2]陳永松.計(jì)算機(jī)應(yīng)用基礎(chǔ)應(yīng)用教程[M].廣州：中山出版社，2008：244-248.

[3]（美）考特（Courter，G.），（美）馬奎斯（Marquis，A）Exce12002從入門到精通[M].魏江力，等，譯.北京：電子工業(yè)出版社，2002：170.

[4]（美）麥克菲德里斯（McFedries，P）.巧學(xué)巧用Excel2003公式與函數(shù)[M].馬樹奇，金燕譯.北京：電子工業(yè)出版社，2005：310-314.

[5]龍馬工作室.Excel 2010中文版完全自學(xué)手冊[M].北京：人民郵電出版社，2011：210-212.

[6]李斌.Excel2010應(yīng)用大全[M].北京：機(jī)械工業(yè)出版社，2010：180-183.

[7]華誠科技.Office 2010從入門到精通[Ml.北京：機(jī)械工業(yè)出版社，2011：201-202.

[8]龍馬工作室.Office 2010辦公應(yīng)用從新手到高手[M].北京：人民郵電出版社，2011：295-298.

【通聯(lián)編輯：梁書】

收稿日期：2019-08-01

基金項(xiàng)目：廣東省教育廳資助項(xiàng)目（粵教高函[2012]54號- A12）

作者簡介：李艷（1978-），女，湖南新邵人，本科，講師，主要研究方向?yàn)橛?jì)算機(jī)應(yīng)用與軟件技術(shù);徐運(yùn)武，高級實(shí)驗(yàn)師，本科.廣東松山職業(yè)技術(shù)學(xué)院，主要研究方向?yàn)殡娮有畔ⅰ?/p>