数据库概论-Excel统计分析

数据库概论

Excel统计分析职场人生：Excel+ppt+word1统计基础与数据描述2描述性统计3数据库统计函数4统计指数5概率分布6参数估计7假设检验目录例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.2COUNTIF函数：计算区域内满足给定条件的单元格的个数COUNTIF(Range,Criteria)，Range表示区域,Criteria表示条件统计基础与数据描述上证180按指数分组组号上证180累积频率观测值数目1≤2400.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.3Frequency函数：返回区域内数据的频率分布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观测值数目1≤2400.0122400.0-2500.0332500.0-2600.0542600.0-2700.0352700.0-2800.0462800.0-2900.0972900.0-3000.0383000.0-3100.0393100.0-3200.03103200.0-3300.02例1.5相对频率分布图统计基础与数据描述上证180按指数分组组号上证180观测值数目相对频率1≤2400.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.055555636D3=C3/$C$13例1.6累积频率分布图统计基础与数据描述上证180按指数分组组号指数值累积频率1≤2400.0122400.0-2500.0432500.0-2600.0942600.0-2700.01252700.0-2800.01662800.0-2900.02572900.0-3000.02883000.0-3100.03193100.0-3200.034103200.0-3300.036例1.7直方图统计基础与数据描述接收频率累积%-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.97600015华夏银行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)中心趋势：几何平均值第一年投资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/62528.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众数:出现频率最高的数中心趋势：众数年龄的众数20201921212018232022192121182121众数21B10=MODE(A2:B9)中位数:居中的数中心趋势：中位数非组数据的中位数812912101310141115111611181119中位数11.5B10=MEDIAN(A2:B9)调和平均数:数的倒数的算术平均值的倒数中心趋势：调和平均数求调和平均数X1/X50.2080.13120.08160.06170.0690.11200.05调和平均10.13B3=1/A3B10=1/SUM(B3:B9)离中趋势：方差日期价格200503077.65200503087.78200503097.57200503107.45200503117.41200503147.37200503157.16200503167.1620050317719样本方差0.052201B13=VAR(B3:B12)离中趋势：标准差日期价格200503077.65200503087.78200503097.57200503107.45200503117.41200503147.37200503157.16200503167.1620050317719样本标准差0.228476B13=STDEV(B3:B12)离中趋势：四分位数B13=QUARTILE(B3:B12,1)B14=QUARTILE(B3:B12,3)四分位数：位于25%和75%位置的数称为低四分位数和高四分位数,刻画数据相对于中位数的离散程度日期价格200503077.65200503087.78200503097.57200503107.45200503117.41200503147.37200503157.16200503167.1620050317719Q17.17Q37.54QUARTILE(Array,Quart)Quart= 0,min 1,低四分位数 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.9716.52200503212.9416.65200503222.71177416.9200503242.7616.86200503252.7516.79平均值2.9616.74标准差0.1756270.3158346方差系数0.05940.0189偏度：偏斜度股价偏斜度日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4偏斜度-0.9236B21=SKEW(B3:B20)偏度：四分位数偏度系数日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4Q15.93Q26.10Q36.34四分位偏度系数0.1677019B21=QUARTILE(B3:B20,1)B22=QUARTILE(B3:B20,2)B23=QUARTILE(B3:B20,3)B24=(B23+B21-2*B22)/(B23-B21)偏度：Spearman偏度系数日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4平均值6.05标准差0.333500中位数6.1Spearman偏度系数-0.46B21=AVERAGE(B3:B20)B22=STDEV(B3:B20)B23=MEDIAN(B3:B20)B24=3*(B21-B23)/B22峰度：峰值日期上证指数200503011,303.41200503021,287.45200503031,294.34200503041,287.71200503071,293.74200503081,318.27200503091,316.79200503101,286.23200503111,289.94200503141,293.50200503151,269255.59200503171,243.47200503181,227.40200503211,231.05200503221,206.92200503231,201.65200503241,208.19200503251,205.63峰值-1.36420044B22=KURT(B3:B21)峰度：矩峰度系数＝3，常峰态，正态分布<3，低峰态>3，高峰态日期上证指数（Xi-X）2（Xi-X）4200503011,303.411534.872355814.24200503021,287.45539.05290570.80200503031,294.34906.45821658.19200503041,287.71551.19303806.92200503071,293.74870.68758092.01200503081,318.272920.048526617316.792762.287630174.09200503101,286.23483.88234143.94200503111,289.94660.87436747.56200503141,293.50856.58733727.33200503151,269.1424.08579.96200503161,255.5974.705579.36200503171,243.47431.09185835.89200503181,227.401356.641840479.55200503211,231.051101.091212392.67200503221,206.923284.7410789502.01200503231,201.653916.5915339644208.193140.789864477.36200503251,205.633434.2711794199.641,264.2328849.8573124042.800.000001B22=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,DSUM,DPRODUCT,DMAX,DMIN,DAVERAGE,DVAR,DSTDEV,DVARP,DSTDEVP,DGET数据库统计函数数据库方差、标准差函数利用DVAR和DSTDEV求样本方差标准差Gold公司销售清单销售单号客户类型类别生产商型号数量单价总价销售员040500001个人台式电脑戴尔G2802900018000刘040500002单位台式电脑惠普H52031000030000王040500003个人笔记本惠普H32011500015000李040500004个人笔记本联想L2021200024000刘040500005单位台式电脑联想L325800040000王040500006单位台式电脑联想L322800016000孙040500006个人台式电脑戴尔G2805900045000王040500008个人笔记本戴尔G50021300026000李040500009单位台式电脑联想L322800016000李040500010个人笔记本戴尔G50061300078000王040500011单位台式电脑联想L325800040000刘040500012个人笔记本戴尔G50021300026000王040500013个人台式电脑惠普H52011000010000刘040500014单位笔记本惠普H32051500075000刘040500015单位台式电脑联想L326800048000刘总价样本方差>25000374250000<=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(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<=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布匹510005360056950电视机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.2600070000D13.515.725002650E9.81131003500F25.426.352007000G10.111.833003500H9.27.815001600I12.51428003000I0100P0*S0P1*S1A30600<=B4*D443230<=C4*E4B1300021300C1932002604000D3375041605E3038038500F132080184100G3333041300H1380012480I3500042000515140<=SUM(B16:B24)3028515<=SUM(D16:D24)股票资本加权指数587.90<=D25/B25*B14某事件发生的概率为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<=BINOMDIST(C3,C2,C4,0)上涨小于5天F(X≤5)0.001612<=BINOMDIST(C3,C2,C4,1)股票上涨的概率为0.6，求20个交易日中概率分布函数大于等于临界值0.75的最小天数CRITBINOM(试验次数,每次试验的成功概率,临界值)概率分布：二项分布应用二项分布函数CRITBINOM函数N20p0.6α0.75二项分布的分布函数值大于等于α的最小X14<=CRITBINOM(C2,C3,C4)在r次成功之前失败的次数X的概率(帕斯卡分布)NEGBINOMDIST(失败次数,成功次数,成功概率)灯泡合格概率0.8,求找到30个合格灯泡之前，需要测试10个不合格灯泡的概率概率分布：负二项分布应用负二项分布函数NEGBINOMDIST求概率值p0.8r30X10测试30个合格之前须测试10个不合格的概率0.08059<=NEGBINOMDIST(C4,C3,C2)

25(N)种股票，15(M)只沪市，10(N-M)只深市，从中选10(n)种，其中有5(x)只属于沪市的概率HYPGEODIST(成功次数,样本数,总样本成功次数,总样本数)概率分布：超几何分布应用超几何分布函数HYPGEOMDIST求概率N25M15n10x510只股票中5只为上海交易所的概率0.231512<=HYPGEOMDIST(E3,C3,E2,C2)随机事件发生的次数POISSON(事件数,期望值,返回的概率分布形式)平均每天呼叫20次，一天收到15次和小于等于15次呼叫的概率概率分布：泊松分布λ20x15收到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<=NORMDIST(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)概率分布：正态分布概率分布：正态分布参数估计：总体均值估计52σ5α0.05n40μ点估计52〈=C2运用CONFIDENCE函数区间上限53.55〈=C2+CONFIDENCE(C3,E2,E3)区间下限50.45〈=C2-CONFIDENCE(C3,E2,E3)CONFIDENCE( alpha, standard_dev, size)参数估计：总体均值估计方差未知且为小样本下总体均值的估计50505651495347525353495355485055n16α0.0551.5<=AVERAGE(B2:E5)s2.65832<=STDEV(B2:E5)tα/22.13145<=TINV(E7,C7-1)μ区间估计区间上限52.92<=C9+C11*C10/SQRT(C7)区间下限50.08<=C9-C11*C10/SQRT(C7)参数估计：总体均值估计方差未知且为大样本下总体均值的估计n50α0.0551.46〈=AVERAGE(B2:F11)s2.666573〈=STDEV(B2:F11)μ区间估计区间上限52.20〈=C15+CONFIDENCE(E13,C16,C13)区间下限50.72〈=C15-CONFIDENCE(E13,C16,C13)5050565148495347525253534953505548505553505552485353535657505249535850474849505150495352514852495553参数估计：总体比例估计在某次选举中，随机抽取100位选民，60%的支持候选人A，分别求在95%和99%的置信水平下，A的支持率参数估计：总体比例估计总体比例的估计n1000.6α0.05Zα/2-1.95996<=NORMSINV(C5/2)p区间估计区间上限69.60%<=C3-C6*(C3*(1-C3)/C2)^0.5区间下限50.40%<=C3+C6*(C3*(1-C3)/C2)^0.5α0.01Zα/2-2.57583<=NORMSINV(C11/2)p区间估计区间上限72.62%<=C3-C12*(C3*(1-C3)/C2)^0.5区间下限47.38%<=C3+C12*(C3*(1-C3)/C2)^0.5参数估计：总体比例之差估计A车间抽取60件样品，合格率90%，B车间抽取50件样品，合格率85%在某次选举中，求在95%的置信水平下，两个车间合格率的置信区间参数估计：总体比例之差估计n160n250α0.050.90.850.05<=C6-C7Zα/2-1.95996<=