如何在excel公式中查找和的加数？

如何在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)、值)=$A$1，arry2)、列($A:A)))、行($1:1))、“”、“))

向下拖到单元格B11，向右拖到列k。

该公式使用辅助单元格L1，其内容是总和等于目标值的组合数，使用的数组公式为：

=sum(n(mmult(mod(int((arry2-1)/2^(transpose(arry1)-1)),2),values)=a1)

公式分析

公式中的值Arry1和Arry2是定义的三个名称。

名称：值

参考位置：=$ 2:澳元$11澳元。

名称：Arry1。

参考位置：=行(间接(“1:”行(值))

名称：Arry2。

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

让我们取一个单元格中的公式作为sum的加数，看看这个公式是如何工作的。单元格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)、值)=$A$1，arry2)、列($A:A)))、行($1:4))、“”、“))

1.首先看公式的这一部分：

mod(int((arry2-1)/2^(transpose(arry1)-1)),2)

这是这个解决方案的关键。上面的一些公式将生成一个1024行10列的大矩阵数组。为了更好地理解它的工作原理，我们将看一个带有少量生成数组的版本。

假设数字是4，而不是示例中的10，也就是说，名称Values没有定义为：

=$A2:$A11

但是：

=$A2:$A5

这样，名称Arry1:

=行(间接(“1:”行(值))

转换为：

=ROW(间接(" 1:" 4))

获取：

{1;2;3;4}

名称Arry2:

=ROW(间接(" 1:" 2^ROWS(Values)))

转换为：

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

转换为：

=ROW(INternet(" 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个组合。

因此，如果我们使用适当的矩阵乘法，我们可以在由名称“值”定义的单元格区域中生成所有可能的数据求和组合。例如，上面阵列矩阵的第四行：

{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})

获取数据区中第一个值和第二个值的总和。

另一个例子是数组矩阵的第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})

获取数据区中第二个值、第三个值和第四个值的总和。

因为我们已经生成了0和1的所有组合，所以我们可以计算数据区域中所有可能组合的总和。

尽管上面描述了在数据区中只有4个数值，但是它适用于其他大小的数值。

2.有了上面的详细解释，我们再来看看公式的一些部分：

mmult(mod(int((arry2-1)/2^(transpose(arry1)-1)),2),values)

将返回由名称“值”定义的单元格区域中所有可能的值组合的总和，形成一个由1024行和1列组成的数组，总共有1024个元素。下面是数组的前50个元素：

{0;283.75;350.25;634;290.27;574.02;640.52;924.27;246.89;530.64;597.14;880.89;537.16;820.91;887.41;1171.16;457.21;740.96;807.46;1091.21;747.48;1031.23;1097.73;1381.48;704.1;987.85;1054.35;1338.1;994.37;1278.12;1344.62;1628.37;123.69;407.44;473.94;757.69;413.96;697.71;764.21;1047.96;370.58;654.33;720.83;1004.58;660.85;944.6;1011.1;1294.85;580.9;864.65;…}

上面的数组包含等于目标值的元素(用红色字体标记)，另外两个元素是同样等于目标值的第485个和第678个元素。

3.这样，公式的一部分：

mmult(mod(int((arry2-1)/2^(transpose(arry1)-1)),2),values)=$a$1

实际是：

{0;283.75;350.25;634;290.27;574.02;640.52;924.27;246.89;530.64;597.14;880.89;537.16;820.91;887.41;1171.16;457.21;740.96;807.46;1091.21;747.48;1031.23;1097.73;1381.48;704.1;987.85;1054.35;1338.1;994.37;1278.12;1344.62;1628.37;123.69;407.44;473.94;757.69;413.96;697.71;764.21;1047.96;370.58;654.33;720.83;1004.58;660.85;944.6;1011.1;1294.85;580.9;864.65;…}=1054.35

比较后的结果是：

{ FALSE假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；…}

4.公式的一部分：

small(if(mmult(mod(int((arry2-1)/2^(transpose(arry1)-1)),2),values)=$a$1,arry2),columns($a:a)

返回数组中为真的每个元素的位置：

小(如果({假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；真；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；…},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;…}),1)

转换为：

SMALL({ FALSE；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；27;假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；假；…},1)

获取：

27

这告诉我们，1024个和中的第27个和等于我们的目标值。

5.现在，我们需要返回到1024个组合矩阵数组(即上面得到的1024行10列的矩阵)中，找出与目标值相等的求和所涉及的具体值。因此，使用INDEX函数提取矩阵数组中第27行的值：

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

转换为：

INDEX({0，0，0，0，0，0，0，0，0，0；1,0,0,0,0,0,0,0,0,0;0,1,0,0,0,0,0,0,0,0;1,1,0,0,0,0,0,0,0,0;0,0,1,0,0,0,0,0,0,0;1,0,1,0,0,0,0,0,0,0;0,1,1,0,0,0,0,0,0,0;1,1,1,0,0,0,0,0,0,0;0,0,0,1,0,0,0,0,0,0;1,0,0,1,0,0,0,0,0,0;0,1,0,1,0,0,0,0,0,0;1,1,0,1,0,0,0,0,0,0;0,0,1,1,0,0,0,0,0,0;1,0,1,1,0,0,0,0,0,0;0,1,1,1,0,0,0,0,0,0;1,1,1,1,0,0,0,0,0,0;0,0,0,0,1,0,0,0,0,0;1,0,0,0,1,0,0,0,0,0;0,1,0,0,1,0,0,0,0,0;1,1,0,0,1,0,0,0,0,0;0,0,1,0,1,0,0,0,0,0;1,0,1,0,1,0,0,0,0,0;0,1,1,0,1,0,0,0,0,0;1,1,1,0,1,0,0,0,0,0;0,0,0,1,1,0,0,0,0,0;1,0,0,1,1,0,0,0,0,0;0,1,0,1,1,0,0,0,0,0;1,1,0,1,1,0,0,0,0,0;0,0,1,1,1,0,0,0,0,0;1,0,1,1,1,0,0,0,0,0;0,1,1,1,1,0,0,0,0,0;1,1,1,1,1,0,0,0,0,0;0,0,0,0,0,1,0,0,0,0;1,0,0,0,0,1,0,0,0,0;0,1,0,0,0,1,0,0,0,0;1,1,0,0,0,1,0,0,0,0;0,0,1,0,0,1,0,0,0,0;1,0,1,0,0,1,0,0,0,0;0,1,1,0,0,1,0,0,0,0;1,1,1,0,0,1,0,0,0,0;0,0,0,1,0,1,0,0,0,0;1,0,0,1,0,1,0,0,0,0;0,1,0,1,0,1,0,0,0,0;1,1,0,1,0,1,0,0,0,0;0,0,1,1,0,1,0,0,0,0;1,0,1,1,0,1,0,0,0,0;0,1,1,1,0,1,0,0,0,0;1,1,1,1,0,1,0,0,0,0;0,0,0,0,1,1,0,0,0,0;1,0,0,0,1,1,0,0,0,0;…},27,)

结果是：

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

对应单元格A3、A5、A6。

6.下一步很简单。只需检查该行是否对应于数组中的非零值：

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“，”)

转换为：

IF(INDEX({0，1，0，1，1，0，0，0，0，0，0}，ROWS($1:4))，“X”、“”)

转换为：

IF(INDEX({0，1，0，1，1，0，0，0，0，0}，4)，" X "，"")

转换为：

IF(1，“X”，“)

得到

X

扩展版

以下是具有以下功能的修订版本：用户可以指定加数的数量。如下图2所示。

图2。

在图2所示的工作表中，单元格L2中的值表示只有A2:A11中的三个值组合的总和预计等于目标值。可以看出，这八个组合中的每一个真的只有三个值。

单元L1中的数组公式为：

=sum(n(mmult(if(mmult(mod(int((arry2-1)/2^(transpose(arry1)-1)),2),arry1^0)=l2,mod(int((arry2-1)/2^(transpose(arry1)-1)),2),0),values)=a1)

单元B2中的数组公式为：

=if(columns($a:a)$l$1,"",if(index(index(mod(int((arry2-1)/2^(transpose(arry1)-1)),2)、small(if(mmult(if(mmult(mod(int((arry2-1)/2^(transpose(arry1)-1)),2),arry1^0)=$l$2,mod(int((arry2-1)/2^(transpose(arry1)-1)),2),0),values)=$a$1,arry2),columns($a:a)),),rows($1:1)),"x "、""))

我脑袋不够用！