授课--Excel数据分析与规划求解(7,8章补充)

1、补充内容：Excel数据分析与规划求解试验设计与数据处理（EDDP）：刘耀驰主讲第一部分第一部分Excel在相关性与回归分析中的应用试验设计与数据处理（EDDP）：刘耀驰主讲n用用Excel计算描述统计量计算描述统计量n用用Excel进行相关分析进行相关分析n用用Excel进行回归分析进行回归分析用用Excel进行相关分析与回归分析进行相关分析与回归分析（一）用（一）用Excel计算描述统计量计算描述统计量n用函数计算描述统计量用函数计算描述统计量n描述统计菜单项的使用描述统计菜单项的使用n常用的描述统计量有众数、中位数、算术平均数、调和平均常用的描述统计量有众数、中位数、算术平均数、调和平均

2、数、几何平均数、极差、四分位差、标准差、方差、标准差数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。下面介绍如何用函数来计算描述统计量。系数等。下面介绍如何用函数来计算描述统计量。一、用函数计算描述统计量一、用函数计算描述统计量用函数计算描述统计量的方法用函数计算描述统计量的方法n手工输入函数名称及参数：这种输入形式比较简单、快这种输入形式比较简单、快捷。但需要非常熟悉函数名称及其参数的输入形式。所捷。但需要非常熟悉函数名称及其参数的输入形式。所以，只有比较简单的函数才用这种方法输入以，只有比较简单的函数才用这种方法输入n函数导入法：最常用，适合于所有函数使用，而且在导最常用，适合

3、于所有函数使用，而且在导入过程中有向导提示，因而非常方便。函数的一般导入入过程中有向导提示，因而非常方便。函数的一般导入过程为：点菜单过程为：点菜单“插入插入”；找；找“函数函数”，此时出现一个，此时出现一个“插入函数插入函数”对话框；在对话框的对话框；在对话框的“选择类别选择类别”中确定中确定函数的类别（如常用函数或统计）；在函数的类别（如常用函数或统计）；在“选择函数选择函数”内内确定欲选的函数名称，如确定欲选的函数名称，如SUM、MODE等；点等；点“确定确定”后即可出现该函数运算的对话框向导，再按向导的提示后即可出现该函数运算的对话框向导，再按向导的提示往下运行即可往下运行即可1众数M

4、ODE 例：为了分析小麦的分蘖情况，进行了例：为了分析小麦的分蘖情况，进行了10株小麦的调查株小麦的调查（1）手工输入函数名称及参数）手工输入函数名称及参数单击任一单元格，输入单击任一单元格，输入“=MODE（B2：B11）”，回车，回车后即可得众数为后即可得众数为14。（2）函数导入法）函数导入法点菜单点菜单“插入插入”；找；找“函数函数”，此时出现一个，此时出现一个“插入函数插入函数”对话框；在对话框的对话框；在对话框的“选择类别选择类别”中确定函数的类别中确定函数的类别“统统计计”；在；在“选择函数选择函数”内确定欲选的函数名称内确定欲选的函数名称“MODE”点点“确定确定”后即可出现该

5、函数运算的对话框向导，在后即可出现该函数运算的对话框向导，在Number1处输入处输入B2：B11或选择或选择Excel中的中的B2：B11区域。按区域。按“确定确定”，在，在Excel中即得到众数中即得到众数14。2中位数单击任一空白单元格，输入单击任一空白单元格，输入“=MEDIAN（B2:B11）”，回车后得中位数为回车后得中位数为14。3算术平均数单击任一空白单元格，输入单击任一空白单元格，输入“=AVERAGE（B2:B11）”，回车后得算术平均数为回车后得算术平均数为14.1。4几何平均数单击任一空白单元格，输入单击任一空白单元格，输入“=GEOMEAN（B2:B11）”，回车后得

6、几何平均数为回车后得几何平均数为14.02。5调和平均数单击任一空白单元格，输入单击任一空白单元格，输入“=HARMEAN（B2:B11）”，回车后得调和平均数为回车后得调和平均数为1.4226截尾平均数将数据按由小到大顺序排列后，因数据两端值不够稳定，将数据按由小到大顺序排列后，因数据两端值不够稳定，按一定比例去掉头尾两端一定数量的观察值，然后再求按一定比例去掉头尾两端一定数量的观察值，然后再求平均，这样得到的平均数就是截尾平均数。如果按平均，这样得到的平均数就是截尾平均数。如果按2/10，即从即从30个数据中去掉最大的一个值和最小的一个值，再个数据中去掉最大的一个值和最小的一个值，再求平均

7、数。求平均数。单击任一空白单元格，输入单击任一空白单元格，输入“=TRIMMEAN（B2:B11，1/10）”，回车后得截尾平均数为，回车后得截尾平均数为14.17全距单击任一空白单元格，输入单击任一空白单元格，输入“=MAX（B2:B11）-MIN（B2:B11）”，回车后得全距为，回车后得全距为58标准差(估计样本）单击任一空白单元格，输入单击任一空白单元格，输入“=STDEV（B2:B11）”，回，回车后得标准差为车后得标准差为1.5249标准差系数单击任一空白单元格，输入单击任一空白单元格，输入“=STDEV（B2:B11）/AVERAGE（B2:B11）”，回车后得标准差系数为，回车

8、后得标准差系数为0.108010偏度系数单击任一空白单元格，输入单击任一空白单元格，输入“=SKEW（B2:B11）”，回车，回车后得偏度系数为后得偏度系数为-0.67811峰度系数单击任一空白单元格，输入单击任一空白单元格，输入“=KURT（B2:B11）”，回车，回车后得峰度系数为后得峰度系数为0.6249常用的统计量函数由于公式执行后显示的是计算结果按由于公式执行后显示的是计算结果按Ctrl+键（位于键（位于键盘左上角），可使公式在显示公式内容与显示公式结键盘左上角），可使公式在显示公式内容与显示公式结果之间切换果之间切换二、描述统计菜单项的使用二、描述统计菜单项的使用仍使用上面的例子，

9、我们已经把数据输入到仍使用上面的例子，我们已经把数据输入到B2:B11单单元格，然后按以下步骤操作元格，然后按以下步骤操作第一步：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框第二步：在输入区域中输入$B$1:$B$11，在输出区域中选择$F$1，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值第三步：单击确定，得输出结果n利用函数计算相关系数利用函数计算相关系数n用相关系数宏计算相关系数用相关系数宏计算相关系数（二）利用（二）利

10、用EXCEL计算相关系数计算相关系数1利用函数计算相关系数利用函数计算相关系数在Excel中，提供了两个计算两个变量之间相关系数的方法，CORREL函数和PERSON函数，这两个函数是等价的，这里我们介绍用CORREL函数计算相关系数第一步：第一步：单击任一个空白单元格，单击“插入”菜单，选择“函数”，打开“插入函数”对话框，在“选择类别”中选择“统计”；在“选择函数”中选择“CORREL”，单击确定后，出现CORREL对话框。第二步：第二步：在array1中输入B3：B10，在array2中输入C3：C10，即可在对话框下方显示出计算结果为0.77723。2用相关系数宏计算相关系数用相关系数

11、宏计算相关系数第一步：第一步：单击“工具”菜单，选择“数据分析”项，在“数据分析”项中选择“相关系数”，弹出“相关系数”对话框 第二步：第二步：在“输入区域”输入$B$2:$C$10，“分组方式”选择逐列，选择“标志位于第一行”，在“输出区域”中输入$I$3，单击“确定”（三）用（三）用Excel进行回归分析进行回归分析lExcel进行回归分析同样分函数和回归分析宏两种形进行回归分析同样分函数和回归分析宏两种形式，提供式，提供9个函数用于建立回归模型和预测：个函数用于建立回归模型和预测：nINTERCEPT返回线性回归模型的返回线性回归模型的截距截距nSLOPE返回线性回归模型的返回线性回归模

12、型的斜率斜率nRSQ返回线性回归模型的返回线性回归模型的判定系数判定系数nFORECAST返回一元线性回归模型的返回一元线性回归模型的预测值预测值nSTEYX计算估计的计算估计的标准误差标准误差nTREND计算计算线性回归线线性回归线的的趋势值趋势值nGROWTH返回返回指数曲线指数曲线的的趋势值趋势值nLINEST返回返回线性回归线性回归模型的模型的参数参数nLOGEST返回返回指数曲线指数曲线模型的模型的参数参数l用函数进行回归分析比较麻烦，这里介绍使用回归分用函数进行回归分析比较麻烦，这里介绍使用回归分析宏进行回归分析析宏进行回归分析第一步：第一步：单击“工具”菜单，选择“数据分析”选项

13、，出现“数据分析”对话框，在分析工具中选择“回归”用回归分析宏进行回归分析用回归分析宏进行回归分析第二步：第二步：单击“确定”按钮，弹出“回归”对话框，在“Y值输入区域”输入$B$1：$B$11；在“X值输入区域”输入$C$1:$C$11，在“输出选项”选择“$E$1” 第三步：第三步：单击确定按钮，得回归分析结果如下图所示Excel回归分析工具的输出结果包括回归分析工具的输出结果包括3个部分：个部分：（1）回归）回归统计表；（统计表；（2）方差分析表；（）方差分析表；（3）回归模型）回归模型回归分析工具的输出解释回归分析工具的输出解释（1）回归统计表：）回归统计表：包括以下几部分内容包括以下

14、几部分内容nMultiple R（复相关系数R）：是R2的平方根，又称为相关系数，衡量x和y之间相关程度的大小。本例中R为0.6313，表示二者之间的关系是正相关nR Square（复测定系数R2）：用来说明自变量解释因变量变差的程度，以测定因变量y的拟合效果nAdjusted R Square（调整复测定系数R2）：用于多元回归，用于衡量加入独立变量后模型的拟合程度。新独立变量加入后，即使它同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用于比较含有同一个因变量的各种模型n标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其他统计量，此值越小，说明拟合程度越好n观测值：用于估计

15、回归方程的数据的观测值个数n（2）方差分析表）方差分析表n方差分析表的主要作用是通过F检验来判断回归模型的回归效果。表中数据说明如下：“回归分析回归分析”行计算的是估计值同均值之差（行计算的是估计值同均值之差（-）的各）的各项指标项指标“残差残差”行是用于计算每个样本观察值与估计值之差行是用于计算每个样本观察值与估计值之差（-）的各项指标）的各项指标“总计总计”行计算每个值同均值之差（行计算每个值同均值之差（-）的各项指标）的各项指标第二列第二列df是自由度是自由度第三列第三列SS是离差的平方和是离差的平方和第四列第四列MS是均方差，它是离差平方和除以自由度是均方差，它是离差平方和除以自由度第

16、五列是第五列是F统计量统计量第六列第六列Significance F是在显著性水平下的是在显著性水平下的F的临界值的临界值 inicmicimETETYY)(1n（3）回归模型）回归模型dbxxy21)(ln()ln()ln(21xdxby基本形式基本形式JENSEN模型（水分生产函数）两边同时取对数转化为线性形式幂函数回归模型幂函数回归模型幂函数回归模型指数回归模型bxcey 基本形式基本形式两边取对数bxcy)ln()ln(对因变量取对数对截距反对数指数回归模型对数回归模型基本形式基本形式自变量取对数多项式回归模型产量与需水量之间的关系：抛物线cbxaxY2u把把x2,x分别看作变量按多元

17、回归计算即可分别看作变量按多元回归计算即可多项式回归模型第二部分第二部分运用运用EXCEL求解线性规划问题求解线性规划问题试验设计与数据处理（EDDP）：刘耀驰主讲outline1.关于关于“规划求解规划求解”2.如何加载如何加载“规划求解规划求解”3. “规划求解规划求解”各参数设置各参数设置4. “规划求解规划求解”步骤步骤5. 利用利用“规划求解规划求解”解线性规划问题解线性规划问题1. 关于关于“规划求解规划求解”Excel“规划求解规划求解” 取自德克萨斯大学奥斯汀分校的取自德克萨斯大学奥斯汀分校的Leon Lasdon和克里夫兰州立大学的和克里夫兰州立大学的Allan Waren共

18、同开发的共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码非线性最优化代码线性和整数规划取自线性和整数规划取自Frontline Systems公司的公司的John Waston和和Dan Fylstra提供的有界变量单纯形法和分支定界法提供的有界变量单纯形法和分支定界法2.如何加载如何加载“规划求解规划求解”1) 在在“工具工具”菜单上，单菜单上，单击击“加载宏加载宏”2) 在弹出的对话框中的在弹出的对话框中的“可用加载宏可用加载宏”列表框中列表框中,选定选定待添加的加载宏待添加的加载宏“规划求解规划求解”选项旁的复选框选项旁的复选框,然后单然后单

19、击击“确定确定”.单击单击“确定确定”后后,“工具工具”菜单下就会出现菜单下就会出现一项一项“规划求解规划求解”3. “规划求解规划求解”各参数设置各参数设置单击单击“规划求解规划求解”按钮会出现参数设置对话框按钮会出现参数设置对话框单击“添加”，显示添加约束对话框显示添加约束对话框选项：显示显示”规划求解选项规划求解选项”对话框对话框.在其中可以加载或保存在其中可以加载或保存规划求解模型，并对规划求解过程的高级属性进行控制规划求解模型，并对规划求解过程的高级属性进行控制4. “规划求解规划求解”步骤步骤 启用启用“规划求解规划求解”宏宏 输入数据输入数据 利用函数利用函数“SUMPRODUC

20、T”引入约束与目标引入约束与目标 对话框对话框“规划求解规划求解”的各要素的各要素.例例1. 雅致家具厂生产计划优化问题雅致家具厂生产计划优化问题雅致家具厂生产雅致家具厂生产4种小型家具，由于该四种家具具有不同种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原的大小、形状、重量和风格，所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不料（木材和玻璃）、制作时间、最大销售量与利润均不相同。该厂每天可提供的木材、玻璃和工人劳动时间分相同。该厂每天可提供的木材、玻璃和工人劳动时间分别为别为600单位、单位、1000单位与单位与400小时，详细的数据资料见小时，详细的数据资料见下表。下表。如何安排这四种家具的日产量，使得该厂的日利润最大？如何安排这四种家具的日产量，使得该厂的日利润最大？表1 雅致家具厂基本数据家 具 类 型 1 2 3 4可提供两劳动时间（小时/件） 2 1 3 2 400