淺談Ｅｘｃｅｌ函數在學籍管理中的鏈接應用

學籍管理，最主要的就是對學生的相關數據信息進行規整、處理。如何找到一條迅速準確鏈接學生信息表單的途徑，成為管理好學生信息必須解決的問題。通過實踐，筆者發現Office辦公自動化軟件中的Excel函數功能完全可以勝任這種機械的重復勞動。下面就利用Excel函數在學生信息處理中的具體應用進行論述。

1 實際問題的提出

某日，教務處接到省教育廳下發文件，要求核對05級新生名單信息。這需要在數小時內從3 831個數據中準確核出3 306個數據，而且要將前者的考試號一一添加到后者當中。

為便于描述，暫且將下載的數據表作為母表，將實際在冊的05級學生信息表作為子表。

2 問題的解決

2.1 思路

如果進行手工處理，必然效率低下，且錯誤率較高；如果進行條件篩選，雖然在效率上有所改進，但工作量依然很大，很難在規定時間內完成任務。筆者利用Excel函數處理，利用身份證號的唯一性，將下載母表中的有效信息提取出來，重新按實際在校學生名單順序進行鏈接處理，這樣就得到了想要的結果。

2.2 具體方案

2.2.1 匹配定位

運用Match(lookup_value，lookup_array，match_type)進行匹配定位，其作用是通過匹配查找找到所需信息的單元格所在位置。主要參數：

lookup_value：要查找的值

lookup_array：要查找的區域

match_type：匹配形式(0精確查找，其他模糊查找)

以上搜索，如無匹配，則返回#N/A。

如S2=MATCH(子表!M2，'母表'!$F$2：$F$3832，0)（見圖1）

2.2.2 調整處理

將處理的數據編號與Excel固有的行序編號一一對應起來，便于后期數據處理。實際有數據列3 831行，加上標題行，所以選擇的有效區域是第2行到第3 832行。同理，每次操作時用到的行序號其實都要比實際數據行數要多一，即T2=S2+1（見圖2）。

2.2.3 索引

Index(array，row_num，col_num，)，其作用為返回數組中指定單元格數組的數值。參數：

array：單元格區域或數組

row_num：某行號

col_num：列號

根據已知位置，反饋出想要的內容。

本例中單元格選擇區域只是一列，故列號缺省（見圖3），如P=INDEX($A$1：$A$3832，T2)。

2.3 注意事項

2.3.1 關鍵字段的選取

關鍵字必須是各表中的具有唯一特性的字段。通過關鍵字段的橋梁作用，將各張數據表聯結起來。

考慮到身份證號在兩張數據表中都具有唯一性的特性，這個問題中就只能選擇該字段作為關鍵字。

2.3.2 引用

引用主要包含3類：絕對引用、相對引用，混合引用。絕對引用指在公式中引用的單元格地址是固定的。在公式復制到其他單元格時，引用的內容不會發生變化。換言之，就是將下載的數據段的在表格中的地址固化。相對引用指在公式中引用的單元格地址是相對地址。在公式復制到其他單元格時，它引用的內容會發生變化。如果沒有將下載的母表中數據段的地址固化，會導致數據溢出，索引數據不完全，進而會影響查找的準確性。混合引用就是以上兩者引用兼而有之。

在上述具體步驟中談及的“MATCH(實際名單!M2，'A表'!$F$2：$F$3832，0)”實際就是一個絕對引用，其目的就是為了將母表中的數據段的地址固化，使得match函數在運行時有址可循，有的放矢。而索引函數“index（）”卻使用了相對引用，目的也是顯而易見的，那就是在子表中的指針自動地由首行開始依次指向下個數據。在一般情況下，實際應用中主要使用的都是混合引用。

在實際操作中，一旦發生數據溢出問題，會出現錯誤提示符“#N/A”。反之，錯誤提示符“#N/A”也能在使用公式時給出相應提示。因此在出現“#N/A”時往往可能是由于引用方式應用不當造成的。筆者在處理本例時也曾有過類似的經歷。2.3.3 舉一反三

以上3條公式執行一次，僅僅只能判斷出一條數據是否找到。要使子表中所有數據都必須在母表里檢查一遍，需要挪動鼠標。將鼠標點選中第一個寫入公式的單元格，移到右下角，當出現“＋”時，雙擊鼠標左鍵，“一鍵”實現所有子表數據的查找匹配。

2.3.4 思考

盡管通過函數處理，將大部分數據都鏈接上了，但仍然有一些遺留問題。本例就留下有71條未能識別的數據。同時，在表格中顯示“#N/A”。這說明在數據處理過程中還是有一些不匹配的因素存在。

經分析，數據中出錯提示符號“#N/A”的主要原因是：（1）身份證號的15/18位匹配問題；（2）學生填寫身份證號時，出現的前后不符情況；（3）由于外省生源沒有統一時間上傳更新補錄新生名單；（4）預科、成教等學生沒有在網上顯示；（5）數據錄入格式不一致。

對于以上5類原因的解決，可以采取這樣的措施：（1）第1，2類是由于學生個人原因造成的，請學生再核實后即可解決；（2）第3，4類是客觀原因，通過核對錄取花名冊，也可清理出來，再進行數據鏈接時可以先將預科、成教等學生信息處理掉，以減輕后期核對工作負擔；（3）在進行數據錄入的過程中難免會出現數據格式不一致的錯誤，為了盡量修正數據，盡最大可能對數據進行必要的規范，可以采用功能強大的Excel數據透視功能對錄入數據進行實時監控，及時修正、規范數據；（4）優化參數配置，匹配定位時將“Match（）”函數中“lookup_array”設為“$X:$X”，即選中某一整列，作為查詢區間，這樣可以省略具體方案中的調整這一步驟，達到殊途同歸的效果；（5）數據前處理，即在進行數據鏈接之初，就對數據按關鍵字段進行必要的排序操作，以提高查找的效率。以學籍管理為例，進行數據前處理就很有必要。一般規模上萬的數據處理也應該進行數據前處理。

3 結語

綜上，在用Excel處理任何問題時也是開頭難，解決好了第一個，以后的就很好辦了。Excel批量化處理問題的優點在這里得到了全面的體現。但對于Excel函數的了解、應用還很不夠，要想解決好實際問題，還需要進行不斷地學習與探索。