《数据分析与可视化实践（第三版）》数据分析基础

数据分析基础

学习目标掌握数据分析工具加载方法。掌握单变量数据表和双变量数据表两种模拟运算表。掌握通过结果来确定相应的输入值的单变量求解运算。学会运用方案管理器对于含有多组不同的参数值给出多种不同解决方案，从中提供最佳解决方案。利用规划求解解决产品比例、人员调度、优化路线、调配材料等方面问题。掌握利用分析工具库中数据分析工具针对工程分析、数理统计、经济计量等数据进行分析和预测。2．1模拟分析2．1．1模拟运算表

主要用于研究当其中一个或两个参数变化时，由此连带的中间变量和最终结果变化情况。单变量数据表双变量数据表例2-1：某人计划在今后5年中每月存入1000元，存款年利率为1.55%，请使用单变量模拟运算表方法计算1-5年各年末的存款额，其计算结果如图所示。2．1模拟分析2．1．1模拟运算表例2-1解题步骤：打开素材文件，建立表格，在A4单元格中输入“=FV(D2/12,E2*12,B2)”，其中FV是Excel自带的投资函数，可以基于固定利率及等额分期付款方式，计算某项投资的未来收益。第1个参数“D2/12”表示每月的存款利率；第2个参数“E2*12”是存款的总期数，其中E2单元格内容为空，其值暂时未定，相当于变量，后面将使用模拟运算法方法将B3:F3区域的年份数据替换；第3个参数“B2”为每月的存款金额，以负值表示。

提示：每月存入1000元，1年后的存款额为：

1000+1000*（1+1.55/100/12）+1000*（1+1.55/100/12）2+……+1000*（1+1.55/100/12）12-1=12085.622．1模拟分析2．1．1模拟运算表例2-1解题步骤：选择包括公式和用于替换输入单元格的区域A3:F5，即模拟运算表。单击“数据”选项卡中“预测”组中“模拟分析”下拉列表中的“模拟运算表”命令，弹出如图所示的“模拟运算表”对话框，将光标放至“输入引用行的单元格”文本框中，选择E2单元格，此时在此文本框中显示“$E$2”，如图2-1-2所示。单击“确定”按钮，计算出1-5年年末的存款额。2．1模拟分析2．1．1模拟运算表

例2-2：某人现需要商业贷款200万买房，已知贷款的基础利率4.9%，请用模拟运算表计算贷款利率分别为基础利率0.85、0.9、0.95、1、1.1、1.2折，贷款年限分别为10、15、20、25、30年时每月的还款额，其结果如图所示。2．1模拟分析2．1．1模拟运算表

例2-2解题步骤：打开素材文件，建立表格，在B4单元格中输入“=PMT(F2/12,G2*12,B2)”，其中PMT是Excel自带的投资函数，可以基于固定利率和等额分期付款方式，计算投资或贷款的每期付款额。第1个参数“F2/12”表示每月的贷款利率第2个参数“G2*12”是贷款的总期数第3个参数“B2”为每月的还款金额，以负值表示。设置F2为贷款利率输入单元格，G2为年限变量输入单元格，单元格内容为空，其值暂时未定，相当于变量，计算时将B5:B10列区域的数据替换利率变量，C4:G4行区域的数据替换年限变量。2．1模拟分析2．1．1模拟运算表

例2-2解题步骤：选择包括公式和用于替换的“输入单元格”的区域B4:G10，即模拟运算表。单击“数据”选项卡中“预测”组中“模拟分析”下拉列表中的“模拟运算表”命令，弹出如图所示的“模拟运算表”对话框，输入定义的“输入单元格”。单击“确定”按钮，计算出每月应还的贷款额。2．1模拟分析2．1．2单变量求解单变量求解就是求解具有一个变量的方程，通过调整可变单元格中的数值，使其按照给定的公式满足设定的目标值。

例2-3：某人每月还款能力为20000元，现计划向银行申请按基准利率4.9%贷款15年，请运用单变量求解方法计算最多可贷款的金额，其结果如图所示。2．1模拟分析2．1．2单变量求解例2-3解题步骤：打开素材文件，在B3单元格中输入“=PMT(B2/12,B4*12,B5)”，可贷款金额B5即为所求的解。单击“数据”选项卡中“预测”组中“模拟分析”下拉列表中的“单变量求解”命令，弹出的“单变量求解”对话框，在“目标值”单元格中输入“20000”，单击“确定”按钮，即可计算出可贷款的额度。2．1模拟分析2．1．2单变量求解Excel单变量求解是通过迭代计算来实现的，即不断修改可变单元格中的值，直到求得的解是目标单元格中的目标值。当无法完全匹配时，可通过指定精度或者迭代次数求得近似解。默认情况，Excel执行100次迭代求解，当与目标值的相差在0.001时停止计算，也可通过“文件/选项/公式”中“计算选项”设置“最多迭代次数”和“最大误差”。2．1模拟分析2．1．3方案管理器“方案管理器”用于一些复杂的、涉及的影响因素较多的决策问题。

对于同一解题方案的模型，可以创建多组不同的参数值，得出多种不同解决方案，从中提供最佳解决方案。

例2-4：如图所示为一个投资收益与风险统计表，其中利润为投资金额*投资利润率，并且对应了不同的风险等级，请提供三种风险等级方案报告，供投资者参考。2．1模拟分析2．1．3方案管理器例2-4解题步骤：打开素材文件，建立如图2-1-3所示表格。单击“数据”选项卡中“预测”组中“模拟分析”下拉列表中的“方案管理器”命令，弹出的“方案管理器”对话框，分别添加不同的方案，在可变单元格中指定变量选取的范围如B2:B4，然后单击“确定”按钮。2．1模拟分析2．1．3方案管理器例2-4解题步骤：在弹出的“方案变量值”对话框中，填入相应的变量参数。在本例中为了生成的方案摘要简洁，每次添加的方案都选取同样的可变单元格范围，但根据不同方案修改其中的参数，如1000000；15%；3，然后单击“确定”按钮，生成方案，如图所示。2．1模拟分析2．1．3方案管理器例2-4解题步骤：再次打开“方案管理器”对话框，单击“摘要”按钮，弹出的“方案摘要”对话框中有“方案摘要”和“方案透视表”两个选项，选择“方案摘要”，生成的方案摘要如图所示。2．2规划求解2．2．1数据分析工具加载加载方法是单击“文件”选项卡中的“选项”命令，在弹出的“Excel选项”对话框中选择“加载项”，在“管理”框中单击“Excel加载项”右侧的“转到”按钮，在弹出的“加载宏”对话框中勾选“分析工具库”、“规划求解加载项”，单击“确定”按钮后，在“数据”选项卡右侧出现“分析”命令组，包含“数据分析”和“规划求解”两个工具。2．2规划求解2．2．2规划求解问题Excel规划求解问题主要由可变单元格、目标函数、约束条件3部分组成，其中可变单元格中存放的变量，通过规划求解来满足约束条件的限制，达到求解目标函数的目的。2．2规划求解2．2．2规划求解问题例2-5：某企业生产两种饮料甲和乙，需要两种配料A和B，每生产饮料甲一瓶需要配料A0.2千克、B0.3千克，每生产饮料乙一瓶需要配料A0.3千克、B0.5千克，企业现存储的A和B配料均为150千克，且配料A的价格为2元/千克，B的价格为3元/千克，现市场需求饮料B是饮料A的两倍，运用规划求解方法计算企业为实现最大利润应安排饮料A和B的产量。2．2规划求解2．2．2规划求解问题

例2-5解题步骤：①依题意设计一个表格，两个变量单元格，有三个约束条件，一个最优目标。在约束条件单元格中分别输入公式“=E3*B3+E4*C3”、“=F3*B3+F4*C3”、“=2*B3-C3”，在最优目标单元格中输入公式“=G3*B3+G4*C3-B3*(E3*H3+F3*I3)-C3*(E4*H3+F4*I3)”。2．2规划求解2．2．2规划求解问题

例2-5解题步骤：②单击“数据”选项卡右侧的“规划求解”工具，在弹出的“规划求解参数”对话框中，在“设置目标”文本框中选择“B7”单元格，“通过更改可变单元格”文本框中拖选“B3:C3”单元格区域。单击“添加”按钮，分别设置单元格B3、C3为“int”，单元格B4<=E5，B5<=F5，单元格B6<=0，添加完成后单击“确定”按扭，返回“规划求解参数”对话框中，“选择求解方法”下拉列表中选择“单纯线性规划”。2．2规划求解2．2．2规划求解问题

例2-5解题步骤：③单击“求解”按钮，在弹出的“规划求解结果”对话框中单击“确定”按钮，计算出最优方案为应生产饮料A115瓶，饮料B231瓶。④在弹出的“规划求解结果”对话框中选择“运算结果报告”，再单击“确定”按钮，即可生成运算结果报告。2．3数据分析工具库2．3．1预测分析预测分析是通过对过去和现在的数据去分析未来的趋势，其中较常用的方法之一就是移动平均法。“移动平均”分析工具可以基于过去几个时期中变量的平均值，设计预测期间的值，使用此工具多用来预测销售量、库存或其他趋势变化。2．3数据分析工具库2．3．1预测分析例2-6：2017年居民消费价格月度涨跌幅度如表所示，利用移动平均法预测涨跌幅度并以图表形式输出。2．3数据分析工具库2．3．1预测分析例2-6解题步骤：①在工作表的一列上输入各时间点上的观察值，如图中的A列所示月份。②选择“数据”选项卡中“分析/数据分析”命令，在弹出的对话框中选择“移动平均”，单击“确定”按扭。2．3数据分析工具库2．3．1预测分析例2-6解题步骤：③在弹出的“移动平均”对话框输入区域中确定数据来源；移动平均数值的间隔可以设定或者采用默认；然后选定输出区域；勾选图表输出和标准误差，如图所示，单击“确定”按扭。2．3数据分析工具库2．3．2相关性分析Excel的分析工具库提供了“相关系数”和“协方差”两个分析工具，运用它们进行相关分析非常简单。例2-7：2012~2017年居民消费价格月度涨跌幅度如图所示，利用相关系数工具计算涨跌幅度同比和环比之间的相关性。2．3数据分析工具库2．3．2相关性分析例2-7解题步骤：①选择“数据”选项卡中“分析/数据分析”命令，在弹出的对话框中选择“相关系数”，单击“确定”按扭。②在弹出的“相关系数”对话框中分组方式分别选择逐行和逐列，设置输入和输出区域，单击“确定”按扭。2．3数据分析工具库2．3．3回归分析回归分析就是运用统计学的理论和方法研究两个或多个变量之间存在的关系，最终根据变量的观测值建立表达变量之间关系的曲线方程，也就是所谓的曲线拟合问题。其中所关注的变量称因变量，而影响因变量变化的那些变量称为自变量。根据自变量的个数，可以把回归分析分为简单回归（一元回归）和多元回归，两者分析的原理相似。按变量之间关系的形式，回归分析可分为线性回归和非线性回归。Excel中线性回归分析是通过对一组观察值使用“最小二乘法”进行直线拟合，该回归分析可同时解决一元回归与多元回归问题。2．3数据分析工具库2．3．3