任意2个求和.... 就是一个2层得循环,结果得个数是 从n个数取出不重复得2个数组合... for =0 to n-1 for j=i+1 to n-1 debug.print a(i)+a(j) next j next i 要是3个任意,就是一个3层得循环.结果得个数是 从n个数取出不重复得3个数组合...for I=0 TO N-1 for j=i+1 to n-1 for k=j+1 to n-1 debug.print a(i)+a(j)+a(k)
记从N个数中取M个数的不重复序列,取法为C(N,M),则C(n,m)=n!/m!*(n-m)! (n>=m,当n=0时n!=1) 则C(n,m)函数为Public Function C(n, m) As Long Dim i As Long, sum1 As Long If n = m or m=0 or n=0Then C = 1 Exit Function End If If n < m Then Err.Raise -1, "", "m不能大于n!" End If sum1 = 1: sum2 = 1: sum3 = 1 For i = 1 To n sum1 = sum1 * i Next For i = 1 To m sum1 = sum1 / i Next For i = 1 To n - m sum1 = sum1 / i Next C = sum1 End Function 记一个数组a的和为sumArray,函数为:Public Function sumArray(a() As Variant) As Long Dim i As Long, sum As Long For i = 0 To UBound(a) sum = sum + a(i) Next sumArray = sum End Function 则,在N个元素中,取M个数字(M<=N)的所有集合元素之和为 sumArray({N})*C(N-1,M-1) 示例: 有数组s,有五个元素{1,2,3,4,5},则任取两个得到的集合为 {1,2},{1,3},{1,4},{1,5},{2,3},{2,4},{2,5},{3,4},{3,5},{4,5} 所有集合中元素之和为: 1+2+1+3+1+4+1+5+2+3+2+4+2+5+3+4+3+5+4+5=60 即 sumArray({N})*C(N-1,M-1)=15*C(4,1)=60. 同样地,从这5个元素中任取3个所得到的集合元素之和为15*C(4,2)=90
完整测试代码如下Public Sub main() Dim s() As Variant, n As Integer, m As Long s = Array(1, 2, 3, 4, 5) n = 5: m = 3 Debug.Print sumArray(s) * C(n - 1, m - 1) '输出90 n = 5: m = 4 Debug.Print sumArray(s) * C(n - 1, m - 1) '输出60 n = 5: m = 2 Debug.Print sumArray(s) * C(n - 1, m - 1) '输出60 End Sub Public Function C(n, m) As Long Dim i As Long, sum1 As Long If n = m Or n = 0 Or m = 0 Then C = 1 Exit Function End If If n < m Then Err.Raise -1, "", "m不能大于n!" End If sum1 = 1: sum2 = 1: sum3 = 1 For i = 1 To n sum1 = sum1 * i Next For i = 1 To m sum1 = sum1 / i Next For i = 1 To n - m sum1 = sum1 / i Next C = sum1 End FunctionPublic Function sumArray(a() As Variant) As Long Dim i As Long, sum As Long For i = 0 To UBound(a) sum = sum + a(i) Next sumArray = sum End Function
再优化一下c(n,m)的算法,能算得大一点快一点 Public Function C(n, m) As Long Public Function C(n As Long, m As Long) As Long Dim i As Long, sum1 As Long, k As Long, j As Long If n = m Or n = 0 Or m = 0 Then C = 1 Exit Function End If If n < m Then Err.Raise -1, "", "m不能大于n!" End If sum1 = 1: k = m If n - m > m Then k = n - m For i = k + 1 To n j = j + 1 sum1 = sum1 / j * i '此处先除再乘 Next C = sum1 End Function
续上面的,如果你想把取2~N的所有组合中所有元素求和,就只需要一个循环 Public Sub main() Dim s() As Variant, n As Long, m As Long, sum As Long, sum1 As Long Dim t As Long s = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) '楼主要做作业的话,把这里的数组换成你想要的就行了 n = UBound(s) + 1: sum1 = sumArray(s) For m = 2 To n '这个循环还可以再优化,只需要循环一半就可以了 t = sum1 * C(n, m) sum = sum + t Debug.Print "从s数组中取" & m & "个数的所有组合元素之和为" & t Next MsgBox sum End Sub Public Function C(n As Long, m As Long) As Long Dim i As Long, sum1 As Long, k As Long, j As Long If n = m Or n = 0 Or m = 0 Then C = 1 Exit Function End If If n < m Then Err.Raise -1, "", "m不能大于n!" End If sum1 = 1: k = m If n - m > m Then k = n - m For i = k + 1 To n j = j + 1 sum1 = sum1 / j * i '此处先除再乘 Next C = sum1 End FunctionPublic Function sumArray(a() As Variant) As Long Dim i As Long, sum As Long For i = 0 To UBound(a) sum = sum + a(i) Next sumArray = sum End Function
修正一下,忘记减1了.Public Sub main() Dim s() As Variant, n As Long, m As Long, sum As Long, sum1 As Long Dim t As Long, k As Long s = Array(1, 2, 3, 4, 5) n = UBound(s) + 1: sum1 = sumArray(s) For m = 2 To n t = sum1 * C(n - 1, m - 1) sum = sum + t Debug.Print "从s数组中取" & m & "个数的所有组合元素之和为" & t Next Debug.Print sum End Sub Public Function C(n As Long, m As Long) As Long Dim i As Long, sum1 As Long, k As Long, j As Long If n = m Or n = 0 Or m = 0 Then C = 1 Exit Function End If If n < m Then Err.Raise -1, "", "m不能大于n!" End If sum1 = 1: k = m If n - m > m Then k = n - m For i = k + 1 To n j = j + 1 sum1 = sum1 / j * i '此处先除再乘 Next C = sum1 End FunctionPublic Function sumArray(a() As Variant) As Long Dim i As Long, sum As Long For i = 0 To UBound(a) sum = sum + a(i) Next sumArray = sum End Function
(1)给定数组,和任意几个元素的序号,求和:使用可变参数定义函数计算,或者传递2个数组:原数组,和煦要求和的数据下标构成的数组
(2)枚举出可能的元素组合,并求和,关键是枚举组合元素。以前写过一个C#版的代码,你可以参考:
http://topic.csdn.net/u/20110609/23/201c0b25-c3ad-4240-9f9a-71c19a758a05.html
就是一个2层得循环,结果得个数是 从n个数取出不重复得2个数组合...
for =0 to n-1
for j=i+1 to n-1
debug.print a(i)+a(j)
next j
next i 要是3个任意,就是一个3层得循环.结果得个数是 从n个数取出不重复得3个数组合...for I=0 TO N-1
for j=i+1 to n-1
for k=j+1 to n-1
debug.print a(i)+a(j)+a(k)
则C(n,m)函数为Public Function C(n, m) As Long
Dim i As Long, sum1 As Long
If n = m or m=0 or n=0Then
C = 1
Exit Function
End If
If n < m Then
Err.Raise -1, "", "m不能大于n!"
End If
sum1 = 1: sum2 = 1: sum3 = 1
For i = 1 To n
sum1 = sum1 * i
Next
For i = 1 To m
sum1 = sum1 / i
Next For i = 1 To n - m
sum1 = sum1 / i
Next
C = sum1
End Function
记一个数组a的和为sumArray,函数为:Public Function sumArray(a() As Variant) As Long
Dim i As Long, sum As Long
For i = 0 To UBound(a)
sum = sum + a(i)
Next
sumArray = sum
End Function
则,在N个元素中,取M个数字(M<=N)的所有集合元素之和为 sumArray({N})*C(N-1,M-1)
示例:
有数组s,有五个元素{1,2,3,4,5},则任取两个得到的集合为
{1,2},{1,3},{1,4},{1,5},{2,3},{2,4},{2,5},{3,4},{3,5},{4,5}
所有集合中元素之和为:
1+2+1+3+1+4+1+5+2+3+2+4+2+5+3+4+3+5+4+5=60
即
sumArray({N})*C(N-1,M-1)=15*C(4,1)=60.
同样地,从这5个元素中任取3个所得到的集合元素之和为15*C(4,2)=90
Dim s() As Variant, n As Integer, m As Long
s = Array(1, 2, 3, 4, 5)
n = 5: m = 3
Debug.Print sumArray(s) * C(n - 1, m - 1) '输出90
n = 5: m = 4
Debug.Print sumArray(s) * C(n - 1, m - 1) '输出60
n = 5: m = 2
Debug.Print sumArray(s) * C(n - 1, m - 1) '输出60
End Sub
Public Function C(n, m) As Long
Dim i As Long, sum1 As Long
If n = m Or n = 0 Or m = 0 Then
C = 1
Exit Function
End If
If n < m Then
Err.Raise -1, "", "m不能大于n!"
End If
sum1 = 1: sum2 = 1: sum3 = 1
For i = 1 To n
sum1 = sum1 * i
Next
For i = 1 To m
sum1 = sum1 / i
Next For i = 1 To n - m
sum1 = sum1 / i
Next
C = sum1
End FunctionPublic Function sumArray(a() As Variant) As Long
Dim i As Long, sum As Long
For i = 0 To UBound(a)
sum = sum + a(i)
Next
sumArray = sum
End Function
Public Function C(n, m) As Long
Public Function C(n As Long, m As Long) As Long
Dim i As Long, sum1 As Long, k As Long, j As Long If n = m Or n = 0 Or m = 0 Then
C = 1
Exit Function
End If
If n < m Then
Err.Raise -1, "", "m不能大于n!"
End If
sum1 = 1: k = m
If n - m > m Then k = n - m
For i = k + 1 To n
j = j + 1
sum1 = sum1 / j * i '此处先除再乘
Next
C = sum1
End Function
Dim s() As Variant, n As Long, m As Long, sum As Long, sum1 As Long
Dim t As Long
s = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) '楼主要做作业的话,把这里的数组换成你想要的就行了
n = UBound(s) + 1: sum1 = sumArray(s)
For m = 2 To n '这个循环还可以再优化,只需要循环一半就可以了
t = sum1 * C(n, m)
sum = sum + t
Debug.Print "从s数组中取" & m & "个数的所有组合元素之和为" & t
Next
MsgBox sum
End Sub
Public Function C(n As Long, m As Long) As Long
Dim i As Long, sum1 As Long, k As Long, j As Long If n = m Or n = 0 Or m = 0 Then
C = 1
Exit Function
End If
If n < m Then
Err.Raise -1, "", "m不能大于n!"
End If
sum1 = 1: k = m
If n - m > m Then k = n - m
For i = k + 1 To n
j = j + 1
sum1 = sum1 / j * i '此处先除再乘
Next
C = sum1
End FunctionPublic Function sumArray(a() As Variant) As Long
Dim i As Long, sum As Long
For i = 0 To UBound(a)
sum = sum + a(i)
Next
sumArray = sum
End Function
Dim s() As Variant, n As Long, m As Long, sum As Long, sum1 As Long
Dim t As Long, k As Long
s = Array(1, 2, 3, 4, 5)
n = UBound(s) + 1: sum1 = sumArray(s)
For m = 2 To n
t = sum1 * C(n - 1, m - 1)
sum = sum + t
Debug.Print "从s数组中取" & m & "个数的所有组合元素之和为" & t
Next
Debug.Print sum
End Sub
Public Function C(n As Long, m As Long) As Long
Dim i As Long, sum1 As Long, k As Long, j As Long If n = m Or n = 0 Or m = 0 Then
C = 1
Exit Function
End If
If n < m Then
Err.Raise -1, "", "m不能大于n!"
End If
sum1 = 1: k = m
If n - m > m Then k = n - m
For i = k + 1 To n
j = j + 1
sum1 = sum1 / j * i '此处先除再乘
Next
C = sum1
End FunctionPublic Function sumArray(a() As Variant) As Long
Dim i As Long, sum As Long
For i = 0 To UBound(a)
sum = sum + a(i)
Next
sumArray = sum
End Function