淺談考務管理系統中Excel函數嵌套

楊旭東

（天水市秦州區回民小學，甘肅 天水 741000）

在平時教學工作中，經常會用到Excel軟件記錄、計算和統計數據，許多時候僅使用了Excel的簡單功能，如求和、求平均值、計數等。據相關統計，80%使用Office Excel的人其實只是用了它不到20%的功能。實際上，Excel數據處理功能很強大，靈活應用其函數功能，可以完成復雜統計，甚至以它為平臺開發小型數據庫。本文以應用Excel內置功能制作考務管理系統為例，討論如何應用函數嵌套，實現學生成績自動化統計分析。考務管理系統數據流向見圖1。

圖1 考務管理系統數據流向圖

數據處理中為了保證數據的準確，使用全國中小學學籍管理系統中的學生基礎數據就成為必然。首先，這些數據經過校對和學期初學生變動情況的更新，可以提供最新的、準確的學生基礎信息。其次，不用再另起爐灶，減少數據維護的工作量，只需要設計好“班內學號”列的數據構成方式。“班內學號”應包括入學時間、班別、序號三個信息，如班內學號“2009215”，2009表示入學的年份，中間的2表示2班，最后兩個數字15，表示第15個序號。這種設計有幾項用處，一是班內學號是數字，在數據錄入中，從本班第1位到最末位的班內學號，都可以使用數據序列填充的方式完成，減少操作量，避免錯誤，提高工作效率。二是通過班內學號升序排列，為整個數據建立索引，方便數據的引入和查詢，數據維護簡單方便。三是學號本身包含信息，可以根據需要調取，完成一些功能設計。四是開放性的設計思想為日后增加信息量預留接口且不影響其原有功能。如學校規模大，每個年級的班級數達到兩位數，就把表示班別的數字定義為兩位數。如要在班內學號中需要增加學生性別信息，只需在數字中增加一個數位，用0和1分別代表男女生便可實現。

“成績冊”工作表，用來記錄每名學生的各門功課的考試成績，進行初步的統計工作，提供各班成績單報表。在實際應用中，學生姓名通過函數命令“=IF(COUNTIF(學生基礎信息!$AN:$AN,A2)=0,"",LOOKUP(A2,學生基礎信息!$AN:$AN,學生基礎信息!$B:$B))”的方法引用。先用IF函數執行真假值判斷，表達式COUNTIF(學生基礎信息!$AN:$AN,A2)返回值如果為0，表示當前行中班內學號值在學生基礎信息表中不存在，結果將返回空值，否則返回表達式LOOKUP(A2,學生基礎信息!$AN:$AN,學生基礎信息!$B:$B)的值，即使用LOOKUP函數的向量形式，在學生基礎信息AN列中查找A2（當前行中的班內學號）數值，然后返回學生基礎信息B列（姓名）中相同位置的數值，從而得到學生姓名。學生的性別信息處理方法和姓名信息處理方法類似，區別是IF返回表達式指定C列 （性別）數據便可。由于班內學號的數據構成已經包涵了學生所在班級的信息，因此“班級”就可以通過班內學號自動計算出來，方法是“=IF(COUNTIF(學生基礎信息!$AN$2:$AN$1000,A2)=0,"",IF(MONTH(TODAY())＞9,(YEAR (TODAY ())-LEFT ($A2,4)+1)*10+MID($A2,5,1),(YEAR(TODAY())-LEFT($A2,4))*10+MID($A2,5,1)))”。先用IF函數執行真假值判斷，如果當前月份大于9月份（即新學年），則用當前年份減去班內學號中的入學年份再加1得到年級數，用MID函數引用出班內學號中的班別數，再用年級數乘10加班別數得到班級名稱。否則，用當前年份減去班內學號中的入學年份得到年級數（不用再加1），用MID函數引用出班內學號中的班別數，再用年級數乘10加班別數得到班級名稱。

提起排名，大家首先就會想到RANK函數，但RANK函數使用時局限較大，排名時班級、年級不同，對數字列表的引用范圍就會有變化。如果手動給RANK函數指定引用范圍，統計數據就得固定下來，不允許修改數據，容錯率很低。此外，如果學校規模大，手動指定引用范圍本身的工作量也是很大的，失去了編寫公式的意義。因此，學生成績班級排名和年級排名時，使用了“SUM+if”的數組函數組合。班級排名方法：“=IF(OR(COUNTIF(學生基礎信息!$AN$2:$AN$1000,A2)=0,SUM(I2:K2)=0),"",IF($H2＞0,SUM(IF(bj=$D2,IF(zf＞H2,1,0)))+1,""))”，先對表達式 bj=$D2進行條件檢測，bj是班級標題下的數據，是提前定義的名稱，通過檢測，篩選出同一個班級的總分數據進一步處理。再嵌套第二層函數IF，對表達式zf＞H2進行條件檢測，zf代表總分標題下的數據。當表達式zf＞H2為TRUE時返回1，為FALSE時返回0。年級排名方法：“=IF(OR(COUNTIF(學生基礎信息!$AN$2:$AN$1000,A2)=0,SUM (I2:K2)=0),"",IF($H2＞0,SUM(IF(LEFT(bj,1)=LEFT(D2,1),IF(zf＞H2,1,0)))+1,""))”。

備注一欄系統設計了數據自動校對功能，可對錄入的考試成績進行各類錯誤的檢查與提示，對學號錯誤、學號重復、成績不全、語文成績異常、數學成績異常、英語成績異常、重名等數據問題進行提示。應用邏輯函數指令拓展、IF函數多層嵌套、文字提示智能化等技術來實現，方法為“=IF(COUNTIF(學生基礎信息!$AN:$AN,A2)=0,"此為空號",IF(COUNTIF(A:A,A2)＞1,"學號重復",IF(OR(AND($D2＞30,COUNTBLANK(I2:K2)＞0),COUNTBLANK(I2:J2)＞0)," 成 績 不 全 ",CONCATENATE(IF(OR($I2＞100,$I2＜0),"語文",""),IF(OR($J2＞100,$J2＜0),"數學",""),IF(OR($K2＞100,$K2＜0),"英語",""),IF(OR($I2＞100,$I2＜0,$J2＞100,$J2＜0,$K2＞100,$K2＜0)," 成績異常",IF(AND($D2＞10,$D2＜30,COUNTBLANK(K2)=0),"低年級不考英語",IF(COUNTIF(B:B,B2)＞1,"重名","")))))))”。 IF 函數可以嵌套七層，用 value_if_false及 value_if_true參數與邏輯函數套用，可以構造復雜的檢測條件，滿足備注欄的數據校對、錯誤提示等需求。

匯總表，對全校各班各門功課的考試成績匯總成績進行統計，形成各班教學質量報表。任課教師引用方法是 “=HLOOKUP(B2,任課教師!$B$1:$K$13,MATCH(AW2,任課教師!$A$1:$A$13,0),FALSE)”。 在“任課教師!表中$B$1:$K$13”數據區域查找B2單元格（課程科目）數值，并由此返回表達式MATCH(AW2,任課教師!$A$1:$A$13,0)指定行處的數值。實考數統計方法是數組函數 “=INDEX(FREQUENCY(IF(bj=$AW2,CHOOSE(HLOOKUP($B2,{"語文","數學","英語";1,2,3},2,FALSE),yw,sx,yy)),{0.1,100.1}),2)”， 這段數組函數也是多個函數嵌套形成的，其中yw、sx、yy是提前定義的數據區域名稱，yw是語文成績數據區域，sx是數學成績數據區域，yy是英語成績數據區域。B列單元格是考試科目，科目各不相同，屬于變量，因此，用 HLOOKUP($B2,{"語文","數學","英語";1,2,3},2,FALSE)語句，把科目轉換為序號，用CHOOSE函數選擇科目所在的定義名稱數據區域，再用IF函數對指定班級的數據進行邏輯篩選，用FREQUENCY函數返回篩選出的符合要求數據的頻率分布，最后用INDEX函數調用所需數據，統計出各科目的實際參加考試的人數。最高分統計方法是數組函數“=MAX(IF(bj=$AW2,CHOOSE(HLOOKUP($B2,{"語文","數學","英語";1,2,3},2,FALSE),yw,sx,yy)))”。用IF函數對指定班級的數據進行邏輯篩選，再用MAX函數返回其中的最大值，也就是指定班級、指定科目的最高分。最低分統計方法只需要把MAX函數換為MIN函數返回其中的最小值便可。年級前10名（人）統計方法是數組函數“=INDEX(FREQUENCY(IF(bj=$AW2,njpm),{0.1,10.1}),2)”。其中njpm是定義的年級排名標題數據區域名稱，先用IF函數篩選出指定班級的年級排名數據，再用FREQUENCY返回10以內數值的頻率分布，最后用INDEX函數調用其所需數據，統計出指定班級的年級排名前10的具體人數。平均分統計方法是數組函數 “=ROUND(AVERAGE(IF(bj=$AW2,CHOOSE(HLOOKUP($B2,{"語文","數學","英語";1,2,3},2,FALSE),yw,sx,yy))),2)”。對篩選出的指定班級、指定科目的考試成績計算平均分，四舍五入保留2位小數。標準偏差統計方法是數組函數“=ROUND(STDEV(IF(bj=$AW2,CHOOSE(HLOOKUP($B2,{"語文","數學","英語";1,2,3},2,FALSE),yw,sx,yy))),2)”，標準偏差反映相對于平均分班級整體成績的離散程度。90至100分(人)統計方法是數組函數“=INDEX(FREQUENCY(IF(bj=$AW2,CHOOSE(HLOOKUP($B2,{"語文","數學","英語";1,2,3},2,FALSE),yw,sx,yy)),{0,9.9,19.9,29.9,39.9,49.9,59.9,69.9,79.9,89.9,100.1}),11)”。用FREQUENCY對指定班級、科目的整體成績按照每10分一個分段進行頻率分布計算，最后用INDEX函數返回所需分數段的具體人數。各科90至100分（人）統計方法是數組函數“=IF($AW2＜30,SUM(IF(bj=$AW2,IF(yw＞=90,IF(sx＞=90,1,0)))),SUM(IF (bj=$AW2,IF (yw ＞=90,IF (sx＞=90,IF (yy＞=90,1,0))))))”。各科的含義對每個年級不盡相同，一、二年級是指語文和數學兩個科目，三至六年級是語文、數學、英語三個科目，因此要用IF函數進行分類，再使用不同的統計方法。

成績查詢工作表，可以按照學號和姓名兩種方式查詢學生考試成績，并且可以跟蹤反饋歷次考試成績。班內學號的查詢方法是“=IF($L2&$M2="","",IF($L2="",IF(MAX(IF(成績冊!$A$1:$B$1000=$M2,ROW($1:$1000)))=0,"",INDEX(成績冊!$A$1:$L$1000,MAX(IF(成績冊!$A$1:$B$1000=$M2,ROW($1:$1000))),MAX(IF(成績冊!$A$1:$K$1000=$M2,COLUMN($A$1:$I$1)))-1)),IF(MAX(IF(成績冊!$A$1:$B$1000=$L2,ROW($1:$1000)))=0,"",VLOOKUP($L2,成績冊!$A$2:$K$1000,1,FALSE))))”。該組函數首先判斷查詢方式，L2單元格接收學號信息，M2單元格接收姓名信息。如果L2單元格為空值，M2單元格不為空值，說明使用者是按照姓名方式查詢的，即使用MAX(IF(成績冊!$A$1:$B$1000=$M2,ROW($1:$1000)))和 MAX(IF(成績冊!$A$1:$K$1000=$M2,COLUMN($A$1:$I$1)))-1查詢該生對應的班內學號在數據區域內的行號、列號，再用INDEX函數引用該生班內學號，顯示在單元格。否則，進一步判斷L2單元格有無學號信息，如果使用者已經在L2單元格填入了班內學號，則使用VLOOKUP($L2,成績冊!$A$2:$K$1000,1,FALSE)語句查證成績冊工作表的班內學號數值，若學號正確則顯示于單元格，否則顯示空值，并在備注欄顯示文字“這是空號”。因為成績查詢工作表與成績冊工作表結構相同，所以姓名、性別、班級、班級排名、年級排名、總分、平均分、語文、數學、英語的數據查詢使用同一方法“=IF($L2&$M2="","",IF($A2="","",VLOOKUP($A2,成績冊!$A$2:$K$1000,COLUMN(),FALSE)))”。表結構相同，每個查詢項目處于同一位置，因此，以函數COLUMN返回所在列號為變量，引導VLOOKUP函數在數值數組中查找指定的數值，并由此返回數組當前行中指定列處的數值，在單元格中顯示所需要的信息。

在處理變量復雜的任務時，單個函數的功能顯得蒼白無力，通過函數嵌套的靈活運用往往可以解決問題。嵌套函數是拓展函數功能的主要方式，將函數返回值作為另一函數的參數使用，層級疊加，性能提升[1]。合理運用Excel函數嵌套，使電子表格具備數據處理的五大要素，即數據錄入、校對、查詢、統計和分析，不需要專業編程軟件技術支持，僅采用Excel內置的函數庫，經過嚴密的設計，也可以形成類似數據庫的簡單功能，實現學校教學質量統計分析的自動化。基于統計方便、準確、全面的目標，界面設計和系統操作采用Excel工作表默認模式，運行環境為Excel，為大家所熟知，操作簡單，上手容易，只要有一點計算機基礎的教師都可以熟練掌握，免除培訓環節。學生考試成績錄入完成之后，使用預設的公式進行數據填充，雙擊鼠標在幾秒鐘時間內，就可以對46個統計項目、幾千個單元格數據進行統計，準確高效的完成工作任務。由于函數代碼開放，使用者可以根據實際需要修改相關代碼，重構統計項目，只需變更單位信息就可為學校教務工作服務，成為教學管理的好助手。

[1]錢秀峰.Excel中函數嵌套功能的實際應用[J].人力資源管理,2010(4).