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个值按各种组合相加所得到的和组成。

然后,将这个数组与目标值进行比较:

MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))=$A$2

即:

{0;10;14;24;20;30;34;44;2;12;16;26;…;50;54;64;60;70;74;84}=47

比较后得到:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

传递给MATCH函数:

MATCH(TRUE,MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))=$A$2,0)

即:

MATCH(TRUE, {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},0)

得到的结果为等于目标值的数据在数组中的位置,本例中为:54。

根据上面公式解析的结果,此时公式中的:

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),)

变换过程为:

INDEX({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},54,)

由于INDEX函数忽略了列参数,因此返回矩阵数组中的第54行,即:

{1,0,1,0,1,1,0,0,0}

可以看到,数组中的1对应着要得到目标值找出的加数,现在要将他们标识出来。

此时单元格C2中的公式变为:

=IF(ISNUMBER(MATCH(1,IF({1,0,1,0,1,1,0,0,0},TRANSPOSE(Arr1)),0)),”X”,””)

转换为:

=IF(ISNUMBER(MATCH(1,IF({1,0,1,0,1,1,0,0,0},{1,2,3,4,5,6,7,8,9}),0)),”X”,””)

转换为:

=IF(ISNUMBER(MATCH(1,{1,FALSE,3,FALSE,5,6,FALSE,FALSE,FALSE},0)),”X”,””)

得到:

=IF(ISNUMBER(1),”X”,””)

结果为:

X

即在单元格C2中输入“X”,表明其上方单元格C1为一个加数。

将公式向右拖放。在单元格D2中:

=IF(ISNUMBER(MATCH(2,{1,FALSE,3,FALSE,5,6,FALSE,FALSE,FALSE},0)),”X”,””)

得到:

=IF(ISNUMBER(#N/A),”X”,””)

转换为:

=IF(FLASE,”X”,””)

结果为空。

在单元格E2中:

=IF(ISNUMBER(MATCH(3,{1,FALSE,3,FALSE,5,6,FALSE,FALSE,FALSE},0)),”X”,””)

得到:

=IF(ISNUMBER(3),”X”,””)

结果为:

X

即在单元格E2中输入“X”,表明其上方单元格E1为一个加数。

……

依此类推。

哇塞!

注意:

1.这里的解决方案理论上限制在20个数值。值越多,计算量越大,对电脑资源需求量也越大。

2.如果不存在和等于目标值的数值组合,则所有标记单元格为空。

3.如果有多个数值组合等于目标值,则只标记其中一种组合。

赞(67)
office办公分享 » excel公式教程:找到和的加数

本文链接:excel公式教程:找到和的加数https://www.officeapi.cn/85928.html

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

OFFICE,天天学一点,进步多一点

联系我们