excel公式教程: 找到和的加数
发布时间:2023-09-08 21:05:11
如下图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”,””))
我的脑袋已经不够用了!
猜你喜欢
- excel2016怎么创建垂直图片重点列表?excel2016表格中想要创建信息列表,该怎么创建直图片重点列表呢?下面我们就来看看详细的教程
- 在Excel数据处理过程中,数值取整比较常见,且针对不同的要求我们需要采用不同的取整函数来解决。那今天帮主就和大家分享数值取整的7种方式,大
- 本文将讲述如何从Excel中包含日期和时间的单元格中提取时间的部分。如何使用Excel中的公式来删除日期/时间戳上的日期部分。如何使用Exc
- 单元格批注就是用于说明单元格内容的说明性文字,可以帮助Excel工作表使用者了解该单元格的意义。在Excel2010工作表中可以添加单元格批
- 1、自动查找生僻字如需要输入“?”,你可以选择“工具→自动更正选项”菜单命令,在打开对话框的“自动更正”选项卡中,在“替换”框中输入被替换的
- VSTO(Visual Studio Tools for Office )是一套用于创建自定义Office应用程序的Visual Studi
- word 2016除了可以在导航窗格中查找与替换文本外,在“查找和替换”对话框中也可以查找与替换文本。事实上,如果在导航窗格中需要对文本进行
- 在日常生活中,我们常常要做出选择,例如,如果明天不下雨,我们就去郊游。在VBA中,也有类似的语句,让我们控制程序的执行方向,例如,如果单元格
- 这篇文章主要介绍了excel怎样把柱状图和折线图放在一起?excel把折线图和柱状图做一起方法的相关资料,需要的朋友可以参考下本文详细内容介
- 今天给大家介绍一波简单操作,分分钟让你的word变成美图软件!话不多说,请看图吧!首先,创建一个“xlsx”或“excel”表格,如下图所示
- 有些用户发现自己想要打开银行站点却打不开,即使打开之后还会提示“TLS安全设置未设置”相关信息,这是怎么回事?出现这样的现象是由于ie浏览器
- 1、在查找替换页面中,开下下面的“高级”,在搜索选项中勾选"使用通配符",在查找内容中输入"?"(注
- 在启动Excel后,切换至“文件”选项卡,在弹出的“Backstage视图”中,会保留4个最近打开过的文件,以帮助用户以最快的速度打开上一次
- Excel技巧是我们经常会使用到的,相信大家也不陌生, 至于我们要怎样去利用好它,让它成为我们最有利的帮手,我们还是需要下一翻苦力的,今天小
- 微软在 2006 年推出了 Office 2007, 这个新的Office 系统使用新的 Ribbon 功能区取代了 Office 2003
- 在举办会议或者进行评比时,需要对相关的人员姓名按照姓氏笔划进行排序。对于新手来说还是有一定难度,怎么办?今天,小编就教大家在Word中进行将
- 我们在使用Word文档的时候,由于在WPS中是无法直接调节光标大小的,所以很多人以为光标就无法调整了。其实不是的,这边有一个关于调节光标的小
- 蓝屏故障基本都是因为系统不兼容引起的崩溃问题,不同的代码又有不同的故障原因,那么Win10专业版出现蓝屏代码0x0000001e要怎么解决呢
- 大家都知道如果太长时间离开电脑它就会自动锁屏,如果没有设置密码的话,回来之后直接登录就好了。但是如果设置了密码又锁屏了太长时间就有可能出现用
- 当我们使用计算机时,如果我们想安装win10系统并在计算机上平稳运行,小边认为我们可以看看我们的计算机配置是否足够运行。还可以对自己的系统进