《Excel操作》-第4章 市场调查数据分析

本章主要介绍利用Excel进行数据分析的方法，包括集中趋势分析、离散趋势分析、时间序列分析。所使用的函数有：AVERAGE、MEDIAN、MODE、STDEVP。本章简介本章重点重点、难点集中趋势分析，离散趋势分析，时间序列分析。本章难点描述统计分析，时间序列趋势分析。14.2市场调查数据动态分析本章目录4.1市场调查数据静态分析24.1.2市场调查数据离散趋势分析4.1.1市场调查数据集中趋势分析4.1市场调查数据静态分析4.1.3市场调查数据的峰度和偏度4.1.4用EXCEL实现数据静态描述分析34.1.1市场调查数据集中趋势集中趋势是指一组数据向某一中心值靠拢的程度，它反映了一组数据中心点的位置所在。集中趋势的分析指标有两类：数值平均数和位置平均数。数值平均数是由一组数据所有数值计算的指标。常用的数值平均数有算术平均数、调和平均数、几何平均数等形式。位置平均数是由处于特殊位置上的个别数据或部分数据的值来确定的指标。常用的位置平均数有众数、中位数。454.1.2市场调查数据离散趋势集中趋势反映了数据聚集的中心，但变异是数据的天然特性，各个数据总是以不同的程度偏离分布中心。因此，对于一组数据不仅要研究分布中心，以说明数据整体水平状况，还要测定数据的离散程度，来说明数据之间差异程度大小。测定数据离散程度的指标有数值变异指标和位置变异指标两种。数值变异指标是由一组数据所有数值计算的指标。常用的数值变异指标有平均差、标准差和方差。位置变异指标是由处于特殊位置上的个别数据或部分数据的值来确定的指标。常用的位置变异指标有极差和四分位差。674.1.3市场调查数据的峰度和偏度峰度是指次数分布曲线顶峰的尖平程度。根据数据的集中与分散程度，峰度一般可表现为三种形态：尖顶峰度、平顶峰度和标准峰度。测定峰度的指标是峰度系数，记作K。计算公式为：1.峰度89当分布为正态分布曲线时，K=0，以此为标准，就可比较分析各种次数分布曲线的峰度。当K>0时，表示分布曲线呈尖顶峰度，为尖顶曲线，说明变量值的次数较为密集地分布在中心的周围，K值越大于0，分布曲线的顶端越尖峭。当K<0时，表示分布曲线呈平顶峰度，为平顶曲线，说明变量值的次数分布比较均匀地分散在中心的两侧，K值越小于0，则分布曲线的顶峰就越平缓。一般当K值接近于−1.2时，分布曲线呈水平矩形分布形态，说明各组变量值的次数相同。当K值小于−1.2时，次数分布曲线趋向U形分布。4.1.3市场调查数据的峰度和偏度偏度是指分布的偏斜方向及程度。测定偏度的指标是偏度系数，记作SK。计算公式为：当分布为正态分布曲线时，SK=0，即对称分布；当SK>0时，是右偏分布；当SK<0时，是左偏分布。SK绝对值越大，偏斜程度越严重。4.1.3市场调查数据的峰度和偏度2.偏度104.1.4用Excel实现数据静态描述分析有两种计算分析方法：一是使用Excel的“描述统计”分析工具，二是使用Excel函数。1.不分组数据11例4-1对下列不分组数据计算静态描述分析指标。具体的操作步骤如下：方法一：使用Excel的“描述统计”分析工具计算第一步：在电子表格中输入数据，在“工具”菜单下选择“数据分析”，打开“数据分析”对话框。如果在“工具”菜单下没有找到“数据分析”，在“工具”下“加载宏”选项中选择“分析工具库”和“分析工具函数”，单击“确定”12例4-1对下列不分组数据计算静态描述分析指标。具体的操作步骤如下：第二步：在“描述统计”对话框中输入相关参数。第三步：单击“确定”，得计算结果。原始数据如果按行排列，分组方式选“逐行”；按列排列，则选“逐列”。输入区域中包含变量名称，勾选“标志位于第一行”复选框，反之不选。4.1.4用Excel实现数据静态描述分析13例4-1对下列不分组数据计算静态描述分析指标。具体的操作步骤如下：方法二：使用Excel函数计算根据图4-1所示的各指标计算公式输入函数进行计算，其中“成绩”是指A2:A55区域。4.1.4用Excel实现数据静态描述分析144.1.4用Excel实现数据静态描述分析对分组数据只能用Excel函数计算有关指标。2.分组数据15例4-2对下图分组数据计算节能灯泡使用寿命的平均值、标准差及偏度和峰度指标。具体的操作步骤如下：第一步：在电子表格中输入数据，如图4-4所示，其中x为各组数据组中值，f为每组频数。4.1.4用Excel实现数据静态描述分析16例4-2对下图分组数据计算节能灯泡使用寿命的平均值、标准差及偏度和峰度指标。具体的操作步骤如下：第二步：在单元格区域F2:F5输入下列公式计算有关指标：F2:“=SUMPRODUCT(B2:B8,C2:C8)/SUM(C2:C8)”F3:“=SQRT(SUMPRODUCT((B2:B8-F2)^2,C2:C8)/SUM(C2:C8))”F4:“=SUMPRODUCT((B2:B8-F2)^3,C2:C8)/SUM(C2:C8)/F3^3”F5:“=SUMPRODUCT((B2:B8-F2)^4,C2:C8)/SUM(C2:C8)/F3^4”4.1.4用Excel实现数据静态描述分析174.2.2市场调查数据速度指标分析4.2.1市场调查数据水平指标分析4.2市场调查数据动态分析4.2.3市场调查数据长期趋势分析18时期序列中每一指标值反映现象在一定时期内发展的结果，即“过程总量”。时期序列平均发展水平计算采用简单算术平均法。4.2.1市场调查数据水平指标分析发展水平是时间序列中的各项数值。各时间上发展水平有高有低，平均发展水平是不同时间上发展水平的平均数，反映了现象在一段时间内的一般水平。1.平均发展水平（1）绝对数时间序列①时期序列平均发展水平1920时点序列中每一指标值反映现象在一定时点上的瞬间水平。对时点序列，如果已知每天的数据，通常视为连续时点序列，采用与时期序列相同的计算方法——简单算术平均法。②连续时点序列平均发展水平③间断时点序列平均发展水平对时点序列，如果不是每天的数据都已知，而是间隔一段时间观测一次，通常视为间断时点序列。间断时点序列先计算相邻时点水平的平均数，再以相邻时点的间隔长度作为权数，用加权算术平均法计算。若权数都相等，则公式简化为：21相对数或平均数有一个共同特点，就是它们归根结底都是由两个有关的绝对数对比得到。因此，相对数时间序列和平均数时间序列的平均发展水平计算分三步完成：第一步求出分子序列的平均发展水平第二步求出分母序列的平均发展水平第三步求出相对数或平均数时间序列平均发展水平（2）相对数或平均数时间序列例4-3图4-5是历年我国职工人数和工资总额资料，计算年平均工资总额、总平均职工人数和年平均工资。具体的操作步骤如下：第一步：建立平均发展水平计算分析表。第二步：在单元格B7中输入公式“=SUM(C3:G3)/COUNT(C3:G3)”，计算年平均工资总额。职工工资总额时间序列是时期序列；职工人数时间序列是间断时点序列，平均工资=工资总额/人数，是平均数，年平均工资=年平均工资总额/总平均职工人数。22例4-3图4-5是历年我国职工人数和工资总额资料，计算年平均工资总额、总平均职工人数和年平均工资。具体的操作步骤如下：第三步：在单元格C5中输入公式“=(B4+C4)/2”，并复制到单元格区域D5:G5，计算年平均职工人数，在单元格区域C6:G6中输入相邻时点的间隔年份长度，在单元格B8输入公式“=SUMPRODUCT(C5:G5,C6:G6)/SUM(C6:G6)”，计算总平均职工人数。23例4-3图4-5是历年我国职工人数和工资总额资料，计算年平均工资总额、总平均职工人数和年平均工资。具体的操作步骤如下：第四步：在单元格B9中输入公式“=B7*10000/B8”，计算年平均工资。2425增长量是报告期水平与基期水平之差，用以说明一定时期内增长变化的绝对数量。增长量小于0，表示现象减少或下降；增长量大于0，表示现象增加或上升。由于选择的基期不同，增长量可以分为逐期增长量和累计增长量。逐期增长量=报告期水平−前一期水平累计增长量=报告期水平−固定基期水平2.增长量26平均增长量是各逐期增长量的平均数，用以说明现象在一定时期内平均逐期增长变化数量。平均增长量=各逐期增长量之和/逐期增长量个数

=最末期累计增长量/（时间序列项数−1）3.平均增长量例4-4计算例4-3职工工资总额的增长量和平均增长量。具体的操作步骤如下：第一步：在单元格中C4输入公式“=C3-B3”，并复制到单元格区域D4:G4，计算逐期增长量。第二步：在单元格中C5输入公式“=C3-$B$3”，并复制到单元格区域D5:G5，计算累计增长量。第三步：在单元格中B6输入公式“=G5/5”，计算平均增长量，如图所示。27284.2.2市场调查数据速度指标分析发展速度是报告期水平与基期水平之比，用于描述现象在观察期内相对的发展变化程度。发展速度小于1，表示现象减少或下降；发展速度大于1，表示现象增加或上升。由于采用的基期不同，发展速度可以分为环比发展速度和定基发展速度。环比发展速度=报告期水平/前一期水平定基发展速度=报告期水平/固定基期水平1.发展速度29增长速度=增长量/基期水平=（报告期水平−基期水平）/基期水平=发展速度−1由于发展速度不同，存在有两种增长速度：环比增长速度和定基增长速度。环比增长速度=环比发展速度−1定基增长速度=定基发展速度−12.增长速度30平均发展速度是各个时期环比发展速度的平均数，用于描述现象在整个观察期内平均发展变化的程度。3.平均发展速度31平均增长速度=平均发展速度−14.平均增长速度增长1%的绝对值是一个将水平指标和速度指标结合运用的指标，增长1%绝对值=前期水平/1005.增长1%的绝对值例4-5图4-7是全社会固定资产投资数据，计算有关速度指标。具体的操作步骤如下：第一步：在单元格C5中输入公式“=B5*100/B4”，并复制到单元格区域C6:C15，计算环比发展速度。在单元格中D5输入公式“=B5*100/$B$4”，并复制到单元格区域D6:D15，计算定基发展速度。32例4-5图4-7是全社会固定资产投资数据，计算有关速度指标。具体的操作步骤如下：第二步：在单元格E5中输入公式“=C5-100”，并复制到单元格区域E6:E15，计算环比增长速度。在单元格F5中输入公式“=D5-100”，并复制到F6:F15，计算定基增长速度。第三步：在单元格G5中输入公式“=B4/100”，并复制到单元格区域G6:G15，计算增长1%绝对值。33例4-5图4-7是全社会固定资产投资数据，计算有关速度指标。具体的操作步骤如下：第四步：在单元格B16中输入公式“=(B15/B4)^(1/11)”，计算平均发展速度。在单元格D16中输入公式“=B16-1”，计算平均增长速度。34354.2.3市场调查数据长期趋势分析长期趋势是指现象在较长时期内持续发展变化的一种趋向或状态。通过对时间序列长期趋势变动的分析，可以掌握现象活动的规律性，并对其未来的发展趋势做出判断或预测。1.移动平均法移动平均法是采用逐项递进的方法，将原时间序列中的若干项数据进行平均，通过平均来消除或减弱时间序列中的不规则变动和其他变动，从而呈现出现象发展变化的长期趋势。若平均的数据项数是K，称为K期（项）移动平均。移动平均法分为简单移动平均和加权移动平均。例4-6图4-8是某企业产品销售量资料，用移动平均法分析现象的长期趋势。具体的操作步骤如下：第一步：在“工具”菜单选择“数据分析”，在弹出的“数据分析”对话框中选择“移动平均”，单击“确定”，打开“移动平均”对话框，如图36例4-6图4-8是某企业产品销售量资料，用移动平均法分析现象的长期趋势。具体的操作步骤如下：第二步：“输入区域”是进行移动平均的数据所在区域，“间隔”是移动平均的项数，“输出区域”是指定移动平均数列的输出位置。输入如图4-9所示相关参数，单击“确定”。3738①移动平均对原数列具有修匀作用，使数列的起伏波动减少。一般移动的项数越多，修匀作用越强。②移动平均值代表所平均的数据的中间位置的趋势，因此，当移动平均的项数是奇数时，只要一次移动平均即可；当移动平均的项数是偶数时，需对第