Excel与数据统计管理分析

1、Excel与数据统计分析 统计计算与统计分析强调与计算机密切结合，Excel与数据统计分析 旨在提高学生计算机的综合运用能力，用统计方法分析问题、解决问题而编 写的。根据教材内容，也可以选择使用SPSS QSTAT Evievs、SAS MINITAB 等统计软件。 第三章统计整理 3.1 计量数据的频数表与直方图 例 3.1 (3-1) 一、指定接受区域直方图 在应用此工具前，用户应先决定分布区间。否则，Excel将用一个大约等 于数据集中某数值的平方根作区间，在数据集的最大值与最小值之间用等宽 间隔。如果用户自己定义区间，可用2、5或10的倍数，这样易于分析。 对于工资数据，最小值是 10

2、0,最大值是298。一个紧凑的直方图可从区 间100开始，区间宽度用10,最后一区间为300结束，需要21个区间。这里所 用的方法在两端加了一个空区间，在低端是区间“100或小于100”，高端是 区间“大于300 ”。 参考图3.3，利用下面这些步骤可得到频率分布和直方图： 1 为了方便，将原始数据拷贝到新工作表“指定频数直方图”中。 2.在B1单元中输入“组距”作为一标记，在B2单元中输入100, B3单元中 输入110,选取B2:B3，向下拖动所选区域右下角的+到B22单元。 3 按下列步骤使用“直方图”分析工具： (1 )选择工具菜单之数据分析选项,在分析工具框中“直方图”。如图 4所示

3、。 帮助 确定 取梢 廿析工丽 方歪分析：単因素方差分斬 方差分祈：可重复刈因索対析 方差另析：无重复或因素分析 弭关不数 协方羞 拇述统计 指数平灣 F-栓验敦样乖方差 便利叶缠一_ 图3.1数据分析工具之直方图对话框 1）输入 输入区域：A1:A51 接受区域：B1:B22 （这些区间断点或界限必须按升序排列） 选择标志 2）输出选项 输出区域：C1 选定图表输出 （2）.单击确定,| Excel将计算出结果显示在输出区域中。 图3.2数据分析工具之直方图对话框 Excel将把频率分布和直方图放在工作表中，如图3.3所示，输出表的C和 列中包括开始指定的界限。这些界限实际上是每一区间的上限

4、，也就是说， 界限实际上是边界。 图3.3频数分布与直方图 为了使图表更像传统的直方图和更易于理解，可双击图表并对它做如下修改： 1 图例：因为只有一个系列的数据显示在图表中，所以不需要图例。单击图 例（位于图表右侧的 频率”）并按Delete键。 2图表区：绘图区是以 X和刑由为边界的矩形区域。通过在柱形上面单击可选 取绘图区，单击鼠标右键并选择绘图区格式，将边框改为无并将区域改为亘 单击确定。 3条宽：在传统的直方图中，柱形是彼此相连接而不是分开的。选择某个柱 形，单击鼠标右键，选择数据系列格式，|并单击选择标签,将间距宽度从150 % 改为0 %，单击确定- 4. X轴标志：选取x轴，单

5、击鼠标右键，选择坐标轴格式，单击对齐标签，将 方向从自动改为水平文本，在这种设置下，即使图表已重置尺寸，x轴标记也 会变为水平的，单击确定。最后的直方图4.6 直方图 图3.4 修改后的直方图 二、不指定接受区域直方图 在进行探索性分析时，为了方便，通常不指定接受区域作直方图，步骤如 下： （1）选择工具菜单之数据分析选项,在分析工具框中“直方图”。如图 4 所示。 1）输入 输入区域：A1:A51 接受区域：（该处为空） 选择标志 2）输出选项 输出区域：B1 选定图表输出 （2）.单击确定,|得结果。 (3)按前面方法对直方图进行进一步修饰即得图 3.5 A B c 1 工挨 搂收 频军

6、2 LQD 1 3 1I7G 123.3 4 4 1S8 155.6 F 5 Z2? 184. 9 13 E 293 213.1 12 r 1137 241.1 7 g 173 2农毛魂“載癡朝觀g單動1 r风药藪區涼环 r蟹重合并计直数藉区域c r另一数强透视表或数据谨观團 .所需创建的报表奕型 F数据透视表 r数据透视團f及數嶠透视表)迦 (2) 选择数据源区域 (3) 选定数据透视表位置，完成 数皓透觇表和數潯透视图可导一5步骤之3 數推谨视表显云位畳. r新樓工作祓(W 金现有工作轰 透视鳶!辯$i5J 单击“完成”按証创餐数据透視表. 叵版式|选顶| 取捎|上一涉 | (4) 将“性

7、别”作为行字段拖至 G列，并将“性别”作为数据拖至数据项处, 得下表结果 G H I J K L M 请将列芋战馳至此女 卜 请将数据项拖至此处 数誓透視表 数据風懐* iffl Bl圖|隹廷| !超厨 工人輪呈 年機 丈牝程度按強别* 同理可得“文化程度”的透视表 G H I J K I 卜数项:性别 减项;文化稈席 匸別- 汇总 文化程度- 汇总 男 12 初中 8 女 3 高中 2 总计 15 技工 1 文盲 1 小学 2 中专 1 总计 15 此时如点击图形按钮，立即得到如下的透视图 沟強乩作押乞肪*七严巴七卢/自曰汁寸冒离图/ (5) 将“性别”作为行字段拖至行字段处，并将“文化程度

8、”作为列字段拖 至列字段处，将“性别”或“文化程度”作为列字段拖至数据字段处 得下表结果 1S 13 炯:性别及优蟻- It S 贰中 訥 KI 1 木学 中专 20 男 7 2 1 1 1 12 21 4 1 1 : 3 22 1L 8. 2 1 1 2. 1 15 第四章 总量指标和相对指标 例 4.1 (4-13) 1 A B C | D E F | 19 99 年 俪年产量 计划完成 2叩0年实际产量 2 TT 实际产量 计划产量 实际产量 旳 为1999年的 3 1950 2000 2200 11.0 112.S2 4 2020 2204 1998 90 G5 90.91 5 295

9、0 3010 3035 100. 83 102.丽 计算步骤: (1) 计算各厂计划完成 %E3=D3/C3*100,- (2) 2000 年实际产量为 1999 年的 %F3=D3/B3*100, 第五章平均指标 5.1简单平均数 例5.1 .某组有学生10人统计课考试成绩为 65, 82, 76, 80, 82, 86, 84, 88, 95, 98分，试求其平均指标。 平均数的计算步骤如下： (1)将数据输入到A列U,根据Excel提供的公式计算各种平均数 A B C 1 65 2 82 算术均数 =AVERAGE 3 76 调和均数 =HAOEAN(A1:A10) 4 80 几恂均数

10、5 82 A数 =WDE(A1:AID) 6 中位数 =KEDIAN(A1:A1O) 7 8 9 .眄 10 38 (2)用Ctrl+可切换到下面的结果: A B C D 1 65 2 82 算耒均数 64 3 76 调和均數 S2. G0 4 BO JL何均数 83. 11 5 82 众数 S2 6 86 中位数 63 7 84 8 88 g 95 10 98 5. 2加权平均数 例5.2 ( 5-1 )原始数据见下图A-D列，其中A、列放日产量的下限和上限 A B D E G 1 日产量f公斤 工人数f 组曲 SAfi打 2 L -V 4月修fl 5月伶也 q 4月榜曲 叨份曲 3 L0

11、別 20 10 15 甜) 150 4 20 10 35 20 25 875 500 5 30 40 30 25 35 1050 875 6 4D 50 10 30 45 13EO 7 50 60 5 15 55 275 825 8 合 计 100 100 伫 2950 37 CO g 2.5 37 10 42.5 11 丰位数 28- 57 38. X 平均数的计算步骤如下： (1) 计算日产量的组中值 E3=(A3+B3)/2, (2) 计算每个组段的总产量F3=C3*E3, G3=D3*E3, (3) 计算每月的总产量 F8=SUM(F3:F7), G8=Sum(G3:G7), (4)

12、计算平均数公式如下： 均数F9=F8/C8G9=G8/D8 众数F10=A4+(C4-C3)/(C4-C3+C4-C5)*10G10=A6+(D6-D5)/(D6-D5+D6-D7)*10 中位数F1仁A4+(C8/2-A4)/C4*10G11=A5+(D8/2-C5)/D5*10 第六章变异度指标 6.1 简单变异度指标 例 6.1 (6-1 ) 变异度指标的计算步骤如下： (1 )将甲乙两组数据输入到A, B列，根据Excel提供的公式计算各种变 异度指标 A B lc D E F 1 1 甲组 乙组 甲詛 乙组n 2 20 67 全距 100 6 3 40 68 平均差 30. 00 2

13、. 00 1 4 60 说 标准差 31.1S 比16 5 90 71 6 100 72 7 120 73 (2)用Ctrl+可切换到下面的公式: A C ! D E F 1 | 甲组 甲雄 乙组 2 W 2D 67 全距 3 to 68 平蜒 =ATEDEV (12:.A 7) =AVEnEV(E2:E7) 4 SD 的 标准盖 STDEVP(a2:A7) =STDEVF(B2:E7) 5 3D U 6 1DD 72 7 L2D 6.2加权变异度指标 例6.2 (6-2 )甲品种的原始数据见下图B-C列，乙品种的原始数据见下图G-H 列 A B CD E F G H J 1 乙品种 q 产量

14、 单产 畤面乎 产量 单产 3 扳号 f xf z 谊闵乜 塢号 f 就 X 4 1 1,2 1200 1000 1.2 1 L5 1680 1120 21561. 5 5 2 LI 104： 950 淀,I 2 1. 3 13C0 ：000 1.3 6 3 1 1100 1100 10：01. 3 1.3 1170 5D0 127U. 2 7 电 Ol? 3L0 900 8320.9 I 1 1208 J03 43681 8 5 0l8 340 1050 20 B0.3 5 0.9 6J0 7D0 00460. 9 g 合计 5 4995 卿 23745 合计 5 5988 99S 1539

15、46 10 均值mz u 标哇善 EE 91 揃磋 LbZ. 7L I n 12 变耳系戴啊 氐90 16.3D F面以甲品种的数据计算为例: (1) 计算单产值 D4=C4/B4, (2) 计算单产均值D9=C9/B9 (3) 计算次数X离差平方 E4=B4*(D4-$D$9)A2 ,并求和 E9=SUM(E4:E8) (4) 计算标准差：D11=SQRT(E9/B9)=68.91 (5) 计算变异系数：D12=D11/D9*100=6.9% 同理可得乙品种的标准差为162.71,变异系数为16.30% 第七章抽样调查 例7.1 (7-5) 期望 B4J= I 乍 SE1(B1:F1)*(B

16、2;F2) 1 A B1 c D E 1 F 1 1 X 10000 1000 100 10 1 2 P 0. 00001 O 0001 0+ 001 H 01 0.1| 3 |4 甌戶| 0-5) 求E(X)的公式B4=SUM(B1:F1)*(B2:F2),由于此处用到数组乘积求 和，所以要得到结果，需用Ctrl+Shift+E nter组合键。 例7.2 (7-6)二项分布 计算公式： P(5=x=10)=P(x=10)-P(x5)=P(x=10)-P(x=9)=1-P(x9)=1-P(x=8) Excel计算结果: A B C IT D 1 n 15 2 p 0. 25 Ek 3. 75

17、 3 P(X=10) 0. 9999 4 P(X5) 0. 6865 Pp=z=10) 0. 3134 5 PQ) 0. 9958 0. 0042 Excel 计算公式: A B C D 1 n 15 2 P 0. 25 Eil =E1*E2 3 P(XB21l) 4 Pg) 二 RIN0MDIET4 E1,RN1) p(5=5i=10j =E3-B4 5 P(X8) =BM0MTIST EL B2,1) P(5=k=5)=1-P(x=4) Excel计算结果： A B C 1 D 1 n 100 2 Fp 0.02 3 Lamda 2 4 P(K=4) 6 0902 p(X=4) 0, 94

18、73 巩盟x5) 0. 0527 | Excel 计算公式: 例7.4 (7-9)超几何分布 02 3= =HYPGEOOIST(C2, B$2, BJ3, B$l) A B | C D E F 1 N 60 2 5 c I 0.6370 . ! 3 竝 5 1 0. 3122 4 2 0. 0480 5 3 0. 0027 5)+100 A C D E F 1 tfiMK 价格 2 何 P1 qi 3 件 10 11 12500 1500 4 乙 担 60 54 12000 1680 5 丙 个 2 2, 3 6000 570C 6 7 销量总指数 136.45 价格总指数 92. 81 价

19、格总指数 125.54 8 变动销售额 312400 变动销售额 -84090 变动销官额 228310 计算可按例12.1步骤一步步进行，也可按下面的数组方式快速计算，例 如销售量总指数计算公式为： B7 =SUM(C3:C5)*(F3:F5)/SUM(C3:C5)*(E3:E5)*100, 但要得到数组结果需用组合键Ctrl+Shift+E nter 例 12.3(12-8) Excel计算公式如下 A B c D E F G H J 1 厂名 单好品戒茸（S） 产量（吨 2 so zl CO 11 zoqL zlql 3 甲厂 W30 餓 +S50 5100 =E3*03 =R3*E3 4 乙厂 1145 1150 3150 3500 之炖4 书1权 5 肝 121C 113： L890 220C =EW5 =B5