在單元格區(qū)域A1:A6中，有一些數(shù)據(jù)，有的是單獨的數(shù)字，有的是由連字符分隔的一組數(shù)字，例如13-16表示13、14、15、16，現(xiàn)在需要將這些數(shù)據(jù)拆分并依次放置在列D中，如下圖1所示。
圖1
先不看答案，自已動手試一試。
公式
在單元格D1中輸入數(shù)組公式：
=IF(ROWS($D$1:$D1)>SUM(last-first+1),””,SMALL(IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)),ROWS($D$1:$D1)))
向下拖拉至出現(xiàn)空單元格為止。
公式解析
公式中的first和last是定義的兩個名稱。
名稱：first
引用位置：=LEFT(SUBSTITUTE($A$1:$A$6,”-“,REPT(“”,5)),5)
名稱：last
引用位置：=RIGHT(SUBSTITUTE($A$1:$A$6,”-“,REPT(“”,5)),5)
我們來看看這兩個名稱是怎樣運轉的。第一個名稱：first=LEFT(SUBSTITUTE($A$1:$A$6,”-“,REPT(“”,5)),5)
轉換為：=LEFT(SUBSTITUTE({“1-2”;”4-6”;”9”;”10-11”;”13-16”;”21”},”-“,REPT(“”,5)),5)
轉換為：=LEFT(SUBSTITUTE({“1-2”;”4-6”;”9”;”10-11”;”13-16”;”21”},”-“,“ ? ? ” ),5)
轉換為：=LEFT({“1 ? ? 2”;”4 ? ?6”;”9”;”10 ? ? 11”;”13 ? ? 16”;”21”},5)
得到的結果為：={“1 ? ?”;”4 ? ?”;”9”;”10 ?”;”13 ? ”;”21”}
上面公式中的數(shù)字5是任意選的，只要能保證將數(shù)字篩選出來即可。
對于第二個名稱：last。與上面的原理相同，最后得到的結果為：={“ ? ?2”;” ? ?6”;”9”;” ?11”;” ? 16”;”21”}
再來看公式中IF語句的第一部分：IF(ROWS($D$1:$D1)>SUM(last-first+1),””
使用定義的名稱替換公式相對應的名稱位置：IF(ROWS($D$1:$D1)>SUM({“ ? ?2”;” ? ?6”;”9”;” ?11”;” ? 16”;”21”}-{“1 ? ?”;”4 ? ?”;”9”;”10 ?”;”13 ? ”;”21”}+1),””
得到：IF(ROWS($D$1:$D1)>SUM({2;3;1;2;4;1}),””
注意，這里沒有必要對兩個數(shù)組使用TRIM函數(shù)，Excel在進行數(shù)學減法運算時忽略數(shù)字前后的空格并強制轉換成數(shù)學運算。
這樣，我們可以看到上面的結果數(shù)組中對應于單元格A1:A6中每個數(shù)據(jù)要返回的數(shù)字個數(shù)，例如“1-2”將返回2個值、“4-6”將返回3個值，依此類推。因此，該數(shù)組的和就是我們想要返回的數(shù)字的總數(shù)：
IF(ROWS($D$1:$D1)>13,””
所以，向下復制公式時，超過13行將返回空值。
下面看看公式中的主要部分：
SMALL(IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)),ROWS($D$1:$D1))
可以看到，下面的部分出現(xiàn)了2次：first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)
其中，last-first在前面已經(jīng)講過，生成數(shù)組：{1;2;0;1;3;0}
其最大值是3，然后加上1得到4，即：MAX(last-first)+1
的結果是4。實際上，這個值代表我們從A1:A6的各字符串中范圍最大的字符串返回的數(shù)字數(shù)量。
這樣，就將上面的部分公式轉換為：first+TRANSPOSE(ROW(INDIRECT(“1:”&4))-1)
轉換為：first+TRANSPOSE({1;2;3;4}-1)
得到：first+{0,1,2,3}
這里是公式的關鍵技巧所在：首先生成一個單列數(shù)組，該數(shù)組由0至3（即數(shù)值范圍的最大間隔）組成，然后將其轉置為單行數(shù)組{0,1,2,3}。接著，將該數(shù)組與first生成的數(shù)組（{“1 ? ?”;”4 ? ?”;”9”;”10 ?”;”13 ? ”;”21”}）相加。因為這兩個相加的數(shù)組正交，一個6行1列的數(shù)組加上一個1行4列的數(shù)組，結果是一個6行4列的數(shù)組，有24個值。
這樣，上面的部分公式轉換為：{“1 ? ?”;”4 ? ?”;”9”;”10 ?”;”13 ? ”;”21”}+{0,1,2,3}
結果為：{1,2,3,4;4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24}
這個數(shù)組包含我們想要的數(shù)值，但是也包含一些我們不想要的值。其實，之所以生成4列數(shù)組，是為了確保能夠添加足夠數(shù)量的整數(shù)，因為A1:A6中最大的間隔范圍就是4個整數(shù)。
要去除不需要的數(shù)值，只需將上面數(shù)組中的每個值與last生成的數(shù)組相比較，（last數(shù)組生成的值為A1:A6中每個數(shù)值范圍的上限）。例如對于上面數(shù)組中的第4行{10,11,12,13}，在last數(shù)組中對應的值是11，因此剔除12和13，只保留10和11。公式中的比較部分為：
IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””
轉換為：IF({1,2,3,4;4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24}>{2;6;9;11;16;21},””
Excel對公式中生成的兩個數(shù)組在相同行中進行比較，例如，左邊數(shù)組第2行的值{4,5,6,7}與右邊數(shù)組第2行的值6進行比較、左邊數(shù)組第5行的值{13,14,15,16}與右邊數(shù)組第5行的值16進行比較，依此類推。得到的結果為：
IF({FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE;FALSE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE},””
由上述的推導可知，公式中的IF語句：
IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1))
可變?yōu)椋?/p>
IF({FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE;FALSE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,TRUE},””,{1,2,3,4;4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24})
轉換為：{1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}
此外，公式中的：ROWS($D$1:$D1)
隨著向下復制，得到1、2、3…等數(shù)字。
綜上，在單元格D1中原來的公式：
=IF(ROWS($D$1:$D1)>SUM(last-first+1),””,SMALL(IF(first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)>–last,””,first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)),ROWS($D$1:$D1)))
轉換為：=IF(1>13,””,SMALL({1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}, 1))
結果是：1
對于單元格D2中的公式轉換為：=IF(2>13,””,SMALL({1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}, 2))
結果是：2
對于單元格D3中的公式轉換為：=IF(3>13,””,SMALL({1,2,””,””;4,5,6,””;9,””,””,””;10,11,””,””;13,14,15,16;21,””,””,””}, 3))
結果是：4
…… 依此類推。
