Excel在市场调查和统计中的应用2

第二章研究程序与抽样一、研究步骤和甘特图绘制研究的步骤：1、确定问题和目的2、决定研究设计3、决定收集数据的方法4、抽样设计5、撰写计划书6、收集数据7、分析及解释数据8、提出报告一、绘制“工作进度表”（甘特图）二、简单随机抽样假定要在10000个号码中（编号0000-9999）抽出500个号码1、函数法2、随机数发生器法三、描述统计3.1.2用Excel工作表函数描述集中趋势1．均值（MEAN）（1）算术平均数（2）加权平均数（3）几何平均数算术平均数：指将数据总和除以数据个数得到的值。在EXCEL中，用语法为AVERAGE(number1,number2,...)指计算所有含数值数据的单元格的平均值。算术平均数加权平均数加权平均数：是指加权平均数是不同比重数据的平均数。主要用于计算分组数据的平均数。在EXCEL中，用语法为SUMPRODUCT(B4:B14,C4:C14)/SUM(B4:B14)几何平均数用于计算特殊数据的一种平均数，主要用于计算比率或速度的平均值。分简单几何平均数和加权几何平均数。这里主要介绍简单几何平均数在实际应用中，通常采用几何平均数1、计算变复利的平均增长率、平均发展速度、2、计算平均发展速度；等。语法：1、GEOMEAN(number1,number2,...)12nnGmxxx2．中位数中位数是指全体数值按大小排列后位于中间的数值。语法：MEDIAN(number1,number2,...)如果参数集合中包含有偶数个数字，函数MEDIAN()将返回位于中间的两个数的平均值。不受极端值影响。适用与分配不对称的数据。政府机关所公布的国民收入，常用中位数来作代表值。为什么呢？不妨先讲一个笑话，如果比尔·盖茨和十几个穷光蛋在一个房间里，这个房间里十几个人的平均收入就都超过亿元。之所以是笑话，是因为比尔·盖茨和穷光蛋的收入差距过大，导致平均数值缺乏实际参考意义。但如果用中位数来衡量，就知道这房间里起码有一半人是穷光蛋。由此可见，中位数有助于了解普通民众的收入水平。而中位数与平均数的差异，则有助于了解全体民众的收入集中度。什么是中位数呢？一组数据中处于最中间的一个数据就是中位数。我们可以再看一个例子。比如，某地有五个人，月工资分别是1000，1000，1300，1700，20000元。那么，月收入1300的那个人，就代表着收入中位数；而该五个人的平均收入为5000。平均收入是收入中位数的接近4倍。如果这五个人的月工资分别是3000，4000，5000，6000和7000元呢？月收入5000的那个人代表着该地区的收入中位数；该地区平均收入也是5000。平均收入=收入中位数。如果不提收入中位数，而只讲平均收入，在操作层面会遇到一些可笑的做法。比如，一个一千人的社区，如果要把人均收入从3000元提高10%，只需要向社区引进一个年收入30万元的小老板落户即可。虽然提高人均收入的“政绩工程”效果显著，但社区群众的收入水平并未发生明显变化。图3-11家用电器销售量中位数3．众数众数是一组数列中出现次数最多的数值，众数函数MODE()返回某一数组或数据区域中出现频率最多的数值。与MEDIAN相同，MODE也是一个位置测量函数。语法：MODE(number1,number2,...)如果数据集合中不含有重复的数据，则MODE()函数返回错误值N/A。不容易受极端值影响。图3-12家用电器销售量众数返回本节4．最大值最大值函数可以返回数据集中的最大数值。语法：MAX(number1,number2,...)MAXA(number1,number2,...)如果参数不包含数字，函数MAX返回0。MAX是求所有数值数据的最大值，而MAXA是求所有非空白单元格的最大值。注意：当数值全为负值时，若其中含文本单元格，用MAXA（）求得所有非空白单元格的最大值将为0。4．最小值最大值函数可以返回数据集中的最小数值。语法：MIN(number1,number2,...)MINA(number1,number2,...)如果参数不包含数字，函数MIN返回0。MIN是求所有数值数据的最大值，而MINA是求所有非空白单元格的最小值。注意：当数值全为正值及含字符串的单元格时，时，若其中含文本单元格，用MINA（）求得所有非空白单元格的最小值将为0。众数、中位数、均值的异同众数是一组数据中出现次数最多的变量值，它用于对分类数据的概括性度量，其特点是不受极端值的影响，但它没有利用全部数据信息，而且还具有不惟一性。一组数据可能有众数，也可能没有众数；可能有一个众数，也可能有多个众数。中位数是一组数据按大小顺序排序后处于中间位置上的变量，它主要用于对顺序数据的概括性度量。均值是一组数据的算术平均，它利用了全部数据信息，是概括一组数据最常用的一个值。例某商场家用电器销售情况如图3-9所示。（1）计算各种电器的全年平均销售量，如图3-10所示。（2）计算各种电器销售量的中位数，如图3-11所示。（3）计算各种电器销售量的众数，如图3-12所示。图3-9某商场家用电器销售情况图3-10家用电器销售量平均数5、离中趋势的测定内容在研究现象总体标志的一般水平时，不仅要研究总体标志的集中趋势，还要研究总体标志的离中趋势，如研究价格背离价值的平均程度。研究离中趋势可以通过计算标志变异指标来进行。标志变异指标是同统计平均数相联系的一种综合指标，用于度量随机变量在取值区间内的分布情况，主要有极差、平均差、标准差、方差、四分位数、百分位数等。在一般计算中，这些指标计算是比较复杂的，但在Excel中都有相应的函数，因而使计算变得很简单。返回本节1.极差一组数据中的最大值减去最小值在EXCEL中，用MAX()-MIN()或LARGE()-SMALL()来计算极差。极差越小表示数据分布越集中。易受极端值影响。反应不够灵敏，因此不是很好的离散程度衡量法在工厂看灯泡的使用寿命适合看极差以及股票组对股价的涨跌等也适合看极差。2.平均差（平均绝对离差）每一个观测值与其平均值间差异的绝对值的算术平均。1niidxxMn1kiiidMxfMn分组数据未分组数据语法：AVEDEV(number1,number2,...)3．样本标准差样本标准差函数用来估算样本的标准偏差，反映相对于平均值（mean）的离散程度，Excel计算样本标准差采用不偏估计式（亦即自由度＝n-1），其计算公式为21()1niixxsn语法：STDEV(number1,number2,...)总体标准差总体标准差函数返回以参数形式给出的整个样本总体的标准偏差，反映相对于平均值（mean）的离散程度。计算总体标准差使用整个总体的变量，通常采用偏性估计式（亦即自由度为n），其计算公式为21()niixn语法：STDEVP(number1,number2,...)样本方差样本方差：样本中各数据与样本平均数的差的平方和的平均数叫做样本方差；样本方差的算术平方根叫做样本标准差。样本方差和样本标准差都是衡量一个样本波动大小的量，样本方差或样本标准差越大，样本数据的波动就越大。例计算各家用电器电销售量的总体标准差，如图3-13所示。图3-13计算总体标准差例两组工人生产某种零件的产量如图3-14所示。图3-14两组工人产量图3-15计算产量平均数图3-16计算两组工人产量标准差图3-17计算两组工人产量标准差系数返回本节4.四分位数与四分位距四分位数是将中值的前后两部分数值再等分为二，以数值小的一端算起，前半部的分区点称为第1四分位数，后半部的分区点称为第3四分位数，而中值即为第2四分位数。四分位数通常用于在销售额和测量值数据集中对总体进行分组。语法：QUARTILE(array,quart)array：需要求四分位数值的数组或数字型单元格区域。quart：决定返回哪一个四分位值（1、2、3、4）。Quart值与QUARTILE返回值的对应关系见表3-1所示。quart值函数QUARTILE返回值0最小数值1第一个四分位数（第25个百分排位）2中分位数（第50个百分排位）3第三个四分位数（第75个百分排位）4最大数值表3-1Quart值与QUARTILE返回值的对应关系例使用前面例子资料，计算四分位数和四分位距，如图3-18所示。图3-18计算四分位数和四分位距返回本节5．偏度系数偏度系数返回分布的偏斜度。偏斜度反映以平均值为中心的分布的不对称程度。其计算公式为语法：SKEW(number1,number2,...)正态分布的偏度为0，两侧尾部长度对称。bs0称分布具有负偏离，也称左偏态，此时数据位于均值左边的比位于右边的多，直观表现为左边的尾部相对于与右边的尾部要长；bs0称分布具有正偏离，也称右偏态，情况相反；而bs接近0则可认为分布是对称的。左偏时相反，即众数中位数平均数。右偏时一般算术平均数中位数众数，正态分布三者相等。6．峰度系数峰态系数。表征概率密度分布曲线在平均值处峰值高低的特征数。直观看来，峰度反映嘞尾部的厚度。语法：KURT(number1,number2,...)32)1(3321124nnnsxxnnnnni峰度以bk表示，Yi是样本测定值，Ybar是样本n次测定值的平均值。正态分布的峰度为3。简单来讲，峰度是描述分布形态的陡缓程度。峰度为3表示与正态分布相同，峰度大于3表示比正态分布陡峭，小于3表示比正态分布平坦。四、样本大小的选择样本不要过大，过大会造成浪费成本，但也不能过小，过小会有太大的抽要误差。在非概率抽样的情况下，除了依靠抽样人员的主观判断或假设外，还有以下客观科学方法可以参考未知总体方差的样本大小选择事实上，很多情况是无法得知总体方差的（σ2）.如果总体方差未知，则可以用过去调查的样本方差（S2）替代。若过去也无类似调查，可先进性小规模测验，计算出样本方差，然后再计算需要样本数：2/2.zsneα即是显著水平，e即是可容忍误差S即是样本标准差举例：现在需要进行一个大学的学生信用卡平均签帐金额抽样，现在要确定抽样的最少人数，但总体方差未知，已先举行一次小规模测验，取得了40位大学生的信用卡每月平均签帐金额。要求在显著水平α=0.05（Zα/2=1.96），希望总体误差不超过50元。求调查的最少样本是多大？四、设计问卷和取得数据1、列举所要收集的信息，初学者可找几份相关研究的论文参考，并与同组的同学和指导老师讨论，得出要收集的信息。2、决定访问的形式，是直接访问还是结构访问，或者是同时结合进行。3、决定访问的方式，是人员访问、电话访问、还是邮件访问、问卷访问。4、决定问题的内容，原则上你最终你想得到什么你就问什么问题。但要考虑以下问题：此问题有无必要？受访者能否回答、愿意回答、容易回答？5、决定问题的顺序，问题有先后逻辑性，比如调查手机，你如果遇到没有手机的怎么办？Q2购买理由Q3使用时间Q4付费形式Q5产品属性Q1-1未买原因Q1有无手机Q6期望价位6、问卷外观、测验和修订，要问卷的纸质、颜色、单双面印刷等因素；还要考虑找20人先做问卷的小测验，一边测验一边修改问卷。几种典型题目：单选、多选、填写、等级等等。4.2举例：新产品市场调查分析4.2.1设计调查流程设计调查问卷完成调查结果数据库样本组成分析产品市场定位分析设计标题前言设计主题结束性别组成分析数据编码设置编码替换年龄结构分析购买因素分析附属功能分析潜在人群分析产品定价分析4.2.2设计问卷4.3.1使用筛选和图表功能分析性别组成附属功能分析