社會統(tǒng)計(jì)學(xué)實(shí)驗(yàn)報告

1、 實(shí)驗(yàn)一：EXCEL的數(shù)據(jù)整理與顯示一、實(shí)驗(yàn)結(jié)果與數(shù)據(jù)處理 第1題 組距式次數(shù)分布表按日加工零件數(shù)分組（個）工人數(shù)（人）工人數(shù)比重（%） 100-11048 110-1201326 120-1302448 130-140918 合計(jì)50100 第2題 頻數(shù)分布表 工人日加工零件數(shù)/件 頻數(shù)/人 100-1104 110-12013 120-13024 130-1409 總數(shù)50第3題 優(yōu)秀率(日生產(chǎn)零件數(shù)130個以上)：9/50=18%二、討論與結(jié)論 通過實(shí)驗(yàn)一的實(shí)驗(yàn)操作與學(xué)習(xí)，我掌握了excel軟件的運(yùn)用以及用直方圖工具繪制頻數(shù)分布直方圖的方法。在實(shí)驗(yàn)操作過程中，我進(jìn)行了如下操作內(nèi)容：1、E

2、XCEL軟件的基本操作；2、編制組距式次數(shù)分布表；3、將次數(shù)分布表轉(zhuǎn)變成分布柱形圖；4、利用FREQUENCY函數(shù)進(jìn)行頻數(shù)統(tǒng)計(jì)；5、利用直方圖工具繪制頻數(shù)分布直方圖。 實(shí)驗(yàn)二：EXCEL的數(shù)據(jù)特征描述、抽樣推斷1、 實(shí)驗(yàn)結(jié)果與數(shù)據(jù)處理（1）COUNT（B4:B53）并回車，得到50個數(shù)據(jù)中的單位總量為50SUM（B4:B53）并回車，得到50個數(shù)據(jù)中的標(biāo)志總量為6127MAX（B4:B53）并回車，得到50個數(shù)據(jù)中的最大值為139MIN（B4:B53）并回車，得到50個數(shù)據(jù)中的最小值為107AVERAGE（B4:B53）并回車，得到50個數(shù)據(jù)中的平均值為122.54MEDIAN（B4:B53）

3、并回車，得到50個數(shù)據(jù)中的中位數(shù)為123GEOMEAN（B4:B53）并回車，得到50個數(shù)據(jù)中的幾何平均數(shù)為122.2679876HARMEAN（B4:B53）并回車，得到50個數(shù)據(jù)中的調(diào)和平均數(shù)為121.9951108AVEDEV（B4:B53）并回車，得到50個數(shù)據(jù)中的變異統(tǒng)計(jì)的平均差為6.4384STDEV（B4:B53）并回車，得到50個數(shù)據(jù)中的變異統(tǒng)計(jì)的標(biāo)準(zhǔn)差為8.234348171VAR（B4:B53）并回車，得到50個數(shù)據(jù)中的變異統(tǒng)計(jì)中的方差為67.8044898KURT（B4:B53）并回車，得到50個數(shù)據(jù)中的變異統(tǒng)計(jì)中的峰度為0.451931676SKEW（B4:B53）并回

4、車，得到50個數(shù)據(jù)中的變異統(tǒng)計(jì)中的偏度為0.026074（2）由（1）得均值為122.54，即企業(yè)職工的平均日加工零件數(shù)為122.54 由題意得置信度為95%，即1=0.95，則顯著性水平為=0.05 則極限誤差= CONFIDENCE(所在單元格，標(biāo)準(zhǔn)差所在單元格，樣本容量單元格)= CONFIDENCE（0.05，8.234348171，50）= 2.282402924，約等于2.28 所以日生產(chǎn)零件的置信區(qū)間為122.54-2.28，122.54+2.28，即120.26，124.82 （3）由題意得待檢假設(shè)為H0：u115 t值=（樣本均值單元格-115）/(樣本標(biāo)準(zhǔn)差單元格/SQRT

5、（樣本容量單元格）)=（122.54-115）/(8.234348171/SQRT（50）)=6.474811387，約等于6.47 t(n-1) =TINV(0.05,49)=2.009575237，約等于2.01，即=0.05，自由度為49的臨界值因?yàn)閨t|=6.47t(n-1)=2.01,則拒絕H0而接受H1，即企業(yè)日生產(chǎn)零件數(shù)有顯著提高二、討論與結(jié)論 通過實(shí)驗(yàn)二的實(shí)驗(yàn)操作與學(xué)習(xí)，我本著較熟練地掌握EXCEL在數(shù)據(jù)特征描述、抽樣推斷中的基本操作內(nèi)容，利用EXCEL軟件計(jì)算描述統(tǒng)計(jì)特征值的目的進(jìn)行學(xué)習(xí)和操作。這一次實(shí)驗(yàn)操作讓我掌握了以下方面：1、利用EXCEL的統(tǒng)計(jì)函數(shù)求解統(tǒng)計(jì)分布特征值；

6、2、利用CONFIDENCE函數(shù)計(jì)算極限誤差，從而得到相關(guān)的置信度 3、通過宏程序計(jì)算相關(guān)的t值，再利用TINV函數(shù)計(jì)算臨界值，最后比較t值和臨界值的大小來進(jìn)行假設(shè)檢驗(yàn)。 實(shí)驗(yàn)三：時間序列分析一、實(shí)驗(yàn)結(jié)果與數(shù)據(jù)處理月度第一年第二年第三年第四年150857458554224474694554383345366352341435432734142753744123883586359353332355736538139237684374604294419353344361382102953112913771145445339539812457486491473年/季度時間標(biāo)號銷售額移動平均值中心化后

7、的移動平均值比值2000/11993.122971.2332264.11542.9251627.98751.390735441943.31713.051833.08751.0601242001/151673.61953.1252161.08750.774425261931.52369.052511.08750.769189373927.82653.1252736.7251.435219483079.62820.3252897.96251.0626782002/292342.42975.62953.06250.793212102552.62930.5253104.6750.82217931137

8、47.53278.8253392.8251.1045374124472.83506.8253718.41.2028832003/1133254.43929.9754205.4250.7738582144245.24480.8754718.41250.8997093155951.14955.955037.1751.1814364166373.15118.45225.98751.2195022004/1173904.25333.5755496.26250.7103372185105.95658.955941.1250.8594163197252.66223.36420.6751.129574208

9、630.56618.056729.4751.2824922005/1215483.26840.97031.33750.7798232225997.37221.7757233.03750.8291543238776.17244.37199.351.2190134248720.67154.47161.11251.2177722006/1255123.67167.8257269.83750.70477522660517371.857324.4250.826143279592.272777254.351.3222694288341.27231.77328.51251.1381852007/129494

10、2.47425.3257338.81250.673462306825.57252.37300.03750.9349953318900.17347.7757356.21251.2098754328723.17364.657293.71.1959772008/1335009.97222.757112.33750.7043962346257.97001.9256894.73750.9076343358016.86787.556918.7251.1587114367865.67049.96995.1251.124442009/1376059.36940.356908.10.877132385819.7

11、6875.856908.6750.8423763397758.86941.54408128.2各季節(jié)指數(shù)計(jì)算表年/季1234合計(jì)20001.390735491.060123972.45085920010.7744250.7691891.43521911.062677664.0415120020.793210.8221791.104536781.202882963.9228120030.7738580.8997091.181436021.219501584.07450520040.7103370.8594161.129569711.282492323.98181620050.7798230.82

12、91541.219012831.217771684.04576120060.7047750.826141.322268711.138184593.99136820070.673460.9349951.209875331.195977354.01430820080.7043960.9076341.15871061.124440243.89518120090.877130.8423761.7195056.7914147.69079311.151364610.504052336.13762平均0.7546020.8545331.239040511.167116934.015292季節(jié)指數(shù)0.7517

13、280.8512781.23432171.162672034季節(jié)指數(shù)10.75172820.85127831.23432241.162672年/季度時間標(biāo)號銷售額(Y)季節(jié)指數(shù)(S)季節(jié)分離后的時間序列(Y/S)回歸后的趨勢(T)最終預(yù)測值預(yù)測誤差2000/11993.10.7517281321.092207.0981659.137-666.03722971.20.8512781140.8732370.8052018.214-1047.01332264.11.2343221834.2872534.5113128.402-864.302441943.31.1626721671.4092698.2

14、183137.142-1193.842001/151673.60.7517282226.3382861.9242151.388-477.788261931.50.8512782268.9413025.632575.653-644.153373927.81.2343223182.1533189.3373936.668-8.86761483079.61.1626722648.7263353.0433898.49-818.892002/292342.40.7517283116.0223516.752643.638-301.2382102552.60.8512782998.553680.4563133

15、.092-580.4923113747.51.2343223036.083844.1624744.933-997.4334124472.81.1626723847.0014007.8694659.837-187.0372003/1133254.40.7517284329.2274171.5753135.889118.51122144245.20.8512784986.8554335.2823690.53554.66983155951.11.2343224821.3534498.9885553.198397.90154166373.11.1626725481.4254662.6945421.18

16、4951.91562004/1173904.20.7517285193.6364826.4013628.139276.06072185105.90.8512785997.9234990.1074247.969857.93113197252.61.2343225875.7785153.8146361.46489151.1626727422.9885317.526182.5322447.9682005/1215483.20.7517287294.135481.2264120.391362.812225997.30.8512787045.0545644.9334805.40

17、81191.8923238776.11.2343227110.0595808.6397169.7291606.3714248720.61.1626727500.4815972.3466943.8791776.7212006/1255123.60.7517286815.7666136.0524612.64510.959522660510.8512787108.1366299.7585362.84668821.2343227771.2326463.4657977.9951614.2054288341.21.1626727174.1646627.1717705.227635

18、.97342007/1294942.40.7517286574.7216790.8785104.891-162.4912306825.50.8512788017.9446954.5845920.285905.21513318900.11.2343227210.5197118.298786.26113.83974328723.11.1626727502.6327281.9978466.574256.5262008/1335009.90.7517286664.5147445.7035597.142-587.2422346257.90.8512787351.1827609.416477.724-21

19、9.8243358016.81.2343226494.9037773.1169594.526-1577.734367865.61.1626726765.1067936.8229227.921-1362.322009/1376059.30.7517288060.4988100.5296089.392-30.09222385819.70.8512786836.4268264.2357035.162-1215.463397758.81.2343226285.8828427.94210402.79-2643.994408128.21.1626726990.9658591.6489989.269-186

20、1.072010/1410.7517288755.3546581.6432420.8512788919.0617592.6013431.2343229082.76711211.064441.1626729246.47410750.62SUMMARY OUTPUT回歸統(tǒng)計(jì)Multiple R0.882769R Square0.779281Adjusted R Square0.773473標(biāo)準(zhǔn)誤差1031.834觀測值40方差分析dfSSMSFSignificance F回歸分析1142842941142842941134.1654.93466E-14殘差3840457896.11064681.48總計(jì)39183300837Coefficients標(biāo)準(zhǔn)誤差t StatP-valueLower 95%Upper 95%Intercept2043.4332.5102656.145350023.5974E-071370.260132716.5238X Variable 1163.7114.133386811.58295964.9347E-14135.0949031192.31799二、討論與結(jié)論1、季節(jié)指數(shù)是以其平均數(shù)等于100%為條件構(gòu)成的，應(yīng)注意當(dāng)季節(jié)比率的平均值不等于1，需要進(jìn)行調(diào)整，即將每個季度比率的平均值除以它們的總平均值，從而使季節(jié)比率的平均值