数据分析报告 - 图文

数据分析实验报告

20 15 - 20 16 学年 第 一 学期 班 级： 学 号： 姓 名：

授课教师： 况湘玲 实验教师：况湘玲

实验一 网上书店数据库的创建及其查询

实验类型：验证性 实验学时：2 实验目的：理解数据库的概念；

理解关系（二维表）的概念以及关系数据库中数据的组织方式； 了解数据库创建方法。

实验步骤：

步骤1：选择“bookstore”数据源，进入“查询设计”窗口。 步骤2：选择查询中需要使用的表。

在“添加表”对话框的“表”列表中分别选择“书”和“出版社”表，并单击“添加” 按钮将它们添加至表窗格。

查询设计4—低价图书信息查询 步骤1：选择数据源并添加表。

选择“bookstore”数据源，进入“查询设计”窗口。在“添加表”对话框中选择“书” 表，将其添加到“表”窗格中。

步骤2：选择字段。

在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“出版年份”和“单价” 字段。 步骤3：设置查询条件，显示查询结果。

在“条件”窗格的“条件字段”行的第一列中选择“单价”，并在下一行中输入“<10 ”

查询设计5—新书信息查询

步骤1：选择“bookstore”数据源并添加“书”和“作者”表。 步骤2：选择字段。

在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“单价”，以及“作者” 表的“姓名”字段。

步骤3：设置查询条件，显示查询结果。

在“条件”窗格的“条件字段”行的第一列中选择“出版年份”，并在下一行中输入“2007”

实验 1-3 “响当当”网上书店会员分布和图书销售信息查询 实验目的

?掌握复杂的数据查询方法：多表查询、计算字段和汇总查询 步骤1：选择“bookstore”数据源并添加“会员”表。 步骤2：选择分类字段、汇总字段和汇总方式。

本查询的分类字段是会员的城市，汇总字段是会员号，汇总方式是计数。在“查询设计”

窗口的“表”窗格中，双击“会员”表的“城市”和“会员号”字段。然后双击“会员号”字段的列标，在“编辑列”对话框中输入列标“会员人数”，并选择汇总方式：“计数”，

步骤3：设置查询条件。

若仅仅想了解上海和北京的会员人数，可以在条件窗格中设置相应的条件，

二、查询设计2—图书总订购量和总销售金额查询

步骤1：选择“bookstore”数据源，并添加“订单”、“订单明细”和“书”表。 步骤2：选择分类字段和汇总字段。

在“查询设计”窗口的“表”窗格中，双击“订单”表的“订购日期”、“订单明细”表的“订购数量”字段。另外还要构造一个计算字段“销售金额”，方法是直接在某空白列的列标中输入公式；在上面的字段中，“订购数量”和“销售金额”是汇总字段，而分类字段是“订购年份”，该字段也是一个计算字段，可以利用 year()函数将订购日期转换成订购年份，方法是双击“订购日期”列的列标，在编辑列对话框的字段项中输入公式“year(订购日期)”，在其中的列标项中输入“订购年份”；

然后分别双击“订购数量”和“订购数量*单价”字段的列标，在编辑列对话框的列标项中分别输入“总 订购数量”和“总销售金额”。

步骤3：设置查询条件。

在“条件”窗格的“条件字段”行的第一列中选择“订购日期”，并在下一行中输入

“>=2005-7-1 and <=2006-6-30”后回车，即可在“查询结果”窗格中显示2005上半年和2006下半年的 图书总订购量和总销售金额。再查询设计窗口中增加一个“订购月份”分类字段。

三、查询设计3—会员订购图书详细信息查询

步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”和“书”表。在表之间建立合适的

联系。

步骤2：选择字段。

在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“会员号”、“姓名”字段，“订单”表的“订单号”字段、“书”表的“书名”字段和“订单明细”表的“订购数量”字段。 步骤3：规定查询结果的排序方式。

选择“记录”菜单的“排序”命令，在随后出现的“排序”对话框中设置排序方式，

四、查询设计4—各城市会员图书订购数量和销售金额统计 书店工作人员想了解位于各个城市的会员在网上订购的图书的总订购数量和总销售金额。

步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”和“书”表。在表之间建立合适的 联系。

步骤2：选择分类字段和汇总字段。

在“查询设计”窗口的“表”窗格中，双击“会员”表的“城市”、“订单明细”表的“订购数量” 字段。另外还要构造一个计算字段“销售金额”，方法是直接在某空白列的列标中输入公式“订购数量*单价”； 步骤3：规定汇总方式。

分别双击“订购数量”和“订购数量*单价”字段的列标，在编辑列对话框的列标项中分别输入“总订购数量”和“总销售金额”字样，并在总计项中选择“求和”。

五、查询设计5—被订购图书的作者和出版社信息查询 书店工作人员想了解2007年会员“刘丹”所订购图书的作者以及出版社的信息。

步骤1：选择“bookstore”数据源并添加“会员”、“订单”、“订单明细”、“书”、“作者”和“出版社”表。 在表之间建立合适的联系。 步骤2：选择查询字段。

在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、“作者”表的“姓名”和“出版社” 表的“出版社名称”字段。

步骤3：添加查询条件。 在条件窗格中添加关于订购日期和会员姓名的条件。 步骤4：查看查询结果。 会员“刘丹”在2007年共订购了7本图书。

实验小结：

其实在这第一个实验中感觉还是挺简单的，只要找到每一个表之间的联系就会很容易做。特别注意 的在设计查询时记住把“向导”关闭。然后就是在实验三中需要计算销售额，这个时候就要在表中 添加“单价”，只有这样才可以计算销售额。 思考题：

1,在数据查询过程中，如果所选择的某个表与其他表之间没有联系的话，会产生什么问题？ 答：如果所选择的某个表与其他表之间没有联系，那么所得出的数据就会是错误的，并且很难继续 下面的步骤。

2,若“响当当”网上书店的某个会员想了解自己最近 2 年的图书订购情况，请为他设计一个查询。 步骤1：选择数据源并添加表。

选择“bookstore”数据源，进入“查询设计”窗口。在“添加表”对话框中选择“书” 表，将其添加到“表”窗格中。 步骤2：选择字段。

在“查询设计”窗口的“表”窗格中，双击“书”表的“书名”、，订单，订单明细。 步骤3：设置查询条件，显示查询结果。

在“条件”窗格的“条件字段”行的第一列中选择“订购日期”，并在下一行中选择“值域”，选择“2006-2007年”。

1在进行汇总查询的过程中，如果被选择的字段除了分类字段和汇总字段以外还包含了其他字段， 查询结果是否正确？为什么？请举例说明。

答：不正确，如果还有其他字段，quary将会把多余的字段自动作为分类字段。

2“响当当”网上书店的管理人员想了解最近2年中哪位作者的书最畅销，请你设计一个查询找到相关作者。

通过汇总发现近两年shammas,Namir c.和wellin, paul的书最畅销。

实验二企业销售数据的分类汇总分析

实验类型：验证性 实验学时：2

实验目的：理解数据分类汇总在企业中的作用与意义；

掌握数据透视表工具的基本分类汇总功能；

掌握建立分类汇总数据排行榜、生成时间序列、绘制pareto曲线图、计算各地

区客户分布、统计各地区客户的平均销售额和大宗销售时间序列的方法和步骤。 实验步骤：

步骤I：获取各客户每笔销售的销售额、销售产品的类别和时间。

在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，随后启动了Microsoft query，选择所建立的连接到Northwind.mdb数据库的ODBC数据源---“nw”，并选中“选择数据源”窗口下方“使用‘查询向导’创建/编辑查询”选项，单击“确定”按钮，选择“客户”表中的“公司名称”、“订单”表中的“订购日期”以及“类别”表中“类别名称”字段，随后query弹出窗口显示“‘查询向导’无法继续，因为该表格无法链接到您的查询中。您必须在Microsoft query中的表格之间拖动字段，人工链接。”这是因为“类别”表无法同“订单”表建立联系。单击“确定”按钮。

要查询销售额，需要在query中首先增加“订单明细”表，利用其中的“单价”、“数量”与“折扣”字段中的数据，计算销售额。在“数据”窗格中一个空白字段的名称处输人公式“订单明细.单价*数量*(1-折扣)”，按回车键后就可以计算出销售额。随后，将“产品”表也添加到查询中，虽然查询结果中并不包括任何“产品”表的字段，但是该表能够建立“类别”表与“订单明细”表之间的联系(“订单明细”表指明所订购产品的ID，“产品”表指明该产品属于哪一个类别)。此时，查询中的表都建立了正确的联系，并在查询结果中包括了汇总所需要的数据，从而也建立“类别”表与“订单”表之间的联系。将计算销售额的字段的列标题命名为“销售额”。选择query菜单中的“文件”→“将数据返回Microsoft Office Excel”命令，此时query已经关闭，操作对象回到了Excel，单击“下一步”按钮，指定位置在“现有工作表”，单元格A3，单击“完成”按钮。

步骤2：汇总客户销售额排行榜，并排序。

首先将“订购日期”拖至行标签，将“销售额”拖至数值，“类别名称”拖至列标签，为了能将销售额按照年度汇总，将光标停留在“行标签”下方的任何单元格，右击鼠标，选择“创建组(G)?”命令，选择组合的步长为“年”。

然后将行标签的字段名称“订购日期”改为“订购年”，将它拉至报表筛选域，将字段列表中的“公司名称”字段拖到行标签处，让透视表按照行总计，从大到小排列，就得到了图表。 2汇总前三大客户各月销售额，并绘制图形

步骤1：将实验要求1所汇总的数据透视表复制到新的工作表。 步骤2：利用数据透视表，汇总前三大客户的销售额时间序列。

按照实验要求1汇总的数据透视表，反映出“高上补习班”、“正人资源”、“大钰贸易”是公司的前三大客户。点开“行标签”字段，勾选中这3个公司名称，并拖到列标签。将列标签的字段“类别名称”拖出数据透视表。将报表筛选中的字段“订购年”拖到到行标签，将其重新组合。选择组合

的步长为“月”和“年”，把字段名称修改为“订购年”与“订购月”。光标停留在数据表中任何单元格，右击鼠标，选择“数据透视表选项(O)?”，打开后“布局和格式”选项卡中的“对于空单元格，显示”填写为“0”，此时得到的前三大客户销售额时间序列。

步骤3：绘制前三大客户销售额时间序列图。

光标停留在数据透视表的数据区域中，选择“数据透视表工具”选项卡中的“选项”卡→“数据透视图”命令，在当前工作簿自动插入一张图表。选择“折线图”→“带数据标记的折线图”命令。随后，再对该图的大小、外观以及数据系列的格式加以调整，就能得到”北风”贸易公司前三大客户销售额时间序列图。

3.绘制按照客户汇总的客户数与销售额Pareto曲线

步骤l：查询“订购日期”、客户的“公司的名称”与“销售额”等数据。

在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“订单”表、“订单明细”表与“客户”表中查询“订购日期”、客户的“公司名称”与“销售额”(销售额=订单明细.单价×数量×（1-折扣））等字段，将所查询数据返回Excel。

步骤2：利用查询的数据，制作数据视表。

从数据透视表的字段列表中，选择“订购日期”字段，并将其拖至行标签，将“销售额”字段拖至“数值”区域。将“订购日期”字段按年组合，然后拖至“报表筛选”区域，将“公司名称”字段拖至行标签，在“行标签”上双击，输入“公司名称”，将“销售额”设置为降序排列字段。在“求和项：销售额”栏下点右键，点“值字段设置(N)?”选项，点击“值显示方式”选项卡，设置“值显示方式”为“列汇总的百分比”，得到按照年度和客户公司名称汇总的数据透视表。

步骤3：利用透视表的数据，计算客户数累计百分比与客户销售额累计百分比，绘制Pareto曲线。 在单元格D3:G3中依次输入说明文字，“公司名称”、“客户百分比”、“客户数累计百分比”、“销售额累计百分比”。按照图2-12所示输入公式，并向下拖动，将E列和F列显示形式设置为百分比，保留1位小数，得到如图2-13所示的汇总数据。

In D4: In E4: In F4: In G4: =A4 =1/COUNTA($D$4:$D$92) =SUM($E$4:E4) =SUM($B$4:B4)

选中单元格F3:G92中的数据，单击“插入”选项卡→选择“散点图”→选择“带平滑线的散点图”，单击该折线图，点击“布局”选项卡中的“网格线”按钮，点击“主要纵网格线”中的“主要网格线”按钮，显示纵向的横坐标网格线。步骤4：在曲线上添加代表20%客户数的垂直参考线。 在单元格I5:I7中输入“20%”，在单元格J5与J7中输入“0”和“120%”，在单元格J6中输入公式：“=INDEX（G4:G92，MATCH(I5,F4:F92,1),1）”，即从客户累计百分比中，查找到20%的客户数在第几行，然后用INDEX函数查找该行对应的销售额累计百分比，计算结果如图2-15所示。选中“图表工具”选项卡组中的“设计”选项卡，点击“选择数据”按钮打开“选择数据源”窗口，选中该窗口中的“添加”按钮，打开“编辑数据系列”窗口，在“系列名称”中输入散点图名称为“20%垂直参考线”，点击“X轴系列值”后的拾取器，选中I5至I7的区域，点击“Y轴系列值”后的拾取器，选中J5至J7的区域，返回并点击“确定”按钮，回到“选择数据源”窗口，点击“确定”按钮。即在前面所绘制的图表上，添加了一条垂直参考线，也就是在源数据中添加了一个系列，这个系列散点图线的X轴数据来自单元格I5:I7，Y轴数据来自单元格J5:J7，得到Pareto曲线。

4.绘制按照订单汇总的销售额与销售次数Pareto曲线 步骤1：查询“订购日期”、“订单ID”与“销售额”等数据。

在一张空自的工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“订单”表、“订单明细”表中查询“订购日期”、“订单ID”与“销售额”（销售额=订单明细.单价×数量×（1-折扣））等字段，将查询数据返回Excel。

步骤2：利用查询的数据，制作数据透视表。

从数据透视表的字段列表中，选择“订购日期”字段，并将其拖至行标签，将“销售额”字段拖至“数值“区域。将“订购日期”字段按年组合，拖至“报表筛选”区域，将“订单ID”字段拖至行标签，在“求和项：销售额”栏下任意位置单击右键打开并设置按销售额降序排列，得到按照年度和订单ID汇总的数据透视表。

步骤3：利用数据透视表的数据，计算客户数累计百分比与销售额累计百分比，绘制Pareto曲线。 在单元格E3:G3中依次输人说明文字：“销售次数百分比”、“销售次数累计百分比”、“销售额累计百分比”。按照图2-17所示输人公式，并复制到下方单元格至G833，设置E、F、G三列数值的显示形式为百分比，3位小数，

单元格E4:E833中计算单次销售占总销售次数（即订单数）的百分比，单元格F4:F833中汇总累计销售次数占总销售次数的百分比，即到该订单为止，已有订单数占到总订单数的百分比。单元格G4:G833中汇总到该订单为止，已有订单实现的销售额占总销售额的百分比

In E4: =1/COUNT($A$4: $A$833)

In F4: In G4: =SUM($E$4:E4) =SUM($B$4:B4)/SUM($B$4:$B$833)

选中单元格F3:G833中的数据，绘制“带平滑线的散点图”，并添加纵向网格线，设置横坐标小数位数为0位

步骤4：在曲线上添加代表20%销售次数的垂直参考线。

在单元格I5:I7中输入“20%”，在单元格J5与J7中输入“0”和“120%”，在单元格J6中输入公式：“=INDEX（G4:G833,MATCH(I5,F4:F833,1),1）”，即从销售次数累计百分比中，查找20%的销售次数在第几行，用INDEX函数查找，该行对应的销售额累计百分比。在前面所绘制的图表上，添加一条垂直参考线。该参考线的X轴数据来自单元格I5:I7，Y轴数据来自单元格J5:J7，

5.汇总各地区客户分布

步骤1：查询“公司名称”’与“地区”等数据。

将Excel一张空白工作表命名为“5.各地区客户分布”，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，

从“客户”表中查询“公司名称”与“地区”字段，然后将所查询的数据返回Excel。 步骤2：利用查询的数据，制作数据透视表。

从数据透视表的字段列表中，选择“地区”字段，拖至行标签。选择“公司名称”字段，拖至“数值”区域，得到按照地区汇总的客户数的数据透视表，

步骤3：利用数据透视表的数据，制作数据透视图。

光标停留在数据透视表中，点击“数据透视表工具”选项卡组中的“数据透视图”命令，选择“簇状柱形图”，在当前工作表中建立数据透视图。

6.绘制各地区平均销售额及销售额占总销售额百分比 步骤1：查询“地区”与“销售额”等数据。

在Excel的空白工作表中，按下Alt+d+p键后启动“数据透视表和数据透视图向导”窗口，选择“外部数据源”选项单击“获取数据”按钮，利用Microsoft query，从“客户”和“订单明细”表中，

查询客户的“地区”与“销售额”（销售额=订单明细.单价×数量×（1-折扣））字段，将查询数据返回Excel。查询时应包括“订单”表，该表能建立“客户”表和“订单明细”表之间的联系。 步骤2：利用查询的数据，制作数据透视表。

从数据透视表的字段列表中，选择“地区”字段，并将其拖至行标签，将“销售额”字段拖至“数值”区域，得到按照地区汇总的销售额的数据透视表，

步骤3：利用数据透视表的数据，计算各地区平均销售额与销售额占总销售额的百分比。 在单元格D3:G3中依次输入说明文字：“地区”、“客户数”、“平均销售额”与“销售额占总额百分比”。按照图2-22所示输入公式，设置平均销售额列显示方式为小数，小数位为0，百分比列显示方式为百分比，小数位为0。

In D4: In E4: In F4: In G4: 3 4 5 6 7 =A4 =’5.各地区客户分布’!B4 =B4/E4 = B4/SUM($B$4:$B$9) D 地区 东北 华北 华东 华南 E 客户数 5 41 16 20 F 平均销售额 10477 12040 17400 12054 G 销售额占总额百分比 4% 39% 22% 19% 8 9

实验小结：

西北 西南 2 7 5597 27019 1% 15% 在实验六中，因为需要插入数据，但是在自己的电脑中无法显示数据，所以只能复制指导书中的数 据，文中已经用红色标出。综合这些实验，给我们最大的体会就是学会了如何查询数据。如何对一 个数据进行分析。 思考题：

1. 你还能从哪些方面对客户的销售数据进行分析，帮助该公司促进销售或者为客户提供 更好的服务？

答：首先除了可以对客户的销售数据进行汇总还可以对数据进行预测，从而得知客户下个季度的销售情况，从而可以进行相应的应对措施。同时可以按照客户的不同级别汇总各级别客户的总销售额，销售额占总销售额的比重，为不同的客户提供不同的服务。 2. 帕累托曲线可以帮助分析投入与产出之间的关系，它还能帮助该公司进行哪些方面的

分析？

答：可以准确的找出销售量达到的层次，便于查看。

实验三 餐饮公司经营数据时间序列预测

实验类型：验证性 实验学时：2 实验目的：

理解指数平滑预测法的概念；

掌握在excel中建立指数平滑预测模型的方法； 掌握寻找最优平滑常数的各种方法。 实验步骤：

步骤1：确定时间序列的类型。

在单元格a1:b21中布置好公司从1987-2006年的销售量数据。然后，绘制公司从1987年至

2006年共20年的销售量折线图。

步骤2：利用“数据分析”工具中的指数平滑功能进行预测。

在“文件”选项卡中选择“选项”，将打开“Excel选项”对话框，在该对话框的左边一栏里选择“加载项”，右边一栏的最下方单击“转到”按钮，Excel将显示“加载宏”对话框。在 “加载宏”对话框中选择“分析工具库”，单击“确定”按钮，将会在“数据”选项卡下方出现“分析”组，其中包含“数据分析”选项，选中“数据分析”，在出现的“数据分析”对话框中选择“指数平滑”，在“指数平滑”对话框中，在“输入区域”输入“b2:b21”单元格，“阻尼系数”输入“0.75”（注：阻尼系数=1-平滑常数），在“输出区域”输入“c2”单元格，单击“确定”按钮。

二、运用指数平滑公式进行预测

步骤1：在单元格f1中输入平滑常数0.25，在单元格c2中输入公式：“=b2”，作为第一年的预测值（F1），在单元格c3中输入指数平滑模型预测公式“=$f$1*b2+(1-$f$1)*c2”。将单元格c3往下复制，便得到2007年的指数平滑预测值7.96。

步骤2：绘制指数平滑预测图。

利用单元格a2:c22中的数据绘制公司销售量指数平滑预测图。

三、寻找最优的平滑常数 步骤1：计算均方误差。

单元格f2中输入公式：“=average((b2:b21-c2:c21)^2)”，作为数组运算，需要同时按Ctrl+Shift+Enter三个键作为输入结束，计算均方误差MSE。 步骤2：利用模拟运算表及查找引用函数功能，寻找最优平滑常数。

单元格e7:e24中给出不同的平滑常数（大于0小于1），在单元格f6中输入公式：“=f2”，选定单元格e6:f24，在“数据”菜单中选择“模拟运算表”。

在单元格f4中输入公式：“=index(e7:e24,match(min(f7:f24),f7:f24,0))”，找到最优平滑常数为0.35。然后，根据最优平滑常数0.35（将此值代入单元格f1中），2007年的预测值为7.94。

步骤3：利用规划求解功能，寻找最优平滑常数。

在“文件”选项卡中选择“选项”，将打开“Excel选项”对话框，在该对话框的左边一栏里选择“加载项”，右边一栏的最下方单击“转到”按钮，Excel将显示“加载宏”对话框。在 “加载宏”对话框中选择“规划求解加载项”，单击“确定”按钮，将会在“数据”选项卡下方的“分析”组中出现“规划求解”选项，选中“规划求解”。 在设置目标栏输入$f$2,然后点击“求解”按钮。 实验3-2 “美食佳”公司月管理费预测 实验目的：

? 理解移动平均预测法的概念；

? 掌握在excel中建立移动平均模型的方法； ? 掌握寻找最优移动平均跨度的各种方法。 实验步骤：

一、运用“数据分析”工具进行移动平均预测 步骤1：确定时间序列的类型。

绘制公司从2006年1月至2007年6月共18个月的管理费用折线图，

步骤2：利用“数据分析”工具的移动平均功能进行预测。

在“数据”选项卡中选择“数据分析”，在出现的“数据分析”对话框中选择“移动平均”， 在“移动平均”对话框中，在“输入区域”输入“c2:c19”单元格，“间隔”输入“3”（注：移动平均跨度为3），在“输出区域”输入“d3”单元格，单击“确定”按钮。

二、运用移动平均公式进行预测

步骤1：利用average()函数计算移动平均预测值。

在单元格g1中输入移动平均跨度3，在单元格d5中输入移动平均模型预测公式：“=average(c2:c4)”。将单元格d5往下复制，便得到2007年7月的移动平均预测值20.3。

步骤2：绘制移动平均预测图。

利用单元格c2:d20中的数据绘制公司18个月的管理费用及移动平均预测图。

三、寻找最优的移动平均跨度 步骤1：计算均方误差。

此处用到两个函数：sumxmy2()函数和count()函数。sumxmy2()函数的功能是返回两数组中对应数值之差的平方和，它需要两个参数，一个参数是第一个数组或数值区域，另一个参数是第二个数组或数值区域。count()函数的功能是计算某一范围内包含数值的单元

格的个数。在单元格g2中输入公式：“=sumxmy2(c2:c19,d2:d19)/count(d2:d19)”，计算均方误差MSE。

步骤2：利用offset()函数辅助进行不同移动平均跨度下的预测。

借助average()函数进行的移动平均计算仅对跨度3有效，若跨度改为其他值，则要修改average()函数的参数。为此引入offset()函数解决average()函数的参数范围变化问题。offset()函数的功能是以指定的范围为参照系，通过给定偏移量得到新的范围。返回（求出）的范围可以为一个单元格或单元格区域，并可以指定返回的行数或列数。它需要五个参数，第一个参数是作为参照系的基准位置；第二个参数是相对于这个基准位置向上（用负数表示）或向下（用正数表示）偏移的行数；第三个参数是相对于这个基准位置向左（用负数表示）或向右（用正数表示）偏移的列数；第四个参数是要返回数据范围的行数；第五个参数是要返回数据范围的列数。事实上前三个参数指定了要返回数据范围的起始单元格。 在单元格d5中输入公式：

“=if(a5<=$g$1,\”，拖动单元格d5的填充柄向上复制至d2，向下复制至d20，从而可在变化的移动平均跨度下计算移动平均值。 将单元格g1中的移动平均跨度改为2，不必改动d列的公式，

步骤3：利用模拟运算表及查找引用函数功能，寻找最优移动平均跨度。

在单元格f7:f21给出不同的移动平均跨度，在单元格g6中给出公式：“=g2”，选定单元格

f6:g21，在“数据”菜单中选择“模拟运算表”，

在单元格g4中输入公式：“=index(f7:f15,match(min(g7:g15),g7:g15,0))”，找到最优移动平均跨度为5。 实验思考

1. 可否利用规划求解功能，寻找最优的移动平均跨度？

答：在实验3-2中，无法利用规划求解功能寻找最优的移动平均刻度。因为求MSE所用的公式为“=SUMXMY2(C2:C19,D2:D19)/COUNT(D2:D19)”与移动平均刻度值所在的G1单元格无直接联系 2. excel提供的移动平均趋势线功能也可进行移动平均预测，但趋势线方法与本实验所介绍的方法有何不同？

答：Excel提供的移动平均趋势线方法与本实验所介绍的方法与本实验所介绍方法的区别在于趋势线的作用是对已知的一堆数据作回归分析，以找到一个可以直接计算的方程式并对其他任意未经测量的数值进行计算。趋势线方法考虑了大量可能的结果。

实验3-3 “美食佳”华东分公司销售额趋势预测

实验目的

? 理解趋势预测法的概念；

? 掌握在excel中建立线性趋势预测模型的方法； ? 掌握寻找线性趋势模型参数的各种方法； ? 掌握线性趋势值预测的不同方法 步骤1：确定时间序列的类型。

步骤2：添加线性趋势线。

在图中选中数据系列，右键菜单中选择“添加趋势线”，出现“添加趋势线”对话框。 在“添加趋势线”对话框的“类型”中选择“线性”。在“添加趋势线”对话框的“选项”中

选

择

“

显

示

公

式

”

和

“

显

示

r

平

方

值

”。

步骤3：用趋势线前推法大致预测线性趋势值。

选定线性趋势线，右键菜单中选择“趋势线格式”，在“趋势线格式”对话框中选定“选项”，将趋势预测前推1周期。

步骤4：用方程或函数准确预测线性趋势值。

根据得到的线性趋势方程公式y=11.473x+861.98，如图3-28所示，在单元格c13中输入公式：“=11.473*a13+861.98”，即将x=12（2007年为第12个时间序列点）代入公式，计

算得到2007年的预测值为999.66。

利用forecast()函数，在单元格c14中输入公式：“=forecast(a13,c2:c12,a2:a12)”，计算得到2007年的预测值为999.65。

利用trend()函数，在单元格c15中输入公式：“=trend(c2:c12,a2:a12,a13)”，计算得到2007年的预测值为999.65。

步骤5：将预测结果在图中表示。

同时选中单元格b13和c13，并作为新数据点复制到图形的数据线上，趋势线前推只能在图中看到大致预测结果1000左右， 实验思考

1．本实验的几张图中，x轴是“分类”还是“自动”？ 本实验（实验3-3）中，X轴是自动

2．预测点数据如果作为新数据系列添加到图形中，结果与图3-29有何不同？

答：实验3-3中，预测点数据如果作为新数据系列添加到图形中，预测部分的值将是一条直线。 3．为什么预测值一定在趋势线的延伸线上？

答：预测值一定在趋势线上的原因是预测值是依据趋势线作出来的。

4．若要预测公司2008年的全国销售额，可以怎么做？若要预测公司2009年、2010年、甚至更远年份的销售额，会有什么问题？

答：若要预测2008年的全国销售额，可依据2007年的预测值来作。但若要预测更远年份的销售额，则不能以之为基础由趋势线函数进行预测，因为彼时销售额呈线性增长，与客观事实不符。 5．除了本实验中介绍的添加趋势线方法可以找到线性趋势预测模型的参数外，还可以用哪些方法找到线性趋势预测模型y=a+bx中的参数 a和b。 答：还可用回归方法找到Y=a+bX中参数a,b的值。

实验3-4 “美食佳”公司会员卡发行量趋势预测 实验类型：验证性 实验学时：2

实验目的：

? 理解非线性趋势预测法的概念；

? 掌握在excel中建立非线性趋势预测模型的方法； ? 掌握非线性趋势值预测的方法。 ? 预测公司2007年7月会员卡的发行量。 实验步骤：

步骤1：确定时间序列的类型。

步骤2：添加非线性趋势线

在图中选中数据系列，右键菜单中选择“添加趋势线”，出现“添加趋势线”对话框。

在“添加趋势线”对话框的“类型”中选择“对数”。在“添加趋势线”对话框的“选项”中选中“显示公式”和“显示r平方值”

步骤3：趋势线前推法大致预测非线性趋势值。 选定对数趋势线，右键菜单中选择“趋势线格式”，

在“趋势线格式”对话框中选定“选项”。将趋势预测前推1周期。 步骤4：用方程或函数准确预测非线性趋势值。

根据得到的方程公式y=7.7785ln(x)+3.7651，在单元格c16中输入公式：“=7.7785*ln(a16)+3.7651”，即将x=15（2007年7月为第15个时间序列点）代入公式，计算得到2007年7月的会员卡发行预测值为24.83万张。

步骤5：将预测结果在图中表示。

趋势线前推只能在图中看到大致预测结果，

实验思考

1．请试一下，图3-39可否考虑用xy散点图做？ 这时用什么数据作为x轴合适？ 答：可以，用序号的对数值ln(x)代替x，作为x轴合适，y与x是一次线性函数关系。 2．为什么预测值一定在趋势线的延伸线上？

答：预测值一定在趋势线上的原因是预测值是依据趋势线作出来的。

3．除了本实验中介绍的添加趋势线方法可以找到对数趋势预测模型的参数外，是否可以用规划求解法找到对数趋势预测模型y=a+bln(x)中的参数a和b？ 答：还可用回归方法找到Y=a+bX中参数a,b的值。 实验3-5 “美食佳”火锅连锁店原料年度采购成本预测 实验目的：

? 理解季节指数的概念； ? 掌握季节指数预测方法。 实验步骤：

步骤1：确定时间序列的类型。

步骤2：计算季节指数。

一年有4个季度，所以以4为移动平均跨度，计算移动平均数，其结果应该对应放在每4个季度的中间位置。但当移动平均跨度为4时，没有中间季度位置可放，因此只能放在第3个季度对应的位置处。所以从第一年开始，4个季度的移动平均数放在单元格d4中，即在单元格d4中输入公式：“=average(c2:c5)”。拖动单元格d4的填充柄复制到单元格d5:d16中，

在单元格e4中输入公式：“=(d4+d5)/2”，并将它复制到单元格e5:e15内。由此完成中心化移动平均数的计算。

把中心化后的移动平均数单元格e2:e17添加到图3-41所示的折线图中，得到如图3-44的结果。可见中心化的移动平均数体现了原材料采购成本的稳定水平，即在一定程度上消除了原材料采购成本时间序列的不规则成分。

在单元格f4中输入公式：“=c4/e4”，并将它复制到单元格f5:f15中，由此完成季节不规则值的计算。 分别计算每一年对应季度的不规则值的平均值，就可得到各个季度的季节指数。如在单元格i2中输入公式：“=average(f2,f6,f10,f14)”，可计算得到第1季度的季节指数。

用每一个季节指数除以未调整的季节指数之和再乘以季度指数总和4。如图3-43中单元格i6所示，未调整前的季节指数之和为3.9852，所以需要调整。在单元格j2中输入公式：“=i2/$i$6*4”，往下复制到j3:j5，得到调整后的季节指数。 步骤3： 消除季节影响。

将调整后的季节指数复制到E列，分别对应2003-2007年的4个季度。

在单元格F2中输入公式：“=D2/E2”，将公式复制到单元格F3:F17中，得到消除季节影响后的结果。 利用单元格F2:F17中的数据绘制公司从2003年第1季度到2006年第4季度共16个季度的原材料采购成本折线图，并添加线性趋势线，

步骤4：计算预测值。

在单元格H2中输入公式：“=G2*E2”，将公式复制到单元格H3:H21中，即在线性趋势预测值的基础上乘以调整后的季节指数得到最终的季节预测值。公司2007年1至4季度的采购成本预测值分别为73.0、20.9、13.8、154.9。根据D列的原始采购成本数据和H列的季度预测值数据，作折线图，

实验思考

1．图3-47中的“序号”一列有什么用？

答：图3-47中“序号”一列的作用是为趋势线公式的获得提供依据（作为自变量X）。

2．计算趋势预测值时，若不用forcast()函数，还可以有什么方法？请至少用两种方法试试看。 答：计算趋势预测值还可用移动平均预测法、指数平滑预测法、一元线性回归分析模型等。 3．季节指数模型是否只能用于季节数据的预测？若是年度、月度、甚至周数据，可以用季节指数模型吗？

答：季节指数模型不是只能用于季节数据的预测，年度、月度、周数据等在某些情况下均能用季节指数模型。 实验小结：

在实验中遇到的问题是，在进行指数平滑求解时，最优的平滑指数求法很麻烦，在多次进行求解后才可以得到。 思考题：

1. 为什么用模拟运算表加查找引用函数功能，得到的最优平滑常数（0.35），与用规划求解功能得到的结果（0.37）不一样？

答：用模拟运算表加查找引用函数功能，得到的最优平滑指数（0.35），与规划求解的结 果（0.37）比一样的原因是因为系统误差的存在。

2. 可否调整模拟运算表的输入数据间隔，再试一试，结果会如何？

答:结果不变，因为模拟运算表只是将数据代入变量中来求得对应的值，所得到的值与数据的间 隔无关。

实验四 住房建筑许可证数量的回归分析 实验类型：验证性 实验学时：2 实验目的：

理解一元线性回归分析的概念；

针对不同的问题，能够建立适当的一元线性回归模型； 掌握内建函数slope()、intercept()与linest()的用法；

掌握用规划求解法、添加线性趋势线法、回归分析报告法确定线性回归方程的系数； 给定自变量的情况下，根据线性回归模型预测因变量的值。 实验步骤：

步骤1：确定因变量与自变量并输入观测值。

根据实验要求，我们确定因变量为建筑许可证的颁发数量，自变量为人口密度，并将数 据合理的布置在excel工作表的单元格a1:b19中

步骤2：绘制因变量与自变量关系散点图。

利用图中的数据，以每平方公里的人口密度为x值，建筑许可证的颁发数量为y值，绘 制xy散点图。

步骤3：求出回归系数a、b的取值，计算判定系数R2，并进行预测。excel提供了几种不同的工具，包括规划求解工具，intercept()、slope()与linest()等内建函数，在散点图中添加趋势线和趋势线方程以及生成回归分析报告等方法来确定回归系数a和b。我们这里介绍利用规划求解的方法来求解回归系数。 步骤4：假定回归系数的值，建立线性回归模型。

假定回归系数的值为a=1，b=1并将之放在单元格f2:f3中。用回归直线方程y=a+bx以及 每平方公里的人口密度来计算建筑许可证的颁发数量预测值，放在单元格c2中，即在单 元c2中输入公式“=$f$2+$f$3*a2”，并将此公式复制到c3:c19中，得到建筑许可证的颁 发数量预测值。在单元格f5中计算建筑许可证的颁发数量观测值与预测值的均方误差 mse，即在单元格f5中输入公式“{=average((c2:c19-b2:b19)^2)}”(注：其中的花括号不 是直接输入，是将所有内容输入完后按住ctrl+shift键后再按回车键生成的)。

步骤5：启动规划求解工具，确定模型最优参数。

在“规划求解参数”对话框中将目标单元格设为$f$5，使其等于最小值，将可变单元格

设为$f$2:$f$3，无须设置任何约束条件即可直接求解，保存规划求解结果。 步骤6： 计算判定系数r2，说明建筑许可证数量的预测值的可信度。

excel有一个专门用来计算一元线性回归判定系数的内建函数rsq()，它需要两个参数，第一个参数是因变量各观测值所在单元格的范围，第二个参数是自变量各观测值所在单元格的范围。将r2的计算结果放在单元格f4中，即在f4中键入公式“=rsq(b2:b19,a2:a19)”，得到r2的值约为0.937。这表明，回归方程可以很好地用来描述建筑许可证的颁发数量与每平方公里的人口密度之间的关系，用此回归方程来进行预测是可信的。 实验4-2 “家家有房”公司建筑许可证一元非线性回归分析 步骤1：确定因变量与自变量。

根据实验要求，我们确定因变量为建筑许可证的颁发数量，自变量为自由房屋的均值。并将数据合理的布置在excel工作表的单元格a1:b19中，如图4-6中所示，以备建模使用。

步骤2：选择合适的回归方程。 利用步骤1中准备的数据画出散点图，

步骤3：假定回归系数的值，建立非线性回归模型。

假定回归系数的值为a=1，b=1并将之放在单元格f2:f3中。用回归对数方程y=a+blnx以及自由房屋均值来计算建筑许可证的颁发数量预测值，放在单元格c2中，即在单元c2中输入公式“=$f$2+$f$3*ln(a2)”，并将此公式复制到c3:c19中，得到建筑许可证的颁发数量预测值。在单元格f5中计算建筑许可证的颁发数量观测值与预测值的均方误差mse，即在单元格f5中输入公式“{=average((c2:c19-b2:b19)^2)}”（用ctrl+shift+enter组合键添加花括号）。

步骤4：确定参数a与b的值。

对于本例的问题，我们采用规划求解的方法来确定参数a与b的值，利用规划求解工具计算出使mse极小的参数a与b。

步骤5：添加趋势线，显示R2值。

在散点图中通过添加对数趋势线，并在添加趋势线对话框中的“选项”中选择“显示R2”与“显示公式”。

步骤6：进行预测。

根据对数回归方程，如果任意给定 自由房屋的均值，即可预测出建筑许可证的颁发数量。将x=300，预测出颁发的建筑许可证数量为-78874.21+16877.35*ln（300）=17390.4976 实验4-3 “家家有房”公司建筑许可证多元线性回归分析 步骤1：输入原始数据。

首先分析案例中的自变量和因变量，并将数据合理的布置在excel工作表的a1:d19中。

步骤2：分别绘制三个候选自变量与因变量之间的关系图。

步骤3：针对每一个候选变量生成回归分析报告。

选择“数据”选项卡，单击“分析”组中的“数据分析”，然后在出现的数据分析对话框中选中“回归”，单击“确定”按钮，在接着弹出的“回归”对话框中将“Y值输入区域”一栏设为“$D$1:$D$19”，将“X值输入区域”一栏设为“$A$1:$A$19”，最后将输出选项中的输出区域设置为新工作薄，单击确定按钮，这个回归分析报告分析了对每平方公里的人口密度与建筑许可证颁发数量间的关系。分别对其他两个候选变量做回归分析报告，根据

值，找出最优的变量。

步骤4：根据调整后的R2值确定回归分析所要采用的两元自变量。

分别做建筑许可证的颁发数量与{每平方公里人口密度x1，平均家庭收入x3}及{自由房屋的均值x2，平均家庭收入x3}之间的回归分析报告。 实验4-4 “家家有房”公司建筑许可证多元非线性回归分析 步骤1：确定因变量与自变量。

根据实验要求，我们确定因变量为建筑许可证的颁发数量（y），因变量为平均家庭收入

（x2）与人均交纳税收（x2），并将数据合理的布置在excel工作表中。

步骤2：确定模型并对模型初始化。

在单元格h2:h4中分别放入参数a，b1，b2初值1，并在单元格d2中输入公式“=$h$2+ $h$3 *a2^2+$h$4*b2^2”，并将其复制到公式d3:d19中，在单元格h5中输入公式“=sumxmy2(d2:d19,c2:c19)/count(d2:d19)”。 步骤3：启用规划求解工具。

在“工具”菜单中选择“规划求解”，打开“规划求解”对话框，然后点击“求解”按钮。 步骤4：根据获得的参数进行预测。

利用规划求解的结果，将参数a，b1，b2的值及平均家庭收入为80千元，人均交纳的税收为7千元代入模型y=a+b1*x1^2+b2*x2^2， 步骤5：将非线性模型与线性模型结果比较。

将模型假设为线性模型y=a+b1*x1+b2*x2，并将参数放置在单元格h8:h11中，重复步骤2与步骤3，可以看到在线性模型求得的mse为1500179.183，比在非线性模型下求得的mse的值1158258.3 实验小结：

本次实验主要目的是理解移动平均法，指数平滑法，趋势预测法，非线性趋势预测法，掌握在excel

中建立指数平滑预测法等的方法。这些方法对以后我每年的学习中有很多的帮助。 思考题：

1.除了用规划求解的方法外，还可以哪些其它方法求出建筑许可证数量与每平方里人口密度之间关系的回归方程y=a+bx的系数，请用其它方法求得系数，并检验与实验4-1所获得的系数是否一致。

答：还可以用添加趋势线的方法获得回归方程的系数。

2.如果每平方公里的人口密度与建筑许可证数量之间是非线性关系，该如何选择非线性模型，并针对任意给定每平方公里的人口密度，预测建筑许可证的颁发数量。

答：可利用添加趋势线的方法进行检测，找出每一种可能的非线性模型的均方差Mse，选择其中最小的作为最佳的非线性模型。

1. 在用回归分析报告求解参数时，自变量与因变量之间应该满足什么关系？

答：在用回归分析报告求解参数时，自变量与因变量之间应满足一个或多个自变量值对应一个应变量。

2. 为什么实验结果只选用两元线性回归模型而不用三元线性回归模型进行建筑许可证数量的预测？

答：实验结果只选用两元线性回归模型二不用三元线性回归模型既高兴建筑许可证数量预测的原因是三元回归分析报告中自有房屋的均值X2的调整后的R2的值并未超过一元回归分析报告中对自有房屋的均值X2的调整后的R2的值，说明自有房屋的均值X2与建筑许可证数量的线性相关性不强，若它参与回归预测，将会影响预测结果。

3. 在用多元线性回归时，如何确定候选变量，确定的依据是什么？

答：用多元线性回归时，可依据对某一自变量在组合前得到的调整后的R2的值与组合后得到的调整后的R2的值之间的大大小进行候选变量的确定。若组合后得到的调整后的R2的值超过组合前得到的调整后的R2的值，则确定其为候选变量。

4. 从实验4-2我们了解自有房屋的均值（x2）与建筑许可证数量是对数相关，如果我们用回归方程y=a+b1*x1+b2*lnx2+b3*x3来进行预测是否更精确？那么我们又怎样确定此方程的各项系数呢？ 答：若用回归方程Y=a+b1*X1+b2*lnX2+b3*X3来进行预测，结果不一定会更精确。因为Y是 受3个自变量的共同影响。可用多元非线性回归确定次方程的各项系数，因为线性回归是特 殊的非线性回归。

实验五 手机用户消费习惯聚类分析

实验类型：验证性 实验学时：2 实验目的

理解聚类分析的概念； 理解聚类分析的原理；

掌握在SPSS中进行聚类分析的方法。 实验步骤：

为研究移动用户的手机消费习惯，现收集了反映移动用户手机使用情况的数据，该数据中包含7个变量：客户编号(Customer_ID)、工作日上班时期电话时长(Peak_mins)、工作日下班时期电话时长(OffPeak_mins)、周末电话时长(Weekend_mins)、国际电话时长(International_mins)、总通话时长(Total_mins)和平均每次通话时长(average_mins)。请用SPSS软件按除客户编号外的6个变量维度对移动用户进行细分。

选择菜单

打开SPSS文件 telco.sav→分析(Analyze)→描述统计(Descriptive Statistics)→描述(Descriptives…)

选入变量 设置选项

将除“Customer_ID”外的其余6个变量选入变量框(Variables)中

打开Options按钮，勾选均值(Mean)、标准差(Std. deviation)、最小值(Minimum)、最大值(Maximum)四项

上图显示6个变量数值差异较大，其中均值最大值为1064.3，最小值为4.1267，标准差的取值也从最小的3.804变化到最大的560.801。这种差异会影响聚类分析的结果。而要消除这种影响，需在聚类前对数据进行标准化处理。 2.标准化处理

在上一步弹出的“Descriptive”对话框中选择“Save standardized values as variables”，即将标准化值另存为变量，

标准化的目的是消除量纲和变异的影响。消除量纲影响，要扣减平均值；消除变异影响，要除以标准差。因此标准化数据等于某变量的观察值减去该变量的平均数，然后除以该变量的标准差。标准化后各变量的平均数为0，标准差为1，消除了量纲和变异的影响。如ID为K1000050的用户工作日上班时间通话时长(Peak_mins)标准化后的值，Peak_mins标准化=（观察值-均值）/标准差=

（40.61-708.347）/515.258=-1.296。 3.聚类分析 选择菜单

打开SPSS文件 telco.sav→分析(Analyze)→分类(Classify)→K均值聚类(K-Means Cluster…)

选择变量 设置选项

选入上图中红框内的6个标准化数据变量

主窗口设置选项如下图中的第一个图，其中Label Cases by设置为客户编号表示个案标记依据为客户编号，Number of Clusters设置为5表示聚类后生成5类不同特征的数据。“Iterate…”、“Save…”、“Options…”3个按钮的设置依次按照下面3个图进行设置。Maximum Iterations设置为100表示最大迭代数为100，Convergence Criterion=0表示收敛标准为0。勾选Cluster membership表示在原始数据中生成每个记录对应的类别号1-5，Initial Cluster centers表示生成初始聚类中心，ANOVA tables表示生成方差分析表。

Initial Cluster Centers初始聚类中心表 Zscore: 工作日上班时期电话3.21791 时长 Zscore: 工作日下班时期电话-.65276 时长 Zscore: 周末电话时长 Zscore: 国际电话时长 Zscore: 总通话时长 Zscore: 平均每次通话时长 3.72181 4.90995 2.96323 -.51651 3.11491 -.02169 -.90652 2.77257 1.47340 -.22792 -1.21281 .53252 1.63709 1.29999E1 -1.26557 -1.03058 3.87339 -.17204 -1.16165 2.64849 .19729 1.93001 Cluster 1 2 3 4 5 -1.16636 .29390 -1.31226 2.07308 .30760 5.49282

最终聚类中心表是非常重要的表格。它反映了所划分的5个类别在6个变量上的平均值，这些平均值的区别反映了各个类别之间的差异。 Final Cluster Centers最终聚类中心表 Zscore: 工作日上班时期电话1.60559 时长 Zscore: 工作日下班时期电话.46081 时长 Zscore: 周末电话时长 Zscore: 国际电话时长 Zscore: 总通话时长 Zscore: 平均每次通话时长 -.14005 1.68250 1.62690 -.06590 -.15010 -.64550 -.94040 -.14835 .35845 .04673 .41420 -.05337 -.02375 .02351 .10398 -.14059 -.40407 -.04415 .21627 4.87718 -.58917 -.49365 1.18873 -.29014 -.78990 .61342 -.33584 .37303 Cluster 1 2 3 4 5 下表是方差分析表，用于差异显著性检验。若F值的相伴概率sig小于显著性水平就通过检验，说明各个类别差异是显著的。在下表中6个变量的相伴概率值sig都小于显著性水平0.05，因此都通过了检验。这说明所分的5类用户之间存在显著差异。

Zscore: 工作日上班时期电话582.315 时长 Zscore: 工作日下班时期电话468.001 时长 Zscore: 周末电话时长 Zscore: 国际电话时长 Zscore: 总通话时长 605.770 4 .286 3390 3 Zscore: 平均每次通话时长 463.823 4 .454 3390 3 1.022E.000 39.060 443.179 4 4 .955 .478 4 .449 3390 3 3390 40.896 .000 3390 926.658 .000 2.115E.000 4 .314 3390 3 1.042E.000 1.854E.000

Cluster 1 2 3 4 5 Valid Missing 4.数据分析及分类

443.000 1.239E3 831.000 806.000 76.000 3.395E3 .000

实验小结：

这个实验主演是让我们了解聚类分析的方法，进行聚类分析，掌握方法后可以为研究客机消 费习惯，可以准确的获取消费者的有效数据，有效信息，为以后我们做调查提供了一个很好 的研究方法。

实验六 新产品价格敏感度测试模型分析

实验目的

? 理解价格敏感度测试模型PSM的概念； ? 理解价格敏感度测试模型PSM的原理； ? 掌握在EXCEL中进行PSM分析的方法。 实验步骤： 1.问题设置

测试用户对价格的可接受范围用测试价格表，要求受访者从表中找出以下四个价格点。 开始觉得价格便宜 开始觉得价格贵 觉得价格太贵而不会购买

觉得价格太便宜以至于怀疑其质量而放弃购买。

2.数据分析

（1）价格范围与最优价格

以上图的价格测试为例，测试完毕后统计出每个价格上述4个问题的累计人数百分比。

根据上表画折线图

理想价格点(P4)：太便宜与太贵的交点。从上图中看出，P4点是认为价格太贵与认为价格太便宜的曲线交点。与其他交点相比，在P4点上，既不觉得太贵(1-10%=90%)也不觉得太便宜(1-10%=90%)的人数最多。这也就意味着在P4点上，有最多的消费者可能购买，市场份额最大化。因此，从规模最大化的角度看，P4为最优价格点。

无差异价格点(P2):开始觉得便宜和开始觉得贵的交点。从上图看出，在P2点上，认为价格较划算

而购买该产品的人数与认为价格较贵但仍愿意购买的人数相等。表明人们对该价格点的感觉最为平淡。

合理定价区间(P1~P3):合理定价区间也是以市场规模为判断标准。若低于P1点，虽然开始觉得贵的人群有所减少，但认为太便宜而不愿购买的人群以更快的速度增长，从而导致实际的市场份额减少；若高于P3点，虽然开始觉得便宜的人群有所减少，但认为太贵而不愿购买的人群增加幅度更高，也会导致实际市场份额减少，因此合理的定价区间为P1~P3。 （2）各类价格的接受区域

PSM模型可计算出给定价格水平上，可接受该价格者比例和有保留接受该价格者比例。可接受该价格者指对该价格既不觉得贵也不觉得便宜的人。有保留接受该价格者指对该价格觉得贵但不觉得太贵，觉得便宜但不觉得太便宜的人。

例如：以上的浴霸产品测试价格在800元时，开始觉得便宜比例Q1为70%，觉得太便宜而不买Q2为23%，开始觉得贵Q3是11%，觉得太贵而不买Q4是3%。则：可接受800元价格的人数比例（确定客户比例）是：不觉得贵并且不觉得便宜的比例=100%-（Q1开始觉得便宜人数70%+ Q3开始觉得贵的人数11%）=1-81%=19%。

不能接受800元价格的人数比例是：觉得太便宜并且觉得太贵的人数比例=Q2+Q4=23%+3%=26%。 有保留接受800元价格的人数比例（潜在客户比例）：觉得贵并且不觉得太贵+觉得便宜并且不觉得太便宜的比例= Q1-Q2+(Q3-Q4)=70%-23%+(11%-3%)=55%。

本文来源：https://www.bwwdw.com/article/4g4p.html