Excel在数学建模中的应用实例

Excel在数学建模中的应用实例

一、Excel 基础

1、自动填充公式函数等

例1.1：自动填充编号：病例数据的统一编号

例1.2：自动填充实现复制

例1.3：自动填充生成序列：等差、等比

例1.4：利用函数计算2

e-

ln3.

例1.5：利用函数求逆矩阵及矩阵转置、数乘矩阵、矩阵和、积、行列式等运算

如已知

1101

1222

2221

3153

A

⎡⎤

⎢⎥

⎢⎥

=

⎢⎥

-

⎢⎥

-

⎣⎦

，求A’,A-1

解：

A’：选择性粘贴，或用transpose()函数（可在名称框中输入范围以选中结果区域，然后填入公式=transpose(原矩阵区域)，再按Ctrl+Shift+Enter即可）A-1：Excel中输入矩阵，在结果矩阵第一行一列处输入=MINVERSE(原矩阵区域)，结果只显示一个数字，用鼠标选中结果区域，先F2，后Ctrl+Shift+ Enter.

A+B：用自动填充，或选中结果区域，在第一格输入公式后Ctrl+Enter

n*A：用自动填充+绝对引用，也可同上

A/n：用自动填充+绝对引用，也可同上

A*B：选中结果区域，用MMult函数后Ctrl+Shift+ Enter

求矩阵行列式的值：用MDETERM函数

用矩阵运算解方程组：未知数X等于系数矩阵的逆矩阵和Y向量的乘积.

例1.6：用公式计算

1121231234

1...

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,0

cos,0

x

x x x

y

e 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 ，如此可发现每半年结算一次获利比一年结算一次多，某储户就想是不是每季度、每月、每半月。。。。，他发现：活期存款存期越短获利越多，假如可以按小时甚至按分钟结算，该储户是否可以发大财？

解：n 为一年中的结算次数，a k 为第k 次结算时的本息，a 0＝20000是首次存入的全金，则结算次数分别为1,2,4,12,24,365,8760…时的利息为。。。。

例1.9：奖金问题

某公式给员工发奖金，奖金与销售额挂钩（按销售额的一定比例提成），另一方面还与其他指标挂钩（提成比例分成三等：一等1.5%，二等1%，三等0.5%） 请计算销售额为2000、3000、…、6000时的三种等级的应发奖金数。

2、 相关数学函数的使用

例1.10：求排列数、求组合数、求阶乘.

Permut(个数n,选取数k)

Combin(个数n,选取数k)

Fact(n)

二、 线性规划

例2.1：

三、 求解方程

1、 线性方程的求解

解方程组：

例3.1: 解二元一次方程组211313x y x y +=⎧⎨+=⎩

例3.2：解方程组：

求解高次方程：

例3.3: 求解高次方程654322433420x x x x x x +-+-++=

2、 非线性方程的求解

例3.4： 用迭代法求非线性方程x-cosx=0的数值解，迭代公式是x k =cos(x k-1),取x 0=1，试用Excel 求解，要求精度达到10-12.

四、 统计

1、 计数与求和

例4.1：统计票数

例4.2：统计每类个数

例4.3：求和、汇总

2、 均值与最值

例4.4: 求平均/期望

例4.5: 求最大值、最小值

3、 筛选与排序

例4.6: 从一系列数据中筛选出符合某个条件的数据.

低级筛选和高级筛选

例4.7：对某系列数据排列以找出最优值.

4、 描述统计

例4.8： 统计某炼钢厂的120炉钢中的Si 含量，已知有如下数据：

。。。。。

解：

①、安装“数据分析”工具包：点击“工具”－>“加载宏”，在弹出的对话中选择“分析工具库”、“规划求解”、“与Access 链接”等项目，“确定”，如果需要，将Office 光盘放入光驱，然后按提示进行安装。

②、输入这120个数据

③、工具——>数据分析——>描述统计

5、 分类汇总与数据透视表

例4.9：按科室分类汇总工资.

例4.10：按地点和油类等汇总销售额.

6、 其他统计应用

例4.11：国有资产的核账

五、 作图

例5.1：分布图

例5.2：直方图

例5.3：函数曲线图

画出22sin ln(1)y x x =-+在区间48x -≤≤内的图像，设步长为0.1

使用无数据点平滑线散点图

六、 其他建模应用如VBA

用其他算法进行统计