用EXCEL统计各分数段人数
前面我们介绍了Excel常用函数的功能和使用方法，现在我们学以致用，介绍一系列用这些函数实现的数据统计实例解析。今天介绍教师最常用的各学科相应分数段学生人数的统计。
教师常常要统计各学科相应分数段的学生人数，以方便对考试情况作全方位的对比分析。在Excel中，有多种函数可以实现这种统计工作，笔者以图1所示的成绩表为例，给出多种统计方法。文章末尾提供.xls 文件供大家下载参考。
文章导读：
方法一：用COUNTIF函数统计
这是最常用、最容易理解的一种方法，我们用它来统计“语文”学科各分数段学生数。
如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们也可以用COUNTIF函数来统计各等级的学生人数。
方法二：用DCOUNT函数统计
这个函数不太常用，但用来统计分数段学生数效果很不错。我们用它统计“数学”学科各分数段学生数。
方法三：用FREQUENCY函数统计
这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。
方法四：用SUM函数统计
我们知道SUM函数通常是用来求和的，其实，他也可以用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。
（图1图片较大，请拉动滚动条观看）
方法一：用COUNTIF函数统计
这是最常用、最容易理解的一种方法，我们用它来统计“语文”学科各分数段学生数。函数功能及用法介绍
①分别选中C63、C67单元格，输入公式：=COUNTIF(C3:C62,"<60")和=COUNTIF(C3:C62,">=90")，即可统计出“语文”成绩“低于60分”和“大于等于90”的学生人数。
②分别选中C64、C65和C66单元格，输入公式：
=COUNTIF(C3:C62,">=60")-COUNTIF(C3:C62,">=70")、
=COUNTIF(C3:C62,">=70")-COUNTIF(C3:C62,">=80")和
=COUNTIF(C3:C62,">=80")-COUNTIF(C3:C62,">=90")，即可统计出成绩在60-69分、70-79分、80-89分区间段的学生人数。
注意：同时选中C63至C67单元格，将鼠标移至C67单元格右下角，成细十字线状时，按住左键向右拖拉至I列，就可以统计出其它学科各分数段的学生数。
如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们可以用COUNTIF函数来统计各等级的学生人数
如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们可以用COUNTIF函数来统计各等级的学生人数。
①在K64至K67单元格中，分别输入成绩等级字符（参见图2）。
②选中L64单元格，输入公式：=COUNTIF($L$3:$L$62,K64)，统计出“优秀”的学生人数。
③再次选中L64单元格，用“填充柄”将上述公式复制到L65至L67单元格中，统计出其它等级的学生人数。
上述全部统计结果参见图1。
（图片较大，请拉动滚动条观看）
方法二：用DCOUNT函数统计
这个函数不太常用，但用来统计分数段学生数效果很不错。我们用它统计“数学”学科各分数段学生数。
①分别选中M63至N72单元格区域（不一定非得不这个区域），输入学科名称（与统计学科名称一致，如“数学”等）及相应的分数段（如图2）。
②分别选中D63、D64……D67单元格，输入公式：=DCOUNT(D2:D62,"数学",M63:N64)、
=DCOUNT(D2:D62,"数学",M65:N66)、=DCOUNT(D2:D62,"数学",M67:N68)、=DCOUNT(D2:D62,"数学",M69:N70)、=DCOUNT($D$2:$D$62,"数学",M71:N72)，确认即可。
注意：将上述公式中的“DCOUNT”函数换成“DCOUNTA”函数，同样可以实现各分数段学生人数的统计。
方法三：用FREQUENCY函数统计
这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。函数功能及用法介绍
①分别选中O64至O67单元格，输入分数段的分隔数值（参见图2）。
②同时选中E63至E67单元格区域，在“编辑栏”中输入公式：=FREQUENCY(E3:E62,$O$64:$O$67)，输入完成后，按下“Ctrl+Shift+Enter”组合键进行确认，即可一次性统计出“英语”学科各分数段的学生人数。
注意：①实际上此处输入的是一个数组公式，数组公式输入完成后，不能按“Enter”键进行确认，而是要按“Ctrl+Shift+Enter”组合键进行确认。确认完成后，在公式两端出现一个数组公式的标志“{}”（该标志不能用键盘直接输入）。②数组公式也支持用“填充柄”拖拉填充：同时选中E63至E67单元格区域，将鼠标移至E67单元格右下角，成细十字线状时，按住左键向右拖拉，就可以统计出其它学科各分数段的学生数。
方法四：用SUM函数统计
我们知道SUM函数通常是用来求和的，其实，他也可以用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。函数功能及用法介绍
①分别选中P64至P69单元格，输入分数段的分隔数值（参见图2）。
②选中F63单元格，输入公式：=SUM(($F$3:$F$62>=P64)*($F$3:$F$62 ③再次选中F63单元格，用“填充柄”将上述公式复制到F64至F67单元格中，统计出其它各分数段的学生人数。 注意：用此法统计时，可以不引用单元格，而直接采用分数值。例如，在F64单元格中输入公式：=SUM(($F$3:$F$62>=60)*($F$3:$F$62<70))，也可以统计出成绩在60-69分之间的学生人数。 注意：①为了表格整体的美观，我们将M至P列隐藏起来：同时选中M至P列，右击鼠标，在随后出现的快捷菜单中，选“隐藏”选项。 用EXCEL 2003创建学生成绩统计表 小技巧 （09管理二班吴彬） 老师们是不是在录入选择题时候，在键盘不同区域按ABCD时候会觉得很不方便呢？ 这里我有一个自己发现的，个人觉得在录入时候会比较省力的方法。现在与老师分享。 一：快速录入选择题 既然录入属于键盘不同区域的ABCDE会很费力，我们可以尝试将ABCDE的输入，替换为12345，然后再利用excel2003中文字替换功能将12345重新改换为ABCDE。因为12345在键盘上位置较为集中，录入时，可以大大节省手指在键盘上移动，以及寻找字母所需要的时间。 录入后效果如下： 接着将excel中数字12345依次替换为ABCD。可这时会发现一个问题。由于文字替换的不定向性，学生学号中的数字也会被替换为 字母。造成如下效果： 解决办法就是，新建一个空白工作表： 然后将学号全部剪切到新的工作表中： 剪切 这样，没有了学号的干扰，我们便可以使用excel2003中的文 字替换功能，将数字替换为字母： 然后再替换窗口中，查找内容窗格中输入1，替换为窗格中输入A ，然后点击全部替换。得到如下效果： 输入数字 输入字母 最后一步，将刚刚新建工作表中的学号剪切回来就好啦。 二：将分数低于60 分的单元格用红色突出显示。 操作步骤： ①选定单元格C2：E14，作为设置对象（可以直接用鼠标拖拽，从 C2拖拽到E14，直至看到所有成绩区域变为蓝色）。 ②打开【格式】菜单，选择【条件格式】命令，出现对话框，选择“单 元格数值”“小于”“60”，单击【格式】按钮，字体颜色选为“红色”，然后单击【确定】按钮。 ③得到如下效果： 在EXCEL中利用身份证号码计算性别 原理： 15位身份证，看最后一位，奇男偶女；18位的，看第17位数，也是奇男偶女。 公式内的“B2”代表的是输入身份证号码的单元格。 方法一： =IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女")) 公式含义： 如果B2单元格中式15位的身份证号，则显示IF(MOD(MID(B2,15,1),2)=1,"男","女")的计算结果，否则，显示IF(MOD(MID(B2,17,1),2)=1,"男","女")的计算结果。 方法二： 18位身份证号码中，第15～17位为顺序号，奇数为男，偶数为女。 将光标定位在“性别”单元格中，然后在单元格中输入函数公式：=IF(VALUE(MID(B2,15,3))/2=INT(VALUE(MID(B2,15,3))/2),"女","男") 公式含义： ①函数公式中，MID(D2,15,3)的含义是将身份证中的第15～17位提取出来。 ②VALUE(MID(D2,15,3))的含义是将提取出来的文本数字转换成能够计算的数值。 ③VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2)的含义是判断奇偶。(“INT”是取整函数，如果是偶数，则前后相等；如果是奇数，则前后不相等。) ④=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),"女","男")的含义是若是“偶数”就填写“女”，若是“奇数”就填写“男”。 excel中用身份证号码生成性别、出生日期、计算年龄 (2010-06-23 22:28:15) 转载 标签： 杂谈 excel中用身份证号码生成性别、出生日期、计算年龄 从身份证号码中自动生成性别和生日 生成性别：（其中B2是身份证号码所在列） 一性别双击性别所在列的第二行，然后输入下面公式，然后按ENTER键；再利用下拉方式将公式复制到该列的其他行中即可 1=CHOOSE(MOD(IF(LEN(B2)=18,MID(B2,17,1),IF(LEN(B2)=15,RIGHT(B2,1),"")),2)+1,"女","男") 2=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女") 3=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女")) 二出生日期提取出生日期：（其中B2是身份证号码所在列） 双击出生日期所在列的第二行，然后输入下面公式，然后按ENTER键；再利用下拉方式将公式复制到该列的其他行中即可 =DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2)) 三计算年龄：（其中C3是出生日期所在列） 双击年龄所在列的第二行，然后输入下面公式，然后按ENTER键；再利用下拉方式将公式复制到该列的其他行中即可 =YEAR(NOW())-YEAR(C3) Excel自动从身份证中提取生日性别 出处：天空软件作者：佚名日期：2009-09-16 每年新入学的一年级学生，都需要向上级教育部门上报一份包含身份证号、出生年月等内容的电子表格，以备建立全省统一的电子学籍档案。数百个新生，就得输入数百行相应数据，这可不是个轻松活儿。有没有什么办法能减轻一下输入工作量、提高一下效率呢？其实，我们只需在Excel2003中将学生的身份证号完整地输入后，它就可以帮我们自动填好出生日期和性别。 现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。前6位数字是户籍所在地的代码，7－14位就是出生日期。第17位“2”代表的是性别，偶数为女性，奇数为男性。我们要做的就是把其中的部分数字想法“提取出来”。 STEp1，转换身份证号码格式 我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。 STEP2，“提取出”出生日期 将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。然后输入 “=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，你会发现在C2单元格内已经出现了该学生的出生日期。然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。如图1 。 图1 通过上述方法，系统自动获取了出生年月日信息 小提示：MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。 STEP3，判断性别“男女” 选中“性别”列的单元格，如D2。输入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”（注意如上）后回车，该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。如图2。 excel 怎样从身份证号码提取年龄和性别- [电脑应用技巧] 版权声明：转载时请以超链接形式标明文章原始出处和作者信息及本声明 https://www.doczj.com/doc/341704951.html,/logs/50218662.html 因为自己需要，在网上找来了这个教程，函数真是好用的东西。这个教程很详细，不过我偷懒，因为自己觉得只需要看公式，所以用红字标记方便自己。。。。 在EXCEL中如何利用身份证号码计算出生年月、年龄及性别 在学校的人事管理中经常会遇到需要统计教职工的年龄的问题，但案头的原始资料只有身份证号码，其实这足够了。在EXCEL中，引用其内置函数利用身份证号码达到此目的比较简单。1、身份证号码简介（18位）： 1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。 2、确定“出生日期”： 18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄” 应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。 ①正确输入了身份证号码。(假设在D2单元格中) ②将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式 “=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。 关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID （text,starl_num,num_chars）。 Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。 starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。 num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。 多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。确定“年龄”： Excel表格中根据身份证号码自动填出生日期、计算年龄18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2 中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)) 自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,"女","男") 15/18位都可以的公式：转换出生日期： =IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TEXT(MID(e2,7,6),"#-0 0-00")) 自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男")) 计算年龄（新旧身份证号都可以）： =IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2,9,2),107-MIDB(E2,7 ,2))) WPS表格提取身份证详细信息 前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。而我们单位员工人数众多，短时间内统计相关信息并且输入计算机几乎是不太可能的。幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。所以，干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。 身份证号放在A2单元格以下的区域。我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。由于现在使用的身份证有15位和18位两种。所以，在提取相关信息时，首先应该判断身份证号码的数字个数，然后再区别不同情况进行相关处理。 一、身份证号的位数判断 在B2单元格输入如下公式“=LEN($A2)”，回车后即可得到A2单元格身份证号码的数字位数，如图1所示。LEN($A2)公式的含义是求出A2单元格字符串中字符的个数。由于当初身份证输入时就是以文本形式输入的，所以用此函数正可以很方便地求到身份证号码的位数。 方法一： 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。 18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符（从左向右）。 对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取： 假如身份证号数据在A1单元格，在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7, 4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)) 回车确认即可。 如果只要“年-月”格式，公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A1,7,4)&"-"&MID(A1,11, 2)) 3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考： 说明：公式中的B2是身份证号 根据身份证号码求性别： =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男 "),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"女","男"),"身份证错")) 根据身份证号码求年龄： =IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B 2,7,4)),"身份证错")) 4.Excel表中用Year\Month\Day函数取相应的年月日数据; Excel表中身份证号码提取出生年月、性 别、年龄的使用技巧 excle中当一个序列号变更，下面序列号自动变更的方法。 浏览次数：298次悬赏分：0 |解决时间：2011-3-11 12:48 |提问者：kasure 问题补充： 比如我编制了序列号001，002，003。。。。，然后我要是中间插入一行，比如在002和003之间插入一行，我下面的编号都要变动，如何实现这样的功能？ 最佳答案 那我想知道如果你需要删除一行的话，下面的编号是否需要变动？如果都需要变动的话，你可以试试这样： 1、把序号列的单元格格式改成"000"(在设置单元格格式--自定义--类型那里可以改) 2、把序列号的单元格填上公式=row() 。如果表格上面有表头的话，你数数表头有多少行，在公式后面减去行数，例如有5行表头，公式就是=row()-5 当你插入行的时候把公式填上就可以了 方法一： 1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数); 2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。 18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。 从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符（从左向右）。 对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取： 假如身份证号数据在A1单元格，在B1单元格中编辑公式 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&M ID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1, 13,2)) 回车确认即可。 如果只要“年-月”格式，公式可以修改为 =IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2),MID(A 1,7,4)&"-"&MID(A1,11,2)) 用Excel从身份证号码中提取信息 （年龄、性别、出生地） 出生年月日信息提取： 方法一：在记录列中输入公式：=--TEXT(MID(B2,7,6+IF(LEN(B2)=15,0,2)),"#-00-00")，往下复制，无论15位还是18位身份证号码全部搞定，方法最简单。 方法二、在记录列中输入公式：=--IF(LEN(B2)=15,TEXT(MID(B2,7,6),"##-00-00"),TEXT(MID(B2,7,8),"####-00-00"))，往下复制，无论15位还是18位身份证号码全部搞定，公式增加了几个字符，原理差不多，结果一致。 原理：使用函数text、if、mid、len。 注意：1、B列存放身份证号码。存放在其它列，则在公式中作相应调整。 2、计算出错（#V ALUE!），说明身份证号码有错。 3、日期显示格式，可在单元格格式中设置。 性别信息提取： 在记录列中输入公式：=IF(LEN(B2)=15,IF(MOD(RIGHT(B2),2)=0,"女","男"),IF(MOD(LEFT(RIGHT(B2,2)),2)=0,"女","男"))无论15位还是18位身份证号码全部轻松完成。 原理：使用函数IF、LEN、MOD、LEFT、RIGHT。 注意：1、B列存放身份证号码。存放在其它列，则在公式中作相应调整。 2、计算出错（#V ALUE!），说明身份证号码有错。 出生地信息提取： 在记录列中输入公式：=LEFT(B2,6)，往下复制，然后根据代码用VLOOKUP查询发证地或者是出生地信息。 Excel文件模板： 从身份证号码中提取信息使用的模板 ： 使用Excel从身份证 号码提取信息.xls 点击该图标，打 开该EXCEL文件，另存为××文件，即可使用。 谢谢你的使用。 水晶六彩 excel表格中输入身份证号码自动识别性别提取出生年月计算年龄前言： 相信很多做过文职的小伙伴有过相同的烦恼，特别在一些流动性很大的公司，每当有人员流动，都要重新录入员工基本信息，比如身份证号码-性别-出生日期，那有没有什么好方法，只要输入身份证号码，就能自动把性别和出生年月和年龄自动提取出来呢？当然有，在exc el表格中就能实现了。 工具： excel表格（office各种版本与WPS都适用） excel自带函数IF，MOD，MID，LEN，YEAR，NOW（每个函数的作用这里我就不讲了，自己百度） 教程： 1.1新建如图所示的身份证-性别-出生年月-年龄格式的表格， （因为这些是基本信息，所以我们制作员工信息表格的时候可以将这些基本信息放在一起，然后后面在添加一些其他的，比如入职日期、 工龄等等其他一些杂七杂八的） 1.2在性别下面的第一个单元格也就是D4单元格输入=IF(LEN(C4)=1 8,IF(MOD(MID(C4,17,1),2)=1,"男","女"),"") （这里为了信息保密我用的假信息做演示）细心的小伙伴可能发现了，这里就用到了4个函数了，IF、LEN、MOD、MID。其实在exc el表格中用的最多的其实就是IF函数了，用来判断，比如制作成绩表的时候，只需要一个IF函数就行了。 1.3下面我们输入身份证。（因为第二代身份证开始都是18位的了，这里我就用18位的作为演示，15位的基本都快消失了，所以可以忽略了。） 当身份证位数输入不正确时，性别会显示为空。不过也有小伙伴说，自己做的表格说身份证输入错误时，显示的是#V ALUE。其实也没问题啦，网上的教程是判断条件是C4>0的时候，但是因为数值不满足计算，导致输出数值错误，就显示这个了，不过也没错啦。输入18位正确的号码时就能正确显示了。那我们为了美观，可以用我的方法，这样就不会显示#V ALUE啦。 函数格式没错的话就会正确的显示性别了。 在EXCEL表格中输入身份证号如何自动提取性别和出生年月 在EXCEL表格中输入身份证号如何自动提取性别和出生年月 如输入大批量的个人信息。(例：输入姓名、性别、身份证号、出生年月日、地址等等)，特别是在输入身份证号之后还要输入一些出年月日、性别、其时这些都已经在身份证号里面体现出来了，所以我想有没有办法提取出来。 经过实践体验，现已经解决了这个问题，这样减少了不少时间，对于一两个人信息的输入这没什么，而对于成百上千的要输入来说，就是关键了。 例如： 序号 姓名 身份证号码 性别 出生年月 说明：公式中的B2是身份证号所在位置 1、根据身份证号码求性别： =IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错")) 2、根据身份证号码求出生年月： =IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,C ONCATENATE(MID(B2,7,4),".",MID(B2,11,2)),"身份证错")) 3、根据身份证号码求年龄： =IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错")) 如何使用Excel从身份证号码中提取出生日期 如何使用Excel从身份证号码中提取出生日期2009-02-27 22:52例如：从身份证420821************中提取出生日期来，如何快速得出？只需使用语句：=DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2)) 【A1是身份证号码所在单元格】 date()函数是日期函数；如输入今天的日期=today() 那么，mid函数是什么东东呢？ MID(text,start_num,num_chars) Text 为包含要提取字符的文本字符串；Start_num 为文本 中要提取的第一个字符的位置。文本中第一个字符的start_num 为1 ，以此类推；Num_chars 指定希望MID 从文本中返回字符的个数。 对身份证号码分析下就知道：420821************，出生日期是1992年2月6日；也就是从字符串（420821************）的第7位开始的4位数字表示年，从字符串的第11位开始的2位数字表示月，字符串的第13位开始的2位数字表示日。呵呵，强悍吧！ Excel中利用身份证号码（15或18位）提取出生日期和性别 需要的函数： LEN(C6)=15：检查C6单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位； INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。 RIGHT:返回文本字符串最后一个字符开始指定个数的字符； MID：返回文本字符串指定起始位置起指定长度的字符，MID(C6,7,2)表示：在C3中从左边第七位起提取2位数；"19"&MID(C6,7,2)表示：在C3中从左边第七位起提取2位数的前面添加19； …… &""&表示：其左右两边所提取出来的数字不用任何符号连接； &"-"&表示：其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年 mm月dd日，则可以把公式中的“-”分别用“年月日”进行替换就行了。 Excel成绩统计表应用到的各种参数各班原始成绩统计表用到的成绩数据参数： 学生成绩分数输入从B5：B62为例 总分：=SUM(B5:B62) 平均分：=B63/COUNTIF(B5:B62,">=1") 合格人数：=COUNTIF(B5:B62,">=60") 合格率：=B65/COUNTIF(B5:B62,">=1") 优秀人数：=COUNTIF(B5:B62,">=90") 优秀率：=B67/COUNTIF(B5:B62,">=1") 年级成绩统计表 参加人数：=COUNTIF(班原始成绩统计表!B5:B62,">=0") 及格人数：=COUNTIF(班原始成绩统计表!B5:B62,">=60") 及格率：=C5/B5*100% 优生人数：=班原始成绩统计表!B67 优生率：=E5/B5*100% 总分：=SUM(班原始成绩统计表!B5:B62) 平均分：=G5/B5 分数段： 100分：=COUNTIF(班原始成绩统计表!B5:B62,">=100") 99-90分：=COUNTIF(班原始成绩统计表!B5:B62,">=90")-I5 89-80分：=COUNTIF(班原始成绩统计表!B5:B62,">=80")-I5-J5 79-70分：=COUNTIF(班原始成绩统计表!B5:B62,">=70")-I5-J5-K5 69-60分：=COUNTIF(班原始成绩统计表!B5:B62,">=60")-I5-J5-K5-L5 59-50分：=COUNTIF(班原始成绩统计表!B5:B62,">=50")-I5-J5-K5-L5-M5 49-40分：=COUNTIF(班原始成绩统计表!B5:B62,">=40")-I5-J5-K5-L5-M5-N5 39-30分： =COUNTIF(班原始成绩统计表!B5:B62,">=30")-I5-J5-K5-L5-M5-N5-O5 29-20分： =COUNTIF(班原始成绩统计表!B5:B62,">=20")-I5-J5-K5-L5-M5-N5-O5-P5 19-10分： =COUNTIF(班原始成绩统计表!B5:B62,">=10")-I5-J5-K5-L5-M5-N5-O5-P5-Q5 9-1分： =COUNTIF(班原始成绩统计表!B5:B62,">0")-I5-J5-K5-L5-M5-N5-O5-P5-Q5-R5 0分：=COUNTIF(班原始成绩统计表!B5:B62,"=0") 级总表中合计部分： 以8个正常班为例：数据从B5：B12 参加人数合计：=SUM(B5:B12) 及格人数合计：=SUM(C5:C12) 及格率合计：=C13/B13*100% 优生人数合计：=SUM(E5:E12) 优生率合计：=E13/B13*100% 总分合计：=SUM(G5:G12) 平均分合计：=G13/B13 分数段合计： 100分个数合计：=SUM(I5:I12) 99-90分个数合计：=SUM(J5:J12) 89-80分个数合计：=SUM(K5:K12) 79-70分个数合计：=SUM(L5:L12) 69-60分个数合计：=SUM(M5:M12) 59-50分个数合计：=SUM(N5:N12)…………… Excel中根据身份证号计算出生日期格式：19920516 15位410881********* 18位410881************ =IF(LEN(B2)=15,MID(B2,7,6),MID(B2，7,8)) LEN(B2)=15：检查B2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。MID(B2,7,6)：从B2单元格中字符串的第7位开始提取6位数字，本例中表示提取15位身份证号码的第7、8、9、10、11、12位数字。MID(B2,7,8)：从B2单元格中字符串的第7位开始提取8位数字，本例中表示提取18位身份证号码的第7、8、9、10、11、12、13、14位数字。 18位身份证号：410881************ 输出出生日期1979/06/05 =CONCATENATE(MID(B2,7,4),"/",MID(B2,11,2),"/",MID(B2,13,2)) （B2表示身份证号码所在的列位置） 1992-05-13-6： =CONCATENATE(MID(I2,7,4),"-",MID(I2,11,2),"-",MID(I2,13,2)) 跟其他函数的使用方法相同，算出第一个后，在往下拖就都算好了 在B列输入身份证号，在C列填写性别，可以在C2单元格中输入公式“=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")”，其中：LEN(B2)=15：检查身份证号码的长度是否是15位。 MID(B2,15,1)：如果身份证号码的长度是15位，那么提取第15位的数字。MID(B2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。15位身份证，看最后一位，奇男偶女；18位的，看第17位数，也是奇男偶女。方法二：如果你是想在Excel表格中，从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女")) 公式内的“B2”代表的是输入身份证号码的单元格。 数据比对公式：=Vlooku(查找值，数据表，1，0) =VLOOKUP(D4,Sheet1!$E$2:$E$1329,1,0) D4为公式所在工作表的第一个数据后面的为对比表格的数据从第一个选到最后一个 在同一个EXCEL表格中两个工作表对比则数据表要加$ 两个EXCEL表格中的工作表则不加美元符号 怎么在excel中制作学生成绩统计表 制作一张成绩表并命名为“成绩表”，并制作一张成绩统计表空表命名为“统计表”。如下图所示。 在“统计表”姓名下面的单元格中输入函数，=成绩表!A3，可以吧成绩表中的C3单元格套入到本单元格中。 在“统计表”名次下面的单元格中输入函数，=RANK(成绩表!C3,成绩表!C3:C16)，RANK函数可以计算在成绩表!C3:C16区域内，C3的大小顺序。 在“统计表”参考人数下面的单元格中输入函数，=COUNT(成绩表!C3:C16)，COUNT函数可以统计在成绩表!C3:C16区域内的单元格数。 在“统计表”总分下面的单元格中输入函数，=SUM(成绩 表!C3:C16)，SUM函数可以统计在成绩表!C3:C16区域内数的总和。 在“统计表”平均分下面的单元格中输入函数，=AVERAGE(成绩表!C3:C16)，AVERAGE函数可以求出在成绩表!C3:C16区域内数的平均值。 在“统计表”及格人数下面的单元格中输入函数，=COUNTIF(成绩表!C3:C16,">=72")，COUNTIF函数可以统计出在成绩表!C3:C16区域内>=72的数的个数。 在“统计表”及格率下面的单元格中输入函数，=I5/F5*100，可以计算出在成绩表!C3:C16区域内>=72的数的百分比。 在“统计表”优良人数下面的单元格中输入函数，=COUNTIF(成绩表!C3:C16,">=96")，COUNTIF函数可以统计出在成绩表!C3:C16区域内>=96的数的个数。 在“统计表”优良率下面的单元格中输入函数，=K5/F5*100，可以计算出在成绩表!C3:C16区域内>=96的数的百分比。 如何使用Excel从身份证号码中提取出生日期2009-02-27 22:52例如：从身份证420821************中提取出生日期来，如何快速得出？ 呵呵，只需使用语句：=DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2)) 【A1是身份证号码所在单元格】 date()函数，地球人都知道，日期函数；如输入今天的日期=today() 那么，mid函数是什么东东呢？ MID(text,start_num,num_chars) Text 为包含要提取字符的文本字符串；Start_num 为文本 中要提取的第一个字符的位置。文本中第一个字符的start_num 为1 ，以此类推；Num_chars指定希望MID 从文本中返回字符的个数。 对身份证号码分析下就知道：420821************，出生日期是1992年2月6日；也就是 从字符串（420821************）的第7位开始的4位数字表示年，从字符串的第11位开始的2位数字表示月，字符串的第13位开始的2位数字表示日。呵呵，强悍吧！ Excel中利用身份证号码（15或18位）提取出生日期和性别 需要的函数： LEN(C6)=15：检查C6单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位；INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。 RIGHT:返回文本字符串最后一个字符开始指定个数的字符； MID：返回文本字符串指定起始位置起指定长度的字符，MID(C6,7,2)表示：在C3中从左边第七位起提取2位数； "19"&MID(C6,7,2)表示：在C3中从左边第七位起提取2位数的前面添加19； …… &""&表示：其左右两边所提取出来的数字不用任何符号连接； &"-"&表示：其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年mm月dd日，则可以把公式中的“-”分别用“年月日”进行替换就行了。 电子表格常用函数汇总 ―――（潘世华2013年版） 注：(1)如何截取身份证号第17位：MID(C2,17,1) Value（字符型数字）这个函数就是转换字符型数字转成数字 N(value)这个函数,将不是数值形式的值转成数值形式.日期转换成序列值,True转换成1,False转换成0 不需要函数，乘1即可例如001 变数值=A1*1 即等于1 1、用“身份证号”提起出生年月日第一种公式：=IF(LEN(C2)=15,19&MID(C2,7,2)&"/"&MID(C2,9,2)&"/"&MID(C2,11,2 ),IF(LEN(C2)=18,MID(C2,7,4)&"/"&MID(C2,11,2)&"/"&MID(C2,13,2) ,"")) 说明：C2为身份证号码所在的单元格，在实践过程中，把“C2”转换成实际表中的“身份证栏”（身份证栏的输入格式为“文本”）。 2、用“身份证号”提起出生年月日第二种公式：（很好）=CONCATENATE(MID(C2,7,4),"年",MID(C2,11,2),"月",MID(C2,13,2),"日") 3、“用身份证”号算出性别第一种公式：=IF(LEN(C2)=15,IF(OR(RIGHT(C2,1)="0",RIGHT(C2,1)="2",RIGHT(C2 ,1)="4",RIGHT(C2,1)="6",RIGHT(C2,1)="8"),"女","男"),IF(LEN(C2)=18,IF(OR(MID(C2,17,1)="0",MID(C2,17,1)="2",MID( C2,17,1)="4",MID(C2,17,1)="6",MID(C2,17,1)="8"),"女","男"),"")) 说明：C2为身份证号码所在的单元格，在实践过程中，把“C2”转换成实际表中的“身份证栏”（身份证栏的输入格式为“文本”）。 一、分析身份证号码 其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。 15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。 18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。 例如，某员工的身份证号码(15位)是320521*********，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。 二、提取个人信息 这里，我们需要使用IF、LEN、MOD、 MID、DATE等函数从身份证号码中提取个人信息。如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。 1. 提取出生年月信息 由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式 “=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中： LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。 MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。 MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。 IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。 如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。 EXCLE中最精确的计算年龄的公式 中午一个同事请教我有关EXCLE自动计算年龄的方法，当时告诉她应该有一堆公式但是一时没有谁能记得清楚，答应他回来以后上网查查。 到网上一搜，大失所望。几乎没有一种方法是精确的。 网上搜到的公式大概有这么几种： 1、计算出生日期到某一指定日期（一般选用某年的最后一天入2006年12月31日）的的天数，然后除以360 ，得到一个数值，然后用 int()函数取整，得出需要的年龄。一般使用的公式如下： =IF(C12="","",INT(DAYS360(C12,"2006-12-31")/360)) 聪明一点的人知道使用这个公式， =IF(C12="","",INT(DAYS360(C12,TODAY())/360)) 这个方法，这个公式的弊端在于，一、将每个月默认为30天去计算两个日期之间的天数，二、将每年默认为360天去计算年龄。这种方法显然不精确。 2、年份直接相减 计算周岁 =YEAR(NOW())-YEAR(C12) 计算虚岁 =YEAR(NOW())-YEAR(C12)+1 这种算法的精确程度显而易见，粗略估算还算可以。 3、使用DATEDIF函数 这种方法与第一种方法采用了相同的思路，但是其的精确程度显然比第一种方法要高，这取决于DATEDIF函数本身的精确性。 =IF(C12="","",INT(DATEDIF(C12,"1983-3-20","D")/365)) 或者， =IF(C12="","",INT(DATEDIF(C12,now(),"D")/365)) 这种方法强行将一年固定为365天，我们知道通常情况每个四年就有一年是366天，所以这种算法也不是很精确。 通过认真分析，我觉得只有结合我们计算年龄的实际方法，才能编制出准确无误的公式。首先分析人们计算年龄的方法。 例如某人系1983年3月20日生人，如果要在2007年3月23日这天计算他的年龄，通常采用这样的方法。 首先，人们会用2007减去1983得出的年龄为24岁，然后再看看他“满没满”24岁，就是看看出生的月份和日期比今天早还是晚，如果出生日期晚于今天则表示没有满，那么他的年龄就应该是2007-1983-1=23岁。如果出生日期早于今天或者就是今天，就说明他已经满了24岁或者正好满24岁，则他的年龄就是2007-1983=24岁。 分析清楚了计算年龄的过程我们再根据这个过程编写公式就很容易了。 综上所述，我编写了如下公式，在实际应用中将公式中所有的C12替换为你所使用的出生日期所在的表格行号列号组合即可。如（A1，B2等等） =IF(MONTH(NOW()) EXCEL身份证号码计算出生年月年龄及性别和重名筛选 在学校的人事管理中经常会遇到需要统计教职工的年龄的问题，但案头的原始资料只有身份证号码，其实这足够了。在EXCEL中，引用其内置函数利用身份证号码达到此目的比较简单。 1、身份证号码简介（18位）： 1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。 2、确定“出生日期”： 18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。 ①正确输入了身份证号码。(假设在D2单元格中) ②将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式“=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。 关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID（text,starl_num,num_chars）。 Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。 starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。 多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。操作效果如下图： 3、确定“年龄”： “出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式 “=INT((TODAY()-E2)/365)”即可计算出“年龄”。 关于这个函数公式的具体说明： ①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确，就能立即计算出当前的日期，它无需参数。操作格式是TODAY（）。 ②用TODAY（）-E2，也就是用当前日期减去出生日期，就可以计算出这个人的出生天数。 ③再除以“365”减得到这个人的年龄。 ④计算以后可能有多位小数，可以用【减少小数位数】按钮，将年龄的数值变用EXCEL 2003 创建学生成绩统计表
Excel里通过身份证号码计算性别
excel中用身份证号码生成性别
excel 怎样从身份证号码提取年龄和性别
Excel表格中根据身份证号码自动填出生日期、计算年龄[1]
Excel身份证提取生日性别年龄
Excel表中身份证号码提取出生年月、年龄、性别的使用技巧[1]
用Excel从身份证号码中提取信息(年龄、性别、出生地)
excel表格中输入身份证号码自动识别性别提取出生年月计算年龄
在EXCEL表格中输入身份证号如何自动提取性别和出生年月
Excel成绩统计表应用到的各种参数
excel wps表格 身份证号计算出生日期和性别
怎么在excel中制作学生成绩统计表
如何使用Excel从身份证号码中提取出生日期及性别
EXCEL电子表格用函数计算年龄、工龄及从身份证中算出周岁等技巧
EXCEL中如何从身份证号码求出生年月日及年龄公式
身份证性别年龄(excel最精确计算年龄的公式)
EXCEL身份证号码计算出生年月年龄及性别和重名筛选
相关主题
文本预览