在单元格区域A1:A6中，有一些数据，有的是单独的数字，有的是由连字符分隔的一组数字，例如13-16表示13、14、15、16，现在需要将这些数据拆分并依次放置在列D中，如下图1所示。
在单元格区域A1:A6中，有一些数据，有的是单独的数字，有的是由连字符分隔的一组数字，例如13-16表示13、14、15、16，现在需要将这些数据拆分并依次放置在列D中，如下图1所示。
图1
先不看答案，自已动手试一试。
公式
在单元格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)))
向下拖拉至出现空单元格为止。
公式解析
公式中的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”}
上面公式中的数字5是任意选的，只要能保证将数字筛选出来即可。
对于第二个名称：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}),””
注意，这里没有必要对两个数组使用TRIM函数，Excel在进行数学减法运算时忽略数字前后的空格并强制转换成数学运算。
这样，我们可以看到上面的结果数组中对应于单元格A1:A6中每个数据要返回的数字个数，例如“1-2”将返回2个值、“4-6”将返回3个值，依此类推。因此，该数组的和就是我们想要返回的数字的总数：
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))
可以看到，下面的部分出现了2次：first+TRANSPOSE(ROW(INDIRECT(“1:”&MAX(last-first)+1))-1)
其中，last-first在前面已经讲过，生成数组：{1;2;0;1;3;0}
其最大值是3，然后加上1得到4，即：MAX(last-first)+1
的结果是4。实际上，这个值代表我们从A1:A6的各字符串中范围最大的字符串返回的数字数量。
这样，就将上面的部分公式转换为：first+TRANSPOSE(ROW(INDIRECT(“1:”&4))-1)
转换为：first+TRANSPOSE({1;2;3;4}-1)
得到：first+{0,1,2,3}
这里是公式的关键技巧所在：首先生成一个单列数组，该数组由0至3（即数值范围的最大间隔）组成，然后将其转置为单行数组{0,1,2,3}。接着，将该数组与first生成的数组（{“1 ”;”4 ”;”9”;”10 ”;”13 ”;”21”}）相加。因为这两个相加的数组正交，一个6行1列的数组加上一个1行4列的数组，结果是一个6行4列的数组，有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}
这个数组包含我们想要的数值，但是也包含一些我们不想要的值。其实，之所以生成4列数组，是为了确保能够添加足够数量的整数，因为A1:A6中最大的间隔范围就是4个整数。
要去除不需要的数值，只需将上面数组中的每个值与last生成的数组相比较，（last数组生成的值为A1:A6中每个数值范围的上限）。例如对于上面数组中的第4行{10,11,12,13}，在last数组中对应的值是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对公式中生成的两个数组在相同行中进行比较，例如，左边数组第2行的值{4,5,6,7}与右边数组第2行的值6进行比较、左边数组第5行的值{13,14,15,16}与右边数组第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))
可变为：
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…等数字。
综上，在单元格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
…… 依此类推。
