数据分析与建模,实验报告,实验一,,简单数据建模

1、数据分析与建模,实验报告,实验一,简单数据建模 学生学号 实验课成绩 学 学 生 实 验 报 告 书 实验课程名称 数据分析与建模 开 开 课 学 院 管理学院 指导教师姓名 鄢 丹 学 学 生 姓 名 学生专业班级 信管 班 2018 20XX 学年 第 1 学期 1 实验报告填写说明 1 综合性、设计性实验必须填写实验报告，验证、演示性实验可不写实验报告。 2 实验报告书 必须按统一格式制作（实验中心网站有下载）。 3 老师在指导学生实验时，必须按实验大纲的要求，逐项完成各项实验；实验报告书中的实验课程名称和实验项目 必须 须与实验指导书一致。 4 每项实验依据其实验内容的多少，可安排在一

2、个或多个时间段内完成，但每项实验只须填写一份实验报告。 5 每份实验报告教师都应该有签名、评分表及实验报告成绩。 6 教师应及时评阅学生的实验报告并给出各实验项目成绩，完整保存实验报告。在完成所有实验项目后，教师应按学生姓名将批改好的各实验项目实验报告装订成册，构成该实验课程总报告，按班级交到实验中心，每个班级实验报告袋中附带一份实验指导书及班级实验课程成绩表。 7 实验报告封面信息需填写完整，并给出实验环节的成绩，实验环节成绩按其类型采取百分制或优、良、中、及格和不及格五级评定（与课程总成绩一致），并记入课程总成绩中。 1 实验课程名称：_ 数据分析与建模_ 实验项目名称 实验一 简单的数据

3、建模 实验 成绩 实 实 验 者 专业班级 组 组 别 无 无 同 同 组 者 无 无 实验日期 2018 年 年 9 月 月 26 日 第一部分：实验预习报告（ 包括实验目的、意义，实验基本原理与方法，主要仪器设备及耗材，实验方案与技术路线等 ） 一、实验目的、意义 本实验旨在通过资料查阅和上机实验，使学生加深了解数据分析与建模的理论与方法，掌握典型的数据模型的建立与使用。 二、实验基本原理与方法 数据分析的理论，最优化模型的建模方法。 应用 Excel 的方法。 三、实验内容及要求 1 、应用 Excel 建模分析 某学院有 3 个系，共有学生 200 人，A 系 103 人，B 系 63

4、 人，C 系 34 人。现在成立一个由 21 名学生组成的学生会，该如何公平地分配席位？ 实验任务：用 利用 Q 值法分配席位，并且在 Excel 中进行 Q 值计算。 （提示：参考讲义中的计算过程。） 2 、单变量最优化 一个汽车制造商售出一辆某品牌的汽车可获利 1500 美元，估计每 100 美元的折扣可以使销售额提高 15%。 （1）多大的折扣可以使利润最高？利用五步方法及单变量最优化模型。 （2）对你所得的结果，求关于所做的 15%假设的灵敏性。分别考虑折扣量和相应收益。 （3）假设实际每 100 美元的折扣仅可以使销售额提高 10%，对结果会有什么影响？如果每 100 美元折扣的提高

5、量为 10%15%之间的某个值，结果又如何？ （4）什么情况下折扣会导致利润降低？ 实验任务：请将上述求解过程， 除了用导数求解外，再用 用 Excel 建模求解之。 （提示：考虑 Excel 的数据，图形， 公式三者的关系；Excel 。 的函数。参考教材第一章。） ） 四、实验方案或技术路线（只针对综合型和设计型实验） 按照实验任务要求，理论结合实际的实验方案，巩固课程内容，温故知新，查遗补漏，夯实理论基础，提升实验动手能力。 技术路线是，从整体规划，分步骤实施，实验全面总结。 2 第二部分：实验过程记录 （可加页）（包括实验原始数据记录，实验现象记录，实验过程发现的问题等） 1 、应用

6、Excel 建模分析 1.分配方案： 第一步：对每个单位各分配一席； 第二步：当分配下一席位时，计算在当前席位份额下各单位的 Q 值，并比较相应 Q 值的大小，将下一席位分配给当前 Q 值最大的一方； Q 值计算公式为： （其中，Qi 表示单位 i 的 Q 值，Pi 表示单位 i 的人数，Ni 表示单位 i 的当前席位数） 第三步：重复执行第二步，直至席位分配完为止。 2.实验步骤：本实验的实验工具为 Excel （1）首先，打开 Excel 新建一个表格，并做好前期的基本数据输入工作，表格内容包括三部分： a. 已知的每个系的人数和所求的每个系最终分得席位数； b. 在不同的已分配席位数的情

7、况下，三个系 Q 值的取值； c. 席位分配过程：给席位编号，标注出每个席位的分配结果； 完成后结果如下图所示： （2）然后，对每个系均分一个席位后，开始对第 4 个席位进行分配。此时各系已分配席位数均为 1，计算此时各系的 Q 值并比较大小： 3 a. 计算 A 系的 Q 值，公式如图所示： b. 计算 B 系的 Q 值，公式如图所示： c. 计算 C 系的 Q 值： 4 Q 值大者得席位，所以第 4 个席位分配给 A 系。 （3）然后对第 5 个席位进行分配，由于只有 A 系的已分配席位数变为 2，所以此时只需计算 A 系的 Q 值，再比较各系 Q 值大小即可。A 系 Q 值的计算公式只需

8、将原来的 A6 都换成 A7即可，如下图所示： Q 值大者得席位，所以第 5 个席位分配给 B 系。 5 （4）然后对第 6 个席位进行分配，由于只有 B 系的已分配席位数变为 2，所以此时只需计算 B 系的 Q 值，再比较各系 Q 值大小即可。B 系 Q 值的计算公式只需将原来的 A6 都换成 A7即可，如下图所示： Q 值大者得席位，所以第 6 个席位分配给 A 系。 （5）采用类似上述的方法（当已分配席位数加 1 时，Q 值的计算公式中 A 后面的数字也加 1 即可）依次对后面的席位进行分配，直到第 21 个席位分配完毕。 最终 A 系分得席位 11 个，B 系分得席位 6 个，C 系分

9、得席位 4 个。最终分配结果及分配具体分配过程如下图： 6 2 、单变量最优化 （ （1 ）多大的折扣可以使利润最高？利用五 步方法及单变量最优化模型。 1. 提出问题 【全部的变量包括】 一辆某品牌汽车的成本 C（美元） 一辆某品牌的汽车的折扣金额 100x（美元） 没有折扣时一辆某品牌汽车的售价 P（美元） 有折扣时一辆某品牌汽车的售价 p（美元） 没有折扣时的销量 Q（辆） 有折扣时的销量 q（辆） 没有折扣时的销售额 R（美元） 有折扣时的销售额 r（美元） 有折扣后的利润 L（美元） 【关于上述变量所做的假设】 P C = 1500 p = P 100x q = Q * (1 + 0

10、.15x) L = q * (p C) x = 0 【目标】求 L 的最大值 2. 选择建模方法 本题为单变量优化问题，则建模方法为：设 y = f ( x )在 x = 0 的区间范围内是可微的，若 f ( x )在 x 处达到极大或极小, 则 f ?( x ) = 0。 3. 推导数学表达式 L = q * (p C) = Q * (1 + 0.15x) * (p C) = Q * (1 + 0.15x)*(1500 - 100x) = Q * ( -15 x2 + 125x + 1500) 记 y = L 作为求最大值的目标变量，x 作为自变量，原问题就化为在集合 S= x : x 0上

11、求以下函数的最大值： y = f ( x ) = Q * ( -15 x2 + 125x + 1500) （Q 为非负常量） 4. 求解模型 在本题中，即对 y = f ( x ) = Q * ( -15 x2 + 125x + 1500) 在区间 x = 0 上求最大值，Q 为非负常量。当 f ?( x ) = Q * (-30x + 125) = 0 时，解得 x 4.17 故 y = f ( x ) = Q * ( -15 x2 + 125x + 1500) 在 x = 4.17 时取得最大值。 5. 回答问题 答：417 美元折扣可以使利润最高。 【 【Excel 建模求解】 1. 打

12、开 Excel 新建一个表格，分别列出 X 栏和 Y 栏。X 栏依次写入 0，1，2，3 ? 等等，Y 栏第一项，根据公式，将 x 以 A2 替代，写入公式“= -15*A2*A2+125*A2+1500”（此处假设 Q = 1），其余的 Y 栏数据，采用拖曳复制的方式复制粘贴公式。当 X 栏有值时，Y 栏就有对应的值。 7 2. 选中 X 栏和 Y 栏的数据，点击菜单栏的【插入】然后插入【散点图】，得到如下图表： 由表和图可知，当 x 在 4 附近时，y 取得最大值。将 x 的取值区间缩小到3.5 , 4.5 , 再绘出一次散点图，如下： 由上述表和图可知，当 x = 4.2 时，y 取得最

13、大值。 回答问题：大约 420 美元折扣可以使利润最高。 （ （2 ）对你所得的结果，求关于所做的 15% 假设的灵敏性。分别考虑折扣量和相应收益。 设销售额提高百分比为 r 1. 折扣量 100x 关于销售额提高百分比 r 的灵敏性（故考虑 x 关于 r 的灵敏性即可） a. 粗分析 前面已假定 r =15% ，现在假设 r 的实际值是不同的，对几个不同的 r 值，重复前面的求解过程, 可以得到对问题的解 x 关于 r 的敏感程度的一些数据。 即给定 r， 对 y = f ( x ) = (1 + r x) * (1500 - 100x)（此处假设 Q = 1）求导，得到 f ( x ) =

14、 -200rx + 1500r - 100，令 f ( x )= 0,可得相应 x = (15r - 1)/2r , 故折扣量 100x = 50(15r - 1)/r ,采用 8 类似第（1）问的 Excel 建模方法，绘出折扣量 100x 关于销售额提高百分比 r 的散点图。 由上述图表可看到折扣量 100x 对参数 r 是很敏感的。即如果给定不同的销售额提高百分比r，则折扣量 100x 将会有明显变化。因此，r 的取值要合适、合理，所做的分析才有意义。 b. 折扣量 100x 对销售额提高百分比 r 灵敏性的系统分析 前面已计算出，使 f(x)=0 的点为 x = (15r - 1)/2

15、r，若要 x0,只要 r = 0.067 , 最佳折扣量100x可由x = (15r - 1)/2r即100x = 50(15r - 1)/r给出,对 r 0.067 ,在0,+)上都有f(x) c. 折扣量 100x 对 r 的灵敏性的相对改变量： 由 x = (15r - 1)/2r 可得在点 r=0.15 处，dx/dr = 1/(2 r2) S(100x , r) = S(x , r) = (dx/dr) * (r/x) = 1/(2rx) = 0.8 即若销售额提高百分比 r 增加 1%，则导致折扣量 100x 增加 0.8% 2. 收益（即利润）L 关于销售额提高百分比 r 的灵敏

16、性 a. 粗分析 L = q * (p C) = Q * (1 + rx) * (p C) = Q * (1 + rx)*(1500 - 100x) 不妨设 Q = 1，由前面分析可得，折扣量 100x 对销售额提高百分比 r 是很敏感的，且此处分析的利润应该是给定 r 的情况下的最大利润，故将 x = (15r - 1)/2r 代入式子 L = (1 + rx)*(1500 - 100x) 得 L = 25(15r+1)2 / r= 25(225r + 1/r + 30) 。 采用类似前面的 Excel 建模方法，绘出利润 L 关于销售额提高百分比 r 的散点图。 9 由上述图表可看到利润

17、L 对参数 r 是很敏感的。即如果给定不同的销售额提高百分比 r，则利润 L 将会有明显变化。因此，r 的取值要合适、合理，所做的分析才有意义。 b. 利润 L 对销售额提高百分比 r 灵敏性的系统分析 对 L 求导可得 L(r) = 25(225 1/r2)，使 L(r) =0 的点为 r = 1/150.067，当 r = 0.067 时，L 随着 r 的增大而增大，r=0.067 是极小值点。 c. 利润 L 对 r 的灵敏性的相对改变量： 由 L = 25(225r + 1/r + 30)可得在点 r=0.15 处，dL/dr = 25(225 1/r2) 4513.89 S(L ,

18、r) = (dL/dr) * (r/L) = (225r 1/r)/(225r + 1/r + 30) 0.385 即若销售额提高百分比 r 增加 1%，则导致利润 L 增加 0.385% （ （3 ）假设实际每 100 美元的折扣仅可以使销售额提高 10% ，对结果会有什么影响？如果每 100为 美元折扣的提高量为 10%15% 之间的某个值，结果又如何？ 假设实际每 100 美元的折扣仅可以使销售额提高 10%，当 r = 0.1 时， 折扣量 100x = 50(15r - 1)/r = 250， 利润 L= Q * (1 + 0.1x)*(1500 - 100x) = 1562.5Q

19、(Q 为常量) 答：会使折扣量变为 250 美元，利润变为 1562.5Q (Q 为没有折扣时的销量) 如果每 100 美元折扣的提高量为 10%15%之间的某个值， 折扣量 100x 的变化曲线如下图所示： 100x = 50(15r - 1)/r 10 利润 L（假设 Q = 1，仅考虑变化趋势）的变化曲线如下图所示：L = 25(225r + 1/r + 30) （ （4 ）什么情况下折扣 会导致利润降低？ 利润 L = y = f ( x ) = Q * ( -15 x2 + 125x + 1500) 利润 L（假设 Q = 1）随 x 变化的变化曲线如下图所示： 由第（1）问所求可得

20、，极大值点为 x = 4.17（折扣量 100x = 417 美元）， 当折扣量 100x 417 美元时，随着折扣量的增加，利润降低。 由上图还可知，当 x 取8 , 8.5区间上的某个值时，利润恰好等于 1500 美元。所以对 x 的取值再进行细分，绘出散点图如下： 由图可知，当 x 8.33 时，即当折扣量 833 美元时，此时利润小于没有折扣时的利润。 11 第三部分 结果与讨论 （可加页） 一、 实验结果分析（包括数据处理、实验现象分析、影响因素讨论、综合分析和结论等） 1 、应用 Excel 建模分析 （1）问题 1：已分配席位数和席位号服从等差数列，重复输入浪费时间。 解决方法：

21、使用 Excel 的自动填充功能 以已分配席位数的输入为例，具体操作如下： a. 在准备填充的第一个单元格输入原本应输入的值，此处输入 1，然后保持鼠标停留在该单元格； b. 然后在菜单栏找到【开始】，点开后找到【填充】并点击； c. 点击【填充】后选择【序列】，然后进行参数设置。此处应选择【列】和【等差数列】，【步长值】输入等差数列公差值，【终止值】为等差数列最后一个数的值。操作如下图： d. 使用自动填充之后可以得到结果如下： 12 （2）问题 2：本实验的实验任务是利用 Q 值法分配席位，并且在 Excel 中进行 Q 值计算。 我认为如果在 Excel 中仅仅只进行 Q 值计算，是无法

22、准确地确定 Q 值计算次数的终止点，容易产生一些不必要的计算。 解决方法： 我将表格的内容分为三部分： a. 已知的每个系的人数和所求的每个系最终分得席位数；（有助于更直观地了解已知条件和最终结论；同时 Q 值计算公式中我使用了 B2、C2、D2 单元格，如果三个系的人数发生变化，则只需要修改此处的数据即可，不必修改公式） b. 在不同的已分配席位数的情况下，三个系 Q 值的取值； c. 席位分配过程：给席位编号，标注出每个席位的分配结果；（有助于更直观地了解 Q 值法分配的原理；便于最后计算各系的最终分得席位数） 此种分法便于确定 Q 值计算次数的终止点。具体方法是： 每进行一次 Q 值计算

23、，则分配一次席位，分配结果直接写在表格中相应位置，更加直观。当所有席位分配完毕，则是 Q 值计算的终止点，此时在表格中回顾席位分配过程并计数即可得到各系最终分得的席位数。 13 2 、单变量最优化 （1）问题 1：绘制散点图之前，要先在表格中输入自变量的值，该数据服从等差数列。 解决方法：使用 Excel 的自动填充功能 具体操作：类似【用 应用 Excel 建模分析】中的问题 1 的操作步骤。 （2）问题 2：绘制散点图之前因变量的计算公式处理方法 解决方法：使用拖曳复制再粘贴的方法。 以第（1）问的第一个散点图为例，具体操作如下： a. 打开 Excel 新建一个表格，分别列出 X 栏和

24、Y 栏。 b. X 栏采用 Excel 的自动填充功能，依次写入 0，1，2，3 ? 等等，Y 栏第一项，根据公式，将 x 以 A2 替代，手写输入公式“= -15*A2*A2+125*A2+1500”（此处假设 Q = 1）， c. 其余的 Y 栏数据，采用拖曳复制的方式复制粘贴公式。首先选中 Y 栏第一项，点击鼠标右键，点击【复制】；然后选中待填入数据的所有 Y 栏单元格，点击鼠标右键，点击【粘贴选项】中的【公式】；则当 X 栏有值时，Y 栏就有对应的值。 14 d. 绘散点图：全部选中 X 栏和 Y 栏的数据，点击菜单栏的【插入】然后插入【散点图】，得到如下图表： （3）问题 3：使用

25、Excel 求函数极值点的方法 解决方法：除了用公式法和导数求解之外，使用 Excel 采用多次绘散点图的方法也可求出函数极值点。 以第（1）问为例，具体操作如下： 采用前面的问题（2）中的方法，得到第一个散点图如下： 由表和图可知，当 x 在 4 附近时，y 取得最大值。 故将 x 的取值区间缩小到3.5 , 4.5 , 再绘出一次散点图，如下： 15 由上述表和图可知，当 x 4.2 时，y 取得最大值。而导数计算结果为 x4.17，可知绘散点图求函数极值点是可行的。 如果想得到更精确的结果，可以将 x 的取值区间继续缩小，每个值之间的差也不断缩小，直至更加接近于真正的极值点。 二、 小结、建议及体会 此次实验涉及到的知识点包括数据分析的理论、最优化模型的建模方法、应用 Excel 的方法等，我按照实验任务的要求，查阅相关资料，制定出理论结合实际的实验方案，采用“从整体规划，分步骤实施，实验全面总结”的技术路线完成了实验。 此次试验，巩固了我在课堂所学的内容，加深了我对数据分析与建模的理论与方法的了解，帮助我基本掌握了典型的数据模型的建立与使用，提升了我的实验动手能力。 此次实验我主要面临的问