李含菁
【摘 要】文章主要介紹Excel軟件在高校戶籍管理中的應(yīng)用,重點介紹COUNTIF、VLOOKUP函數(shù)的應(yīng)用實例,合理使用EXCEL軟件的公式能極大提高戶籍管理工作效率,提升戶口遷移準(zhǔn)確率,減輕工作量,是高校戶籍管理好幫手。
【關(guān)鍵詞】excel 戶籍管理 應(yīng)用
高校學(xué)生戶口具有人數(shù)多、密度大、流動頻繁、情況復(fù)雜等特點。隨著高校逐年擴招,學(xué)生數(shù)量逐年遞增,許多綜合性高校每年的招生人數(shù)可達(dá)四五千人,畢業(yè)人數(shù)也是如此。如此多的學(xué)生戶籍在遷入、遷出學(xué)校的過程中會需要進(jìn)行大量的數(shù)據(jù)處理,擁有高效的辦公處理方式十分重要。EXCEL是戶籍?dāng)?shù)據(jù)承載主體,也是常用辦公軟件,在日常工作經(jīng)常使用。但是一般管理人員對其掌握也不是很精通。本文是根據(jù)日常工作經(jīng)驗使用EXCEL的幾個小技巧提出來供大家參考,幫助提升高校戶籍管理工作效率。
一、戶籍管理中需要大量簡單數(shù)據(jù)處理
隨著經(jīng)濟社會發(fā)展,高校的規(guī)模不斷擴大,截至2015年5月21日,全國高等學(xué)校共計2845所,全年研究生教育招生64.5萬人,在校研究生191.1萬人,畢業(yè)生55.2萬人。普通本??普猩?37.8萬人,在校生2625.3萬人,畢業(yè)生680.9萬人。2600多萬在校生,再加上教職員工,戶籍管理基數(shù)龐大。這些數(shù)據(jù)通常都以excel表格的形式儲存與處理,處理方式僅僅是插入、填寫、刪除、復(fù)制、粘貼等等簡單操作,但是數(shù)據(jù)量龐大,再加上數(shù)據(jù)需求和使用部門對數(shù)據(jù)要求和口徑差異,更加使戶籍?dāng)?shù)據(jù)管理工作冗雜。如何高效利用excel,有效提升戶籍管理手段成為大家關(guān)注的焦點。
二、Contif函數(shù)在比對戶口遷移表中的使用
在戶籍管理中有一類常見情況,從一個表中找出符合某些條件的學(xué)生名單,如果采用人工一一核對,效率很低也容易看錯,如果運用countif函數(shù)可以解決這個問題。
Countif函數(shù)是 Excel中對指定區(qū)域中符合指定條件的單元格計數(shù)的一個函數(shù),該函數(shù)的語法規(guī)則如下。
countif(range,criteria)
參數(shù)range: 要計算其中非空單元格數(shù)目的區(qū)域
參數(shù)criteria :以數(shù)字、表達(dá)式或文本形式定義的條件
通常它用來對符合條件的數(shù)量計數(shù),但是我們可以用它來查重。下面通過一個實例介紹該函數(shù)在高校戶籍管理管理中的具體用法。
筆者在工作中碰到過一個問題,2016年研究生畢業(yè)戶口處理中,就業(yè)辦提供有一份已遷出學(xué)生名單,需根據(jù)總表找出比對未遷出名單,已遷出名單118人,總名單699人。這是一個數(shù)據(jù)不是很大的工程,但是如果用傳統(tǒng)辦法手工一一比對,工作量還是很大的。采用countif函數(shù)能很快解決問題。
兩個表均包含學(xué)院、學(xué)號、姓名、身份證、單位名稱、所在地區(qū)、性別、學(xué)歷、專業(yè)、擴展項這幾個項目。由于身份證號碼的唯一性我們首先用身份證進(jìn)行篩選對比,首先我們建立一個新的Excel表格,避免破壞原始數(shù)據(jù),第一個工作簿內(nèi)拷入總表命名為總表,把已遷出表格拷入sheet2命名為已遷出。
使用公式如下:=IF(COUNTIF(已遷出!$D:$D,$D2)=1,"已遷出","未遷出"),這個公式表示如果在總表身份證數(shù)據(jù)里找已遷出表格里的身份證號碼,如果有,則輸出“已遷出”,否則“未遷出”。再下拉復(fù)制公式,通過這個功能,就可找出重復(fù)學(xué)生信息。
該函數(shù)的程序流程圖如下:
由于身份證號碼的唯一性,計數(shù)的結(jié)果為如果查到有一致的則唯一,如果找不到結(jié)果為零,這樣就可輕松把兩個表中重復(fù)的學(xué)生找出來。再通過排序或者篩選功能找出未遷出的名單以備使用。
為避免出錯,還可以用學(xué)生的學(xué)號作為處理項,進(jìn)行另外一次處理,兩個表進(jìn)行一次比對,找出異常項,修正,避免表格錄入錯誤導(dǎo)致出錯。
三、VLOOKUP函數(shù)在比對戶口遷移表中的使用
戶口管理工作中還有一類非常常見的工作是把一個表格中有的數(shù)據(jù)合并到另外一個表里。比如一個表里有每個學(xué)生的畢業(yè)高中學(xué)校,另外一個表里沒有,如果要一個一個找起來填進(jìn)去,費時費力還容易錯。這時候excel提供的VLOOKUP函數(shù)就能提供很大的幫助。
VLOOKUP函數(shù)是Excel中的一個縱向查找函數(shù),也是可以在戶籍管理中起到很大作用的函數(shù)。
該函數(shù)的語法規(guī)則如下。
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
參數(shù)lookup_value:要查找的值 ,可以是數(shù)值、引用或文本字符串
參數(shù)table_array:要查找的數(shù)值表區(qū)域
參數(shù)col_index_num:返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù)
參數(shù)range_lookup:模糊匹配/精確匹配 TRUE(或不填)/FALSE
下面我舉一個例子來介紹該函數(shù)的使用方法。
在處理2016年秋季高?;厩闆r統(tǒng)計表時,需要在學(xué)生基本情況表中補填學(xué)生的畢業(yè)高中項。這時作為校保衛(wèi)處管理人員手上有兩張表,一張是“學(xué)生基本情況表”,一張是各個學(xué)院匯總過來的“更新高中”的表,接下來用VLOOKUP函數(shù)迅速把“更新高中”表的高中信息插入學(xué)生基本情況表中,下面我以我校某籍學(xué)生的統(tǒng)計表為例介紹。
首先在“學(xué)生基本情況表”的sheet2拷入“更新高中”表并重命名為“更新高中”,在“學(xué)生基本情況表”中,新增一列“入學(xué)前就讀學(xué)校”,在第一個個格子里輸入公式=VLOOKUP(H4,更新高中!$H$2:$P$322,9,0)。H列儲存的是學(xué)生的身份證號碼,H4表示第一個學(xué)生的身份證;更新高中!$H$2:$P$322表示查詢區(qū)域為“更新高中”表格的第H列到P列第2行到第322行,加上$符號是表示絕對引用,可以使該公式下拉復(fù)制時相應(yīng)行、列號不變,這點非常重要;9,表示輸出這個檢索區(qū)域的第9列,即該學(xué)生“入學(xué)前就讀學(xué)?!?;0,表示精確匹配,這里必須要寫0或者FALSE,由于身份證號很長,如果不填或填了true模糊匹配會造成匹配錯誤,這點要特別注意。
該函數(shù)的程序流程圖如圖2。
接下來只要把這個表格的公式下拉到所有表格里即可復(fù)制公式,快速將學(xué)生畢業(yè)高中信息填寫到《學(xué)生基本情況表》中了。為避免出錯,還可以用學(xué)生的學(xué)號作為處理項,進(jìn)行另外一次處理,兩個表進(jìn)行一次比對,找出異常項,修正,避免表格錄入錯誤導(dǎo)致出錯。
總之,Excel是戶籍管理的主要工具,其合理有效使用必能大大提高戶籍管理工作效率。本文簡單介紹了countif和vlookup函數(shù)的使用經(jīng)驗,希望能夠給廣大高校戶籍管理工作者提供一些借鑒與幫助。
【參考文獻(xiàn)】
[1]邱靜.Excel 在學(xué)生戶籍?dāng)?shù)據(jù)采集與整合中的應(yīng)用[J].南京廣播電視大學(xué)學(xué)報,2007(2):68-70.