excel数据分析与数据管理操作实验.docxVIP

Excel数据分析与数据管理操作实验 1、 随机数据的产生 利用rand函数产生成绩表（如下图），学生人数不少于100人。 说明： RAND函数：产生0.00~1.00范围内的平均分布的小数。 Int函数：取数值的整数部分。 注意：为避免数据发生变化，需在成绩表生成后先拷贝到word文档中再拷贝回excel中来。 2、 数据频度分析 使用Frequency函数分析各科不及格(60)、及格(60~75)、良好(75~90)、优秀（=90）学生人数。 分析各科成绩的最高分，最低分，平均分，标准差stdev和方差var。 3、 数据相关性分析 ? CORREL函数--相关系数 CORREL函数均可计算两个测量值变量之间的相关系数，条件是每种变量的测量值都是对 N 个对象进行观测所得到的。 相关系数的值都必须介于 -1 和 +1 之间 -1 为完全负相关和 +1为完全正相关，0为完全不相关）。 语法： CORREL(array1,array2) Array1 第一组数值单元格区域。 Array2? 第二组数值单元格区域。 结果为2组数据的相关系数。 ? F检验--检验2组数据方差是否相等（2组数据的离散程度） FTEST(array1,array2) Array1??第一个数组或数据区域。 Array2??第二个数组或数据区域。 结果为2组数据方差无明显差异时的单尾概率（=0.05,方差无显著差异，即等方差） ? TTEST 函数—T检验（2组数据的均值差异是否显著） T检验分为配对样本T检验和独立样本T检验。 – 配对样本T检验是检验两个样本的观测值之间彼此有关联，这样的两个样本它们的均值是否存在差异（同一对象的两次测试的比较，比如前测与后测又无差异） – 独立样本T检验，是检验彼此独立的两个样本，其均值是否存在差异（男生和女生的成绩是否有显著差异） – 独立样本T检验，2组样本方差相等与否，其计算公式是不同的，因此独立样本T、检验要先进行F检验，然后鉴定TTest的最后一个参数是2还是3。 语法 TTEST(array1,array2,tails,type) Array1?? 为第一个数据集。 Array2?? 为第二个数据集。 Tails?? 指示分布曲线的尾数。如果 tails = 1，函数 TTEST 使用单尾分布。如果 tails = 2，函数 TTEST 使用双尾分布。（一般均值相等用双尾分布，大于或小于用单尾） Type?? 为 t 检验的类型。 如果 type 等于 检验方法 1 配对样本检验 2 等方差独立样本检验 3 异方差独立样本检验 利用成绩表完成以下操作： a) 在F116中，计算外语和政治成绩的相关系数。如果相关性很高（0.7），在G116中标记“高”，“一般”（0.3~0.7）或“低”（0.3）。 b) 在F117中，计算男生、女生“数学”成绩是否存在显著性差异。如果没有显著性差异，在G117中标记“无显著差异”，否则标记“有显著差异”。 提示：独立样本T检验前，需先进行F检验，以确定等方差检验还是异方差检验。 4、 限定数据提取 给定下图名单，在成绩表中选出对应人员数学成绩 使用VLOOKUP函数。 VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为： ??????VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找) 下面以一个实例来介绍一下这四个参数的使用 ???? 例1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄。?? ???公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)?? ?? 参数说明： ?????? 1?查找目标：就是你指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中A列进行查找。 ??????? 公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)??? ???????2?查找范围（VLOOKUP(A13,$B$2:$D$8,3,0)?）：指定了查找目标，如果没有说从哪里查找，EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。本例中要从表一中进行查找，那么范围我们要怎么指定呢？这里也是极易出错的地方。大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错： ????????A?查找目标一定要在该区域的第一列。本例中查找表二的姓名，那么姓名所对应的表一的姓名列，那么表一的姓名列（列）一定要是查找区域的第一列。象本例中，给定的区域要从第二列开始，即$B$2:$D$8，而不能是$A$2:$D$8。因