初始化电子表格,计算顺序 [英] initializing a spreadsheet, order of calculation
问题描述
= 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屋!