高校資助管理工作Excel常用函數比對方法

周恒勇

[摘 要] 根據國家對高校資助工作的新常態要求，結合三峽大學學生資助管理中心工作中的實際需要，介紹三峽大學資助常用函數及其操作，以探索高校資助工作常用函數的處理辦法。

[關 鍵 詞] 高校；資助；函數；比對

[中圖分類號] G647 [文獻標志碼] A [文章編號] 2096-0603（2018）19-0220-01

為實現中華民族的偉大復興，根據國家對資助工作的脫貧攻堅要求，精準資助在高校資助工作中日益突出。三峽大學本部普通本科在校學生22000多人，學校每年對困難學生的界定人數占學生總數的近40%，根據湖北省教育廳為學校劃撥的資助經費，學校資助和獎勵的學生人數達7000多人，每年全校2萬多普通本科學生要參加城鄉醫療保險和保險繳費。如何在有效時間內，在全校上萬的學生數據庫中找出需要資助的對象和數據，提高學校的辦事效率顯得尤為迫切。

根據近些年在資助工作中常用的函數運用，現將我校在資助管理工作Excel表常用函數比對方法進行歸納，以便于匯總、核實、統計數據。

一、不同“工作表”的數據比對函數

資助工作中經常涉及的一種情況是大批量的兩張不同表格數據之間的數據比對，要通過學號（或身份證號）找出兩張表格中數據相同或不同的數據。對此，我們運用以下兩個函數公式解決。

（一）不同工作表的數據比對函數公式

1.學號（10位數以內）的比對函數公式：“=if（countif（Sheet2！A：A，A1）=0，""，A1）”

2.身份證號比對函數公式：“=vlookup（A1，Sheet2！A：A，1，0）”

以上2個函數公式使用，都是在Sheet 1中“B1”格輸入公式，查找Sheet 2工作表A列與Sheet 1工作表中A列同行中相同數據（或需要導入的數據）。

（二）不同“工作表”學號（或身份證號）比對操作步驟

1.將需要比對的表格數據分別拷入Sheet 1、Sheet 2中；

2.將Sheet 1、Sheet 2中需要校對的數據項目拷入到A列；然后將兩個A列數據“文本化”，具體做法如下：

（1）將A列數據復制到新建文本文件中；（2）將A列改為文本格式（選定A列，點擊鼠標右鍵/設置單元格格式/數字，選“文本”）；（3）將文本文件中的內容復制，重新粘回到A列。

3.將Sheet 1中插入一列（B列），B列“設置單元格格式”設為“常規”格式。B1中輸入公式：

“=if（countif（Sheet2！A：A，A1）=0，""，A1）”（或“=vlookup（A1，Sheet2！A：A，1，0）”）；在Sheet 1中B1右下角雙擊“+”或下拉至全表最后一行。

4.再將B列比對數據結果“文本化”（重復步驟2）。

（三）不同“工作表”的合并函數公式

資助工作中我們經常涉及的第二種情況是大批量的兩張不同表格數據之間的數據合并，要通過學號（或身份證號）把一張表格的數據導入另一張表格。對此，我們運用以下函數公式可以解決：

“=Index（Sheet2！B：B，match（A1，Sheet2！A：A，）”

函數公式的含義：根據兩表中A列數據進行比對，把Sheet 2工作表中B列同行的數據導入到Sheet 1中的對應行。

函數計算結果及其含義：Sheet 1中的B列顯示出的就是在Sheet 2中B列同行導入數據；顯示為“#N/A”的，即是Sheet 2中A列沒有的數據。

二、函數比對操作中的注意事項

1.函數公式輸入狀態均為半角英文格式；

2.由于基層數據統計和輸入的方法可能不同，因此在復制數據時，一定要先做好比對數據的準確性校對工作：

（1）把所有數據改為文本格式（金額除外）；（2）替換掉數據中的“空白”信息；（3）檢查數據是否有重復、數據輸入是否有多一位或少一位的問題；身份證核對：是否有粘貼后，后三位顯示為“0”的現象（可以用查找“000”找出）。

三、同一個工作表內的相關數據比對函數

資助工作中我們經常涉及的第三種情況是大批量的同一張表格內的數據校對和提取，一般情況下，我們還是通過學號（或身份證號）完成。

（一）以下4個函數公式使用，都是在當前工作表中“B1”格輸入公式，查找當前工作表同行A列數據中所需信息

1.學號（10位數以內）重復核對函數公式：“=countif（A：A，A1）”，顯示結果1、2、3...分別表示同行A列中有一個、有二個、有三個......本數據。

2.身份證號從第1個到第20000個數據的重復核對函數公式：

“=sumproduct（n（A1：A20000=A1））”，顯示結果1、2、3...分別表示：同行A列中的第一個、第二個、第三個...本數據。

“=sumproduct（n（$A$1：$A$20000=A1））”，顯示結果1、2、3...分別表示：同行A列中有一個、有二個、有三個......本數據。

3.身份證號（數值個數）是15位、18位檢驗：“=or（len（a1）=15，len（a1）=18）”，公式含義：如果是15位或18位身份證號，就顯示正確（TRUE），否則顯示錯誤（FALSE）。

4.提取身份證號的出生日期公式：

=IF（LEN（A1）=15，"19"&MID（A1，7，2）&"-"&MID（A1，9，2）&"-"&MID（A1，11，2），IF（LEN（A1）=18，MID（A1，7，4）&"-"&MID（A1，11，2）&"-"&MID（A1，13，2），""））

公式的含義：如果是15位或18位身份證號，就顯示出生日期，否則不顯示。

（二）同行兩列數據一致性檢驗公式“=if（A1=B1，””，”錯誤”）”

在當前工作表中插入C列，“C1”格輸入公式，查找當前工作表A列與B列同行中所需核對數據信息是否一致。顯示結果含義：顯示為空的表示數據一致，顯示為“錯誤”的，表示數據不一致。