李金海

【中圖分類號(hào)】 TP317 【文獻(xiàn)標(biāo)識(shí)碼】 A 【文章編號(hào)】 2236-1879（2017）12-0027-02

1 問題提出

作為高校的教師或輔導(dǎo)員經(jīng)常面對(duì)這樣一種情況，很多時(shí)候我們需要將學(xué)生的個(gè)人成績(jī)或?qū)W籍等私密的信息，反饋給學(xué)生進(jìn)行查詢核對(duì)，目前很多單位采取辦法有兩種：第一種是將學(xué)生成績(jī)信息以Excel文件形式通過網(wǎng)站附件掛載；第二種是委托學(xué)校IT部門開發(fā)網(wǎng)絡(luò)查詢系統(tǒng)；這兩種方式存在如下的問題。

1.1 Excel表格形式公示信息：

通過Excel文件形式將學(xué)生的成績(jī)或?qū)W籍信息整理，通過網(wǎng)絡(luò)平臺(tái)分發(fā)，讓學(xué)生自助通過網(wǎng)頁以附件形式查看；或打印全部需要公示或查詢的學(xué)生信息，粘貼公示欄學(xué)生查看自己信息。這兩種辦法是目前

各個(gè)單位普遍采用的方法，存在最大問題是，學(xué)生的個(gè)人信息甚至很多私密信息特別容易外漏，對(duì)學(xué)生的個(gè)人信息沒有進(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)試運(yùn)維

這些技術(shù)性問題，單是服務(wù)器審批租賃至少需要一個(gè)月，而且IT部門日常事務(wù)繁多，沒有足夠的時(shí)間和精力進(jìn)行設(shè)計(jì)開發(fā)。

1.3 函數(shù)實(shí)現(xiàn)的優(yōu)勢(shì)：

基于以上兩種情況的分析，需要解決兩個(gè)問題：第一、既要查詢快速準(zhǔn)確又要容易操作；第二、每位學(xué)生只能查詢自己信息，而不能查看他人的信息；第三、小巧靈活便于通過網(wǎng)絡(luò)平臺(tái)分發(fā)。分析上面兩種需求，對(duì)于信息發(fā)布者和信息查詢者來說，需要開發(fā)一款操作簡(jiǎn)單，開發(fā)成本低廉，使用性強(qiáng)的軟件成為迫切需要，筆者認(rèn)為最優(yōu)的策略是，開發(fā)基于本地Excel表格的微型查詢系統(tǒng)。

首先、Excel是目前最流行的，并已成為世界標(biāo)準(zhǔn)[1]，大部分計(jì)算機(jī)用戶都熟知office軟件，office的基本操作簡(jiǎn)單易懂，通過電子表格Excel獲知信息，如打開，輸入身份號(hào)碼等基本操作，這就降低了用戶的門檻；其二、信息表通過保護(hù)和隱藏的設(shè)置對(duì)查詢者是隱蔽的，查詢者只能通過自己的學(xué)號(hào)查詢自己的成績(jī)信息，其他人在未知他人學(xué)號(hào)的情況下，無法獲取除自己以外他人的成績(jī)信息，從而實(shí)現(xiàn)了對(duì)個(gè)人信息的保護(hù)。

通過以實(shí)際學(xué)校學(xué)生期末計(jì)算機(jī)成績(jī)查詢?yōu)槔謩e采用Excel函數(shù)方法和來實(shí)現(xiàn)。Excel函數(shù)實(shí)現(xiàn)相對(duì)簡(jiǎn)單，主要使用vlookup（）函數(shù)和lookup函數(shù)來實(shí)現(xiàn)成績(jī)查詢。

2 設(shè)計(jì)實(shí)現(xiàn)

2.1 界面設(shè)計(jì)：

將查詢窗口和具有所有學(xué)生的成績(jī)表（成績(jī)表）分別放在兩個(gè)工作表中，為了保證查詢成績(jī)的唯一性，我們?cè)O(shè)定查詢關(guān)鍵字為考生的學(xué)生證號(hào)（學(xué)號(hào)），成績(jī)查詢界面設(shè)計(jì)如圖 1所示：

2.2 vlookup（）函數(shù)實(shí)現(xiàn)

2.2.1 認(rèn)識(shí)vlookup（）函數(shù)：

Excel 2010中vlookup（）函數(shù)具有根據(jù)兩表通用字段值實(shí)現(xiàn)匹配查詢的作用，是excel中的一個(gè)縱向查找函數(shù)[2]，利用vlookup（）的這種特性，查詢窗口考生輸入自己的學(xué)號(hào)，成績(jī)表中是以學(xué)號(hào)為首列的所有學(xué)生成績(jī)。成績(jī)表的表結(jié)構(gòu)如圖 2所示：

考生輸入學(xué)生證號(hào)，即可在成績(jī)表中匹配該行學(xué)號(hào)所對(duì)應(yīng)成績(jī)列成績(jī)值，從而實(shí)現(xiàn)成績(jī)的查詢。由于學(xué)生證號(hào)屬于學(xué)生敏感信息，此處將學(xué)生證號(hào)替代為字母加數(shù)字文本串。

vlookup（）函數(shù)實(shí)現(xiàn)返回值為姓名的查詢的公式如圖3所示：

參數(shù)解釋：

① $C$3：為輸入學(xué)號(hào)所在單元格

② 成績(jī)表！$A$1：$E$49：為成績(jī)表的數(shù)據(jù)區(qū)域，此區(qū)域必須以學(xué)號(hào)為首列。

③ 2：表示查詢匹配后返回“成績(jī)表！$A$1：$E$49”成績(jī)表區(qū)域的第2列。

④ 0：查詢匹配的模式為精確匹配。

2.2.2 vlookup（）函數(shù)的局限性：

由于vlookup（）函數(shù)參數(shù)易于理解且使用便捷，故在查詢案例中被廣泛的使用，但是該函數(shù)也有局限性，比如在該案例應(yīng)用中，無法實(shí)現(xiàn)多條件匹配查詢，考生同時(shí)報(bào)考了兩個(gè)科目（1級(jí)和2級(jí)），即輸入學(xué)號(hào)同時(shí)返回該考生兩科目各自的成績(jī)信息，單獨(dú)靠vlookup函數(shù)是無法實(shí)現(xiàn)多條匹配查詢的，雖然可以借助其他函數(shù)嵌套來實(shí)現(xiàn)，原理不是很復(fù)雜，但最終函數(shù)構(gòu)成式非常臃腫和繁瑣，不便于理解和推廣應(yīng)用，筆者將重點(diǎn)講解lookup函數(shù)實(shí)現(xiàn)本案例多條件查詢匹配的問題，故不對(duì)vlookup解決方案不做過多解釋和說明，讀者可以自行研究。

2.3 LOOKUP函數(shù)實(shí)現(xiàn)

2.3.1 Lookup的特點(diǎn)：

Looup是一個(gè)非常靈活、自由度比較高的函數(shù)，主要有兩種語法形式，向量形式和數(shù)組形式。

數(shù)組語法形式：是匹配查找某一個(gè)數(shù)值在那個(gè)范圍內(nèi)，在實(shí)際應(yīng)用中經(jīng)常解決范圍內(nèi)，數(shù)據(jù)匹配的問題。比如學(xué)生成績(jī)的“優(yōu)良中差”等級(jí)的判斷，工資扣稅計(jì)算的問題等等。

本案例重點(diǎn)使用向量的語法形式來解決問題，向量語法基本結(jié)構(gòu)：

=LOOKUP（lookup_value， lookup_vector， [result_vector]）

參數(shù)解釋：

①lookup_value：向量的搜索值，如學(xué)號(hào)。

②lookup_vector：向量查詢區(qū)域，如學(xué)號(hào)列。

③result_vector：向量結(jié)果返回區(qū)域，如成績(jī)列！

根據(jù)學(xué)號(hào)查詢，獲知查詢區(qū)域中該學(xué)號(hào)所對(duì)應(yīng)的行號(hào)或列號(hào)，在result_vector所對(duì)應(yīng)的行號(hào)或列號(hào)返回對(duì)應(yīng)的值。需要說明的是，lookup_value采用的向下查找的方法來匹配，如表 1所示：比如LOOKUP（4.19， A2：A6， B2：B6）和LOOKUP（5.00， A2：A6， B2：B6）兩個(gè)函數(shù)的返回結(jié)果都為橙色，第二個(gè)為橙色的原因是，在 A 列中查找 5.00，與接近它的最小值 （4.19） 匹配，然后返回 B 列中同一行內(nèi)的值。

2.3.2 Lookup實(shí)現(xiàn)精確查找：

但是，正是由于lookup的這種向下查找的特性，是lookup無法實(shí)現(xiàn)精確匹配，要解決lookup的精確匹配，可以利用的lookup無法對(duì)公式報(bào)錯(cuò)信息“#DIV/0”進(jìn)行匹配，設(shè)計(jì)思路如下：

將vlookup的參數(shù)2（lookup_vector）設(shè)置為多條件匹配區(qū)域，在成績(jī)表中的不同的科目有不同的科目代碼，比如1級(jí)科目代碼為15，二級(jí)科目代碼為65，故而可以將條件區(qū)域設(shè)置為：

（成績(jī)表！$A$2：$A$49=成績(jī)查詢！$C$3）*（成績(jī)表！$C$2：$C$49=65）

兩個(gè)括號(hào)表示兩個(gè)條件：條件1中“成績(jī)查詢！$C$3”是考生輸入學(xué)號(hào)單元格，“成績(jī)表！$A$2：$A$49=成績(jī)查詢！$C$3”意思是，在成績(jī)表中的所有學(xué)號(hào)與輸入學(xué)號(hào)匹配，該運(yùn)算的結(jié)果是一組有“0”或“1”構(gòu)成的數(shù)組數(shù)據(jù)；條件2“成績(jī)表！$C$2：$C$49=65”表示科目是二級(jí)的科目，該表達(dá)式運(yùn)算結(jié)果也是一組“0”或“1”的數(shù)組數(shù)據(jù)。條件1和條件2相乘也是“0”或“1”的數(shù)組數(shù)據(jù)，只有兩個(gè)條件都滿足相乘的結(jié)果也為1，即是同時(shí)兩個(gè)條件都成立。

查找成績(jī)的原理是，lookup函數(shù)在兩組相乘結(jié)果中，一組由“0”或“1”的數(shù)組數(shù)據(jù)中找1得到行號(hào)，通過該行號(hào)在成績(jī)列中返回成績(jī)值作為函數(shù)的運(yùn)算結(jié)果，函數(shù)表達(dá)式簡(jiǎn)化如下所示：

=LOOKUP（1，（條件1）*（條件2），成績(jī)列）

實(shí)際運(yùn)行后發(fā)現(xiàn)并不能實(shí)現(xiàn)精確的查找，是因?yàn)関lookup是向下匹配查找，故而會(huì)出現(xiàn)隨機(jī)返回結(jié)果的情況。筆者經(jīng)過分析發(fā)現(xiàn)，vlookup函數(shù)對(duì)于函數(shù)出錯(cuò)值會(huì)忽略跳過，利用該特性可以讓數(shù)字0除“（條件1）*（條件2）”。0除以數(shù)組數(shù)據(jù)中為“0”的數(shù)，報(bào)“#DIV/0”錯(cuò)誤（即分母不能為零的錯(cuò)誤）；而數(shù)組數(shù)據(jù)中結(jié)果為1的被0除后，結(jié)果為0，也是一組數(shù)字中唯一0，其他全部報(bào)“#DIV/0”錯(cuò)誤。用lookup在函數(shù)查找數(shù)字1從而實(shí)現(xiàn)精確查找。

=LOOKUP（1，0/（（成績(jī)表！$A$2：$A$49=成績(jī)查詢！$C$3）*（成績(jī)表！$C$2：$C$49=15）），成績(jī)表！$E$2：$E$49）

該函數(shù)內(nèi)部一共有3個(gè)參數(shù)：如果查詢科目為2級(jí)，只需要將條件2中科目代碼修改為65即可即可；參數(shù)3為查詢結(jié)果返回區(qū)域，如要查詢返回該學(xué)生姓名，該參數(shù)參數(shù)設(shè)置為姓名所在區(qū)域即可。

利用lookup函數(shù)可以高效快速實(shí)現(xiàn)多條件查詢匹配問題，結(jié)構(gòu)清晰易懂，而且運(yùn)行效率高，如果有同時(shí)新增條件時(shí)，只需在第二個(gè)參數(shù)中乘入新條件即可。

需要注意的是，為邏輯清晰建議各個(gè)條件用括號(hào)分割，多條件最外層一定要記得加括號(hào)用0除，否則會(huì)出現(xiàn)查找無結(jié)果的情況。

3 安全性輸出設(shè)置

使用函數(shù)設(shè)計(jì)實(shí)現(xiàn)查詢系統(tǒng)后，為了防止他人意外重寫公式導(dǎo)致二次錯(cuò)誤，對(duì)公式進(jìn)行隱藏；或考生禁止查看或修改原始全體考生成績(jī)表[3]，對(duì)Excel工作薄和工作表設(shè)置加密保護(hù)，通過以上方法對(duì)

Excel實(shí)現(xiàn)封裝，具體的做法是：

①美化查詢窗口，做到美觀大方、清晰易懂，容易上手操作！

②隱藏成績(jī)工作表，同時(shí)隱藏查詢工作表中多余的行或列，只保留成績(jī)查詢的窗口！

③對(duì)工作薄、工作表進(jìn)行加密保護(hù)，防止非管理員對(duì)表結(jié)構(gòu)或成績(jī)表內(nèi)容進(jìn)行修改！

④對(duì)查詢窗口的區(qū)域鎖定與限制，設(shè)置C3單元格允許編輯，實(shí)現(xiàn)輸入學(xué)號(hào)，其他區(qū)域鎖定，不可編輯，不可查看公式。[4]

4 討論

通過Excel兩種函數(shù)實(shí)現(xiàn)成績(jī)查詢，以Excel自身為數(shù)據(jù)源查詢成績(jī)?yōu)榘咐?，初步探討了利用office軟件，如何在日常辦公中對(duì)個(gè)人信息進(jìn)行安全保護(hù)，讓學(xué)校的教務(wù)工作人員在日常工作中能夠有方法可參考，從學(xué)生個(gè)人信息保護(hù)的角度著手。以此來提高大家對(duì)信息保護(hù)的意識(shí)。

參考文獻(xiàn)

[1] JOHN WALKENBACH.Excel 2013bible[M].Wiley，2013.

[2] 錢平生. 基于Excel函數(shù)實(shí)現(xiàn)學(xué)生成績(jī)有效管理[J]. 軟件，2013，（06）：115-117.

[3] 徐慧. 基于Excel的平時(shí)成績(jī)查詢系統(tǒng)設(shè)計(jì)及實(shí)現(xiàn)[J]. 軟件導(dǎo)刊，2014，（04）：74-76.

[4] 于偉昌， 蘇慧紅. 運(yùn)用Excel生成考試質(zhì)量分析報(bào)告和成績(jī)數(shù)據(jù)庫的理論與實(shí)踐[J]. 現(xiàn)代情報(bào)， 2005， 25（8）：186-189.