初始化电子表格,计算顺序 [英] initializing a spreadsheet, order of calculation

查看:129
本文介绍了初始化电子表格,计算顺序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我已经写了一个VBA代码,它解决了一组代数方程,其系数矩阵是Tridiagonal(称为Thomas算法)。 (i)X(i-1)+ B(i)X(i)+ C(i)X(i + 1)的形式



= R(i)



A,B,C和R的值传递给返回值X的函数。VBA代码如下。功能TRIDI(ByVal Ac As Range,ByVal Bc As Range,ByVal Cc As Range,_
ByVal Rc As Range)As Variant
Dim BN As Single
Dim i As Integer
Dim II As Integer
Dim A()As Single,B()As Single ,C()As Single,R()As Single,X()As Single
N = Ac.Rows.Count
ReDim A(N),B(N),C(N),R (N),X(N)
对于i = 1到N
A(i)= Ac.Parent.Cells(Ac.Row + i - 1,Ac.Column)
B (i)= Bc.Parent.Cells(Bc.Row + i - 1,Bc.Column)
C(i)= Cc.Parent.Cells(Cc.Row + i - 1,Cc.Column)
R(i)= Rc.Parent.Cells(Rc.Row + i - 1,Rc.Column)
下一个i
A(N)= A(N)/ B(N)
R(N)= R(N)/ B(N)
对于i = 2到N
II = -i + N + 2
BN = 1 /(B (II-1) - A(II)* C(II-1))
A(II - 1)= A(II - 1)* BN
R(II-1)=(R(II-1)-C(II-1)* R(II))* BN
下一个i
X(1)= R(1)
对于i = 2到N
X(i)= R(i) - A(i)* X(i - 1)
下一个i
TRIDI = Application.WorksheetFunction。转置(X)
结束函数

该函数对于线性方程式很正常。如果方程是非线性的,例如对于以下三个方程式,



X(1)= 1



(1)-2X(2)+ X(3)= 3 + X(1)^ 2



X(1)+ X(3)= 2

使用Excel的迭代功能迭代获得解决方案,如下所示。 (我还没有发布图片,因为这是我的第一篇文章,如果你给我发电子邮件,bumedoc@gmail.com,我可以给你发送电子表格)

  ABCDE 

1 ABCRX

2 0 1 0 = 1 = TRIDI(A2:A4,B2:B4,C2:C4,D2 :D4)

3 1 -2 1 = 3 + E2 ^ 2 = TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)

4 1 1 0 = 2 = TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)

然而,保存电子表格并重新打开后,我得到#value! E2错误:E4和D3。我假设在打开电子表格时,Excel会尝试初始化具有循环引用的单元格,并使其混淆。有没有人能解决这个问题?

解决方案

替换 = 3 + E2 ^ 2 = IFERROR(3 + E2 ^ 2,0)适用于我。


I have written a VBA code that solves a set of algebraic equations whose coefficient matrix is Tridiagonal (known as Thomas algorithm). The equations have the form

A(i)X(i-1)+B(i)X(i)+C(i)X(i+1)=R(i)

The values of A, B, C, and R are passed to the function that returns the values of X. The VBA code is below.

Option Base 1
Function TRIDI(ByVal Ac As Range, ByVal Bc As Range, ByVal Cc As Range, _
ByVal Rc As Range) As Variant
Dim BN As Single
Dim i As Integer
Dim II As Integer
Dim A() As Single, B() As Single, C() As Single, R() As Single, X() As Single
N = Ac.Rows.Count
ReDim A(N), B(N), C(N), R(N), X(N)
For i = 1 To N
A(i) = Ac.Parent.Cells(Ac.Row + i - 1, Ac.Column)
B(i) = Bc.Parent.Cells(Bc.Row + i - 1, Bc.Column)
C(i) = Cc.Parent.Cells(Cc.Row + i - 1, Cc.Column)
R(i) = Rc.Parent.Cells(Rc.Row + i - 1, Rc.Column)
Next i
A(N) = A(N) / B(N)
R(N) = R(N) / B(N)
For i = 2 To N
II = -i + N + 2
BN = 1 / (B(II - 1) - A(II) * C(II - 1))
A(II - 1) = A(II - 1) * BN
R(II - 1) = (R(II - 1) - C(II - 1) * R(II)) * BN
Next i
X(1) = R(1)
For i = 2 To N
X(i) = R(i) - A(i) * X(i - 1)
Next i
TRIDI = Application.WorksheetFunction.Transpose(X)
End Function

The function works fine for linear equations. If the equations are nonlinear, for example for the three equations below

X(1)=1

X(1)-2X(2)+X(3)=3+ X(1)^2

X(1)+X(3)=2

The solution is obtained iteratively using Excel’s iterative functionality, as shown below. (I can't yet post images, since this is my first post. If you email me, bumedoc@gmail.com, I can send you the spreadsheet too)

     A    B    C     D                  E

1    A    B    C     R                  X

2    0    1    0     =1          =TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)

3    1   -2    1     =3+E2^2     =TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)

4    1    1    0     =2          =TRIDI(A2:A4,B2:B4,C2:C4,D2:D4)

However, after saving the spreadsheet and opening it again, I get #value! Error in E2:E4 and D3. I assume that upon opening the spreadsheet, Excel tries to initialize the cells that have circular reference and gets confused. Does anyone have a fix for this problem?

解决方案

Replacing =3+E2^2 with =IFERROR(3+E2^2,0) works for me.

这篇关于初始化电子表格,计算顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆