Excel与统计分析

Excel与统计分析鞍山市统计局数据处二○一二年四月Excel与统计分析一、Excel基本操作二、数据整理三、数据分析Excel基本操作页面设置打印区域设置窗口冻结单元格属性选择性粘贴加载宏Excel基本操作页面设置文件——页面设置Excel基本操作打印区域设置Excel基本操作窗口冻结Excel基本操作单元格属性格式——单元格Excel基本操作单元格属性Excel基本操作选择性粘贴编辑——选择性粘贴Excel基本操作加载宏工具——加载宏数据处理数据的导入数据处理数据的导入数据处理数据的导入数据处理有效性设置选择要应用数据有效性的单元格数据处理有效性设置选择“数据”|“有效性”菜单项，打开“数据有效性”对话框数据处理有效性设置数据处理数据筛选与排序数据筛选有两方面内容：一是将某些不符合要求的数据或有明显错误的数据予以剔除；二是将符合某种特定条件的数据筛选出来，不符合特定条件的数据予以剔除。排序的目的是为了便于研究者通过浏览数据发现一些明显的特征或趋势，找到解决问题的线索。排序还有助于对数据检查、纠错，为重新分组或归类提供依据。数据处理数据筛选1.自动筛选选择“数据”|“筛选”|“自动筛选”菜单项，数据清单进入自动筛选状态。数据处理数据筛选2.高级筛选选择“数据”|“筛选”|“高级筛选”菜单项数据处理数据排序选择“数据”|“排序”菜单项，打开“排序”对话框数据处理列表的使用选择“数据”|“列表”|“创建列表”菜单项，打开“创建列表”对话框数据处理分类汇总分类汇总前，要对分类汇总的数据区域按分类字段进行排序选择“数据”|“分类汇总”，弹出“分类汇总”对话框数据处理数据分组使用VLOOKUP函数实现对数据分组。VLOOKUP函数的作用是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。在VLOOKUP中的V代表垂直。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP。数据处理数据分组数据处理数据分组VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value：要在区域中查找的值table_array：包含数据的区域col_index_num：希望返回的匹配值在查找区域中的列序号range_lookup：近似匹配（1）还是精确匹配（0），默认值为1。上例单元格B2中公式的含义即为：在D2：E12区域中寻找最接近A2，又不大于A2的值，找到后在B2单元格中返回D2：E12区域第2列的相应值。数据处理频数分析1、利用FREQUENCY函数进行频数分析FREQUENCY(data_array,bins_array)：以一列垂直数组返回某个区域中数据的频率分布Data_array：为一数组或对一组数值的引用，用来计算频率Bins_array：为间隔的数组或对间隔的引用，该间隔用于对data_array中的数值进行分组。数据处理频数分析数据处理频数分析2、利用COUNTIF函数进行频数分析COUNTIF(range,criteria)：计算区域中满足给定条件的单元格的个数Range：为需要计算其中满足条件的单元格数目的单元格区域Criteria：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本数据处理频数分析=D9-D8=MAX(A1：J5)数据处理频数分析3、直方图频数分析选择“工具”|“数据分析”选项，在弹出对话框中选择“直方图”，打开“直方图”对话框数据处理频数分析数据处理数据抽样INDEX函数①数组形式：INDEX（array，row_num，column_num）返回数组中指定单元格或单元格数组的数值②引用形式：INDEX（reference，row_num，column_num，area_num）返回引用中指定单元格区域的引用RAND函数：RAND（）返回大于等于0小于1的均匀分布随机数，每次计算工作表时都将返回一个新的数值CEILING函数：CEILING（number，significance）将参数number向上舍入为最接近的significance的倍数INT函数：INT（number）将数字向下舍入到最接近的整数数据处理数据抽样随机数发生器选择“工具”|“加载宏”，在弹出的“加载宏”对话框中选择分析工具库。选择“工具”|“数据分析”，在弹出的对话框中选择随机数发生器数据处理数据抽样1、用INDEX函数进行数据抽样（1）选择A2：A21区域，在编辑栏中输入如下图所示公式，按Ctrl+Shift+Enter，生成随机数数组数据处理数据抽样（2）选择B2：B21区域，在编辑栏中输入如下图所示公式，按Ctrl+Shift+Enter数据处理数据抽样（3）拖动B2：B21区域的公式，完成数据抽样数据处理数据抽样2、用VLOOKUP函数进行数据抽样（1）用随机数发生器生成20个随机数，并用INT函数取整数据处理数据抽样（2）在C3：C22区域输入如下图所示公式数据处理数据抽样（3）拖动C3：C22区域的公式，完成数据抽样数据分析数据透视表数据透视表是Excel中强有力的数据列表分析工具，它不仅可以用来作单变量数据的次数分布或综合分析，还可以用来作双变量数据的交叉频数分析、总和分析和其它统计计量的分析数据分析数据透视表选择“数据”|“数据透视表和数据透视图”，弹出数据透视表向导数据分析数据透视表数据分析数据透视表数据分析数据透视表数据分析离散程度描述相关函数1.极值：MAX(number1,number2,...)；MIN(number1,number2,...)2.平均值：AVERAGE(number1,number2,...)3.绝对值：ABS(number)4.标准差：STDEV(number1,number2,...)5.总体标准差：STDEVP(number1,number2,...)6.方差：VAR(number1,number2,...)7.总体方差：VARP(number1,number2,...)8.置信区间：CONFIDENCE(alpha,standard_dev,size)数据分析离散程度描述描述统计工具的使用选择“工具”|“数据分析”，在弹出的对话框中选择“描述统计”数据分析离散程度描述数据分析相关与回归分析1、相关系数CORREL(array1,array2)：返回单元格区域array1和array2之间的相关系数2、一元线性回归截距INTERCEPT(known_y‘s,known_x’s)：利用现有的x值与y值计算直线与y轴的截距。截距为穿过已知的known_x's和known_y's数据点的线性回归线与y轴的交点斜率SLOPE(known_y‘s,known_x’s)：返回根据known_y's和known_x's中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的重直距离与水平距离的比值数据分析相关与回归分析一元线性回归模型的应用例题：已知某地区1978-2003年国内生产总值GDP与货运周转量的数据如右表所示，试对其进行一元线性回归分析。年份GDP（亿元）货运周转量(亿吨公里)年份GDP（亿元）货运周转量(亿吨公里)1978591991443219798.71219924734198012141993543719811615199456.54119821917199556441983222019965743.519842520.519975943.519852823.519986343.519863630199966.5441987403520006745.519884132200170.54719893224200270.6461990342820037352数据分析①绘制散点图由散点图可以看出，GDP与货运周转量之间线性相关，可以建立一元线性回归方程，进行回归分析。散点图010203040506001020304050607080GDP（亿元）货运周转量（亿吨公里）系列1数据分析②回归分析选择“工具”|“数据分析”，在弹出的对话框中选择“回归”，弹出回归对话框数据分析分析结果如下数据分析第一部分为汇总统计，MultipleR指复相关系数，RSquare指判定系数，Adjusted指调整的判定系数，标准误差指估计的标准误，观测值指样本容量；第二部分为方差分析，df指自由度，SS指平方和，MS指均方，F指F统计量，SignificanceofF指p值；第三部分包括：Intercept指截距，Coefficient指系数，tstat指t统计量，P-value指p值。