当前所在位置: 首页 > 儿童教育 > 正文

excel公式教程: 找到和的加数

2023-01-26 餐饮美食网 【 字体:

如下图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个元素:

{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;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…}

4. 公式中的部分:

SMALL(IF(MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),Values)=$A$1,Arry2),COLUMNS($A:A))

返回每个为TRUE的元素在数组中的位置:

SMALL(IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…},{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;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;27;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…},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},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中3个值组合之和等于目标值。可以看到,8种组合中,每种确实只有3个值。

在单元格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”,””))

我的脑袋已经不够用了!

阅读全文
相关推荐

如何自制蛋挞

如何自制蛋挞
1、倒入低筋面、黄油和水,揉成面团状,藏一小时后取出,抹上黄油卷起,包上保鲜膜冷藏半小时。2、取出,将面团切成一厘米的面团,放入模具内,用力按压,中间稍薄,外缘要比模具高,将蛋挞液倒入蛋挞皮中,放入烤箱里烤30分钟即可。

肉火烧面怎么和面

肉火烧面怎么和面
1、首先用温水把酵母融开,加入面粉,用手把面粉揉成非常软的面团,可以放一点熬好的猪油在面团里面。2、然后放在温暖的地方,盖上保鲜膜发酵至2倍大,发好的面团用手插入不回缩,就说明面团发酵好了。

奶茶几分糖好喝

奶茶几分糖好喝
1、不同的人对奶茶的口感要求是不一样的,一般来说,奶茶有三分糖、五分糖、七分糖三种不同的口味。2、女生一般都喜欢喝甜一点的,而男生则喜欢喝不那么甜的,七分糖的奶茶最好喝。

过桥米线是哪里的?

过桥米线是哪里的?
过桥米线是来自云南省滇南地区的一种有名特色小吃。过桥米线最早是在清朝就已经出现,距离现在至少有一百多年的历史啦!起源于建水县东城外锁龙桥西侧的鸡市街头处,有建水的特产草芽、地椒作配料,风味独特而远近闻名。深受广大美食爱好者的喜欢,不少人为此专门长途跋涉,只为尝

面霜和乳液的区别

面霜和乳液的区别
面霜和乳液的区别,相较而言乳液的水分含量要比面霜高,乳液质地要轻薄一些,乳液主要作用是保湿,滋润可以隔离外界干燥的气候,面霜既可保湿,又可美白,还能抗衰老,乳液的吸收快一些,而面霜吸收比较慢一些,因为液体的吸收速度都比较快。

怎么摘隐形眼镜

怎么摘隐形眼镜
在摘隐形眼镜之前,先用洗手液将手清洗干净,以免将细菌带入眼睛内,对着镜子,用右手中指轻拉眼睛下眼睑,左手中指轻拉眼睛上眼睑,让黑色眼球暴露在空气中,用右手食指和拇指轻触镜片的两边缘部分,向中间推使镜片拱起,再用两手指轻轻捏出镜片即可。

粉饼和散粉的区别

粉饼和散粉的区别
粉饼是呈压缩固体状态,多呈圆形或者方形,散粉则是细腻的粉末状,粉饼遮瑕力会比较强一些,可以湿用做粉底,或者用来补妆,而散粉则是定妆的效果,粉饼通常用在底妆的第一步,而散粉通常用在底妆最后一步。

冷烫和热烫的区别

冷烫和热烫的区别
冷烫和热烫的区别:冷烫对头发的要求是要在保温状态下才能给卷有个好的效果,而热烫是在干和湿的情况下都行,热烫烫出来的头发比较自然明显而有弹力,冷烫的头发风干后基本看不出来,并且发质会有点干。

高品质香水如何鉴别?用三步就可以解决

高品质香水如何鉴别?用三步就可以解决
1、看色泽以天然香料调制而成的高级香水,都有它本来的颜色,且大都是琥珀色或褐色,看起来很像宝石,比如,从茉莉、玫瑰或水仙等天然鲜花中所萃取的精油都呈黄色、褐白或绿褐色;此外,香水中所添加的魅惑香气物性香料也是褐色,苔类中的橡树苔是绿色,从树根或树根脂类中萃取的

如何去除黑眼圈

如何去除黑眼圈
去除黑眼圈首先可以用热毛巾敷眼,然后再用冷毛巾敷,十分钟就能让黑眼圈淡化的不那么严重,其次将去壳后的鸡蛋用无菌纱布包裹住,敷于眼部轻轻转动,可以急救去除黑眼圈,另外抹完眼霜后,用双手顺时针按摩,可以促进血液循环消除黑眼圈。
本文Tag