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