社会统计学实验报告

1、 实验一：EXCEL的数据整理与显示一、实验结果与数据处理 第1题 组距式次数分布表按日加工零件数分组（个）工人数（人）工人数比重（%） 100-11048 110-1201326 120-1302448 130-140918 合计50100 第2题 频数分布表 工人日加工零件数/件 频数/人 100-1104 110-12013 120-13024 130-1409 总数50第3题 优秀率(日生产零件数130个以上)：9/50=18%二、讨论与结论 通过实验一的实验操作与学习，我掌握了excel软件的运用以及用直方图工具绘制频数分布直方图的方法。在实验操作过程中，我进行了如下操作内容：1、E

2、XCEL软件的基本操作；2、编制组距式次数分布表；3、将次数分布表转变成分布柱形图；4、利用FREQUENCY函数进行频数统计；5、利用直方图工具绘制频数分布直方图。 实验二：EXCEL的数据特征描述、抽样推断1、 实验结果与数据处理（1）COUNT（B4:B53）并回车，得到50个数据中的单位总量为50SUM（B4:B53）并回车，得到50个数据中的标志总量为6127MAX（B4:B53）并回车，得到50个数据中的最大值为139MIN（B4:B53）并回车，得到50个数据中的最小值为107AVERAGE（B4:B53）并回车，得到50个数据中的平均值为122.54MEDIAN（B4:B53）

3、并回车，得到50个数据中的中位数为123GEOMEAN（B4:B53）并回车，得到50个数据中的几何平均数为122.2679876HARMEAN（B4:B53）并回车，得到50个数据中的调和平均数为121.9951108AVEDEV（B4:B53）并回车，得到50个数据中的变异统计的平均差为6.4384STDEV（B4:B53）并回车，得到50个数据中的变异统计的标准差为8.234348171VAR（B4:B53）并回车，得到50个数据中的变异统计中的方差为67.8044898KURT（B4:B53）并回车，得到50个数据中的变异统计中的峰度为0.451931676SKEW（B4:B53）并回

4、车，得到50个数据中的变异统计中的偏度为0.026074（2）由（1）得均值为122.54，即企业职工的平均日加工零件数为122.54 由题意得置信度为95%，即1=0.95，则显著性水平为=0.05 则极限误差= CONFIDENCE(所在单元格，标准差所在单元格，样本容量单元格)= CONFIDENCE（0.05，8.234348171，50）= 2.282402924，约等于2.28 所以日生产零件的置信区间为122.54-2.28，122.54+2.28，即120.26，124.82 （3）由题意得待检假设为H0：u115 t值=（样本均值单元格-115）/(样本标准差单元格/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的临界值因为|t|=6.47t(n-1)=2.01,则拒绝H0而接受H1，即企业日生产零件数有显著提高二、讨论与结论 通过实验二的实验操作与学习，我本着较熟练地掌握EXCEL在数据特征描述、抽样推断中的基本操作内容，利用EXCEL软件计算描述统计特征值的目的进行学习和操作。这一次实验操作让我掌握了以下方面：1、利用EXCEL的统计函数求解统计分布特征值；

6、2、利用CONFIDENCE函数计算极限误差，从而得到相关的置信度 3、通过宏程序计算相关的t值，再利用TINV函数计算临界值，最后比较t值和临界值的大小来进行假设检验。 实验三：时间序列分析一、实验结果与数据处理月度第一年第二年第三年第四年150857458554224474694554383345366352341435432734142753744123883586359353332355736538139237684374604294419353344361382102953112913771145445339539812457486491473年/季度时间标号销售额移动平均值中心化后

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各季节指数计算表年/季1234合计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季节指数0.7517

13、280.8512781.23432171.162672034季节指数10.75172820.85127831.23432241.162672年/季度时间标号销售额(Y)季节指数(S)季节分离后的时间序列(Y/S)回归后的趋势(T)最终预测值预测误差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回归统计Multiple R0.882769R Square0.779281Adjusted R Square0.773473标准误差1031.834观测值40方差分析dfSSMSFSignificance F回归分析1142842941142842941134.1654.93466E-14残差3840457896.11064681.48总计39183300837Coefficients标准误差t StatP-valueLower 95%Upper 95%Intercept2043.4332.5102656.145350023.5974E-071370.260132716.5238X Variable 1163.7114.133386811.58295964.9347E-14135.0949031192.31799二、讨论与结论1、季节指数是以其平均数等于100%为条件构成的，应注意当季节比率的平均值不等于1，需要进行调整，即将每个季度比率的平均值除以它们的总平均值，从而使季节比率的平均值