|
使用Excel 管理人事信息，具有無須編程、簡便易行的特點，在各行各業(yè)中得到廣泛了應用，但人事數據信息與其它數據計算相比，存在著數據重復和不便統(tǒng)計的問題。只在身份證號中，就隱含著公民的大部分個人信息，有性別、出生年月日和證件辦理所在地。如何既盡可能減少數據錄入的工作量，又能提取統(tǒng)計出所需人事信息呢，下面我們就利用Excel 函數，來一步一步實現身份證信息的自動化統(tǒng)計。
一、創(chuàng)建工作簿：
用Excel創(chuàng)建一個身份證信息函數統(tǒng)計實例工作簿，在A1、B1、C1、D1、E1、F1中，分別填入姓名、身份證號碼、性別、出生日期、年齡、辦證縣市，自第2 行開始依次錄入員工的姓名和身份證號，身份證號所在B列要選用“文本”格式。示例設定共24名員工，即A2~A25。
在下列圖例中，我們?yōu)榱朔奖愦蠹易R別，把需要手工錄入的值字體設置為：”黑色”；用函數公式提取的值把它設置為：“藍色”；用函數公式所統(tǒng)計出的值，設置為：“紅色”。
二、提取性別：
根據現行的居民身份證號碼編碼規(guī)定，正在使用的18 位的身份證編碼。它的第17 位為性別(奇 數為男，偶數為女)，第18 位為效驗位。而早期使用的是15 位的身份證編碼，它的第15 位是性別(奇數為男，偶數為女)。
(1)函數分解
LEN 函數返回文本字符串中的字符數。 語法：LEN(text) Text 是要查找其長度的文本?？崭駥⒆鳛樽址M行計數。
MOD 函數返回兩數相除的余數。結果的正負號與除數相同。 語法：MOD(number,divisor) Number 為被除數；Divisor為除數。 MID 函數返回文本字符串中從指定位置開始的特定數目的字符，該數目由用戶指定。 語法：MID(text,start_num,num_chars) Text 為包含要提取字符的文本字符串；Start_num 為文本中要提取的第一個字符的位置。文本中第一個字符的start_num 為1 ，以此類推；Num_chars指定希望MID 從文本中返回字符的個數。
(2)實例分析
為了適應上述情況，必須設計一個能夠適應兩種身份編碼的性別計算公式，在C2 單元格中輸入“=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女"))”。回車后即可在單元格獲得該職工的性別，而后只要把公式拖動復制到C3、C4等單元格，即可得到其他職工的性別。
為了便于大家了解上述公式的設計思路，下面簡單介紹一下它的工作原理：該公式由三個IF 函數構成，其中“IF(MOD(MID(B2,15,1),2)=1,"男","女")”和“IF(MOD(MID(B2,17,1),2)=1,"男","女")”作為第一個函數的參數。公式中“LEN(B2)=15”是一個邏輯判斷語句，LEN 函數提取B2 等單元格中的字符長度，如果該字符的長度等于15， 則執(zhí)行參數中的第一個IF 函數，否則就執(zhí)行第二個IF 函數。
在參數“IF(MOD(MID(B2,15,1),2)=1,"男","女")”中。MID 函數從B2 的指定位置(第15 位)提取1 個字符，而MOD 函數將該字符與2 相除，獲取兩者的余數。如果兩者能夠除盡，說明提取出來的字符是0(否則就是1)。邏輯條件“MOD(MID(B2,15,1),2)=1”不成立，這時就會在D2 單元格中填入“女”，反之則會填入“男”。 如果LEN 函數提取的B2 等單元格中的字符長度不等于15， 則會執(zhí)行第2個IF函數。除了MID 函數從B2 的指定位置(第17 位，即倒數第2 位)提取1 個字符以外，其他運算過程與上面的介紹相同。
注意：錄入函數公式時所使用的 “，”“：”“；”全部為英文半角輸入。
三、提取出生日期
(1)函數分解
CONCATENATE 函數將幾個文本字符串合并為一個文本字符串。 語法：CONCATENATE(text1,text2,...) Text1,text2,...為1～30 個要合并成單個文本項的文本項。文本項可以為文本字符串、數字或對單個單元格的引用。
(2)實例分析
與上面思路基本相同，我們可以在D2 單元格中輸入函數公式=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),"年",MID(B2,9,2),"月",MID(B2,11,2),"日"),CONCATENATE(MID(B2,7,4),"年",MID(B2,11,2),"月",MID(B2,13,2),"日")) 回車，可得到A2員工出生日期，下拉C2單元格進行拖動復制，即可得到全部員工的出生年月日。
其中“LEN(B2)=15”仍然作為邏輯判斷語句使用，它可以判斷身份證號碼是15 位的還是18 位的，從而調用相應的計算語句。 對15 位的身份證號碼來說，左起第7 至12 個字符表示出生年、月、日，此時可以使用MID 函數從身份證號碼的特定位置，分別提取出生年、月、日。然后用CONCATENATE 函數將提取出來的文字合并起來，就能得到對應的出生年月日。公式中“19”是針對早期身份證號碼中存在2000 年問題設計的，它可以在計算出來的出生年份前加上“19”。對“18”位的身份證號碼不存在2000 年問題，公式中不用給計算出來的出生年份前加上“19”。
注意：CONCATENATE 函數和MID 函數的操作對象均為文本，所以存放身份證號碼的單元格必須事先設為文本格式，然后再輸入身份證號。
四、提取年齡
(1)函數分解：
YEAR用途：返回某日期的年份。其結果為1900 到9999 之間的一個整數。 語法：YEAR(serial_number) 參數：Serial_number 是一個日期值，其中包含要查找的年份。日期有多種輸入方式：帶引號的文本串(例如 "1998/01/30")、序列號(例如，如果使用 1900 日期系統(tǒng)則 35825 表示 1998 年 1 月 30 日)或其他公式或函數的結果 (例如 DATEVALUE("1998/1/30"))。
（2）實例分析：
，基本思路是用當前日期(NOW())，減去提取的身份證日期，用YEAR返回年份差，我們就可獲得年齡。基于此思路，我們在E2 單元格中輸入公式
=YEAR(NOW())-IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2)),CONCATENATE(MID(B2,7,4)))
其中“LEN(B2)=15”仍然作為邏輯判斷語句使用，它可以判斷身份證號碼是15 位的還是18 位的，從而調用相應的計算語句。 對15 位的身份證號碼來說，左起第7 至12 個字符表示出生年、月、日，此時可以使用MID 函數從身份證號碼的特定位置，分別提取出生年、月、日。然后用CONCATENATE 函數將提取出來的文字合并起來，就能得到身份證的出生年月日。
另外，對表中已經有出生日期的，我們可以用兩個簡便函數公式來計算年齡：
（1）根據當前日期和出生日期，自動計算年齡公式：
=SUM(YEAR(NOW())-YEAR(D2))
（2）由于跨年度的時候，我們可能不需要當前日期的，需手動輸入時間（如2007）計算年齡：
=2007-YEAR(D2)
注意：添加公式后把當前格設為：“常規(guī)”格式
五、提取證件辦理地：
公民身份證號的前6位是辦證地的城市代碼，通過它可以大略了解員工的籍貫和戶籍情況。下面我們可以通過網上公布的城市代碼和提取函數的使用，來實現證件辦理地的查詢：
(1) 函數分解：
VLOOKUP 用途：在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。當比較值位于數據表首列時，可以使用函數VLOOKUP 代替函數HLOOKUP。 語法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) 參數：Lookup_value為需要在數據表第一列中查找的數值，它可以是數值、引用或文字串。Table_array 為需要在其中查找數據的數據表，可以使用對區(qū)域或區(qū)域名稱的引用。Col_index_num 為table_array 中待返回的匹配值的列序號。Col_index_num為1 時，返回table_array 第一列中的數值； col_index_num 為2，返回table_array 第二列中的數值，以此類推。Range_lookup為一邏輯值，指明函數VLOOKUP 返回時是精確匹配還是近似匹配。如果為TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value 的最大數值；如果range_value 為FALSE， 函數VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值#N/A。
LEFT 或LEFTB 用途：根據指定的字符數返回文本串中的第一個或前幾個字符。此函數用于雙字節(jié)字符。 語法：LEFT(text，num_chars)或LEFTB(text， num_bytes)。 參數：Text 是包含要提取字符的文本串；Num_chars 指定函數要提取的字符數，它必須大于或等于0。Num_bytes按字節(jié)數指定由LEFTB 提取的字符數。
（2）實例分析：
基于VLOOKUP 和LEFT函數用途，我們的思路為，截取身份證號的前6位，與城市代碼（需另建）表中的代碼區(qū)域（如：城市代碼表中A2：An）比照查找，匹配后，返回城市名稱（如第二列 B）對應得值。得出公式：
=VLOOKUP(LEFT(B2,6),城市代碼!$A$2:$B$3525,2,FALSE)
這里的關鍵是建立城市代碼表，其位置、格式如下：
最新城市代碼可以從國家統(tǒng)計局網http://www.stats.gov.cn/tjbz/index.htm下載，復制后，直接粘入城市代碼表，利用我們上面學到的MID、VLOOKUP 、CONCATENATE函數，進行數據分類提取和提取后的合并，刪除空行。 并對完成的數值列要進行固化處理。
方法為： 在A列前新插入1列，復制數值列的數值，然后，點鼠標右鍵——選擇性粘貼——在粘貼對話框中選數值后確定——右鍵點新增列的首行——粘貼，即可。
函數公式中：“城市代碼!$A$2:$B$3525,2,FALSE”!，為跨頁計算符號；$，為絕對引用；“2”為返回第二列的對應數；“FALSE”為返回精確匹配值；如果，身份證號容錯的話，可省略或填入TRUE，也可用另這個函數“=LOOKUP(MID(B3,1,6),城市代碼!$A$1:$B$3525)” 則返回近似匹配值。
六、員工性別統(tǒng)計：
通過上述制作演示，我們已經在身份證號中提取出了相關的全部人事信息，但站在人事管理統(tǒng)計的角度，這還沒有達到匯總的要求，下面我們就用提取出的性別，對男女人數的進行統(tǒng)計：
(1)函數分解
COUNTIF用途：計算區(qū)域中滿足給定條件的單元格的個數。語法：COUNTIF(range,criteria) Range 為需要計算其中滿足條件的單元格數目的單元格區(qū)域；Criteria為確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。
(2)實例分析
根據COUNTIF函數用途和上面性別C列的24 條記錄，統(tǒng)計職工中男性和女性人數的方法是：選中單元格H2 (或其他用不上的空白單元格均可)，統(tǒng)計男性職工人數可以在其中輸入公式“="男"&COUNTIF(C2:C25,"男")&"人"”；接著選中單元格H3，在其中輸入公式“="女"&COUNTIF(C2:C25,"女")&"人"”?；剀嚭蠹纯傻玫健澳?9 人”、“女5 人”。
上式中C2:C25 是對“性別”列數據區(qū)域的引用，實際使用時必須根據數據個數進行修改?！澳小被颉芭眲t是條件判斷語句，用來判斷區(qū)域中符合條件的數據然后進行統(tǒng)計。“&” 則是字符連接符，可以在統(tǒng)計結果的前后加上“男”、“人”字樣，使其更具有可讀性。
七、年齡分段統(tǒng)計：
在人事管理工作中，統(tǒng)計分布在各個年齡段中的職工人數也是一項經常性工作?，F根據我們創(chuàng)建的人員信息
工作表的E2:E25 單元格存放職工的年齡，我們要以5 年為一段分別統(tǒng)計年齡小于20 歲、21 至25 歲之間，一直到56 至60 歲之間的各年齡段的人數，采用下面的操作方法。
(1)函數分解
FREQUENCY 函數以一列垂直數組返回某個區(qū)域中數據的頻率分布。 語法：FREQUENCY(data_array,bins_array)
Data_array 為一數組或對一組數值的引用，用來計算頻率。如果data_array 中不包含任何數值，函數FREQUENCY 返回零數組；Bins_array為間隔的數組或對間隔的引用，該間隔用于對data_array 中的數值進行分組。如果bins_array 中不包含任何數值，函數FREQUENCY 返回data_array 中元素的個數。
(2)實例分析
首先在工作表中找到空白的I列(或其他列)在 I 1填入年齡段，自I2 單元格開始依次輸入20、25、30 、35、40...60， 分別表示統(tǒng)計年齡小于20、21至25 之間、26 至30 之間等的人數。并在該列旁邊選中與年齡段相同個數的單元格，如J2：J10 準備存放各年齡段的統(tǒng)計結果。然后在編輯欄輸入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter 組合鍵即可在選中單元格中看到計算結果。
特別注意：計算結果為數組格式，不能改動，如年齡有變或誤操作后，可按Ctrl+Shift+Enter 組合鍵重新計算還原。
八、年齡排次查詢：
對我們查出的員工年齡，你想知道最大的是幾歲、其次是幾歲、最小的是幾歲嗎？當然在此24人的實例中，你看一眼可能就知道了，但如果人數在300、800、1000以上，恐怕就不好找了。現在我們就學習一種簡單方法，讓你想查第幾名，就查出第幾名。這在工資、成績統(tǒng)計等場合會很有用的，其操作方法如下。
(1) 函數分解
LARGE用途：返回某一數據集中的某個最大值。使用LARGE 函數可以查詢到數據集中特定位置上的數值。語法：LARGE(array,k) Array 為需要從中選擇第K 個最大值的數組或數據區(qū)域； K 為返回值在數組或數據單元格區(qū)域中的位置(從大到小排)。
SMALL用途：返回數據集中第k 個最小值，從而得到數據集中特定位置上的數值。 語法：SMALL(array，k) 參數：Array 是需要找到第k 個最小值的數組或數字型數據區(qū)域，K為返回的數據在數組或數據區(qū)域里的位置(從小到大)。
(2)實例分析
在我們的實例中E2:E25 區(qū)域存放著員工的年齡，首先在K列選取空白單元格K10中輸入公式“=LARGE(E2:E25,K4)”。其次是把K4 作為我們輸入名次變量的單元格，如果你在其中輸入1，公式就可以返回E2:E25 區(qū)域中第一大的數值；以此類推，當輸入24時，當然，就是最小的了。
如果我們需要從小到大，把最小的當作第一，則上述公式修改為“=SMALL(E2:E25,K4)”后，在K4 單元格中輸入1，就可以獲得E2:E25 區(qū)域中最小的數值。
為方便起見，你也可以給E2:E25 區(qū)域定義一個名稱“年齡”。然后把上述公式修改為“=LARGE(年齡,K4)”或“=SMALL(年齡,K4)”，可實現同樣效果。 定義方法為：用鼠標選定E2:E25 區(qū)域——點擊插入——名稱——定義，在彈出的對話中填入年齡，確定即可。
九、年齡排名統(tǒng)計:
根據實際需要，如果我們不滿足上面的單一查詢，要查看全員的年齡排名，那也不難，用下列方式其即可實現。
- （1）函數分解
RANK用途：返回一個數值在一組數值中的排位。 語法：RANK(number，ref，order) 參數：Number 是需要計算其排位的一個數字；Ref 是包含一組數字的數組或引用；Order 為一數字，指明排位的方式。如果order為0 或省略， 則按降序排列的數據清單進行排位。如果order 不為零，ref當作按升序排列的數據清單進行排位。
（2）實例分析
在實例中E2:E25 區(qū)域存放著員工的年齡，我們首先在L1輸入“年齡排名”，選L2單元格輸入公式“=RANK (E2,$E$2:$E$25)”。E2為需要排位的年齡，$E$2:$E$25是我們要絕對引用的年齡數組(其中如有非數值型參數將會被忽略)。這里我們省略order的參數，為降序排列；如果把公式改為“=RANK (E2,$E$2:$E$25,1)” 則為升序排列，那么，年齡最小的就排第一了。
另：由于，我們前面以定義了“$E$2:$E$25”的名稱為“年齡”，所以，把公式改為“=RANK (E2, 年齡)，可實現同樣結果。
注意：如果數據清單已經排過序了，則數值的排位就是它當前的位置；函數RANK 對重復數值的排位相同。但重復數的存在將影響后續(xù)數值的排位。如在一列整數中，若年齡31 出現兩次，其排位為7，則32 的排位則為9 ，就沒有排位為8名的數值了。
十、年齡條件查詢：
上面，我們已經介紹了三種年齡的查詢方式，但實際工作中，我們的需求還不止這些，比如要查詢32~50歲的人數或37~48歲的人數，上述方式就不能滿足了。下面我們就用一個新的函數即可來實現這一條件查詢。
（1）函數分解
DCOUNT函數用途：返回數據庫或列表的列中滿足指定條件并且包含數字的單元格數目。語法：DCOUNT(database,field,criteria) 參數：Database表示需要統(tǒng)計的單元格區(qū)域；Field表示函數所使用的數據列（在第一行必須要有標志項）；Criteria包含條件的單元格區(qū)域。
（2）實例分析：
在上面實例中E列為年齡，這里我們把C1:E25設為查詢區(qū)域（寓意是可以在多列中選出我們所需的E列）；把H14:I15作為條件區(qū)，錄入兩個年齡標志項和查詢條件，（切記在兩個區(qū)域中必須都包含“年齡”標志項）；在J15空單元格中輸入公式：=DCOUNT(C1:E25,"年齡",H14:I15)，確認后即可求出此條件查詢的結果。
另：如果將上述公式修改為：=DCOUNT(C1:E25,,H14:I15)，也可以達到相同目的。
|
