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}
這樣，部分公式：
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”,””))
我的腦袋已經不夠用了！
