使用EXCEL进行数据分析.ppt

使用EXCEL进行数据分析 主讲老师：李玲娟 数据分析与数据挖掘 掌握EXCEL的常用统计函数； 熟悉使用EXCEL透视表进行数据分析和分类汇总； 了解使用EXCEL进行数据分析 实验环境 版本：EXCEL 2000 完全安装 典型安装 最小化安装 讲授内容 常用统计函数 数据透视分析 图表 数据透视表(图) 数据分析 描述统计 检验分析 z-检验：双样本平均差检验 F-检验：双样本方差 t-检验：双样本等方差假设 t-检验：双样本异方差假设 t-检验：平均值的成对二样本分析 常用统计函数 一般统计函数：用来统计给定某个区域的数据的平均值、最大值或者最小值等 应用举例一 某班级的成绩表，如下图所示，其中有两个学生的该门课程缺考，需要分别用到AVERAGE和AVERAGEA函数来计算两个不同的平均值。 【操作步骤】 ①在单元格D3中输入公式“=AVERAGE(B2:B9)”，可以计算参加考试学生的平均成绩，函数AVERAGE在计算时对缺考的两个学生的成绩自动认为是0。 ②在单元格D6中输入公式“=AVERAGEA(B2:B9)”，可以计算全体学生的平均成绩，对缺考的学生成绩，AVERAGE函数将自动忽略。 应用举例二 某班级的成绩表，如下图所示。需要用到FREQUENCY函数来来统计各个分数段的分布学生人数。 FREQUENCY函数 功能：统计指定单元格区域中数据的频率分布。由于函数 FREQUENCY 返回一个数组，必须以数组公式的形式输入。 语法形式: FREQUENCY(data_array,bins_array) data_array为一数组或对一组数值的引用，用来计算频率。如果 data_array 中不包含任何数值，函数 FREQUENCY 返回零数组。 bins_array为一数组或对数组区域的引用，设定对 data_array 进行频率计算的分段点。如果 bins_array 中不包含任何数值，函数 FREQUENCY 返回 data_array 元素的数目。 【操作步骤】 ①在E4~E7单元格输入进行频率计算的分段点{59,70,80,90}； ②选中F4~F8单元格,作为输出统计结果的单元格； ③在公式栏输入“=FREQUENCY(B2:B11,E4:E8)”，然后按下“Ctrl＋Shift＋Enter” 组合键锁定数组公式即可。 CORREL函数 功能是单元格区域中的两个数列之间的相关系数。 表达式为CORREL（arrnyl，arrny2）。 arrnyl和arrny2表示的都是两个数据列 CORREL函数可以用来分辨两种属性之间的关系。 应用举例三 某医生为了探讨缺碘地区母婴TSH水平关系，应用免疫放射分析测定了160名孕妇（孕周15～17w）及分娩时脐带血TSH水平（mU/L），现随机抽取10对数据如图2-8所示，试求脐带血TSH水平对母血TSH水平的相关系数。 【操作步骤】 ①选中结果输出单元格B14； ②在公式栏输入“=CORREL(C3:C12,B3:B12)”,点击公式栏 按钮； ③在单元格A14输入“相关系数”即可。 应用举例四 某地120名7岁男童的身高，已知身高均数为119.41cm，标准差为4.38cm。试估计该地7岁男童身高在110cm以下者的概率，身高在110cm～120cm之间的概率。如果要制定一个健康标准使得90％的7岁男童符合健康标准，那么健康标准应该定多少？ NORMDIST和NORMINV函数 NORMDIST函数 功能：返回指定平均值和标准偏差的正态分布函数 表达式：NORMDIST（x，mean，standard_dev，cumulative）。 x是需要计算其分布的数值； mean是分布的算术平均值： standard_dev表示分布的标准偏差； cumulative是一逻辑值，指明函数的形式。如果cumulative为TRUE，NORMDIST函数返回累积分布函数，否则返回概率密度函数。此函数在统计方面应用范围广泛。 NORMINV函数 功能：返回指定平均值和标准偏差的正态累积分布函数的反函数。 表达式：NORMINV（probability，mean，standard_dev） probability是正态分布的概率； mean是分布的算术平均值； standard_dev表示分布的标准偏差。 应用举例五 某地120名7岁男童的身高，已知身高均数为119.41cm，标准差为4.38cm, 如下图所示。试估计该地7岁男童身高在110cm以下者的概率，身高在110cm～120cm之间的概率。如果要制定一个健康标准使得90％的7岁男童符合健康标准，那么健康标准应该定多少？ 【操作步骤】 ①在单元格C5中输入公式