好吧,我想说的这是一个标题党,主要是讲通过Excsl的函数来处理一些简单数据排列问题
选取1000个身份证号码,提取号码中的男女性别,生肖,星座,出生年月和生日信息(身份证号码自己生成)
看到问题后我首先遇到的是1000个身份证号码该如何生成,其次才是如何去筛选信息,如果是你,你会怎么怎么想呢?先想想在看下面我的思路.....
当然首先的想法是到网上搜索有没有1000个现成的身份证号码,当然没有,有也不要去用....
其次是去了解身份证号码的组成部分,身份证号码主要是由18位数字组成(含有特殊字母X的就不例入考虑中,不要把问题复杂化了),是由6位地区号码+8位数出生年月日+4位数校验码组成。其中表示男女性别的号码是第17位数,奇数为男,偶数为女。
明白了身份证号码组成的规律,首先要提取的是6位数的地区号码,这个网上就有,直接拿来用,随便选取1000个地区码就可以了(我选的是前1000个),就不再赘述了,需要随机生成的是1000个生日日期和4位数的校验码。
随机生成8位数的出生年月日。我设定了一个期限1991年5月31日到2011年5月1日。在这里讲到我学到的一个函数,rand()函数,是将的一个随机生成数字的函数,我们要在指定日期内随机生成一个日期,那就是=text(rand()*("1991-5-31"-"2011-5-1")+"2011-5-1","yyyymmdd")。来解释下着两个函数,首先我们要提前一个日期,可以用时间函数date函数,这里为什么要用text来,我也不是很清楚。需要以后来回答这个问题,我只能说的是解释清楚这个函数的意义吧。text函数的格式是=TEXT(value,format_text) value是数值,或者选取的数据源;format_text是只单元格显示的格式。简单的说,语法结构可以理解为:TEXT(数值,单元格格式)
我们要选取的数据源是随机日期,单元格格式是8位数的出生年月日“yyyymmdd”。rand()*(b-a)+a;的函数意思是在b到a中随机生成一个数字。
按照这个函数填充下去,就随机生成了1000个出生在1991-05-31到2011-5-1之间的出生年月日号码。
同理随机生成4位数的校验码如下
方法一:=int(rand()*(1000-9999)+9999)
方法二:=round((rand()*(1000-9999)+9999),0)
这两个方法都是可以随机筛选出4位数的校验码的(不含特殊号)
通过上述的行动把这些数据基本上生成出来,通过=A1&B1&C1;把数据整合到一个单元里,复制,选择性粘贴为数值,1000合理的身份证号码终于生成了!(做到这一步真的很辛苦啊···)
身份证号码出来了,其他的就好做多了
我们首先来提取身份证里面的男女性别,MID是提起MID(text, start_num, num_chars),text是字符,start_num是字符的位置,num_chars是字符的长度,所以=mid(A1,17,1)提取的数字就是我们需要判断奇数偶数的数字。mod这个应该很熟悉了就是取余数的函数了=MOD(除数,被除数);可以得到的函数是=mod(B1,2);余数出来了,奇数是男,偶数是女,怎么样显示呢,=IF(C1=1,"男",“女”)。合并函数就是=IF(MOD(MID(A2,17,1),2)=1,"男","女")如图
好吧,下面的就不在一个一个的解释函数的原理,一来我也没有理解透彻,二来我准备把函数的功能做一个详细的归纳
选择G1单元格,在G1中输入=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(TODAY())-DATEDIF(E2,TODAY(),"y")-4,12)+1,1),回车,就可以看到生肖了,用填充手柄把剩余的单元格都填充好。
选择H1单元格,在H1中输入=IF(AND(F1>="3-21",F1<="4-19"),"白羊座",IF(AND(F1>="4-20",F1<="5-20"),"金牛座",IF(AND(F1>="5-21",F1<="6-21"),"双子座",IF(AND(F1>="6-22",F1<="7-22"),"巨蟹座",IF(AND(F1>="7-23",F1<="8-22"),"狮子座",IF(AND(F1>="8-23",F1<="9-22"),"处女座",IF(AND(F1>="9-23",F1<="10-23"),"天秤座",IF(AND(F1>="10-24",F1<="11-21"),"天蝎座",IF(AND(F1>"11-22",F1<="12-21"),"射手座",IF(AND(F1>="12-22",F1<="1-19"),"摩羯座",IF(AND(F1>="1-20",F1<="2-18"),"水瓶座",IF(AND(F1>="2-19",F1<="3-20"),"双鱼座")))))))))))),回车,就可以看到星座了,用填充手柄把剩余的单元格都填充好。现在表格就做好了。
后面写到不是很顺畅,内容解释的也不清楚,说明我也没有完全懂,需要好好的学习和练习把这些东西弄懂,操作熟练!
最后欢迎大家点评下