帮我设计一个公式,计算下列数值中哪些相加等于519:146 72 55 92 46 67 88 35 70 39 112

问题描述:

帮我设计一个公式,计算下列数值中哪些相加等于519:146 72 55 92 46 67 88 35 70 39 112 91 125
1个回答 分类:综合 2014-10-17

问题解答:

我来补答
把每个数放在固定的位置,用二进制来表示这些数的组合状态,例如本题中的13个数值,
146,72,55,92,46,67,88,35,70,39,112,91,125,可以用13位二进制数来表示它的组合状态.二进制上的哪一位数为1,表示该数值参与相加,哪一位数为0,表示该数值不参与相加.例如1111111111111,表示所有数据相加,1111011111111表示第5个数不参与相加,13个数值的组合状态就有8196种.
所以,如果数据的数量不是很多,用Excel可以处理.如果数据量很大,最好还是编程来做,不受Excel容量的限制.对于本问题来说,可以这样做:
1. 输入原始数据:
将原始数据输入B1~N1
2. 将二进制数中的每一位所对应的数值输入到B2~N2:
在B2中输入公式:
=2^(COUNT($B1:$N1)-COLUMN()+COLUMN($B1)-1)
选中B2单元格,向右拖拉复制公式,一直到N2.
3. 估计二进制数的最大值和最小值,确定表格大小:(不估计大小也可以,最小值为0,最大值为8192)
最前面的连续几个数相加如果大于519了,那么这种组合所对应的二进制数就是可能的最大值.
最后面的连续几个数相加如果小于519了,那么这种组合所对应的二进制数就是可能的最小值.
本题中前6个加起来不超过519,前7个加起来超过519,所以最大值是:1111111000000,对应的十进制数为8128.本题中后6个加起来不超过519,后7个加起来超过519,所以最小值是;0000000111111,对应的十进制数为63.于是一共有8128-63+1=8066种组合.计算公式前面有两行固定值,所以表格大小一共有8068行.
4. 输入初始值和公式:
在A2中输入62,在A3中输入公式:
=A2+1
在B3中输入公式:
=MOD(($A3-SUMPRODUCT(C3:$N3,C$2:$N$2))/B$2,2)
选中B3单元格,向右拖拉复制公式,一直到M3.
在N3中输入公式:
=MOD(A3,2)
在O3中输入公式:
=SUMPRODUCT($B$1:$N$1,$B3:$N3)
6. 复制公式
选中A3~O3,向下拖拉复制公式,直到第8068行.
7. 统计总和为519的组合:
在P3中输入公式:
=MATCH(519,O3:O8068,0)+2
在P4中输入公式:
=IF(ISERROR(MATCH(519,OFFSET(O3:O8068,P3-ROW(P1)+1,0,8068-ROW(),1),0)),"",MATCH(519,OFFSET(O3:O8068,P3-ROW(P1)+1,0,8068-ROW(),1),0)+P3+2)
选中P4单元格,下拉复制公式,直到公式的结果为空.
在Q3中输入公式:
=IF(INDEX($B$3:$N$8192,$P3-2,COLUMN(A1))=0,"",INDEX($B$1:$N$1,COLUMN(A1)))
选中Q3单元格,向右拖拉复制公式,共13列.
选中Q3~AD3,向下拖拉复制公式,直到P列最后一行.
8. 验证结果
在AE3单元格输入公式:
=sum(Q3:AD3)
选中AE3单元格,向下拖拉复制公式,直到公式P列的最后一行.
AE列的值应当等于519.
附图给出了结果,共有22种组合. 
公式中有些表格范围直接写成B3:N8192,也可以根据所计算的最大值和最小值,计算表格的最后一个单元.,
 
 
展开全文阅读
剩余:2000