求助,在excel中已知求和数值,从多组数字里任意选取几组数字求和要等于求和值,公式方法是怎样?

2025-03-18 07:24:23
推荐回答(5个)
回答1:

共有如下60种组合:
400,2760,1090,8970,12420,5360,7200
2760,400,1090,8970,12420,5360,7200
16440,2180,4140,5360,2880,7200
16440,2760,400,2760,2180,1090,8970,2880,720
2070,2180,8970,12420,5360,7200
2070,2760,400,2760,1090,4140,12420,5360,7200
2070,16440,400,2180,1090,12420,2880,720
2070,16440,400,2760,1090,5360,2880,7200
2070,16440,2760,400,1090,5360,2880,7200
3200,2070,400,2180,8970,12420,5360,2880,720
1380,2760,2760,2180,4140,12420,5360,7200
1380,16440,2760,2180,5360,2880,7200
1380,16440,400,2180,1090,4140,8970,2880,720
1380,16440,2760,2180,5360,2880,7200
1380,3200,2180,1090,8970,12420,5360,2880,720
1380,3200,2760,400,2760,2180,4140,12420,5360,2880,720
1380,3200,2070,2760,2180,1090,4140,12420,5360,2880,720
1380,3200,2070,2760,2180,1090,4140,12420,5360,2880,720
6165,2070,2760,2760,2180,12420,1925,7200,720
6165,2070,2760,400,2760,1090,4140,8970,1925,7200,720
6165,2070,16440,2760,2760,5360,1925,720
6165,3200,2760,2760,8970,12420,1925
6165,3200,2760,2760,1090,4140,5360,1925,2880,7200,720
6165,3200,16440,2180,1090,1925,7200
6165,3200,2070,16440,2760,2760,1925,2880
6165,1380,400,2760,1090,4140,12420,1925,7200,720
6165,1380,2760,2760,2180,4140,8970,1925,7200,720
6165,1380,2760,400,1090,4140,12420,1925,7200,720
6165,1380,16440,400,1090,1925,2880,7200,720
6165,1380,2070,2180,4140,12420,1925,7200,720
6165,1380,2070,16440,4140,5360,1925,720
6165,1380,3200,4140,8970,12420,1925
6165,1380,3200,400,8970,5360,1925,2880,7200,720
6165,1380,3200,2070,400,2760,4140,5360,1925,2880,7200,720
6165,1380,3200,2070,2760,400,4140,5360,1925,2880,7200,720
6165,1380,3200,2070,16440,4140,1925,2880
3815,16440,12420,1925,2880,720
3815,16440,2760,400,2760,2180,1925,7200,720
3815,2070,16440,2760,2180,1090,1925,7200,720
3815,2070,16440,2760,2180,1090,1925,7200,720
3815,3200,2760,400,2760,1090,8970,5360,1925,7200,720
3815,3200,16440,2760,1090,8970,1925
3815,3200,16440,400,12420,1925
3815,3200,16440,2760,1090,8970,1925
3815,3200,2070,2760,2180,8970,5360,1925,7200,720
3815,3200,2070,400,1090,12420,5360,1925,7200,720
3815,3200,2070,2760,2180,8970,5360,1925,7200,720
3815,3200,2070,16440,2760,2760,1090,4140,1925
3815,1380,16440,400,2180,4140,1925,7200,720
3815,1380,2070,2760,4140,8970,12420,1925,720
3815,1380,2070,2760,4140,8970,12420,1925,720
3815,1380,2070,16440,8970,1925,2880,720
3815,1380,3200,2180,12420,5360,1925,7200,720
3815,1380,3200,400,1090,4140,8970,5360,1925,7200,720
3815,1380,3200,2070,16440,400,8970,1925
3815,6165,3200,400,2180,1090,4140,8970,5360,2880
3815,6165,1380,3200,400,2760,2180,1090,8970,5360,2880
3815,6165,1380,3200,2760,400,2180,1090,8970,5360,2880
3815,6165,1380,3200,16440,7200
3815,6165,1380,3200,2070,2760,400,2760,2180,1090,4140,5360,2880
算法是用穷举法,做一组19二进制数,用每一位数字是1代表选取这个数字,是0代表不选取,用每一位数字与对应数据相乘,然后乘积求和表示随机选取求和这个过程,一共有524288种情况,计算量非常大,视你电脑情况量力而行,很可能死机,或者直接告诉你系统资源不足。

回答2:

这问题用vba也只能穷举了,那运算量即使对电脑也是很可观的,由于你并不限定每组组合的数字个数,所以要穷举所有的非空子集,即2^n-1,所以问题取决于你的数据有多庞大,数据量过大就是一个不可能解决的问题。

回答3:

公式应该是不能实现的,可以用VBA来实现,算法的话我想可以采用穷举法,但这样的话,时间复杂度太高了,特别是当你的这个数字组个数越大,时间复杂度就会越高,这样感觉有点得不尝失,可能我考虑的复杂了点,坐等高手!

回答4:

问题似乎交代不够清楚 你的 “组数字” 是什么概念?就是你举例的数字中的 一个为一组?
每一个 “组合”对于 数字的个数是否有限制?(如果对个数没有限制的话 比较困难些)
所以说寻求问题的解决 要严格地给出 详细的条件……
另外这些数字 不应该有重复!
确实要求出和值为固定的某个数?
再补充一下吧……

回答5:

这个直接用excel中的公式恐怕不行,得用VBA