巧用Excel函数--减少数据冗余.docVIP

巧用Excel函数，减少数据冗余在当今的计算机应用领域中，微软的Office 2000以其阵容整齐、功能强大，成为广大用户最基本、最常用的软件。在Office 2000中，Excel则又是以制表、计算、数据分析功能见长。 在用Excel做数据处理时，经常需要录入有关人员的身份证号码、出生年月、性别、参加工作时间、工龄等信息。其实这些信息中有不少在身份证号码中已经包括了，大可不必重复输入。以下，通过Excel几个函数的使用介绍，来说明三点： （1）自动从身份证号码中提取出生年月、性别信息。 （2）自动从参加工作时间中提取工龄信息。 （3）根据工龄的大小来确定名次。 在下表中，录入的数据仅两列：身份证号码、参加工作时间，相应的数据类型为：文本、日期。其它数据如出生年月、性别、工龄、名次等均是利用Excel 2000的函数及公式表达式的运算，实现自动提取的。 ? A B C D E F 1 身份证号码 出生年月 性别 参加工作时间 工龄 名次 2 340111630306701 1963年03月06日 男 1980年8月 21 2 31978年10月29日 女 1997年3月 4 3 4 221217520419509 1952年04月19日 男 1970年5月 31 1 说明：上表中A、B、C、D、E、F……表示的是Excel工作表的列号，1、2、3、4……表示的是Excel工作表的行号。 一、身份证号码 众所周道，当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下： （1）15位的身份证号码：1~6位为地区代码，7~8位为出生年份(2位)，9~10位为出生月份，11~12位为出生日期，15位(即最后一位)为性别(奇数为男，偶数为女)。 ?（2）18位的身份证号码：1~6位为地区代码，7~10位为出生年份(4位)，11~12位为出生月份，13~14位为出生日期，17位(即倒数第二位)为性别(奇数为男，偶数为女)，18位(即最后一位)为效验位。 二、有关函数 要实现数据的自动提取，将用到以下的8个Excel函数,简介如下： （1）IF（logical_test, value_if_true,value_if_false）：根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套。 （2）LEN(text)：返回文本字符串中字符个数。 （3）CONCATENATE(text1,text2……)：将若干个文字项合并至一个文字项中。 （4）MID(text,start_num,num_chars)：从文本字符串中指定的起始位置起，返回指定长度的字符。 （5）MOD(number,divisor)：返回两数相除后的余数。 （6）TODAY（）：返回计算机系统内部的当前日期。 （7）YEAR（serial_number）：返回日期序列数对应的年份数。 （8）RANK（number,ref,order）：返回指定数字在一列数字中的排位。 三、公式表达式 （1）B2=IF(LEN(A2)=15,CONCATENATE(19,MID(A2,7,2),年,MID(A2,9,2),月,MID(A2,11,2),日),CONCATENATE(MID(A2,7,4),年,MID(A2,11,2),月,MID(A2,13,2),日)) （2）C2=IF(LEN(A2)=15,IF(MOD(MID(A2,15,1),2)=1,男,女),IF(MOD(MID(A2,17,1),2)=1,男,女)) （3）E2=YEAR(TODAY())-YEAR(D2) （4）F2=RANK(E2,$E$2:$E$4) 说明： （1）将上述四个公式表达式顺序输入至表格第二行的B2、C2、E2、F2单元格中，求出表中数据第一行的结果。 （2）分别移光标至B2、C2、E2、F2四个单元格的右下角，呈黑色“+”符号时，向下拖曳鼠标即可求出其它各单元格的值。 （3）在F2单元格的公式中，为了确保数据的比较范围在公式的复制中保持不变，采用了绝对引用——在单元格的行列标号前，均加上“$”符号。