李金海
【中圖分類號】 TP317 【文獻(xiàn)標(biāo)識碼】 A 【文章編號】 2236-1879(2017)12-0027-02
1 問題提出
作為高校的教師或輔導(dǎo)員經(jīng)常面對這樣一種情況,很多時候我們需要將學(xué)生的個人成績或?qū)W籍等私密的信息,反饋給學(xué)生進(jìn)行查詢核對,目前很多單位采取辦法有兩種:第一種是將學(xué)生成績信息以Excel文件形式通過網(wǎng)站附件掛載;第二種是委托學(xué)校IT部門開發(fā)網(wǎng)絡(luò)查詢系統(tǒng);這兩種方式存在如下的問題。
1.1 Excel表格形式公示信息:
通過Excel文件形式將學(xué)生的成績或?qū)W籍信息整理,通過網(wǎng)絡(luò)平臺分發(fā),讓學(xué)生自助通過網(wǎng)頁以附件形式查看;或打印全部需要公示或查詢的學(xué)生信息,粘貼公示欄學(xué)生查看自己信息。這兩種辦法是目前
各個單位普遍采用的方法,存在最大問題是,學(xué)生的個人信息甚至很多私密信息特別容易外漏,對學(xué)生的個人信息沒有進(jìn)行有效的保護(hù),存在的著嚴(yán)重的信息安全問題。
1.2 開發(fā)web在線查詢系統(tǒng):
如果將問題委托IT部門(信息技術(shù)部門)解決,按需定制開發(fā)一套基于Internet的B/S web在線查詢系統(tǒng),筆者本人早年系程序員出生,熟知開發(fā)程序流程之繁瑣,且不說開發(fā)的技術(shù)難度,軟件調(diào)試運維
這些技術(shù)性問題,單是服務(wù)器審批租賃至少需要一個月,而且IT部門日常事務(wù)繁多,沒有足夠的時間和精力進(jìn)行設(shè)計開發(fā)。
1.3 函數(shù)實現(xiàn)的優(yōu)勢:
基于以上兩種情況的分析,需要解決兩個問題:第一、既要查詢快速準(zhǔn)確又要容易操作;第二、每位學(xué)生只能查詢自己信息,而不能查看他人的信息;第三、小巧靈活便于通過網(wǎng)絡(luò)平臺分發(fā)。分析上面兩種需求,對于信息發(fā)布者和信息查詢者來說,需要開發(fā)一款操作簡單,開發(fā)成本低廉,使用性強的軟件成為迫切需要,筆者認(rèn)為最優(yōu)的策略是,開發(fā)基于本地Excel表格的微型查詢系統(tǒng)。
首先、Excel是目前最流行的,并已成為世界標(biāo)準(zhǔn)[1],大部分計算機用戶都熟知office軟件,office的基本操作簡單易懂,通過電子表格Excel獲知信息,如打開,輸入身份號碼等基本操作,這就降低了用戶的門檻;其二、信息表通過保護(hù)和隱藏的設(shè)置對查詢者是隱蔽的,查詢者只能通過自己的學(xué)號查詢自己的成績信息,其他人在未知他人學(xué)號的情況下,無法獲取除自己以外他人的成績信息,從而實現(xiàn)了對個人信息的保護(hù)。
通過以實際學(xué)校學(xué)生期末計算機成績查詢?yōu)槔?,分別采用Excel函數(shù)方法和來實現(xiàn)。Excel函數(shù)實現(xiàn)相對簡單,主要使用vlookup()函數(shù)和lookup函數(shù)來實現(xiàn)成績查詢。
2 設(shè)計實現(xiàn)
2.1 界面設(shè)計:
將查詢窗口和具有所有學(xué)生的成績表(成績表)分別放在兩個工作表中,為了保證查詢成績的唯一性,我們設(shè)定查詢關(guān)鍵字為考生的學(xué)生證號(學(xué)號),成績查詢界面設(shè)計如圖 1所示:
2.2 vlookup()函數(shù)實現(xiàn)
2.2.1 認(rèn)識vlookup()函數(shù):
Excel 2010中vlookup()函數(shù)具有根據(jù)兩表通用字段值實現(xiàn)匹配查詢的作用,是excel中的一個縱向查找函數(shù)[2],利用vlookup()的這種特性,查詢窗口考生輸入自己的學(xué)號,成績表中是以學(xué)號為首列的所有學(xué)生成績。成績表的表結(jié)構(gòu)如圖 2所示:
考生輸入學(xué)生證號,即可在成績表中匹配該行學(xué)號所對應(yīng)成績列成績值,從而實現(xiàn)成績的查詢。由于學(xué)生證號屬于學(xué)生敏感信息,此處將學(xué)生證號替代為字母加數(shù)字文本串。
vlookup()函數(shù)實現(xiàn)返回值為姓名的查詢的公式如圖3所示:
參數(shù)解釋:
① $C$3:為輸入學(xué)號所在單元格
② 成績表!$A$1:$E$49:為成績表的數(shù)據(jù)區(qū)域,此區(qū)域必須以學(xué)號為首列。
③ 2:表示查詢匹配后返回“成績表!$A$1:$E$49”成績表區(qū)域的第2列。
④ 0:查詢匹配的模式為精確匹配。
2.2.2 vlookup()函數(shù)的局限性:
由于vlookup()函數(shù)參數(shù)易于理解且使用便捷,故在查詢案例中被廣泛的使用,但是該函數(shù)也有局限性,比如在該案例應(yīng)用中,無法實現(xiàn)多條件匹配查詢,考生同時報考了兩個科目(1級和2級),即輸入學(xué)號同時返回該考生兩科目各自的成績信息,單獨靠vlookup函數(shù)是無法實現(xiàn)多條匹配查詢的,雖然可以借助其他函數(shù)嵌套來實現(xiàn),原理不是很復(fù)雜,但最終函數(shù)構(gòu)成式非常臃腫和繁瑣,不便于理解和推廣應(yīng)用,筆者將重點講解lookup函數(shù)實現(xiàn)本案例多條件查詢匹配的問題,故不對vlookup解決方案不做過多解釋和說明,讀者可以自行研究。
2.3 LOOKUP函數(shù)實現(xiàn)
2.3.1 Lookup的特點:
Looup是一個非常靈活、自由度比較高的函數(shù),主要有兩種語法形式,向量形式和數(shù)組形式。
數(shù)組語法形式:是匹配查找某一個數(shù)值在那個范圍內(nèi),在實際應(yīng)用中經(jīng)常解決范圍內(nèi),數(shù)據(jù)匹配的問題。比如學(xué)生成績的“優(yōu)良中差”等級的判斷,工資扣稅計算的問題等等。
本案例重點使用向量的語法形式來解決問題,向量語法基本結(jié)構(gòu):
=LOOKUP(lookup_value, lookup_vector, [result_vector])
參數(shù)解釋:
①lookup_value:向量的搜索值,如學(xué)號。
②lookup_vector:向量查詢區(qū)域,如學(xué)號列。
③result_vector:向量結(jié)果返回區(qū)域,如成績列!
根據(jù)學(xué)號查詢,獲知查詢區(qū)域中該學(xué)號所對應(yīng)的行號或列號,在result_vector所對應(yīng)的行號或列號返回對應(yīng)的值。需要說明的是,lookup_value采用的向下查找的方法來匹配,如表 1所示:比如LOOKUP(4.19, A2:A6, B2:B6)和LOOKUP(5.00, A2:A6, B2:B6)兩個函數(shù)的返回結(jié)果都為橙色,第二個為橙色的原因是,在 A 列中查找 5.00,與接近它的最小值 (4.19) 匹配,然后返回 B 列中同一行內(nèi)的值。
2.3.2 Lookup實現(xiàn)精確查找:
但是,正是由于lookup的這種向下查找的特性,是lookup無法實現(xiàn)精確匹配,要解決lookup的精確匹配,可以利用的lookup無法對公式報錯信息“#DIV/0”進(jìn)行匹配,設(shè)計思路如下:
將vlookup的參數(shù)2(lookup_vector)設(shè)置為多條件匹配區(qū)域,在成績表中的不同的科目有不同的科目代碼,比如1級科目代碼為15,二級科目代碼為65,故而可以將條件區(qū)域設(shè)置為:
(成績表!$A$2:$A$49=成績查詢!$C$3)*(成績表!$C$2:$C$49=65)
兩個括號表示兩個條件:條件1中“成績查詢!$C$3”是考生輸入學(xué)號單元格,“成績表!$A$2:$A$49=成績查詢!$C$3”意思是,在成績表中的所有學(xué)號與輸入學(xué)號匹配,該運算的結(jié)果是一組有“0”或“1”構(gòu)成的數(shù)組數(shù)據(jù);條件2“成績表!$C$2:$C$49=65”表示科目是二級的科目,該表達(dá)式運算結(jié)果也是一組“0”或“1”的數(shù)組數(shù)據(jù)。條件1和條件2相乘也是“0”或“1”的數(shù)組數(shù)據(jù),只有兩個條件都滿足相乘的結(jié)果也為1,即是同時兩個條件都成立。
查找成績的原理是,lookup函數(shù)在兩組相乘結(jié)果中,一組由“0”或“1”的數(shù)組數(shù)據(jù)中找1得到行號,通過該行號在成績列中返回成績值作為函數(shù)的運算結(jié)果,函數(shù)表達(dá)式簡化如下所示:
=LOOKUP(1,(條件1)*(條件2),成績列)
實際運行后發(fā)現(xiàn)并不能實現(xiàn)精確的查找,是因為vlookup是向下匹配查找,故而會出現(xiàn)隨機返回結(jié)果的情況。筆者經(jīng)過分析發(fā)現(xiàn),vlookup函數(shù)對于函數(shù)出錯值會忽略跳過,利用該特性可以讓數(shù)字0除“(條件1)*(條件2)”。0除以數(shù)組數(shù)據(jù)中為“0”的數(shù),報“#DIV/0”錯誤(即分母不能為零的錯誤);而數(shù)組數(shù)據(jù)中結(jié)果為1的被0除后,結(jié)果為0,也是一組數(shù)字中唯一0,其他全部報“#DIV/0”錯誤。用lookup在函數(shù)查找數(shù)字1從而實現(xiàn)精確查找。
=LOOKUP(1,0/((成績表!$A$2:$A$49=成績查詢!$C$3)*(成績表!$C$2:$C$49=15)),成績表!$E$2:$E$49)
該函數(shù)內(nèi)部一共有3個參數(shù):如果查詢科目為2級,只需要將條件2中科目代碼修改為65即可即可;參數(shù)3為查詢結(jié)果返回區(qū)域,如要查詢返回該學(xué)生姓名,該參數(shù)參數(shù)設(shè)置為姓名所在區(qū)域即可。
利用lookup函數(shù)可以高效快速實現(xiàn)多條件查詢匹配問題,結(jié)構(gòu)清晰易懂,而且運行效率高,如果有同時新增條件時,只需在第二個參數(shù)中乘入新條件即可。
需要注意的是,為邏輯清晰建議各個條件用括號分割,多條件最外層一定要記得加括號用0除,否則會出現(xiàn)查找無結(jié)果的情況。
3 安全性輸出設(shè)置
使用函數(shù)設(shè)計實現(xiàn)查詢系統(tǒng)后,為了防止他人意外重寫公式導(dǎo)致二次錯誤,對公式進(jìn)行隱藏;或考生禁止查看或修改原始全體考生成績表[3],對Excel工作薄和工作表設(shè)置加密保護(hù),通過以上方法對
Excel實現(xiàn)封裝,具體的做法是:
①美化查詢窗口,做到美觀大方、清晰易懂,容易上手操作!
②隱藏成績工作表,同時隱藏查詢工作表中多余的行或列,只保留成績查詢的窗口!
③對工作薄、工作表進(jìn)行加密保護(hù),防止非管理員對表結(jié)構(gòu)或成績表內(nèi)容進(jìn)行修改!
④對查詢窗口的區(qū)域鎖定與限制,設(shè)置C3單元格允許編輯,實現(xiàn)輸入學(xué)號,其他區(qū)域鎖定,不可編輯,不可查看公式。[4]
4 討論
通過Excel兩種函數(shù)實現(xiàn)成績查詢,以Excel自身為數(shù)據(jù)源查詢成績?yōu)榘咐?,初步探討了利用office軟件,如何在日常辦公中對個人信息進(jìn)行安全保護(hù),讓學(xué)校的教務(wù)工作人員在日常工作中能夠有方法可參考,從學(xué)生個人信息保護(hù)的角度著手。以此來提高大家對信息保護(hù)的意識。
參考文獻(xiàn)
[1] JOHN WALKENBACH.Excel 2013bible[M].Wiley,2013.
[2] 錢平生. 基于Excel函數(shù)實現(xiàn)學(xué)生成績有效管理[J]. 軟件,2013,(06):115-117.
[3] 徐慧. 基于Excel的平時成績查詢系統(tǒng)設(shè)計及實現(xiàn)[J]. 軟件導(dǎo)刊,2014,(04):74-76.
[4] 于偉昌, 蘇慧紅. 運用Excel生成考試質(zhì)量分析報告和成績數(shù)據(jù)庫的理論與實踐[J]. 現(xiàn)代情報, 2005, 25(8):186-189.