Excel在数学建模中的应用.ppt

1 数学建模培训 Excel的应用技巧 主讲 曹清洁 2 Excel基本知识Excel在数学建模中的高级应用技巧 3 Excel基本知识 手动输入数据自动输入数据公式的使用函数的引用数据管理与分析 4 手动输入数据 文本输入默认方式为 左对齐 由数字组成的字符串前加一个 号 数值输入默认 右对齐 方式 输入分数时 要先输入 0 和空格 否则作为日期型数据处理 5 自动输入数据 自动填充鼠标拖动填充柄向下 右拖数字增量为1 向上 左拖增量为 1 自定义序列 工具 选项 新序列 输入序列内容 每个序列间按回车键 添加 选中序列区域 工具 选项 导入 确定 6 7 公式的使用 用公式计算所有的公式运算均以 开头 错误的公式以 开头 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 运算符优先级 比较运算符 若优先级相同 则按从左到右的顺序计算 8 函数的引用 手工输入 在编辑栏或单元格内输入引用单元格地址 9 相对引用利用单元格间的相对位置关系引用单元格内容 公式中的相对引用随单元格的移动而修改 但原来的位置不变 例 打开文件 数据编辑 在工作表 电视机的销售统计表 的G3中输入 E3 F3 则可在G4 G5 G6 G7 G8中填入相应公式来计算商品的销售金额 绝对引用指引用单元格和被引用单元格位置关系是固定的 公式中的引用不随单元格地址变化而变化 通常在地址前加 表示绝对引用 例 打开文件 数据编辑 计算其金额与利润值 计算金额 在G3中输入 E3 F3 并将之复制至G8 计算利润 在I3中输入 G3 G3 G 1 H3 E3 然后复制到I8混合引用即采用行为相对地址 列为绝对地址或列为相对地址 行为绝对地址来表示地址 10 Excel的数据管理和分析 1设计数据清单2记录编辑3排序数据4筛选数据5分类汇总数据 11 1设计数据清单 数据清单的条件工作表中只含有一张数据表数据表应在第1行设置列标题数据表不应含有空行或空列 12 2记录编辑 逐条显示添加记录删除记录查询记录 13 3排序数据 排序依据 主要关键字第二关键字第三关键字排序选项 自定义排序次序区分大小写排序方向排序方法 14 4筛选数据 筛选种类 自动筛选 高级筛选自动筛选方式 前10个自定义 15 5分类汇总数据 汇总步骤按汇总依据字段排序指定汇总依据字段设定汇总方式指定汇总字段汇总显示控制 删除汇总 16 数据透视表 功能按多个字段分类汇总步骤指定数据源选定数据区域决定布局确定位置 17 返回 18 Excel在数学建模中的高级应用技巧 Excel的数据处理功能用Excel绘制图表相关与回归分析 19 一 Excel的数据处理功能 1 Excel的函数 函数的结构以函数名称开始 后面是左圆括号 以逗号分隔的参数和右圆括号 1 函数以公式的形式出现 请在函数名称前面键入等号 如 sum A1 A4 A10 56 20 2 使用函数向导插入函数 按fx即可调入粘贴函数对话框 21 例1 已知某班级学生成绩 统计其中某科成绩为80分 含 90分 不含 的人数 例2 计算 例3 计算的值 例4 求矩阵的逆矩阵 注 鼠标先选中一块4 4区 先按F2键 再同时按下Shift Ctrl Enter三个键 则选定区域内出现逆矩阵的计算结果 22 例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 23 2 Excel的数据分析功能 数据分析 的统计分析工具包 包含方差分析 回归分析 协方差和相关系数 傅里叶分析等分析工具 首次使用时需要进行安装 方法如下 1 点击 工具 加载宏 2 在弹出对话框中列出各种可以加载的项目 按照需要选择 分析工具库 规划求解 等等项目 点 确定 安装完后 工具 菜单中多出了 数据分析 子菜单 点击它 弹出对话框 显示各种数据分析工具 24 25 26 数据分析功能 1 描述统计 主要统计数据的平均值 中位数 标准差 方差等等统计量 例6 某炼钢厂测了120种炉钢中的Si含量 得到120个原始数据 见Excel 27 28 29 2 直方图分析工具 直方图分析工具可完成数据的分组 频数分布与累积频数的计算 绘制直方图与累积折线图等一系列操作 以例7为操作范例 阐述直方图分析工具的统计整理功能 其操作过程如下 直方图是一大批数据的频率分布图 由直方图可以观察和分析数据的概率分布 见数据分析实例 例7 根据抽样调查 某月某市50户居民购买消费品支出资料 单位 元 30 31 首先 将样本数据排成一列 最好对数据进行排序 本例中已利用排序操作排好序 为A1 A51 输入分组标志 本例中为B1 B10 分别是899 999 1099 1199 1299 1399 1499 1599 1699 如图所示 然后 利用直方图分析工具进行分析 具体操作步骤如下 32 第一步 单击 工具 菜单 选择 数据分析 选项 打开 数据分析 对话框 从 分析工具 列表中选择 直方图 选项 如图1所示 33 第二步 打开 直方图 对话框 确定输入区域 接收区域和输出区域 如图所示 34 1 输入区域 输入待分析数据区域的单元格引用 若输入区域有标志项 则选中 标志 复选框 否则 系统自动生成数据标志 接收区域 输入接收区域的单元格引用 该框可为空 则系统自动利用输入区域中的最小值和最大值建立平均分布的区间间隔的分组 本例中输入区域为 A 2 A 51 接收区域为 B 2 B 10 2 在 输出 选项中可选择输出去向 输出去向类似于 抽样 对话框的输出去向 本例中选择 输出区域 为 C 1 3 选择 柏拉图 可以在输出表中同时按降序排列频数数据 选择 累积百分率 可在输出表中增加一列累积百分比数值 并绘制一条百分比曲线 选择 图表输出 可生成一个嵌入式直方图 35 第三步 单击 确定 按钮 在输出区域单元格可得到频数分布 如图所示 36 第四步 将条形图转换成标准直方图 如图所示 37 二 用Excel绘制图表 图表是数据表现的另一种形式 数据的图表化就是将单元格中的数据以各种统计图的形式显示 1 图表种类 1 独立的图表 单独占据一个工作表 打印时也将与数据表分开打印 2 附属于工作表的嵌入式图表 它和数据源放置在同一张工作表中 同时打印 这两种表所依据的数据都来自工作表上选中的区域 数据变化 则图表随之变化 以文件 图表 为例 38 2 图表术语 1 数据系列 图表中决定图形Y轴取值的数值集合 对应工作表中的数据行 2 分类 图表中决定数据系列的X轴的标题值 对应工作表中的数据列 3 坐标轴 图表的一边 4 图例 定义图表的图形的含义 5 网格线 帮助确定数据点在Y轴或X轴刻度上的确切值 39 3 创建图表 1 利用图表向导分四个步骤创建图表 选中区域 图表向导工具 2 按F11键快速创建图表 选中区域 F11 40 4 编辑图表 1 选中图表元素 单击 2 图表移动及改变大小 拖动鼠标 3 改变图表图案 颜色 设置刻度等 双击图表 4 改变图表类型 右键单击 5 数据系列编辑 添加数据系列 选中区域 拖曳到图标 选中图表 图表 添加数据 删除数据系列 选中系列 Delete 选中系列 编辑 清除 系列 系列次序的调整 选中系列 格式 数据系列 系列次序 修改系列颜色 选中系列双击 6 修改图表中的数据 修改图表中的数据 双击要修改数据的单元格 修改数据 修改图表中的图形 选中系列 有间断地单击结点 拖动鼠标 7 增加图表标题及数据标志 双击某一系列 选中图表 图表 图表选项 标题 数据标志 41 绘图实例 用Excel绘制任意一元函数的图像 以为例介绍 42 1 用Excel计算描述统计量2 用Excel进行相关分析3 用Excel进行回归分析 三 用Excel进行相关与回归分析 43 三 1 用Excel计算描述统计量 1 用函数计算描述统计量 2 描述统计菜单项的使用 44 常用的描述统计量有众数 中位数 算术平均数 调和平均数 几何平均数 极差 四分位差 标准差 方差 标准差系数等 下面介绍如何用函数来计算描述统计量 1 用函数计算描述统计量 45 用函数运算有两种方法 一是手工输入函数名称及参数 这种输入形式比较简单 快捷 但需要非常熟悉函数名称及其参数的输入形式 所以 只有比较简单的函数才用这种方法输入 二是函数导入法 这是一种最为常用的办法 它适合于所有函数的使用 而且在导入过程中有向导提示 因而非常方便 函数的一般导入过程为 点菜单 插入 找 函数 此时出现一个 插入函数 对话框 在对话框的 选择类别 中确定函数的类别 如常用函数或统计 在 选择函数 内确定欲选的函数名称 如SUM MODE等 点 确定 后即可出现该函数运算的对话框向导 再按向导的提示往下运行即可 46 众数 例 为了分析小麦的分蘖情况 进行了10株小麦的调查 如下图所示 47 众数 Mode 众数 Mode 统计学名词 在统计分布上具有明显集中趋势点的数值 代表数据的一般水平 众数可以不存在或多于一个 修正定义 是一组数据中出现次数最多的数值 叫众数 有时众数在一组数中有好几个 用M表示 理性理解 简单的说 就是一组数据中占比例最多的那个数 48 手工输入函数名称及参数 单击任一单元格 输入 MODE B2 B11 回车后即可得众数为14 49 函数导入法 点菜单 插入 找 函数 此时出现一个 插入函数 对话框 在对话框的 选择类别 中确定函数的类别 统计 在 选择函数 内确定欲选的函数名称 MODE 50 点 确定 后即可出现该函数运算的对话框向导 在Number1处输入B2 B11或选择Excel中的B2 B11区域 按 确定 在Excel中即得到众数14 51 中位数 单击任一空白单元格 输入 MEDIAN B2 B11 回车后得中位数为14 52 中位数 Medians 中位数 Medians 统计学名词 是指将统计总体当中的各个变量值按大小顺序排列起来 形成一个数列 处于变量数列中间位置的变量值就称为中位数 用Me表示 当变量值的项数N为奇数时 处于中间位置的变量值即为中位数 当N为偶数时 中位数则为处于中间位置的2个变量值的平均数 注意 中位数和众数不同 众数不一定在中间 53 算术平均数 单击任一空白单元格 输入 AVERAGE B2 B11 回车后得算术平均数为14 1 54 几何平均数 单击任一空白单元格 输入 GEOMEAN B2 B11 回车后得几何平均数为14 02 几何平均数是是指n个观察值连乘积的n次方根 其计算公式为 55 调和平均数 单击任一空白单元格 输入 HARMEAN B2 B11 回车后得调和平均数为1 422 公式 调和平均数 1 1 A 1 B 1 C 1 D 56 截尾平均数 将数据按由小到大顺序排列后 因数据两端值不够稳定 按一定比例去掉头尾两端一定数量的观察值 然后再求平均 这样得到的平均数就是截尾平均数 如果按1 10 即从10个数据中去掉最大的一个值和最小的一个值 再求平均数 单击任一空白单元格 输入 TRIMMEAN B2 B11 1 10 回车后得截尾平均数为14 1 57 全距 单击任一空白单元格 输入 MAX B2 B11 MIN B2 B11 回车后得全距为5 58 标准差 估计样本 单击任一空白单元格 输入 STDEV B2 B11 回车后得标准差为1 524 59 标准差计算公式 60 标准差系数 单击任一空白单元格 输入 STDEV B2 B11 AVERAGE B2 B11 回车后得标准差系数为0 1080 61 标准差系数的计算公式 式中 V 为标准差系数 为标准差 x为平均数 62 偏度系数 单击任一空白单元格 输入 SKEW B2 B11 回车后得偏度系数为 0 678 63 峰度系数 单击任一空白单元格 输入 KURT B2 B11 回车后得峰度系数为0 6249 64 常用的统计量函数 由于公式执行后显示的是计算结果 按Ctrl 键 位于键盘左上角 可使公式在显示公式内容与显示公式结果之间切换 65 2 描述统计菜单项的使用 仍使用上面的例子我们已经把数据输入到B2 B11单元格 然后按以下步骤操作 66 第一步 在工具菜单中选择数据分析选项 从其对话框中选择描述统计 按确定后打开描述统计对话框 67 第二步 在输入区域中输入 B 1 B 11 在输出区域中选择 F 1 其他复选框可根据需要选定 选择汇总统计 可给出一系列描述统计量 选择平均数置信度 会给出用样本平均数估计总体平均数的置信区间 第K大值和第K小值会给出样本中第K个大值和第K个小值 68 第三步 单击确定 可得输出结果 69 上面的结果中 平均指样本均值 标准误差指样本平均数的标准差 中值即中位数 模式指众数 标准偏差指样本标准差 自由度为n 1 峰值即峰度系数 偏斜度即偏度系数 区域实际上是极差 或全距 可以看出与我们前面用函数计算的结果完全相同 最大值为16 最小值为11 第三个最大值为15 第三个最小值为13 70 三 2 相关性分析 相关性分析 判断两组数据集 可以使用不同的度量单位 之间的关系 相关系数 R 相关性计算的返回值 用来确定两个区域中数据的变化是否相关 以及相关的程度 是两组数据集的协方差除以它们标准偏差的乘积 1 R 0 一个集合的较大数据与另一个集合的较大数据相对应 正相关 2 R 0 8 非常高度相关 71 1 CORREL函数 CORREL array1 array2 返回单元格区域array1和array2之间的相关系数 使用相关系数可以确定两种属性之间的关系 例如 可以检测某地的平均温度和空调使用情况之间的关系 Array1第一组数值单元格区域 Array2第二组数值单元格区域 如果数组或引用包含文本 逻辑值或空白单元格 这些数值将被忽略 但是包含零值的单元格将计算在内 如果array1和array2的数据点的数目不同 函数CORREL返回错误值 N A 72 例1 利用统计数据计算广告费与销售额之间的相关系数 相关系数CORREL B4 B15 C4 C15 0 92251818 73 例2 我们收集了某厂家同一车型中旧车的车龄及其售价数据 求价格与车龄的相关系数 74 例3 总平均成绩 出勤率 选修学分与每周打工小时数的关系 2 使用数据分析工具求相关矩阵利用CORREL函数只能返回两种属性之间的相关系数 如果使用Excel提供的 数据分析 工具可以计算多组数据间的相关系数 组成相关系数表 75 例利用 数据分析 工具计算汽车扳金 省油与价格的满意度之间的相关系数 76 回归分析 当一个结果与一个或多个参数之间存在联系时 可以进行回归分析 通常可由一个或多个自变量来预测一个变量的值 回归方程 表达参数与结果之间相互关系的数学方程式 数学模型 线性回归 如果变量与结果之间具有线性关系 我们可以用线性方程式来描述它们之间的关系 这种回归方法叫线性回归 非线性回归 如果变量与结果之间不具有线性关系 我们必须用非线性方程式来描述它们之间的关系 如指数关系 对数关系等等 这种回归方法叫非线性回归 单回归 当一个结果只与一个参数存在联系时 进行的回归分析称为单回归 复回归 当一个结果与多个参数存在联系时 进行的回归分析称为复回归 判定系数 R2 用来确定回归方程式的可解释性 即吻合程度 范围在0 1之间 越接近1 解释性越强 即吻合程度越高 回归方法 1 给图表增加趋势线 2 使用Excel提供的 数据分析工具 3 利用回归函数 三 3 回归分析 77 例4 线性回归 我们收集了某厂家同一车型中旧车的车龄及其售价数据 请使用给图表增加趋势线 求车龄对售价的回归方程 并计算车龄为6 5年的旧车售价是多少 6 5年的旧车车价 4 8091 6 5 57 8 26 54 1 给图表增加趋势线进行回归分析 78 79 有些数据间并不是简单的线性关系 如果用线性模式求其回归方程式 判定系数 R2 很小 根本不具有任何解释力 因此要引入非线性回归 如多项式 指数 对数等回归方法 例5 非线性回归 我们收集了年龄与月收入关系的数据 请绘制该数据的散点图 并求年龄对月收入的回归方程式 80 例6 非线性回归 我们收集了一个原始森林中树的直径与高度之间的关系数据 请绘制该数据的散点图 并求直径与高度的回归方程式 指数回归 81 第一步 单击 工具 菜单 选择 数据分析 选项 出现 数据分析 对话框 在分析工具中选择 回归 2 使用Excel提供的 数据分析工具 进行回归分析 82 第二步 单击 确定 按钮 弹出 回归 对话框 在 Y值输入区域 输入 B 1 B 11 在 X值输入区域 输入 C 1 C 11 在 输出选项 选择 E 1 如下图所示 多元回归分析 用Excel进行回归分析 83 第三步 单击确定按钮 得回归分析结果如下图所示 用Excel进行回归分析 84 Excel回归分析工具的输出结果包括3个部分 1 回归统计表回归统计表包括以下几部分内容 MultipleR 复相关系数R 是R2的平方根 又称为相关系数 用来衡量变量x和y之间相关程度的大小 本例中R为0 6313 表示二者之间的关系是正相关 RSquare 复测定系数R2 用来说明自变量解释因变量变差的程度 以测定因变量y的拟合效果 回归分析工具的输出解释 85 AdjustedRSquare 调整复测定系数R2 仅用于多元回归才有意义 它用于衡量加入独立变量后模型的拟合程度 当有新的独立变量加入后 即使这一变量同因变量之间不相关 未经修正的R2也要增大 修正的R2仅用于比较含有同一个因变量的各种模型 标准误差 用来衡量拟合程度的大小 也用于计算与回归相关的其他统计量 此值越小 说明拟合程度越好 观测值 用于估计回归方程的数据的观测值个数 回归分析工具的输出解释 86 2 方差分析表方差分析表的主要作用是通过F检验来判断回归模型的回归效果 表中 回归分析 行计算的是估计值同均值之差 的各项指标 残差 行是用于计算每个样本观察值与估计值之差 的各项指标 总计 行用于计算每个值同均值之差 的各项指标 第二列df是自由度 第三列SS是离差的平方和 第四列MS是均方差 它是离差平方和除以自由度 第五列是F统计量 第六列SignificanceF是在显著性水平下的F 的临界值 87 用Excel进行回归分析 幂函数回归 基本形式 JENSEN模型 水分生产函数 两边同时取对数转化为线性形式 88 用Excel进行回归分析 89 用Excel进行回归分析 90 用Excel进行回归分析 91 用Excel进行回归分析 指数模型 基本形式 两边取对数 对因变量取对数 对截距反对数 92 用Excel进行回归分析 93 用Excel进行回归分析 94 用Excel进行回归分析 对数模型 基本形式 对自变量取对数 95 用Excel进行回归分析 96 用Excel进行回归分析 多项式回归 产量与需水量之间的关系 抛物线 把x2 x分别看作变量按多元回归计算即可 97 用Excel进行回归分析 98 用Excel进行回归分析 99 加入趋势线可以进行简单的回归分析 但要获得更多的统计数据 可以使用数据分析工具 求简单相关系数 判定系数 用F检定判定变量与自变量间是否有回归关系存在 用t检定判定各回归系数是否不为0 计算回归系数的置信度 标准残差等 例7 根据广告费与销售额统计数据使用分析工具进行回归分析 回归分析信息说明 100 方差分析 F检定 用回归分析检定 判定变量与自变量之间是否有显著的回归关系存在 如果显著水准 SignificanceF a值 回归关系存在 否则不存在 在这里判定系数a 1 置信度 在我们回归分析中置信度取95 所以a 1 95 0 05 101 T检定 判断回归系数与常数项是否为0 Intercept 回归方程中的常数项信息广告费 万 自变量X的回归系数信息 102 例8 我们收集了某厂家同一车型中旧车的车龄及其售价数据 请使用数据分析工具 求车龄对售价的回归方程 y 4 8091x 57 8 103 练习题 以下是银行客户的存款金额与贷款金额对照表 用数据分析工具求存款金额对贷款金额的回归方程 并预测当存款金额为80千万时 贷款金额为多少 y 0 8903x 3 4461 104 通过给图表加入趋势线只能进行简单的单一参数的回归分析 但在许多情况下需要使用多个自变量来预测一个变量的情况 这种回归分析叫复回归 复回归必须通过数据分析工具进行 复回归 105 例9 银行为了核发信用卡 收集了申请人的每月总收入 不动产 动产 每月房贷与抚养支出费用等数据 并以业务主管员的经验 主观地给予一个信用分数 为使评估信用分数能有一套公式 免得老是依赖主管评分 请使用复回归求其回归方程式 y a1x1 a2x2 a3x3 a4x4 a5x5 b 106 107 例10 我们收集了某厂家同一车型中旧车的车龄 行驶里程与售价数据 请使用数据分析工具 求车龄 行驶里程对售价的回归方程 y 1 5407 车龄 0 4278 里程 65 9959 108 练习影响出勤率的因素分析 1 5代表影响程度 请使用复回归求其回归方程式 109 非线性的复回归 例11 我们收集了年龄与月收入关系的数据 使用数据分析工具求年龄对月收入的回归方程式 y a1x2 a2x b 110 y 36 54x2 3463 7x 42087 111 1 直线回归函数LINEST 使用最小平方法计算最适合于变量区域的回归直线公式 并传回该直线公式的数组 可以用于单回归和复回归 语法 LINEST 变量区域 自变量区域 常数项是否不为零 是否返回附加的统计值 操作方法 1