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),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}
【excel公式教程：找到和的加数】这样，部分公式：
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)
取整后的结果：

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

推荐阅读

怎么计算尺寸链——极值法

红烧猪蹄|妈妈最拿手的红烧猪蹄，被我偷学到了

京品铁观音，枸杞泡茶的功效

海尔和美博是什么关系？

二年级比喻作业椅子像什么怎么写？

年夜饭的寓意是什么年夜饭有什么寓意

爸组词有哪些爸组词具体有哪些

为什么古生物的软组织能够形成化石古生物的软组织能够形成化石的原因

轮滑运动包括哪些项目轮滑运动包括的项目介绍

开进口食品超市的费用需多少开进口零食店要多少钱

席中用赵子常诗韵赠鲁志敏其三原文、作者

豉香榨菜蒸粉肠

金卡有什么好处银行的金卡有什么作用

空气炸锅能热饭菜吗

中国人际网3260是什么样的工程

荫的读音

哪些人不能吃核桃仁什么人不能吃核桃

柚子肉吃不完要怎么保存柚子肉存放方法

worry是什么意思

办公桌上适合摆放的物品和植物办公桌上适合摆什么

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

推荐阅读

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