excel与数学建模复习进程.ppt

1,数学建模培训Excel的应用技巧,2,Excel基本知识用Excel进行相关与回归分析用Excel进行线性规划,3,Excel基本知识,手动输入数据自动输入数据公式的使用函数的引用,4,手动输入数据,文本输入默认方式为“左对齐”.由数字组成的字符串前加一个号.数值输入默认“右对齐”方式.输入分数时，要先输入“0”和空格，否则作为日期型数据处理。,5,自动输入数据,自动填充鼠标拖动填充柄向下、右拖数字增量为1，向上、左拖增量为-1.自定义序列工具选项新序列输入序列内容，每个序列间按回车键添加选中序列区域工具选项导入确定,6,公式的使用,用公式计算所有的公式运算均以“=”开头。错误的公式以#开头。（1）数学运算符：+、-、*、/、%（计算准则：先乘方，再乘、除，后加、减）（2）文本连接符：&将两段用双引号（英文状态下）括起的文本连接为一段连续的文本。（3）比较运算符：、=、（用于比较两个数据的大小，比较结果是一个逻辑值，即TRUE（真）或FALSE（假）。（4）引用运算符：冒号、空格、逗号冒号：用于定义一个单元格区域，以便在公式中使用。例：=Sum(A3：A7)A3至A7的内容之和空格：交集运算符，表示只处理几个单元格区域之间互相重叠部分。例：=Sum（A3：A5A4：A7）A4+A5的结果逗号：并集运算符，用于连接两个或多个单元格区域。例：=Sum(A3:A5,A4:A7)A3、A4、A5及A4、A5、A6、A7之和(单元格有可能重复)。（5）运算符优先级（）%*、/+、-、&比较运算符。（若优先级相同，则按从左到右的顺序计算）,7,函数的引用,手工输入：在编辑栏或单元格内输入引用单元格地址:,8,相对引用利用单元格间的相对位置关系引用单元格内容。公式中的相对引用随单元格的移动而修改，但原来的位置不变。例：打开文件“数据编辑”，在工作表“电视机的销售统计表”的G3中输入“=E3*F3，则可在G4、G5、G6、G7、G8中填入相应公式来计算商品的销售金额。绝对引用指引用单元格和被引用单元格位置关系是固定的，公式中的引用不随单元格地址变化而变化。通常在地址前加“$”表示绝对引用。例：打开文件“数据编辑”，计算其金额与利润值。计算金额：在G3中输入：=E3*F3，并将之复制至G8。计算利润：在I3中输入：=G3-（G3*$G$1+H3+E3），然后复制到I8混合引用即采用行为相对地址、列为绝对地址或列为相对地址、行为绝对地址来表示地址。,DBXYD-TF477-46YM4-W74MH-6YDQ8,9,用Excel计算描述统计量用Excel进行相关分析用Excel进行回归分析,用Excel进行相关与回归分析,1、用Excel计算描述统计量,几种常用的统计量计算,2、用Excel进行相关分析,相关性分析：判断两组数据集（可以使用不同的度量单位）之间的关系。相关系数（R）：相关性计算的返回值。用来确定两个区域中数据的变化是否相关，以及相关的程度。是两组数据集的协方差除以它们标准偏差的乘积。1、R0：一个集合的较大数据与另一个集合的较大数据相对应（正相关）2、R=0.8:非常高度相关,12,CORREL函数：CORREL(array1,array2)返回单元格区域array1和array2之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。Array1第一组数值单元格区域。Array2第二组数值单元格区域。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略，但是包含零值的单元格将计算在内。如果array1和array2的数据点的数目不同，函数CORREL返回错误值#N/A。,13,利用统计数据计算广告费与销售额之间的相关系数。,相关系数CORREL(B4:B15,C4:C15)=0.92251818,3、用Excel进行回归分析,回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。回归方程:表达参数与结果之间相互关系的数学方程式(数学模型)线性回归:如果变量与结果之间具有线性关系,我们可以用线性方程式来描述它们之间的关系,这种回归方法叫线性回归.非线性回归:如果变量与结果之间不具有线性关系,我们必须用非线性方程式来描述它们之间的关系(如指数关系,对数关系等等),这种回归方法叫非线性回归.单回归:当一个结果只与一个参数存在联系时，进行的回归分析称为单回归。复回归:当一个结果与多个参数存在联系时，进行的回归分析称为复回归。判定系数(R2):用来确定回归方程式的可解释性，即吻合程度。范围在0-1之间，越接近1，解释性越强，即吻合程度越高。回归方法：1、给图表增加趋势线；2、使用Excel提供的“数据分析工具”；3、利用回归函数,15,例4(线性回归)、我们收集了某厂家同一车型中旧车的车龄及其售价数据，求车龄对售价的回归方程,并计算车龄为6.5年的旧车售价是多少.,6.5年的旧车车价=-4.8091*6.5+57.8=26.54,1、给图表增加趋势线进行回归分析,2、使用Excel提供的“数据分析工具”进行回归分析,回归分析示例,17,Excel回归分析工具的输出结果包括3个部分：(1)回归统计表回归统计表包括以下几部分内容：MultipleR（复相关系数R）：是R2的平方根，又称为相关系数，用来衡量变量x和y之间相关程度的大小。本例中R为0.6313，表示二者之间的关系是正相关。RSquare（复测定系数R2）：用来说明自变量解释因变量变差的程度，以测定因变量y的拟合效果。,回归分析工具的输出解释,18,AdjustedRSquare（调整复测定系数R2）：仅用于多元回归才有意义，它用于衡量加入独立变量后模型的拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用于比较含有同一个因变量的各种模型。标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其他统计量，此值越小，说明拟合程度越好。观测值：用于估计回归方程的数据的观测值个数。,回归分析工具的输出解释,应用EXCEL求解线性规划模型,线性规划模型的描述,例：某工厂生产两种新产品：门和窗。经测算，每生产一扇门需要在车间1加工1小时、在车间3加工3小时；每生产一扇窗需要在车间2和车间3各加工2小时。而车间1每周可用于生产这两种新产品的时间为4小时、车间2为12小时、车间3为18小时。已知每扇门的利润为300元，每扇窗的利润为500元。根据市场调查得到的这两种新产品的市场需求状况可以确定，按当前的定价可确保所有的新产品均能销售出去。问：该工厂如何安排这两种新产品的生产计划，才能使总利润最大？,线性规划模型的描述,数据表格：,线性规划模型的建立,假设：每周各生产门和窗x1、x2个。建立线性规划模型如下：MaxZ=300 x1+500 x2x142x2123x1+2x218x1、x20,EXCEL求解线性规划模型,一、在EXCEL电子表格中建立线性规划模型1、把相关数据输入到EXCEL电子表格中,EXCEL求解线性规划模型,2、主要求解结果两种新产品每周的产量；两种新产品每周各实际使用的工时（不能超过计划工时）；两种新产品的总利润,EXCEL求解线性规划模型,3、主要结果的计算方法（1）两种新产品的每周产量：C12、D12，试验解为0。（2）实际使用工时计算（三种方法）1）分别在E7、E8、E9中输入相应的计算公式：E7：C7*C12+D7*D12E8：C8*C12+D8*D12E9：C9*C12+D9*D12,EXCEL求解线性规划模型,2）复制、粘贴方法：在E7中输入：C7*$C$12+D7*$D$12复制E7单元格到E8、E93）公式法：在E7中输入：=SUMPRODUCT（C7：D7，$C$12：$D$12）复制E7单元格到E8、E9,EXCEL求解线性规划模型,（3）总利润计算：在G12单元格输入公式：=C4*C12+D4*D12或：=SUMPRODUCT（C4：D4，C12：D12）,EXCEL求解线性规划模型,收集问题数据；在电子表格中输入数据（数据单元格）；确定决策变量单元格（可变单元格）；输入约束条件左边的公式（输出单元格）使用SUMPRODUCT函数简化输入；输入目标函数公式（目标单元格）。使用SUMPRODUCT函数简化输入。,在电子表格中建立线性规划模型步骤总结,EXCEL求解线性规划模型,二、在EXCEL电子表格中求解线性规划模型1、求解参数设置：“工具”规划求解“，弹出“规划求解参数”对话框，设置求解相关参数。,EXCEL求解线性规划模型,2、约束的设置：单击“添加”，弹出“添加约束”，添加约束条件。,EXCEL求解线性规划模型,3、求解选项设置：单击“选项”，弹出“