Excel统计分析.ppt

《Excel统计分析.ppt》由会员分享，可在线阅读，更多相关《Excel统计分析.ppt（44页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、ExcelExcel统计分析统计分析第一章 统计基础与数据描述第二章 描述性统计第三章 数据库统计函数第四章 统计指数提纲提纲例1.1：上证180指数的月收益率统计基础与数据描述统计基础与数据描述上证180指数时间指数收益率2002/12,828.952002/22,869.650.0143872002/32,941.430.0250142002/43,028.490.0295982002/52,773.89-0.084072002/63,299.060.189326C4=(B4-B3)/B3例1.2 COUNTIF函数：计算区域内满足给定条件的单元格的个数COUNTIF(Range,Crit

2、eria)，Range表示区域,Criteria表示条件统计基础与数据描述统计基础与数据描述上证180按指数分组组号上证180累积频率观测值数目12400.01122400.0-2500.04332500.0-2600.09542600.0-2700.012352700.0-2800.016462800.0-2900.025972900.0-3000.028383000.0-3100.031393100.0-3200.0343103200.0-3300.0362C3=COUNTIF(例1.1!B3:B38,=2400)D3=C3D4=C4-C3例1.3 Frequency函数：返回区域内数据的

3、频率分布Frequency(data_array,bins_array)，data_array统计基础与数据描述统计基础与数据描述上证180指数按收益率分组应用FREQUENCY函数间隔观测值数目-0.120-0.083-0.049060.04100.0850.1211合计35B5=-0.12 等差序列，步长0.04，终止值=0.12C5=FREQUENCY(例1.1!C4:C38,B5:B11)C13=SUM(C5:C12)例1.4 频率分布图统计基础与数据描述统计基础与数据描述上证180按指数分组组号上证180观测值数目12400.0122400.0-2500.0332500.0-2600

4、.0542600.0-2700.0352700.0-2800.0462800.0-2900.0972900.0-3000.0383000.0-3100.0393100.0-3200.03103200.0-3300.02指数频率2400.02400.0-2500.02500.0-2600.02600.0-2700.02700.0-2800.02800.0-2900.02900.0-3000.03000.0-3100.03100.0-3200.03200.0-3300.0012345678910上证180按指数分组频率图例1.5 相对频率分布图统计基础与数据描述统计基础与数据描述上证180按指数分

5、组组号上证180观测值数目 相对频率12400.010.027777822400.0-2500.030.083333332500.0-2600.050.138888942600.0-2700.030.083333352700.0-2800.040.111111162800.0-2900.090.2572900.0-3000.030.083333383000.0-3100.030.083333393100.0-3200.030.0833333103200.0-3300.020.055555636指数值相对频率2400.02400.0-2500.02500.0-2600.02600.0-2700.

6、02700.0-2800.02800.0-2900.02900.0-3000.03000.0-3100.03100.0-3200.03200.0-3300.000.050.10.150.20.250.3上证180按指数分组的相对频率图D3=C3/$C$13例1.6 累积频率分布图统计基础与数据描述统计基础与数据描述上证180按指数分组组号指数值累积频率12400.0122400.0-2500.0432500.0-2600.0942600.0-2700.01252700.0-2800.01662800.0-2900.02572900.0-3000.02883000.0-3100.03193100

7、.0-3200.034103200.0-3300.036指数值累积频率2400.02400.0-2500.02500.0-2600.02600.0-2700.02700.0-2800.02800.0-2900.02900.0-3000.03000.0-3100.03100.0-3200.03200.0-3300.00510152025303540上证180按指数分组累积曲线例1.7 直方图统计基础与数据描述统计基础与数据描述接收频率-0.12-0.08-0.0400.040.080.120.160.2其他024681012100.00%90.00%80.00%70.00%60.00%50.00

8、%40.00%30.00%20.00%10.00%0.00%直方图频率累积%接收频率累积%-0.1200.00%-0.0838.57%-0.04934.29%0651.43%0.041080.00%0.08594.29%0.12197.14%0.16097.14%0.21100.00%其他0100.00%中心趋势离中趋势偏度峰度描述性统计描述性统计中心趋势：中心趋势：算术平均值算术平均值股票价格算术平均值代码证券名价格600000浦发银行7.24600004白云机场8.54600006东风汽车2.94600008首创股份8.2600009上海机场16.65600011华能国际6.9760001

9、5华夏银行4.05600016民生银行5.69600018上港集箱16.37600019宝钢股份6.17求和82.82算术平均值8.282股票价格算术平均值代码证券名价格600000浦发银行7.24600004白云机场8.54600006东风汽车2.94600008首创股份8.2600009上海机场16.65600011华能国际6.97600015华夏银行4.05600016民生银行5.69600018上港集箱16.37600019宝钢股份6.17算术平均值8.282C13=SUM(C3:C12)C14=C13/10C13=AVERAGE(C3:C12)中心趋势：中心趋势：几何平均值几何平均值

10、第一年投资100元，亏损50元，收益率=(50-100)/100=-50%第二年投资50元，盈利50元，收益率=(100-50)/50=100%两年平均收益率=(-50%+100%)/2=25%上证180指数收益率几何平均值时间指数值收益率r1+r2003/122828.82004/13019.040.067251131.0672512004/23140.530.040241271.0402412004/33213.480.023228561.0232292004/42912.81-0.09356520.9064352004/52819.49-0.03203780.9679622004/625

11、28.44-0.10322790.8967722004/72548.490.007929791.007932004/82479.16-0.02720430.9727962004/925910.045112051.0451122004/102452.73-0.05336550.9466352004/112490.280.015309471.0153092004/122362.07-0.05148420.948516采用GEOMEAN函数求几何平均值-0.01491中心趋势：中心趋势：几何平均值几何平均值D16=GEOMEAN(D4:D15)-1众数:出现频率最高的数中心趋势中心趋势：众数众数年龄

12、的众数20201921212018232022192121182121众数21B10=MODE(A2:B9)中位数:居中的数中心趋势中心趋势：中位数中位数非组数据的中位数812912101310141115111611181119中位数11.5B10=MEDIAN(A2:B9)调和平均数:数的倒数的算术平均值的倒数中心趋势中心趋势：调和平均数调和平均数求调和平均数X1/X50.20 80.13 120.08 160.06 170.06 90.11 200.05 调和平均10.13 B3=1/A3B10=1/SUM(B3:B9)离中趋势离中趋势：方差方差日期价格200503077.65 2005

13、03087.78 200503097.57 200503107.45 200503117.41 200503147.37 200503157.16 200503167.16 200503177.13 200503187.19 样本方差0.052201 B13=VAR(B3:B12)离中趋势离中趋势：标准差标准差日期价格200503077.65 200503087.78 200503097.57 200503107.45 200503117.41 200503147.37 200503157.16 200503167.16 200503177.13 200503187.19 样本标准差0.228

14、476 B13=STDEV(B3:B12)离中趋势离中趋势：四分位数四分位数B13=QUARTILE(B3:B12,1)B14=QUARTILE(B3:B12,3)四分位数：位于25%和75%位置的数称为低四分位数和高四分位数,刻画数据相对于中位数的离散程度日期价格200503077.65 200503087.78 200503097.57 200503107.45 200503117.41 200503147.37 200503157.16 200503167.16 200503177.13 200503187.19 Q17.17 Q37.54 QUARTILE(Array,Quart)Qu

15、art=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方差系数：标准差与均值之间的比值。消除了量纲影响采用方差系数度量分散程度日期东风汽车上海机场200503103.1716.06200503113.1616.55200503143.117.27200503153.116.82200503163.0916.6200503173.0216.65200503182.9

16、716.52200503212.9416.65200503222.7117.17200503232.7416.9200503242.7616.86200503252.7516.79平均值2.96 16.74 标准差0.1756270.3158346方差系数0.0594 0.0189 偏度：偏度：偏斜度偏斜度股价偏斜度日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503

17、165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4偏斜度-0.9236 B21=SKEW(B3:B20)偏度：偏度：四分位数偏度系数四分位数偏度系数日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200

18、503215.94200503225.54200503235.36200503245.4Q15.93 Q26.10 Q36.34 四分位偏度系数0.1677019B21=QUARTILE(B3:B20,1)B22=QUARTILE(B3:B20,2)B23=QUARTILE(B3:B20,3)B24=(B23+B21-2*B22)/(B23-B21)偏度：偏度：SpearmanSpearman偏度系数偏度系数日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.292005031

19、06.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4平均值6.05 标准差0.333500 中位数6.1Spearman偏度系数-0.46 B21=AVERAGE(B3:B20)B22=STDEV(B3:B20)B23=MEDIAN(B3:B20)B24=3*(B21-B23)/B22峰度：峰度：峰值峰值日期上证指数200503011,303.41200503021,287.45200503031,2

20、94.34200503041,287.71200503071,293.74200503081,318.27200503091,316.79200503101,286.23200503111,289.94200503141,293.50200503151,269.14200503161,255.59200503171,243.47200503181,227.40200503211,231.05200503221,206.92200503231,201.65200503241,208.19200503251,205.63峰值-1.36420044B22=KURT(B3:B21)峰度：峰度：矩峰度系

21、数矩峰度系数3，常峰态，正态分布3，高峰态日期上证指数（Xi-X）2（Xi-X）4200503011,303.411534.87 2355814.24 200503021,287.45539.05 290570.80 200503031,294.34906.45 821658.19 200503041,287.71551.19 303806.92 200503071,293.74870.68 758092.01 200503081,318.272920.04 8526617.17 200503091,316.792762.28 7630174.09 200503101,286.23483.88

22、 234143.94 200503111,289.94660.87 436747.56 200503141,293.50856.58 733727.33 200503151,269.1424.08 579.96 200503161,255.5974.70 5579.36 200503171,243.47431.09 185835.89 200503181,227.401356.64 1840479.55 200503211,231.051101.09 1212392.67 200503221,206.923284.74 10789502.01 200503231,201.653916.59 1

23、5339644.14 200503241,208.193140.78 9864477.36 200503251,205.633434.27 11794199.64 1,264.2328849.85 73124042.80 0.000001 B22=AVERAGE(B3:B21)C3=(B3-$B$22)2D3=(B3-$B$22)4C22=SUM(D3:D21)D23=D22/18/(C22/18)4Dfunctions(database,field,criteria)database：数据库单元格区域field：字段criteria：包含过滤条件的单元格区域DCOUNT,DCOUNTA,DS

24、UM,DPRODUCT,DMAX,DMIN,DAVERAGE,DVAR,DSTDEV,DVARP,DSTDEVP,DGET数据库统计函数数据库统计函数数据库方差、标准差函数利用DVAR和DSTDEV求样本方差标准差Gold 公司销售清单销售单号客户类型类别生产商型号数量单价总价销售员040500001个人台式电脑戴尔G2802900018000刘040500002单位台式电脑惠普H52031000030000王040500003个人笔记本惠普H32011500015000李040500004个人笔记本联想L2021200024000刘040500005单位台式电脑联想L325800040000

25、王040500006单位台式电脑联想L322800016000孙040500006个人台式电脑戴尔G2805900045000王040500008个人笔记本戴尔G50021300026000李040500009单位台式电脑联想L322800016000李040500010个人笔记本戴尔G50061300078000王040500011单位台式电脑联想L325800040000刘040500012个人笔记本戴尔G50021300026000王040500013个人台式电脑惠普H52011000010000刘040500014单位笔记本惠普H32051500075000刘040500015单位台式

26、电脑联想L326800048000刘总价样本方差25000 374250000=DVAR(A4:I19,H4,A21:A22)样本标准差19345.54212=DSTDEV(A4:I19,H4,A21:A22)统计指数统计指数：同等加权指数同等加权指数同等加权指数消费品价格（元）销量期初期末期初期末大米500540300330鸡蛋55.860006450盐77.3150155布匹8085600670电视机300031008083书2022500560同等加权消费价格指数 104.10%=SUM(C4:C9)/SUM(B4:B9)同等加权消费数量指数 108.10%=SUM(E4:E9)/SUM

27、(D4:D9)统计指数统计指数：拉氏指数拉氏指数基期加权（拉氏）指数消费品价格（元）销量期初P0期末P1期初Q0期末Q1大米500540300330鸡蛋55.860006450盐77.3150155布匹8085600670电视机300031008083书2022500560P0*Q0P1*Q0P0*Q1大米150000=B4*D4162000=C4*D4165000=B4*E4鸡蛋300003480032250盐105010951085布匹480005100053600电视机240000248000249000书100001100011200479050=SUM(B12:B17)507895=

28、SUM(D12:D17)512135=SUM(F12:F17)拉氏消费价格指数106.02%=D18/B18拉氏消费数量指数106.91%=F18/B18统计指数统计指数：帕氏指数帕氏指数现期加权（帕氏）指数消费品价格（元）销量期初P0期末P1期初Q0期末Q1大米500540300330鸡蛋55.860006450盐77.3150155布匹8085600670电视机300031008083书2022500560P1*Q0P0*Q1P1*Q1大米162000=C4*D4165000=B4*E4178200=C4*E4鸡蛋348003225037410盐109510851131.5布匹510005

29、360056950电视机248000249000257300书110001120012320507895=SUM(B12:B17)512135=SUM(D12:D17)543311.5=SUM(F12:F17)帕氏消费价格指数106.97%=F18/B18帕氏消费数量指数106.09%=F18/D18统计指数统计指数：埃奇沃斯指数埃奇沃斯指数统计指数统计指数：费雪指数费雪指数统计指数统计指数：资本加权指数资本加权指数资本加权指数股票名称价格（元）发行在外的流通股股数(万股)期初P0期末P1期初S0期末S1A10.213.130003300B6.57.120003000C32.237.26000

30、70000D13.515.725002650E9.81131003500F25.426.352007000G10.111.833003500H9.27.815001600I12.51428003000I0100P0*S0P1*S1 A30600=B4*D443230=C4*E4B1300021300C1932002604000D3375041605E3038038500F132080184100G3333041300H1380012480I3500042000515140=SUM(B16:B24)3028515=SUM(D16:D24)股票资本加权指数587.90=D25/B25*B14某事件

31、发生的概率为p，不发生的概率为q，则在N次实验中该事件发生X次的概率为又称伯努利分布BINOMDIST(number,trials,probability,cumulative)number：实验成功的次数trials：独立实验的次数probability：每次独立实验成功的概率cumulative：true返回累积分布，false返回密度函数概率分布：概率分布：二项分布二项分布股票上涨的概率为0.6，20个交易日中上涨5天的概率，以及上涨小于等于5天的概率概率分布：概率分布：二项分布二项分布应用二项分布函数BINOMDIST求概率值N20X5p0.6上涨5天p(X)0.001294=BINO

32、MDIST(C3,C2,C4,0)上涨小于5天F(X5)0.001612=BINOMDIST(C3,C2,C4,1)股票上涨的概率为0.6，求20个交易日中概率分布函数大于等于临界值0.75的最小天数CRITBINOM(试验次数,每次试验的成功概率,临界值)概率分布：概率分布：二项分布二项分布应用二项分布函数CRITBINOM函数N20p0.60.75二项分布的分布函数值大于等于的最小X14=CRITBINOM(C2,C3,C4)在r次成功之前失败的次数X的概率(帕斯卡分布)NEGBINOMDIST(失败次数,成功次数,成功概率)灯泡合格概率0.8,求找到30个合格灯泡之前，需要测试10个不合

33、格灯泡的概率概率分布：概率分布：负二项分布负二项分布应用负二项分布函数NEGBINOMDIST求概率值p0.8r30X10测试30个合格之前须测试10个不合格的概率0.08059=NEGBINOMDIST(C4,C3,C2)25(N)种股票，15(M)只沪市，10(N-M)只深市，从中选10(n)种，其中有5(x)只属于沪市的概率HYPGEODIST(成功次数,样本数,总样本成功次数,总样本数)概率分布：概率分布：超几何分布超几何分布应用超几何分布函数HYPGEOMDIST求概率N25M15n10 x510只股票中5只为上海交易所的概率0.231512=HYPGEOMDIST(E3,C3,E2

34、,C2)随机事件发生的次数POISSON(事件数,期望值,返回的概率分布形式)平均每天呼叫20次，一天收到15次和小于等于15次呼叫的概率概率分布：概率分布：泊松分布泊松分布20 x15收到15次呼叫的概率0.051649=POISSON(E2,C2,0)收到小于等于15此呼叫的概率0.156513=POISSON(E2,C2,1)股票收益率呈正态分布，均值为5%，标准差为2%，求收益率为4%对应的概率密度函数值概率分布：概率分布：正态分布正态分布NORMDIST(x,均值,标准差,返回的概率分布形式)正态分布函数5.00%0.024%的收益率对应概率密度函数值17.60327=NORMDIS

35、T(0.04,C2,E2,0)小于等于4%的收益率概率0.308538=NORMDIST(0.04,C2,E2,1)股票收益率80%的可能性不超过某值，求这个值概率分布：概率分布：正态分布正态分布NORMSINV(概率,均值,标准差)正态分布函数5.00%0.0280%的概率下对应的临界值0.066832=NORMINV(0.8,C2,E2)概率分布：概率分布：正态分布正态分布00.050.10.150.2-0.05-0.110.90.80.70.60.50.40.30.20.10=0.01=0.02=0.03概率分布：概率分布：正态分布正态分布00.040.080.120.160.2-0.04-0.08-0.120510152025不同均值下正态分布概率密度函数图=0.03=0.05=0.07