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)))
向下拖拉至出现空单元格为止。
【excel公式教程：拆分连字符分隔的数字并放置在同一列中】公式解析
公式中的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({“12”;”46”;”9”;”1011”;”1316”;”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个值。

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

推荐阅读

对方把我拉黑了怎么恢复移除黑名单的步骤

板筋识图中x.r.各代表什么向

如何根除跳蚤

摄影术公认的诞生日期

韩国电视剧国语版来自

冰棍冻久了为什么会有冰碴

怎么做麻辣牛蛙麻辣牛蛙的做法

茶诗：茶

国家一级建筑师考什么内容

成龙阿娇合作出演的僵尸片叫什么名字

红眼鱼用什么钓最好红眼鱼用啥钓最好

福寿螺卵可以用手摸吗

腊鱼腊肉怎么做熟好吃腊鱼腊肉怎么做熟好吃又简单

史上最精彩的10句含“笑”古诗关于笑的诗句或名言

dnf怎么制作史诗武器

茉莉花茶治胃部不适

人们所说的破月可信嘛?

沃柑皮可以做陈皮吗沃柑皮里面有蓝色能不能吃

求苏格兰风笛名曲

干鲍鱼蒸多久才可以吃制作蒸干鲍鱼的时间