Excel在数学建模中的应用共117页
- 格式：ppt
- 大小：3.10 MB
- 文档页数：117
文档推荐
-
数学建模方法及其应用页数:38
-
什么是数学模型页数:45
-
数学建模方法及其应用教学片ppt课件页数:40
-
初中数学建模方法及应用页数:4
-
国内中学数学建模及其教学的研究现状页数:3
-
《数学建模方法及其应用》页数:44
下载文档原格式
合集下载
Excel在数学建模中的应用
环境系统分析
Excel规划求解 （四）Excel规划求解
的规划求解， 用Excel的规划求解，解农药管理问题。 的规划求解 解农药管理问题。
LP: S.t. Max Z=140X1+ 100X2 0.9X1+ 0.5X2 ≤632.5 X1+ X2 ≤1000 X1,X2≥0
环境系统分析
Excel的规划求解操作过程 的规划求解操作过程
ISCST3模型是由美国环保局开发并推广使用的一个城 ISCST3模型是由美国环保局开发并推广使用的一个城 市尺度的大气扩散模型， 市尺度的大气扩散模型，该模型是基于统计理论的正态 烟流模式，使用的公式为目前广范应用的稳态密封型高 烟流模式， 斯扩散方程。 斯扩散方程。
环境系统分析
气象参数预处理程序界面
环境系统分析
（一）多元回归模型的建立
环境系统分析
（一）多元回归模型的建立
环境系统分析
（二）模型参数估值
已 知 河 流 平 均 流 速 为 4.0km/h ， 饱 和 溶 解 氧 (DO) 为 10.0mg/L，河流起点的 ，河流起点的BOD(L0)浓度为 浓度为20mg/L，沿程 浓度为 ， 的溶解氧(DO)的测定数据在文件 的测定数据在文件BOD-DO.xls 给出。 给出。 的溶解氧 的测定数据在文件 试根据河流溶解氧的变化规律，确定耗氧速度常数K 试根据河流溶解氧的变化规律，确定耗氧速度常数 d 和复氧速度常数Ka。已知数学模型为： 已知数学模型为： 和复氧速度常数
工具”菜单中，单击“规划求解” 1 在“工具”菜单中，单击“规划求解”命令 目标单元格”编辑框中， 2 在“ 目标单元格 ”编辑框中 ，键入单元格引用或目标单元格 的名称。目标单元格必须包含公式。 的名称。目标单元格必须包含公式。 最大值” 、、“ 最小值” 3 目标单元格中数值可选 “ 最大值 ” 、、 “ 最小值 ” 或指定 “目标值” 。 目标值” 可变单元格”编辑框中， 4 在“ 可变单元格 ”编辑框中 ，键入每个可变单元格的名称或 引用，用逗号分隔不相邻的引用。 引用 ，用逗号分隔不相邻的引用。可变单元格必须直接或间 接与目标单元格相联系。 接与目标单元格相联系。
Excel在数模解题中的应用(董)
此题可列出微分方程并解出纯理论解， 但这里我们采用数值解法。
模型假设及构建
以坦克初始点为坐标原点，正东方向为x轴正方 向建立直角坐标系。假定在每个0.1秒时间间隔内， 坦克先向北行驶1.5米，然后导弹朝着对准坦克的方 向行驶6米。 Tk ~坦克第k个0.1秒末的纵坐标值 xk~导弹第k个0.1秒末的横坐标值 yk~导弹第k个0.1秒末的纵坐标值 Tk=1.5k; k=1,2, 0≤ xk ≤3000 ; 0≤ yk; k=1,2,
为解决此题，首先必须建立从k时刻到k+1时刻的 导弹轨迹坐标关系式：
（xk+1，yk+1）
由此可得出从k时刻到k+1时刻的导弹轨迹坐标关系式：
yk 1 yk Tk 1 yk xk 1 xk xk
xk 1 xk yk 1 yk
2
2
6
模型求解：
循环计算，直到可以覆盖整个轨道即可。
对于椭圆轨道：最少需要多少个测控站？
进一步讨论： 有必要求解β2吗？ 假如将（θ+β1） 视为任意初始角θ， 从P1开始作为第一 个测控站，往下递 推，如何？ 飞船最少测控站数
建模案例3：
如何预报人口的增长
•已知近200多年来美国人口数据（见上图） •问：如何预测2000年和2010年美国的人口数？
根据以上导弹坐标关系式，可推导出以下递推关系式
xk 1 xk
6 xk
Tk 1 yk
2
xk
2
yk 1 yk 36 xk 1 xk
2
有了这个导弹坐标递推公式，就可借助Excel表格软 件上机逐步计算出导弹运行轨迹。当： xk+1≤ 0且xk ＞0 时，即可近似确认导弹击中了坦克。追迹题解 所得结果为：当k≈533.3(即导弹运行53.3秒）时， 在Tk+1 ≈ 800（即坦克约向北行驶了800米）处，导弹 击中坦克。
Excel 在数学建模中的应用在数学建模中的应用
Excel 在数学建模中的应用在数学建模中的应用Excel 是Microsoft Office套件中的电子表格软件，它的应用很广泛，许多人把它当作一般的制作表格和图表的软件，而不清楚它的强大数据运算能力。
其实，Excel 内置了数百个函数供用户调用，还允许用户根据自己的需要随意定义自己的函数，Excel 无需编程就能够实现其他软件需要编程才能完成的复杂计算，能进行各种数据的统计、运算、处理和绘制统计图形，只要善于开发，Excel 一定能够在数学建模中发挥出更大的作用。
第一章 Excel 基本知识1． Excel的安装、启动和退出2． Excel工作界面简介Excel 以工作簿（book）的形式构成文件。
一个工作簿文件可包含多个工作表（至多255 张，一个工作表最多可容纳65536 个观察个体（记录）和256个变量（或字段名））、图表、宏指令表，这些都可以同时存在于同一个工作簿文件内。
当保存工作簿时，会把工作簿中的工作表、图表、宏指令表一并保存。
Excel 工作界面包括如下几项内容：标题栏、菜单栏、工具栏、编辑栏、工作表、工作表名称、滚动条、状态栏等。
3．单元格的命名4．配合鼠标操作的键：Shift 键、Ctrl键、Alt键。
5．宏的录制与使用第二章数据文件的建立与利用除直接输入数据或调用数据文件的一般功能外，Excel 还可由公式或一般数据快速填充方式产生新的数据，数据的复制、移动、插入、删除、排序、筛选等编缉操作非常灵活，经格式化后的数据工作表美观好看，使得Excel成为目前建立中小型数据文件最常用、最优秀软件之一。
Excel 数据与世界最优秀的统计软件SAS、SPSS等的数据可相互导入，方便处理。
本章将介绍如何利用录入、导入数据来建立Excel 数据文件。
1．单元格格式：数字、对齐、字体、边框、图案、保护。
2．数据的有效性设置3．建立数据清单4．数据“记录单”的作用5．数据的自动填充：填充柄、Ctrl+Enter复合键、填充序列对话框、等比数列数据的填充（编辑-填充-序列）、公式的自动填充（相对引用A1，绝对引用$A$1）6．公式与函数（例九九乘法口诀表的编制）7．数据的分列（数据-分列）8．添加与编辑批注（插入-批注）9．数据的导入与导出第三章数据的编缉与查询数据清单建立后，可能需要继续对部分数据进行剪切、复制、移动、删除、替换、产生新数据等。
Excel在数学建模中的应用实例
Excel在数学建模中的应用实例一、Excel 基础1、自动填充公式函数等例1.1：自动填充编号：病例数据的统一编号例1.2：自动填充实现复制例1.3：自动填充生成序列：等差、等比例1.4：利用函数计算2e-ln3.例1.5：利用函数求逆矩阵及矩阵转置、数乘矩阵、矩阵和、积、行列式等运算如已知1101122222213153A⎡⎤⎢⎥⎢⎥=⎢⎥-⎢⎥-⎣⎦，求A’,A-1解：A’：选择性粘贴，或用transpose()函数（可在名称框中输入范围以选中结果区域，然后填入公式=transpose(原矩阵区域)，再按Ctrl+Shift+Enter即可）A-1：Excel中输入矩阵，在结果矩阵第一行一列处输入=MINVERSE(原矩阵区域)，结果只显示一个数字，用鼠标选中结果区域，先F2，后Ctrl+Shift+ Enter.A+B：用自动填充，或选中结果区域，在第一格输入公式后Ctrl+Entern*A：用自动填充+绝对引用，也可同上A/n：用自动填充+绝对引用，也可同上A*B：选中结果区域，用MMult函数后Ctrl+Shift+ Enter求矩阵行列式的值：用MDETERM函数用矩阵运算解方程组：未知数X等于系数矩阵的逆矩阵和Y向量的乘积.例1.6：用公式计算11212312341...23353573579π=+++++计算π的近似值，使误差小于10-14解：令n=1; m=3; t=1; p=1，然后n=n+1; m=m+2; t=t*n/m; p=p+t; pi=p*2 10-14即计算出的前后两项相差小于10-14例1.7：利用公式及函数计算：当x=3,2,1,0,-1,-2,-3时分段函数sin,0cos,0xx x xye x x>⎧=⎨≤⎩的值。
解：先输入列x，后用if和三角函数解决。
例1.8：求连续复利问题假设银行活期存款年利率为r（如r=3.25%），若某储户存20000元活期存款，那么一年后，他可以得到利息20000r，本息合计20000(1+r)元，因活期可以随便什么时候支取，如果满半年就结算一次，此时的本息合计为20000(1+r/2)，把本息取出后立即再存入的话可得复利，即半年后再次结算，则全年的本息合计为20000（1＋r/2）2，因为(1+r/2)2=1+r+r 2/4>1+r ，如此可发现每半年结算一次获利比一年结算一次多，某储户就想是不是每季度、每月、每半月。
EXCEL学习-数学建模
7
链接
back
1.5 简单计算
Excel中，加、减、乘、除的运算符分 别是：“+”、“-”、“*”、“/” 如右图（上），计算销售金额；销售 金额＝单价*数量： 1、选中E5 2、输入“＝”，然后单击C5 3、输入“*”代表乘号 4、用鼠标单击D5单元格 5、单击确定
17:03:42
17:03:42
大学生数学建模协会
36
Excel创建图表步骤
17:03:42
大学生数学建模协会
37
Excel创建图表步骤
17:03:42
大学生数学建模协会
38
Excel创建图表步骤
17:03:42
大学生数学建模协会
39
Excel创建图表步骤
17:03:42
大学生数学建模协会
40
Excel创建图表步骤
17:03:42
大学生数学建模协会
43
如何在excel输入文本型数据？
17:03:42
大学生数学建模协会
44
B题 眼科病床的合理安排 （2009高教社杯全国大学生数学建模竞赛题目）
医院就医排队是大家都非常熟悉的现象，它以这样或那样 的形式出现在我们面前，例如，患者到门诊就诊、到收费处 划价、到药房取药、到注射室打针、等待住院等，往往需要 排队等待接受某种服务。 我们考虑某医院眼科病床的合理安排的数学建模问题。 该医院眼科门诊每天开放，住院部共有病床79张。该医院 眼科手术主要分四大类：内障、视网膜疾病、青光眼和外伤。 附录中给出了2008年7月13日至2008年9月11日这段时间里各 类病人的情况。 白内障手术较简单，而且没有急症。目前该院是每周一、 三做白内障手术，此类病人的术前准备时间只需1、2天。做 两只眼的病人比做一只眼的要多一些，大约占到60%。如果 要做双眼是周一先做一只，周三再做另一只。
Excel在数学建模教学中的应用
Excel在数学建模教学中的应用背景数学建模是现代教育和科研中重要的一环，它将现实生活中的实际问题转化为数学问题，并通过数学模型进行分析和解决。
在学习过程中，学生需要提高模型构建和求解问题的能力，这就需要使用工具来支持学生的学习。
其中，Excel是一种应用广泛、易于上手的工具，因此在数学建模教学中，Excel的应用也越来越多。
Excel在数学建模教学中的优势数据处理和分析Excel具有数据处理和分析的能力，这使得学生可以通过Excel来处理数据，绘制各种统计图表，进行数据分析和预测。
这些能力可以帮助学生更好地理解模型和现实问题的关系，并进一步提高他们的模型构建和分析能力。
运算和求解Excel也具有强大的运算和求解能力。
无论是线性回归、非线性方程、优化问题，Excel都能够提供相关的函数和工具，帮助学生求解数学建模中的实际问题。
这些能力可以帮助学生掌握数学建模的基本技能，提高他们的数学思维和解决问题的能力。
可视化呈现Excel可以将复杂的数据和分析结果以图形和表格的形式直观地呈现出来，这对于学生理解和分析问题非常重要。
学生可以使用Excel绘制求解结果的各种图表，从而更好地理解模型和问题之间的关系。
灵活性和易用性Excel具有灵活和易用的特点，不需要编写复杂的代码就能够完成许多数学建模的任务。
学生可以通过自己的理解和创造力来使用Excel完成各种数学建模的任务，这也有助于培养学生的创新精神和动手能力。
Excel在数学建模教学中的实际应用数据处理和分析在数学建模教学中，学生经常需要使用实际数据进行建模和分析，这时候Excel就可以发挥它的优势了。
学生可以使用Excel进行数据预处理和清洗，处理缺失值和异常值，绘制各种数据图表。
在此基础上，学生可以通过Excel进行各种数据分析，比如拟合曲线、计算统计量、构建回归模型等等。
模型的求解在数学建模中，模型的求解是一个非常重要的步骤，也是学生需要掌握的一个基本技能。
Excel在数学建模中的应用简介
xi i 1 k
CRITBINOM 二项分布的临界值（分位数） INTERCEPT 线性回归中的常数项 LINEST
n, p,
两组数 两组数
数组y，多维 数组x，逻辑 值c,s
LOGEST
GEOMEAN
同上
n个数
几何平均数
调和平均数（倒数平均值的倒数）
HARMEAN
MIN
n个数
n个数
n个数中的最小值
（续）表2 Excel数学与三角函数
函数名 RAND 功 能 0-1之间均匀分布随 机数 参 无 数
RANDBETWEEN 两个数之间的随机数 两个数 SUMXMY2 SERIESSUM SIGN 两个数组对应数值的 两个数组 平方和 求幂级数的和 符号函数 满足要求 的四个数 实数
还有一些舍入或取整函数没有一一列出，如 INT，功能是向下取整。
26
27
（二）Excel的数据分析功能 EXCEL提供了用作“数据分析”的统计分析 包，内含方差分析、回归分析、协方差和相关 系数、傅立叶分析等分析工具，使用这些分析 工具，可大大提高工作效率和质量。 在默认安装时，EXCEL并不直接提供数据分析 工具包，首次使用时需要进行安装，方法如下： ⑴ 点击工具→加载宏； ⑵ 按需要选择分析工具库、规划求解等项 目，点击确定； ⑶ 如果需要，需原OFFICE安装光盘。
SQRT
LOG LOG10
x的平方根
给定底的对数 10为底的对数
同上
真数和底数 真数或单元格
LN
ABS FACT COMBIN MDETERM MINVERSE
自然对数
x的绝对值 计算n阶乘 组合数 C n 求行列式的值 求矩阵的逆
6
CRITBINOM 二项分布的临界值（分位数） INTERCEPT 线性回归中的常数项 LINEST
n, p,
两组数 两组数
数组y，多维 数组x，逻辑 值c,s
LOGEST
GEOMEAN
同上
n个数
几何平均数
调和平均数（倒数平均值的倒数）
HARMEAN
MIN
n个数
n个数
n个数中的最小值
（续）表2 Excel数学与三角函数
函数名 RAND 功 能 0-1之间均匀分布随 机数 参 无 数
RANDBETWEEN 两个数之间的随机数 两个数 SUMXMY2 SERIESSUM SIGN 两个数组对应数值的 两个数组 平方和 求幂级数的和 符号函数 满足要求 的四个数 实数
还有一些舍入或取整函数没有一一列出，如 INT，功能是向下取整。
26
27
（二）Excel的数据分析功能 EXCEL提供了用作“数据分析”的统计分析 包，内含方差分析、回归分析、协方差和相关 系数、傅立叶分析等分析工具，使用这些分析 工具，可大大提高工作效率和质量。 在默认安装时，EXCEL并不直接提供数据分析 工具包，首次使用时需要进行安装，方法如下： ⑴ 点击工具→加载宏； ⑵ 按需要选择分析工具库、规划求解等项 目，点击确定； ⑶ 如果需要，需原OFFICE安装光盘。
SQRT
LOG LOG10
x的平方根
给定底的对数 10为底的对数
同上
真数和底数 真数或单元格
LN
ABS FACT COMBIN MDETERM MINVERSE
自然对数
x的绝对值 计算n阶乘 组合数 C n 求行列式的值 求矩阵的逆
6
Excel在数模中的运用ppt课件
的一种模拟运算，神经网络具有人脑的学习记忆功能，主 要运用到数据建模、预测和方式识别和函数优化等领域 神经网络的根本类形，主要有线形神经网络〔主要运用在 信号处置和简单的数据分类领域〕，前向传送神经网络 〔主要包括BP神经网络、径向基神经网络〕，其中运用 最广泛的就是三层前向BP神经网络〔市面上有很多工具 箱都是基于BP神经网络的原理建立，如2nsoft等〕，还有 反响形神经网络〔例如Elman、Hopfielddeng〕，他们普 通用于信号处置和函数(条件)优化〔主要是运用它们的预 测和联想记忆功能〕,还有一种就是自组织神神经网络 〔主要包括学习矢量量化神经网络和自组织特征映射神经 网络〕。
利用excel插件作bp神络
输出量
Excel插件nnx引见
本软件是针对运用神经网络中最烦琐的数据和图 形表示而开发，特别方便用户处置数据和编辑图 形
本卷须知： 1)根本步骤是按菜单中的“新建网络〞->“训练 样本〞->和“预测〞进展，可以保管和翻开以前 训练好的网络模型并了解权值。 2)训练数据前需求翻开或输入样本数据的Excel任 务表
利用神经网络预测根本步骤
建立神经网络 输入样本数据 训练网络，让网络进展学习直至误差降低
到可接受的范围 输入需求预测的数据 Bp模拟仿真计算，得出预测结果
网络目的误差设定与训练
为什么要设置网络目的误差：BP〔back－progatation〕的源意 就是反向传播算法，它每运算一次，网络都会有一个输出网络 误差，它会和他设置的网络目的误差相比较，一次来运转网络， 知道它的输出误差，在规定的迭代次数内小于他预先设定的目 的误差才停顿原算，BP神经网络就是这么来的。
〔3〕，BP神经网络模型的建立，普通建立的都是三层前 向传送神经网络模型。
利用excel插件作bp神络
输出量
Excel插件nnx引见
本软件是针对运用神经网络中最烦琐的数据和图 形表示而开发，特别方便用户处置数据和编辑图 形
本卷须知： 1)根本步骤是按菜单中的“新建网络〞->“训练 样本〞->和“预测〞进展，可以保管和翻开以前 训练好的网络模型并了解权值。 2)训练数据前需求翻开或输入样本数据的Excel任 务表
利用神经网络预测根本步骤
建立神经网络 输入样本数据 训练网络，让网络进展学习直至误差降低
到可接受的范围 输入需求预测的数据 Bp模拟仿真计算，得出预测结果
网络目的误差设定与训练
为什么要设置网络目的误差：BP〔back－progatation〕的源意 就是反向传播算法，它每运算一次，网络都会有一个输出网络 误差，它会和他设置的网络目的误差相比较，一次来运转网络， 知道它的输出误差，在规定的迭代次数内小于他预先设定的目 的误差才停顿原算，BP神经网络就是这么来的。
〔3〕，BP神经网络模型的建立，普通建立的都是三层前 向传送神经网络模型。
EXCEL在数学建模中的应用
EXCEL 在数学建模中的应用一、excel的数据处理功能1.函数Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。
Excel统计函数例如：求矩阵的逆矩阵MINVERSE、IF、SUM、SUMIF、COUNTIF、A VERAGE、例如：简单数值计算用户自定义函数例：当x=3 ,2,1,0,-1,-2,-3时，计算分段函数x sin x>0e cos x 0x x y x ⎧=⎨≤⎩。
例：用迭代法求非线性方程cos 0x x -=的数值解，迭代公式1cos k k x x -=，取01x =，要求精度达到1210-。
2、EXCEL 的数据分析功能1.描述统计：主要统计数据的平均值、中位数、标准差、方差等统计量。
2.直方图 （频率分布图） 例：randrandbetween;二、用EXCEL 绘制图表在实际生活中，有时为了能够直观的展现数据和分析数据，需要用折线图、柱形图或饼图表示表格中数据的比例关系，通过图表可以将抽象的数据形象化，便于我们理解、分析。
下图就是EXCEL 提供的一种饼图“我国土地利用类型”图表，能直观地看出我国土地利用的整体水平，也能反映出不同类型之间的差异。
EXCEL 中有很多的基本图表可供选择，我们只介绍其中的折线图、柱形图和饼图。
步骤1：选定用于制作图表的数据区和数据标志区。
在此例中我们选择A1：A8和D1：D8，其中A1：A8为图表的标志区，D1：D8为图表的数据区。
步骤2：单击常用工具栏上的“图表向导”按钮，出现“图表类型”对话框：在对话框左边的图表类型里选择相应的图表类型，右边选择相应的子图表类型。
在此例中我们选择“簇状柱形图”。
步骤3：单击“下一步”，出现“图表源数据”对话框，对数据区域和系列进行设定。
步骤4：单击下一步，出现“图表选项”对话框，对“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”和“数据表”分别进行设定。
Excel在数学建模中的应用(二)
二、动态数列分析
1.计算移动平均数
操作步骤：
❖ 单击“工具”菜单→ “数据分析” →“移动平 均”，单击“确定”按钮
❖ 打开“移动平均”对话框，确定输入区域、间 隔和输出区域。
❖ 选中“图表输出”复选框，单击“确定”按钮， 可得输出结果。
例4：在某地点连续观测16周来的游客人数如下表 （百人次）所示：
❖ 打开“回归”对话框，确定Y值输入区域、X值输入 区域和输出区域。
❖ 选中“线性拟合图”复选框，单击“确定”按钮， 可得回归分析结果和线性拟合图。
❖ 在线性拟合图的数据点上添加趋势线，并计算长期 趋势的拟合方程式
线性拟合图上添加趋势线步骤：
❖ 在线性拟合图的数据点上单击鼠标右键，从弹出的 快捷菜单中选择“添加趋势线”。
周次 1 2 3
人次 142 126 132
周次 9 10 11
人次 144 124 134
试计算3周移动平 均和4周移动平均 数。
4
138
12
136
5
140
13
146
6
128
14
130
7
138
15
140
8
114
16
136
二、动态数列分析
2.最小二乘—直线回归
操作步骤：
❖ 单击“工具”菜单→ “数据分析” →“回归”， 单击“确定”按钮
❖ 在“添加趋势线”对话框的“类型”选项卡中选择 “线性”趋势预测/回归分析类型，并选择“Y”数据 系列；切换到“选项”选项卡，选中“显示公式” 和“显示R平方值”复选框，单击“确定”按钮， 可得长期趋势图。
二、动态数列分析
2.最小二乘—直线回归 预测函数： ❖ TREND()函数
EXCEL在数学建模中的应用解析
EXCEL在数学建模中的应用许多人对EXCEL的数据计算功能不了解，仅把它当作制作表格和图表的办公软件。
用它不需编程就能够实现其他软件需要编程才能完成的复杂计算，能够进行各种数据统计、运算、处理和绘制统计图形，只要善于开发，一定能够在数学建模中发挥出更大的作用。
一、EXCEL的数据处理功能EXCEL擅长数据统计，用它来处理数据能够节省大量时间，提高效率。
EXCEL的数据处理功能主要有两大块：1）计算功能它提供了300多个内部函数供用户使用，还充许自定义函数。
当大批数据都要用同一公式计算时，只要用鼠标拖动而不需要编程。
2）数据分析功能EXCEL提供了“数据分析”工具包，内含方差分析、回归分析、协方差和相关系数、博立叶分析、t检验等分析工具。
（一）Excel的函数Excel提供了12类（有常用、财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息、工程、用户定义）共300多个内部函数，其中用得比较多的是常用、统计和数学与三角函数类中的函数。
函数由函数名、参数组成。
不同函数对其参数要求不同，若参数为数值，则可用单元格取代，有些函数的参数是多个数据，则可用区域取代，有些函数的参数是矩阵，则可用矩形区域取代。
①常用函数当插入函数对话框的选择类别中显示“常用函数”时，共有十多个函数供选择，它们的功能和参数如表1所示。
表1 Excel常用函数②数学与三角函数这些是数值计算时常用到的函数。
在插入函数对话框中选择数学与三角函数，则显示出58种函数供选择，其中常用的函数见表2所示。
表2 Excel数学与三角函数还有一些舍入或取整函数没有一一列出，如INT ，功能是向下取整。
例1 计算2e -。
例2 ln 3的值。
例3 求矩阵1101122222213153A ⎛⎫⎪⎪= ⎪-⎪-⎝⎭的逆矩阵。
【作法】插入→函数→数学与三角函数→MINVERSE →A1：D4→确定然后再在插入函数的区域仅出现一个-4，若要显示全部逆矩阵，则以插入函数的单元格（如上例的A7）为开始，选择一个和原矩阵A 大小一样的区域（如A7：D10），再按F2，再同时输入Shift+Ctrl+Enter ，则在选定的区域出现逆阵的计算结果。
EXCL在数学建模和土木工程中的应用
《环境质量评价》实验指导（适用专业：农业资源与环境、水土保持与荒漠化专业）XX八一农垦大学植物科技学院资环系目录实验一Excel 在建立数学模型的应用 (1)实验二用Excel进行等标污染指数、等标污染负荷、污染负荷比的运算方法 (9)实验三Excel在大气环境质量评价中的应用 (13)实验四用Excel模板进行有关河流湖泊水质评价的应用 (21)实验五环境系统最优化 (26)实验一 Excel 在建立数学模型的应用一、实验目的：练习掌握线性回归分析、曲线拟合及参数估计等数学建模问题二、实验原理：数学模型的建立过程中，从数据分析、参数估计直至模型的检验，数据计算的工作量十分巨大，没有计算机的帮助要完成这些工作是很难想象的。
Microsoft Excel 就是完成该项工作的一种简便有效的工具。
三、实验内容：1、污水处理的线性回归分析:月份的数据建立其出水COD对应入水COD的线性回归模型，然后用4月份的数据进行验证。
解:首先建立Excel的工作表，输入污水处理厂监测的原始数据。
在2.2 中已介绍了Microsoft Excel的“分析工具库”。
线性回归也是属于该工具库的内容。
在“工具”菜单中，单击“数据分析”命令。
如果“数据分析”命令没有出现在“工具”菜单中，则需要通过加载宏安装“分析工具库”，与此同时也将“规划求解”安装备用。
如图2-5所示。
完成了加载宏的安装过程，在“工具”菜单中，单击“数据分析”命令，选择线性回归操作。
按照对话框要求在Y 值输入区域输入对因变量数据区域的引用，该区域必须由单列数据组成。
这里选择输入3月份的出水COD的数据区域；在X 值输入区域输入对应入水COD数据。
回归统计的一些主要结果如表2-5。
图2-5 加载宏安装“分析工具库”和“规划求解”表2-5 出水COD对应入水COD回归统计结果Multiple R 0.630237Intercept 43.25682X Variable 1 0.136996标准误差26.22009观测值24因此，出水COD对应入水COD的线性回归的模型形式是：Y = 0.137X + 43.257相关系数R=0.63，观测值24个。
Excel在数学建模中的应用(一)
➢ 首先，先将样本数据排成一列，最好对数据进行 排序
➢ 第一步：“工具”菜单中单击“数据分析”选项， 从其对话框的“分析工具”列表中选择“直方 图”。
➢ 第二步：在“直方图”对话框中，确定输入区域、 接收区域和输出区域。
➢ 第三步：选择图表输出，单击“确定” 。 ➢ 第四步：将柱形图转换成标准直方图
二、数据整理
1.变量数列的编制 利用频数分布函数FREQUENCY函数
➢ 第一步：在选定单元格区域，单击“插入”菜单， 选择“函数”选项，（或单击“常用”工具栏的 “fx”按钮）弹出“插入函数”对话框，在对话框 的上方“选择类别”中选择“统计”，在下方的 “选择函数”中选择FREQUENCY。
➢ 第二步：打开“FREQUENCY”对话框，输入待 分组数据与分组标志。
基本概念：
输入区域：在此输入待分析数据区域的单元格范围。 接收区域：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计 算频数的边界值。这些值应当按升序排列。只要存在的话，Excel 将统计在 各个相邻边界直之间的数据出现的次数。如果省略此处的接收区域，Excel 将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。 标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果 输入区域没有标志项，则清除此该复选框，Excel 将在输出表中生成适宜的 数据标志。 输出区域：在此输入结果输出表的左上角单元格的地址用于控制计算结果的显 示位置。如果输出表将覆盖已有的数据，Excel 会自动确定输出区域的大小 并显示信息。 柏拉图：选中此复选框，可以在输出表中同时显示按降序排列频率数据。如果 此复选框被清除，Excel 将只按升序来排列数据。 累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并 同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。 图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。
➢ 第一步：“工具”菜单中单击“数据分析”选项， 从其对话框的“分析工具”列表中选择“直方 图”。
➢ 第二步：在“直方图”对话框中，确定输入区域、 接收区域和输出区域。
➢ 第三步：选择图表输出，单击“确定” 。 ➢ 第四步：将柱形图转换成标准直方图
二、数据整理
1.变量数列的编制 利用频数分布函数FREQUENCY函数
➢ 第一步：在选定单元格区域，单击“插入”菜单， 选择“函数”选项，（或单击“常用”工具栏的 “fx”按钮）弹出“插入函数”对话框，在对话框 的上方“选择类别”中选择“统计”，在下方的 “选择函数”中选择FREQUENCY。
➢ 第二步：打开“FREQUENCY”对话框，输入待 分组数据与分组标志。
基本概念：
输入区域：在此输入待分析数据区域的单元格范围。 接收区域：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计 算频数的边界值。这些值应当按升序排列。只要存在的话，Excel 将统计在 各个相邻边界直之间的数据出现的次数。如果省略此处的接收区域，Excel 将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。 标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果 输入区域没有标志项，则清除此该复选框，Excel 将在输出表中生成适宜的 数据标志。 输出区域：在此输入结果输出表的左上角单元格的地址用于控制计算结果的显 示位置。如果输出表将覆盖已有的数据，Excel 会自动确定输出区域的大小 并显示信息。 柏拉图：选中此复选框，可以在输出表中同时显示按降序排列频率数据。如果 此复选框被清除，Excel 将只按升序来排列数据。 累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并 同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。 图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。
excel中数学建模中的应用
函数名参数11常用函数函数名功能参数exp计算ex任意被实数sum求和数组ln求自然对数正实数countif统计满足某种条件的数据个数数据区域和条件average求算术平均值数组由条件决定返回值一个条件两个结果count统计个数数组max求最大值数组sumif满足某种条件的数据和数据区域和条件hyperlink创建一个快捷方式或链接路径和文件名标识符12数学与三角函数函数名函数名函数名sincostanmdeterm行列式sumxmy2asinacosatanminverse求逆矩阵两个数组对应数值差的平方和sinhcoshtanhmmult矩阵乘积seriessum幂积数的和asinhacoshatanhsumsq平方和sign符号函数powerx的y次方mod整除求余int向下取整sqrtproduct连乘积loglog10pi圆周率abslcm最小公倍数fact阶乘rand01之间的随机数combin组合randbetween两个数之间的随机数应特别注意矩阵逆及乘积求法
1.2数学与三角函数
函数名 Sin,cos,tan Asin,acos,atan Sinh,cosh,tanh Asinh,acosh,atanh power X的y次方 sqrt Log,log10 abs fact阶乘 Combin组合 函数名
应特别注意矩阵逆及乘积求法!
函数名 Sumxmy2 两个数组对应数值差的平方和 Seriessum幂积数的和 Sign符号函数 Int向下取整
i =1 k
(mi − n pi ) 2
∧
∧
的值，如果此
n pi
值过大就否定原假设。 方法：可先给定α，反查χ 2（k − r − 1 , 若χ 2 > χ 2（k − r − 1 , ） ） α α 则拒绝H 0 .
1.2数学与三角函数
函数名 Sin,cos,tan Asin,acos,atan Sinh,cosh,tanh Asinh,acosh,atanh power X的y次方 sqrt Log,log10 abs fact阶乘 Combin组合 函数名
应特别注意矩阵逆及乘积求法!
函数名 Sumxmy2 两个数组对应数值差的平方和 Seriessum幂积数的和 Sign符号函数 Int向下取整
i =1 k
(mi − n pi ) 2
∧
∧
的值，如果此
n pi
值过大就否定原假设。 方法：可先给定α，反查χ 2（k − r − 1 , 若χ 2 > χ 2（k − r − 1 , ） ） α α 则拒绝H 0 .
数学建模——excel
§10.4 EXCEL在数学建模中的应用10.4.1 简介Microsoft Excel是目前应用最为广泛的办公室表格处理软件之一。
它在数学统计中也有广泛应用。
Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具，具有分析能力强、操作简便、图表能力强等特点。
10.4.2 Excel 中的统计工具简介1．统计函数Excel提供78个统计函数。
在主菜单中的“插入”中选择“函数”，单击后就可以得到一组常用的统计函数，如均值AVERAGE、方差VAR、中位数 MEDIAN、秩RANK、最大值MAX、最小值MIN、计数COUNT，离散和连续分布的分布函数、概率函数、分位点等，如图10.所示。
在选定函数的同时，在命令的下方会出现一条说明，表明命令的意义及每个参数的含义。
图10.例如正态分布分布函数 NORMDIST，返回给定均值和标准差的正态分布分布函数或正态分布概率密度函数。
语法：NORMDIST(x, mean, standard_dev , cumulative)说明： x 为需要计算其分布的数值，Mean 为分布的均值，Standard_dev 为分布的标准差，Cumulative 为一逻辑值，指明函数的形式。
如果 cumulative 为 TRUE，函数 NORMDIST 返回分布函数；如果为 FALSE，返回概率密度函数。
（1）如果 mean 或 stand_dev 为非数值型，函数 NORMDIST 返回错误值 #VALUE!。
（2）如果 standard_dev < 0，函数 NORMDIST 返回错误值 #NUM!。
（3）如果 mean= 0 且 standard_dev = 1，函数 NORMDIST 返回标准正态分布，即函数NORMSDIST。
图10.2．统计宏Excel 为统计分析提供了一个功能很强的统计软件包。
它是一个外挂的开发产品。
在安装时可以有选择地将它加载到Excel 系统环境中去，在主菜单“工具”菜单的最下面一栏，如果已存在“数据分析”命令，则直接调用该软件包。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
即采用行为相对地址、列为绝对地址或列为相对地址、 8
Excel的数据管理和分析
1 设计数据清单 2 记录编辑 3 排序数据 4 筛选数据 5 分类汇总数据
9
1 设计数据清单
数据清单的条件
工作表中只含有一张数据表 数据表应在第1行设置列标题 数据表不应含有空行或空列
10
2 记录编辑
逐条显示 添加记录 删除记录 查询记录
直方图分析工具可完成数据的分组、频数分 布与累积频数的计算、绘制直方图与累积折线图 等一系列操作。以例7为操作范例，阐述直方图 分析工具的统计整理功能，其操作过程如下。
21Biblioteka 2、Excel的数据分析功能
“数据分析”的统计分析工具包，包含方差分析、回 归分析、协方差和相关系数、傅里叶分析等分析工具
首次使用时需要进行安装，方法如下： （1）点击“工具”----”加载宏”； （2）在弹出对话框中列出各种可以加载的项目，按 照需要选择“分析工具库”、“规划求解”等等项目， 点“确定”。 安装完后，“工具”菜单中多出了“数据分析”子菜 单，点击它，弹出对话框，显示各种数据分析工具。
汇总显示控制
删除汇总
14
数据透视表
功能
按多个字段分类汇总
步骤 指定数据源 选定数据区域 决定布局 确定位置
15
16
Excel在数学建模中的高级应用技巧
Excel的数据处理功能 用Excel绘制图表 相关与回归分析
17
一、Excel的数据处理功能
1、Excel的函数
函 数的结构以函数名称开始，后面是左圆括号、 以逗号分隔的参数和右圆括号。
指引用单元格和被引用单元格位置关系是固定的，公 式中的引用不随单元格地址变化而变化。
通常在地址前加“$”表示绝对引用。 例：打开文件“数据编辑”，计算其金额与利润值。
计算金额：在G3中输入：=E3*F3，并将之复制至G8。 计算利润：在I3中输入：=G3-（G3*$G$1+H3+E3）， 然后复制到I8 混合引用
22
23
24
数据分析功能： 1、描述统计
主要统计数据的平均值、中位数、标准差、方差等等 统计量
例6：某炼钢厂测了120种炉钢中的Si含量，得到 120个原始数据，见Excel.
25
26
27
2、直方图分析工具
直方图是一大批数据的频率分布图，由直方图 可以观察和分析数据的概率分布。见数据分析实例
后加、减） （2）文本连接符：&——将两段用双引号（英文状态下）括起的文本
连接为一段连续的文本。 （3）比较运算符：>、<、=、>=、<=、<>（用于比较两个数据的大小，
比较结果是一个逻辑值，即TRUE（真）或FALSE（假））。 （4）引用运算符：冒号、空格、逗号
①冒号：用于定义一个单元格区域，以便在公式中使用。 例：=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）运算符优先级 （）——%——^——*、/——+、-、——&——比较运算符。（若优 先级相同，则按从左到右的顺序计算）
3
自动输入数据
自动填充鼠标拖动填充柄向下、右拖数字 增量为1，向上、左拖增量为-1 .
自定义序列 ① 工具——选项——新序列——输入序列 内容，每个序列间按回车键——添加 ② 选中序列区域——工具——选项——导 入——确定
4
5
公式的使用
用公式计算 所有的公式运算均以“=”开头。错误的公式以#开头。 （1）数学运算符： +、-、*、/、^、%（计算准则：先乘方，再乘、除，
6
函数的引用
手工输入：在编辑栏或单元格内输入 引用单元格地址:
7
相对引用
利用单元格间的相对位置关系引用单元格内容。公式中 的相对引用随单元格的移动而修改，但原来的位置不变。
例：打开文件“数据编辑”，在工作表“电视机的销售 统计表” 的G3中输入“=E3*F3，则可在G4、G5、G6、 G7、G8中填入相应公式来计算商品的销售金额。 绝对引用
(1)函数以公式的形式出现，请在函数名称前面 键入等号（=）。
如： =sum(A1,A4:A10,56)
18
（2）使用函数向导插入函数：按fx即可调入粘贴函数 对话框
19
例1：已知某班级学生成绩，统计其中某科成 绩为80分（含）-90分（不含）的人数。
例2：计算 e 2
例3：计算 2的ln3值。
Excel基本知识 Excel在数学建模中的高级应用
技巧
1
Excel基本知识
手动输入数据 自动输入数据 公式的使用 函数的引用 数据管理与分析
2
手动输入数据
文本输入默认方式为“左对齐”.由数字组 成的字符串前加一个’号 .
数值输入默认“右对齐”方式.输入分数时， 要先输入“0”和空格，否则作为日期型数据 处理。
11
3 排序数据
排序依据：
主要关键字 第二关键字 第三关键字
排序选项：
自定义排序次序 区分大小写 排序方向 排序方法
12
4 筛选数据
筛选种类：自动筛选、高级筛选 自动筛选方式：
前10个
自定义
13
5 分类汇总数据
汇总步骤
按汇总依据字段排序 指定汇总依据字段 设定汇总方式 指定汇总字段
1
例4：求矩阵
A
1
2
3
1 0 1
2 的2 逆2 矩 阵
2 2 1
1
5
3
注：鼠标先选中一块4*4区，先按F2键，再同时按下Shift+Ctrl+Enter三个 键，则选定区域内出现逆矩阵的计算结果。
20
例5：利用自定义函数进行计算
（1）当x=3,2,1,0,-1,-2,-3时，计算分段函数 xsin的x,值x 。0
（2）用y 迭e代x c法osx求,x非线0性方程x-cosx=0的数 值解，迭代公式是xk=cos(xk-1)，取x0=1, 试用Excel计算，要求精度达到10-12。
（3）利用公式
1 1 1 2 1 2 3 1 2 3 * 4
2 3353573579
计算 的近似值，使误差小于10-14。
Excel的数据管理和分析
1 设计数据清单 2 记录编辑 3 排序数据 4 筛选数据 5 分类汇总数据
9
1 设计数据清单
数据清单的条件
工作表中只含有一张数据表 数据表应在第1行设置列标题 数据表不应含有空行或空列
10
2 记录编辑
逐条显示 添加记录 删除记录 查询记录
直方图分析工具可完成数据的分组、频数分 布与累积频数的计算、绘制直方图与累积折线图 等一系列操作。以例7为操作范例，阐述直方图 分析工具的统计整理功能，其操作过程如下。
21Biblioteka 2、Excel的数据分析功能
“数据分析”的统计分析工具包，包含方差分析、回 归分析、协方差和相关系数、傅里叶分析等分析工具
首次使用时需要进行安装，方法如下： （1）点击“工具”----”加载宏”； （2）在弹出对话框中列出各种可以加载的项目，按 照需要选择“分析工具库”、“规划求解”等等项目， 点“确定”。 安装完后，“工具”菜单中多出了“数据分析”子菜 单，点击它，弹出对话框，显示各种数据分析工具。
汇总显示控制
删除汇总
14
数据透视表
功能
按多个字段分类汇总
步骤 指定数据源 选定数据区域 决定布局 确定位置
15
16
Excel在数学建模中的高级应用技巧
Excel的数据处理功能 用Excel绘制图表 相关与回归分析
17
一、Excel的数据处理功能
1、Excel的函数
函 数的结构以函数名称开始，后面是左圆括号、 以逗号分隔的参数和右圆括号。
指引用单元格和被引用单元格位置关系是固定的，公 式中的引用不随单元格地址变化而变化。
通常在地址前加“$”表示绝对引用。 例：打开文件“数据编辑”，计算其金额与利润值。
计算金额：在G3中输入：=E3*F3，并将之复制至G8。 计算利润：在I3中输入：=G3-（G3*$G$1+H3+E3）， 然后复制到I8 混合引用
22
23
24
数据分析功能： 1、描述统计
主要统计数据的平均值、中位数、标准差、方差等等 统计量
例6：某炼钢厂测了120种炉钢中的Si含量，得到 120个原始数据，见Excel.
25
26
27
2、直方图分析工具
直方图是一大批数据的频率分布图，由直方图 可以观察和分析数据的概率分布。见数据分析实例
后加、减） （2）文本连接符：&——将两段用双引号（英文状态下）括起的文本
连接为一段连续的文本。 （3）比较运算符：>、<、=、>=、<=、<>（用于比较两个数据的大小，
比较结果是一个逻辑值，即TRUE（真）或FALSE（假））。 （4）引用运算符：冒号、空格、逗号
①冒号：用于定义一个单元格区域，以便在公式中使用。 例：=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）运算符优先级 （）——%——^——*、/——+、-、——&——比较运算符。（若优 先级相同，则按从左到右的顺序计算）
3
自动输入数据
自动填充鼠标拖动填充柄向下、右拖数字 增量为1，向上、左拖增量为-1 .
自定义序列 ① 工具——选项——新序列——输入序列 内容，每个序列间按回车键——添加 ② 选中序列区域——工具——选项——导 入——确定
4
5
公式的使用
用公式计算 所有的公式运算均以“=”开头。错误的公式以#开头。 （1）数学运算符： +、-、*、/、^、%（计算准则：先乘方，再乘、除，
6
函数的引用
手工输入：在编辑栏或单元格内输入 引用单元格地址:
7
相对引用
利用单元格间的相对位置关系引用单元格内容。公式中 的相对引用随单元格的移动而修改，但原来的位置不变。
例：打开文件“数据编辑”，在工作表“电视机的销售 统计表” 的G3中输入“=E3*F3，则可在G4、G5、G6、 G7、G8中填入相应公式来计算商品的销售金额。 绝对引用
(1)函数以公式的形式出现，请在函数名称前面 键入等号（=）。
如： =sum(A1,A4:A10,56)
18
（2）使用函数向导插入函数：按fx即可调入粘贴函数 对话框
19
例1：已知某班级学生成绩，统计其中某科成 绩为80分（含）-90分（不含）的人数。
例2：计算 e 2
例3：计算 2的ln3值。
Excel基本知识 Excel在数学建模中的高级应用
技巧
1
Excel基本知识
手动输入数据 自动输入数据 公式的使用 函数的引用 数据管理与分析
2
手动输入数据
文本输入默认方式为“左对齐”.由数字组 成的字符串前加一个’号 .
数值输入默认“右对齐”方式.输入分数时， 要先输入“0”和空格，否则作为日期型数据 处理。
11
3 排序数据
排序依据：
主要关键字 第二关键字 第三关键字
排序选项：
自定义排序次序 区分大小写 排序方向 排序方法
12
4 筛选数据
筛选种类：自动筛选、高级筛选 自动筛选方式：
前10个
自定义
13
5 分类汇总数据
汇总步骤
按汇总依据字段排序 指定汇总依据字段 设定汇总方式 指定汇总字段
1
例4：求矩阵
A
1
2
3
1 0 1
2 的2 逆2 矩 阵
2 2 1
1
5
3
注：鼠标先选中一块4*4区，先按F2键，再同时按下Shift+Ctrl+Enter三个 键，则选定区域内出现逆矩阵的计算结果。
20
例5：利用自定义函数进行计算
（1）当x=3,2,1,0,-1,-2,-3时，计算分段函数 xsin的x,值x 。0
（2）用y 迭e代x c法osx求,x非线0性方程x-cosx=0的数 值解，迭代公式是xk=cos(xk-1)，取x0=1, 试用Excel计算，要求精度达到10-12。
（3）利用公式
1 1 1 2 1 2 3 1 2 3 * 4
2 3353573579
计算 的近似值，使误差小于10-14。