淺談考務(wù)管理系統(tǒng)中Excel函數(shù)嵌套

楊旭東

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

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

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

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

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

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

提起排名，大家首先就會想到RANK函數(shù)，但RANK函數(shù)使用時局限較大，排名時班級、年級不同，對數(shù)字列表的引用范圍就會有變化。如果手動給RANK函數(shù)指定引用范圍，統(tǒng)計數(shù)據(jù)就得固定下來，不允許修改數(shù)據(jù)，容錯率很低。此外，如果學(xué)校規(guī)模大，手動指定引用范圍本身的工作量也是很大的，失去了編寫公式的意義。因此，學(xué)生成績班級排名和年級排名時，使用了“SUM+if”的數(shù)組函數(shù)組合。班級排名方法：“=IF（OR（COUNTIF（學(xué)生基礎(chǔ)信息！$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是班級標(biāo)題下的數(shù)據(jù)，是提前定義的名稱，通過檢測，篩選出同一個班級的總分?jǐn)?shù)據(jù)進一步處理。再嵌套第二層函數(shù)IF，對表達式zf>H2進行條件檢測，zf代表總分標(biāo)題下的數(shù)據(jù)。當(dāng)表達式zf>H2為TRUE時返回1，為FALSE時返回0。年級排名方法：“=IF（OR（COUNTIF（學(xué)生基礎(chǔ)信息！$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è)計了數(shù)據(jù)自動校對功能，可對錄入的考試成績進行各類錯誤的檢查與提示，對學(xué)號錯誤、學(xué)號重復(fù)、成績不全、語文成績異常、數(shù)學(xué)成績異常、英語成績異常、重名等數(shù)據(jù)問題進行提示。應(yīng)用邏輯函數(shù)指令拓展、IF函數(shù)多層嵌套、文字提示智能化等技術(shù)來實現(xiàn)，方法為“=IF（COUNTIF（學(xué)生基礎(chǔ)信息！$AN：$AN，A2）=0，"此為空號"，IF（COUNTIF（A：A，A2）>1，"學(xué)號重復(fù)"，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ù)學(xué)"，""），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ù)套用，可以構(gòu)造復(fù)雜的檢測條件，滿足備注欄的數(shù)據(jù)校對、錯誤提示等需求。

匯總表，對全校各班各門功課的考試成績匯總成績進行統(tǒng)計，形成各班教學(xué)質(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ù)學(xué)"，"英語";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ù)學(xué)成績數(shù)據(jù)區(qū)域，yy是英語成績數(shù)據(jù)區(qū)域。B列單元格是考試科目，科目各不相同，屬于變量，因此，用HLOOKUP（$B2，{"語文"，"數(shù)學(xué)"，"英語";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ù)學(xué)"，"英語";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是定義的年級排名標(biāo)題數(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ù)學(xué)"，"英語";1，2，3}，2，F(xiàn)ALSE），yw，sx，yy））），2）”。對篩選出的指定班級、指定科目的考試成績計算平均分，四舍五入保留2位小數(shù)。標(biāo)準(zhǔn)偏差統(tǒng)計方法是數(shù)組函數(shù)“=ROUND（STDEV（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"語文"，"數(shù)學(xué)"，"英語";1，2，3}，2，F(xiàn)ALSE），yw，sx，yy））），2）”，標(biāo)準(zhǔn)偏差反映相對于平均分班級整體成績的離散程度。90至100分（人）統(tǒng)計方法是數(shù)組函數(shù)“=INDEX（FREQUENCY（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"語文"，"數(shù)學(xué)"，"英語";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ù)返回所需分?jǐn)?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ù)學(xué)兩個科目，三至六年級是語文、數(shù)學(xué)、英語三個科目，因此要用IF函數(shù)進行分類，再使用不同的統(tǒng)計方法。

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

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

參考文獻：

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

（編輯：魯利瑞）