締切済み

お初に質問させていただきます。最近、Excelのマクロを始め、下のよう

2010/04/27 00:13

お初に質問させていただきます。最近、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