有時候，我們給出一個目標值，想要知道這個值是已經提供的一系列值中哪幾個值的和。如下圖1所示，在單元格A2中給出了目標值47，在單元格C1:K1中有9個值，現在我們想知道這些值中哪些值相加等于47，在這些值的下方單元格中使用“X”標記。
圖1
在單元格C2中輸入公式，然后拖放至單元格K2，得到結果。
那么，如何編寫這個公式呢？
先不看答案，自已動手試一試。
公式
在單元格C2中輸入數組公式：
=IF(ISNUMBER(MATCH(COLUMNS($C:C),IF(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))=$A$2,0),),TRANSPOSE(Arry1)),0)),”X”,””)
向右拖拉至單元格K2為止。
結果如下圖2所示。
圖2
公式解析
公式中的Data、Arry1和Arry2是定義的三個名稱。
名稱：Data
引用位置：=$C$1:$K$1
名稱：Arry1
引用位置：=ROW(INDIRECT(“1:” &COLUMNS(Data)))
名稱：Arry2
引用位置：=ROW(INDIRECT(“1:” &2^COLUMNS(Data)))
我們來看看這兩個公式定義的名稱是怎樣運轉的。名稱：Arry1
=ROW(INDIRECT(“1:”& COLUMNS(Data)))
轉換為：
=ROW(INDIRECT(“1:” & 9))
得到的結果為：
={1;2;3;4;5;6;7;8;9}
名稱：Arry2
=ROW(INDIRECT(“1:”& 2^COLUMNS(Data)))
轉換為：
=ROW(INDIRECT(“1:” & 2^9))
轉換為：
=ROW(INDIRECT(“1:” & 512))
得到的結果為：
={1;2;3;……;510;511;512}
再來看看公式中相同的兩個部分：
MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2)
轉換為：
MOD(INT(({1;2;3;……;510;511;512}-1)/2^(TRANSPOSE({1;2;3;4;5;6;7;8;9})-1)),2)
轉換為：
MOD(INT(({0;1;2;……;509;510;511})/2^({0,1,2,3;4,5,6,7,8}),2)
轉換為：
MOD(INT({0;1;2;……;509;510;511}/{1,2,4,8,16,32,64,128,256}),2)
兩個數組相除，一個是512行1列的數組，一個是1行9列的數組，得到一個512行9列的矩陣：
MOD(INT({0,0,0,0,0,0,0,0,0;1,0.5,0.25,0.125,0.0625,0.03125,0.015625,0.0078125,0.00390625;2,1,0.5,0.25,0.125,0.0625,0.03125,0.015625,0.0078125;3,1.5,0.75,0.375,0.1875,0.09375,0.046875,0.0234375,0.01171875;……;509,254.5,127.25,63.625,31.8125,15.90625,7.953125,3.9765625,1.98828125;510,255,127.5,63.75,31.875,15.9375,7.96875,3.984375,1.9921875;511,255.5,127.75,63.875,31.9375,15.96875,7.984375,3.9921875,1.99609375}),2)
對矩陣元素取整，結果為：
MOD({0,0,0,0,0,0,0,0,0;1,0,0,0,0,0,0,0,0;2,1,0,0,0,0,0,0,0;3,1,0,0,0,0,0,0,0;……;509,254,127,63,31,15,7,3,1;510,255,127,63,31,15,7,3,1;511,255,127,63,31,15,7,3,1},2)
對矩陣元素求余，結果為：
{0,0,0,0,0,0,0,0,0;
1,0,0,0,0,0,0,0,0;
0,1,0,0,0,0,0,0,0;
1,1,0,0,0,0,0,0,0;
……
1,0,1,1,1,1,1,1,1;
0,1,1,1,1,1,1,1,1;
1,1,1,1,1,1,1,1,1}
這是9個0或1的所有可能組合組成的數組矩陣。
公式中：
TRANSPOSE(Data)
的結果為：
TRANSPOSE({10,14,20,2,12,5,1,17,3})
轉置后得到：
{10;14;20;2;12;5;1;17;3}
這樣，公式中：
MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))
的結果就是上面得到的兩個數組（一個是512行9列的大數組矩陣，另一個是9行1列的數組）的乘積，得到一個512行1列的數組：
{0;10;14;24;20;30;34;44;2;12;16;26;…;50;54;64;60;70;74;84}
這個數組由C1:K1中的9個值按各種組合相加所得到的和組成。
然后，將這個數組與目標值進行比較：
MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))=$A$2
即：
{0;10;14;24;20;30;34;44;2;12;16;26;…;50;54;64;60;70;74;84}=47
比較后得到：
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
傳遞給MATCH函數：
MATCH(TRUE,MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))=$A$2,0)
即：
MATCH(TRUE, {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},0)
得到的結果為等于目標值的數據在數組中的位置，本例中為：54。
根據上面公式解析的結果，此時公式中的：
INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Data))=$A$2,0),)
變換過程為：
INDEX({0,0,0,0,0,0,0,0,0;
1,0,0,0,0,0,0,0,0;
0,1,0,0,0,0,0,0,0;
1,1,0,0,0,0,0,0,0;
……
1,0,1,1,1,1,1,1,1;
0,1,1,1,1,1,1,1,1;
1,1,1,1,1,1,1,1,1},54,)
由于INDEX函數忽略了列參數，因此返回矩陣數組中的第54行，即：
{1,0,1,0,1,1,0,0,0}
可以看到，數組中的1對應著要得到目標值找出的加數，現在要將他們標識出來。
此時單元格C2中的公式變?yōu)椋?/p>
=IF(ISNUMBER(MATCH(1,IF({1,0,1,0,1,1,0,0,0},TRANSPOSE(Arr1)),0)),”X”,””)
轉換為：
=IF(ISNUMBER(MATCH(1,IF({1,0,1,0,1,1,0,0,0},{1,2,3,4,5,6,7,8,9}),0)),”X”,””)
轉換為：
=IF(ISNUMBER(MATCH(1,{1,FALSE,3,FALSE,5,6,FALSE,FALSE,FALSE},0)),”X”,””)
得到：
=IF(ISNUMBER(1),”X”,””)
結果為：
X
即在單元格C2中輸入“X”，表明其上方單元格C1為一個加數。
將公式向右拖放。在單元格D2中：
=IF(ISNUMBER(MATCH(2,{1,FALSE,3,FALSE,5,6,FALSE,FALSE,FALSE},0)),”X”,””)
得到：
=IF(ISNUMBER(#N/A),”X”,””)
轉換為：
=IF(FLASE,”X”,””)
結果為空。
在單元格E2中：
=IF(ISNUMBER(MATCH(3,{1,FALSE,3,FALSE,5,6,FALSE,FALSE,FALSE},0)),”X”,””)
得到：
=IF(ISNUMBER(3),”X”,””)
結果為：
X
即在單元格E2中輸入“X”，表明其上方單元格E1為一個加數。
……
依此類推。
哇塞！
注意：
1.這里的解決方案理論上限制在20個數值。值越多，計算量越大，對電腦資源需求量也越大。
2.如果不存在和等于目標值的數值組合，則所有標記單元格為空。
3.如果有多個數值組合等于目標值，則只標記其中一種組合。
