• 
    

    
    

      99热精品在线国产_美女午夜性视频免费_国产精品国产高清国产av_av欧美777_自拍偷自拍亚洲精品老妇_亚洲熟女精品中文字幕_www日本黄色视频网_国产精品野战在线观看 ?

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

      2017-10-21 12:33:17李金海
      關(guān)鍵詞:學(xué)號數(shù)組科目

      李金海

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

      猜你喜歡
      學(xué)號數(shù)組科目
      JAVA稀疏矩陣算法
      電腦報(2022年13期)2022-04-12 00:32:38
      2024年擬在河北招生的普通高校招生專業(yè)選考科目要求發(fā)布
      考試與招生(2022年2期)2022-03-18 08:10:02
      JAVA玩轉(zhuǎn)數(shù)學(xué)之二維數(shù)組排序
      電腦報(2020年24期)2020-07-15 06:12:41
      我們來打牌
      嘗試親歷的過程,感受探究的快樂
      下一代英才(酷炫少年)(2016年10期)2016-04-17 06:45:43
      學(xué)生學(xué)號的妙用
      尋找勾股數(shù)組的歷程
      與56號說再見
      VB數(shù)組在for循環(huán)中的應(yīng)用
      考試周刊(2012年88期)2012-04-29 04:36:47
      方城县| 南安市| 南投县| 汉中市| 鹤壁市| 金沙县| 吉水县| 图木舒克市| 金川县| 吴桥县| 吉木萨尔县| 乌苏市| 敦化市| 东阿县| 资兴市| 许昌市| 松溪县| 长沙县| 湖州市| 怀化市| 三台县| 灵川县| 沂南县| 漳州市| 上蔡县| 无锡市| 霍邱县| 五河县| 金华市| 正阳县| 怀来县| 涡阳县| 濉溪县| 古田县| 吉林省| 禄劝| 巧家县| 南皮县| 乌拉特中旗| 三河市| 赤峰市|