お初に質問させていただきます。最近、Excelのマクロを始め、下のよう
お初に質問させていただきます。最近、Excelのマクロを始め、下のようなユーザー定義関数を作成し無事動作を確認しました。そして、他の方法で実測した任意の時刻における値との差の二乗の総和が最小になるようにK(1),K(2)の値を最適化したいと思い、最小二乗の総和をワークシート上に計算し、Excelのツールのソルバーを用いて最適化を試みました。しかし、「最適解は見つかりました」と出るのですが、値に全く変化はなく最適化できていません。ユーザー定義関数を含むとソルバーは使えないのですか？もし何か解決法があれば教えてください。私が使っているExcelはExcel 2003です。
Public Function RK1(成分, 時刻かける20) As Double
Application.Volatile
Dim Koc As Double, Kco As Double, Eo As Integer, Ec As Integer, Y0(2) As Double
Dim Y(2) As Double, K1(4) As Double, K2(4) As Double, K3(4) As Double
Dim K4(4) As Double, T As Double
Dim K(2) As Double, DelY(2) As Double
Dim Tt(1500) As Double, TV(2, 1500) As Double
For I = 1 To 2
Y0(I) = Cells(3 + I, 2).Value
Const nn As Integer = 20
dx = 1# / nn
T = 0#
K(1) = Cells(2, 2).Value
K(2) = Cells(3, 2).Value
Next I
For J = 1 To 300
Y(1) = Y0(1)
Y(2) = Y0(2)
K1(1) = F1(T, Y(1), Y(2)) * dx
K1(2) = F2(T, Y(1), Y(2)) * dx
'
Y(1) = Y0(1) + 0.5 * K1(1)
Y(2) = Y0(2) + 0.5 * K1(2)
K2(1) = F1(T, Y(1), Y(2)) * dx
K2(2) = F2(T, Y(1), Y(2)) * dx
'
Y(1) = Y0(1) + 0.5 * K2(1)
Y(2) = Y0(2) + 0.5 * K2(2)
K3(1) = F1(T, Y(1), Y(2)) * dx
K3(2) = F2(T, Y(1), Y(2)) * dx
'
Y(1) = Y0(1) + K3(1)
Y(2) = Y0(2) + K3(2)
K4(1) = F1(T, Y(1), Y(2)) * dx
K4(2) = F2(T, Y(1), Y(2)) * dx
'
DelY(1) = (K1(1) + 2# * K2(1) + 2# * K3(1) + K4(1)) / 6#
DelY(2) = (K1(2) + 2# * K2(2) + 2# * K3(2) + K4(2)) / 6#
T = T + dx
Y0(1) = Y0(1) + DelY(1)
Y0(2) = Y0(2) + DelY(2)
Tt(J) = T
TV(1, J) = Y0(1)
TV(2, J) = Y0(2)
Next J
RK1 = TV(成分, 時刻かける20)
End Function
Function F1(T, a, b)
Dim K(2) As Double
Eo = Cells(7, 2).Value
Ec = Cells(8, 2).Value
K(1) = Cells(2, 2).Value
K(2) = Cells(3, 2).Value
F1 = (-K(1) * a + K(2) * b) * (1 - 10 ^ (-(Eo * a + Ec * b))) / (Eo * a + Ec * b)
End Function
Function F2(T, a, b)
Dim K(2) As Double
Eo = Cells(7, 2).Value
Ec = Cells(8, 2).Value
K(1) = Cells(2, 2).Value
K(2) = Cells(3, 2).Value
F2 = (K(1) * a - K(2) * b) * (1 - 10 ^ (-(Eo * a + Ec * b))) / (Eo * a + Ec * b)
End Function
