Excel在统计分析中的应用(全113页).ppt

Excel统计分析 职场人生 Excel ppt 第一章统计基础与数据描述第二章描述性统计第三章数据库统计函数第四章统计指数 提纲 例1 1 上证180指数的月收益率 统计基础与数据描述 C4 B4 B3 B3 例1 2COUNTIF函数 计算区域内满足给定条件的单元格的个数COUNTIF Range Criteria Range表示区域 Criteria表示条件 统计基础与数据描述 C3 COUNTIF 例1 1 B3 B38 2400 D3 C3D4 C4 C3 例1 3Frequency函数 返回区域内数据的频率分布Frequency data array bins array data array 统计基础与数据描述 B5 0 12等差序列 步长0 04 终止值 0 12 C5 FREQUENCY 例1 1 C4 C38 B5 B11 C13 SUM C5 C12 例1 4频率分布图 统计基础与数据描述 例1 5相对频率分布图 统计基础与数据描述 D3 C3 C 13 例1 6累积频率分布图 统计基础与数据描述 例1 7直方图 统计基础与数据描述 中心趋势离中趋势偏度峰度 描述性统计 中心趋势 算术平均值 C13 SUM C3 C12 C14 C13 10 C13 AVERAGE C3 C12 中心趋势 几何平均值 第一年投资100元 亏损50元 收益率 50 100 100 50 第二年投资50元 盈利50元 收益率 100 50 50 100 两年平均收益率 50 100 2 25 中心趋势 几何平均值 D16 GEOMEAN D4 D15 1 众数 出现频率最高的数 中心趋势 众数 B10 MODE A2 B9 中位数 居中的数 中心趋势 中位数 B10 MEDIAN A2 B9 调和平均数 数的倒数的算术平均值的倒数 中心趋势 调和平均数 B3 1 A3 B10 1 SUM B3 B9 离中趋势 方差 B13 VAR B3 B12 离中趋势 标准差 B13 STDEV B3 B12 离中趋势 四分位数 B13 QUARTILE B3 B12 1 B14 QUARTILE B3 B12 3 四分位数 位于25 和75 位置的数称为低四分位数和高四分位数 刻画数据相对于中位数的离散程度 QUARTILE Array Quart Quart 0 min1 低四分位数2 中位数3 高四分位数4 max 离中趋势 方差系数 B15 AVERAGE B3 B14 B16 STDEV B3 B14 C15 AVERAGE C3 C14 C16 STDEV C3 C14 B17 B16 B15C17 C16 C15 方差系数 标准差与均值之间的比值 消除了量纲影响 偏度 偏斜度 B21 SKEW B3 B20 偏度 四分位数偏度系数 B21 QUARTILE B3 B20 1 B22 QUARTILE B3 B20 2 B23 QUARTILE B3 B20 3 B24 B23 B21 2 B22 B23 B21 偏度 Spearman偏度系数 B21 AVERAGE B3 B20 B22 STDEV B3 B20 B23 MEDIAN B3 B20 B24 3 B21 B23 B22 峰度 峰值 B22 KURT B3 B21 峰度 矩峰度系数 3 常峰态 正态分布3 高峰态 B22 AVERAGE B3 B21 C3 B3 B 22 2D3 B3 B 22 4C22 SUM D3 D21 D23 D22 18 C22 18 4 Dfunctions database field criteria database 数据库单元格区域field 字段criteria 包含过滤条件的单元格区域DCOUNT DCOUNTA DSUM DPRODUCT DMAX DMIN DAVERAGE DVAR DSTDEV DVARP DSTDEVP DGET 数据库统计函数 统计指数 同等加权指数 统计指数 拉氏指数 统计指数 帕氏指数 统计指数 埃奇沃斯指数 统计指数 费雪指数 统计指数 资本加权指数 某事件发生的概率为p 不发生的概率为q 则在N次实验中该事件发生X次的概率为又称伯努利分布BINOMDIST number trials probability cumulative number 实验成功的次数trials 独立实验的次数probability 每次独立实验成功的概率cumulative true返回累积分布 false返回密度函数 概率分布 二项分布 股票上涨的概率为0 6 20个交易日中上涨5天的概率 以及上涨小于等于5天的概率 概率分布 二项分布 股票上涨的概率为0 6 求20个交易日中概率分布函数大于等于临界值0 75的最小天数CRITBINOM 试验次数 每次试验的成功概率 临界值 概率分布 二项分布 在r次成功之前失败的次数X的概率 帕斯卡分布 NEGBINOMDIST 失败次数 成功次数 成功概率 灯泡合格概率0 8 求找到30个合格灯泡之前 需要测试10个不合格灯泡的概率 概率分布 负二项分布 25 N 种股票 15 M 只沪市 10 N M 只深市 从中选10 n 种 其中有5 x 只属于沪市的概率HYPGEODIST 成功次数 样本数 总样本成功次数 总样本数 概率分布 超几何分布 随机事件发生的次数POISSON 事件数 期望值 返回的概率分布形式 平均每天呼叫20次 一天收到15次和小于等于15次呼叫的概率 概率分布 泊松分布 股票收益率呈正态分布 均值为5 标准差为2 求收益率为4 对应的概率密度函数值 概率分布 正态分布 NORMDIST x 均值 标准差 返回的概率分布形式 股票收益率80 的可能性不超过某值 求这个值 概率分布 正态分布 NORMSINV 概率 均值 标准差 概率分布 正态分布 概率分布 正态分布 45 45 一 计算机准备二 分析工具库提供的统计分析方法三 数据基本信息计算四 图表制作五 假设检验 一 t检验1 成对2 成组 二 U检验 z检验 六 方差分析 一 单因素 二 两因素1 无重复2 有重复七 回归与相关 Excel在统计分析中应用 46 一 计算机准备1 启动Excel 检查 工具 菜单中是否有 数据分析 命令 如果没有发现 数据分析 命令 就表示未加载 分析工具库 2 加载 分析工具库 步骤 工具加载宏分析工具库确定工具数据分析进入统计分析功能 47 单向分类资料的方差分析 两向分组单独观测值试验资料的方差分析 两向分组有重复观测值资料的方差分析 描述性统计量计算 二总体方差的同质性检验 编制次数分布表及绘制统计图 二 分析工具库提供的统计分析方法 48 排位与百分比排位 两样本平均数u检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样 49 三 数据基本信息计算 一 统计分析的粘贴函数AVERAGE计算算术平均值BINOMDIST计算二项式分布的概率值CHIDIST计算特定 2分布的单尾概率值CHIINV计算一定单尾概率值时的 2临界值CHITEST计算独立性检验的 2值CONFIDENCE计算总体平均值的置信区间CORREL计算两组数据的相关系数COVAR计算两组数据的协方差 50 FDIST计算特定F分布的单尾概率值FINV计算一定概率时的临界F值FTEST计算二个样本方差之比F值的概率GEOMEAN计算几何平均数HARMEAN计算调和平均数INTERCEPT计算直线回归的截距MAX计算最大值MEDIAN计算一组给定数字的中位数MIN计算最小值 51 MODE计算一组数据的众数NORMDIST计算正态分布的累积函数NORMINV计算正态分布累积函数的逆函数NORMSDIST计算标准正态分布的累积函数NORMSINV计算标准正态分布累积函数的逆函数POISSON计算泊松分布的概率SLOPE计算给定数据的直线回归系数STDEV计算样本标准差STDEVP计算样本总体的标准差 52 TDIST计算学生氏 t分布的概率值TINV计算特定概率时学生氏 t分布的临界t值TTEST计算t检验时的学生氏 t检验相关的概率VAR计算样本的方差VARP计算样本总体的方差ZTEST计算Z检验的双尾概率值 53 53 二 利用粘贴函数计算方法例举1 算术平均数 AVERAGE 数值区域 2 几何平均数 GEOMEAN 数据区域 3 中数 MEDIAN 数据区域 4 极差 最大值 MAX 数据区域 最小值 MIN 数据区域 极差 最大值 最小值5 方差 VARP 数据区域 6 标准差 STDEVP 数据区域 7 变异系数 标准差 算术平均数 54 三 描述统计DescriptiveStatistics 57 57 四 图表制作1 输入原始数据和各组下限 可用功能 MIN MAX 2 工具数据分析直方图3 输入区域 原始数据接受区域 各组下限输出区域 图标位置图表输出4 设定图表选项 图表类型 表题 坐标 选项等 58 表4 1100听罐头样品的净重 g 59 表4 2100盒鲜枣每盒检出不合格枣数 60 例5 1 为了分析某种新型减肥药剂是否对人具有显著减肥作用 现随机选取12位自愿者进行试验 服药后 间隔1个疗程 分别测其12位自愿者的体重见下表 假设服药前后 除服用此减肥药剂外 其余的生活方式 生活条件均未变化 试问根据此试验结果 能否判断这种新型减肥药对人具有显著减肥作用 五 假设检验HypothesisTesting 一 t检验1 成对数据资料的平均数t检验 61 61 工具数据分析t 检验 平均值的成对二样本分析变量1区域 B 2 B 14变量2区域 C 2 C 14假设平均值 0标志 0 05输出区域 D 10确定 62 63 结果分析 所以否定H0 接受HA即 服用新型减肥药剂的自愿者体重有极显著差异 因此可得出结论 这种新型减肥药剂对人具有减肥作用 效果极显著 67 例5 2 某番茄品种分别按3200株 667m2种植 各调查5个小区 得产量见下表 试测验两种密度的小麦产量有无显著差异 67 2 成组数据资料的平均数t检验 68 68 工具数据分析t检验 双样本等方差检验 t检验 双样本异方差检验 变量1区域 A 1 A 6变量2区域 B 1 C 6假设平均值 0标志 0 05确定 69 69 结果分析 t 1 33t0 05 2 31 t t0 05接受Ho即 两种密度小区产量无差异 70 例5 3 现有两种茶多糖提取工艺 分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量 结果见表4 问两种工艺的粗提物中茶多糖含量有无显著差异 表5 3两种工艺粗提物中茶多糖含量测定结果 双样本假设检验可先检验两个样本的方差是否相等 用F检验法 然后再选择检验方法 利用Excel提供的 F检验 双样本方差 工具进行检验 71 2 利用Excel提供的 F检验 双样本方差 工具进行检验 72 结果分析 由F检验结果可得 F 1 044304 F0 025 4 5 7 38788 故接受H0 认为两个样本的方差相等 即两种工艺的粗提物中茶多糖含量的方差相等 注意 Excel只提供了F检验的单尾临界值 而本例属于双边假设检验问题 因此需要查找双尾临界值 查F分布表得 0 05 n1 5 n2 6 73 因此选用 t检验 双样本等方差假设 74 结果分析 故接受H0 即 两种工艺的粗提物中茶多糖含量无显著差异 75 在以下的情况中 可以利用Z检验分析工具来检验两个总体均值之间是否存在差异 总体方差已知 当总体方差未知 n 30 Z检验 u检验 教材P79 例4 4 某食品厂在甲乙两条生产线上各测了30个日产量如下表 试检验两条生产线的平均日产量有无显著差异 先用粘贴函数 VAR 数值 计算方差 输入格式单行或单列 将方差数值代入用z 检验 推断 结论 80 六 方差分析AnalysisofVariance 一 单因素试验方差分析 例6 1 以淀粉为原料生产葡萄糖过程中 残留的许多糖蜜可用于酱色生产 在生产酱色之前应尽可能彻底除杂 以保证酱色质量 为此对除杂方法进行选择 今选用5种除杂方法 每种方法做4次试验 试验结果见表5 试分析不同除杂方法的除杂效果有无差异 设各总体服从正态分布 且方差相等 81 81 工具数据分析方差分析 单因素方差分析输入区域 A 3 E 7分组方式 行标志位于第1列 0 01确定 88 注意 对照具体事例 汇总显著水平0 05和0 01两次计算结果 先将方差分析表做具体化修改完善 F测验 F 49 55 F0 01 4 15 4 89 所以 在显著性水平0 01下拒绝原假设H0 认为5种不同除杂方法的除杂效果有极显著差异 但哪几个方法差异显著 哪几个方法差异不显著 尚需进一步进行多重比较分析 但Excel分析工具尚不能自动完成 无法完成最后结论表述 需手工完成多重比较 89 1 两因素无重复试验的方差分析 例6 2 某厂现有化验员3人 担任该厂牛奶酸度 T 的检验 每天从牛奶中抽样一次进行检验 连续10天的检验分析结果见下表 试分析3名化验员的化验技术有无差异 以及每天的原料牛奶酸度有无差异 二 两因素试验方差分析 90 90 工具数据分析方差分析 无重复双因素输入区域 A 1 D 5标志 0 01确定 继续手工完成结果推断和多重比较过程 96 表6 73种肥料施于3种土壤的小麦产量 g 96 例6 6 施用A1 A2 A33种肥料于B1 B2 B33种土壤 以小麦为指示作物 每处理组合种3盆 得产量结果 g 于表6 7 试作方差分析 2 两因素有重复试验的方差分析 97 输入格式 98 98 工具数据分析方差分析 可重复双因素输入区域 A 1 D 10每一样本的行数 3 0 05确定 104 整理后 继续手工完成结果推断和多重比较过程 105 105 例6 4 设有A B C D E这5个大豆品种 其中E为对照 进行大区比较试验 成熟后分别在5块地随机抽取4个样点 每点产量 kg 列入下表 试对A B C D四个品种做产量比较 注意 方差分析表要对照试验做具体化修改完善 106 整理后 107 例6 5 将A1 A2 A3 A4四种生长素 并用B1 B2 B3三种时间浸渍菜用大豆品种种子 45天后处理平均单株干物重 g 列入表6 5试做方差分析 108 整理后 109 109 教材P157 例6 1 某食品感官评定时 测得食品甜度与蔗糖质量分数的关系如下表 试求y对x的直线回归方程 七 直线回归与相关 输入格式 得回归方程为