Chapter 01Excel 简单用法

1.1 EXCEL用法 Excel是Office软件中的核心成员，是优秀的电子表格软件之一，具有强大的数据处理和数据分析能力，是个人及办公事务中进行表格处理和数据分析的理想工具之一。 Excel它内置了数百个函数供用户调用，还允许用户根据自己的需要随意定义自己的函数， Excel不用编程就能够实现其他软件需要编程才能完成的复杂计算，能进行各种数据的统计、运算、处理和绘制统计图形。 在实际问题中，经常会遇到大量数据需要处理，还有各种统计图表需要绘制。Excel 擅长数据分析和统计，用它来处理数据能够节省大量时间，提高效率。

工作界面 菜单栏 工作薄 快捷键 名字框 公式栏 列号 当前单元格 工作表 行号 工作表标签

单元格的引用 Excel的基本单位是单元格。 Excel中引用单元格式的方式包括“相对引用”、“绝对引用”两种，”绝对引用”又包括通过名称来标识的引用。 相对引用：引用格式形如“A1”。这种对单元格的引用是完全相对的，当引用单元格的公式被复制时，新公式引用的单元格的位置将会发生改变。 绝对引用：引用格式形如“$A$1”。这种对单元格引用的方式是完全绝对的，即一旦成为绝对引用，无论公式如何被复制，对采用绝对引用的单元格的引用位置是不会改变的。

Excel 的数据处理功能主要有两大块: 1)计算功能 Excel有强大的计算能力，提供了300 多个内部函数给用户使用，还允许自定义函数。当大批数据都要用同一个公式计算结果时，只要用鼠标拖动而不需要编程。 2) 数据分析功能 Excel 提供了"数据分析"工具包，内含方差分析、回归分析、协方差和相关系数、傅里叶分析、t 检验等分析工具.

Excel提供了12类(类别有常用、财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息、工程、用户定义)共300多个各种内部函数，其中用得比较多的是常用、数学与三角函数以及统计类中的函数。 依次点击公式菜单，最左侧的插入函数，

函数公式的书写规则 =(100-$b$1)*sum(a1:a50) 公式特定符号 相对引用范围 绝对引用 常数 函数

例 1.1.1 计算x=f(x)=rx(1-x)的根。 利用单变量求解。 输入公式 r值 初值

分析:使用迭代关系xn+1=rxn(1-xn).

混沌的特征：对初值极度敏感。 当r=3.7时初值分别为0.2000和0.20001时在第100次迭代时分别对应值为0.4814和0.2572。

例 1. 1. 2 设y=f(x)=ax+(1-a)x2(1+x2)-1，取a=0. 4, b=0 例 1.1.2 设y=f(x)=ax+(1-a)x2(1+x2)-1，取a=0.4, b=0.99875，在Excel中画出(f(x)+by,f(f(x)+by)-x)的散点图,并观测初值点(x0,y0)，a变化时散点图变化情况。 初值 a值 f(f(x)+by)-x b值 f(x)+by

2、Excel统计分析 Excel的基础统计分析功能主要体现在众多的统计函数中，直接求得数据的统计量，如数学期望和方差等。 Excel还包涵了更复杂的数据分析功能的【数据分析】模块, 内含方差分析、回归分析、协方差、和相关系数、傅利叶分析、t检验等工具。 打开Excel工作文件—>文件—>【选项】->【Excel选项】—>【加载项】—>在【管理：】右方选择【Excel  加载项】—>【转到】—>【加载宏】—>勾选【分析工具库】—>【确定】； Excel2013工作文档中单击【数据】选项卡即可看到弹出的【数据分析】窗口及【数据分析】模块下包含的【分析工具】。

例1.1.3：某大型牙膏制造企业为了更好的拓展产品市场，有效地管理库存，公司董事要求销售部门根据市场调查，找出公司生产的牙膏销售量与销售价格、广告投入等之间的关系，从而预测出在不同价格和广告费用下的销售量。 销售周期 公司销售价格/元 其他厂家平均价格/元 广告费用/百万元 销售量/百万支 1 3.85 3.80 5.50 7.38 2 3.75 4.00 6.75 8.51 3 3.70 4.30 7.25 9.52 4 7.50 5 3.60 7.00 9.33 6 6.50 8.28

(1)统计性描述

(2)回归分析

越大越好 越大越好 回归系数

加入 ，对模型改进。

一元回归通常选择的六类曲线： 双曲线: 幂指数曲线: 指数曲线: 倒指数曲线: 对数曲线: S型曲线:

3、Excel规划求解功能 例1.1.4 点击Excel数据选项,从最右侧找到规划求解。 输入目标函数: =EXP(B1)*(4*B1^2+2*C1^2+4*B1*C1+2*C1+1) 输入约束条件表达式: =B1+C1 点击Excel数据选项,从最右侧找到规划求解。

目标函数 目标 自变量 约束条件

取消对勾

4、Excel数据拟合功能 Excel本身没有数据拟合功能，但可以通过一定的方法化成最优化问题，利用规则问题求解得出相应的系数 例1.1.4 2004年全国大学生数学建模竞赛C题中给出体重约70kg的某人在短时间内喝下2 瓶啤酒后， 隔一定时间测量他的血液中酒精含量[mg/(ml×102)]，得到数据见表1.1.1. 请建立饮酒后血液中酒精含量的数学模型. 表1.1.1 备注中酒精含量数据 时间t(小时) 0.25 0.5 0.75 1 1.5 2 2.5 3 3.5 4 4.5 5 酒精含量y 30 68 75 82 77 58 51 50 41 6 7 8 9 10 11 12 13 14 15 16 38 35 28 25 18

直接利用Excel的散点图功能拟合曲线。 y = -0.0009x6 + 0.0488x5 - 0.9922x4 + 9.7719x3 - 47.156x2 + 91.403x + 23.195 方法是不可取的，没有模型直接用软件，不是数学建模，而是软件应用，必须要有模型。

解 把人体内酒精的吸收、代谢、排除过程分成两个“室”，胃为第一室，血液为第二室，酒精先进入胃，然后被吸收进入血液，由循环到达体液内，再通过代谢、分解及排泄、出汗、呼气等方式排除. 假设胃里的酒被吸收进入血液的速度与胃中的酒量x(t) 成正比，比例常数为k1, 血液中的烟被排出的速度与血液内的酒量y(t) 成正比.比列系数为k2 . 建立如下微分方程模型:

这是线性常系数微分方程组，式中G0是短时间内喝人胃中的酒精总量，求解得到 如果统一用a1,a2,a3表示待定常数，则上式可以写成 由己知数据求出使 最小的待定系数a1,a2,a3 使用Lingo求出a1=114.4323 , a2=0.1855014, a3=2.007944

在Excel中输入时间与酒精含量，H3,I3,J3中输入待拟合系数的初值。 E3中输入公式：=$H$3*(EXP(-$I$3*A3)-EXP(-$J$3*A3)) 使用自动填充功能至最后一项.

为计算 ,先在F3输入公式:=(B3-E3)^2,使用自动填充功能至最后一项，然后再利用SUM把该列求和即可得。

点击规划求解:

输入优化 目标函数 选中拟合系数初值

如果有约束条件还可以进行敏感性分析等。

与LINGO求解相同.

5.Excel 在线性代数中应用 1 矩阵的界定 利用矩阵所有范围, 如：A1:C3可以定义一个3×3的矩阵. 利用定义名称：选中矩阵所在的单元格范围，然后点击右键，选择定义名称—打开新建名称对话框

2 矩阵的加减，一个数与矩阵的运算 加减运算示例： A用范围表示：A1:C3; B用范围表示: G1:I3. 选择一个与A同样大小的区域，输入：=A1:C3+G1:I3; 同时按下CRTL+SHIFT+ENTER即可得A+B的结果。 注：一定不要直接按下ENTER，=必不可少。 数与矩阵的运算：数乘矩阵与数加矩阵，两者运算相似。 假设在E1处有一数4，则4×A，选择一个与A同样大小的区域，输入：=4*A1:C3，同时按下CRTL+SHIFT +ENTER。 实际上用名称写起来更简洁一些。

3 计算行列式 计算行列式的值直接使用MDETERM函数即可。 4. 逆矩阵 求矩阵逆矩阵的函数是MINVERSE, 缺点是求得的是近似值，而非精确值。 5. 矩阵乘法 对应元素相乘：选与A同样大小的区域后，输入：=A*B(或A1:C3*G1:I3)再按下CRTL+SHIFT+ENTER. 矩阵乘法：使用函数MMULT。 6. 特征值与特征向量的计算比较复杂与且取的初值有关。

参考文献 汤在祥，高清松，徐辰武，非线性方程的Excel拟合及其应用.中国农学通报.Vol.21,No.3 2005,306-310.