如何从数据列中查找目标数是由哪些数组成？（数据组合问题）

神箭 · 发表于 2018-7-9 10:27

如何从数据列中查找目标数是由哪些数组成？请各位老师指点一下，多谢了！问题见附件。

duquancai · 发表于 2018-7-9 10:31

看描述，没看懂。手机上网中！

神箭 · 发表于 2018-7-9 10:40

duquancai 发表于 2018-7-9 10:31
看描述，没看懂。手机上网中！

有附件，具体的要看附件。谢谢，麻烦你帮我看看，能否解决这个问题。

duquancai · 发表于 2018-7-9 10:44

神箭发表于 2018-7-9 10:40
有附件，具体的要看附件。谢谢，麻烦你帮我看看，能否解决这个问题。

有空，有电脑的时候，再看附件！！！

liulang0808 · 发表于 2018-7-9 10:45

http://club.excelhome.net/thread-1385584-1-1.html
这个链接楼主可以参考下，
另外香川老师有关于凑数的帖子，可以查阅

神箭 · 发表于 2018-7-9 11:16

duquancai 发表于 2018-7-9 10:44
有空，有电脑的时候，再看附件！！！

非常感谢

神箭 · 发表于 2018-7-9 11:21

liulang0808 发表于 2018-7-9 10:45
http://club.excelhome.net/thread-1385584-1-1.html
这个链接楼主可以参考下，
另外香川老师有关于凑数 ...

贴子看了，不懂啊，能帮看看吗？谢谢了

liulang0808 · 发表于 2018-7-9 14:44

Public d
Public a
Public arr
Public m
Sub lqxs_zd()
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Offset(1, 2).ClearContents
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion
a = 3
m = [e1].Value
For j = 2 To UBound(arr)
If arr(j, 2) < m Then
d(j) = arr(j, 2)
dg j
If d.Count > 0 Then d.Remove j
Else
If arr(j, 2) = m Then
Cells(2, a) = arr(j, 2)
a = a + 1
End If
End If
Next j
Application.ScreenUpdating = True
End Sub
Sub dg(y)
For j = y + 1 To UBound(arr)
sm = WorksheetFunction.Sum(d.items)
If sm + arr(j, 2) = m Then
d(j) = arr(j, 2)
Cells(2, a).Resize(d.Count) = WorksheetFunction.Transpose(d.items)
a = a + 1
d.Remove j
Else
If sm + arr(j, 2) < m Then
d(j) = arr(j, 2)
dg j
End If
End If
If d.exists(j) Then d.Remove j
Next j
End Sub

复制代码

liulang0808 · 发表于 2018-7-9 14:45

由于楼主附件内容比较多，这个代码运行时间会比较长的，供参考

香川群子 · 发表于 2018-7-10 15:17

满足条件的组合有很多吧。

+3951794.88+81156.78+2860+2307.69+1127.46+1035
+3951794.88+80138.8+2476.89+2476.88+1216.9+1127.46+1050
+3951794.88+80113.76+3780+2860+1333.17+400
+3951794.88+80113.76+3680+1700+1660+1333.17
+3951794.88+80113.76+3339.37+2200+1216.9+1216.9+400
+3951794.88+80113.76+3300+2080+1660+1333.17
+3951794.88+80113.76+3100+2280+1660+1333.17
+3951794.88+80113.76+2860+2080+1700+1333.17+400
+3951794.88+80113.76+2850+2530+1660+1333.17
+3951794.88+80113.76+2700+2280+1660+1333.17+400
+3951794.88+80113.76+2670+1700+1620+1333.17+1050
+3951794.88+80113.76+2350.43+2070.84+1700+1216.9+1035
+3951794.88+79566.27+5683.76+1620+1216.9+400
+3951794.88+79566.27+3247.86+2435.9+1620+1216.9+400
+3951794.88+79566.27+2649.57+2159.47+2070.84+1640.78+400
+3951794.88+79566.27+2476.88+2476.88+1700+1216.9+1050
+3951794.88+79090.02+3282.9+2070.84+1660+1333.17+1050
+3951794.88+79090.02+2824+2002.84+1620+1333.17+1216.9+400
+3951794.88+79000+5623.93+2222.22+1640.78
+3951794.88+79000+4881.93+4605
+3951794.88+79000+4466.02+2070.84+1333.17+1216.9+400
+3951794.88+79000+3979.21+2416.94+1640.78+1050+400
+3951794.88+79000+3240+2735.04+2476.89+1035
+3951794.88+79000+3200+2476.88+2476.88+1333.17
+3951794.88+79000+2800+2476.88+2476.88+1333.17+400
+3951794.88+78861.99+3846.15+2476.89+1216.9+1050+1035
+3951794.88+78861.99+2860+2688+2416.94+1660
+3951794.88+78861.99+2820.51+2350.43+2070.83+1333.17+1050
+3951794.88+78861.99+2735.04+2435.9+2070.83+1333.17+1050
+3951794.88+78861.99+2688+2500+2416.94+1620+400
+3951794.88+78861.99+2476.89+2136.75+1709.4+1216.9+1050+1035
+3951794.88+78488.94+4892.2+3888.89+1216.9
+3951794.88+78488.94+3979.21+2761.56+2222.22+1035
+3951794.88+78488.94+3680+2913.99+2070.83+1333.17
+3951794.88+78488.94+3680+2913.98+2070.84+1333.17
+3951794.88+78488.94+3339.37+2824+2384.62+1050+400
+3951794.88+78488.94+3324.79+2530+1709.4+1216.9+1216.9
+3951794.88+78488.94+2913.99+2824+2200+1660+400
+3951794.88+78488.94+2913.99+2070.83+1660+1620+1333.17+400
+3951794.88+78488.94+2913.98+2070.84+1660+1620+1333.17+400
+3951794.88+78488.94+2860+2307.69+2002.84+1700+1127.46
…………

		自动登录	找回密码
密码			免费注册

[求助] 如何从数据列中查找目标数是由哪些数组成？（数据组合问题）