大学计算机基础实验七数据的公式计算分析

实验七数据的公式计算分析1. 实验目的和要求 掌握单元格及区域的相对地址、绝对地址的引用 熟练掌握单元格中公式的输入和应用； 熟练掌握各种常见函数的应用。2. 实验环境 Office办公组件中的Excel电子表格软件。要求：版本最好是2007之后版本，机房开机应选择Win7；作业说明是基于2007以后版本，相关图片则基于Excel 2016简体版。不建议使用WPS表格、LibreOffice、iWork等软件编辑，以免作业检查程序处理时出现误判。 大学计算机基础课程所有的课件、实验要求和相关软件，可在链接/s/1Nssig_Bm8xUycPiR-s54VQ，提取密码: yvbx。Excel作业可参考网盘中的“2019excel范例.xls”。3. 实验内容打开实验六所创建的工作簿文件，根据实验六批改确认邮件所附的意见进行修改，并在此基础上完成本次的实验内容。切记，不要在其他同学的实验六文件基础上完成本次作业，以免作业检查程序处理时出现误判。3.1. 单元格区域名称的定义和引用在Excel中，可以将某一单元格或者单元格区域，定义为指定的名称，以方便引用。定义的方法：选中待定义的单元格或者单元格区域，然后点击“公式”“定义名称”，然后指定相应的名称即可。随后，便可以在公式中直接引用该名称，或者单击编辑栏左端的名称栏，从下拉列表中，选取已定义的名称，便可完成对相应单元格或者单元格区域的绝对引用。具体可参见课本P110(5)/118(6)。作业部分：定位到“公式”工作表中，将“住院天数”列的数值部分所对应的单元格区域I5:I54，定义为名称“住院天数”。3.2. 常见函数的应用函数是Excel系统预先定义好的公式，是可以直接调用的功能模块。使用时仅需设置好相应的参数即可完成相关的计算。常见函数包括：1) 数学类函数INT(数值)：向下取整；ROUND(数值, 执行四舍五入的位数)：四舍五入；SUM(数值, 数值)：求所有数据之和。2) 文本类函数LEN(文本)：返回文本字符数；LEFT/RIGHT(文本, 字符数)：返回左侧或者右侧开始的特定数目的字符；MID(文本, 起始位置, 字符数)：返回从指定位置开始的特定数目的字符。3) 日期与时间类函数TODAY()，NOW()：返回当前日期/时间，无参数；DATE(year, month, day)：返回一个日期型数据，在年、月、日为变数的公式中非常有用。4) 逻辑类函数AND/OR(逻辑判断式, 逻辑判断式)：返回一个逻辑型数据，用于判断多个条件是否成立。前者需所有逻辑判断式都为真，才返回“TRUE”，否则返回“FALSE”；后者仅需一个为真，便返回“TRUE”，如全部条件都不成立，则返回“FALSE”。IF(逻辑判断式, 当逻辑判断为真时执行的命令, 当逻辑判断为假时执行的命令)，常用于通过逻辑判断实现不同的输出结果。函数中还可以嵌套多个IF函数，实现更多的判断选择。5) 统计类函数COUNT：计算日期和数值型单元格的个数；AVERAGE：计算数值数据的算式平均值；MEDIAN：计算数值数据的中位数；MAX/MIN：计算数值数据的最大/最小值；VAR（VAR.S，新版本中对应的函数）：计算样本方差（variance）；STDEV（STDEV.S，新版本中对应的函数）：计算样本标准偏差（标准差，standard deviation）。6) 查找和引用函数VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)：垂直方向查找某个数据，并返回对应行指定列指定的数据。其参数含义：查找值，查找范围，返回值所在列相对第一列的列数，精确匹配（false/0）或者近似匹配（true/1）。具体含义：根据“查找值”参数，在“查找范围”的第一列搜索“查找值”，如果找到该值，则返回值为：从“查找范围”第一列开始的第“返回值所在列相对第一列的列数”列所对应的值。HLOOKUP：参数基本同上，区别在于水平方向查找某个数据，并返回指定的数据。各种函数的具体用法可参见课件、范例以及Excel的帮助。作业部分：注意：计算住院天数相关数据时，可直接引用上一步所定义的名称。1) 定位到“公式”工作表中，将O列的列宽设置为18（或者3.77cm）；2) 在O5单元格输入文字“病人人数”，然后在P5单元格计算病人的总数目；3) 在O6单元格输入文字“平均住院天数”，然后在P6单元格计算所有病人的住院天数的算术平均值；4) 在O7单元格输入文字“住院天数取整”，然后在P7单元格，根据P6单元格的数据，计算3）计算所得的平均住院天数的取整值（注意：不是针对住院天数区域的取整，而是针对“平均住院天数”结果的取整）；5) 在O8单元格输入文字“保留一位小数”，然后在P8单元格，根据P6单元格的数据，计算3）计算所得的平均住院天数四舍五入到一位小数的所得到的数值（注意：不是设置显示一位小数，且也是针对“平均住院天数”结果的）；6) 在O9单元格输入文字“中位住院天数”，然后在P9单元格计算所有病人的住院天数的中位数；7) 在O10单元格输入文字“最长住院天数”，然后在P10单元格计算最长的病人住院天数；8) 在O11单元格输入文字“最短住院天数”，然后在P11单元格计算最短的病人住院天数；9) 在O12单元格输入文字“住院天数标准差”，然后在P12单元格计算所有病人的住院天数的标准差（注意：函数应是针对样本的，而不是针对总体的），并设置显示两位小数（注意：不是保留两位小数）；10) 在O13单元格输入文字“住院最长病人医师”，然后在P13单元格，利用P10单元格的数据和VLOOKUP函数，求出该病人的负责住院医师；11) 在O14单元格输入文字“住院最短病人医师”，然后在P14单元格，利用P11单元格的数据和VLOOKUP函数，求出该病人的负责住院医师；12) 删除J2单元格中的日期数据，然后利用TODAY函数指定当前的制表日期。（注意TODAY函数计算出来的日期会随着打开工作簿的日期变化而变化）3.3. 复杂公式的应用由于公式是可以嵌套和叠加的。因此，在解决一个复杂问题时，可以利用问题分解的思路，即将复杂问题分解为多个简单的、小的问题。通过设置过渡的单元格，利用简单公式计算得到中间过程中对应小问题的结果。逐步推进，直到得到最终的结果。确保正确后，再用小问题的具体公式替换最终公式中对应的单元格引用，完成公式合并。作业部分：1) 计算病人年龄定位到“公式”工作表中，将L列的列宽设置为6（或者1.34cm）。在L4单元格输入文字“年龄”。然后，在L5单元格利用“制表日期”和对应的“出生日期”数据，计算本行病人的年龄，再将该公式复制到L6:L54的单元格区域。提示，可使用多种方法实现，如使用YEAR或者DATEDIF函数。注意：不同的方法得到的结果可能会有少许差异，这个并不是错误。此外，公式需考虑绝对引用，并可与使用相对引用的公式结果相比较，可参见课本P106(5)/121(6)。2) 计算条件标记将M列的列宽设置为6（或者1.34cm）。在M4单元格输入文字“正常”。然后，在M5单元格利用“分娩方式”列和“妊娠结局”列的数据，计算本行对应病人的“正常”条件标记值，条件是“顺产且妊娠结局为足月的病人（注：本条件是假定的，非真实判断条件）”。结果用1表示正常，用0表示不正常。注意：“分娩方式”数据中的1表示“顺产”，2表示“剖宫产”。“妊娠结局”数据中的1表示“足月”，2表示“其他”。再将该公式复制到M6:M54的单元格区域。提示，可使用IF，AND函数，也可以使用嵌套的IF函数。注意可以有多种公式实现方法。图 1 “公式”工作表样图（注意TODAY函数计算出来的当前日期会随日期变化而变化）3.4. 数据随机分组应用在医学实验中，常常需要对实验对象进行分组，并要保证分组的随机。在Excel中可以利用RAND和RANK（在新版本中可使用RANK.EQ函数）等函数的联合应用，实现数据的随机分组。函数的具体使用方法可参见课件和Excel帮助。其中，RAND()：可生成0-1范围上的随机数，无参数。RANK(待排位的数据, 整个数据区域, 排序方法)，可用于计算数据的排位。作业部分：将所有的病人随机分为A、B两组。1) 新建一张工作表，将其重命名为“分组”。复制“公式”工作表中的A1:M54的单元格区域，将其“选择性粘贴”到“分组”表中。选择性粘贴以A1为起始位置，并选择“值和数字格式”。2) 在“分组”工作表中删除“出生日期”列和“住院医师”列。3) 在L4单元格输入文字“随机数”。然后，在L5单元格利用RAND函数生成随机数，再将该公式复制到L6:L54的单元格区域。4) 在M4单元格输入文字“值”。选中随机数所在的L5:L54单元格区域，将其“选择性粘贴”到M5:M54单元格区域中，并选择“数值”。注意，进行选择性粘贴的原因是RAND函数会自动重算，故每次的数据改变，“随机数”列的数据都会改变，进而影响排序结果。因此，需要将生成的随机数以值的形式保存下来，以便进行排序。5) 在N4单元格输入文字“排序”。然后，在N5单元格利用RANK（或者RANK.EQ）函数获取M5单元格在M5:M54单元格区域中的排名，再将该公式复制到N6:N54的单元格区域。从而，获取整个“值”列数据的排名情况。注意，函数参数中，参与排序的单元格区域应使用绝对引用；排序方式随意，升序降序皆可。6) 在O4单元格输入文字“分组”。然后，利用IF函数对N5:N54的数据进行分组，分组结果显示在O5:O54单元格区域中。分组方法：排名前25个数据分到A组，后25个数据分为B组。图 2 “分组”工作表样图（生