实验二认识数据类型

1实验二认识数据类型实验目的1.识别数据的数据类型和特性。2.熟悉四种基本数据类型的运算。3.了解不同基本数据类型的常用函数，能通过EXCELHELP学习新函数的用法。4.了解不同数据混合运算时的数据转换。实验范例本实验所用素材为文件“实验二.xlsx”。1.学生成绩计算题目：工作表【学生成绩】包含了某班学生《大学计算机》课程的平时成绩和期末考试成绩，按平时成绩占40%，考试成绩占60%，计算每个学生的总评成绩，不保留小数；计算期末最高分、最低分和平均分。（1）计算学生的总评成绩分析：总评成绩由平时成绩和期末考试成绩加权平均得到，公式为：平时成绩*0.4+期末考试成绩*0.6。这样计算出来的总评成绩是实数，有可能带小数位数。要求不保留小数，涉及到实数截位的方法。思考与讨论：实数截位的方法有以下四种不同的方法。①“设置单元格格式”→“数字”→“数值”中，将小数位数设置为0，四舍五入（实质：单元格中的实数数值不变，显示改变）②四舍五入函数：INT(number+0.5)ROUND(number,0)③截尾取整函数：INT(number)TRUNC(number)④向下向上的舍入函数FLOOR和CEILING技巧：使用EXCEL帮助学习EXCEL函数。EXCELHELP提供了EXCEL函数的详尽解释，以CEILING函数为例：在公式输入栏旁点击fx按钮，打开“插入函数”对话框，如图1所示。2图1插入CEILING函数通过搜索或列表选择，找到需要的函数，单击链接“有关该函数的帮助”打开EXCEL帮助。EXCEL帮助提供了该函数的语法、用法和示例，如图2所示，将示例复制到一个空表的工作表中，会更容易理解和学习示例。图2CEILING函数的用法和示例请同学们自行学习上面提及的ROUND、TRUNC、INT、CEILING、FLOOR函数，理解它们用法和函数之间的区别。（2）求期末考试成绩的最大值和最小值分析：求最大值和最小值可以通过MAX和MIN函数对期末考试成绩区域D2：D59进行统计。函数的参数可以是区域，也可以是使用名称。技巧：批量生成名称，在函数中使用名称。在一个带标题行的表格中，为每列或每行的数据区域指定名称，可以使用“公式”选项卡下“定义的名称”组中的菜单命令“根据所选3内容创建”→“首行”，快速创建。名称创建好后，在输入函数参数时，可以很方便地使用“公式”选项卡下“定义的名称”组中的菜单命令“fx用于公式”获取名称。例如：计算期末最高分公式：=MAX(考试成绩)选中单元格区域D1:D59，使用菜单命令“公式”→“定义的名称”→“根据所选内容创建”→“首行”，快速创建名为“考试成绩”的数据区域。在H2单元格输入公式“=MAX()”，使用菜单命令“公式”→“定义的名称”→“fx用于公式”获取名称作为函数参数，如图3所示。图3使用区域名称作为函数参数（3）求班级期末考试成绩的平均值思考与讨论：工作表【学习成绩】中期末考试有一位同学缺考，AVERAGE函数求得的平均成绩是如何考虑这位同学的呢？分析：分别以两个不同的公式计算平均成绩：=SUM(考试成绩)/COUNT(考试成绩)72.63=SUM(考试成绩)/COUNTA(考试成绩)71.38SUM函数计算区域中所有数值数据之和，单元格数据为文本类型的视为0。COUNTA函数计算区域中不为空的单元格的个数，值为“缺考”的单元格被计算在内。COUNT函数只计算包含数值数据或可转化为数值数字字符串的单元格，所以，值为“缺考”的单元格并未被计算在内。对照AVERAGE函数与AVERAGEA的计算值，可知AVERAGE函数不统计值为“缺考”的单元格，而AVERAGEA则统计“缺考”单元格，并将其值视为0。因此，在使用时要区分下面两组函数。COUNTCOUNTAMAXMAXAMINMINAAVERAGEAVERAGEA42.解析身份证号码题目：工作表【身份证号分析素材】中包含了学生的身份证号码信息，分别在D、E列的相应位置解析出学生的性别、出生日期。分析：（1）分析身份证号码的数据类型第二代身份证由18个数字构成，由数字构成的数据是否一定是数值数据呢？确定一个数据的数据类型，还要看它的计算特性，如果对它的处理要使用算术运算或数学函数，那么这个数据是数值类型的；如果数值的处理主要是字符串的操作，求长度，取子串，编码核对等，那么即便数据都是有数字构成的，这个数据也是文本类型。身份证号码是一种有结构的编码数据，不会参与数学运算，而是获取其中有关地区、性别、生日的信息，所以应认定为文本数据。像这一类的数据还有学号、工号、邮编、手机号码、银行卡账号等。（2）分析身份证号码的特点身份证前2位表示省份代码；第7位到第14位表示生日信息，以yyyymmdd格式表示；第17位表示性别，奇数为男，偶数为女。步骤：（1）解析性别整个身份证文本串中取出性别。方法一：使用MID(text,start_num,num_chars)函数。方法二：分列（参考实验一）。判断所取到的数的奇偶性。使用MOD(number,divisor)函数，number表示被除数，divisor表示除数；结果为1表示男，0表示女。思考与讨论：（1）嵌套函数的构造以C10：310107199303027748为例，提取身份证中的性别编码：MID(C10,17,1)=〉‘4’判断月份的奇偶：MOD(D10,2)=1=〉FALSE根据奇偶得到性别：IF(E10,”男”,”女”)=〉女综合上面的式子：IF(MOD(MID(E10,17,1),2),”男”,”女”)（2）数据类型的转化在实验1范例竞赛学生信息分析中判断是否是本月生日的公式如下：“=VALUE(MID(C2,11,2))=MONTH(TODAY())”其中，value函数的作用是将mid函数得到的月份，转化为整数，再和后面的当前月份进行比较。例如：身份证号51382419950228003X，mid函数取得的是子串：“02”，month（today（））得到的当前月份是整数，假如是2，关系表达式：“02”=2的结果是FALSE。所以要使用value函数，将“02”转化为整数2，再比较：2=2，结果为TRUE。这里使用VALUE函数进行数据类型的显式转化。而本例中：“=IF(MOD(MID(C2,17,1),2)=1,男,女)”，以C10为例，MID函数得到的是字符串“04”，MOD函数第一参数需要一个整数，于是将“04”自动转化为整数4，继续5MOD函数的计算。这是系统自动进行的数据类型转化，称为数据类型的隐式转化。计算必须在两个相同数据类型的数据之间进行，如果进行运算的数据的数据类型不符合要求，首先看系统是否会进行自动转换，如果不能自动转化，要使用数据类型转换函数进行显式转换。例如value，text就是这样一类的函数。（2）解析出生日期思考与讨论：问题：用mid得到的YYYYMMDD不是日期类型数据，而是文本类型，如19900305方法一：字符串构造MID(C2,7,4)&/&MID(C2,11,2)&/&MID(C2,13,2)方法二：类型函数转换DATE（MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))想一想：观察图4中用不同方法得到的结果相同吗？图4不同的出生日期类型3.分列数字和汉字题目：将工作表【会计科目-银行存款】的科目名称一列中的开户银行和银行账号分列为两列分析：科目名称中开户银行和账号的长度都不固定，也没有分隔号，不能用“分列”完成。思路：一个数字（半角字符）占一个字节；一个汉字（全角字符）占两个字节，只要计算出字节数和字符数的差异，就用可以求出开户银行的汉字长度x和银行账号数字长度y。字符串构成：x个汉字+y个数字字符字节数=2*x+y字符数=x+y字节数-字符数=x2*字符数-字节数=yEXCEL提供了LEN函数：计算字符数，LENB函数：计算字节数。解决方案：身份证号码出生日期年龄出生日期3101071994012312411994/01/231994/1/233101071994050291351994/05/021994/5/23101071997020121931997/02/011997/2/13101071991040168911991/04/011991/4/13101071999090298471999/09/021999/9/23101071996080132841996/08/011996/8/13101071996021223421996/02/121996/2/123101071999080173871999/08/011999/8/13101071993030277481993/03/021993/3/23101071997030304891997/03/031997/3/33101071993060308291993/06/031993/6/33101071997030185941997/03/011997/3/16开户银行=LEFT(TRIM(B2),LENB(B2)-LEN(B2))账号=RIGHT(TRIM(B2),2*LEN(TRIM(B2))-LENB(TRIM(B2)))其中，函数TRIM功能为删除字符串中多余的空格，LEFT，RIGHT函数请利用帮助自行查找用法。4.文本函数的使用通过EXCELHELP学习FIND,REPLACE函数的使用，按教材步骤完成例1-2-115.计算工人的上班工时题目：某工厂按计时方式考勤，员工的上班时间机动，又区分为白日班，中班和夜班，每日记录工人的上班时间和下班时间，计算工人的实际上班工时。分析：当员工上夜班时，下班时间比上班时间要小，导致计算为负值，显示出错。解决方法为使用MOD函数修正。如果计算小时为单位的数值，扩充24倍。提示：出勤工时1:=MOD（E4-D4，1），显示格式为数值，单位为天，0.38天日期时间数据对应数值数据，以天为单位，1天是1，1小时是1/24（0.0416667）。两个时间数据相减，实质是实数相减。一天的上班时间一定是小于1的整数，求余数，以一天为单位，故，MOD的第二参数为1。出勤工时2的工时与出勤工时1相同，显示格式时间，例如10:15读作10小时15分钟。出勤工时3:=MOD(E2-D2,1)*24，1天24个小时，转化单位为小时，读作10.25小时，与10小时15分钟等价。拓展：如果计算工时按半小时为单位计算，超过半小时按1小时计，不足半小时按半小时计算。公司每小时工资为30元，请计算日薪。6.复合条件函数简化if语句题目：工作表【学生三门课成绩】中，包含了某校学生某次语文、数学、英语的考试成序号姓名工号上班时间下班时间出勤工时1出勤工时2出勤工时3按半小时舍位日薪1员工一E20020038:3017:300.389:009.00009270.002员工二E20020047:4518:000.4310:1510.250010.5315.003员工三E200200523:007:000.338:008.00008240.004员工四E20020069:0017:300.358:308.50008.5255.005员工五E200200715:151:000.419:459.750010300.006员工六E200200819:408:000.5112:2012.333312.5375.007员工七E20020099:5018:000.348:108.16678.5255.007绩，计算每位学生的总分，然后根据以下条件给出等第：语文、数学、英语三科都大于等于60分，且总分还要求大于等于200分，四个条件同时满足时为“及格”，否则为“不及格”。步骤：计算每位学生的总分。使用逻辑函数AND（参数1，参数2，...）来获得四个条件同时满足的结果。IF(AND(C2=60,D2=60,E2=60,F2=200),及格,不及格)思考：若使用OR（参数1，参数2，。。。）函数该如何表达？实验内容1.统计工作