楊旭東

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

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

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

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

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

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

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

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

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

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

參考文獻(xiàn)：

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

（編輯：魯利瑞）