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

1、一、研究步骤和甘特图绘制 研究的步骤： 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 ,

2、 .) 指计算所有含数值数据的单元格的平均值。 算术平均数 加权平均数 加权平均数：是指加权平均数是不同比重数据的平均数。主要用于计算分组数据的平均数。 在EXCEL中，用语法为 SUMPRODUCT(B4:B14,C4:C14)/SUM(B4:B14) 几何平均数 用于计算特殊数据的一种平均数，主要用于计 算比率或速度的平均值。分简单几何平均数和 加权几何平均数。这里主要介绍简单几何平均 数 在实际应用中，通常采用几何平均数 1、计算变复利的平均增长率、平均发展速度、 2、计算平均发展速度；等。 语法：1、 GEOMEAN(number1,number2 , .) 12 n n Gmx xx

3、 2中位数 l中位数是指全体数值按大小排列后位于中间的数值。 l语法：MEDIAN(number1,number2, .) l如果参数集合中包含有偶数个数字，函数MEDIAN()将返回位于中间的两个数的 平均值。 l不受极端值影响。 l适用与分配不对称的数据。政府机关所公布的国民收入，常用中位数来作代表值。 为什么呢？不妨先讲一个笑话，如果比尔盖茨和十几个穷光蛋在 一个房间里，这个房间里十几个人的平均收入就都超过亿元。之所以是 笑话，是因为比尔盖茨和穷光蛋的收入差距过大，导致平均数值缺乏 实际参考意义。但如果用中位数来衡量，就知道这房间里起码有一半人 是穷光蛋。由此可见，中位数有助于了解普通民

4、众的收入水平。而中位 数与平均数的差异，则有助于了解全体民众的收入集中度。 什么是中位数呢？一组数据中处于最中间的一个数据就是中位数。 我们可以再看一个例子。比如，某地有五个人，月工资分别是1000， 1000，1300，1700，20000元。那么，月收入1300的那个人，就代 表着收入中位数；而该五个人的平均收入为5000。平均收入是收入中 位数的接近4倍。如果这五个人的月工资分别是3000，4000，5000， 6000和7000元呢？月收入5000的那个人代表着该地区的收入中位数； 该地区平均收入也是5000。平均收入=收入中位数。 如果不提收入中位数，而只讲平均收入，在操作层面会遇到

5、一些可 笑的做法。比如，一个一千人的社区，如果要把人均收入从3000元提 高10%，只需要向社区引进一个年收入30万元的小老板落户即可。虽 然提高人均收入的“政绩工程”效果显著，但社区群众的收入水平并未 发生明显变化。 图3-11 家用电器销售量中位数 3众数 l众数是一组数列中出现次数最多的数值，众数函数MODE()返回某一数组或数据区域中出现频率最多的数值。 与MEDIAN相同，MODE也是一个位置测量函数。 l语法：MODE(number1,number2, .) l如果数据集合中不含有重复的数据，则MODE()函数返回错误值N/A。 l 不容易受极端值影响。 图3-12 家用电器销售量

6、众数 返回本节 4最大值 l最大值函数可以返回数据集中的最大数值。 l语法：MAX(number1,number2,.) l MAXA(number1,number2, .) l如果参数不包含数字，函数MAX返回0。 l MAX是求所有数值数据的最大值，而MAXA是求所有非空白 单元格的最大值。 l注意：当数值全为负值时，若其中含文本单元格，用MAXA（） 求得所有非空白单元格的最大值将为0。 4最小值 l最大值函数可以返回数据集中的最小数值。 l语法： MIN(number1,number2, .) MINA(number1,number2,.) l如果参数不包含数字，函数MIN返回0。 l

7、MIN是求所有数值数据的最大值，而MINA是求所有非空白单元格的最小 值。 l注意：当数值全为正值及含字符串的单元格时，时，若其中含文本单元格， 用MINA （）求得所有非空白单元格的最小值将为0。 众数、中位数、均值的异同 众数是一组数据中出现次数最多的变量值，它 用于对分类数据的概括性度量，其特点是不受极 端值的影响，但它没有利用全部数据信息，而且 还具有不惟一性。一组数据可能有众数，也可能 没有众数；可能有一个众数，也可能有多个众数。 中位数是一组数据按大小顺序排序后处于中间 位置上的变量，它主要用于对顺序数据的概括性 度量。 均值是一组数据的算术平均，它利用了全部数 据信息，是概括一组

8、数据最常用的一个值。 例 某商场家用电器销售情况如图3-9所示。 （1）计算各种电器的全年平均销售量，如图3-10所示。 （2）计算各种电器销售量的中位数，如图3-11所示。 （3）计算各种电器销售量的众数，如图3-12所示。 图3-9 某商场家用电器销售情况 图3-10 家用电器销售量平均数 5、 离中趋势的测定内容 在研究现象总体标志的一般水平时，不仅要研究总体标志的集中 趋势，还要研究总体标志的离中趋势，如研究价格背离价值的平 均程度。研究离中趋势可以通过计算标志变异指标来进行。标志 变异指标是同统计平均数相联系的一种综合指标，用于度量随机 变量在取值区间内的分布情况，主要有极差、平均差

9、、标准差、 方差、四分位数、百分位数等。在一般计算中，这些指标计算是 比较复杂的，但在Excel中都有相应的函数，因而使计算变得很 简单。 返回本节 1.极差 一组数据中的最大值减去最小值 在EXCEL中，用MAX()-MIN()或LARGE()-SMALL()来计算极差。 极差越小表示数据分布越集中。 易受极端值影响。 反应不够灵敏，因此不是很好的离散程度衡量法 在工厂看灯泡的使用寿命适合看极差以及股票组对股价的涨跌等也适合看极差。 2.平均差（平均绝对离差） 每一个观测值与其平均值间差异的绝对值的算术 平均。 1 n i i d xx M n 1 k ii i d Mx f M n 分组数

10、据 未分组数据 语法：AVEDEV(number1,number2,.) 3样本标准差 样本标准差函数用来估算样本的标准偏差，反映相对于平均值（mean）的离散程度，Excel计算样本标准差 采用不偏估计式（亦即自由度n-1），其计算公式为 2 1 () 1 n i i xx s n 语法：STDEV(number1,number2,.) 总体标准差 总体标准差函数返回以参数形式给出的整个样本总体的标准偏差，反映相对于平均值（mean）的离散程度。 计算总体标准差使用整个总体的变量，通常采用偏性估计式（亦即自由度为n），其计算公式为 2 1 () n i i x n 语法：STDEVP(num

11、ber1,number2,.) 样本方差 样本方差：样本中各数据 与样本平均数的差的平方 和的平均数叫做样本方差； 样本方差的算术平方根叫 做样本标准差。 样本方差和样本标准差都 是衡量一个样本波动大小 的量，样本方差或样本标 准差越大，样本数据的波 动就越大。 例 计算各家用电器电销售量的总体标准差，如图3-13所示。 图3-13 计算总体标准差 例 两组工人生产某种零件的产量如图3-14所示。 图3-14 两组工人产量 图3-15 计算产量平均数 图3-16 计算两组工人产量标准差 图3-17 计算两组工人产量标准差系数 返回本节 4. 四分位数与四分位距 四分位数是将中值的前后两部分数值

12、再等分为二， 以数值小的一端算起，前半部的分区点称为第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-1

13、Quart值与QUARTILE返回值的对应关系 例 使用前面例子资料，计算四分位数和四分位距，如图3-18所示。 图3-18 计算四分位数和四分位距 返回本节 5偏度系数偏度系数 偏度系数返回分布的 偏斜度。偏斜度反映以 平均值为中心的分布的 不对称程度。其计算公 式为 语法：SKEW(number1,number2,.) 正态分布的偏度为0，两侧尾部长度对称。 bs0称分布具有正偏离，也称右偏态，情况相反； 而bs接近0则可认为分布是对称的。 左偏时相反，即众数中位数平均数。右偏时一般算术平均数中位数众数， 正态分布三者相等。 6峰度系数峰度系数 峰态系数。表征概率 密度分布曲线在平均值 处

14、峰值高低的特征数。 直观看来，峰度反映嘞 尾部的厚度。 语法：KURT(number1,number2, .) 32 ) 1(3 321 1 2 4 nn n s xx nnn nn i 峰度以bk表示，Yi是样本测定值，Ybar是样本n次测定值的平均值。正态分布的峰度为3。 简单来讲，峰度是描述分布形态的陡缓程度。峰度为3表示与正态分布相同，峰度大于3表示比正态分布 陡峭，小于3表示比正态分布平坦。 四、样本大小的选择 样本不要过大，过大会造成浪费成本，但也不能过小，过小会有太大的抽要误 差。 在非概率抽样的情况下，除了依靠抽样人员的主观判断或假设外，还有以下客 观科学方法可以参考 未知总体

15、方差的样本大小选择 事实上，很多情况是无法得知总体方差的（2）.如果总体方差未知，则可以用 过去调查的样本方差（S2）替代。若过去也无类似调查，可先进性小规模测验， 计算出样本方差，然后再计算需要样本数： 2 / 2 .zs n e 即是显著水平， e即是可容忍误差 S即是样本标准差 举例：现在需要进行一个大学的学生信用卡平均签帐金额抽样，现在要确定抽样的最少人数，但总体方差 未知，已先举行一次小规模测验，取得了40位大学生的信用卡每月平均签帐金额。要求在显著水平=0.05 （Z/2=1.96），希望总体误差不超过50元。求调查的最少样本是多大？ 四、设计问卷和取得数据 1、列举所要收集的信息

16、，初学者可找几份相关研究的论文参考，并与同组的同学和指导老师讨论，得出要 收集的信息。 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 使用筛选和图表功能分析