投稿
  1. 小新网首页
  2. 投稿

excel函数公式技巧应用案例，Excel函数的应用

运维笔记ywbj • • 投稿

在自然数幂求和-计算量风暴（二）第七节与自然数求和周边-数列（二） 第四节中均涉及到通过待定系数法构造多元一次方程组，求解方程组。文中提及利用excel进行求解。

本期继续说明几个Excel函数的应用。

一、理论基础

一般地，n个未知数需要n个线性方程构造线性方程组，进行求解。形如：

excel函数公式技巧应用案例，Excel函数的应用

基本思路为通过“代入”或“加减”进行消元。也可利用矩阵方程来解。事实上，手算矩阵方程时，是将矩阵变换为上三角矩阵或下三角矩阵，类似“进行消元”的过程。方程组中未知数的系数构成系数矩阵A，未知数矩阵为X，常数矩阵为B。这样构成最简单的矩阵方程，即：。其中：

excel函数公式技巧应用案例，Excel函数的应用

矩阵方程，简单说就是未知数为矩阵的方程。当系数矩阵是方阵时，先判断是否可逆。如果可逆，则可以利用左乘或右乘逆矩阵的方法求解未知矩阵。

当矩阵A为n阶可逆矩阵，则对于任意的，矩阵方程 有唯一解 :。

二、相关Excel函数

矩阵是一组数值，在excel中用到数组类函数。在excel中搜索：

excel函数公式技巧应用案例，Excel函数的应用

MINVERSE函数

作用：返回数组矩阵的逆距阵。

语法：MINVERSE(array)。参数Array必需。

说明：行数和列数相等的数值数组。如果行数和列数不相等，则返回错误值 #VALUE!。

对于结果为数组的公式，必须以数组公式的形式输入。

对于不可逆矩阵，则返回错误值 #NUM!。

MMULT函数

作用：返回两数组的矩阵乘积。

语法：MMULT(array1, array2)。参数array1, array2是要进行矩阵乘法运算的两个数组。

说明：array1的列数必须与array2的行数相同。

若array1的列数与array2的行数不相等，则返回错误值 #VALUE!。

三、利用excel求方程组的解

以自然数求和周边-数列（二） 第四节中需求解的方程组为例，说明函数用法：

excel函数公式技巧应用案例，Excel函数的应用

有ABCDEF6个未知数。系数矩阵很有规律，非常友好。

excel函数公式技巧应用案例，Excel函数的应用

Excel中新建工作表，设置辅助行（列），方便界定数据量。输入系数矩阵每一个值，在单元格B4中填入：=POWER(ROW()-3,7-COLUMN())，下拉填充，右拉填充，得到系数矩阵。

在I列依次填入常数项。

excel函数公式技巧应用案例，Excel函数的应用

选中单元格区域B12：G17，注意要选中单元格区域，选中后直接键入内容，不需多余操作，输入：=MINVERSE(B4:G9)，此时，同时按下Ctrl+Shift+Enter键。B12：G17单元格区域将会自动填充，这就是数组计算的结果（系数矩阵的逆矩阵）。

excel函数公式技巧应用案例，Excel函数的应用

选中单元格区域K4：K9，选中后直接键入内容，输入：=MMULT(B12:G17,I4:I9)，此时同样的操作，同时按下Ctrl+Shift+Enter键。K4：K9单元格区域将会自动填充，这时数组计算的结果即为未知矩阵X。这时观察公式的编辑栏，公式的外边多了一对大括号{ }，数组计算的标识。

excel函数公式技巧应用案例，Excel函数的应用

于是，求得方程组解为：

excel函数公式技巧应用案例，Excel函数的应用

以上编写的计算公式，只适用于未知数为6个的情况。当试图求解5个未知数时，删除一行或一列，数组计算结果报错。

四、探讨如何编写一个可以多次使用的解方程算法。查找函数界面：

excel函数公式技巧应用案例，Excel函数的应用

OFFSET函数

作用：以指定的引用为参照系，通过给定偏移量、行数及列数返回一个新的引用。

语法：OFFSET(reference,rows,cols,height,width)

各参数如下：

参数Reference：作为偏移量参照系的引用区域。Reference须为单元格或相连单元格区域。

参数Rows：相对于偏移量参照系的左上角单元格，上（下）偏移的行数。

参数Cols： 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

参数Height：高度，要返回的引用区域的行数。

参数Height：宽度，要返回的引用区域的列数。

说明：返回的引用可以为一个单元格或单元格区域。

OFFSET函数只是返回一个引用 ，实际上并不更改选定区域。

提供思路：预先设定行数和列数较多的数组，求解n元一次方程组时，利用OFFSET函数只引用单元格区域。

1、说干就干。 先试着对举例中的系数矩阵引用的单元格区域。此时， 参照系的引用区域为单元格区域B4:G9；不进行偏移，Rows=Cols=0；要返回的引用区域的行数与列数为3，Height=Height=3。

选中单元格区域B12:D14，选中后直接键入内容，输入：=OFFSET(B4:G6,0,0,3,3)，返回的是数组，故依旧同时按下Ctrl+Shift+Enter键。单元格区域B12:D14自动填充。成功引用系数矩阵的数据。

excel函数公式技巧应用案例，Excel函数的应用

对所得到的矩阵利用MINVERSE函数求逆矩阵，常数矩阵利用OFFSET函数引用前3行。利用MMULT函数进行矩阵乘法运算，即可得到的未知数矩阵。

excel函数公式技巧应用案例，Excel函数的应用

2、着手编制多元一次方程计算器

Excel中新建工作表，设置辅助行（列），方便限定数据量。现在先试着建立大小的系数矩阵。在单元格B3中填入：=COLUMN()-1，向右填充，达到50时，为AY列。

在单元格A4中填入：=ROW()-3。在页面左上角的名称框中输入A53，回车，光标即可跳转单元格A53。点击单元格A53后，返回页面最前端，按住Shift键，点击单元格A4，选中单元格区域A4：A53，同时按下Ctrl+D，单元格区域A4：A53自动填充。

在系数矩阵区域填充数字，随便填充。（如若填充的矩阵不可逆，在计算逆矩阵时会自动报错）。以单元格B3为初始位置的 矩阵，单元格区域为B3：AY53。这样，随便在单元格B4填入一个公式：=0.1*ROW()+0.2*COLUMN()。在名称框中输入AY53，光标会跳转单元格AY53。返回页面最前端，按住Shift键，点击单元格B4，会选中单元格区域B4：AY53。同时按下Ctrl+R，4行会自动填充，再同时按下Ctrl+D，单元格区域B4：AY53会自动填充。

excel函数公式技巧应用案例，Excel函数的应用

为了界面简洁，隐藏部分单元格区域。设定单元格D1为n值，这里设置为5。

计算逆矩阵。以单元格B55为初始位置的 矩阵，单元格区域为B55：AY104。选中单元格区域B55：AY104，输入：=MINVERSE(OFFSET(B4:AY53,0,0,$D$1,$D$1))，同时按下Ctrl+Shift+Enter键进行确认。由于要对单元格D1进行绝对引用，需用$符号进行固定，快捷键F4。

常数矩阵随便填入，这里填入自然数序列。

未知矩阵尺寸为，设定单元格区域为BA4：BA53。选中单元格区域BA4：BA53，输入：=MMULT(OFFSET(B55:AY104,0,0,$D$1,$D$1),OFFSET(BA4:BA53,0,0,$D$1,1))。同时按下Ctrl+Shift+Enter键进行确认。

excel函数公式技巧应用案例，Excel函数的应用

这样，n元一次方程组的计算器编制完成。使用时，在单元格D1内键入未知数个数；在系数矩阵内依次填入未知数系数；在常数矩阵内依次键入常数项，即可。

3、以递推公式和特征方程-数列（三）中举例8中的方程组为例。

需解方程组：

excel函数公式技巧应用案例，Excel函数的应用

有XYZUVW，6个未知数，n=6。系数矩阵与常数矩阵为：

excel函数公式技巧应用案例，Excel函数的应用

填入多元一次方程组计算器，会自动获得方程组的解。注意：矩阵中超出6*6的数据忽略，不必理会；逆矩阵在这里只起到中间量作用，不需操作。

excel函数公式技巧应用案例，Excel函数的应用

于是方程组的解为：

excel函数公式技巧应用案例，Excel函数的应用

逆矩阵中返回错误值 #VALUE! 时，进行检查，系数矩阵中有空单元格或填入了非数值。

逆矩阵中返回错误值 #NUM! 时，说明系数矩阵为不可逆矩阵，方程组不可解。

五、本期涉及到的内容有：

线性方程组矩阵矩阵方程MINVERSE函数MMULT函数ROW函数COLUMN函数POWER函数OFFSET函数

本文来自作者:运维笔记ywbj，不代表小新网立场！

转载请注明：https://www.xiaoxinys.cn/105527.html

赞 (1)
运维笔记ywbj运维笔记ywbj注册用户
0
上一篇 2022-06-29 21:15:00
下一篇 2022-06-29 21:21:00

相关推荐

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。