李金海

【中圖分類號】 TP317 【文獻標識碼】 A 【文章編號】 2236-1879（2017）12-0027-02

1 問題提出

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

1.1 Excel表格形式公示信息：

通過Excel文件形式將學生的成績或學籍信息整理，通過網絡平臺分發(fā)，讓學生自助通過網頁以附件形式查看；或打印全部需要公示或查詢的學生信息，粘貼公示欄學生查看自己信息。這兩種辦法是目前

各個單位普遍采用的方法，存在最大問題是，學生的個人信息甚至很多私密信息特別容易外漏，對學生的個人信息沒有進行有效的保護，存在的著嚴重的信息安全問題。

1.2 開發(fā)web在線查詢系統(tǒng)：

如果將問題委托IT部門（信息技術部門）解決，按需定制開發(fā)一套基于Internet的B/S web在線查詢系統(tǒng)，筆者本人早年系程序員出生，熟知開發(fā)程序流程之繁瑣，且不說開發(fā)的技術難度，軟件調試運維

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

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

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

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

通過以實際學校學生期末計算機成績查詢?yōu)槔?，分別采用Excel函數(shù)方法和來實現(xiàn)。Excel函數(shù)實現(xiàn)相對簡單，主要使用vlookup（）函數(shù)和lookup函數(shù)來實現(xiàn)成績查詢。

2 設計實現(xiàn)

2.1 界面設計：

將查詢窗口和具有所有學生的成績表（成績表）分別放在兩個工作表中，為了保證查詢成績的唯一性，我們設定查詢關鍵字為考生的學生證號（學號），成績查詢界面設計如圖 1所示：

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

2.2.1 認識vlookup（）函數(shù)：

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

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

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

參數(shù)解釋：

① $C$3：為輸入學號所在單元格

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

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

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

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

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

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

2.3.1 Lookup的特點：

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

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

本案例重點使用向量的語法形式來解決問題，向量語法基本結構：

=LOOKUP（lookup_value， lookup_vector， [result_vector]）

參數(shù)解釋：

①lookup_value：向量的搜索值，如學號。

②lookup_vector：向量查詢區(qū)域，如學號列。

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

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

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

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

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

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

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

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

=LOOKUP（1，（條件1）*（條件2），成績列）

實際運行后發(fā)現(xiàn)并不能實現(xiàn)精確的查找，是因為vlookup是向下匹配查找，故而會出現(xiàn)隨機返回結果的情況。筆者經過分析發(fā)現(xiàn)，vlookup函數(shù)對于函數(shù)出錯值會忽略跳過，利用該特性可以讓數(shù)字0除“（條件1）*（條件2）”。0除以數(shù)組數(shù)據(jù)中為“0”的數(shù)，報“#DIV/0”錯誤（即分母不能為零的錯誤）；而數(shù)組數(shù)據(jù)中結果為1的被0除后，結果為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ù)內部一共有3個參數(shù)：如果查詢科目為2級，只需要將條件2中科目代碼修改為65即可即可；參數(shù)3為查詢結果返回區(qū)域，如要查詢返回該學生姓名，該參數(shù)參數(shù)設置為姓名所在區(qū)域即可。

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

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

3 安全性輸出設置

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

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

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

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

③對工作薄、工作表進行加密保護，防止非管理員對表結構或成績表內容進行修改！

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

4 討論

通過Excel兩種函數(shù)實現(xiàn)成績查詢，以Excel自身為數(shù)據(jù)源查詢成績?yōu)榘咐醪教接懥死胦ffice軟件，如何在日常辦公中對個人信息進行安全保護，讓學校的教務工作人員在日常工作中能夠有方法可參考，從學生個人信息保護的角度著手。以此來提高大家對信息保護的意識。

參考文獻

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

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

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

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