《excel如何进行信息统计》由会员分享，可在线阅读，更多相关《excel如何进行信息统计（9页珍藏版）》请在金锄头文库上搜索。
1、excel如何进行信息统计 使用Excel管理人事信息具有无须编程、简便易行的特点为了尽可能减少数据录入的工作量下面利用Excel函数实现数据统计的自动化 1.性别输入根据现行的居民身份证号码编码规定正在使用的18位的身份证编码它的第17位为性别(奇数为男偶数为女)第18位为效验位而早期使用的是15位的身份证编码它的第15位是性别(奇数为男偶数为女) (1)函数分解 LEN函数返回文本字符串中的字符数语法：LEN(text)Text是要查找其长度的文本空格将作为字符进行计数 MOD函数返回两数相除的余数结果的正负号与除数相同语法：MOD(number,divisor)Number为被除数;Di
2、visor为除数MID函数返回文本字符串中从指定位置开始的特定数目的字符该数目由用户指定语法：MID(text,startnum,numchars)Text为包含要提取字符的文本字符串;Startnum为文本中要提取的第一个字符的位置文本中第一个字符的startnum为1以此类推;Numchars指定希望MID从文本中返回字符的个数 (2)实例分析 为了适应上述情况必须设计一个能够适应两种身份编码的性别计算公式在D2单元格中输入“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,男,女),IF(MOD(MID(C2,17,1),2)=1,男,女)”回车后即可 在单
3、元格获得该职工的性别而后只要把公式复制到D3、D4等单元格即可得到其他职工的性别为了便于大家了解上述公式的设计思路下面简单介绍一下它的工作原理：该公式由三个IF函数构成其中“IF(MOD(MID(C2,15,1),2)=1,男,女)”和“IF(MOD(MID(C2,17,1),2)=1,男,女)”作为第一个函数的参数公式中“LEN(C2)=15”是一个逻辑判断语句LEN函数提取C2等单元格中的字符长度如果该字符的长度等于15 则执行参数中的第一个IF函数否则就执行第二个IF函数在参数“IF(MOD(MID(C2,15,1),2)=1,男,女)”中 MID函数从C2的指定位置(第15位)提取1个
4、字符而MOD函数将该字符与2相除获取两者的余数如果两者能够除尽说明提取出来的字符是0(否则就是1)逻辑条件“MOD(MID(C2,15,1),2)=1”不成立这时就会在D2单元格中填入“女”反之则会填入“男”如果LEN函数提取的C2等单元格中的字符长度不等于15则会执行第2个IF函数除了MID函数从C2的指定位置(第17位即倒数第2位)提取1个字符以外其他运算过程 与上面的介绍相同 2.出生日期输入 (1)函数分解 CONCATENATE函数将几个文本字符串合并为一个文本字符串语法：CONCATENATE(text1,text2,.)Text1,text2,.为130个要合并成单个文本项的文本
5、项文本项可以为文本字符串、数字或对单个单元格的引用 (2)实例分析 与上面的思路相同我们可以在E2单元格中输入公式“=IF(LEN(C2)=15,CONCATENATE(19,MID(C2,7,2),年 ,MID(C2,9,2),月,MID(C2,11,2),日),CONCCTENCTE(MID(C2,7,4),年,MID(C2,11,2),月,MID(C2,13,2),日)”其中“LEN(C2)=15”仍然作为逻辑判断语句使用它可以判断身份证号码是15位的还是18位的从而调用相应的计算语句对15位的身份证号码来说左起第7至12个字符表示出生年、月、日此时可以使用MID函数从身份证号码的特定位
6、置分别提取出生年、月、日然后用CONCATENATE函数将提取出来的文字合并起来就能得到对应的出生年月日公式中“19”是针对早期身份证号码中存在2000年问题设计的它可以在计算出来的出生年份前加上“19”对“18”位的身份证号码的计算思路相同只是它不存在2000年问题公式中不用给计算出来的出生年份前加上“19”注意：CONCATENATE函数和MID函数的操作对象均为文本所以存放身份证号码的单元格必须事先设为文本格式然后再输入身份证号 3.职工信息查询 Excel提供的“记录单”功能可以查询记录如果要查询人事管理工作表中的某条记录然后把它打印出来必须采用下面介绍的方法 (1)函数分解 INDE
7、X函数返回数据清单或数组中的元素值此元素由行序号和列序号的索引值给定INDEX函数有两种语法形式：数组和引用数组形式通常返回数值或数值数组引用形式通常返回引用当函数INDEX的第一个参数为数组常数时使用数组形式语法1(数组形式)：INDEX(array,rownum,columnnum)Array为单元格区域或数组常量如果数组只包含一行或一列则相对应的参数rownum或columnnum为可选如果数组有多行和多列但只使用rownum或columnnum函数INDEX返回数组中的整行或整列且返回值也为数组;Rownum为数组中某行的行序号函数从该行返回数值如果省略rownum则必须有column
8、num;Columnnum为数组中某列的 列序号函数从该列返回数值如果省略columnnum则必须有rownum语法2(引用形式)：INDEX(reference,rownum,columnnum,areanum)Reference表示对一个或多个单元格区域的引用如果为引用输入一个不连续的区域必须用括号括起来如果引用中的每个区域只包含一行或一列则相应的参数rownum或 columnnum分别为可选项;Rownum引用中某行的行序号函数从该行返回一个引用;Columnnum引用中某列的列序号函数从该列返回一个引用;Areanum选择引用中的一个区域并返回该区域中rownum和columnnum
9、的交叉区域选中或输入的第一个区域序号为1第二个为2以此类推如果省略areanum函数INDEX使用区域1MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置语法：MATCH(lookupvalue,lookuparray,matchtype)Lookupvalue为需要在数据表中查找的数值;Lookupvalue为需要在Lookarray中查找的数值;Matchtype为数字1、0或1 (2)实例分析 如果上面的人事管理工作表放在Sheet1中为了防止因查询操作而破坏它(必要时可以添加只读保护)我们可以打开另外一个空白工作表Sheet2把上一个数据清单中的列标记复制到第一行假如你
10、要以“身份证号码”作为查询关键字就要在C2单元格中输入公式“=INDEX(Sheet1C2:C600,MATCH(SCS5,Sheet1SCS2:SCS600,0),1)”其中的参数“SCS5”引用公式所在工作表中的C5单元格(也可以选用其他单元格)执行查询时要在其中输入查询关键字也就是待查询记录中的身份证号码参数“Sheet1C2:C600”设定INDEX函数的查询范围引用的是数 据清单C列的所有单元格MATCH函数中的参数“0”指定它查找“Sheet1SCS2:SCS600”区域中等于SCS5的第一个值并且引用的区域“Sheet1SCS2:SCS600,0”可以按任意顺序排列上面的公式执行
11、数据查询操作时首先由MATCH函数在“Sheet1SCS2:SCS600”区域搜索找到“SCS5”单元格中的数据在引用区域中的位置(自上而下第几个单元格)从而得知待查询数据在引用区域中的第几行接下来INDEX函数根据MATCH函数给出的行号返回“Sheet1C2:C600”区域中对应行数单元格中的数据假设其中待查询的“身份证号码”是“3234567896”它位于“Sheet1SCS2:SCS600”区域的第三行MATCH函数就会返回“3”接着INDEX函数返回“Sheet1C2:C600”区域中行数是“3”的数据也就是“3234567896”然后我们将光标放到C2单元格的填充柄上当十字光标出现
12、以后向右拖动从而把C2中的公式复制到D2、E2等单元格(然后再向左拖动以便把公式复制到B2、A2单元格)这样就可以获得与该身份证号对应的性别、籍贯等数据注意：公式复制到D2、E2等单元格以后INDEX函数引用的区域就会发生变化由C2:C600变成D2:D600、E2:E600等等但是MATCH函数返回的(相对)行号仍然由查询关键字给出此后INDEX函数就会根据MATCH函数返回的行号从引用区域中找到数据在Sheet2工作表中进行查询时只要在查询输入单元格中输入关键字回车后即可在工作表的C2单元格内看到查询出来的身份证号码如果输入的身份证号码关键字不存在或输入错误则单元格内会显示“N/A”字样
13、4.职工性别统计 (1)函数分解 COUNTIF函数计算区域中满足给定条件的单元格的个数语法：COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域;Criteria为确定些单元格将被计算在内的条件其形式可以为数字、表达式或文本 (2)实例分析 假设上面使用的人事管理工作表中有599条记录统计职工中男性和女性人数的方法是：选中单元格D601(或其他用不上的空白单元格)统计男性职工人数可以在其中输入公式“=男COUNTIF(D2:D600,男)人”;接着选中单元格D602在其中输入公式“=女COUNTIF(D2:D227,女)人”回车后即可得到“男
14、399人”、“女200人” 上式中D2:D600是对“性别”列数据区域的引用实际使用时必须根据数据个数进行修改“男”或“女”则是条件判断语句用来判断区域中符合条件的数据然后进行统计“”则是字符连接符可以在统计结果的前后加上“男”、“人”字样使其更具有可读性 5.年龄统计 在人事管理工作中统计分布在各个年龄段中的职工人数也是一项经常性工作假设上面介绍的工作表的E2:E600单元格存放职工的工龄我们要以5年为一段分别统计年龄小于20岁、20至25岁之间一直到55至60岁之间的年龄段人数可以采用下面的操作方法 (1)函数分解 FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分布语法：FR
15、EQUENCY(dataarray,binsarray) Dataarray为一数组或对一组数值的引用用来计算频率如果dataarray中不包含任何数值函数FREQUENCY返 回零数组;Binsarray为间隔的数组或对间隔的引用该间隔用于对dataarray中的数值进行分组如果binsarray 中不包含任何数值函数FREQUENCY返回dataarray中元素的个数 (2)实例分析 首先在工作表中找到空白的I列(或其他列)自I2单元格开始依次输入20、25、30、35、40.60分别表示统计年龄小于20、20至25之间、25至30之间等的人数然后在该列旁边选中相同个数的单元格例如J2：J10准备存放各年龄段的统计结果然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY()YEAR(E2:E600),I2:I10)”按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果其中位于J2单元格中的结果表示年龄小于20岁的职工人数J3单元格中的数值表示年龄在20至25之间的职工人数等 6.名次值统计 在工资统计和成绩统计等场合往往需要知道
