急EXCEL中如何从一组数中找到2-5个数求和等于期望的某定值?

2024-11-05 17:29:41
推荐回答(2个)
回答1:

帮你弄了一个,首先把那41个数放到A列,B1放期望值699.24,执行下慎神旅面的宏,在C列显示结果:
Sub Macro()
Dim i, j, k, l, m, r As Integer
Dim s As Single
r = 1
s = Cells(1, 2)

'查找2个数的和
For i = 1 To 40
For j = i + 1 To 41
If Cells(i, 1) + Cells(j, 1) = s Then
Cells(r, 3) = Cells(i, 1) & "+" & Cells(j, 1) & "=" & s: r = r + 1
End If
Next j
Next i

'查宽凳找3个数的和
For i = 1 To 39
For j = i + 1 To 40
For k = j + 1 To 41
If Cells(i, 1) + Cells(j, 1) + Cells(k, 1) = s Then
Cells(r, 3) = Cells(i, 1) & "+" & Cells(j, 1) & "+" & Cells(k, 1) & "=" & s: r = r + 1
End If
Next k
Next j
Next i

'查找4个瞎迹数的和
For i = 1 To 38
For j = i + 1 To 39
For k = j + 1 To 40
For l = k + 1 To 41
If Cells(i, 1) + Cells(j, 1) + Cells(k, 1) + Cells(l, 1) = s Then
Cells(r, 3) = Cells(i, 1) & "+" & Cells(j, 1) & "+" & Cells(k, 1) & "+" & Cells(l, 1) & "=" & s: r = r + 1
End If
Next l
Next k
Next j
Next i

'查找5个数的和
For i = 1 To 37
For j = i + 1 To 38
For k = j + 1 To 39
For l = k + 1 To 40
For m = l + 1 To 41
If Cells(i, 1) + Cells(j, 1) + Cells(k, 1) + Cells(l, 1) + Cells(m, 1) = s Then
Cells(r, 3) = Cells(i, 1) & "+" & Cells(j, 1) & "+" & Cells(k, 1) & "+" & Cells(l, 1) & "+" & Cells(m, 1) & "=" & s: r = r + 1
End If
Next m
Next l
Next k
Next j
Next i

End Sub

执行结果:
248.8+233.33+108.67+108.44=699.24
244.73+191.43+147.55+115.53=699.24

回答2:

使用规划求解