基于Excel多功能統計表的設計與實現

李艷 徐運武

摘要：為了解決手工錄入成績費時又易錯位的問題，通過使用VLOOKUP函數將模考成績快速搜索并寫入匯總統計表功能，然后利用lNDEX函數、MATCH函數和MAX三個函數結合實時查找最高分人的姓名，并使用COUNTIF函數實時統計及格人數和及格率等，經實驗測試實現了快速搜索并寫入數據，實時查找和統計功能，最后對模擬實驗后成績提取和生成進行了探討。

關鍵詞：函數;快速;搜索;寫入;實時;統計

中圖分類號：TP399 文獻標識碼：A

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

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

1 設計思路

此統計表主要實現快速搜索并寫入每次模擬成績，統計各小班的及格人數、及格率、安全線上人數、最高分及最高分人姓名等，從而實現快速將所有的模擬實驗成績綜合在一個工作表中，實時反映學生的學習情況。

2 搜索并寫入所有模擬成績后統計表的局部效果

3 功能分析與實現

3.1 功能分析

主要在匯總統計表中完成以下幾個功能：

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

3.2 關鍵技術與功能實現

3.2.1使用VLOOKUP函數快速搜索每次模考成績并寫入匯總統計表

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

公式分析：公式中的$B1I為查找值即準考證號，因為要查找所有人的準考證號即查找值是變量所以使用單元地址，由于要進行公式復制并且準考證號放在匯總統計表B列，所以此處用混合地址;'[scoreOl.xls]Sheetl！$A$2$B$85表示在Excel文件scoreOl.xls的Sheetl工作表的$A$2：$B$85區域中查找，其中$A$2：$B$85區域A列數據必須是準考證號，B列必須是成績，這樣才能保證搜索結果的正確性;找到此準考證號后返回此行第2列的值即成績，FALSE表示精確查找。特別強調：該公式中查找區域必須以查找值為首列;如要進行公式復制，查找值用混合地址，查找區域要用絕對地址。

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

3.2.2 及格人數、及格率等統計實現

實時統計模擬1成績相應及格人數、及格率等實現方法：

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

將這幾個公式進行橫向復制到相應的單元格就可對相應模擬成績進行相關統計。

3.2.3 實現查找最高分人的姓名

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

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

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

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

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

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

3.2.4 使用豐富多彩的顏色和箭頭直觀顯示不同分數段值。

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

3.2.5 匯總統計表的使用方法

使用Excel函數制作完基于多工作溥及工作表的復雜統計表，未寫入任何數據的匯總統計表的局部效果如圖2所示。

以搜索并寫入模擬1成績為例來講解匯總統計表的使用方法：可將匯總統計表與模擬1成績表scoreOl.xls放在同一路徑下，然后打開匯總統計表，選中單元格CIO，雙擊該單元格右下方的填充柄（+）即可將模擬1成績快速寫入匯總統計表并實時完成所要求的統計工作，也可在系統彈出的“打開”對話框中選擇打開相應模擬成績表后完成數據的寫入和相應統計。用同樣的方法可完成其他模擬成績的快速探索寫入和實時統計。

4 探討與研究

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

5 結束語

使用Excel函數制作成績匯總統計表，只要打開該統計表就能迅速完成所有學生成績的搜索和寫入以及統計計算，大大節省了教師的工作時間，使老師能迅速把握學生學習情況和效果，并適時對教學做出相應的調整，從而大大提高教學效率和教學效果;相關函數的應用對高級辦公人員在查詢與統計數據方面起示范作用。

參考文獻：

[1]羅亞玲.計算機應用基礎應用教程（windows7+office2010）[M].北京：清華大學出版社，2014：270-274.

[2]陳永松.計算機應用基礎應用教程[M].廣州：中山出版社，2008：244-248.

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

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

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

[6]李斌.Excel2010應用大全[M].北京：機械工業出版社，2010：180-183.

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

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

【通聯編輯：梁書】

收稿日期：2019-08-01

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

作者簡介：李艷（1978-），女，湖南新邵人，本科，講師，主要研究方向為計算機應用與軟件技術;徐運武，高級實驗師，本科.廣東松山職業技術學院，主要研究方向為電子信息。