excel公式教程:拆分连字符分隔的数字并放置在同一列中

在单元格区域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

…… 依此类推。

赞(70)
office办公分享 » excel公式教程:拆分连字符分隔的数字并放置在同一列中

本文链接:excel公式教程:拆分连字符分隔的数字并放置在同一列中https://www.officeapi.cn/85929.html

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

OFFICE,天天学一点,进步多一点

联系我们