excel与数学建模

1数学建模培训——Excel的应用技巧2•Excel基本知识•用Excel进行相关与回归分析•用Excel进行线性规划3Excel基本知识•手动输入数据•自动输入数据•公式的使用•函数的引用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-6YDQ89•用Excel计算描述统计量•用Excel进行相关分析•用Excel进行回归分析用Excel进行相关与回归分析1、用Excel计算描述统计量•几种常用的统计量计算几何平均数=average(区域)中位数=median(区域)众数=mode(区域)调和平均值=harmean(区域)几何平均值=geomean(区域)估计样本标准差=stdev(区域)总体样本标准差=stdevp(区域)2、用Excel进行相关分析相关性分析：判断两组数据集（可以使用不同的度量单位）之间的关系。相关系数（R）：相关性计算的返回值。用来确定两个区域中数据的变化是否相关，以及相关的程度。是两组数据集的协方差除以它们标准偏差的乘积。1、R0：一个集合的较大数据与另一个集合的较大数据相对应（正相关）2、R0：一个集合的较大数据与另一个集合的较小数据相对应（负相关）3、R=0：两个集合中的数据互不相关。|R|0.4：低度相关；0.4=|R|0.6:中度相关；0.6=|R|0.8:高度相关；|R|=0.8:非常高度相关12CORREL函数：CORREL(array1,array2)返回单元格区域array1和array2之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。Array1第一组数值单元格区域。Array2第二组数值单元格区域。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略，但是包含零值的单元格将计算在内。如果array1和array2的数据点的数目不同，函数CORREL返回错误值#N/A。13利用统计数据计算广告费与销售额之间的相关系数。相关系数CORREL(B4:B15,C4:C15)=0.922518183、用Excel进行回归分析回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。回归方程:表达参数与结果之间相互关系的数学方程式(数学模型)线性回归:如果变量与结果之间具有线性关系,我们可以用线性方程式来描述它们之间的关系,这种回归方法叫线性回归.非线性回归:如果变量与结果之间不具有线性关系,我们必须用非线性方程式来描述它们之间的关系(如指数关系,对数关系等等),这种回归方法叫非线性回归.单回归:当一个结果只与一个参数存在联系时，进行的回归分析称为单回归。复回归:当一个结果与多个参数存在联系时，进行的回归分析称为复回归。判定系数(R2):用来确定回归方程式的可解释性，即吻合程度。范围在0-1之间，越接近1，解释性越强，即吻合程度越高。回归方法：1、给图表增加趋势线；2、使用Excel提供的“数据分析工具”；3、利用回归函数15例4(线性回归)、我们收集了某厂家同一车型中旧车的车龄及其售价数据，求车龄对售价的回归方程,并计算车龄为6.5年的旧车售价是多少.车龄价格(万)156.0248.5342.0437.6532.5628.7722.2818.5915.01012.5旧车车龄与售价关系图y=-4.8091x+57.8R2=0.98650.010.020.030.040.050.060.0024681012车龄售价6.5年的旧车车价==-4.8091*6.5+57.8=26.541、给图表增加趋势线进行回归分析2、使用Excel提供的“数据分析工具”进行回归分析回归分析示例17Excel回归分析工具的输出结果包括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元。根据市场调查得到的这两种新产品的市场需求状况可以确定，按当前的定价可确保所有的新产品均能销售出去。问：该工厂如何安排这两种新产品的生产计划，才能使总利润最大？线性规划模型的描述•数据表格：车间单位产品的生产时间（小时）每周可获得的生产时间（小时）门窗11042021233218单位利润（元）300500线性规划模型的建立•假设：每周各生产门和窗x1、x2个。建立线性规划模型如下：MaxZ=300x1+500x2x1≤42x2≤123x1+2x2≤18x1、x2≥0EXCEL求解线性规划模型一、在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*D12EXCEL求解线性规划模型2）复制、粘贴方法：在E7中输入：C7*$C$12+D7*$D$12复制E7单元格到E8、E93）公式法：在E7中输入：=SUMPRODUCT（C7：D7，$C$12：$D$12）复制E7单元格到E8、E9EXCEL求解线性规划模型（3）总利润计算：在G12单元格输入公式：=C4*C12+D4*D12或：=SUMPRODUCT（C4：D4，C12：D12）EXCEL求解线性规划模型•收集问题数据；•在电子表格中输入数据（数据单元格）；•确定决策变量单元格（可变单元格）；•输入约束条件左边的公式（输出单元格）使用SUMPRODUCT函数简化输入；•输入目标函数公式（目标单元格）。使用SUMPRODUCT函数简化输入。在电子表格中建立线性规划模型步骤总结EXCEL求解线性规划模型二、在EXCEL电子表格中求解线性规划模型1、求解参数设置：“工具”——”规划求解“，弹出“规划求解参数”对话框，设置求解相关参数。EXCEL求解线性规划模型2、约束的设置：单击“添加”，弹出“添加约束”，添加约束条件。EXCEL求解线性规划模型3、求解选项设置：单击“选项”，弹出“规划求解选项”对话框。选择“采用线性模型”和“假定非负”。EXCEL求解线性规划模型4、求解及结果单击“求解”，开始规划求解。弹出“规划求解结果”对话框。选择“保存规划求解结果”。EXCEL求解线性规划模型5、电子表格显示结果：单击“确定”，在电子表格的可变单元格、输出单元格及目标单元格出现求解结果。