人事資料分析.doc

1、 示例說明在如圖6所示的某公司人事資料表中，除了編號、員工姓名、身份證號碼以及參加工作時間為手工添入外，其余各項均為用函數(shù)計算所得。 圖6在此例中我們將詳細(xì)說明如何通過函數(shù)求出：（1）自動從身份證號碼中提取出生年月、性別信息。（2）自動從參加工作時間中提取工齡信息。2、身份證號碼相關(guān)知識在了解如何實現(xiàn)自動從身份證號碼中提取出生年月、性別信息之前，首先需要了解身份證號碼所代表的含義。我們知道，當(dāng)今的身份證號碼有15/18位之分。早期簽發(fā)的身份證號碼是15位的，現(xiàn)在簽發(fā)的身份證由于年份的擴(kuò)展（由兩位變?yōu)樗奈唬┖湍┪布恿诵灤a，就成了18位。這兩種身份證號碼將在相當(dāng)長的一段時期內(nèi)共存。兩種身份證號碼的含義如下：（1）15位的身份證號碼：16位為地區(qū)代碼，78位為出生年份(2位)，910位為出生月份，1112位為出生日期，第1315位為順序號，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。（2）18位的身份證號碼：16位為地區(qū)代碼，710位為出生年份(4位)，1112位為出生月份，1314位為出生日期，第1517位為順序號，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。18位為效驗位。3、 應(yīng)用函數(shù)在此例中為了實現(xiàn)數(shù)據(jù)的自動提取，應(yīng)用了如下幾個Excel函數(shù)。（1）IF函數(shù)：根據(jù)邏輯表達(dá)式測試的結(jié)果，返回相應(yīng)的值。IF函數(shù)允許嵌套。語法形式為：IF（logical_test, value_if_true,value_if_false）（2）CONCATENATE：將若干個文字項合并至一個文字項中。語法形式為：CONCATENATE(text1,text2)（3）MID：從文本字符串中指定的起始位置起，返回指定長度的字符。語法形式為：MID(text,start_num,num_chars)（4）TODAY：返回計算機系統(tǒng)內(nèi)部的當(dāng)前日期。語法形式為：TODAY（）（5）DATEDIF：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。語法形式為：DATEDIF(start_date,end_date,unit)（6）VALUE：將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。語法形式為：VALUE(text)（7）RIGHT：根據(jù)所指定的字符數(shù)返回文本串中最后一個或多個字符。語法形式為：RIGHT(text,num_chars)（8）INT：返回實數(shù)舍入后的整數(shù)值。語法形式為：INT(number)4、 公式寫法及解釋（以員工Andy為例說明）說明：為避免公式中過多的嵌套，這里的身份證號碼限定為15位的。如果您看懂了公式的話，可以進(jìn)行簡單的修改即可適用于18位的身份證號碼，甚至可適用于15、18兩者并存的情況。（1）根據(jù)身份證號碼求性別=IF(VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2),女,男)公式解釋：a. RIGHT(E4,3)用于求出身份證號碼中代表性別的數(shù)字，實際求得的為代表數(shù)字的字符串b. VALUE(RIGHT(E4,3)用于將上一步所得的代表數(shù)字的字符串轉(zhuǎn)換為數(shù)字c. VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2用于判斷這個身份證號碼是奇數(shù)還是偶數(shù)，當(dāng)然你也可以用Mod函數(shù)來做出判斷。d. =IF(VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2),女,男)及如果上述公式判斷出這個號碼是偶數(shù)時，顯示女，否則，這個號碼是奇數(shù)的話，則返回男。（2）根據(jù)身份證號碼求出生日期=CONCATENATE(19,MID(E4,7,2),/,MID(E4,9,2),/,MID(E4,11,2)公式解釋：a. MID(E4,7,2)為在身份證號碼中獲取表示年份的數(shù)字的字符串b. MID(E4,9,2) 為在身份證號碼中獲取表示月份的數(shù)字的字符串c. MID(E4,11,2) 為在身份證號碼中獲取表示日期的數(shù)字的字符串d. CONCATENATE(19,MID(E4,7,2),/,MID(E4,9,2),/,MID(E4,11,2)目的就是將多個字符串合并在一起顯示。（3）根據(jù)參加工作時間求年資（即工齡）=CONCATENATE(DATEDIF(F4,TODAY(),y),年,DATEDIF(F4,TODAY(),ym),個月)公式解釋：a. TODAY()用于求出系統(tǒng)當(dāng)前的時間b. DATEDIF(F4,TODAY(),y)用于計算當(dāng)前系統(tǒng)時間與參加工作時間相差的年份c. DATEDIF(F4,TODAY(),ym)用于計算當(dāng)前系統(tǒng)時間與參加工作時間相差的月份，忽略日期中的日和年。d. =CONCATENATE(DATEDIF(F4,TODAY(),y),年,DATEDIF(F4,TODAY(),ym),個月)目的就是將多個字符串合并在一起顯示。5. 其他說明在這張人事資料表中我們還發(fā)現(xiàn)，創(chuàng)建日期：31-05-2001時顯示在同一個單元格中的。這是如何實現(xiàn)的呢？難道是手工添加的嗎？不是，實際上這個日期還是變化的，它顯示的是系統(tǒng)當(dāng)前時間。這里是利用函數(shù) TODAY 和函數(shù) TEXT 一起來創(chuàng)建一條信息，該信息包含著當(dāng)前日期并將日期以dd-mm-yyyy