楊旭東

摘 要：Excel在工作應用中，許多時候僅僅使用了它的簡單功能，如求和、求平均值、計數(shù)等。實際上，Excel數(shù)據(jù)處理功能很強大，經(jīng)過縝密設計，靈活應用其函數(shù)功能，可以完成復雜統(tǒng)計，或達成類似數(shù)據(jù)庫的功能。文章以應用Excel內(nèi)置功能制作考務管理系統(tǒng)為例，討論應用函數(shù)嵌套，實現(xiàn)學生成績自動化統(tǒng)計分析，具有操作簡單、上手容易、免除培訓環(huán)節(jié)、準確高效地完成工作任務等軟件特征。在函數(shù)代碼開放環(huán)境中，修改代碼，重構統(tǒng)計項目的方法，對考務管理軟件《學生成績統(tǒng)計分析》設計、開發(fā)的主導思想和函數(shù)嵌套技巧進行總結(jié)。研討如何拓展Excel函數(shù)功能，更好地為學校教務工作服務，使之成為教學管理的好助手。

關鍵詞：考務管理;Excel函數(shù)嵌套;軟件開發(fā)

中圖分類號：G434 ? ? ? ? ? ? 文獻標志碼：A ? ? ? ? ? 文章編號：1673-8454（2015）04-0089-03

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

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

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

提起排名，大家首先就會想到RANK函數(shù)，但RANK函數(shù)使用時局限較大，排名時班級、年級不同，對數(shù)字列表的引用范圍就會有變化。如果手動給RANK函數(shù)指定引用范圍，統(tǒng)計數(shù)據(jù)就得固定下來，不允許修改數(shù)據(jù)，容錯率很低。此外，如果學校規(guī)模大，手動指定引用范圍本身的工作量也是很大的，失去了編寫公式的意義。因此，學生成績班級排名和年級排名時，使用了“SUM+if”的數(shù)組函數(shù)組合。班級排名方法：“=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是班級標題下的數(shù)據(jù)，是提前定義的名稱，通過檢測，篩選出同一個班級的總分數(shù)據(jù)進一步處理。再嵌套第二層函數(shù)IF，對表達式zf>H2進行條件檢測，zf代表總分標題下的數(shù)據(jù)。當表達式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，""））”。

備注一欄系統(tǒng)設計了數(shù)據(jù)自動校對功能，可對錄入的考試成績進行各類錯誤的檢查與提示，對學號錯誤、學號重復、成績不全、語文成績異常、數(shù)學成績異常、英語成績異常、重名等數(shù)據(jù)問題進行提示。應用邏輯函數(shù)指令拓展、IF函數(shù)多層嵌套、文字提示智能化等技術來實現(xiàn)，方法為“=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），"數(shù)學"，""），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函數(shù)可以嵌套七層，用 value_if_false 及 value_if_true 參數(shù)與邏輯函數(shù)套用，可以構造復雜的檢測條件，滿足備注欄的數(shù)據(jù)校對、錯誤提示等需求。

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

成績查詢工作表，可以按照學號和姓名兩種方式查詢學生考試成績，并且可以跟蹤反饋歷次考試成績。班內(nèi)學號的查詢方法是“=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，F(xiàn)ALSE））））”。該組函數(shù)首先判斷查詢方式，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查詢該生對應的班內(nèi)學號在數(shù)據(jù)區(qū)域內(nèi)的行號、列號，再用INDEX函數(shù)引用該生班內(nèi)學號，顯示在單元格。否則，進一步判斷L2單元格有無學號信息，如果使用者已經(jīng)在L2單元格填入了班內(nèi)學號，則使用VLOOKUP（$L2，成績冊！$A$2：$K$1000，1，F(xiàn)ALSE）語句查證成績冊工作表的班內(nèi)學號數(shù)值，若學號正確則顯示于單元格，否則顯示空值，并在備注欄顯示文字“這是空號”。因為成績查詢工作表與成績冊工作表結(jié)構相同，所以姓名、性別、班級、班級排名、年級排名、總分、平均分、語文、數(shù)學、英語的數(shù)據(jù)查詢使用同一方法“=IF（$L2&$M2=""，""，IF（$A2=""，""，VLOOKUP（$A2，成績冊！$A$2：$K$1000，COLUMN（），F(xiàn)ALSE）））”。表結(jié)構相同，每個查詢項目處于同一位置，因此，以函數(shù)COLUMN返回所在列號為變量，引導VLOOKUP函數(shù)在數(shù)值數(shù)組中查找指定的數(shù)值，并由此返回數(shù)組當前行中指定列處的數(shù)值，在單元格中顯示所需要的信息。

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

參考文獻：

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

（編輯：魯利瑞）