excel公式教程：找到和的加数 _知识经验

有时候，我们给出一个目标值，想要知道这个值是已经提供的一系列值中哪几个值的和。如下图1所示，在单元格A2中给出了目标值47，在单元格C1:K1中有9个值，现在我们想知道这些值中哪些值相加等于47，在这些值的下方单元格中使用“X”标记。

文章插图
图1
在单元格C2中输入公式，然后拖放至单元格K2，得到结果。
那么，如何编写这个公式呢？
先不看答案，自已动手试一试。
公式
在单元格C2中输入数组公式：
=IF(ISNUMBER(MATCH(COLUMNS($C:C),IF(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))=$A$2,0),),TRANSPOSE(Arry1)),0)),”X”,””)
向右拖拉至单元格K2为止。
结果如下图2所示。

文章插图
图2
公式解析
公式中的Data、Arry1和Arry2是定义的三个名称。
名称：Data
引用位置：=$C$1:$K$1
名称：Arry1
引用位置：=ROW(INDIRECT(“1:” &COLUMNS(Data)))
名称：Arry2
引用位置：=ROW(INDIRECT(“1:” &2^COLUMNS(Data)))
我们来看看这两个公式定义的名称是怎样运转的。名称：Arry1
=ROW(INDIRECT(“1:”& COLUMNS(Data)))
转换为：
=ROW(INDIRECT(“1:” & 9))
得到的结果为：
={1;2;3;4;5;6;7;8;9}
名称：Arry2
=ROW(INDIRECT(“1:”& 2^COLUMNS(Data)))
转换为：
=ROW(INDIRECT(“1:” & 2^9))
转换为：
=ROW(INDIRECT(“1:” & 512))
得到的结果为：
={1;2;3;……;510;511;512}
再来看看公式中相同的两个部分：
MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2)
转换为：
MOD(INT(({1;2;3;……;510;511;512}-1)/2^(TRANSPOSE({1;2;3;4;5;6;7;8;9})-1)),2)
转换为：
MOD(INT(({0;1;2;……;509;510;511})/2^({0,1,2,3;4,5,6,7,8}),2)
转换为：
MOD(INT({0;1;2;……;509;510;511}/{1,2,4,8,16,32,64,128,256}),2)
两个数组相除，一个是512行1列的数组，一个是1行9列的数组，得到一个512行9列的矩阵：
MOD(INT({0,0,0,0,0,0,0,0,0;1,0.5,0.25,0.125,0.0625,0.03125,0.015625,0.0078125,0.00390625;2,1,0.5,0.25,0.125,0.0625,0.03125,0.015625,0.0078125;3,1.5,0.75,0.375,0.1875,0.09375,0.046875,0.0234375,0.01171875;……;509,254.5,127.25,63.625,31.8125,15.90625,7.953125,3.9765625,1.98828125;510,255,127.5,63.75,31.875,15.9375,7.96875,3.984375,1.9921875;511,255.5,127.75,63.875,31.9375,15.96875,7.984375,3.9921875,1.99609375}),2)
对矩阵元素取整，结果为：
MOD({0,0,0,0,0,0,0,0,0;1,0,0,0,0,0,0,0,0;2,1,0,0,0,0,0,0,0;3,1,0,0,0,0,0,0,0;……;509,254,127,63,31,15,7,3,1;510,255,127,63,31,15,7,3,1;511,255,127,63,31,15,7,3,1},2)
对矩阵元素求余，结果为：
{0,0,0,0,0,0,0,0,0;
1,0,0,0,0,0,0,0,0;
0,1,0,0,0,0,0,0,0;
1,1,0,0,0,0,0,0,0;
……
1,0,1,1,1,1,1,1,1;
0,1,1,1,1,1,1,1,1;
1,1,1,1,1,1,1,1,1}
这是9个0或1的所有可能组合组成的数组矩阵。
公式中：
TRANSPOSE(Data)
的结果为：
TRANSPOSE({10,14,20,2,12,5,1,17,3})
转置后得到：
{10;14;20;2;12;5;1;17;3}
这样，公式中：
MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))
的结果就是上面得到的两个数组（一个是512行9列的大数组矩阵，另一个是9行1列的数组）的乘积，得到一个512行1列的数组：
{0;10;14;24;20;30;34;44;2;12;16;26;…;50;54;64;60;70;74;84}
这个数组由C1:K1中的9个值按各种组合相加所得到的和组成。
然后，将这个数组与目标值进行比较：