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

周恒勇

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

[關(guān) 鍵 詞] 高校；資助；函數(shù)；比對

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

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

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

一、不同“工作表”的數(shù)據(jù)比對函數(shù)

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

（一）不同工作表的數(shù)據(jù)比對函數(shù)公式

1.學(xué)號（10位數(shù)以內(nèi)）的比對函數(shù)公式：“=if（countif（Sheet2！A：A，A1）=0，""，A1）”

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

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

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

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

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

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

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

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

4.再將B列比對數(shù)據(jù)結(jié)果“文本化”（重復(fù)步驟2）。

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

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

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

函數(shù)公式的含義：根據(jù)兩表中A列數(shù)據(jù)進行比對，把Sheet 2工作表中B列同行的數(shù)據(jù)導(dǎo)入到Sheet 1中的對應(yīng)行。

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

二、函數(shù)比對操作中的注意事項

1.函數(shù)公式輸入狀態(tài)均為半角英文格式；

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

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

三、同一個工作表內(nèi)的相關(guān)數(shù)據(jù)比對函數(shù)

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

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

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

2.身份證號從第1個到第20000個數(shù)據(jù)的重復(fù)核對函數(shù)公式：

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

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

3.身份證號（數(shù)值個數(shù)）是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位身份證號，就顯示出生日期，否則不顯示。

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

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