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)、转置(数据))=$A$2，0)、转置(Arry1))、0))、“x”、“)

向右拖动，直到单元格K2。

结果如下图2所示。

图2。

公式分析

在公式中，数据、数组1和数组2是定义的三个名称。

名称：数据

参考位置：=$C$1:$K$1。

名称：Arry1。

参考位置：=行(间接(“1:”列(数据))

名称：Arry2。

参考位置：=行(间接(“1:”2列(数据))

让我们看看这两个公式定义的名称是如何工作的。名称：Arry1。

=行(间接(“1:”列(数据))

转换为：

=ROW(INternet(" 1: " 9))

结果是：

={1;2;3;4;5;6;7;8;9}

名称：Arry2。

=ROW(间接(" 1:" 2^COLUMNS(Data)))

转换为：

=ROW(间接(" 1:" 2^9))

转换为：

=ROW(INternet(" 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的所有可能组合组成的阵列矩阵。

在公式中：

转置(数据)

的结果是：

转置({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}

传递给MATCH函数：

match(true,mmult(mod(int((arry2-1)/2^(transpose(arry1)-1)),2),transpose(data))=$a$2,0)

那就是：

匹配(真，{假；假；假；假；假；假；假；假；假；假；假；假；…;假；假；假；假；假；假；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，1，IF({1，0，1，0，1，0，0}，转置(Arr1))，0))，“X”、“)

转换为：

=IF(ISNUMBER(MATCH(1，1，IF({1，0，1，0，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}，0))，“X”、“”

获取：

=IF(ISNUMBER(1)，" X "，")

结果是：

X

也就是说，在单元格C2中输入“x ”,表示它上面的单元格C1是一个加数。

将公式拖放到右侧。在D2牢房：

=IF(ISNUMBER(MATCH(2，{1，FALSE，3，FALSE，5，6，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}，0))，“X”、“”

获取：

=IF(ISNUMBER(3)，" X "，")

结果是：

X

也就是说，在单元格E2中输入“x”，表示它上面的单元格E1是一个加数。

……

等等。

哇哦！

注意：

1.这里的解决方案理论上限制为20个值。数值越大，计算量越大，对计算机资源的需求越大。

2.如果没有等于目标值的数字组合，所有标记的单元格都为空。

3.如果有多个等于目标值的数字组合，将只标记一个组合。