- wps如何自动生成目录:word红头文件制作方法?
- wps教程全集自学:wps表格中怎样设置自动备份文件
- wpsppt制作教程:wps office图片怎么裁剪图片?
- wps演示者视图:WPS Office如何重置工具栏?WPS Office重置工具栏教程
excel公式怎么找到和的加数?如下图2所示,在单元格A1中给出了目标值1054.35,在单元格A2:A11中有10个值,现在我们想知道这些值中哪些值相加等于1054.35,在这些值右侧单元格中使用“X”标记。如果有几种组合加起来都等于1054.35,则将他们都标识出来。
图1
在单元格B2中输入公式,然后向下拖放至单元格B11、向右拖放至K列,得到结果。
在本例中,有3个组合:
1054.35=350.25+246.89+457.21
1054.35=290.27+123.69+198.56+201.35+240.48
1054.35=283.75+290.27+123.69+201.35+155.29
那么,如何编写这个公式呢?
先不看答案,自已动手试一试。
公式
在单元格B2中输入数组公式:
=IF(COLUMNS($A:A)>$L$1,””,IF(INDEX(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),SMALL(IF(MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),Values)=$A$1,Arry2),COLUMNS($A:A)),),ROWS($1:1)),”X”,””))
向下拖拉至单元格B11,向右拖至列K。
公式使用了一个辅助单元格L1,内容为相加等于目标值的组合的个数,其中使用的数组公式为:
=SUM(N(MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),Values)=A1))
公式解析
公式中的Values、Arry1和Arry2是定义的三个名称。
名称:Values
引用位置:=$A$2:$A$11
名称:Arry1
引用位置:=ROW(INDIRECT(“1:” & ROWS(Values)))
名称:Arry2
引用位置:=ROW(INDIRECT(“1:” & 2^ROWS(Values)))
下面以一个确定为和的加数的单元格中的公式,来看看公式是怎么运转的。在单元格B5中的公式为:
=IF(COLUMNS($A:A)>$L$1,””,IF(INDEX(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),SMALL(IF(MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),Values)=$A$1,Arry2),COLUMNS($A:A)),),ROWS($1:4)),”X”,””))
1. 先看看公式中的这部分:
MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2)
这是本解决方案的关键。上述部分公式将会生成一个1024行10列的大矩阵数组,为了更好地理解其运作原理,我们看一个生成的数组数量较小的版本。
假设数值是4个,而不是示例中的10个,即名称Values定义不是:
=$A2:$A11
而是:
=$A2:$A5
这样,名称Arry1:
=ROW(INDIRECT(“1:”& ROWS(Values)))
转换为:
=ROW(INDIRECT(“1:” & 4))
得到:
{1;2;3;4}
名称Arry2:
=ROW(INDIRECT(“1:”& 2^ROWS(Values)))
转换为:
=ROW(INDIRECT(“1:” & 2^4))
转换为:
=ROW(INDIRECT(“1:” & 16))
得到:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}
这样,部分公式:
MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2)
转换为:
MOD(INT(({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-1)/2^(TRANSPOSE({1;2;3;4})-1)),2)
转换为:
MOD(INT(({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-1)/2^({0,1,2,3})),2)
转换为:
MOD(INT(({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-1)/{1,2,4,8}),2)
转换为:
MOD(INT(({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15})/{1,2,4,8}),2)
执行数组除法,因为这两个数组正交,即一个16行1列数组除以一个1行4列数组,得到一个16行4列数组:
MOD(INT(
{0,0,0,0;
1,0.5,0.25,0.125;
2,1,0.5,0.25;
3,1.5,0.75,0.375;
4,2,1,0.5;
5,2.5,1.25,0.625;
6,3,1.5,0.75;
7,3.5,1.75,0.875;
8,4,2,1;
9,4.5,2.25,1.125;
10,5,2.5,1.25;
11,5.5,2.75,1.375;
12,6,3,1.5;
13,6.5,3.25,1.625;
14,7,3.5,1.75;
15,7.5,3.75,1.875}
),2)
取整后的结果:
MOD(
{0,0,0,0;
1,0,0,0;
2,1,0,0;
3,1,0,0;
4,2,1,0;
5,2,1,0;
6,3,1,0;
7,3,1,0;
8,4,2,1;
9,4,2,1;
10,5,2,1;
11,5,2,1;
12,6,3,1;
13,6,3,1;
14,7,3,1;
15,7,3,1}
),2)
对2求余后的结果:
{0,0,0,0;
1,0,0,0;
0,1,0,0;
1,1,0,0;
0,0,1,0;
1,0,1,0;
0,1,1,0;
1,1,1,0;
0,0,0,1;
1,0,0,1;
0,1,0,1;
1,1,0,1;
0,0,1,1;
1,0,1,1;
0,1,1,1;
1,1,1,1}
可以看到,我们成功地创建了一个由0和1组成4个元素的所有16种组合。
因此,如果我们使用合适的矩阵乘法,就可以生成名称Values定义的单元格区域中数据求和的所有可能组合。例如,上面数组矩阵的第4行:
{1,1,0,0}
与假设的数据区域:
{283.75;350.25;290.27;246.89}
作为MMULT函数的参数:
=MMULT({1,1,0,0},{283.75;350.25;290.27;246.89})
得到数据区域中第1个值和第2个值之和。
又如,数组矩阵的第15行:
{0,1,1,1}
与假设的数据区域:
{283.75;350.25;290.27;246.89}
作为MMULT函数的参数:
=MMULT({0,1,1,1},{283.75;350.25;290.27;246.89})
得到数据区域中第2个值、第3个值和第4个值之和。
由于我们已经生成了所有0和1的组合,因此可以计算出数据区域内所有可能组合的和。
虽然上面讲述的是数据区域只有4个数值的情况,但它适用于其他大小的数值数量。
2. 有了上述详细讲解,我们再看看公式中的部分:
MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),Values)
将返回名称Values定义的单元格区域中数值所有可能的组合之和,组成一个1024行1列的数组,共1024个元素。下面是该数组的前50个元素:
上一篇:wps文字排版新手教程:excel怎么识别带图片的单元格 下一篇:wps页眉页脚怎么设置:excel矩阵数据怎么绘制线条
郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。