Excel兩種函數(shù)實現(xiàn)信息查詢的比較

李金海

【中圖分類號】 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.