EXCEL在数学建模中的应用
许多人对EXCEL的数据计算功能不了解，仅把它当作制作表格和图表的办公软件。用它不需编程就能够实现其他软件需要编程才能完成的复杂计算，能够进行各种数据统计、运算、处理和绘制统计图形，只要善于开发，一定能够在数学建模中发挥出更大的作用。
一、EXCEL的数据处理功能
EXCEL擅长数据统计，用它来处理数据能够节省大量时间，提高效率。 EXCEL的数据处理功能主要有两大块： 1）计算功能
它提供了300多个内部函数供用户使用，还充许自定义函数。当大批数据都要用同一公式计算时，只要用鼠标拖动而不需要编程。
2）数据分析功能
EXCEL提供了“数据分析”工具包，内含方差分析、回归分析、协方差和相关系数、博立叶分析、t检验等分析工具。
（一） Excel的函数
Excel提供了12类（有常用、财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息、工程、用户定义）共300多个内部函数，其中用得比较多的是常用、统计和数学与三角函数类中的函数。
函数由函数名、参数组成。不同函数对其参数要求不同，若参数为数值，则可用单元格取代，有些函数的参数是多个数据，则可用区域取代，有些函数的参数是矩阵，则可用矩形区域取代。
① 常用函数
当插入函数对话框的选择类别中显示“常用函数”时，共有十多个函数供选择，它们的功能和参数如表1所示。
表1 Excel常用函数
函数名 EXP SUM LN COUNTIF AVERAGE IF COUNT MAX SIN SUMIF HYPERLINK 计算ex 求和 求自然对数lnx 统计满足某种条件的数据个数 求算术平均值 由条件决定返回值 统计个数 求最大值 正弦 满足某种条件的所有数据的和 创建一个快捷方式或链接 功 能 参 数 任意实数 数组，如A2：A10 正实数 数据区域和条件 数组 一个条件，两个结果 数组 数组 以弧度表示的角度 数据区域和条件 路径和文件名、标识符 ② 数学与三角函数
这些是数值计算时常用到的函数。在插入函数对话框中选择数学与三角函数，则显示出58种函数供选择，其中常用的函数见表2所示。
表2 Excel数学与三角函数
1
函数名 三角函数SIN,COS,TAN 反三角函数ASIN,ACOS,ATAN 双曲函数SINH,COSH,TANH 反双曲函数ASINH,ACOSH,ATANH POWER EXP SQRT LOG LOG10 LN ABS FACT COMBIN MDETERM MINVERSE MMULT SUMSQ MOD PRODUCT PI DEGREES RADIANS LCM GCD RAND RANDBETWEEN SUMXMY2 SERIESSUM SIGN 功 能 求三角函数值 求反三角函数值 求双曲函数值 求反双曲函数值 x的y次方 ex x的平方根 给定底的对数 10为底的对数 自然对数 x的绝对值 计算阶乘 r组合数Cn 求行列式的值 参 数 以弧度表示的角度 定义域内的数 实数 定义域内的实数 两个数x和y 数x或单元格 同上 真数和底数 真数或单元格 真数或单元格 数x或单元格 整数n 求矩阵的逆 两个矩阵相乘 计算平方和 整除求余数 连乘积 圆周率 弧度转换成度 度转换成弧度 最小公倍数 最大公约数 0-1之间均匀分布随机数 两个数之间的随机数 两个数组对应数值的平方和 求幂级数的和 符号函数 n和r两个整数 n行n列数据 n行n列数据 两个矩阵数据 数组（向量） 两个整数 若干个数 无 弧度 度 若干个数 若干个数 无 两个数 两个数组 满足要求的四个数 实数 还有一些舍入或取整函数没有一一列出，如INT，功能是向下取整。 例1 计算e。
例2 计算2?ln3的值。
?2?11?12例3 求矩阵A???2?2??3?102251??2?的逆矩阵。 1??3? 2
【作法】插入→函数→数学与三角函数→MINVERSE→A1：D4→确定
然后再在插入函数的区域仅出现一个-4，若要显示全部逆矩阵，则以插入函数的单元格（如上例的A7）为开始，选择一个和原矩阵A大小一样的区域（如A7：D10），再按F2，再同时输入Shift+Ctrl+Enter，则在选定的区域出现逆阵的计算结果。 【注】MMULT函数的用法同上，显示时也要选区域，再按F2，再同时输入Shift+Ctrl+Enter ③ 统计函数
Excel2003有80种统计函数供选择使用，其中常用的如表3所示。
表3 Excel中常用的统计函数
函数名 AVERAGE VAR VARP STDEV STDEVP DEVSQ AVEDV NORMSDIST NORMDIST NORMINV NORMSINV CHIDIST CHINV 求算术平均值 样本方差(修正) (?xi?nx)(n?1) i?1n22功 能 参 数 n个数 n个数 n个数 n个数 n个数 n个数 总体方差(?xi?nx)n i?1n22VAR的平方根 VARP的平方根 ?(xi?x)??xi2?nx2 i?1i?1n2n?x?xn 标准正态分布的分布函数值 正态分布，1：返回分布函数，0：返回概率密度 正态分布概率为时的的值 标准正态分布由得 n个数 数x x,?,?,1或0 ?,?,?, ? x，自由度n ?2分布P(X?x) ?2分布由?查x 3
?，n
CHITEST POISSON 两组数据同分布的概率 泊松分布，0：返回对应k的概率；1：返回累积概率 kkn?k二项分布，0：返回的概率值Cnpq；1：返回累两组数据 k,?，1或0 k,n,p，1或0 x,?，1或0 x,n，1或0 BINOMDIST 积概率 EXPONDIST TDIST TINV FDIST FINV CONFIDENCE COVAR CORREL FTEST CRITBINOM SLOPE INTERCEPT 指数分布，1：返回分布函数值；0：返回概率密度 t分布，1：返回分布函数值；0：返回概率密度 t分布满足P(T??)的T值 F分布满足P(F??)的F值 由?查F分布临界值 总体均值的置信区间（半长度） 协方差 相关系数 两组数方差相等的概率 二项分布的临界值（分位数） 线性回归y?a?bx中的b 线性回归y?a?bx中的a 多元线性回归y?a??bixi，c?0时强制a?0，?，n x,n1,n2 ?,n1,n2 ?,?,n（数据个数） 两组数 两组数 两组数据 n,p,? 两组数 两组数 数组y，多维数组x，以及逻辑值c,s 同上 LINEST c?1时返回附加回归统计值 LOGEST GEOMEAN HARMEAN MIN 指数回归y?b?mi中的b,mi i?1kxi几何平均数 调和平均数（倒数平均值的倒数） n个数中的最小值 n个数 n个数 n个数 以上概率统计函数中，有些函数名有一定规律，凡是后四个字母为DIST的函数，功能是返回某种分布的分布函数值或概率密度值（根据参数逻辑值1或0决定，为1时返回分布函数值或累积概率，为0时返回概率密度或分布律的值）；如果函数名称后几个字母为INV，它们是对应DIST函数的反函数，功能是给定概率反查自变量的值。
④ 自定义函数
实际计算时库函数有时不能完全满足用户的需要，须自己定义函数，称为自定义函数。任何一个计算软件，如果不具备允许用户按自己的意愿定义函数的功能，则该计算软件的使用范围很有限。
Excel允许用户自己定义任意带参数的函数，方法是：把光标放在空白处（点击空白格子）选输入一个等号“=”，然后输入自定义函数的表达式。表达式可由常数、变量、内部函数和运算符组成，其中运算符包括算术运算符（-，*，/，^，%，＋，－）、比较运算符（=，
4
<，>，<=，>=，<>）和连接符&。
例4 当x?3,2,1,0,?1,?2,?3时，计算分段函数y???xsinx, x?0?ecosx, x?0x的值。
【步骤】1） 选一个空白列（如A列），输入自变量x的值，在第一行（A1）输入3，点击第二行（A2），输入A1-1，再点击其他单元格，则A2显示计算结果2，再点击A2单元格，则它的边框出现加粗的黑色且右下角有一黑点。用鼠标拉着该黑点向下拖动一直到A7单元格（复制公式AX-1），放开鼠标，则A3至A7单元格内的计算结果依次显示为
1,0,?1,?2,?3。
〖注〗用上方法复制公式时，如果公式中的自变量是单元格的名称（编号），则随着拖动，公式的自变量作相应的变化，其变化规律是：纵向拖动公式时，行号变而列号不变；横向拖动公式时，列号变而行号不变。
2）选另外一列，如B列，点击第一行单元格B1，输入=IF(A1>0,A1*SIN(A1),EXP(A1)*COS(A1))。鼠标点击其他单元格，则B1单元格显示自定义函数的计算结果，再点击B1单元格，拉着它的边框右下角的黑点，向下拖动到B7放开，则B1至B7单元格依次得到自定义函数当自变量分别为A1至A7时的值。
⑤ 利用自定义函数完成较复杂的计算
表达式（自定义函数）可以拖动，且函数的自变量能够自动改变，我们利用该项功能就可完成大批量数据计算以及各种复杂的计算（用其他软件通常需要编程才能进行的计算），举例如下：
例5 用迭代法能求非线性方程x?cosx?0的数值解，迭代公式是xk?cos(xk?1)，取x0?1，试用Excel计算，要求精度达到10?12。
解：在空白列（如A列）的第一位置处输入初始值1，点击该单元格同行的下一行（A2）单元格，输入=COS（A1），得到计算结果0.540302306，然后向下连续拖动黑边框右下角的黑点，产生的效果是按迭代公式Ak?COS(Ak?1)不断进行迭代，放开鼠标就能看到计算结果，此时单元格内显示的数字格式为小数点后9位，A55后数字不再变化，说明迭代55次之后计算结果的精度达到10。为了显示小数点后面更多位数，先选择该列从A2开始的单元格，然后从主菜单选择格式→单元格→弹出对话框单元格格式→点数字栏目，选数值→把小数位数栏目内的数字改为16→确定 〖注〗Excel的计算精度通常最多能有16位有效数字，继续增加小数点后的位数将无效。
由本例可见，Excel用于较复杂计算有两大优点：
1）不需要编写程序，这对于不熟悉编程，但急需计算的人员比较实用； 2）显示结果比较直观，能看见中间结果，便于数据分析。
例6 利用公式
?9?2?1?1121231234????3353573579计算?的近似值，使误差小于
10?14。
5