在给定的区间内搜索函数的最大值或最小值及自变量的取值
董占山
该函数具有十分强大的功能，给定任意一个多项式方程，并给定各个自变量的取值区间及步长，就可以自动搜寻多项式方程的极值。函数的用法：
MinMax(n As Integer, X As Object, b As Object, Optional isMax As Boolean = True)
其中，n是自变量的个数,X为各个自变量的取值区间和步长值,b为多项式的系数矩阵,isMax是一个布尔变量，默认值是true,表示计算最大值，如果计算最小值，将其设置为false即可。
函数的返回值是一个一行多列的数组，列数等于自变量个数加1。第一个返回值是函数的极值，其后的数据为自变量在函数极值时的取值。
本文所附的EXCEL文件含有宏命令，当你使用EXCEL打开文件时，请接受宏，这样你就可以
运行程序了。
Option Explicit
Option Base 1
' search the minimum or maximum value of an equation in a specific range
Function MinMax(n As Integer, X As Object, b As Object, Optional isMax _
As Boolean = True)
' n - the number of variables
' X - is a n-by-3 matrix, first column is the initial values for each variable
' second column is the end values for each variables
' third column is the step size for each variables
' b - n+1-by-n+1 matrix, the coeficients for polynomial equation
' column 1 - constant, column 2 - x1, and so on
' row 1 - constant, row 2 - x1, ans so on
' isMax - a boolean variable to indicate calculate the maximum or minimum
'
' output variable - a row vector with n+1 values
' first value - maximum or minimum value of the equation
' the remaining values - the values for all xs at maximum or minimum of y
'
Dim i, j, l, y
ReDim myMinMax(n + 1)
If isMax Then
myMinMax(1) = -1E+100
Else
myMinMax(1) = 1E+100
End If
Dim xtemp, TotalN
ReDim tempint(n), xn(n), xminmax(n)
TotalN = 1
For i = 1 To n
tempint(i) = 1
Next
For i = 1 To n
xn(i) = Round((X(i, 2) - X(i, 1)) / X(i, 3)) + 1
TotalN = TotalN * xn(i)
Next
For j = 1 To n
For i = j + 1 To n
tempint(j) = tempint(j) * xn(j)
Next
Next
ReDim gridx(TotalN, n)
For i = 1 To TotalN
j = 1
Do While j <= n
gridx(i, j) = X(j, 1) + X(j, 3) * (((i - 1) \ tempint(j)) Mod xn(j))
j = j + 1
Loop
Next
For l = 1 To TotalN
y = 0
For i = 1 To n + 1
For j = i To n + 1
If i = 1 Then
If j = 1 Then
y = y + b(i, j)
Else
y = y + b(i, j) * gridx(l, j - 1)
End If
Else
y = y + b(i, j) * gridx(l, j - 1) * gridx(l, i - 1)
End If
Next
Next
If isMax Then
If y > myMinMax(1) Then
myMinMax(1) = y
For i = 1 To n
myMinMax(i + 1) = gridx(l, i)
Next
End If
Else
If y < myMinMax(1) Then
myMinMax(1) = y
For i = 1 To n
myMinMax(i + 1) = gridx(l, i)
Next
End If
End If
Next
MinMax = myMinMax
End Function
下载EXCEL工作表
©董占山Zhanshan Dong
|
