《Excel在数学建模中应用.ppt》由会员分享，可在线阅读，更多相关《Excel在数学建模中应用.ppt（116页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数学建模培训Excel的应用技巧主讲：曹清洁1nExcel基本知识nExcel在数学建模中的高级应用技巧 2Excel基本知识n手动输入数据手动输入数据 n自动输入数据自动输入数据 n公式的使用公式的使用n函数的引用函数的引用n数据管理与分析数据管理与分析 3手动输入数据手动输入数据n文本输入文本输入默认方式为“左对齐”.由数字组成的字符串前加一个号.n数值输入数值输入默认“右对齐”方式.输入分数时，要先输入“0”和空格，否则作为日期型数据处理。4自动输入数据自动输入数据 n自动填充鼠标拖动填充柄向下、右拖数字自动填充鼠标拖动填充柄向下、右拖数字增量为增量为1，向上、左拖增量为，向上、左拖增量
2、为-1.n自定义序列自定义序列 工具选项新序列输入序列内容，每个序列间按回车键添加 选中序列区域工具选项导入确定 56公式的使用公式的使用用公式计算用公式计算 所有的公式运算均以所有的公式运算均以“=”开头开头。错误的公式以#开头。（1）数学运算符：）数学运算符：+、-、*、/、%（计算准则：先乘方，再乘、除，后加、减）（2）文本连接符：）文本连接符：&将两段用双引号（英文状态下）括起的文本连接为一段连续的文本。（3）比较运算符）比较运算符：、=、=、（用于比较两个数据的大小，比较结果是一个逻辑值，即TRUE（真）或FALSE（假）。（4）引用运算符：）引用运算符：冒号、空格、逗号 冒号：冒号
3、：用于定义一个单元格区域，以便在公式中使用。例：=Sum(A3：A7)A3至A7的内容之和 空格：空格：交集运算符，表示只处理几个单元格区域之间互相重叠部分。例：=Sum（A3：A5 A4：A7）A4+A5的结果 逗号：逗号：并集运算符，用于连接两个或多个单元格区域。例：=Sum(A3:A5,A4:A7)A3、A4、A5及A4、A5、A6、A7之和(单元格有可能重复)。（5）运算符优先级）运算符优先级 （）%*、/+、-、&比较运算符。（若优先级相同，则按从左到右的顺序计算）7函数的引用函数的引用n手工输入：手工输入：在编辑栏或单元格内输入n引用单元格地址引用单元格地址:8n相对引用相对引用
4、利用单元格间的相对位置关系引用单元格内容。公式中的相对引用随单元格的移动而修改，但原来的位置不变。例：打开文件“数据编辑”，在工作表“电视机的销售统计表”的G3中输入“=E3*F3，则可在G4、G5、G6、G7、G8中填入相应公式来计算商品的销售金额。n绝对引用绝对引用 指引用单元格和被引用单元格位置关系是固定的，公式中的引用不随单元格地址变化而变化。通常在地址前加“$”表示绝对引用。例：打开文件“数据编辑”，计算其金额与利润值。计算金额：在G3中输入：=E3*F3，并将之复制至G8。计算利润：在I3中输入：=G3-（G3*$G$1+H3+E3），然后复制到I8n混合引用混合引用 即采用行为相
5、对地址、列为绝对地址或列为相对地址、行为绝对地址来表示地址。9Excel的数据管理和分析的数据管理和分析1设计数据清单设计数据清单2记录编辑记录编辑3排序数据排序数据4筛选数据筛选数据5分类汇总数据分类汇总数据101 设计数据清单设计数据清单数据清单的条件工作表中只含有一张数据表数据表应在第1行设置列标题数据表不应含有空行或空列112记录编辑记录编辑n逐条显示n添加记录n删除记录n查询记录123排序数据排序数据n排序依据：主要关键字第二关键字第三关键字n排序选项：自定义排序次序区分大小写排序方向排序方法134筛选数据筛选数据n筛选种类：自动筛选、高级筛选n自动筛选方式：前10个自定义145分类
6、汇总数据分类汇总数据n汇总步骤按汇总依据字段排序指定汇总依据字段设定汇总方式指定汇总字段n汇总显示控制删除汇总删除汇总15数据透视表n功能按多个字段分类汇总n步骤指定数据源选定数据区域决定布局确定位置1617Excel在数学建模中的高级应用技巧uExcel的数据处理功能u用Excel绘制图表u相关与回归分析18一、Excel的数据处理功能1、Excel的函数 函 数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。(1)函数以公式的形式出现，请在函数名称前面键入等号（=）。如：=sumsum(A1,A4:A10,56)19（2）使用函数向导插入函数：按fx即可调入粘贴函数对话框
7、20例1：已知某班级学生成绩，统计其中某科成绩为80分（含）-90分（不含）的人数。例2：计算 例3：计算 的值。例4：求矩阵 的逆矩阵注：鼠标先选中一块4*4区，先按F2键，再同时按下Shift+Ctrl+Enter三个键，则选定区域内出现逆矩阵的计算结果。21例5：利用自定义函数进行计算（1）当x=3,2,1,0,-1,-2,-3时，计算分段函数 的值。（2）用迭代法求非线性方程x-cosx=0的数值解，迭代公式是xk=cos(xk-1)，取x0=1,试用Excel计算，要求精度达到10-12。（3）利用公式 计算 的近似值，使误差小于10-14。222、Excel的数据分析功能 “数据分
8、析”的统计分析工具包，包含方差分析、回归分析、协方差和相关系数、傅里叶分析等分析工具 首次使用时需要进行安装，方法如下：（1）点击“工具”-”加载宏”；（2）在弹出对话框中列出各种可以加载的项目，按照需要选择“分析工具库”、“规划求解”等等项目，点“确定”。安装完后，“工具”菜单中多出了“数据分析”子菜单，点击它，弹出对话框，显示各种数据分析工具。232425数据分析功能：1、描述统计 主要统计数据的平均值、中位数、标准差、方差等等统计量例6：某炼钢厂测了120种炉钢中的Si含量，得到120个原始数据，见Excel.2627282、直方图分析工具分析工具 直方图分析工具可完成数据的分组、频数分
9、布与累积频数的计算、绘制直方图与累积折线图等一系列操作。以例7为操作范例，阐述直方图分析工具的统计整理功能，其操作过程如下。直方图是一大批数据的频率分布图，由直方图可以观察和分析数据的概率分布。见数据分析实例例7：根据抽样调查，某月某市50户居民购买消费品支出资料。（单位：元）（单位：元）（单位：元）（单位：元）2930首先，将样本数据排成一列，最好对数据进行排序，本例中已利用排序操作排好序，为A1：A51。输入分组标志，本例中为B1：B10，分别是899、999、1099、1199、1299、1399、1499、1599、1699（如图所示）然后然后然后然后 ，利用直方图分析工具进行分析，具
10、体操作步骤如下。，利用直方图分析工具进行分析，具体操作步骤如下。，利用直方图分析工具进行分析，具体操作步骤如下。，利用直方图分析工具进行分析，具体操作步骤如下。31 第一步：单击“工具”菜单，选择“数据分析”选项；打开“数据分析”对话框，从“分析工具”列表中选择“直方图”选项，（如图1所示）32第二步：打开“直方图”对话框，确定输入区域、接收区域和输出区域，（如图所示）33n（1）“输入区域”输入待分析数据区域的单元格引用，若输入区域有标志项，则选中“标志”复选框；否则，系统自动生成数据标志。“接收区域”输入接收区域的单元格引用，该框可为空，则系统自动利用输入区域中的最小值和最大值建立平均分布
11、的区间间隔的分组。本例中输入区域为$A$2:$A$51，接收区域为$B$2:$B$10。n（2）在“输出”选项中可选择输出去向，输出去向类似于“抽样”对话框的输出去向。本例中选择“输出区域”为$C$1。n（3）选择“柏拉图”可以在输出表中同时按降序排列频数数据；选择“累积百分率”可在输出表中增加一列累积百分比数值，并绘制一条百分比曲线；选择“图表输出”可生成一个嵌入式直方图。34第三步：单击“确定”按钮，在输出区域单元格可得到频数分布，（如图所示）35第四步：将条形图转换成标准直方图 （如图所示）36二、用Excel绘制图表 图表是数据表现的另一种形式。数据的图表化就是将单元格中的数据以各种统
12、计图的形式显示。1.图表种类图表种类（1）独立的图表：）独立的图表：单独占据一个工作表，打印时也将与数据表分开打印。（2）附属于工作表的嵌入式图表：）附属于工作表的嵌入式图表：它和数据源放置在同一张工作表中，同时打印。这两种表所依据的数据都来自工作表上选中的区域，数据变化，则图表随之变化。（以文件（以文件“图表图表”为例）为例）372图表术语图表术语（1）数据系列：）数据系列：图表中决定图形Y轴取值的数值集合，对应工作表中的数据行。（2）分类：）分类：图表中决定数据系列的X轴的标题值，对应工作表中的数据列。（3）坐标轴）坐标轴：图表的一边。（4）图例：）图例：定义图表的图形的含义。（5）网格线
13、：）网格线：帮助确定数据点在Y轴或X轴刻度上的确切值。38n3创建图表创建图表n（1）利用图表向导分四个步骤创建图表：选中区域图表向导工具n（2）按F11键快速创建图表：选中区域F11394编辑图表编辑图表（1）选中图表元素：）选中图表元素：单击（2）图表移动及改变大小：）图表移动及改变大小：拖动鼠标（3）改变图表图案、颜色、设置刻度等：）改变图表图案、颜色、设置刻度等：双击图表（4）改变图表类型：）改变图表类型：右键单击（5）数据系列编辑）数据系列编辑添加数据系列：添加数据系列：选中区域拖曳到图标；选中图表图表添加数据删除数据系列：删除数据系列：选中系列Delete；选中系列编辑清除系列系列
14、次序的调整：系列次序的调整：选中系列格式数据系列系列次序修改系列颜色：修改系列颜色：选中系列双击（6）修改图表中的数据）修改图表中的数据修改图表中的数据：修改图表中的数据：双击要修改数据的单元格修改数据 修改图表中的图形：修改图表中的图形：选中系列有间断地单击结点拖动鼠标（7）增加图表标题及数据标志）增加图表标题及数据标志双击某一系列 选中图表图表图表选项标题（数据标志）40绘图实例：用Excel绘制任意一元函数的图像以 为例介绍：411、用Excel计算描述统计量2、用Excel进行相关分析3、用Excel进行回归分析三、用Excel进行相关与回归分析42三-1、用Excel计算描述统计量（
15、1）用函数计算描述统计量（2）描述统计菜单项的使用43n常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。下面介绍如何用函数来计算描述统计量。（1）用函数计算描述统计量44n用函数运算有两种方法：一是手工输入函数名称及参数。这种输入形式比较简单、快捷。但需要非常熟悉函数名称及其参数的输入形式。所以，只有比较简单的函数才用这种方法输入；二是函数导入法。这是一种最为常用的办法，它适合于所有函数的使用，而且在导入过程中有向导提示，因而非常方便。函数的一般导入过程为：点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选
16、择类别”中确定函数的类别（如常用函数或统计）；在“选择函数”内确定欲选的函数名称，如SUM、MODE等；点“确定”后即可出现该函数运算的对话框向导，再按向导的提示往下运行即可。45众数 例：为了分析小麦的分蘖情况，进行了10株小麦的调查，如下图所示：46众数（Mode）n众数（Mode）统计学名词，在统计分布上具有明显集中趋势点的数值，代表数据的一般水平（众数可以不存在或多于一个）。修正定义：是一组数据中出现次数最多的数值，叫众数，有时众数在一组数中有好几个。用M表示。理性理解：简单的说，就是一组数据中占比例最多的那个数。47手工输入函数名称及参数单击任一单元格，输入“=MODE（B2：B11
17、）”，回车后即可得众数为14。48函数导入法点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选择类别”中确定函数的类别“统计”；在“选择函数”内确定欲选的函数名称“MODE”，49点“确定”后即可出现该函数运算的对话框向导，在Number1处输入B2：B11或选择Excel中的B2：B11区域。按“确定”，在Excel中即得到众数14。50中位数单击任一空白单元格，输入“=MEDIAN（B2:B11）”，回车后得中位数为14。51中位数（Medians）n中位数（Medians）统计学名词，是指将统计总体当中的各个变量值按大小顺序排列起来，形成一个数列，处于变量数列中间
18、位置的变量值就称为中位数，用Me表示。当变量值的项数N为奇数时，处于中间位置的变量值即为中位数；当N为偶数时，中位数则为处于中间位置的2个变量值的平均数。（注意：中位数和众数不同，众数不一定在中间）52算术平均数单击任一空白单元格，输入“=AVERAGE（B2:B11）”，回车后得算术平均数为14.1。53几何平均数几何平均数单击任一空白单元格，输入“=GEOMEAN（B2:B11）”，回车后得几何平均数为14.02。几何平均数是是指n个观察值连乘积的n次方根。其计算公式为：54调和平均数调和平均数单击任一空白单元格，输入“=HARMEAN（B2:B11）”，回车后得调和平均数为1.422。公
19、式:调和平均数=1/(1/A+1/B+1/C+1/D+）55截尾平均数截尾平均数n将数据按由小到大顺序排列后，因数据两端值不够稳定，按一定比例去掉头尾两端一定数量的观察值，然后再求平均，这样得到的平均数就是截尾平均数。如果按1/10，即从10个数据中去掉最大的一个值和最小的一个值，再求平均数。n单击任一空白单元格，输入“=TRIMMEAN（B2:B11，1/10）”，回车后得截尾平均数为14.156全距全距n单击任一空白单元格，n输入“=MAX（B2:B11）-MIN（B2:B11）”，n回车后得全距为5。57标准差标准差(估计样本）估计样本）单击任一空白单元格，输入“=STDEV（B2:B1
20、1）”，回车后得标准差为1.524。58标准差计算公式59标准差系数标准差系数单击任一空白单元格，输入“=STDEV（B2:B11）/AVERAGE（B2:B11）”，回车后得标准差系数为0108060标准差系数的计算公式 n式中：V为标准差系数；为标准差；x 为平均数。61偏度系数偏度系数单击任一空白单元格，输入“=SKEW（B2:B11）”，回车后得偏度系数为-0.678。62峰度系数峰度系数单击任一空白单元格，输入“=KURT（B2:B11）”，回车后得峰度系数为0.6249。63常用的统计量函数由于公式执行后显示的是计算结果按Ctrl+、键(位于键盘左上角)可使公式在显示公式内容与显示
21、公式结果之间切换 64（2）描述统计菜单项的使用仍使用上面的例子我们已经把数据输入到B2:B11单元格，然后按以下步骤操作：65第一步：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框。66第二步：在输入区域中输入$B$1:$B$11，在输出区域中选择$F$1，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值。67第三步：单击确定，可得输出结果。68上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数
22、；标准偏差指样本标准差，自由度为n-1；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距；可以看出与我们前面用函数计算的结果完全相同。最大值为16，最小值为11，第三个最大值为15，第三个最小值为13。69三三-2.相关性分析相关性分析相关性分析：相关性分析：判断两组数据集（可以使用不同的度量单位）之间的关系。相关系数（相关系数（R）：）：相关性计算的返回值。用来确定两个区域中数据的变化是否相关，以及相关的程度。是两组数据集的协方差除以它们标准偏差的乘积。1、R0：一个集合的较大数据与另一个集合的较大数据相对应（正相关）2、R0：一个集合的较大数据与另一个集合的较小数据相对应（负相关
23、）3、R=0：两个集合中的数据互不相关。|R|0.4 ：低度相关；0.4=|R|0.6:中度相关；0.6=|R|=0.8:非常高度相关701、CORREL函数：函数：CORRELCORREL(array1array1,array2array2)返回单元格区域 array1 和 array2 之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。Array1 第一组数值单元格区域。Array2 第二组数值单元格区域。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略，但是包含零值的单元格将计算在内。如果 array1 和 arra
24、y2 的数据点的数目不同，函数 CORREL 返回错误值#N/A。71例 1、利用统计数据计算广告费与销售额之间的相关系数。相关系数相关系数CORREL(B4:B15,C4:C15)=0.9225181872例例2.2.我们收集了某厂家同一车型中旧车的车龄及其售我们收集了某厂家同一车型中旧车的车龄及其售价数据，求价格与车龄的相关系数价数据，求价格与车龄的相关系数 73例 3、总平均成绩、出勤率、选修学分与每周打工小时数的关系。2、使用数据分析工具求相关矩阵、使用数据分析工具求相关矩阵利用CORREL函数只能返回两种属性之间的相关系数，如果使用Excel提供的“数据分析”工具可以计算多组数据间的
25、相关系数，组成相关系数表。74例 利用“数据分析”工具计算汽车扳金、省油与价格的满意度之间的相关系数。75回归分析回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。回归方程回归方程:表达参数与结果之间相互关系的数学方程式(数学模型)线性回归线性回归:如果变量与结果之间具有线性关系,我们可以用线性方程式来描述它们之间的关系,这种回归方法叫线性回归.非线性回归非线性回归:如果变量与结果之间不具有线性关系,我们必须用非线性方程式来描述它们之间的关系(如指数关系,对数关系等等),这种回归方法叫非线性回归.单回归单回归:当一个结果只与一个参
26、数存在联系时，进行的回归分析称为单回归。复回归复回归:当一个结果与多个参数存在联系时，进行的回归分析称为复回归。判定系数判定系数(R2):(R2):用来确定回归方程式的可解释性，即吻合程度。范围在0-1之间，越接近1，解释性越强，即吻合程度越高。回归方法：回归方法：1、给图表增加趋势线；2、使用Excel提供的“数据分析工具”；3、利用回归函数三三-3.-3.回归分析回归分析 76例4(线性回归)、我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使用给图表增加趋势线，求车龄对售价的回归方程,并计算车龄为6.5年的旧车售价是多少.车龄车龄价格价格(万万)156.0248.5342.0437.
27、6532.5628.7722.2818.5915.01012.56.5年的旧车车价年的旧车车价=-4.8091*6.5+57.8=26.541 1、给图表增加趋势线进行回归分析、给图表增加趋势线进行回归分析7778有些数据间并不是简单的线性关系，如果用线性模式求其回归方程式，判定有些数据间并不是简单的线性关系，如果用线性模式求其回归方程式，判定系数（系数（R R2 2）很小，根本不具有任何解释力。因此要引入非线性回归，如多项）很小，根本不具有任何解释力。因此要引入非线性回归，如多项式、指数、对数等回归方法。式、指数、对数等回归方法。例例5(5(非线性回归非线性回归)、我们收集了年龄与月收入关系
28、的数据，请我们收集了年龄与月收入关系的数据，请绘制该数据的散点图，并求年龄对月收入的回归方程式。绘制该数据的散点图，并求年龄对月收入的回归方程式。79例例6(6(非线性回归非线性回归)、我们收集了一个原始森林中树的直径我们收集了一个原始森林中树的直径与高度之间的关系数据与高度之间的关系数据,请绘制该数据的散点图，并求直请绘制该数据的散点图，并求直径与高度的回归方程式径与高度的回归方程式(指数回归指数回归)。80第一步：单击“工具”菜单，选择“数据分析”选项，出现“数据分析”对话框，在分析工具中选择“回归”。2 2、使用、使用、使用、使用ExcelExcel提供的提供的提供的提供的“数据分析工具
29、数据分析工具数据分析工具数据分析工具”进行回归分析进行回归分析进行回归分析进行回归分析81第二步：单击“确定”按钮，弹出“回归”对话框，在“Y值输入区域”输入$B$1：$B$11；在“X值输入区域”输入$C$1:$C$11，在“输出选项”选择“$E$1”，如下图所示。多多元元回回归归分分析析用用用用ExcelExcel进行回归分析进行回归分析进行回归分析进行回归分析82 第三步：单击确定按钮，得回归分析结果如下图所示。用用用用ExcelExcel进行回归分析进行回归分析进行回归分析进行回归分析83Excel回归分析工具的输出结果包括3个部分：n(1)(1)回归统计表回归统计表n回归统计表包括以
30、下几部分内容：nMultiple R（复相关系数R）：是R2的平方根，又称为相关系数，用来衡量变量x和y之间相关程度的大小。本例中R为0.6313，表示二者之间的关系是正相关。nR Square（复测定系数R2）：用来说明自变量解释因变量变差的程度，以测定因变量y的拟合效果。回归分析工具的输出解释回归分析工具的输出解释84Adjusted Adjusted Adjusted Adjusted R R R R SquareSquareSquareSquare（调调调调整整整整复复复复测测测测定定定定系系系系数数数数R R R R2 2 2 2）：仅仅仅仅用用用用于于于于多多多多元元元元回回回回归
31、归归归才才才才有有有有意意意意义义义义，它它它它用用用用于于于于衡衡衡衡量量量量加加加加入入入入独独独独立立立立变变变变量量量量后后后后模模模模型型型型的的的的拟拟拟拟合合合合程程程程度度度度。当当当当有有有有新新新新的的的的独独独独立立立立变变变变量量量量加加加加入入入入后后后后，即即即即使使使使这这这这一一一一变变变变量量量量同同同同因因因因变变变变量量量量之之之之间间间间不不不不相相相相关关关关，未未未未经经经经修修修修正正正正的的的的R R R R2 2 2 2也也也也要要要要增增增增大大大大，修修修修正正正正的的的的R R R R2 2 2 2仅仅仅仅用用用用于于于于比比比比较较较较
32、含含含含有有有有同同同同一一一一个个个个因因因因变变变变量量量量的的的的各各各各种模型。种模型。种模型。种模型。标标标标准准准准误误误误差差差差：用用用用来来来来衡衡衡衡量量量量拟拟拟拟合合合合程程程程度度度度的的的的大大大大小小小小，也也也也用用用用于于于于计计计计算算算算与与与与回回回回归归归归相相相相关关关关的的的的其其其其他他他他统统统统计计计计量量量量，此此此此值值值值越越越越小小小小，说说说说明明明明拟拟拟拟合合合合程度越好。程度越好。程度越好。程度越好。观测值：用于估计回归方程的数据的观测值个数。观测值：用于估计回归方程的数据的观测值个数。观测值：用于估计回归方程的数据的观测值个
33、数。观测值：用于估计回归方程的数据的观测值个数。回归分析工具的输出解释回归分析工具的输出解释85n（2 2）方差分析表）方差分析表n方差分析表的主要作用是通过F检验来判断回归模型的回归效果。表中“回归分析”行计算的是估计值同均值之差（-）的各项指标；“残差”行是用于计算每个样本观察值与估计值之差（-）的各项指标；“总计”行用于计算每个值同均值之差（-）的各项指标。第二列df是自由度，第三列SS是离差的平方和，第四列MS是均方差，它是离差平方和除以自由度，第五列是F统计量，第六列Significance F是在显著性水平下的F的临界值 86用Excel进行回归分析基本形式基本形式JENSEN模型
34、（水模型（水分生产函数）分生产函数）两边同时取对数转化为线性形式两边同时取对数转化为线性形式87用Excel进行回归分析88用Excel进行回归分析89用Excel进行回归分析90用Excel进行回归分析基本形式基本形式两边取对数两边取对数对因变量取对因变量取对数对数对截距反对对截距反对数数91用Excel进行回归分析92用Excel进行回归分析93用Excel进行回归分析基本形式基本形式对自变量取对自变量取对数对数94用Excel进行回归分析95用Excel进行回归分析产量与需水量之间的关系：抛物线产量与需水量之间的关系：抛物线把把x2,x分别看作变量按多元回归计算即可分别看作变量按多元回归
35、计算即可96用Excel进行回归分析97用Excel进行回归分析98加入趋势线可以进行简单的回归分析，但要获得更多的统计数据，可以使用数据分析工具，求简单相关系数、判定系数、用F检定判定变量与自变量间是否有回归关系存在、用t检定判定各回归系数是否不为0、计算回归系数的置信度、标准残差等。例 7、根据广告费与销售额统计数据使用分析工具进行回归分析。回归分析信息说明回归分析信息说明99方差分析方差分析(F检定检定):用回归分析检定用回归分析检定,判定变量与自变量之间是否判定变量与自变量之间是否有显著的回归关系存在。如果显著水准有显著的回归关系存在。如果显著水准(SignificanceF)a值值,
36、回归关系存在回归关系存在,否则不存在。否则不存在。(在这里判定系数在这里判定系数a=1-置信度置信度,在我们回归分析中置信度取在我们回归分析中置信度取95%,所以所以a=1-95%=0.05)100T检定检定:判断回归系数与常数项是否为判断回归系数与常数项是否为0Intercept:回归方程中的常数项信息回归方程中的常数项信息广告费广告费(万万):自变量自变量X的回归系数信息的回归系数信息101车龄车龄价格价格(万万)156.0248.5342.0437.6532.5628.7722.2818.5915.01012.5例例8.8.我们收集了某厂家同一车型中旧车的车龄及其售价数我们收集了某厂家同
37、一车型中旧车的车龄及其售价数据，请使用据，请使用数据分析工具数据分析工具，求车龄对售价的回归方程，求车龄对售价的回归方程y=-4.8091x+57.8102练习题练习题:以下是银行客户的存款金额与贷款金额对照表以下是银行客户的存款金额与贷款金额对照表,用数据分析用数据分析工具求存款金额对贷款金额的回归方程工具求存款金额对贷款金额的回归方程,并预测当存款金额为并预测当存款金额为80千千万时万时,贷款金额为多少贷款金额为多少?y=0.8903x3.4461103通过给图表加入趋势线只能进行简单的单一参数的回归分析，通过给图表加入趋势线只能进行简单的单一参数的回归分析，但在许多情况下需要使用多个自变
38、量来预测一个变量的情况但在许多情况下需要使用多个自变量来预测一个变量的情况,这种回归分析叫复回归，复回归必须通过数据分析工具进行。这种回归分析叫复回归，复回归必须通过数据分析工具进行。复回归复回归104例9、银行为了核发信用卡，收集了申请人的每月总收入、不动产、动产、每月房贷与抚养支出费用等数据，并以业务主管员的经验，主观地给予一个信用分数。为使评估信用分数能有一套公式，免得老是依赖主管评分，请使用复回归求其回归方程式。y=a1x1+a2x2+a3x3+a4x4+a5x5+b105106例例10.10.我们收集了某厂家同一车型中旧车的车龄我们收集了某厂家同一车型中旧车的车龄,行驶里程与售价数据
39、，请行驶里程与售价数据，请使用使用数据分析工具数据分析工具，求车龄，求车龄,行驶里程对售价的回归方程行驶里程对售价的回归方程y=-1.5407*车龄车龄-0.4278*里程里程+65.9959107练习练习 影响出勤率的因素分析影响出勤率的因素分析,1-5,1-5代表影响程度，请使用代表影响程度，请使用复回归求其回归方程式。复回归求其回归方程式。108非线性的复回归非线性的复回归例例11.11.我们收集了年龄与月收入关系的数据，使用数据分我们收集了年龄与月收入关系的数据，使用数据分析工具求年龄对月收入的回归方程式。析工具求年龄对月收入的回归方程式。y=a1x2+a2x+b109y=-36.54
40、x2+3463.7x-42087110(1)(1)直线回归函数直线回归函数LINEST()LINEST()使用最小平方法计算最适合于变量区域的回归直线公式,并传回该直线公式的数组。可以用于单回归和复回归.语法语法:LINEST(变量区域,自变量区域,常数项是否不为零,是否返回附加的统计值)操作方法操作方法:1.选定输出区域 2.输入公式 3.Ctrl+Shift+Enter例12.以广告费与销售量的单一参数数据为例使用LINEST函数,求线性回归方程式。3 3、使用回归函数进行回归分析、使用回归函数进行回归分析单回归单回归(y=bx+c)(y=bx+c)输出结果输出结果y=9.1846x+29
41、9.8688111复回归复回归(y=b(y=bn nx+bx+bn-1n-1x+bx+b1 1x+c)x+c)输出结果输出结果例例13.13.我们收集了某厂家同一车型中旧车的车龄我们收集了某厂家同一车型中旧车的车龄,行驶里程与售价行驶里程与售价数据，请使用数据，请使用LINESTLINEST函数，求车龄函数，求车龄,行驶里程对售价的回归方程行驶里程对售价的回归方程y=-1.5407*车龄车龄-0.4278*里程里程+65.9959112(2)(2)线性预测函数线性预测函数(FORECAST)(FORECAST)根据给定的数据计算或预测未来值。以数组或数据区域的形式给定 x 值和 y 值后，返回
42、基于 x 的线性回归预测值。使用此函数可以对未来销售额、库存需求或消费趋势进行预测。FORECASTFORECAST(x x,known_yknown_y,known_xknown_x)X 为需要进行预测的数据点。Known_y 为因变量数组或数据区域。Known_x 为自变量数组或数据区域。例例 14.14.以广告费与销售量的单一参数数据为例使用以广告费与销售量的单一参数数据为例使用ForecastForecast函数预测当广告费用为函数预测当广告费用为500500万元时销售量为多少。万元时销售量为多少。FORECAST(500,$C$2:$C$13,$B$2:$B$13)=4892万万11
43、3(3)(3)线性趋势函数（线性趋势函数（TRENDTREND）返回一条线性回归拟合线的一组纵坐标值（y 值）。即找到适合给定的数组 known_y 和 known_x 的直线（用最小二乘法），并返回指定数组 new_xs 值在直线上对应的 y 值。语法：语法：TREND(known_y,known_x,new_x,const)操作方法操作方法:1.选定输出区域 2.输入公式 3.Ctrl+Shift+Enter例例15 15、以广告费与销售量的单一参数数据为例使用、以广告费与销售量的单一参数数据为例使用TRENDTREND函数预测一组广函数预测一组广告费的销售量。告费的销售量。114(4)指
44、数回归函数 LOGEST LOGEST：指数回归函数，计算最符合观测数据组的指数回归拟合曲线，并返回描述该曲线的数组。此曲线的方程为：y=b*mx or y=(b*(m1x1)*(m2x2)*)（如果有多个 x 值）LOGESTLOGEST(known_ysknown_ys,known_xs,const,stats)GROWTH GROWTH:指数趋势函数，根据给定的数据预测指数增长值。根据已知的 x 值和 y 值，函数 GROWTH 返回一组新的 x 值对应的 y 值。可以使用 GROWTH 工作表函数来拟合满足给定 x 值和 y 值的指数曲线。语法：语法：GROWTHGROWTH(known_ysknown_ys,known_xs,new_xs,const)选定输出区域输入公式Ctrl+Shift+Enter115116