數(shù)據(jù)庫概論-Excel統(tǒng)計分析

數(shù)據(jù)庫概論

Excel統(tǒng)計分析職場人生：Excel+ppt+word1統(tǒng)計基礎與數(shù)據(jù)描述2描述性統(tǒng)計3數(shù)據(jù)庫統(tǒng)計函數(shù)4統(tǒng)計指數(shù)5概率分布6參數(shù)估計7假設檢驗目錄例1.1：上證180指數(shù)的月收益率統(tǒng)計基礎與數(shù)據(jù)描述上證180指數(shù)時間指數(shù)收益率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函數(shù)：計算區(qū)域內(nèi)滿足給定條件的單元格的個數(shù)COUNTIF(Range,Criteria)，Range表示區(qū)域,Criteria表示條件統(tǒng)計基礎與數(shù)據(jù)描述上證180按指數(shù)分組組號上證180累積頻率觀測值數(shù)目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函數(shù)：返回區(qū)域內(nèi)數(shù)據(jù)的頻率分布Frequency(data_array,bins_array)，data_array統(tǒng)計基礎與數(shù)據(jù)描述上證180指數(shù)按收益率分組應用FREQUENCY函數(shù)間隔觀測值數(shù)目-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頻率分布圖統(tǒng)計基礎與數(shù)據(jù)描述上證180按指數(shù)分組組號上證180觀測值數(shù)目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相對頻率分布圖統(tǒng)計基礎與數(shù)據(jù)描述上證180按指數(shù)分組組號上證180觀測值數(shù)目相對頻率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累積頻率分布圖統(tǒng)計基礎與數(shù)據(jù)描述上證180按指數(shù)分組組號指數(shù)值累積頻率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直方圖統(tǒng)計基礎與數(shù)據(jù)描述接收頻率累積%-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%中心趨勢離中趨勢偏度峰度描述性統(tǒng)計中心趨勢：算術平均值股票價格算術平均值代碼證券名價格600000浦發(fā)銀行7.24600004白云機場8.54600006東風汽車2.94600008首創(chuàng)股份8.2600009上海機場16.65600011華能國際6.97600015華夏銀行4.05600016民生銀行5.69600018上港集箱16.37600019寶鋼股份6.17求和82.82算術平均值8.282股票價格算術平均值代碼證券名價格600000浦發(fā)銀行7.24600004白云機場8.54600006東風汽車2.94600008首創(chuàng)股份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指數(shù)收益率幾何平均值時間指數(shù)值收益率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函數(shù)求幾何平均值-0.01491中心趨勢：幾何平均值D16=GEOMEAN(D4:D15)-1眾數(shù):出現(xiàn)頻率最高的數(shù)中心趨勢：眾數(shù)年齡的眾數(shù)20201921212018232022192121182121眾數(shù)21B10=MODE(A2:B9)中位數(shù):居中的數(shù)中心趨勢：中位數(shù)非組數(shù)據(jù)的中位數(shù)812912101310141115111611181119中位數(shù)11.5B10=MEDIAN(A2:B9)調(diào)和平均數(shù):數(shù)的倒數(shù)的算術平均值的倒數(shù)中心趨勢：調(diào)和平均數(shù)求調(diào)和平均數(shù)X1/X50.2080.13120.08160.06170.0690.11200.05調(diào)和平均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)離中趨勢：四分位數(shù)B13=QUARTILE(B3:B12,1)B14=QUARTILE(B3:B12,3)四分位數(shù)：位于25%和75%位置的數(shù)稱為低四分位數(shù)和高四分位數(shù),刻畫數(shù)據(jù)相對于中位數(shù)的離散程度日期價格200503077.65200503087.78200503097.57200503107.45200503117.41200503147.37200503157.16200503167.1620050317719Q17.17Q37.54QUARTILE(Array,Quart)Quart= 0,min 1,低四分位數(shù) 2,中位數(shù) 3,高四分位數(shù) 4,max離中趨勢：方差系數(shù)B15=AVERAGE(B3:B14)B16=STDEV(B3:B14)C15=AVERAGE(C3:C14)C16=STDEV(C3:C14)B17=B16/B15C17=C16/C15方差系數(shù)：標準差與均值之間的比值。消除了量綱影響采用方差系數(shù)度量分散程度日期東風汽車上海機場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方差系數(shù)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)偏度：四分位數(shù)偏度系數(shù)日期價格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4Q15.93Q26.10Q36.34四分位偏度系數(shù)0.1677019B21=QUARTILE(B3:B20,1)B22=QUARTILE(B3:B20,2)B23=QUARTILE(B3:B20,3)B24=(B23+B21-2*B22)/(B23-B21)偏度：Spearman偏度系數(shù)日期價格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4平均值6.05標準差0.333500中位數(shù)6.1Spearman偏度系數(shù)-0.46B21=AVERAGE(B3:B20)B22=STDEV(B3:B20)B23=MEDIAN(B3:B20)B24=3*(B21-B23)/B22峰度：峰值日期上證指數(shù)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)峰度：矩峰度系數(shù)＝3，常峰態(tài)，正態(tài)分布<3，低峰態(tài)>3，高峰態(tài)日期上證指數(shù)（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：數(shù)據(jù)庫單元格區(qū)域field：字段criteria：包含過濾條件的單元格區(qū)域DCOUNT,DCOUNTA,DSUM,DPRODUCT,DMAX,DMIN,DAVERAGE,DVAR,DSTDEV,DVARP,DSTDEVP,DGET數(shù)據(jù)庫統(tǒng)計函數(shù)數(shù)據(jù)庫方差、標準差函數(shù)利用DVAR和DSTDEV求樣本方差標準差Gold公司銷售清單銷售單號客戶類型類別生產(chǎn)商型號數(shù)量單價總價銷售員040500001個人臺式電腦戴爾G2802900018000劉040500002單位臺式電腦惠普H52031000030000王040500003個人筆記本惠普H32011500015000李040500004個人筆記本聯(lián)想L2021200024000劉040500005單位臺式電腦聯(lián)想L325800040000王040500006單位臺式電腦聯(lián)想L322800016000孫040500006個人臺式電腦戴爾G2805900045000王040500008個人筆記本戴爾G50021300026000李040500009單位臺式電腦聯(lián)想L322800016000李040500010個人筆記本戴爾G50061300078000王040500011單位臺式電腦聯(lián)想L325800040000劉040500012個人筆記本戴爾G50021300026000王040500013個人臺式電腦惠普H52011000010000劉040500014單位筆記本惠普H32051500075000劉040500015單位臺式電腦聯(lián)想L326800048000劉總價樣本方差>25000374250000<=DVAR(A4:I19,H4,A21:A22)樣本標準差19345.54212<=DSTDEV(A4:I19,H4,A21:A22)統(tǒng)計指數(shù)：同等加權指數(shù)同等加權指數(shù)消費品價格（元）銷量期初期末期初期末大米500540300330雞蛋55.860006450鹽77.3150155布匹8085600670電視機300031008083書2022500560同等加權消費價格指數(shù)104.10%<=SUM(C4:C9)/SUM(B4:B9)同等加權消費數(shù)量指數(shù)108.10%<=SUM(E4:E9)/SUM(D4:D9)統(tǒng)計指數(shù)：拉氏指數(shù)基期加權（拉氏）指數(shù)消費品價格（元）銷量期初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)拉氏消費價格指數(shù)106.02%<=D18/B18拉氏消費數(shù)量指數(shù)106.91%<=F18/B18統(tǒng)計指數(shù)：帕氏指數(shù)現(xiàn)期加權（帕氏）指數(shù)消費品價格（元）銷量期初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)帕氏消費價格指數(shù)106.97%<=F18/B18帕氏消費數(shù)量指數(shù)106.09%<=F18/D18統(tǒng)計指數(shù)：埃奇沃斯指數(shù)統(tǒng)計指數(shù)：費雪指數(shù)統(tǒng)計指數(shù)：資本加權指數(shù)資本加權指數(shù)股票名稱價格（元）發(fā)行在外的流通股股數(shù)(萬股)期初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)股票資本加權指數(shù)587.90<=D25/B25*B14某事件發(fā)生的概率為p，不發(fā)生的概率為q，則在N次實驗中該事件發(fā)生X次的概率為

又稱伯努利分布

BINOMDIST(number,trials,probability,cumulative)

number：實驗成功的次數(shù) trials：獨立實驗的次數(shù) probability：每次獨立實驗成功的概率 cumulative：true返回累積分布，false返回密度函數(shù)概率分布：二項分布股票上漲的概率為0.6，20個交易日中上漲5天的概率，以及上漲小于等于5天的概率概率分布：二項分布應用二項分布函數(shù)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個交易日中概率分布函數(shù)大于等于臨界值0.75的最小天數(shù)CRITBINOM(試驗次數(shù),每次試驗的成功概率,臨界值)概率分布：二項分布應用二項分布函數(shù)CRITBINOM函數(shù)N20p0.6α0.75二項分布的分布函數(shù)值大于等于α的最小X14<=CRITBINOM(C2,C3,C4)在r次成功之前失敗的次數(shù)X的概率(帕斯卡分布)NEGBINOMDIST(失敗次數(shù),成功次數(shù),成功概率)燈泡合格概率0.8,求找到30個合格燈泡之前，需要測試10個不合格燈泡的概率概率分布：負二項分布應用負二項分布函數(shù)NEGBINOMDIST求概率值p0.8r30X10測試30個合格之前須測試10個不合格的概率0.08059<=NEGBINOMDIST(C4,C3,C2)

25(N)種股票，15(M)只滬市，10(N-M)只深市，從中選10(n)種，其中有5(x)只屬于滬市的概率HYPGEODIST(成功次數(shù),樣本數(shù),總樣本成功次數(shù),總樣本數(shù))概率分布：超幾何分布應用超幾何分布函數(shù)HYPGEOMDIST求概率N25M15n10x510只股票中5只為上海交易所的概率0.231512<=HYPGEOMDIST(E3,C3,E2,C2)隨機事件發(fā)生的次數(shù)POISSON(事件數(shù),期望值,返回的概率分布形式)平均每天呼叫20次，一天收到15次和小于等于15次呼叫的概率概率分布：泊松分布λ20x15收到15次呼叫的概率0.051649<=POISSON(E2,C2,0)收到小于等于15此呼叫的概率0.156513<=POISSON(E2,C2,1)股票收益率呈正態(tài)分布，均值為5%，標準差為2%，求收益率為4%對應的概率密度函數(shù)值概率分布：正態(tài)分布NORMDIST(x,均值,標準差,返回的概率分布形式)正態(tài)分布函數(shù)μ5.00%σ0.024%的收益率對應概率密度函數(shù)值17.60327<=NORMDIST(0.04,C2,E2,0)小于等于4%的收益率概率0.308538<=NORMDIST(0.04,C2,E2,1)股票收益率80%的可能性不超過某值，求這個值概率分布：正態(tài)分布NORMSINV(概率,均值,標準差)正態(tài)分布函數(shù)μ5.00%σ0.0280%的概率下對應的臨界值0.066832<=NORMINV(0.8,C2,E2)概率分布：正態(tài)分布概率分布：正態(tài)分布參數(shù)估計：總體均值估計52σ5α0.05n40μ點估計52〈=C2運用CONFIDENCE函數(shù)區(qū)間上限53.55〈=C2+CONFIDENCE(C3,E2,E3)區(qū)間下限50.45〈=C2-CONFIDENCE(C3,E2,E3)CONFIDENCE( alpha, standard_dev, size)參數(shù)估計：總體均值估計方差未知且為小樣本下總體均值的估計50505651495347525353495355485055n16α0.0551.5<=AVERAGE(B2:E5)s2.65832<=STDEV(B2:E5)tα/22.13145<=TINV(E7,C7-1)μ區(qū)間估計區(qū)間上限52.92<=C9+C11*C10/SQRT(C7)區(qū)間下限50.08<=C9-C11*C10/SQRT(C7)參數(shù)估計：總體均值估計方差未知且為大樣本下總體均值的估計n50α0.0551.46〈=AVERAGE(B2:F11)s2.666573〈=STDEV(B2:F11)μ區(qū)間估計區(qū)間上限52.20〈=C15+CONFIDENCE(E13,C16,C13)區(qū)間下限50.72〈=C15-CONFIDENCE(E13,C16,C13)5050565148495347525253534953505548505553505552485353535657505249535850474849505150495352514852495553參數(shù)估計：總體比例估計在某次選舉中，隨機抽取100位選民，60%的支持候選人A，分別求在95%和99%的置信水平下，A的支持率參數(shù)估計：總體比例估計總體比例的估計n1000.6α0.05Zα/2-1.95996<=NORMSINV(C5/2)p區(qū)間估計區(qū)間上限69.60%<=C3-C6*(C3*(1-C3)/C2)^0.5區(qū)間下限50.40%<=C3+C6*(C3*(1-C3)/C2)^0.5α0.01Zα/2-2.57583<=NORMSINV(C11/2)p區(qū)間估計區(qū)間上限72.62%<=C3-C12*(C3*(1-C3)/C2)^0.5區(qū)間下限47.38%<=C3+C12*(C3*(1-C3)/C2)^0.5參數(shù)估計：總體比例之差估計A車間抽取60件樣品，合格率90%，B車間抽取50件樣品，合格率85%在某次選舉中，求在95%的置信水平下，兩個車間合格率的置信區(qū)間參數(shù)估計：總體比例之差估計n160n250α0.050.90.850.05<=C6-C7Zα/2-1.95996<=