|
AACC88 发表于 2013-1-4 23:36
新的附件只抽取7个数据,有办法用数组公式按总和最小值每列抽取一个不同行数据吗?
VBA可以实现- Dim arr()
- Sub pailie2(ParamArray x())
- Dim i As Integer, j As Integer, Num As Long, n As Integer
- Dim a() As Integer '记载数组下标
- Dim b() As Integer '记载使用标识
- n = UBound(x) + 1
- ReDim a(1 To n)
- ReDim b(1 To n)
- i = 1
- a(1) = 0
- Do While i <= n
- a(i) = a(i) + 1
- If a(i) <= n Then
- If b(a(i)) = 0 Then
- If i = n Then '达到数组末,则输出组合情况
- Num = Num + 1
- For j = 1 To n
- arr(Num, j) = x(a(j) - 1)
- Next
- i = i - 1
- b(a(i)) = 0 '清空使用标识
- Else
- b(a(i)) = 1 '标记已使用
- i = i + 1
- a(i) = 0 '重查
- End If
- End If
- Else
- i = i - 1
- If i = 0 Then Exit Do '回溯至数组前则结束
- b(a(i)) = 0
- End If
- DoEvents
- Loop
- End Sub
- Private Sub Command1_Click()
- Dim br, i&, j&, x, t, st
- x = 2 * 10 ^ 26
- ReDim arr(1 To WorksheetFunction.Fact(7), 1 To 7)
- pailie2 1, 2, 3, 4, 5, 6, 7
- br = Range("a1:g7")
- For i = 1 To UBound(arr)
- t = 0
- For j = 1 To UBound(arr, 2)
- t = t + br(j, arr(i, j))
- Next
- If t < x Then x = t: st = br(1, arr(i, 1)) & "+" & br(2, arr(i, 2)) & "+" & br(3, arr(i, 3)) & "+" & br(4, arr(i, 4)) _
- & "+" & br(5, arr(i, 5)) & "+" & br(6, arr(i, 6)) & "+" & br(7, arr(i, 7)) & "="
- Next
- MsgBox st & x
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|